Aggregrate function Max() in db

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.

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;
                } 

        }