实际开发中我们操作数据库持久化，总是需要写重复的mapper，service，xml浪费了我们大量的时间，在这里推荐大家使用SqlSessionTemplate
server.port=8081
#server.servlet.context-path=/recordLog
#数据源必填项
spring.datasource.url=jdbc:mysql://localhost:3306/test?generateSimpleParameterMetadata=true&allowMultiQueries=true
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#验证连接的有效性
spring.datasource.secondary.test-while-idle=true
#获取连接时候验证，会影响性能
spring.datasource.secondary.test-on-borrow=false
#在连接归还到连接池时是否测试该连接
spring.datasource.secondary.test-on-return=false
spring.datasource.secondary.validation-query=SELECT 1 FROM DUAL
#空闲连接回收的时间间隔，与test-while-idle一起使用，设置5分钟
spring.datasource.secondary.time-between-eviction-runs-millis=300000
#连接池空闲连接的有效时间 ，设置30分钟
spring.datasource.secondary.min-evictable-idle-time-millis=1800000
spring.datasource.secondary.initial-size=5
#指定连接池中最大的活跃连接数.
spring.datasource.secondary.max-active=50
#指定连接池等待连接返回的最大等待时间，毫秒单位.
spring.datasource.secondary.max-wait=60000
#指定必须保持连接的最小值
spring.datasource.secondary.min-idle=5
#Mybatis配置
#mybatis.config-location=classpath:config/sqlMapConfig.xml
mybatis.type-aliases-package=com.example.recordlog.bean
#mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
#引用mybatis-plus-boot-starter写法如下
mybatis-plus.mapper-locations=classpath:mybatis/mapper/*.xml
import java.util.List;
/**
* @author liuminglin
*
*/
public interface BaseDao {
/**
* 单个查询
*
* @Param namespace :xml的映射路径
* @Param sqlId :xml中方法的名字
* @Param params :参数
* @Return
*/
E select(String namespace, String sqlId, T params);
List selectList(String namespace, String sqlId, T params);
/**
* @Desc
* @Param 单个修改
* @Return
*/
int update(String namespace, String sqlId, T params);
/**
* @Desc
* @Param 批量修改
* @Return
*/
List updateList(String namespace, String sqlId, List list);
/**
* @Desc
* @Param 单个插入
* @Return
*/
long insert(String namespace, String sqlId, T params);
/**
* @Desc
* @Param 批量差入
* @Return
*/
List insertList(String namespace, String sqlId, List list);
/**
* @Desc
* @Param 单个删除
* @Return
*/
int delete(String namespace, String sqlId, T params);
/**
* @Desc
* @Param 批量删除
* @Return
*/
List deleteList(String namespace, String sqlId, List list);
/**
* @Desc
* @Param 所有的批量都可以用这个方法，它识别的是xml的sql，与方法无关;bathcount指的是多少条提交一次事物
* 注意:数据库连接池请求超时 HikariPool-1 - Connection is not available, request timed out after 30009ms
* 解决方案：
* 1.增加数据库连接池
* 2.多个线程通过 SynchronousQueue.pool() 获取连接
* @Return
*/
void batchALL(String namespace, String sqlId, List params, Integer bathcount);
/**
* @Desc
* @Param 所有的批量都可以用这个方法，
*/
void batchExecutor(String namespace, String sqlId, List params);
}
3.BaseDAOImpl
import com.example.recordlog.service.BaseDao;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@Slf4j
@Component
public class BaseDaoImpl implements BaseDao {
@Autowired
private SqlSessionTemplate sqlSessionTemplate;
@Override
public E select(String namespace, String sqlId, T params) {
if (params == null) {
return sqlSessionTemplate.selectOne(namespace + "." + sqlId);
} else {
return sqlSessionTemplate.selectOne(namespace + "." + sqlId, params);
}
}
@Override
public List selectList(String namespace, String sqlId, T params) {
if (params == null) {
return sqlSessionTemplate.selectList(namespace + "." + sqlId);
} else {
return sqlSessionTemplate.selectList(namespace + "." + sqlId, params);
}
}
@Transactional(rollbackFor = Exception.class)
@Override
public int update(String namespace, String sqlId, T params) {
if (params == null) {
return sqlSessionTemplate.update(namespace + "." + sqlId);
} else {
return sqlSessionTemplate.update(namespace + "." + sqlId, params);
}
}
@Transactional(rollbackFor = Exception.class)
@Override
public List updateList(String namespace, String sqlId, List list) {
Long startTime = System.currentTimeMillis();
try {
if (CollectionUtils.isEmpty(list)) {
return null;
}
MappedStatement ms = sqlSessionTemplate.getConfiguration().getMappedStatement(namespace + "." + sqlId);
SqlCommandType sqlCommandType = ms.getSqlCommandType();
BoundSql boundSql = ms.getSqlSource().getBoundSql(list.get(0));
String sql = boundSql.getSql();
List list2 = boundSql.getParameterMappings();
//此处错误
// Connection connection = sqlSessionTemplate.getConnection();
Connection connection = sqlSessionTemplate.getSqlSessionFactory().openSession().getConnection();
PreparedStatement statement = null;
if (sqlCommandType == SqlCommandType.INSERT) {
statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
} else {
statement = connection.prepareStatement(sql);
}
for (T item : list) {
if (item == null) {
continue;
}
if (item instanceof Map) {
Map map = (Map) item;
for (int index = 0; index < list2.size(); index++) {
ParameterMapping pm = list2.get(index);
Object value = map.get(pm.getProperty());
statement.setObject(index + 1, value);
}
} else if (item instanceof Long || item instanceof String || item instanceof Integer) {
statement.setObject(1, item);
} else {
for (int index = 0; index < list2.size(); index++) {
ParameterMapping pm = list2.get(index);
// String methodName = StringUtil.hump("get_" + pm.getProperty(), "_");
String methodName = getMethodName(pm.getProperty());
Method method = item.getClass().getMethod(methodName);
Object value = method.invoke(item);
statement.setObject(index + 1, value);
}
}
statement.addBatch();
}
List resultList = new ArrayList();
int[] resultArray = statement.executeBatch();
if (sqlCommandType != SqlCommandType.INSERT) {
for (int intval : resultArray) {
resultList.add(Long.valueOf(intval + ""));
}
} else {
ResultSet resultSet = statement.getGeneratedKeys();
while (resultSet.next()) {
//此处错误,由于rs.next()遍历查询结果时，下标是从“1”开始，而这里是从“0”开始，导致出错
//resultList.add(resultSet.getLong(0));
resultList.add(resultSet.getLong(1));
}
}
Long endTime = System.currentTimeMillis();
log.info("sql耗时：{}", endTime - startTime + "毫秒");
return resultList;
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
}
@Transactional(rollbackFor = Exception.class)
@Override
public long insert(String namespace, String sqlId, T params) {
return update(namespace, sqlId, params);
}
@Transactional(rollbackFor = Exception.class)
@Override
public List insertList(String nameSpace, String id, List list) {
if (list == null || list.size() == 0) {
return new ArrayList<>();
}
return updateList(nameSpace, id, list);
}
@Transactional(rollbackFor = Exception.class)
@Override
public int delete(String namespace, String sqlId, T params) {
return update(namespace, sqlId, params);
}
@Transactional(rollbackFor = Exception.class)
@Override
public List deleteList(String namespace, String sqlId, List list) {
return updateList(namespace, sqlId, list);
}
@Transactional(rollbackFor = Exception.class)
@Override
public void batchALL(String namespace, String sqlId, List list, Integer bathcount) {
Long startTime = System.currentTimeMillis();
List data = new ArrayList<>();
for (int i = 0; i < list.size(); i++) {
data.add(list.get(i));
if (data.size() == bathcount || i == list.size() - 1) {
this.batchUtil(namespace, sqlId, data);
data.clear();
}
}
Long endTime = System.currentTimeMillis();
log.info("batchALL方法sql耗时：{}", endTime - startTime + "毫秒");
}
@Transactional(rollbackFor = Exception.class)
@Override
public void batchExecutor(String namespace, String sqlId, List params) {
batchUpdate(namespace, sqlId, params);
}
private void batchUtil(String namespace, String sqlId, List list) {
try {
if (list == null || list.isEmpty()) {
return;
}
MappedStatement ms = sqlSessionTemplate.getConfiguration().getMappedStatement(namespace + "." + sqlId);
SqlCommandType sqlCommandType = ms.getSqlCommandType();
BoundSql boundSql = ms.getSqlSource().getBoundSql(list.get(0));
String sql = boundSql.getSql();
List list2 = boundSql.getParameterMappings();
Connection connection = sqlSessionTemplate.getSqlSessionFactory().openSession().getConnection();
PreparedStatement statement = null;
if (sqlCommandType == SqlCommandType.INSERT) {
statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
} else {
statement = connection.prepareStatement(sql);
}
sql = sql.replaceAll("
“, “”);
sql = sql.replaceAll(”\t", “”);
sql = sql.replaceAll(“[[ ]]{2,}”, " “);
log.info(”> Preparing：" + sql);
for (T item : list) {
if (item == null) {
continue;
}
StringBuffer values = new StringBuffer();
if (item instanceof Map) {
Map
for (int index = 0; index < list2.size(); index++) {
ParameterMapping pm = list2.get(index);
Object value = map.get(pm.getProperty());
values.append(value).append(“(”).append(value.getClass()).append(“),”);
statement.setObject(index + 1, value);
}
} else if (item instanceof Long || item instanceof String || item instanceof Integer) {
statement.setObject(1, item);
values.append(item).append(“(”).append(StringUtils.substringAfterLast(item.getClass().toString(), “.”)).append(“),”);
} else {
List params = new ArrayList<>();
for (int index = 0; index < list2.size(); index++) {
ParameterMapping pm = list2.get(index);
//String methodName = StringUtil.hump(“get_” + pm.getProperty(), “_”);
String methodName = getMethodName(pm.getProperty());
Method method = item.getClass().getMethod(methodName);
Object value = method.invoke(item);
params.add(value.toString());
statement.setObject(index + 1, value);
values.append(value).append(“(”).append(StringUtils.substringAfterLast(value.getClass().toString(), “.”)).append(“),”);
}
}
statement.addBatch();
values.delete(values.length() - 1, values.length());
log.info("
}
List resultList = new ArrayList<>();
int[] resultArray = statement.executeBatch();
if (sqlCommandType != SqlCommandType.INSERT) {
for (int intval : resultArray) {
resultList.add(Long.valueOf(intval + “”));
}
} else {
ResultSet resultSet = statement.getGeneratedKeys();
while (resultSet.next()) {
try {
resultList.add(resultSet.getLong(1));
} catch (Exception e) {
log.error(“错误：” + e.toString());
}
}
}
return;
} catch (Exception e) {
log.error(“错误：” + e.toString());
throw new RuntimeException(e.toString());
}
}
void batchUpdate(String namespace, String sqlId, List parameterList) {
Long startTime = System.currentTimeMillis();
String statement = namespace + "." + sqlId;
if (parameterList == null || parameterList.size() == 0) {
return;
}
SqlSession sqlSession = null;
try {
sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
for (Object obj : parameterList) {
sqlSession.update(statement, obj);
}
sqlSession.commit();
//清理缓存防止溢出
sqlSession.clearCache();
} catch (Exception e) {
log.error("batch insert error");
if (sqlSession != null) {
sqlSession.rollback();
}
} finally {
if (sqlSession != null) {
sqlSession.close();
Long endTime = System.currentTimeMillis();
log.info("batchExecutor方法sql耗时：{}", endTime - startTime + "毫秒");
}
}
}
@SuppressWarnings("unchecked")
protected void printSql(String id, T params) {
try {
MappedStatement ms = sqlSessionTemplate.getConfiguration().getMappedStatement(id);
BoundSql boundSql = ms.getSqlSource().getBoundSql(params);
String sql = boundSql.getSql();
sql = sql.replaceAll("
“, “”);
sql = sql.replaceAll(”\t", “”);
sql = sql.replaceAll(“[[ ]]{2,}”, " ");
List list2 = boundSql.getParameterMappings();
if (params == null) {
} else if (params instanceof Map) {
Map map = (Map) params;
for (int index = 0; index < list2.size(); index++) {
ParameterMapping pm = list2.get(index);
Object value = map.get(pm.getProperty());
sql = sql.replaceFirst("[?]", value + "");
}
} else if (params instanceof Long || params instanceof String || params instanceof Integer) {
sql = sql.replaceFirst("[?]", params + "");
} else {
for (int index = 0; index < list2.size(); index++) {
ParameterMapping pm = list2.get(index);
//String methodName = StringUtil.hump("get_" + pm.getProperty(), "_");
String methodName = getMethodName(pm.getProperty());
Method method = params.getClass().getMethod(methodName);
Object value = method.invoke(params);
sql = sql.replaceFirst("[?]", value + "");
}
}
log.info(sql);
} catch (Exception e) {
e.printStackTrace();
}
}
private String getMethodName(String fieldName) {
if (StringUtils.isBlank(fieldName)) {
return null;
}
String firstLetter = fieldName.substring(0, 1).toUpperCase();
String methodName = "get" + firstLetter + fieldName.substring(1);
return methodName;
}
}
说明：namespace指的是你的xml的映射路径，不喜欢的可以写成自己的xml所在路径，
sqlid指的是你xml中方法的名字,无论是单个操作还是批量操作，你的xml中的sql都是单个，这里的批量用的并不是mybatis的foreach操作而是通过传进来的集合批量提交事务到数据库‘’
4.NameSpaceEnum
/**
* @author liuminglin
* @date 2021/8/23 16:46
* @description: TODO
*/
public enum NameSpaceEnum {
USER_INFO_MAPPER("com.example.recordlog.mapper.UserInfoMapper");
public String nameSpace;
NameSpaceEnum(String nameSpace) {
this.nameSpace = nameSpace;
}
public String getNameSpace() {
return nameSpace;
}
}
4.mapper接口
@Repository
public interface UserInfoMapper {
int deleteByPrimaryKey(Long id);
Long batchdeleteByPrimaryKey(List list);
int insert(UserInfo record);
int insertSelective(UserInfo record);
UserInfo selectByPrimaryKey(Long id);
UserInfo selectByPrimaryKey();
List selectAll();
int updateByPrimaryKeySelective(UserInfo record);
int updateByPrimaryKey(UserInfo record);
Integer batchUpdateInfo(@Param("userInfoList") List userInfoList);
Integer batchInsert(@Param("userInfoList") List userInfoList);
}
5.sql.xml
id
, user_Id, user_name, phone, hometown, email, address, creat_time, modify_date
delete
from user_info
where id = #{id,jdbcType=BIGINT}
delete
from user_info
where id = #{id,jdbcType=BIGINT}
insert into user_info (id, user_Id, user_name,
phone, hometown, email,
address, creat_time, modify_date)
values (#{id,jdbcType=BIGINT}, #{userId,jdbcType=VARCHAR}, #{userName,jdbcType=VARCHAR},
#{phone,jdbcType=VARCHAR}, #{hometown,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR},
#{address,jdbcType=VARCHAR}, #{creatTime,jdbcType=TIMESTAMP}, #{modifyDate,jdbcType=TIMESTAMP})
insert into user_info
id,
user_Id,
user_name,
phone,
hometown,
email,
address,
creat_time,
modify_date,
#{id,jdbcType=BIGINT},
#{userId,jdbcType=VARCHAR},
#{userName,jdbcType=VARCHAR},
#{phone,jdbcType=VARCHAR},
#{hometown,jdbcType=VARCHAR},
#{email,jdbcType=VARCHAR},
#{address,jdbcType=VARCHAR},
#{creatTime,jdbcType=TIMESTAMP},
#{modifyDate,jdbcType=TIMESTAMP},
update user_info
user_Id = #{userId,jdbcType=Ingeger},
user_name = #{userName,jdbcType=VARCHAR},
phone = #{phone,jdbcType=VARCHAR},
hometown = #{hometown,jdbcType=VARCHAR},
email = #{email,jdbcType=VARCHAR},
address = #{address,jdbcType=VARCHAR},
creat_time = #{creatTime,jdbcType=TIMESTAMP},
modify_date = #{modifyDate,jdbcType=TIMESTAMP},
where id = #{id,jdbcType=BIGINT}
update user_info
set user_Id = #{userId,jdbcType=VARCHAR},
user_name = #{userName,jdbcType=VARCHAR},
phone = #{phone,jdbcType=VARCHAR},
hometown = #{hometown,jdbcType=VARCHAR},
email = #{email,jdbcType=VARCHAR},
address = #{address,jdbcType=VARCHAR},
creat_time = #{creatTime,jdbcType=TIMESTAMP},
modify_date = #{modifyDate,jdbcType=TIMESTAMP}
where id = #{id,jdbcType=BIGINT}
update user_info
user_Id = #{item.userId,jdbcType=VARCHAR},
user_name = #{item.userName,jdbcType=VARCHAR},
where id = #{item.id,jdbcType=BIGINT}
insert into user_info
id,
user_Id,
user_name,
phone,
hometown,
email,
address,
creat_time,
modify_date,
#{id,jdbcType=BIGINT},
#{userId,jdbcType=VARCHAR},
#{userName,jdbcType=VARCHAR},
#{phone,jdbcType=VARCHAR},
#{hometown,jdbcType=VARCHAR},
#{email,jdbcType=VARCHAR},
#{address,jdbcType=VARCHAR},
#{creatTime,jdbcType=TIMESTAMP},
#{modifyDate,jdbcType=TIMESTAMP},
6.使用
import com.example.recordlog.bean.UserInfo;
import com.example.recordlog.constant.NameSpaceEnum;
import com.example.recordlog.service.BaseDao;
import com.example.recordlog.tools.ResponseUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author
* @Date 2021/11/24 10:15
* @Desc BaseDao测试controler
* @Param
* @Return
*/
@RestController
@RequestMapping("/base")
public class BaseDaoController {
@Autowired
private BaseDao baseDao;
@RequestMapping(value = "/insertList", method = {RequestMethod.GET})
public ResponseUtils batchInsertList() {
List userInfoList = new ArrayList<>();
for (int i = 1; i <= 200000; i++) {
UserInfo userInfo = new UserInfo();
userInfo.setId(i);
userInfo.setUserId(String.valueOf(i));
userInfo.setUserName("张三" + String.valueOf(i));
userInfo.setPhone(String.valueOf(i));
userInfo.setHometown(String.valueOf(i));
userInfo.setEmail(String.valueOf(i));
userInfo.setAddress(String.valueOf(i));
userInfo.setCreatTime(new Date());
userInfo.setModifyDate(new Date());
userInfoList.add(userInfo);
}
System.out.println(userInfoList);
baseDao.insertList(NameSpaceEnum.USER_MAPPER, "batchInsert", userInfoList);
ResponseUtils responseUtils = ResponseUtils.success(true);
return responseUtils;
}
@RequestMapping(value = "/batchALL", method = {RequestMethod.GET})
public ResponseUtils batchALL() {
List userInfoList = new ArrayList<>();
for (int i = 1; i <= 200000; i++) {
UserInfo userInfo = new UserInfo();
userInfo.setId(i);
userInfo.setUserId(String.valueOf(i));
userInfo.setUserName("张三" + String.valueOf(i));
userInfo.setPhone(String.valueOf(i));
userInfo.setHometown(String.valueOf(i));
userInfo.setEmail(String.valueOf(i));
userInfo.setAddress(String.valueOf(i));
userInfo.setCreatTime(new Date());
userInfo.setModifyDate(new Date());
userInfoList.add(userInfo);
}
System.out.println(userInfoList);
baseDao.batchALL(NameSpaceEnum.USER_MAPPER, "batchInsert", userInfoList, 5000);
//baseDao.batchALL(NameSpaceEnum.USER_MAPPER, "batchdeleteByPrimaryKey", userInfoList, 5000);
//ResponseUtils responseUtils = ResponseUtils.success(true);
return ResponseUtils.success(true);
}
@RequestMapping(value = "/batchRemoveInfo", method = {RequestMethod.GET})
//@RequestMapping(value = "/batchRemoveInfo", produces = "application/json;charset=utf-8", method = {RequestMethod.GET})
public ResponseUtils batchRemoveInfo() {
List userInfoList = new ArrayList<>();
for (int i = 1; i <= 200000; i++) {
UserInfo userInfo = new UserInfo();
userInfo.setId(i);
userInfoList.add(userInfo);
}
List deleteList = baseDao.deleteList(NameSpaceEnum.USER_MAPPER, "batchdeleteByPrimaryKey", userInfoList);
ResponseUtils responseUtils = ResponseUtils.success(deleteList);
return responseUtils;
}
@RequestMapping(value = "/batchExecutor", method = {RequestMethod.GET})
public ResponseUtils batchExecutor() {
List userInfoList = new ArrayList<>();
for (int i = 1; i <= 200000; i++) {
UserInfo userInfo = new UserInfo();
userInfo.setId(i);
userInfo.setUserId(String.valueOf(i));
userInfo.setUserName("张三" + String.valueOf(i));
userInfo.setPhone(String.valueOf(i));
userInfo.setHometown(String.valueOf(i));
userInfo.setEmail(String.valueOf(i));
userInfo.setAddress(String.valueOf(i));
userInfo.setCreatTime(new Date());
userInfo.setModifyDate(new Date());
userInfoList.add(userInfo);
}
System.out.println(userInfoList);
baseDao.batchExecutor(NameSpaceEnum.USER_MAPPER, "batchInsert", userInfoList);
return ResponseUtils.success(true);
}
}