这篇文章主要介绍了SpringDataJPA原生sql查询方式的封装操作，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教
工具类相关代码
使用到了apache的map2bean工具类 导入方法
commons-beanutils commons-beanutils 1.9.3
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 泛型 * @return JavaBean */ public static T convert(Map map, Class 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 Pageimplements Serializable { private static final long serialVersionUID = -5395997221963176643L; private List 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 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 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 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 columns = new HashMap<>(); public Record() { } public Record(Map columns) { this.columns = columns; } public Map getColumns() { return columns; } public Record setColumns(Map 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> it = getColumns().entrySet().iterator(); it.hasNext(); ) { Map.Entry 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 newColumns = new HashMap(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 get(String column) { return (T) getColumns().get(column); } public 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 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 attrNameSet = getColumns().keySet(); return attrNameSet.toArray(new String[attrNameSet.size()]); } /** * Return column values of this record. */ public Object[] getColumnValues() { java.util.Collection 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注入即可使用 */ @Component public 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 searchMap) { return findFirst(sql, Record.class, searchMap); } /** * 返回查询的一个实体，没有则为null */ public T findFirst(String sql, Class clazz, Object... params) { List ts = find(sql, clazz, params); return (ts == null || ts.size() == 0) ? null : ts.get(0); } public T findFirst(String sql, Class clazz, Map searchMap) { List ts = find(sql, clazz, searchMap); return (ts == null || ts.size() == 0) ? null : ts.get(0); } public List find(String sql, Object... params) { return find(sql, Record.class, params); } public List find(String sql, Map searchMap) { return find(sql, Record.class, searchMap); } public List find(String sql) { return find(sql, Record.class, (Map) null); } /** * 查询列表 * * @param sql native sql语句，可以包含？ * @param clazz 返回的类型，可以是JavaBean，可以是Record * @param params 参数列表 * @param 泛型 * @return 查询列表结果 */ public List find(String sql, Class clazz, Object... params) { Session session = entityManager.unwrap(Session.class); org.hibernate.Query query = session.createSQLQuery(sql); //0-Based for (int i = 0; i 泛型 * @return 查询列表结果 */ public List find(String sql, Class clazz, Map 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 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 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 paginate(String nativeSQL, String nativeCountSQL, int pageNumber, int pageSize, Object... params) { return paginate(null, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, params); } public Page 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 paginate(String nativeSQL, int pageNumber, int pageSize, Map searchMap) { String nativeCountSQL = getCountSQL(nativeSQL); return paginate(null, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, searchMap); } public Page paginate(String nativeSQL, Boolean isGroupBySql, int pageNumber, int pageSize, Map searchMap) { String nativeCountSQL = getCountSQL(nativeSQL); return paginate(isGroupBySql, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, searchMap); } public Page paginate(String nativeSQL, String nativeCountSQL, int pageNumber, int pageSize, Map searchMap) { return paginate(null, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, searchMap); } public Page paginate(Boolean isGroupBySql, String nativeSQL, String nativeCountSQL, int pageNumber, int pageSize, Map searchMap) { return paginate(isGroupBySql, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, searchMap); } /** * ----------------------------------------------JavaBean-positioned-parameter--------------------------------------------------- */ public Page paginate(Boolean isGroupBySql, String nativeSQL, Class clazz, int pageNumber, int pageSize, Object... params) { String nativeCountSQL = getCountSQL(nativeSQL); return paginate(isGroupBySql, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, params); } public Page paginate(String nativeSQL, String nativeCountSQL, Class clazz, int pageNumber, int pageSize, Object... params) { return paginate(null, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, params); } public Page paginate(String nativeSQL, Class clazz, int pageNumber, int pageSize, String... params) { String nativeCountSQL = getCountSQL(nativeSQL); return paginate(null, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, params); } /** * ----------------------------------------------JavaBean-maped-parameter--------------------------------------------------- */ public Page paginate(String nativeSQL, Class clazz, int pageNumber, int pageSize, Map searchMap) { String nativeCountSQL = getCountSQL(nativeSQL); return paginate(null, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, searchMap); } public Page paginate(Boolean isGroupBySql, String nativeSQL, Class clazz, int pageNumber, int pageSize, Map searchMap) { String nativeCountSQL = getCountSQL(nativeSQL); return paginate(isGroupBySql, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, searchMap); } public Page paginate(String nativeSQL, String nativeCountSQL, Class clazz, int pageNumber, int pageSize, Map 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 返回JavaBean风格的DTO或者Record * @param params 按照顺序给条件 */ public Page paginate(Boolean isGroupBySql, String nativeSQL, String nativeCountSQL, Class clazz, int pageNumber, int pageSize, Object... params) { if (pageNumber <1 || pagesize < 1) { throw new illegalargumentexception("pagenumber and must more than 0"); } query countquery=entityManager.createNativeQuery(nativeCountSQL);> 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 (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 返回JavaBean风格的DTO或者Record * @param searchMap k-v条件 */ public Page paginate(Boolean isGroupBySql, String nativeSQL, String nativeCountSQL, Class clazz, int pageNumber, int pageSize, Map searchMap) { if (pageNumber <1 || pagesize < 1) { throw new illegalargumentexception("pagenumber and must more than 0"); } query countquery=entityManager.createNativeQuery(nativeCountSQL); if (null !=searchMap) searchmap.foreach(countquery::setparameter); list countqueryresultlist=countQuery.getResultList(); int size=countQueryResultList.size(); (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(list, pageNumber, pageSize, totalPage, (int) totalRow); } private List getList(org.hibernate.Query query, Class 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 tmp = (Map) 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 List getList(org.hibernate.Query query, Class 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 tmp = (Map) 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(""); } } 代码中调用
@Autowired SqlUtils mSqlUtils; 。。。 @Transactional @ApiOperation("测试") @PostMapping("/get1") public Result get1() { HashMap 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 findAllBill(String customerNo, String caseNo, Integer status, String creator, String createBy, Pageable pageable); 需要注意的方法有以下几点：
1、From 不支持重命名.
2、返回的是一个page
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 PageconvertForPage(List pageInfos, Pageable pageable, Integer totalPage) { return new PageImpl<>(pageInfos, pageable, totalPage); }
以上就是SpringDataJPA原生sql查询方式的封装操作的详细内容，更多请关注0133技术站其它相关文章！
