Introduction:
This paper mainly elaborates on four aspects: 1: background; 2: ideas; 3: code implementation; 4: use
One: Packaging background.
In the project, JPA, some complex queries, such as native JdbcTemplate, are inconvenient; Paging function and result set auto-alignment are needed. See Section 4.
The following two diagrams show the statistical functions of test management software itest. Because of the complexity of SQL, some of them have 200 lines, so there is Jdbc Template Wrapper in the back.
Second: The idea of packaging realization
(1) Implementing spring RowMapper interface, directly transferring jdbc result sets to JAVA objects (using reflection, which is not recommended for heavy-visited businesses)
(2) Without RowMapper interface, JdbcTemplate returns List < Map < String, Object > native JDBC result set, field name, that is KEY in MAP, which is converted to attribute name of hump rule JAVA pair, which is convenient for front-end components to use, and then returns to front-end after no longer converting to VO or entity class pair image; it is more than traditional query (one more LIST traversal, basic performance shadow). Not loud enough)
(3) Paging
Three: Code Implementation
Two classes, ObjectRowMapper and JdbcTemplateWrapper
The ObjectRowMapper code is as follows:
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>Title: RowMapper Interface Implementation</p> 24 * <p>Business Description: Complete the transformation of native result sets to JAVA objects</p> 25 * <p>Company: itest.work</p> 26 * <p>Copyright: ITest 2018</p> 27 * @author itest andy 28 * @date 2018 June 8th 2013 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 ///Caching current result set word pairs and current class Of Field Corresponding relations, 39 private Map<String ,Field> currQueryFieldMap ; 40 //Caching the current result set, fields, and JAVA Corresponding relationship of attribute names, conversion according to peak-off rule 41 private Map<String ,String> fieldClassMap ; 42 private Boolean isConvertSwitch = null; 43 // MYSQL Whether to distinguish between case and case tags, if you turn around, you should change the result set, field name, capitalization to lowercase. 44 private String mysqlLowerCaseTableNames = null; 45 private static Log logger = LogFactory.getLog(ObjectRowMapper.class); 46 //Cache some class The processed fields are mapped to the relationship between attribute names, avoiding each reprocessing of the same class, the first KEY Class name 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 * This method automatically corresponds database fields to corresponding field requirements in Object: 73 * The field name is strictly hump form=== database field name is changed to hump form without underlining 74 * If user_name is changed to userName, such as database field name, no underline 75 * You can only change the initial letter to a case 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 // Restore corresponding field Authority 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 * Assignment of specific object attributes by type 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 //There is no implementation here, if there is a special need to deal with the implementation here. 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>Title: JdbcTemplate wrapper</p> * <p>Business Description: Paging, native result set to object, native result set field name to off-peak java attribute name</p> * <p>Company: itest.work</p> * <p>Copyright: ITest 2018</p> * @author itest andy * @date 2018 June 8th 2013 * @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; } /** * Query all matched lists * * @param sql * Query sql * @param className * object type * @param args * Query parameters * @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; } /** * Query all matched lists * * @param sql * Query sql * @param className * object type * @param args * Query parameters * @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; } /** * Convert the list map native JDBC result set, field name, KEY in the MAP, to the JAVA pair attribute name of the hump rule * * @param resultList : JDBC Result set * @return Converting KEY in MAP to LIST<map<hump rule JAVA to attribute name form KEY,Object>of hump rule JAVA to attribute name form * @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()); //For caching mapping from field name to attribute name, the conversion from field name to attribute name is no longer handled when the second record is made to improve performance. 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); //Cached, the second record is no longer processed when the field name to attribute name conversion, improve performance 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(); } /** * Query all matched lists * * @param sql * Query sql * @param className * object type * @param paramMap * Query parameters * @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 * : The class constructed from the query result set, such as null, pageModel's PageData is List < Map>. * If it is not null, the Page Data of pageModel is List < className>. * @param columnNameForCount:The field name when querying the number of records, usually with the primary key */ @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); } /** * Multi-table SQL paging query, multi-table continuous query, only use this method, other use commonDao's SQL paging query * * @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; } /** * Number of query records * * @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 { // Processing multiple occurrences of the same parameter 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); } /** * Order by removing sql for page query * * @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; } }
Three: use
Data source and JPA are the same data source. In the same transaction, the same connection is used by JPA and the same connection is used by JPA. A Jdbc Template Wrapper is injected into CommonDao as an additional assistant to deal with complex SQL.
The common methods of JdbcTemplate Wrapper are as follows:
clasz converts the query results to class objects, and constructs ObjectRowMapper internally
1: public List queryAllMatchListWithFreePra(String sql, Class clasz, Object... args) // SQL parameter is a question mark placeholder, using small parameters
2: Public List query AllMatchListWithParaMap (String sql, Class clasz, Map paramMap)// SQL parameter is placeholder of parameter name, such as name=: name, key in paramMap is parameter name, value is value value value value; if clasz is null, it actually returns to List < Map < String, Object > instead of empty side, it is List < Map < String, clasz > > value.
3: Paging with pageModel as the carrier
/**
*
*@ param pageModel: Paging objects for query SQL and query parameter Map paramMap are set
* @param className
* The class constructed from the query result set, such as null, pageModel's PageData is List < Map>.
* If it is not null, the Page Data of pageModel is List < className>.
*@ Param Column Name ForCount: The field name when querying the number of records, usually with the primary key
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public void fillPageModelData(PageModel pageModel, Class className)
4: Paging queries without pageModel
/**
* Multi-table SQL paging query, multi-table continuous query, only use this method, other use commonDao's SQL paging query
*
* @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: Convert the list map native JDBC result set, field name, KEY in MAP, to the JAVA pair attribute name of hump rule.
/**
* Convert the list map native JDBC result set, field name, KEY in the MAP, to the JAVA pair attribute name of the hump rule
* It is convenient for front-end components to use, no longer converting to VO or entity class objects in the middle, and then returning to the front-end. Compared with traditional queries (one more LIST traversal, basically has little impact on performance).
*@ param resultList: JDBC result set
*@ return: Convert KEY in MAP to LIST<map<hump rule JAVA to attribute name form KEY,Object> in Hump rule LIST<map<hump rule JAVA to attribute name form
* @author itest andy
*/
public void converDbColumnName2ObjectPropName(List<Map<String,Object>> resultList)