工具类相关代码
使用到了apache的map2bean工具类 导入方法
<dependency> <groupId>commons-beanutils</groupId> <artifactId>commons-beanutils</artifactId> <version>1.9.3</version></dependency>
import org.apache.commons.beanutils.BeanUtils;import java.util.Map;/** * 将查询结果 map 封装成对应的javaBean，支持级联 ，但是属性不能重复 * 对应的javaBean的属性名必须以小驼峰形式命名，否则无法填充数据 */public class Map2Bean { private Map2Bean() { } /** * 将 map 数据封装成javaBean * * @param map Map类型数据 * @param clazz 需要转换的JavaBean * @param <T> 泛型 * @return JavaBean */ public static <T> T convert(Map<String, Object> map, Class<T> clazz) { if (map == null || clazz == null) { return null; } T result = null; try { result = clazz.newInstance(); BeanUtils.populate(result, map); } catch (Exception e) { e.printStackTrace(); } return result; }}
import java.io.Serializable;import java.util.List;/** * Page is the result of Model.paginate(......) or Db.paginate(......) */public class Page<T> implements Serializable { private static final long serialVersionUID = -5395997221963176643L; private List<T> list; // list result of this page private int pageNumber; // page number private int pageSize = 10; // result amount of this page private int totalPage; // total page private int totalRow; // total row public Page(int pageNumber) { this.pageNumber = pageNumber; } /** * Constructor. * * @param list the list of paginate result * @param pageNumber the page number * @param pageSize the page size * @param totalPage the total page of paginate * @param totalRow the total row of paginate */ public Page(List<T> list, int pageNumber, int pageSize, int totalPage, int totalRow) { this.list = list; this.pageNumber = pageNumber; this.pageSize = pageSize; this.totalPage = totalPage; this.totalRow = totalRow; } public Page(int pageNumber, int pageSize) { this.pageNumber = pageNumber; this.pageSize = pageSize; } /** * Return list of this page. */ public List<T> getList() { return list; } /** * Return page number. */ public int getPageNumber() { return pageNumber; } /** * Return page size. */ public int getPageSize() { return pageSize; } /** * Return total page. */ public int getTotalPage() { totalPage = totalRow / pageSize; if (totalRow % pageSize > 0) { totalPage++; } return totalPage; } /** * Return total row. */ public int getTotalRow() { return totalRow; } public boolean isFirstPage() { return pageNumber == 1; } public boolean isLastPage() { return pageNumber == totalPage; } public void setList(List<T> list) { this.list = list; } public void setPageNumber(int pageNumber) { this.pageNumber = pageNumber; } public void setPageSize(int pageSize) { this.pageSize = pageSize; } public void setTotalPage(int totalPage) { this.totalPage = totalPage; } public void setTotalRow(int totalRow) { this.totalRow = totalRow; } @Override public String toString() { return "Page{" + "list=" + list + ", pageNumber=" + pageNumber + ", pageSize=" + pageSize + ", totalPage=" + totalPage + ", totalRow=" + totalRow + '}'; }}
import java.io.Serializable;import java.util.HashMap;import java.util.Map;import java.util.Set;/** * Record */public class Record implements Serializable { private static final long serialVersionUID = 905784513600884082L; private Map<String, Object> columns = new HashMap<>(); public Record() { } public Record(Map<String, Object> columns) { this.columns = columns; } public Map<String, Object> getColumns() { return columns; } public Record setColumns(Map<String, Object> columns) { this.getColumns().putAll(columns); return this; } public Record setColumns(Record record) { getColumns().putAll(record.getColumns()); return this; } public Record remove(String column) { getColumns().remove(column); return this; } public Record remove(String... columns) { if (columns != null) { for (String c : columns) { this.getColumns().remove(c); } } return this; } public Record removeNullValueColumns() { for (java.util.Iterator<Map.Entry<String, Object>> it = getColumns().entrySet().iterator(); it.hasNext(); ) { Map.Entry<String, Object> e = it.next(); if (e.getValue() == null) { it.remove(); } } return this; } /** * Keep columns of this record and remove other columns. * * @param columns the column names of the record */ public Record keep(String... columns) { if (columns != null && columns.length > 0) { Map<String, Object> newColumns = new HashMap<String, Object>(columns.length); // getConfig().containerFactory.getColumnsMap(); for (String c : columns) { if (this.getColumns().containsKey(c)) { // prevent put null value to the newColumns newColumns.put(c, this.getColumns().get(c)); } } this.getColumns().clear(); this.getColumns().putAll(newColumns); } else { this.getColumns().clear(); } return this; } /** * Keep column of this record and remove other columns. * * @param column the column names of the record */ public Record keep(String column) { if (getColumns().containsKey(column)) { // prevent put null value to the newColumns Object keepIt = getColumns().get(column); getColumns().clear(); getColumns().put(column, keepIt); } else { getColumns().clear(); } return this; } public Record clear() { getColumns().clear(); return this; } public Record set(String column, Object value) { getColumns().put(column, value); return this; } public <T> T get(String column) { return (T) getColumns().get(column); } public <T> T get(String column, Object defaultValue) { Object result = getColumns().get(column); return (T) (result != null ? result : defaultValue); } /** * Get column of mysql type: varchar, char, enum, set, text, tinytext, mediumtext, longtext */ public String getStr(String column) { return (String) getColumns().get(column); } /** * Get column of mysql type: int, integer, tinyint(n) n > 1, smallint, mediumint */ public Integer getInt(String column) { return (Integer) getColumns().get(column); } /** * Get column of mysql type: bigint */ public Long getLong(String column) { return (Long) getColumns().get(column); } /** * Get column of mysql type: unsigned bigint */ public java.math.BigInteger getBigInteger(String column) { return (java.math.BigInteger) getColumns().get(column); } /** * Get column of mysql type: date, year */ public java.util.Date getDate(String column) { return (java.util.Date) getColumns().get(column); } /** * Get column of mysql type: time */ public java.sql.Time getTime(String column) { return (java.sql.Time) getColumns().get(column); } /** * Get column of mysql type: timestamp, datetime */ public java.sql.Timestamp getTimestamp(String column) { return (java.sql.Timestamp) getColumns().get(column); } /** * Get column of mysql type: real, double */ public Double getDouble(String column) { return (Double) getColumns().get(column); } /** * Get column of mysql type: float */ public Float getFloat(String column) { return (Float) getColumns().get(column); } /** * Get column of mysql type: bit, tinyint(1) */ public Boolean getBoolean(String column) { return (Boolean) getColumns().get(column); } /** * Get column of mysql type: decimal, numeric */ public java.math.BigDecimal getBigDecimal(String column) { return (java.math.BigDecimal) getColumns().get(column); } /** * Get column of mysql type: binary, varbinary, tinyblob, blob, mediumblob, longblob * I have not finished the test. */ public byte[] getBytes(String column) { return (byte[]) getColumns().get(column); } /** * Get column of any type that extends from Number */ public Number getNumber(String column) { return (Number) getColumns().get(column); } @Override public String toString() { StringBuilder sb = new StringBuilder(); sb.append(super.toString()).append(" {"); boolean first = true; for (Map.Entry<String, Object> e : getColumns().entrySet()) { if (first) { first = false; } else { sb.append(", "); } Object value = e.getValue(); if (value != null) { value = value.toString(); } sb.append(e.getKey()).append(":").append(value); } sb.append("}"); return sb.toString(); } @Override public boolean equals(Object o) { if (!(o instanceof Record)) { return false; } if (o == this) { return true; } return this.getColumns().equals(((Record) o).getColumns()); } @Override public int hashCode() { return getColumns() == null ? 0 : getColumns().hashCode(); } /** * Return column names of this record. */ public String[] getColumnNames() { Set<String> attrNameSet = getColumns().keySet(); return attrNameSet.toArray(new String[attrNameSet.size()]); } /** * Return column values of this record. */ public Object[] getColumnValues() { java.util.Collection<Object> attrValueCollection = getColumns().values(); return attrValueCollection.toArray(new Object[attrValueCollection.size()]); } /** * Return json string of this record. */ public String toJson() { throw new UnsupportedOperationException("还未实现"); }}
import org.hibernate.Session;import org.hibernate.transform.Transformers;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Component;import java.util.ArrayList;import java.util.List;import java.util.Map;import java.util.regex.Pattern;import javax.persistence.EntityManager;import javax.persistence.PersistenceContext;import javax.persistence.Query;/** * 作者：guoyzh * 时间：2019/8/20 12:53 * 功能：使用jpa进行原生sql查询的工具类 使用AutoWrite注入即可使用 */@Componentpublic class SqlUtils { @Autowired @PersistenceContext private EntityManager entityManager; public SqlUtils(EntityManager entityManager) { this.entityManager = entityManager; } public SqlUtils() { } public void setEntityManager(EntityManager entityManager) { this.entityManager = entityManager; } /** * 返回查询的一个Record，没有则为null */ public Record findFirst(String sql, Object... params) { return findFirst(sql, Record.class, params); } public Record findFirst(String sql, Map<String, Object> searchMap) { return findFirst(sql, Record.class, searchMap); } /** * 返回查询的一个实体，没有则为null */ public <T> T findFirst(String sql, Class<T> clazz, Object... params) { List<T> ts = find(sql, clazz, params); return (ts == null || ts.size() == 0) ? null : ts.get(0); } public <T> T findFirst(String sql, Class<T> clazz, Map<String, Object> searchMap) { List<T> ts = find(sql, clazz, searchMap); return (ts == null || ts.size() == 0) ? null : ts.get(0); } public List<Record> find(String sql, Object... params) { return find(sql, Record.class, params); } public List<Record> find(String sql, Map<String, Object> searchMap) { return find(sql, Record.class, searchMap); } public List<Record> find(String sql) { return find(sql, Record.class, (Map<String, Object>) null); } /** * 查询列表 * * @param sql native sql语句，可以包含？ * @param clazz 返回的类型，可以是JavaBean，可以是Record * @param params 参数列表 * @param <T> 泛型 * @return 查询列表结果 */ public <T> List<T> find(String sql, Class<T> clazz, Object... params) { Session session = entityManager.unwrap(Session.class); org.hibernate.Query query = session.createSQLQuery(sql); //0-Based for (int i = 0; i < params.length; i++) { query.setParameter(i, params[i]); } List list = getList(query, clazz); return list; } /** * 查询列表 * * @param sql native sql语句，可以包含 :具名参数 * @param clazz 返回的类型，可以是JavaBean，可以是Record * @param searchMap 具名参数列表 * @param <T> 泛型 * @return 查询列表结果 */ public <T> List<T> find(String sql, Class<T> clazz, Map<String, Object> searchMap) { Session session = entityManager.unwrap(Session.class); org.hibernate.Query query = session.createSQLQuery(sql); if (null != searchMap) { searchMap.forEach(query::setParameter); } List list = getList(query, clazz); return list; } /** * ----------------------------------------------record-positioned-parameter--------------------------------------------------- */ public Page<Record> paginate(String nativeSQL, int pageNumber, int pageSize, Object... params) { String nativeCountSQL = getCountSQL(nativeSQL); return paginate(null, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, params); } public Page<Record> paginate(String nativeSQL, Boolean isGroupBySql, int pageNumber, int pageSize, Object... params) { String nativeCountSQL = getCountSQL(nativeSQL); return paginate(isGroupBySql, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, params); } public Page<Record> paginate(String nativeSQL, String nativeCountSQL, int pageNumber, int pageSize, Object... params) { return paginate(null, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, params); } public Page<Record> paginate(Boolean isGroupBySql, String nativeSQL, String nativeCountSQL, int pageNumber, int pageSize, Object... params) { return paginate(isGroupBySql, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, params); } /** * ----------------------------------------------record-maped-parameter--------------------------------------------------- */ public Page<Record> paginate(String nativeSQL, int pageNumber, int pageSize, Map<String, Object> searchMap) { String nativeCountSQL = getCountSQL(nativeSQL); return paginate(null, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, searchMap); } public Page<Record> paginate(String nativeSQL, Boolean isGroupBySql, int pageNumber, int pageSize, Map<String, Object> searchMap) { String nativeCountSQL = getCountSQL(nativeSQL); return paginate(isGroupBySql, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, searchMap); } public Page<Record> paginate(String nativeSQL, String nativeCountSQL, int pageNumber, int pageSize, Map<String, Object> searchMap) { return paginate(null, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, searchMap); } public Page<Record> paginate(Boolean isGroupBySql, String nativeSQL, String nativeCountSQL, int pageNumber, int pageSize, Map<String, Object> searchMap) { return paginate(isGroupBySql, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, searchMap); } /** * ----------------------------------------------JavaBean-positioned-parameter--------------------------------------------------- */ public <T> Page<T> paginate(Boolean isGroupBySql, String nativeSQL, Class<T> clazz, int pageNumber, int pageSize, Object... params) { String nativeCountSQL = getCountSQL(nativeSQL); return paginate(isGroupBySql, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, params); } public <T> Page<T> paginate(String nativeSQL, String nativeCountSQL, Class<T> clazz, int pageNumber, int pageSize, Object... params) { return paginate(null, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, params); } public <T> Page<T> paginate(String nativeSQL, Class<T> clazz, int pageNumber, int pageSize, String... params) { String nativeCountSQL = getCountSQL(nativeSQL); return paginate(null, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, params); } /** * ----------------------------------------------JavaBean-maped-parameter--------------------------------------------------- */ public <T> Page<T> paginate(String nativeSQL, Class<T> clazz, int pageNumber, int pageSize, Map<String, Object> searchMap) { String nativeCountSQL = getCountSQL(nativeSQL); return paginate(null, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, searchMap); } public <T> Page<T> paginate(Boolean isGroupBySql, String nativeSQL, Class<T> clazz, int pageNumber, int pageSize, Map<String, Object> searchMap) { String nativeCountSQL = getCountSQL(nativeSQL); return paginate(isGroupBySql, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, searchMap); } public <T> Page<T> paginate(String nativeSQL, String nativeCountSQL, Class<T> clazz, int pageNumber, int pageSize, Map<String, Object> searchMap) { return paginate(null, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, searchMap); } /** * @param pageNumber pageNumber * @param pageSize pageSize * @param isGroupBySql 是否包含Group by语句，影响总行数 * @param nativeSQL 原生SQL语句 {@see QueryHelper} * @param nativeCountSQL 原生求总行数的SQL语句 {@see QueryHelper} * @param clazz JavaBean风格的DTO或者Record，需要用别名跟JavaBean对应 * @param <T> 返回JavaBean风格的DTO或者Record * @param params 按照顺序给条件 */ public <T> Page<T> paginate(Boolean isGroupBySql, String nativeSQL, String nativeCountSQL, Class<T> clazz, int pageNumber, int pageSize, Object... params) { if (pageNumber < 1 || pageSize < 1) { throw new IllegalArgumentException("pageNumber and pageSize must more than 0"); } Query countQuery = entityManager.createNativeQuery(nativeCountSQL); //坑死人，1-Based for (int i = 1; i <= params.length; i++) { countQuery.setParameter(i, params[i - 1]); } List countQueryResultList = countQuery.getResultList(); int size = countQueryResultList.size(); if (isGroupBySql == null) { isGroupBySql = size > 1; } long totalRow; if (isGroupBySql) { totalRow = size; } else { totalRow = (size > 0) ? ((Number) countQueryResultList.get(0)).longValue() : 0; } if (totalRow == 0) { return new Page<>(new ArrayList<>(0), pageNumber, pageSize, 0, 0); } int totalPage = (int) (totalRow / pageSize); if (totalRow % pageSize != 0) { totalPage++; } if (pageNumber > totalPage) { return new Page<>(new ArrayList<>(0), pageNumber, pageSize, totalPage, (int) totalRow); } Session session = entityManager.unwrap(Session.class); int offset = pageSize * (pageNumber - 1); org.hibernate.Query query = session.createSQLQuery(nativeSQL).setFirstResult(offset).setMaxResults(pageSize); //坑死人，0-Based for (int i = 0; i < params.length; i++) { query.setParameter(i, params[i]); } final List list = getList(query, clazz); return new Page<T>(list, pageNumber, pageSize, totalPage, (int) totalRow); } /** * @param pageNumber pageNumber * @param pageSize pageSize * @param isGroupBySql 是否包含Group by语句，影响总行数 * @param nativeSQL 原生SQL语句 {@see QueryHelper} * @param nativeCountSQL 原生求总行数的SQL语句 {@see QueryHelper} * @param clazz JavaBean风格的DTO或者Record，需要用别名跟JavaBean对应 * @param <T> 返回JavaBean风格的DTO或者Record * @param searchMap k-v条件 */ public <T> Page<T> paginate(Boolean isGroupBySql, String nativeSQL, String nativeCountSQL, Class<T> clazz, int pageNumber, int pageSize, Map<String, Object> searchMap) { if (pageNumber < 1 || pageSize < 1) { throw new IllegalArgumentException("pageNumber and pageSize must more than 0"); } Query countQuery = entityManager.createNativeQuery(nativeCountSQL); if (null != searchMap) { searchMap.forEach(countQuery::setParameter); } List countQueryResultList = countQuery.getResultList(); int size = countQueryResultList.size(); if (isGroupBySql == null) { isGroupBySql = size > 1; } long totalRow; if (isGroupBySql) { totalRow = size; } else { totalRow = (size > 0) ? ((Number) countQueryResultList.get(0)).longValue() : 0; } if (totalRow == 0) { return new Page<>(new ArrayList<>(0), pageNumber, pageSize, 0, 0); } int totalPage = (int) (totalRow / pageSize); if (totalRow % pageSize != 0) { totalPage++; } if (pageNumber > totalPage) { return new Page<>(new ArrayList<>(0), pageNumber, pageSize, totalPage, (int) totalRow); } Session session = entityManager.unwrap(Session.class); int offset = pageSize * (pageNumber - 1); org.hibernate.Query query = session.createSQLQuery(nativeSQL).setFirstResult(offset).setMaxResults(pageSize); if (null != searchMap) { searchMap.forEach(query::setParameter); } final List list = getList(query, clazz); return new Page<T>(list, pageNumber, pageSize, totalPage, (int) totalRow); } private <T> List getList(org.hibernate.Query query, Class<T> clazz) { final List list; //Object[].class if (Object[].class == clazz) { return query.list(); } query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); List mapList = query.list(); list = new ArrayList(mapList.size()); mapList.forEach(map -> { Map<String, Object> tmp = (Map<String, Object>) map; //Record.class if (Record.class == clazz) { list.add(new Record(tmp)); //Map及子类 } else if (Map.class.isAssignableFrom(clazz)) { list.add(tmp); //JavaBean风格 } else { list.add(Map2Bean.convert(tmp, clazz)); } }); return list; } /*private <T> List getList(org.hibernate.Query query, Class<T> clazz) { final List list; if(Record.class == clazz){ //返回Record query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); List mapList = query.list(); list = new ArrayList(mapList.size()); mapList.forEach(map->{ Map<String , Object> tmp = (Map<String , Object>) map; list.add(new Record(tmp)); }); }else { //返回JavaBean //只能返回简单的Javabean，不具备级联特性 query.setResultTransformer(Transformers.aliasToBean(clazz)); list = query.list(); } return list; }*/ private String getCountSQL(String sql) { String countSQL = "SELECT COUNT(*) AS totalRow " + sql.substring(sql.toUpperCase().indexOf("FROM")); return replaceOrderBy(countSQL); } protected static class Holder { private static final Pattern ORDER_BY_PATTERN = Pattern.compile( "order\\s+by\\s+[^,\\s]+(\\s+asc|\\s+desc)?(\\s*,\\s*[^,\\s]+(\\s+asc|\\s+desc)?)*", Pattern.CASE_INSENSITIVE | Pattern.MULTILINE); } public String replaceOrderBy(String sql) { return Holder.ORDER_BY_PATTERN.matcher(sql).replaceAll(""); }}
代码中调用
@AutowiredSqlUtils mSqlUtils;。。。@Transactional@ApiOperation("测试")@PostMapping("/get1")public Result get1() { HashMap<String, Object> map = new HashMap<>(); map.put("id", "SPA0000001"); TestResp record = mSqlUtils.findFirst("select * from st_PkgActvty where id = :id", TestResp.class, map); return ResultGenerator.genSuccessResult(record);}
Spring data jpa@query使用原生SQl，需要注意的坑
根据代码来解说：
@Query(value = "select bill.id_ as id, bill.created_date as date, bill.no, lawyer_case .case_no as caseNo, " + "lawyer_case .case_name as caseName, customer.no as customerNo, customer.cn_name as customerName, " + "bill.total_expense_after_tax, bill.collected_money, bill.book_ticket_amount, bill.version " + "e1.name as creator, bill.status" + "from bill " + "left join lawyer_case on lawyer_case .case_no=bill.case_no " + "left join customer on customer.no=bill.customer_no " + "left join employee e1 on e1.id_=bill.creator " + "where IF (?1!='', customer_no=?1, 1=1) " + "and IF (?2!='', case_no=?2, 1=1) " + "and IF (?3!='', status=?3, 1=1) " + "and IF (?4!='', creator'%',?4,'%')), 1=1) " + "and create_by=?5 " + "ORDER BY ?#{#pageable} ", countQuery = "select count(*) " + "from bill " + "left join lawyer_case on lawyer_case .case_no=bill.case_no " + "left join customer on customer.no=bill.customer_no " + "left join employee e1 on e1.id_=bill.creator " + "where IF (?1!='', customer_no=?1, 1=1) " + "and IF (?2!='', case_no=?2, 1=1) " + "and IF (?3!='', status=?3, 1=1) " + "and IF (?4!='', creator'%',?4,'%')), 1=1) " + "and create_by=?5 "+ "ORDER BY ?#{#pageable} ", nativeQuery = true) Page<Object[]> findAllBill(String customerNo, String caseNo, Integer status, String creator, String createBy, Pageable pageable);
需要注意的方法有以下几点：
1、From 不支持重命名.
2、返回的是一个page<Object[]>，数组中只保存了数据，没有对应的key，只能根据返回数据的顺序，依次注入到DTO中。
3、对于使用分页，需要：“ORDER BY ?#{#pageable}”，可以直接传入一个pageable对象，会自动解析。
4、注意格式问题，很多时候就是换行的时候，没有空格。
5、仔细对应数据库中表字段，很多时候报某个字段找不到，就是因为字段名写错，和数据库中对应不上。
6、这是解决使用微服务，大量的数据都需要远程调用，会降低程序的性能。
7、使用Pageabel作为参数的时候，去进行分页。刚开始的时候，觉得还是一个可行的办法，但是得注意的时候，当需要排序的时候，是无法加入sort字段的。 会一直报错left*。
8、针对7的解决方案，把原生SQL的数据查询和countQuery分成两个查询方法。
得到count，然后进行判断，若是等于0，则直接返回空集合；反之，则取获取数据。 需要自己进行分页计算，传入正确的pageNumber和pageSize。
大部分系统都是按照修改时间进行降序排序。 所以，order by可以写死。
然后pageNumber和pageSize动态传入。 pageNumber的算法= (pageNumber – 1) * pageSize, 前提是PageNumber是从1开始，若0，则pageNumber=pageNumber * PageSize； 这样就可以保证数据的正确。
/*** pageInfos: 转换之后的数据。* pageable：传入的pageable.* totalPage: 第一条SQL算好的返回值。* 这样就可以统一的返回各种pageDTO。*/private Page<T> convertForPage(List<T> pageInfos, Pageable pageable, Integer totalPage) { return new PageImpl<>(pageInfos, pageable, totalPage); }
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。
看自家总在期待,不知将来好歹,新乐吧总在不断等待,
