主要从4个方面来阐述,1:背景;2:思路;3:代码实现;4:使用
一:封装背景,
在做项目的时候,用的JPA ,有些复杂查询,比如报表用原生的JdbcTemplate ,很不方便,传参也不方便,如果参数多;需要增加分页功能,以及结果集自动转对像等5个常用功能,见第4节
下面两个图是开源测试管理软件 itest 的统计功能,因为SQL复杂,有些有200行,所以才有后面的 JdbcTemplateWrapper;可以在这体验这些报表(点测试,然后选择一个项目,然后点度量分析),速度还不错,https://itest.work/rsf/site/itest/product/index.html
上面这图,SQL 就有200行
二:封装实现思路
(1)实现spring RowMapper 接口,直接把jdbc 结果集转 JAVA 对像(用的反射,访问量大的业务不推荐用这方式)
(2)不通过 RowMapper 接口, 把JdbcTemplate 返回的List<Map<String,Object>> 原生JDBC结果集中,字段名,也就是MAP中的KEY,转换为驼峰规则的JAVA对属性名,方便前端组件使用,不再中间转为VO 或 实体类对像后,再返前端;比传统查询(多了一个LIST的遍历 ,基本对性能影响不大)
(3)封装分页
(4) 在 JdbcTemplateWrapper 中包装 NamedParameterJdbcTemplate 解决友好传参的问题,
再封几个常用的查询方法,可变长参数,或是以MAP形式传参数 key 为SQL中的参数名占位符
三:代码实现
两个类,ObjectRowMapper 和 JdbcTemplateWrapper
ObjectRowMapper 代码如下:
package cn.com.mypm.framework.jdbc;
import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.util.StringUtils;
import cn.com.mypm.common.util.CalendaUtilities;
import cn.com.mypm.framework.common.config.PropertiesBean;
/**
*
* <p>标题: RowMapper 接口实现</p>
* <p>业务描述:完成原生结果集到JAVA对像的转换</p>
* <p>公司:itest.work</p>
* <p>版权:itest 2018</p>
* @author itest andy
* @date 2018年6月8日
* @version V1.0
*/
@SuppressWarnings("rawtypes")
public class ObjectRowMapper implements RowMapper {
private Class<?> objectClass;
private String[] columnNames = null;
private Field[] fields ;
///缓存 当前结果集字对段和当前class的 Field的对应关系,
private Map<String ,Field> currQueryFieldMap ;
//缓存当前结果集,字段和 JAVA属性名的对应关系 ,按脱峰规则做的转换
private Map<String ,String> fieldClassMap ;
private Boolean isConvertSwitch = null;
// MYSQL 是否区分大小写的标记,要是区转,要把结果集中,字段名,大写转为小写
private String mysqlLowerCaseTableNames = null;
private static Log logger = LogFactory.getLog(ObjectRowMapper.class);
//缓存某个class 已处理过的字段映射到属性名的关系,避免同一个类每次重新处理,第一个KEY 为类名
private static Map<String, Map<String, String>> dbColumnClassFieldMap = new ConcurrentHashMap<String, Map<String, String>>();
public ObjectRowMapper(Class<?> objectClass) {
this.objectClass = objectClass;
fields = objectClass.getDeclaredFields();
}
public void clean(){
if(currQueryFieldMap!=null){
currQueryFieldMap.clear();
currQueryFieldMap = null;
}
if(fieldClassMap!=null){
fieldClassMap.clear();
fieldClassMap = null;
}
if(fields!=null){
fields = null;
}
if(columnNames!=null){
columnNames = null;
}
}
/**
* 该方法自动将数据库字段对应到Object中相应字段 要求:
* 字段名严格为驼峰形式 == 数据库字段名去掉下划线转为驼峰形式
* 如user_name 转为userName ,如数据库字段名,无下划线
* 就只能把首字母变为大小写后的 set ,get
*/
@Override
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
Object targetObject = null;
try {
targetObject = objectClass.newInstance();
} catch (InstantiationException | IllegalAccessException e) {
logger.error(e.getMessage(), e);
}
if (columnNames == null) {
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
columnNames = new String[columnCount];
for (int i = 0; i < columnCount; i++) {
columnNames[i] = rsmd.getColumnLabel(i + 1);
}
}
isConvertSwitch = true;
if(mysqlLowerCaseTableNames == null){
String lowerCaseNames = PropertiesBean.getInstance().getProperty("conf.mysql.lowerCaseNames");
if(lowerCaseNames==null){
mysqlLowerCaseTableNames = "yes";
}else{
mysqlLowerCaseTableNames = "no";
}
}
if(currQueryFieldMap==null){
currQueryFieldMap = new HashMap<String,Field>(columnNames.length);
for (String columnName : columnNames) {
for (Field field : fields) {
if(isConvertSwitch==null){
if (field.getName().equals(
convertColumnNameToFieldName(columnName))) {
currQueryFieldMap.put(columnName, field);
break;
}
}else{
if(isConvertSwitch){
if(targetObject instanceof CustomRowMapper&&(!((CustomRowMapper)targetObject).isConvert())){
if (field.getName().equals(columnName)) {
currQueryFieldMap.put(columnName, field);
break;
}
}else{
if (field.getName().equals(
convertColumnNameToFieldName(columnName))) {
currQueryFieldMap.put(columnName, field);
break;
}
}
}
}
}
}
}
for (String columnName : columnNames) {
Field field = currQueryFieldMap.get(columnName);
if(field==null){
if(logger.isDebugEnabled()){
logger.debug(objectClass.getName() +"is not property match db columnName:"+columnName );
}
continue;
}
Object value = rs.getObject(columnName);
if (value == null) {
continue;
}
boolean accessFlag = field.isAccessible();
if (!accessFlag) {
field.setAccessible(true);
}
if(fieldClassMap==null){
fieldClassMap = new HashMap<String,String>(columnNames.length);
}
if(fieldClassMap.get(columnName)==null){
fieldClassMap.put(columnName, getFieldClaszName(field));
}
setFieldValue(targetObject, field, rs, columnName,fieldClassMap.get(columnName));
// 恢复相应field的权限
if (!accessFlag) {
field.setAccessible(accessFlag);
}
}
return targetObject;
}
public String convertColumnNameToFieldName(String columnName) {
Map<String, String> fieldMap = dbColumnClassFieldMap.get(objectClass
.getName());
boolean emptyFlg = false;
if (fieldMap == null) {
fieldMap = new HashMap<String, String>();
emptyFlg = true;
}
String classFieldName = fieldMap.get(columnName);
if (classFieldName != null) {
return classFieldName;
}
String columnNameKey = columnName;
//if ("oracle".equals(dbType)||("mysql".equals(dbType)&&"no".equals(mysqlLowerCaseTableNames))) {
columnName = columnName.toLowerCase();
//}
StringBuffer buf = new StringBuffer();
int i = 0;
while ((i = columnName.indexOf('_')) > 0) {
buf.append(columnName.substring(0, i));
columnName = StringUtils.capitalize(columnName.substring(i + 1));
}
buf.append(columnName);
fieldMap.put(columnNameKey, buf.toString());
if (emptyFlg) {
dbColumnClassFieldMap.put(objectClass.getName(), fieldMap);
}
return fieldMap.get(columnNameKey);
}
/**
* 根据类型对具体对象属性赋值
*/
public static void setFieldValue(Object targetObj, Field field,
ResultSet rs, String columnLabel,String fieldClass) {
try {
if ("String".equals(fieldClass)) {
field.set(targetObj, rs.getString(columnLabel));
} else if ("Double".equals(fieldClass)) {
field.set(targetObj, rs.getDouble(columnLabel));
} else if ("Float".equals(fieldClass)) {
field.set(targetObj, rs.getFloat(columnLabel));
} else if ("Integer".equals(fieldClass)) {
field.set(targetObj, rs.getInt(columnLabel));
} else if ("Long".equals(fieldClass)) {
field.set(targetObj, rs.getLong(columnLabel));
} else if ("BigDecimal".equals(fieldClass)) {
field.set(targetObj, rs.getBigDecimal(columnLabel));
} else if ("Date".equals(fieldClass)) {
field.set(targetObj, rs.getDate(columnLabel));
} else if ("Short".equals(fieldClass)) {
field.set(targetObj, rs.getShort(columnLabel));
} else if ("Boolean".equals(fieldClass)) {
field.set(targetObj, rs.getBoolean(columnLabel));
} else if ("Byte".equals(fieldClass)) {
field.set(targetObj, rs.getByte(columnLabel));
} else if ("Timestamp".equals(fieldClass)) {
field.set(targetObj, rs.getTimestamp(columnLabel));
} else if("BigDecimal".equals(fieldClass)) {
field.set(targetObj, rs.getBigDecimal(columnLabel));
}else {
//这里没有实现,如有特殊需要处理的在这里实现
}
} catch (IllegalArgumentException e) {
logger.error(e.getMessage(), e);
} catch (IllegalAccessException e) {
logger.error(e.getMessage(), e);
} catch (SQLException e) {
logger.error(e.getMessage(), e);
}
}
private static String getFieldClaszName(Field field) {
String elemType = field.getType().toString();
if ("class java.lang.String".equals(elemType)
|| elemType.indexOf("char") != -1
|| elemType.indexOf("Character") != -1) {
return "String";
} else if (elemType.indexOf("double") != -1
|| elemType.indexOf("Double") != -1) {
return "Double";
} else if (elemType.indexOf("float") != -1
|| elemType.indexOf("Float") != -1) {
return "Float";
} else if (elemType.indexOf("int") != -1
|| elemType.indexOf("Integer") != -1||elemType.indexOf("BigInteger") != -1) {
return "Integer";
} else if (elemType.indexOf("long") != -1
|| elemType.indexOf("Long") != -1) {
return "Long";
} else if (elemType.indexOf("BigDecimal") != -1) {
return "BigDecimal";
} else if (elemType.indexOf("Date") != -1) {
return "Date";
} else if (elemType.indexOf("short") != -1
|| elemType.indexOf("Short") != -1) {
return "Short";
} else if (elemType.indexOf("boolean") != -1
|| elemType.indexOf("Boolean") != -1) {
return "Boolean";
} else if (elemType.indexOf("byte") != -1
|| elemType.indexOf("Byte") != -1) {
return "Byte";
} else if (elemType.indexOf("Timestamp") != -1) {
return "Timestamp";
}
return "String";
}
JdbcTemplateWrapper
三:使用
数据源和JPA是同一数据源,在同一事务中,用的连接和JPA使用的是同一个连接,在CommonDao 额外注入一个 JdbcTemplateWrapper,当作处理复杂SQL的帮手。
JdbcTemplateWrapper 常用的方法同如下几个:
clasz 把查询结果要转为的类对像,内部再构造 ObjectRowMapper
1:public List queryAllMatchListWithFreePra(String sql, Class clasz, Object... args) // SQL 参数为问号占位符,使用参数个数<=3 个时的情况,可以用要不参数多了,可变长参数多,阅读性差
2:public List queryAllMatchListWithParaMap(String sql, Class clasz, Map paramMap) // SQL 参数,为 参数名占位符 ,如 name=:name, paramMap中 key 为参数名,value 为值数值(用于参数个数多于3时,用参数名占位符的方式,然后用Map 传参) ; 如clasz 传为为空,实际返为 List<Map<String,Object>>,不为空侧为 List<Map<String,clasz>>
3: 以pageModel为载体实现分页
/**
*
* @param pageModel: 设置了查询SQL ,及查询参数 Map paramMap 的分页对像
* @param className
* : 从查询结果集中构建出的类,如为null则pageModel的PageData为List<Map>,
* 不为null则pageModel的PageData为List<className>
* @param columnNameForCount:查询记录数时的字段名,一般用主键
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public void fillPageModelData(PageModel pageModel, Class className)
4 : 不用 pageModel 分页查询
/**
* 多表sql 分页查询,多表连查时,才用这个方法,其他请用commonDao的 SQL分页查询
*
* @param sql
* @param className
* @param paramMap
* @param pageNo
* @param PageSize
* @param columnNameForCount
* @return
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public List queryAllMatchListWithParaMap(String sql, Class clasz, Map paramMap, int pageNo, int pageSize)
5 : 把list map 原生JDBC结果集中,字段名,也就是MAP中的KEY,转换为驼峰规则的JAVA对属性名
/**
* 把list map 原生JDBC结果集中,字段名,也就是MAP中的KEY,转换为驼峰规则的JAVA对属性名
* 方便前端组件使用,不再中间转为VO 或 实体类对像后,再返前端;比传统查询(多了一个LIST的遍历 ,基本对性能影响不大)
* @param resultList :JDBC 结果集
* @return 把MAP中的KEY转换为转换为驼峰规则的JAVA对属性名的LIST<map<驼峰规则的JAVA对属性名形式的KEY,Object>>
* @author itest andy
*/
public void converDbColumnName2ObjectPropName(List<Map<String,Object>> resultList)