导读:
主要从 4 个方面来阐述, 1: 背景; 2: 思路; 3: 代码实现; 4: 使用
一: 封装背景,
在做项目的时候, 用的 JPA , 有些复杂查询, 比如报表用原生的 JdbcTemplate , 很不方便; 需要增加分页功能, 以及结果集自动转对像等 5 个常用功能, 见第 4 节.
下面两个图是开源测试管理软件 itest 的统计功能, 因为 SQL 复杂, 有些有 200 行, 所以才有后面的 JdbcTemplateWrapper; 可以在这体验这些报表 (点测试, 然后选择一个项目, 然后点度量分析), 速度还不错, https://itest.work/demo/login.htm https://itest.work/demo/login.htm
上面这图, SQL 就有 200 行
二: 封装实现思路
(1) 实现 spring RowMapper 接口, 直接把 jdbc 结果集转 JAVA 对像 (用的反射, 访问量大的业务不推荐用这方式)
(2) 不通过 RowMapper 接口, 把 JdbcTemplate 返回的 List<Map<String,Object>> 原生 JDBC 结果集中, 字段名, 也就是 MAP 中的 KEY, 转换为驼峰规则的 JAVA 对属性名, 方便前端组件使用, 不再中间转为 VO 或 实体类对像后, 再返前端; 比传统查询 (多了一个 LIST 的遍历 , 基本对性能影响不大)
(3) 封装分页
三: 代码实现
两个类, 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 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
public List queryAllMatchListWithFreePra(String sql, Class clasz, Object... args) // SQL 参数为问号占位符, 使用参数小的情况 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)
来源: https://www.cnblogs.com/mypm/p/10709689.html