【Java】自动获取某表某列的最大ID数

使用场景：

当需要往数据库插入数据时，表的主键需要接着已经有的数据后面进行自增。比如已经wq_customer表里，主键为TBL_ID，如果是空表，那么插入的数据TBL_ID设置为1，如果已经有n条数据，那么插入的数据TBL_ID设置为n+1。

SQL语句为

SELECT
    CASE
WHEN MAX(TBL_ID) IS NULL THEN
    1
ELSE
    MAX(TBL_ID) + 1
END AS MAXID
FROM
    wq_customer
WHERE
    TBL_ID < 100000

单纯查询某表某列MAX为：

SELECT
    MAX(TBL_ID)
FROM
    wq_customer
WHERE
    TBL_ID < 100000;

mybatis的相关代码如下（仅供参考）：

调用代码如下：

 1 package com.msk.batch.wq.logic;
 2 
 3 import com.msk.batch.wq.bean.BWQ201701Bean;
 4 import com.msk.batch.wq.bean.BWQ201701Bean2;
 5 import com.msk.batch.wq.bean.BWQ201701Bean3;
 6 import com.msk.common.logic.CommonLogic;
 7 import com.msk.core.base.BaseDao;
 8 import com.msk.core.base.BaseLogic;
 9 import com.msk.core.bean.BaseParam;
10 import org.slf4j.Logger;
11 import org.slf4j.LoggerFactory;
12 import org.springframework.beans.factory.annotation.Autowired;
13 
14 import java.util.List;
15 
16 /**
17  * Created by fjm on 2017/1/9.
18  */
19 public class BWQ201701Logic extends BaseLogic{
20 
21     private static Logger logger = LoggerFactory.getLogger(BWQ201701Logic.class);
22 
23     public interface SqlId {
24         static final String SQLID_INSERT_OMSDATA = "insertCmCustomer";
25     }
26 
27     @Autowired
28     @Override
29     public void setBaseDao(BaseDao baseDao) {
30         super.setBaseDao(baseDao);
31     }
32     @Autowired
33     CommonLogic commonLogic;
34 
35 
36     public void insertCmCustomer(List<BWQ201701Bean3> bwq201701BeanList){
37 
38         for(BWQ201701Bean3 bwq201701Beans:bwq201701BeanList){
39             BWQ201701Bean bwq201701Bean = new BWQ201701Bean();
40             long id=commonLogic.maxId("WQ_CUSTOMER","TBL_ID");
41             bwq201701Bean.setTblId(id);
42             bwq201701Bean.setCmId(bwq201701Beans.getCm_id());
43             bwq201701Bean.setCmName(bwq201701Beans.getCm_name());
44             bwq201701Bean.setCmCode(bwq201701Beans.getCm_code());
45             bwq201701Bean.setCmType(bwq201701Beans.getCm_type());
46             bwq201701Bean.setCmManager(bwq201701Beans.getCm_manager());
47             super.save(SqlId.SQLID_INSERT_OMSDATA, bwq201701Bean);
48         }
49     }
50 
51 }

共通代码如下：

 1 package com.msk.common.logic;
 2 
 3 import java.util.List;
 4 
 5 import org.slf4j.Logger;
 6 import org.slf4j.LoggerFactory;
 7 import org.springframework.beans.factory.annotation.Autowired;
 8 import org.springframework.stereotype.Service;
 9 import org.springframework.transaction.annotation.Transactional;
10 
11 import com.msk.core.base.BaseDao;
12 import com.msk.core.base.BaseLogic;
13 import com.msk.core.bean.BaseParam;
14 
15 /**
16  * CommonLogic
17  *
18  * @author fjm
19  * @version 1.0
20  **/
21 @Service("commonLogic")
22 public class CommonLogic extends BaseLogic {
23     /**
24      * logger
25      */
26     private static Logger logger = LoggerFactory.getLogger(CommonLogic.class);
27 
28     /**
29      * SQL Id
30      */
31     interface SqlId {
32         /** 获得最大Max Id */
33         String SQL_ID_MAX_ID = "maxId";
34     }
35 
36     /**
37      * 获得Max Id
38      * 
39      * @param tableName 表名
40      * @param tableFiled 字段名称
41      * @return Max Id
42      */
43     @Transactional(readOnly = true)
44     public Long maxId(String tableName, String tableFiled) {
45         logger.debug("获得Max Id");
46         BaseParam param = new BaseParam();
47         param.setFilter("tableName", tableName);
48         param.setFilter("tableFiled", tableFiled);
49         return (Long) super.findObject(SqlId.SQL_ID_MAX_ID, param);
50     }
51 
52 
53     @Autowired
54     @Override
55     public void setBaseDao(BaseDao baseDao) {
56         super.setBaseDao(baseDao);
57     }
58 }

xml代码如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.msk.common.logic.CommonLogic">
    <sql id="maxIdSql">
        SELECT
              CASE
              WHEN MAX(${filterMap.tableFiled}) IS NULL THEN 1
              ELSE MAX(${filterMap.tableFiled})+1 END AS MAXID
        FROM ${filterMap.tableName}
        WHERE ${filterMap.tableFiled} <![CDATA[<]]> 100000
    </sql>
    <select id="maxId" parameterType="BaseParam" resultType="java.lang.Long">
        <include refid="maxIdSql"/>
    </select>
</mapper>