【JAVA】Mysql varchar自增ID

import utils.DBHelper;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

import java.text.SimpleDateFormat;

import java.util.Date;


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