Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Answers Viewqa SQL Aggregrate function Max() in db
Login         

View Questions and Answers by Category

Advertisements


 
Have Programming Question? Ask it here!
 
 
 


Darsana K
Aggregrate function Max() in db
1 Answer(s)      a year and 9 months ago
Posted in : SQL


Hi everyone i have a question and stuck with this problem. i have a table like Brand in mySql db where brandid(varchar) is generating automatically.. im having a Db connection class and a method for generating id. Mycode for id generation is:

public String GetId(String sql, String strchar) {
    int num;
    String strid, value;
    try {
        stmt = conn.createStatement();
        rst = stmt.executeQuery(sql);
        // System.out.println("stmt executed");
        if (rst.next()) {
            strid = rst.getString(1);
            //   System.out.println("strid is:"+strid);
            if (strid == null) {
                return strchar + "1";
            } else {
                num = Integer.parseInt(strid.substring(2, 3));
                //System.out.println("inside if");
                System.out.println("id is:" + (num + 1));
                num++;
                return strchar + num;
            }

        }
        return strchar + "1" + "hi";


    } catch (SQLException sq) {
        System.out.println("error" + sq);
        return "error";
    }
}

my problem is after generating BR9 max(brandid) will always give BR9 instead of BR10 even if BR10 is inserted.So after a row with BR9 again and again i have Br10 inserted when executing the getid() ,because BR9 is getting after max(brandid).i know one solution that is to give start id as BR1001,but i think it is not obsolete because after the range BR9999 ,max(brandid) will always give BR9999.can anyaone suggest me good solution for that.Thanks in advance.


Advertisement
View Answers

January 12, 2013 at 12:33 PM


anyway i found answer to my question

use query like

String bid = dbconn.GetId("select max(cast(substring(BrandId,3)as unsigned)) from brand", "BR")

and my db class method shoul be like:

        stmt=conn.createStatement();
        rst=stmt.executeQuery(sql); 
         if(rst.next())
        {   
                num=rst.getInt(1); 
                if(num == 0)
                {
                return strchar + "1";
                }
                else{
                num++;
              //  System.out.println("hi"+num);
                return strchar +num;
                } 

        }


Related Tutorials/Questions & Answers:
Advertisements
 

 

 

DMCA.com