mysql varchar 自增_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(); } } }