导读：
主要从4个方面来阐述，1：背景；2：思路；3：代码实现；4：使用
一：封装背景，
在做项目的时候，用的JPA ，有些复杂查询，比如报表用原生的JdbcTemplate ，很不方便；需要增加分页功能，以及结果集自动转对像等5个常用功能，见第4节。
下面两个图是开测试测试管理软件 itest 的统计功能，因为SQL复杂，有些有200行，所以才有后面的 JdbcTemplateWrapper,、
二：封装实现思路
（1）实现spring RowMapper 接口，直接把jdbc 结果集转 JAVA 对像(用的反射，访问量大的业务不推荐用这方式)
（2）不通过 RowMapper 接口, 把JdbcTemplate 返回的List<Map<String,Object>> 原生JDBC结果集中，字段名，也就是MAP中的KEY，转换为驼峰规则的JAVA对属性名,方便前端组件使用，不再中间转为VO 或 实体类对像后，再返前端;比传统查询（多了一个LIST的遍历 ，基本对性能影响不大）
（3）封装分页
三：代码实现
两个类，ObjectRowMapper 和 JdbcTemplateWrapper
ObjectRowMapper 代码如下：
1 package cn.com.mypm.framework.jdbc;
2
3 import java.lang.reflect.Field;
4 import java.sql.ResultSet;
5 import java.sql.ResultSetMetaData;
6 import java.sql.SQLException;
7 import java.util.HashMap;
8 import java.util.Map;
9 import java.util.concurrent.ConcurrentHashMap;
10
11 import org.apache.commons.logging.Log;
12 import org.apache.commons.logging.LogFactory;
13 import org.springframework.jdbc.core.RowMapper;
14 import org.springframework.util.StringUtils;
15
16 import cn.com.mypm.common.util.CalendaUtilities;
17 import cn.com.mypm.framework.common.config.PropertiesBean;
18
19
20
21 /**
22 *
23 * <p>标题: RowMapper 接口实现</p>
24 * <p>业务描述:完成原生结果集到JAVA对像的转换</p>
25 * <p>公司:itest.work</p>
26 * <p>版权:itest 2018</p>
27 * @author itest andy
28 * @date 2018年6月8日
29 * @version V1.0
30 */
31 @SuppressWarnings("rawtypes")
32 public class ObjectRowMapper implements RowMapper {
33
34
35 private Class<?> objectClass;
36 private String[] columnNames = null;
37 private Field[] fields ;
38 ///缓存 当前结果集字对段和当前class的 Field的对应关系，
39 private Map<String ,Field> currQueryFieldMap ;
40 //缓存当前结果集，字段和 JAVA属性名的对应关系 ，按脱峰规则做的转换
41 private Map<String ,String> fieldClassMap ;
42 private Boolean isConvertSwitch = null;
43 // MYSQL 是否区分大小写的标记，要是区转，要把结果集中，字段名，大写转为小写
44 private String mysqlLowerCaseTableNames = null;
45 private static Log logger = LogFactory.getLog(ObjectRowMapper.class);
46 //缓存某个class 已处理过的字段映射到属性名的关系，避免同一个类每次重新处理，第一个KEY 为类名
47 private static Map<String, Map<String, String>> dbColumnClassFieldMap = new ConcurrentHashMap<String, Map<String, String>>();
48
49 public ObjectRowMapper(Class<?> objectClass) {
50 this.objectClass = objectClass;
51 fields = objectClass.getDeclaredFields();
52 }
53
54 public void clean(){
55 if(currQueryFieldMap!=null){
56 currQueryFieldMap.clear();
57 currQueryFieldMap = null;
58 }
59 if(fieldClassMap!=null){
60 fieldClassMap.clear();
61 fieldClassMap = null;
62 }
63 if(fields!=null){
64 fields = null;
65 }
66 if(columnNames!=null){
67 columnNames = null;
68 }
69 }
70
71 /**
72 * 该方法自动将数据库字段对应到Object中相应字段 要求：
73 * 字段名严格为驼峰形式 == 数据库字段名去掉下划线转为驼峰形式
74 * 如user_name 转为userName ，如数据库字段名，无下划线
75 * 就只能把首字母变为大小写后的 set ,get
76 */
77 @Override
78 public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
79
80 Object targetObject = null;
81 try {
82 targetObject = objectClass.newInstance();
83 } catch (InstantiationException | IllegalAccessException e) {
84 logger.error(e.getMessage(), e);
85 }
86 if (columnNames == null) {
87 ResultSetMetaData rsmd = rs.getMetaData();
88 int columnCount = rsmd.getColumnCount();
89 columnNames = new String[columnCount];
90 for (int i = 0; i < columnCount; i++) {
91 columnNames[i] = rsmd.getColumnLabel(i + 1);
92 }
93 }
94 isConvertSwitch = true;
95
96 if(mysqlLowerCaseTableNames == null){
97 String lowerCaseNames = PropertiesBean.getInstance().getProperty("conf.mysql.lowerCaseNames");
98 if(lowerCaseNames==null){
99 mysqlLowerCaseTableNames = "yes";
100 }else{
101 mysqlLowerCaseTableNames = "no";
102 }
103 }
104 if(currQueryFieldMap==null){
105 currQueryFieldMap = new HashMap<String,Field>(columnNames.length);
106 for (String columnName : columnNames) {
107 for (Field field : fields) {
108 if(isConvertSwitch==null){
109 if (field.getName().equals(
110 convertColumnNameToFieldName(columnName))) {
111 currQueryFieldMap.put(columnName, field);
112 break;
113 }
114 }else{
115 if(isConvertSwitch){
116 if(targetObject instanceof CustomRowMapper&&(!((CustomRowMapper)targetObject).isConvert())){
117 if (field.getName().equals(columnName)) {
118 currQueryFieldMap.put(columnName, field);
119 break;
120 }
121 }else{
122 if (field.getName().equals(
123 convertColumnNameToFieldName(columnName))) {
124 currQueryFieldMap.put(columnName, field);
125 break;
126 }
127 }
128 }
129
130 }
131
132 }
133 }
134 }
135 for (String columnName : columnNames) {
136 Field field = currQueryFieldMap.get(columnName);
137 if(field==null){
138 if(logger.isDebugEnabled()){
139 logger.debug(objectClass.getName() +"is not property match db columnName:"+columnName );
140 }
141 continue;
142 }
143 Object value = rs.getObject(columnName);
144 if (value == null) {
145 continue;
146 }
147 boolean accessFlag = field.isAccessible();
148 if (!accessFlag) {
149 field.setAccessible(true);
150 }
151 if(fieldClassMap==null){
152 fieldClassMap = new HashMap<String,String>(columnNames.length);
153 }
154 if(fieldClassMap.get(columnName)==null){
155 fieldClassMap.put(columnName, getFieldClaszName(field));
156 }
157 setFieldValue(targetObject, field, rs, columnName,fieldClassMap.get(columnName));
158 // 恢复相应field的权限
159 if (!accessFlag) {
160 field.setAccessible(accessFlag);
161 }
162 }
163 return targetObject;
164 }
165
166
167 public String convertColumnNameToFieldName(String columnName) {
168
169 Map<String, String> fieldMap = dbColumnClassFieldMap.get(objectClass
170 .getName());
171 boolean emptyFlg = false;
172 if (fieldMap == null) {
173 fieldMap = new HashMap<String, String>();
174 emptyFlg = true;
175 }
176
177 String classFieldName = fieldMap.get(columnName);
178 if (classFieldName != null) {
179 return classFieldName;
180 }
181 String columnNameKey = columnName;
182
183 //if ("oracle".equals(dbType)||("mysql".equals(dbType)&&"no".equals(mysqlLowerCaseTableNames))) {
184 columnName = columnName.toLowerCase();
185 //}
186
187 StringBuffer buf = new StringBuffer();
188 int i = 0;
189 while ((i = columnName.indexOf('_')) > 0) {
190 buf.append(columnName.substring(0, i));
191 columnName = StringUtils.capitalize(columnName.substring(i + 1));
192 }
193 buf.append(columnName);
194 fieldMap.put(columnNameKey, buf.toString());
195 if (emptyFlg) {
196 dbColumnClassFieldMap.put(objectClass.getName(), fieldMap);
197 }
198 return fieldMap.get(columnNameKey);
199 }
200
201 /**
202 * 根据类型对具体对象属性赋值
203 */
204 public static void setFieldValue(Object targetObj, Field field,
205 ResultSet rs, String columnLabel,String fieldClass) {
206
207 try {
208 if ("String".equals(fieldClass)) {
209 field.set(targetObj, rs.getString(columnLabel));
210 } else if ("Double".equals(fieldClass)) {
211 field.set(targetObj, rs.getDouble(columnLabel));
212 } else if ("Float".equals(fieldClass)) {
213 field.set(targetObj, rs.getFloat(columnLabel));
214 } else if ("Integer".equals(fieldClass)) {
215 field.set(targetObj, rs.getInt(columnLabel));
216 } else if ("Long".equals(fieldClass)) {
217 field.set(targetObj, rs.getLong(columnLabel));
218 } else if ("BigDecimal".equals(fieldClass)) {
219 field.set(targetObj, rs.getBigDecimal(columnLabel));
220 } else if ("Date".equals(fieldClass)) {
221 field.set(targetObj, rs.getDate(columnLabel));
222 } else if ("Short".equals(fieldClass)) {
223 field.set(targetObj, rs.getShort(columnLabel));
224 } else if ("Boolean".equals(fieldClass)) {
225 field.set(targetObj, rs.getBoolean(columnLabel));
226 } else if ("Byte".equals(fieldClass)) {
227 field.set(targetObj, rs.getByte(columnLabel));
228 } else if ("Timestamp".equals(fieldClass)) {
229 field.set(targetObj, rs.getTimestamp(columnLabel));
230 } else if("BigDecimal".equals(fieldClass)) {
231 field.set(targetObj, rs.getBigDecimal(columnLabel));
232 }else {
233 //这里没有实现，如有特殊需要处理的在这里实现
234 }
235
236 } catch (IllegalArgumentException e) {
237 logger.error(e.getMessage(), e);
238 } catch (IllegalAccessException e) {
239 logger.error(e.getMessage(), e);
240 } catch (SQLException e) {
241 logger.error(e.getMessage(), e);
242 }
243 }
244
245 private static String getFieldClaszName(Field field) {
246
247 String elemType = field.getType().toString();
248 if ("class java.lang.String".equals(elemType)
249 || elemType.indexOf("char") != -1
250 || elemType.indexOf("Character") != -1) {
251 return "String";
252 } else if (elemType.indexOf("double") != -1
253 || elemType.indexOf("Double") != -1) {
254 return "Double";
255 } else if (elemType.indexOf("float") != -1
256 || elemType.indexOf("Float") != -1) {
257 return "Float";
258 } else if (elemType.indexOf("int") != -1
259 || elemType.indexOf("Integer") != -1||elemType.indexOf("BigInteger") != -1) {
260 return "Integer";
261 } else if (elemType.indexOf("long") != -1
262 || elemType.indexOf("Long") != -1) {
263 return "Long";
264 } else if (elemType.indexOf("BigDecimal") != -1) {
265 return "BigDecimal";
266 } else if (elemType.indexOf("Date") != -1) {
267 return "Date";
268 } else if (elemType.indexOf("short") != -1
269 || elemType.indexOf("Short") != -1) {
270 return "Short";
271 } else if (elemType.indexOf("boolean") != -1
272 || elemType.indexOf("Boolean") != -1) {
273 return "Boolean";
274 } else if (elemType.indexOf("byte") != -1
275 || elemType.indexOf("Byte") != -1) {
276 return "Byte";
277 } else if (elemType.indexOf("Timestamp") != -1) {
278 return "Timestamp";
279 }
280
281 return "String";
282
283 }
JdbcTemplateWrapper
package cn.com.mypm.framework.jdbc;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.util.Assert;
import cn.com.mypm.common.dto.PageModel;
import cn.com.mypm.framework.exception.DataBaseException;
/**
*
* <p>标题: JdbcTemplate 包装器</p>
* <p>业务描述:实现分页，原生结果集转对像，原生结果集字段名转脱峰java 属性名</p>
* <p>公司:itest.work</p>
* <p>版权:itest 2018</p>
* @author itest andy
* @date 2018年6月8日
* @version V1.0
*/
public class JdbcTemplateWrapper {
private static Log logger = LogFactory.getLog(JdbcTemplateWrapper.class);
private JdbcTemplate jdbcTemplate;
private String dbType = "mysql";
private String showSql = "false";
protected NamedParameterJdbcTemplate namedParameterJdbcTemplate;
private String mysqlLowerCaseTableNames = null;
public JdbcTemplateWrapper() {
super();
}
public JdbcTemplateWrapper(JdbcTemplate jdbcTemplate) {
super();
this.jdbcTemplate = jdbcTemplate;
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
}
public JdbcTemplateWrapper(JdbcTemplate jdbcTemplate, String dbType, String showSql) {
super();
this.jdbcTemplate = jdbcTemplate;
this.dbType = dbType;
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
this.showSql = showSql;
}
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
/**
* 查询所有匹配的列表
*
* @param sql
* 查询sql
* @param className
* 对象类型
* @param args
* 查询参数
* @return
* @author itest andy
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public List queryAllMatchList(String sql, Class clasz, Object[] args) {
Assert.notNull(clasz, "clasz must not be null");
List dataList = null;
RowMapper rowMapper = new ObjectRowMapper(clasz);
dataList = getJdbcTemplate().query(sql, args, rowMapper);
((ObjectRowMapper) rowMapper).clean();
rowMapper = null;
return dataList;
}
/**
* 查询所有匹配的列表
*
* @param sql
* 查询sql
* @param className
* 对象类型
* @param args
* 查询参数
* @return
* @author itest andy
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public List queryAllMatchListWithFreePra(String sql, Class clasz, Object... args) {
Assert.notNull(clasz, "clasz must not be null");
List dataList = null;
RowMapper rowMapper = new ObjectRowMapper(clasz);
dataList = getJdbcTemplate().query(sql, args, rowMapper);
((ObjectRowMapper) rowMapper).clean();
rowMapper = null;
return dataList;
}
/**
* 把list map 原生JDBC结果集中，字段名，也就是MAP中的KEY，转换为驼峰规则的JAVA对属性名
*
* @param resultList ：JDBC 结果集
* @return 把MAP中的KEY转换为转换为驼峰规则的JAVA对属性名的LIST<map<驼峰规则的JAVA对属性名形式的KEY,Object>>
* @author itest andy
*/
public void converDbColumnName2ObjectPropName(List<Map<String,Object>> resultList) {
if(resultList!=null&&!resultList.isEmpty()) {
List<Map<String,Object>> convertList= new ArrayList<Map<String,Object>>(resultList.size());
//用于缓存字段名到属性名的映射，第二条记录时就不再处理字段名到属性名的转换，提升性能
Map<String,String> ColumnNamePropNameMap = null;
if(resultList.size()>1) {
ColumnNamePropNameMap = new HashMap<String,String>();
}
for(Map<String,Object> currMap :resultList) {
if(currMap!=null&&!currMap.isEmpty()) {
Iterator<Entry<String, Object>> currentIt = currMap.entrySet().iterator();
Map tempMap = new HashMap<String,Object>();
convertList.add(tempMap);
while(currentIt.hasNext()) {
Map.Entry<String,Object> me= currentIt.next();
String dbColumnName = me.getKey();
Object value = me.getValue();
if(resultList.size()>1) {
if(ColumnNamePropNameMap.get(dbColumnName)==null) {
String currProName = convertColumnName2OFieldName(dbColumnName);
tempMap.put(currProName, value);
//缓存起来，第二条记录时就不再处理字段名到属性名的转换，提升性能
ColumnNamePropNameMap.put(dbColumnName, currProName);
}else {
tempMap.put(ColumnNamePropNameMap.get(dbColumnName), value);
}
}else {
tempMap.put(convertColumnName2OFieldName(dbColumnName), value);
}
}
}
}
resultList.clear();
for(Map<String,Object> currMap:convertList) {
resultList.add(currMap);
}
convertList.clear();
convertList = null;
}
}
public String convertColumnName2OFieldName(String columnName ) {
if(mysqlLowerCaseTableNames == null){
String lowerCaseNames = PropertiesBean.getInstance().getProperty("conf.mysql.lowerCaseNames");
if(lowerCaseNames==null){
mysqlLowerCaseTableNames = "yes";
}else{
mysqlLowerCaseTableNames = "no";
}
}
if ("oracle".equals(dbType)||("mysql".equals(dbType)&&"no".equals(mysqlLowerCaseTableNames))) {
columnName = columnName.toLowerCase();
}
if("true".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);
return buf.toString();
}
/**
* 查询所有匹配的列表
*
* @param sql
* 查询sql
* @param className
* 对象类型
* @param paramMap
* 查询参数
* @return
* @author itest andy
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public List queryAllMatchListWithParaMap(String sql, Class clasz, Map paramMap) {
if (paramMap != null && paramMap.isEmpty()) {
paramMap = null;
}
if ("true".equals(showSql)) {
try {
logger.info(getSqlFromQueryData(sql, paramMap));
} catch (Exception e) {
logger.error(e.getMessage(), e);
}
}
List resultList = null;
if (clasz == null) {
resultList = namedParameterJdbcTemplate.queryForList(sql, paramMap);
} else {
RowMapper rowMapper = new ObjectRowMapper(clasz);
resultList = namedParameterJdbcTemplate.query(sql, paramMap, rowMapper);
((ObjectRowMapper) rowMapper).clean();
rowMapper = null;
}
return resultList;
}
/**
*
* @param pageModel:
* @param className
* : 从查询结果集中构建出的类，如为null则pageModel的PageData为List<Map>，
* 不为null则pageModel的PageData为List<className>
* @param columnNameForCount:查询记录数时的字段名，一般用主键
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public void fillPageModelData(PageModel pageModel, Class className, String columnNameForCount) {
if (pageModel.getHqlParamMap() != null && pageModel.getHqlParamMap().isEmpty()) {
pageModel.setHqlParamMap(null);
}
if (pageModel.getTotal() == 0) {
int totalRows = this.getResultCountWithValuesMap(pageModel.getQueryHql(), columnNameForCount,
pageModel.getHqlParamMap());
pageModel.setTotal(totalRows);
}
if (pageModel.getTotal() == 0) {
pageModel.setRows(new ArrayList());
return;
}
if (pageModel.getPageNo() > 1) {
int pageCount = this.getValidPage(pageModel.getPageNo(), pageModel.getTotal(), pageModel.getPageSize());
if (pageCount < pageModel.getPageNo()) {
pageModel.setPageNo(pageCount);
}
}
int startRow = getStartOfPage(pageModel.getPageNo(), pageModel.getPageSize());
String sql = this.buildPageSql(pageModel.getQueryHql(), startRow, pageModel.getPageSize());
if ("true".equals(showSql)) {
try {
logger.info(getSqlFromQueryData(sql, pageModel.getHqlParamMap()));
} catch (Exception e) {
logger.error(e.getMessage(), e);
}
}
List dataList = null;
if (className == null) {
dataList = namedParameterJdbcTemplate.queryForList(sql, pageModel.getHqlParamMap());
} else {
RowMapper rowMapper = new ObjectRowMapper(className);
dataList = namedParameterJdbcTemplate.query(sql, pageModel.getHqlParamMap(), rowMapper);
((ObjectRowMapper) rowMapper).clean();
rowMapper = null;
}
pageModel.setRows(dataList);
}
/**
* 多表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,
String columnNameForCount) {
if (paramMap != null && paramMap.isEmpty()) {
paramMap = null;
}
if ("true".equals(showSql)) {
try {
logger.info(getSqlFromQueryData(sql, paramMap));
} catch (Exception e) {
logger.error(e.getMessage(), e);
}
}
// int totalRows = this.getResultCountWithValuesMap(sql,
// columnNameForCount, paramMap);
// if(pageNo>1){
// int pageCount = this.getValidPage(pageNo, totalRows, pageSize);
// }
List resultList = null;
int startRow = getStartOfPage(pageNo, pageSize);
if (clasz == null) {
resultList = namedParameterJdbcTemplate.queryForList(this.buildPageSql(sql, startRow, pageSize), paramMap);
} else {
RowMapper rowMapper = new ObjectRowMapper(clasz);
resultList = namedParameterJdbcTemplate.query(this.buildPageSql(sql, startRow, pageSize), paramMap,
rowMapper);
rowMapper = null;
}
return resultList;
}
private String buildPageSql(String sql, int startRow, int pageSize) {
if ("oracle".equals(this.getDbType())) {
return this.buildOraclePageSql(sql, startRow, pageSize);
} else if ("mysql".equals(this.getDbType())) {
return this.buildMysqlPageSql(sql, startRow, pageSize);
} else if ("informix".equals(this.getDbType())) {
return this.buildInformixPageSql(sql, startRow, pageSize);
}
throw new DataBaseException("don't support db type,please confirm db is oracle or mysql or informix");
}
private String buildOraclePageSql(String sql, int startRow, int pageSize) {
StringBuilder pageSql = new StringBuilder("SELECT * FROM ");
pageSql.append(" ( ");
pageSql.append(" SELECT pageDataTable.*, ROWNUM RNV ");
pageSql.append(" FROM (" + sql + " ) pageDataTable ");
pageSql.append(" WHERE ROWNUM <= " + (startRow + pageSize));
pageSql.append(" ) WHERE RNV >= " + (startRow + 1));
return pageSql.toString();
}
private String buildMysqlPageSql(String sql, int startRow, int pageSize) {
sql = sql + " limit " + startRow + ", " + pageSize;
return sql;
}
private String buildInformixPageSql(String sql, int startRow, int pageSize) {
sql = sql.trim();
if (sql.startsWith("select")) {
sql = sql.replaceFirst("select", " select skip " + startRow + " first " + pageSize + " ");
} else {
sql = sql.replaceFirst("SELECT", " select skip " + startRow + " first " + pageSize + " ");
}
return sql;
}
private Integer getValidPage(Integer pageNo, int totalRows, Integer pageSize) {
if (!isValidPage(pageNo, totalRows, pageSize)) {
return getValidPage(--pageNo, totalRows, pageSize);
}
int pageCount = (totalRows + (pageSize - (totalRows % pageSize == 0 ? pageSize : totalRows % pageSize)))
/ pageSize;
return pageCount;
}
private static int getStartOfPage(int pageNo, int pageSize) {
if(pageNo==0){
pageNo=1;
}
return (pageNo - 1) * pageSize;
}
private boolean isValidPage(Integer pageNo, Integer totalRows, Integer pageSize) {
if (pageNo == 1) {
return true;
}
int rowStart = (pageNo - 1) * pageSize;
int rowEnd = rowStart + pageSize;
if (rowEnd > totalRows) {
rowEnd = totalRows;
}
return rowEnd > rowStart;
}
/**
* 查询记录数
*
* @param sql
* @param columnNameForCount
* @param praValuesMap
* @return
*/
// @SuppressWarnings("deprecation")
public int getResultCountWithValuesMap(String sql, String columnNameForCount, Map<String, Object> praValuesMap) {
if (praValuesMap != null && praValuesMap.isEmpty()) {
praValuesMap = null;
}
String countQuerySql = null;
countQuerySql = " select count(*) from ( " + sql + " ) V_TABLE";
return namedParameterJdbcTemplate.queryForInt(countQuerySql, praValuesMap);
}
public int queryForIntWithpraValuesMap(String countQuerySql, Map<String, Object> praValuesMap) {
if (praValuesMap != null && praValuesMap.isEmpty()) {
praValuesMap = null;
}
return namedParameterJdbcTemplate.queryForInt(countQuerySql, praValuesMap);
}
public int queryForInt(String countQuerySql, Object... args) {
return getJdbcTemplate().queryForInt(countQuerySql, args);
}
public static String getSqlFromQueryData(String sql, Map<String, Object> paramMap) {
if (StringUtils.isEmpty(sql)) {
return null;
}
if (paramMap == null) {
return sql;
}
StringBuffer sqlExp = new StringBuffer(sql);
Set<Entry<String, Object>> set = paramMap.entrySet();
for (Entry<String, Object> entry : set) {
int start = sqlExp.indexOf(":" + entry.getKey() + " ");
if (start < 0) {
continue;
}
int last = sqlExp.lastIndexOf(":" + entry.getKey() + " ");
if (start >= 0 && start == last) {
if (entry.getValue() != null) {
sqlExp.replace(start, start + entry.getKey().length(), "'" + entry.getValue().toString() + "'");
// sqlExp.replace(start-1, start+entry.getKey().length(),
// "'"+entry.getValue().toString()+"'");
}
} else {
// 处理同一参数多处出现
sqlExp.replace(start, start + entry.getKey().length(), "'" + entry.getValue().toString() + "'");
start = sqlExp.indexOf(":" + entry.getKey());
while (start > 0) {
sqlExp.replace(start, start + entry.getKey().length(), "'" + entry.getValue().toString() + "'");
start = sqlExp.indexOf(":" + entry.getKey());
}
}
}
return sqlExp.toString();
}
private static String removeSelect(String sql) {
int beginPos = sql.indexOf(" from ");
if (beginPos < 0) {
beginPos = sql.indexOf("from ");
}
if (beginPos < 0) {
beginPos = sql.toLowerCase().indexOf(" from ");
}
if (beginPos < 0) {
beginPos = sql.toLowerCase().indexOf("from ");
}
return sql.substring(beginPos);
}
/**
* 去除sql的orderby 用于页查果询
*
* @param sql
* @return
*/
private static String removeOrders(String sql) {
Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);
Matcher m = p.matcher(sql);
StringBuffer sb = new StringBuffer();
while (m.find()) {
m.appendReplacement(sb, "");
}
m.appendTail(sb);
return sb.toString();
}
public String getDbType() {
return dbType;
}
public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
return namedParameterJdbcTemplate;
}
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
}
public String getMysqlLowerCaseTableNames() {
return mysqlLowerCaseTableNames;
}
public void setMysqlLowerCaseTableNames(String mysqlLowerCaseTableNames) {
this.mysqlLowerCaseTableNames = mysqlLowerCaseTableNames;
}
public String isShowSql() {
return showSql;
}
public void setShowSql(String showSql) {
this.showSql = showSql;
}
}
三：使用
数据源和JPA是同一数据源，在同一事务中，用的连接和JPA使用的是同一个连接，在CommonDao 额外注入一个 JdbcTemplateWrapper,当作处理复杂SQL的帮手。
JdbcTemplateWrapper 常用的方法同如下几个：
clasz 把查询结果要转为的类对像，内部再构造 ObjectRowMapper
1：public List queryAllMatchListWithFreePra(String sql, Class clasz, Object... args) // SQL 参数为问号占位符，使用参数小的情况
2：public List queryAllMatchListWithParaMap(String sql, Class clasz, Map paramMap) // SQL 参数为 参数名占位符 ,如 name=:name, paramMap中 key 为参数名，value 为值数值; 如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)