【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>