基于jdbcTemplate的二次操作封装

ゝ一纸荒年。 2022-05-01 12:16 291阅读 0赞

## 前言 ##

在SpringBoot开发时，加入spring-boot-starter-jdbc依赖后，框架会自动注入jdbcTemplate，在使用的地方使用@Autowired注解注入即可使用，jdbcTemplate虽然已经提供了很多方便的api，但是在和实体类结合使用时还是有很多不方便的地方，这里给出一种二次封装的代码

## 实现 ##

首先引入第三方jar包commons-dbutils

<dependency>
 <groupId>commons-dbutils</groupId>
 <artifactId>commons-dbutils</artifactId>
 <version>1.7</version>
 </dependency>
 复制代码

JDBCSupport.java

import com.google.common.base.Preconditions;
 import com.google.common.base.Throwables;
 import com.google.common.collect.Lists;
 import com.zxsoft.spider.exception.ServiceException;
 import org.apache.commons.collections.CollectionUtils;
 import org.apache.commons.dbutils.BasicRowProcessor;
 import org.apache.commons.dbutils.RowProcessor;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.jdbc.core.JdbcTemplate;
 import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
 import org.springframework.stereotype.Repository;
 
 
 @Repository
 public class JDBCSupport {
 private static final Logger log = LoggerFactory.getLogger(JDBCDao.class);
 
 @Autowired
 private JdbcTemplate jdbcTemplate;
 
 private RowProcessor processor = new BasicRowProcessor();
 
 /** * 方法名:select * 描述:查询记录. * @param clazz 转换的实体类 * @param table 表名 * @param column 列名 * @param valueMap where条件，适用于key=value的查询 */
 public <T> List<T> select(String table, String column, Map<String, Object> valueMap, Class<T> clazz) {
 StringBuilder sql = new StringBuilder("select ");
 sql.append(column).append(" from ").append(table).append(" where 1=1 ");
 if (null != valueMap) {
 Iterator it = valueMap.entrySet().iterator();
 while (it.hasNext()) {
 Map.Entry pair = (Map.Entry) it.next();
 sql.append(" and ").append(pair.getKey()).append(" = '").append(pair.getValue()).append("'");
 }
 }
 log.debug("select sql ==" + sql.toString());
 List<T> list = null;
 try {
 list = jdbcTemplate.query(sql.toString(), (rs, rowNum) -> processor.toBean(rs, clazz));
 } catch (ServiceException e) {
 log.error(Throwables.getStackTraceAsString(e));
 }
 return list == null ? Lists.newArrayList() : list;
 }
 
 /** * 方法名:select * 描述:查询记录. * @param clazz 转换的实体类 * @param sql 原生查询sql */
 public <T> List<T> select(String sql, Class<T> clazz) {
 List<T> list = null;
 try {
 list = jdbcTemplate.query(sql, (rs, rowNum) -> processor.toBean(rs, clazz));
 } catch (ServiceException e) {
 log.error(Throwables.getStackTraceAsString(e));
 }
 return list == null ? Lists.newArrayList() : list;
 }
 
 /** * 方法名:select * 描述:查询记录. * @param sql 原生查询sql * @return 返回map结构的list */
 public List<Map<String, Object>> select(String sql) {
 List<Map<String, Object>> list = null;
 try {
 list = jdbcTemplate.queryForList(sql);
 } catch (ServiceException e) {
 log.error(Throwables.getStackTraceAsString(e));
 }
 return list == null ? Lists.newArrayList() : list;
 }
 
 public List<Map<String, Object>> select(String sql, Object[] args) {
 List<Map<String, Object>> list = null;
 try {
 list = jdbcTemplate.queryForList(sql, args);
 } catch (ServiceException e) {
 log.error(Throwables.getStackTraceAsString(e));
 }
 return list == null ? Lists.newArrayList() : list;
 }
 
 public int queryCount(String table, Map<String, Object> valueMap) {
 StringBuilder sql = new StringBuilder("select count(1) as count from ");
 sql.append(table);
 sql.append(" where 1=1");
 Object[] bindArgs = new Object[valueMap.size()];
 int i = 0;
 Iterator it = valueMap.entrySet().iterator();
 while (it.hasNext()) {
 Map.Entry pair = (Map.Entry) it.next();
 sql.append(" and ").append(pair.getKey()).append(" = ?");
 bindArgs[i] = pair.getValue();
 i++;
 }
 log.debug("sql ==== " + sql.toString() + " " + Arrays.toString(bindArgs));
 return jdbcTemplate.queryForObject(sql.toString(), bindArgs, Integer.class);
 }
 
 /** * 根据表名，字段插入 * * @param tableName * @param valueMap * @return */
 public int insert(String tableName, Map<String, Object> valueMap) {
 Preconditions.checkNotNull(tableName, "table name can not be empty");
 Set<String> keySet = valueMap.keySet();
 Iterator<String> iterator = keySet.iterator();
 StringBuilder columnSql = new StringBuilder();
 StringBuilder unknownMarkSql = new StringBuilder();
 Object[] bindArgs = new Object[valueMap.size()];
 int i = 0;
 while (iterator.hasNext()) {
 String key = iterator.next();
 columnSql.append(i == 0 ? "" : ",");
 columnSql.append(key);
 unknownMarkSql.append(i == 0 ? "" : ",");
 unknownMarkSql.append("?");
 bindArgs[i] = valueMap.get(key);
 i++;
 }
 StringBuilder sql = new StringBuilder();
 sql.append("INSERT INTO ");
 sql.append(tableName);
 sql.append(" (");
 sql.append(columnSql);
 sql.append(" ) VALUES (");
 sql.append(unknownMarkSql);
 sql.append(" )");
 log.debug("insert sql ==== " + sql.toString() + " " + Arrays.toString(bindArgs));
 return jdbcTemplate.update(sql.toString(), bindArgs);
 }
 
 /** * 批量插入 * * @param list * @param tableName * @return * @throws Exception */
 public int[] batchInsert(String tableName, List<Map<String, Object>> list) throws Exception {
 if (CollectionUtils.isEmpty(list)) {
 return new int[1];
 }
 Map<String, Object>[] maps = new Map[list.size()];
 for(int i = 0;i< list.size(); i++) {
 maps[i] = list.get(i);
 }
 SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
 return simpleJdbcInsert.withTableName(tableName).executeBatch(maps);
 }
 
 /** * 根据表名，字段更新 * * @param tableName * @param valueMap * @return */
 public int update(String tableName, Map<String, Object> valueMap) {
 if (valueMap.size() < 1) {
 return -1;
 }
 Set<String> keySet = valueMap.keySet();
 Iterator<String> iterator = keySet.iterator();
 int i = 0;
 StringBuilder sql = new StringBuilder();
 sql.append("UPDATE ");
 sql.append(tableName);
 sql.append(" SET ");
 Object[] bindArgs = new Object[valueMap.size() - 1];
 StringBuilder columnSql = new StringBuilder();
 while (iterator.hasNext()) {
 String key = iterator.next();
 if ("id".equals(key.toLowerCase())) {
 continue;
 }
 columnSql.append(i == 0 ? "" : ",");
 columnSql.append(key + " = ?");
 bindArgs[i] = valueMap.get(key);
 i++;
 }
 sql.append(columnSql).append(" WHERE ID = " + valueMap.get("id"));
 log.debug("update sql ==== " + sql.toString() + " " + Arrays.toString(bindArgs));
 return jdbcTemplate.update(sql.toString(), bindArgs);
 }
 
 /** * 方法名:executeUpdate * 描述:可以执行新增，修改，删除. * @param sql * @param bindArgs * @return */
 public int update(String sql, Object[] bindArgs) throws ServiceException {
 return jdbcTemplate.update(sql, bindArgs);
 }
 
 }
 复制代码

转载于:https://juejin.im/post/5caaa93f6fb9a05e643e02cd