前言
在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<br>
* 描述:查询记录. <br>
* @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<br>
* 描述:查询记录. <br>
* @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<br>
* 描述:查询记录. <br>
* @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<br>
* 描述:可以执行新增，修改，删除. <br>
* @param sql
* @param bindArgs
* @return
*/
public int update(String sql, Object[] bindArgs) throws ServiceException {
return jdbcTemplate.update(sql, bindArgs);
}
}
