之前写过的jdbc工具类有一个朋友用到,不过只是简单的调用,不需要多个数据库,也不需要Redis缓存,并且是JDK6,改了不少东西才OK,现在把这个版本也做个记录。
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import java.lang.reflect.Field;
import java.sql.*;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* JDBC工具类
* 1. 调用getInstance方法即可获得工具类实例,此实例为参数单例,当数据库参数相同时,返回的将会是同一个实例
* 2. 数据库参数改为获取工具实例时传入,当传入的数据库参数已经被创建过时,将不会创建新的实例,而是返回已经创建的实例。
* 3. 如果使用返回泛型类型的方法,请注意 select 后的 column 名称要与对象中的 field 名称对应
* 4. 如果 column 的名称有下划线,但 field 是驼峰命名也没有关系,返回指定类型的方法会自动互转
* 5. 工具类中使用了 Apache.common 依赖包,如果业务中不能添加,可以考虑修改。
* 6. 工具中的connection设为了长连接,当数据库参数 url 没有指定 autoReconnect 为 true 时,构造时将会自动设置该参数,避免数据库长连接超时导致无法访问数据库
*/
public class DataFetcher {
// 日志对象
private static final Log log = LogFactory.getLog(DataFetcher.class);
// 数据库参数
private String url;
private String driver;
private String user;
private String pass;
private Connection conn;
/**
* DataFetcher 实例缓存,避免创建过多的重复对象
*/
private static final Map<String, DataFetcher> INSTANCE_CACHE = new HashMap<String, DataFetcher>();
/**
* 查询类型的缓存
* 一般的项目中,应该不会有很多的model,所以直接定义为成员变量保存在内存中
* 如果有特殊的需求,可以改为用Redis之类的缓存数据库保存
* 如果项目不要求高并发处理,可以改为HashMap实现
*/
private static final Map<Class<?>, Map<String, String>> TYPE_CACHE = new HashMap<Class<?>, Map<String, String>>();
/**
* 匹配下划线的正则模板
*/
private static final Pattern underlinePattern = Pattern.compile("_(\\w)");
/**
* 匹配驼峰的正则模板
*/
private static final Pattern camelPattern = Pattern.compile("[A-Z]");
/**
* 当数据库连接为长连接时,必须设置自动重连属性为 true
*/
private static final String longConn = "autoReconnect=true";
/**
* MySQL数据库分页模板
* 0: 查询SQL
* 1: 开始的数据下标
* 2: 返回的数据条数
*/
private static final String LIMIT_SQL = "{0} LIMIT {1}, {2}";
// 封装私有构造方法
private DataFetcher(String url, String driver, String user, String pass) {
this.url = url;
this.driver = driver;
this.user = user;
this.pass = pass;
init();
}
/**
* 返回工具类实例
*/
public synchronized static DataFetcher getInstance(String url, String driver, String user, String pass) {
String cacheKey = StringUtils.join(url, "_", driver, "_", user);
if (INSTANCE_CACHE.containsKey(cacheKey)) {
return INSTANCE_CACHE.get(cacheKey);
}
try {
DataFetcher dataFetcher = new DataFetcher(url, driver, user, pass);
INSTANCE_CACHE.put(cacheKey, dataFetcher);
return dataFetcher;
} catch (Exception e) {
log.error(e.getMessage(), e);
throw new RuntimeException(e);
}
}
/**
* 应用关闭时销毁连接并清空缓存
*/
public synchronized static void destroyAllInstance() {
log.info("begin destroy dataFetcher -----");
for (String key : INSTANCE_CACHE.keySet()) {
DataFetcher dataFetcher = INSTANCE_CACHE.get(key);
close(dataFetcher.conn);
}
log.info("- destroyed all conn.");
TYPE_CACHE.clear();
INSTANCE_CACHE.clear();
log.info("- clean type cache and instance cache.");
log.info("ended destroy dataFetcher -----");
}
/**
* 加载实例配置
*/
private void init() {
loadDriver();
this.conn = loadConn();
// 给 url 添加必须参数
if (StringUtils.isNotBlank(this.url) && !this.url.contains(longConn)) {
this.url = this.url.contains("?")
? StringUtils.join(this.url, "&", longConn)
: StringUtils.join(this.url, "?", longConn);
}
}
/**
* 加载驱动类
*/
private void loadDriver() {
try {
Class.forName(driver);
log.info(StringUtils.join("JDBC Driver Class load completion:", driver));
} catch (ClassNotFoundException e) {
log.error(StringUtils.join("JDBC Driver Class [", url, "] is not Found!"));
}
}
/**
* 获取数据库连接connection
*/
private Connection getConn() {
try {
synchronized (this) {
if (this.conn == null || this.conn.isClosed()) {
this.conn = loadConn();
}
}
} catch (Exception e) {
// 考虑到并发时使用加号作为字符串拼接方法可能会出现问题
// 而且加号操作容易造成不停创建重复的 string 对象
// 使用了底层 StringBuilder 作为拼接操作的办法
String msg = StringUtils.join("Cannot get url[", url, "] connection!");
throw new RuntimeException(msg, e);
}
return this.conn;
}
/**
* 加载 Connection 实例
*/
private Connection loadConn() {
try {
return DriverManager.getConnection(url, user, pass);
} catch (Exception e) {
throw new RuntimeException("Please check your config!", e);
}
}
/**
* 执行SQL
*
* @param sql 需要执行的sql
* 因为使用PreparedStatement所以需要在创建连接的时候注入SQL
* @param callback 执行sql后的处理逻辑
*/
public <T> T execute(CharSequence sql, SQLCallback<T> callback) {
T result = null;
// TODO connection 不会被关闭
Connection conn = getConn();
PreparedStatement pStmt = null;
try {
pStmt = conn.prepareStatement(sql.toString());
log.info(StringUtils.join("execute sql : ", sql));
if (callback != null)
result = callback.callback(pStmt);
} catch (SQLException e) {
log.error(e.getMessage(), e);
} finally {
close(pStmt);
}
return result;
}
/**
* 执行DML语句
*/
public void executeUpdate(CharSequence sql) {
execute(sql, new SQLCallback<Integer>() {
@Override
public Integer callback(PreparedStatement pStmt) throws SQLException {
int retCount = pStmt.executeUpdate();
SQLWarning warning = pStmt.getWarnings();
log.info(StringUtils.join("[", warning.getSQLState(), "] ", warning.getMessage()));
while ((warning = warning.getNextWarning()) != null) {
log.info(StringUtils.join("[", warning.getSQLState(), "] ", warning.getMessage()));
}
return retCount;
}
});
}
/**
* 获得组函数返回值
* 返回第一列数据的第一个字段的值
*
* @throws RuntimeException 如果返回结果中数据条数为0,或者值不是整数,抛出异常
*/
public Long executeQuerySqlGetCount(CharSequence sql) {
Map<String, Object> map = executeQuerySqlGetFirst(sql);
if (map.isEmpty()) {
throw new RuntimeException("This is no data in the query result!");
}
try {
Object count = map.values().toArray()[0];
return Long.valueOf(count.toString());
} catch (NumberFormatException e) {
throw new RuntimeException("The field is not a number!");
}
}
/**
* 只保留查询结果的第一条数据
*
* @throws RuntimeException 如果查询结果为空,抛出异常
*/
public Map<String, Object> executeQuerySqlGetFirst(CharSequence sql) {
List<Map<String, Object>> data = executeQuerySql(sql);
if (data.isEmpty()) {
throw new RuntimeException("The result of the query is empty!");
}
return data.get(0);
}
/**
* 返回标准结构的分页查询
*/
public List<Map<String, Object>> executeQuerySqlWithLimit(CharSequence sql, int start, int end) {
String limitSql = MessageFormat.format(LIMIT_SQL, sql, start, end);
return executeQuerySql(limitSql);
}
/**
* 返回泛型集合的分页查询
*/
public <T> List<T> executeQuerySqlWithLimit(Class<T> clazz, CharSequence sql, int start, int end) {
String limitSql = MessageFormat.format(LIMIT_SQL, sql, start, end);
return executeQuerySql(clazz, limitSql);
}
/**
* 返回一个标准结构的查询结果
*
* @param sql 查询SQL
* @return 一个map类型的集合
*/
public List<Map<String, Object>> executeQuerySql(CharSequence sql) {
return execute(sql, new SQLCallback<List<Map<String, Object>>>() {
@Override
public List<Map<String, Object>> callback(PreparedStatement pStmt) throws SQLException {
ResultSet rs = null;
try {
rs = pStmt.executeQuery();
int columnCount = rs.getMetaData().getColumnCount();
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(columnCount);
while (rs.next()) {
Map<String, Object> data = new HashMap<String, Object>();
for (int i = 1; i <= columnCount; i++) {
String columnName = rs.getMetaData().getColumnLabel(i);
Object value = rs.getObject(columnName);
data.put(underlineToCamel(columnName), value);
}
list.add(data);
}
return list;
} finally {
close(rs);
}
}
});
}
/**
* 返回一个泛型集合
*
* @param clazz 指定返回集合类型
* @param sql 查询SQL
* <pre>
* example:
* @code
* class Obj {int id; int parentId}
* List<Obj> list = executeQuerySql(Obj.class, "select id, parent_id from table");
* </pre>
*/
public <T> List<T> executeQuerySql(final Class<T> clazz, CharSequence sql) {
return execute(sql, new SQLCallback<List<T>>() {
@Override
public List<T> callback(PreparedStatement pStmt) throws SQLException {
ResultSet rs = null;
List<T> result = new ArrayList<T>();
try {
rs = pStmt.executeQuery();
int columnCount = rs.getMetaData().getColumnCount();
List<T> data = new ArrayList<T>(columnCount);
Map<String, String> fieldMap = getFieldMap(clazz);
while (rs.next()) {
T entity = clazz.newInstance();
for (int i = 1; i <= columnCount; i++) {
String columnName = rs.getMetaData().getColumnLabel(i);
Object value = rs.getObject(columnName);
if (value == null) {
continue;
}
Field field = getField(clazz, fieldMap, columnName);
Class<?> fieldType = field.getType();
// 如果指定的Object与此Class所表示的对象赋值不兼容
if (!fieldType.isInstance(value)) {
// 调用common.beanUtils包转化值为对应类型
value = ConvertUtils.convert(value, fieldType);
}
field.set(entity, value);
}
data.add(entity);
}
result.addAll(data);
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
} finally {
close(rs);
}
return result;
}
});
}
private synchronized static Map<String, String> getFieldMap(Class clazz) {
if (TYPE_CACHE.containsKey(clazz)) {
return TYPE_CACHE.get(clazz);
}
Field[] fields = clazz.getDeclaredFields();
Map<String, String> fieldMap = new HashMap<String, String>();
for (Field field : fields) {
String name = field.getName();
fieldMap.put(camelToUnderline(name), name);
fieldMap.put(name.toLowerCase(), name);
}
TYPE_CACHE.put(clazz, fieldMap);
return fieldMap;
}
private static Field getField(Class clazz, Map<String, String> fieldMap, String columnName) throws NoSuchFieldException {
Field field;
try {
field = clazz.getDeclaredField(columnName);
} catch (NoSuchFieldException e) {
if (!fieldMap.containsKey(columnName)) {
throw new NoSuchFieldException(columnName);
}
field = clazz.getDeclaredField(fieldMap.get(columnName));
}
return field;
}
/**
* 字符串的下划线转驼峰
*/
private static String underlineToCamel(String str) {
StringBuffer sb = new StringBuffer();
Matcher m = underlinePattern.matcher(str);
while (m.find()) {
m.appendReplacement(sb, m.group(1).toUpperCase());
}
m.appendTail(sb);
return sb.toString();
}
/**
* 字符串的驼峰转下划线
*/
private static String camelToUnderline(String str) {
StringBuffer sb = new StringBuffer();
Matcher m = camelPattern.matcher(str);
while (m.find()) {
String replace = StringUtils.join("_", m.group(0).toLowerCase());
m.appendReplacement(sb, replace);
}
m.appendTail(sb);
return sb.toString();
}
private static void close(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private static void close(Statement stmt) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private static void close(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 处理查询结果的实现接口
*/
interface SQLCallback<T> {
T callback(PreparedStatement pStmt) throws SQLException;
}
}
2019.07.04改进:
- 最低支持JDK8
- 添加了下划线命名的数据库字段到驼峰命名的对象属性的互相转换
- 添加了对象映射类型、工具类实例的内存级缓存,同时满足并发。如果在大型项目中,可以考虑将基于
JDK
的内存缓存替换为使用Ehcache
框架。内部实现了LRU
、LFU
、FIFO
等缓存策略,支持内部缓存、磁盘缓存,分布式缓存机制等。
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import java.lang.reflect.Field;
import java.sql.*;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* JDBC工具类
* 1. 调用getInstance方法即可获得工具类实例,此实例为参数单例,当数据库参数相同时,返回的将会是同一个实例
* 2. 数据库参数改为获取工具实例时传入,当传入的数据库参数已经被创建过时,将不会创建新的实例,而是返回已经创建的实例。
* 3. 如果使用返回泛型类型的方法,请注意 select 后的 column 名称要与对象中的 field 名称对应
* 4. 如果 column 的名称有下划线,但 field 是驼峰命名也没有关系,返回指定类型的方法会自动互转
* 5. 工具类中使用了 Apache.common 依赖包,如果业务中不能添加,可以考虑修改。
* 6. 工具中的connection设为了长连接,当数据库参数 url 没有指定 autoReconnect 为 true 时,构造时将会自动设置该参数,避免数据库长连接超时导致无法访问数据库
*/
public class DataFetcher {
// 日志对象
private static final Log log = LogFactory.getLog(DataFetcher.class);
// 数据库参数
private String url;
private String driver;
private String user;
private String pass;
private Connection conn;
/**
* DataFetcher 实例缓存,避免创建过多的重复对象
*/
private static final Map<String, DataFetcher> INSTANCE_CACHE = new HashMap<>();
/**
* 查询类型的缓存
* 一般的项目中,应该不会有很多的model,所以直接定义为成员变量保存在内存中
* 如果有特殊的需求,可以改为用Redis之类的缓存数据库保存
* 如果项目不要求高并发处理,可以改为HashMap实现
*/
private static final Map<Class<?>, Map<String, String>> TYPE_CACHE = new HashMap<>();
/**
* 匹配下划线的正则模板
*/
private static final Pattern underlinePattern = Pattern.compile("_(\\w)");
/**
* 匹配驼峰的正则模板
*/
private static final Pattern camelPattern = Pattern.compile("[A-Z]");
/**
* 当数据库连接为长连接时,必须设置自动重连属性为 true
*/
private static final String longConn = "autoReconnect=true";
/**
* MySQL数据库分页模板
* 0: 查询SQL
* 1: 开始的数据下标
* 2: 返回的数据条数
*/
private static final String LIMIT_SQL = "{0} LIMIT {1}, {2}";
// 封装私有构造方法
private DataFetcher(String url, String driver, String user, String pass) {
this.url = url;
this.driver = driver;
this.user = user;
this.pass = pass;
init();
}
/**
* 返回工具类实例
*/
public synchronized static DataFetcher getInstance(String url, String driver, String user, String pass) {
String cacheKey = StringUtils.join(url, "_", driver, "_", user);
if (INSTANCE_CACHE.containsKey(cacheKey)) {
return INSTANCE_CACHE.get(cacheKey);
}
try {
DataFetcher dataFetcher = new DataFetcher(url, driver, user, pass);
INSTANCE_CACHE.put(cacheKey, dataFetcher);
return dataFetcher;
} catch (Exception e) {
log.error(e.getMessage(), e);
throw new RuntimeException(e);
}
}
/**
* 应用关闭时销毁连接并清空缓存
*/
public synchronized static void destroyAllInstance() {
log.info("begin destroy dataFetcher -----");
for (String key : INSTANCE_CACHE.keySet()) {
DataFetcher dataFetcher = INSTANCE_CACHE.get(key);
try {
if (dataFetcher.conn != null && !dataFetcher.conn.isClosed()) {
dataFetcher.conn.close();
}
log.info("- destroy conn:" + key);
} catch (SQLException e) {
e.printStackTrace();
}
}
log.info("- destroyed all conn.");
TYPE_CACHE.clear();
INSTANCE_CACHE.clear();
log.info("- clean type cache and instance cache.");
log.info("ended destroy dataFetcher -----");
}
/**
* 加载实例配置
*/
private void init() {
loadDriver();
this.conn = loadConn();
// 给 url 添加必须参数
if (StringUtils.isNotBlank(this.url) && !this.url.contains(longConn)) {
this.url = this.url.contains("?")
? StringUtils.join(this.url, "&", longConn)
: StringUtils.join(this.url, "?", longConn);
}
}
/**
* 加载驱动类
*/
private void loadDriver() {
try {
Class.forName(driver);
log.info(StringUtils.join("JDBC Driver Class load completion:", driver));
} catch (ClassNotFoundException e) {
log.error(StringUtils.join("JDBC Driver Class [", url, "] is not Found!"));
}
}
/**
* 获取数据库连接connection
*/
private Connection getConn() {
try {
synchronized (this) {
if (this.conn == null || this.conn.isClosed()) {
this.conn = loadConn();
}
}
} catch (Exception e) {
// 考虑到并发时使用加号作为字符串拼接方法可能会出现问题
// 而且加号操作容易造成不停创建重复的 string 对象
// 使用了底层 StringBuilder 作为拼接操作的办法
String msg = StringUtils.join("Cannot get url[", url, "] connection!");
throw new RuntimeException(msg, e);
}
return this.conn;
}
/**
* 加载 Connection 实例
*/
private Connection loadConn() {
try {
return DriverManager.getConnection(url, user, pass);
} catch (Exception e) {
throw new RuntimeException("Please check your config!", e);
}
}
/**
* 执行SQL
*
* @param sql 需要执行的sql
* 因为使用PreparedStatement所以需要在创建连接的时候注入SQL
* @param callback 执行sql后的处理逻辑
*/
public <T> T execute(CharSequence sql, SQLCallback<T> callback) {
T result = null;
// TODO connection 不会被关闭
Connection conn = getConn();
try (PreparedStatement pStmt = conn.prepareStatement(sql.toString())) {
log.info(StringUtils.join("execute sql : ", sql));
if (callback != null)
result = callback.callback(pStmt);
} catch (SQLException e) {
log.error(e.getMessage(), e);
}
return result;
}
/**
* 执行DML语句
*/
public void executeUpdate(CharSequence sql) {
execute(sql, pStmt -> {
int retCount = pStmt.executeUpdate();
SQLWarning warning = pStmt.getWarnings();
log.info(StringUtils.join("[", warning.getSQLState(), "] ", warning.getMessage()));
while ((warning = warning.getNextWarning()) != null) {
log.info(StringUtils.join("[", warning.getSQLState(), "] ", warning.getMessage()));
}
return retCount;
});
}
/**
* 获得组函数返回值
* 返回第一列数据的第一个字段的值
*
* @throws RuntimeException 如果返回结果中数据条数为0,或者值不是整数,抛出异常
*/
public Long executeQuerySqlGetCount(CharSequence sql) {
Map<String, Object> map = executeQuerySqlGetFirst(sql);
if (map.isEmpty()) {
throw new RuntimeException("This is no data in the query result!");
}
try {
Object count = map.values().toArray()[0];
return Long.valueOf(count.toString());
} catch (NumberFormatException e) {
throw new RuntimeException("The field is not a number!");
}
}
/**
* 只保留查询结果的第一条数据
*
* @throws RuntimeException 如果查询结果为空,抛出异常
*/
public Map<String, Object> executeQuerySqlGetFirst(CharSequence sql) {
List<Map<String, Object>> data = executeQuerySql(sql);
if (data.isEmpty()) {
throw new RuntimeException("The result of the query is empty!");
}
return data.get(0);
}
/**
* 返回标准结构的分页查询
*/
public List<Map<String, Object>> executeQuerySqlWithLimit(CharSequence sql, int start, int end) {
String limitSql = MessageFormat.format(LIMIT_SQL, sql, start, end);
return executeQuerySql(limitSql);
}
/**
* 返回泛型集合的分页查询
*/
public <T> List<T> executeQuerySqlWithLimit(Class<T> clazz, CharSequence sql, int start, int end) {
String limitSql = MessageFormat.format(LIMIT_SQL, sql, start, end);
return executeQuerySql(clazz, limitSql);
}
/**
* 返回一个标准结构的查询结果
*
* @param sql 查询SQL
* @return 一个map类型的集合
*/
public List<Map<String, Object>> executeQuerySql(CharSequence sql) {
return execute(sql, pStmt -> {
try (ResultSet rs = pStmt.executeQuery()) {
int columnCount = rs.getMetaData().getColumnCount();
List<Map<String, Object>> list = new ArrayList<>(columnCount);
while (rs.next()) {
Map<String, Object> data = new HashMap<>();
for (int i = 1; i <= columnCount; i++) {
String columnName = rs.getMetaData().getColumnLabel(i);
Object value = rs.getObject(columnName);
data.put(underlineToCamel(columnName), value);
}
list.add(data);
}
return list;
}
});
}
/**
* 返回一个泛型集合
*
* @param clazz 指定返回集合类型
* @param sql 查询SQL
* <pre>
* example:
* @code
* class Obj {int id; int parentId}
* List<Obj> list = executeQuerySql(Obj.class, "select id, parent_id from table");
* </pre>
*/
public <T> List<T> executeQuerySql(Class<T> clazz, CharSequence sql) {
return execute(sql, pStmt -> {
try (ResultSet rs = pStmt.executeQuery()) {
int columnCount = rs.getMetaData().getColumnCount();
List<T> data = new ArrayList<>(columnCount);
Map<String, String> fieldMap = getFieldMap(clazz);
while (rs.next()) {
T entity = clazz.newInstance();
for (int i = 1; i <= columnCount; i++) {
String columnName = rs.getMetaData().getColumnLabel(i);
Object value = rs.getObject(columnName);
if (value == null) {
continue;
}
Field field = getField(clazz, fieldMap, columnName);
Class<?> fieldType = field.getType();
// 如果指定的Object与此Class所表示的对象赋值不兼容
if (!fieldType.isInstance(value)) {
// 调用common.beanUtils包转化值为对应类型
value = ConvertUtils.convert(value, fieldType);
}
field.set(entity, value);
}
data.add(entity);
}
return data;
} catch (ReflectiveOperationException e) {
e.printStackTrace();
return new ArrayList<>();
}
});
}
private synchronized static Map<String, String> getFieldMap(Class clazz) {
if (TYPE_CACHE.containsKey(clazz)) {
return TYPE_CACHE.get(clazz);
}
Field[] fields = clazz.getDeclaredFields();
Map<String, String> fieldMap = new HashMap<>();
for (Field field : fields) {
String name = field.getName();
fieldMap.put(camelToUnderline(name), name);
fieldMap.put(name.toLowerCase(), name);
}
TYPE_CACHE.put(clazz, fieldMap);
return fieldMap;
}
private static Field getField(Class clazz, Map<String, String> fieldMap, String columnName) throws NoSuchFieldException {
Field field;
try {
field = clazz.getDeclaredField(columnName);
} catch (NoSuchFieldException e) {
if (!fieldMap.containsKey(columnName)) {
throw new NoSuchFieldException(columnName);
}
field = clazz.getDeclaredField(fieldMap.get(columnName));
}
return field;
}
/**
* 字符串的下划线转驼峰
*/
private static String underlineToCamel(String str) {
StringBuffer sb = new StringBuffer();
Matcher m = underlinePattern.matcher(str);
while (m.find()) {
m.appendReplacement(sb, m.group(1).toUpperCase());
}
m.appendTail(sb);
return sb.toString();
}
/**
* 字符串的驼峰转下划线
*/
private static String camelToUnderline(String str) {
StringBuffer sb = new StringBuffer();
Matcher m = camelPattern.matcher(str);
while (m.find()) {
String replace = StringUtils.join("_", m.group(0).toLowerCase());
m.appendReplacement(sb, replace);
}
m.appendTail(sb);
return sb.toString();
}
/**
* 处理查询结果的实现接口
*/
interface SQLCallback<T> {
T callback(PreparedStatement pStmt) throws SQLException;
}
}
以下是调用的方法
实体
public class dict_channel{
private Integer id1;
private String key1;
private String value1;
public Integer getId1() {
return id1;
}
public void setId1(Integer id1) {
this.id1 = id1;
}
public String getKey1() {
return key1;
}
public void setKey1(String key1) {
this.key1 = key1;
}
public String getValue1() {
return value1;
}
public void setValue1(String value1) {
this.value1 = value1;
}
}
数据库表信息
调用方法
import java.util.List;
import java.util.Map;
public class TestDataFetcher {
public static void main(String[] args) {
DataFetcher dataFetcher = DataFetcher.getInstance();
String sql = "select id as id1,`key` as key1,value as value1 from dict_channel";
// 标准结构
List<Map<String, Object>> maps = dataFetcher.executeQuerySql(sql);
for (Map<String, Object> map : maps) {
for (String key : map.keySet()) {
System.out.print("key:" + key + ", value:" + map.get(key));
}
System.out.println();
}
// 指定泛型
List<dict_channel> dictChannelList = dataFetcher.executeQuerySql(dict_channel.class, sql);
for (dict_channel dictChannel : dictChannelList) {
System.out.println("key:" + dictChannel.getKey1() + ", value:" + dictChannel.getValue1());
}
}
}
从调用方法的SQL中可以看到,数据库字段到对象属性是通过字段名映射的,所以在编辑SQL的时候需要格外小心。
以上,通用的一个jdbc工具就完成了,除去Common-BeanUtils.jar需要引入,可以适用于几乎所有场景。当然了,我也只是用了四五个数据库,如果使用过程遇到问题,可以给我留消息。