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