【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的相关代码如下（仅供参考）：

调用代码如下：

package com.msk.batch.wq.logic;

import com.msk.batch.wq.bean.BWQ201701Bean;
import com.msk.batch.wq.bean.BWQ201701Bean2;
import com.msk.batch.wq.bean.BWQ201701Bean3;
import com.msk.common.logic.CommonLogic;
import com.msk.core.base.BaseDao;
import com.msk.core.base.BaseLogic;
import com.msk.core.bean.BaseParam;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;

import java.util.List;

/**
 * Created by fjm on 2017/1/9.
 */
public class BWQ201701Logic extends BaseLogic{

    private static Logger logger = LoggerFactory.getLogger(BWQ201701Logic.class);

    public interface SqlId {
        static final String SQLID_INSERT_OMSDATA = "insertCmCustomer";
    }

    @Autowired
    @Override
    public void setBaseDao(BaseDao baseDao) {
        super.setBaseDao(baseDao);
    }
    @Autowired
    CommonLogic commonLogic;


    public void insertCmCustomer(List<BWQ201701Bean3> bwq201701BeanList){

        for(BWQ201701Bean3 bwq201701Beans:bwq201701BeanList){
            BWQ201701Bean bwq201701Bean = new BWQ201701Bean();
            long id=commonLogic.maxId("WQ_CUSTOMER","TBL_ID");
            bwq201701Bean.setTblId(id);
            bwq201701Bean.setCmId(bwq201701Beans.getCm_id());
            bwq201701Bean.setCmName(bwq201701Beans.getCm_name());
            bwq201701Bean.setCmCode(bwq201701Beans.getCm_code());
            bwq201701Bean.setCmType(bwq201701Beans.getCm_type());
            bwq201701Bean.setCmManager(bwq201701Beans.getCm_manager());
            super.save(SqlId.SQLID_INSERT_OMSDATA, bwq201701Bean);
        }
    }

}

共通代码如下：

package com.msk.common.logic;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.msk.core.base.BaseDao;
import com.msk.core.base.BaseLogic;
import com.msk.core.bean.BaseParam;

/**
 * CommonLogic
 *
 * @author fjm
 * @version 1.0
 **/
@Service("commonLogic")
public class CommonLogic extends BaseLogic {
    /**
     * logger
     */
    private static Logger logger = LoggerFactory.getLogger(CommonLogic.class);

    /**
     * SQL Id
     */
    interface SqlId {
        /** 获得最大Max Id */
        String SQL_ID_MAX_ID = "maxId";
    }

    /**
     * 获得Max Id
     * 
     * @param tableName 表名
     * @param tableFiled 字段名称
     * @return Max Id
     */
    @Transactional(readOnly = true)
    public Long maxId(String tableName, String tableFiled) {
        logger.debug("获得Max Id");
        BaseParam param = new BaseParam();
        param.setFilter("tableName", tableName);
        param.setFilter("tableFiled", tableFiled);
        return (Long) super.findObject(SqlId.SQL_ID_MAX_ID, param);
    }


    @Autowired
    @Override
    public void setBaseDao(BaseDao baseDao) {
        super.setBaseDao(baseDao);
    }
}

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>