Mysql varchar自增ID

import java.sql.Connection;


import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;


import utils.DBHelper;


public class CreateID {
static DBHelper dbHelper = new DBHelper();
Connection conn = dbHelper.getConn();// 获取连接
Statement stmt = null;


/**
* 
* 
* @param head
*            头部编号
* @param col
*            字段名称
* @param tableName
*            数据表名称
* @return
* @throws Exception
*/
public String createID(String head, String col, String tableName) throws Exception {
stmt = conn.createStatement();
// 获取编号最大值
String sql = "select " + col + " from " + tableName + " order by " + col + " desc limit 0,1";
ResultSet rs = stmt.executeQuery(sql);


String value = null;// 课程编号值
ResultSetMetaData metaData = rs.getMetaData();
if (rs.next()) {
// 此方法返回列的别名。如果列别名不可用，此方法将返回列名称。
String columnName = metaData.getColumnLabel(1);
// 获取到最大的编号值
value = rs.getString(columnName);
}
if (value == null) {
// 如果编号是空，则新增为"KC0001"
value = head + "0001";
} else {
// 取出后4位流水号
value = value.substring(2, 6);
int num = Integer.parseInt(value) + 1;// 实现编号自增
int length = Integer.toString(num).length();
String zero = "";
// 将剩余位数补全为0
for (int i = 0; i < 4 - length; i++) {
zero += "0";
}
value = head + zero + num;// 返回流水号的数值
}
dbHelper.closeConn();
return value;


}


/**
* 
* @param head
* @param col
* @param tableName
* @return
* @throws Exception
*/
public String createOrderID(String head, String col, String tableName) throws Exception {
stmt = conn.createStatement();
String sql = "select " + col + " from " + tableName + " order by " + col + " desc limit 0,1";
ResultSet rs = stmt.executeQuery(sql);


String value = null;
String dateNow = new SimpleDateFormat("yyyyMM").format(new Date());

ResultSetMetaData metaData = rs.getMetaData();
if (rs.next()) {
metaData.getColumnCount();
String columnName = metaData.getColumnLabel(1);


value = rs.getString(columnName);


}
if (value == null) {
value = head + dateNow + "0001";
} else {
String date=value.substring(3,9);
if (date.equals(dateNow)) {
value = value.substring(9, 13);
int num = Integer.parseInt(value) + 1;
int length = Integer.toString(num).length();
String zero = "";
for (int i = 0; i < 4 - length; i++) {
zero += "0";
}
value = head + date + zero + num;
} else {
value = head + dateNow + "0001";
}
}
dbHelper.closeConn();
return value;
}


public String createEmpId() throws Exception {
return createID("XD", "employeeid", "employeebasicinfo");
}


public String createPetId() throws Exception {
return createOrderID("Pet", "petid", "petinfo");
}
public static void main(String[] args) {
try {
System.out.println(new CreateID().createPetId());
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}


}