SpringDataJPA原生sql查詢方式的封裝操作
更新時(shí)間：2021年06月11日 09:41:37 作者：414丶小哥
這篇文章主要介紹了SpringDataJPA原生sql查詢方式的封裝操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教
工具類相關(guān)代碼
使用到了apache的map2bean工具類 導(dǎo)入方法
<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;
/**
* 將查詢結(jié)果 map 封裝成對(duì)應(yīng)的javaBean，支持級(jí)聯(lián) ，但是屬性不能重復(fù)
* 對(duì)應(yīng)的javaBean的屬性名必須以小駝峰形式命名，否則無(wú)法填充數(shù)據(jù)
*/
public class Map2Bean {
private Map2Bean() {
}
/**
* 將 map 數(shù)據(jù)封裝成javaBean
*
* @param map Map類型數(shù)據(jù)
* @param clazz 需要轉(zhuǎn)換的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("還未實(shí)現(xiàn)");
}
}
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
* 時(shí)間：2019/8/20 12:53
* 功能：使用jpa進(jìn)行原生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;
}
/**
* 返回查詢的一個(gè)Record，沒(méi)有則為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);
}
/**
* 返回查詢的一個(gè)實(shí)體，沒(méi)有則為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語(yǔ)句，可以包含？
* @param clazz 返回的類型，可以是JavaBean，可以是Record
* @param params 參數(shù)列表
* @param <T> 泛型
* @return 查詢列表結(jié)果
*/
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語(yǔ)句，可以包含 :具名參數(shù)
* @param clazz 返回的類型，可以是JavaBean，可以是Record
* @param searchMap 具名參數(shù)列表
* @param <T> 泛型
* @return 查詢列表結(jié)果
*/
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語(yǔ)句，影響總行數(shù)
* @param nativeSQL 原生SQL語(yǔ)句 {@see QueryHelper}
* @param nativeCountSQL 原生求總行數(shù)的SQL語(yǔ)句 {@see QueryHelper}
* @param clazz JavaBean風(fēng)格的DTO或者Record，需要用別名跟JavaBean對(duì)應(yīng)
* @param <T> 返回JavaBean風(fēng)格的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語(yǔ)句，影響總行數(shù)
* @param nativeSQL 原生SQL語(yǔ)句 {@see QueryHelper}
* @param nativeCountSQL 原生求總行數(shù)的SQL語(yǔ)句 {@see QueryHelper}
* @param clazz JavaBean風(fēng)格的DTO或者Record，需要用別名跟JavaBean對(duì)應(yīng)
* @param <T> 返回JavaBean風(fēng)格的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風(fēng)格
} 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
//只能返回簡(jiǎn)單的Javabean，不具備級(jí)聯(lián)特性
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("");
}
}
代碼中調(diào)用
@Autowired
SqlUtils mSqlUtils;
。。。
@Transactional
@ApiOperation("測(cè)試")
@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，需要注意的坑
根據(jù)代碼來(lái)解說(shuō)：
@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);
需要注意的方法有以下幾點(diǎn)：
1、From 不支持重命名.
2、返回的是一個(gè)page<Object[]>，數(shù)組中只保存了數(shù)據(jù)，沒(méi)有對(duì)應(yīng)的key，只能根據(jù)返回?cái)?shù)據(jù)的順序，依次注入到DTO中。
3、對(duì)于使用分頁(yè)，需要：“ORDER BY ?#{#pageable}”，可以直接傳入一個(gè)pageable對(duì)象，會(huì)自動(dòng)解析。
4、注意格式問(wèn)題，很多時(shí)候就是換行的時(shí)候，沒(méi)有空格。
5、仔細(xì)對(duì)應(yīng)數(shù)據(jù)庫(kù)中表字段，很多時(shí)候報(bào)某個(gè)字段找不到，就是因?yàn)樽侄蚊麑?xiě)錯(cuò)，和數(shù)據(jù)庫(kù)中對(duì)應(yīng)不上。
6、這是解決使用微服務(wù)，大量的數(shù)據(jù)都需要遠(yuǎn)程調(diào)用，會(huì)降低程序的性能。
7、使用Pageabel作為參數(shù)的時(shí)候，去進(jìn)行分頁(yè)。剛開(kāi)始的時(shí)候，覺(jué)得還是一個(gè)可行的辦法，但是得注意的時(shí)候，當(dāng)需要排序的時(shí)候，是無(wú)法加入sort字段的。 會(huì)一直報(bào)錯(cuò)left*。
8、針對(duì)7的解決方案，把原生SQL的數(shù)據(jù)查詢和countQuery分成兩個(gè)查詢方法。
得到count，然后進(jìn)行判斷，若是等于0，則直接返回空集合；反之，則取獲取數(shù)據(jù)。 需要自己進(jìn)行分頁(yè)計(jì)算，傳入正確的pageNumber和pageSize。
大部分系統(tǒng)都是按照修改時(shí)間進(jìn)行降序排序。 所以，order by可以寫(xiě)死。
然后pageNumber和pageSize動(dòng)態(tài)傳入。 pageNumber的算法= (pageNumber - 1) * pageSize, 前提是PageNumber是從1開(kāi)始，若0，則pageNumber=pageNumber * PageSize； 這樣就可以保證數(shù)據(jù)的正確。
/**
* pageInfos: 轉(zhuǎn)換之后的數(shù)據(jù)。
* pageable：傳入的pageable.
* totalPage: 第一條SQL算好的返回值。
* 這樣就可以統(tǒng)一的返回各種pageDTO。
*/
private Page<T> convertForPage(List<T> pageInfos, Pageable pageable, Integer totalPage) {
return new PageImpl<>(pageInfos, pageable, totalPage);
}
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
您可能感興趣的文章:
- SpringData JPA快速上手之關(guān)聯(lián)查詢及JPQL語(yǔ)句書(shū)寫(xiě)詳解
- SpringData JPA中查詢接口Repository的使用
- SpringDataJpa如何使用union多表分頁(yè)條件查詢
- SpringDataJpa like查詢無(wú)效的解決
- SpringDataJPA之Specification復(fù)雜查詢實(shí)戰(zhàn)
- springdata jpa使用Example快速實(shí)現(xiàn)動(dòng)態(tài)查詢功能
- SpringData JPA實(shí)現(xiàn)查詢分頁(yè)demo
- SpringData JPA Mongodb查詢部分字段問(wèn)題
相關(guān)文章
Spring Security實(shí)現(xiàn)微信公眾號(hào)網(wǎng)頁(yè)授權(quán)功能
這篇文章主要介紹了Spring Security中實(shí)現(xiàn)微信網(wǎng)頁(yè)授權(quán),本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-08-08
這篇文章主要介紹了Java編程調(diào)用微信分享功能,結(jié)合實(shí)例形式分析了java微信分享功能接口的定義與調(diào)用相關(guān)操作技巧,需要的朋友可以參考下2017-08-08
這篇文章主要為大家詳細(xì)介紹了JAVA開(kāi)發(fā)環(huán)境搭建教程，配置JAVA開(kāi)發(fā)環(huán)境，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-06-06
解決Mapper接口和mapper.xml的文件位置問(wèn)題
這篇文章主要介紹了解決Mapper接口和mapper.xml的文件位置問(wèn)題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12
這篇文章主要介紹了Java日期與時(shí)間類原理解析,文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-01-01
只需兩步實(shí)現(xiàn)Eclipse+Maven快速構(gòu)建第一個(gè)Spring Boot項(xiàng)目
這篇文章主要介紹了只需兩步實(shí)現(xiàn)Eclipse+Maven快速構(gòu)建第一個(gè)Spring Boot項(xiàng)目，小編覺(jué)得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2018-12-12
將本地JAR文件手動(dòng)添加到Maven本地倉(cāng)庫(kù)的實(shí)現(xiàn)過(guò)程
在Java開(kāi)發(fā)中,使用Maven作為項(xiàng)目管理工具已經(jīng)成為了主流的選擇,Maven提供了強(qiáng)大的依賴管理功能,可以輕松地下載和管理項(xiàng)目所需的庫(kù)和工具,在某些情況下,你可能會(huì)需要將本地下載的JAR文件手動(dòng)添加到Maven的本地倉(cāng)庫(kù)中,這篇博客將詳細(xì)介紹如何實(shí)現(xiàn)這一過(guò)程2024-10-10
IDEA導(dǎo)入JDBC驅(qū)動(dòng)的jar包步驟詳解
JDBC是一種底層的API，是連接數(shù)據(jù)庫(kù)和Java應(yīng)用程序的紐帶，因此我們?cè)谠L問(wèn)數(shù)據(jù)庫(kù)時(shí)需要在業(yè)務(wù)邏輯層中嵌入SQL語(yǔ)句，這篇文章主要介紹了IDEA導(dǎo)入JDBC驅(qū)動(dòng)的jar包,需要的朋友可以參考下2023-07-07
這篇文章主要介紹了Spring interceptor攔截器配置及用法解析,文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-10-10
這篇文章主要介紹了JSP代碼實(shí)現(xiàn) 金字塔(倒置)示例,需要的朋友可以參考下2014-02-02