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.

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

        }