Encapsulation operation of SpringDataJPA native sql query mode
- 2021-09-16 06:46:22
- OfStack
Tool class related code
map2bean tool class import method using apache
<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;
/**
* Will query results map Packaged into corresponding javaBean Support cascading But the attribute cannot be duplicated
* Corresponding javaBean The property name of must be named as a small hump, otherwise the data cannot be filled
*/
public class Map2Bean {
private Map2Bean() {
}
/**
* Will map Data is encapsulated into javaBean
*
* @param map Map Type data
* @param clazz To be converted JavaBean
* @param <T> Generic type
* @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(" Not yet realized ");
}
}
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;
/**
* By: guoyzh
* Time: 2019/8/20 12:53
* Function: Use jpa Conducting native sql Tool class for query Use AutoWrite Injection can be used
*/
@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;
}
/**
* Returns the of the query 1 A Record If there is no 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);
}
/**
* Returns the of the query 1 Entities, otherwise 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);
}
/**
* Query list
*
* @param sql native sql Statement, which can contain?
* @param clazz The type returned, which can be JavaBean , can be Record
* @param params Parameter list
* @param <T> Generic type
* @return Query list results
*/
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;
}
/**
* Query list
*
* @param sql native sql Statement, which can contain : Named parameter
* @param clazz The type returned, which can be JavaBean , can be Record
* @param searchMap Named parameter list
* @param <T> Generic type
* @return Query list results
*/
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 Whether to include Group by Statement that affects the total number of rows
* @param nativeSQL Native SQL Statement {@see QueryHelper}
* @param nativeCountSQL Native to find the number of total rows SQL Statement {@see QueryHelper}
* @param clazz JavaBean Stylistic DTO Or Record , you need to use an alias to follow JavaBean Correspondence
* @param <T> Return JavaBean Stylistic DTO Or Record
* @param params Give conditions in order
*/
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);
// Pit dead people, 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);
// Pit dead people, 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 Whether to include Group by Statement that affects the total number of rows
* @param nativeSQL Native SQL Statement {@see QueryHelper}
* @param nativeCountSQL Native to find the number of total rows SQL Statement {@see QueryHelper}
* @param clazz JavaBean Stylistic DTO Or Record , you need to use an alias to follow JavaBean Correspondence
* @param <T> Return JavaBean Stylistic DTO Or Record
* @param searchMap k-v Condition
*/
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 And subclasses
} else if (Map.class.isAssignableFrom(clazz)) {
list.add(tmp);
//JavaBean Style
} 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){
// Return 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 {
// Return JavaBean
// You can only return simple Javabean , does not have cascading characteristics
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("");
}
}
Call in code
@Autowired
SqlUtils mSqlUtils;
. . .
@Transactional
@ApiOperation(" Test ")
@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 use native SQl, pits to pay attention to
Explain according to the code:
@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);There are several methods to pay attention to:
1. From does not support renaming.
2. 1 page is returned < Object[] > The array only stores data, and there is no corresponding key. It can only be injected into DTO in turn according to the order of returning data.
3. For using paging, you need: "ORDER BY? # {# pageable}", which can directly pass in an pageable object and will be parsed automatically.
4. Pay attention to the format problem. Most of the time, there is no space when wrapping lines.
5. Carefully correspond to the table fields in the database. Most of the time, a field cannot be found because the field name is incorrectly written and does not correspond to the database.
6. This is to solve the problem of using micro-services, and a large amount of data needs to be called remotely, which will reduce the performance of programs.
7. When using Pageabel as a parameter, paginate. At the beginning, I think it is still a feasible method, but I have to pay attention to it. When I need to sort, I can't add sort field. Will 1 directly report error left*.
8. According to the solution of 7, the data query of native SQL and countQuery are divided into two query methods.
Get count, and then judge, if it is equal to 0, it directly returns an empty set; On the contrary, the obtained data is taken. You need to do your own paging calculation and pass in the correct pageNumber and pageSize.
Most systems sort in descending order according to modification time. Therefore, order by can be written to death.
Then pageNumber and pageSize are dynamically passed in. The algorithm of pageNumber = (pageNumber-1) * pageSize, provided that PageNumber starts from 1, and if 0, pageNumber = pageNumber * PageSize; In this way, the data can be guaranteed to be correct.
/**
* pageInfos: Data after conversion.
* pageable Incoming pageable.
* totalPage: No. 1 1 Article SQL Calculated return value.
* In this way, it can be unified 1 Returns various kinds of pageDTO .
*/
private Page<T> convertForPage(List<T> pageInfos, Pageable pageable, Integer totalPage) {
return new PageImpl<>(pageInfos, pageable, totalPage);
}