spring data jpa 1.9/1.10 复杂sql分页查询

基于jpa

controller中创建Pageable实现类PageRequest

PageRequest pageRequest = new PageRequest(page, size,new Sort(Sort.Direction.DESC, "update_time") );//

dao代码如下

public interface DataNodeDao extends PagingAndSortingRepository<TDataNode, Long> {    @Query(value = "SELECT dn.* FROM data_node dn LEFT JOIN data_device_rel ddr ON dn.id = ddr.data_id LEFT JOIN device_station_rel dsr ON ddr.device_id = dsr.device_id LEFT JOIN user_station us ON dsr.station_id = us.station_id WHERE us.organization_id = ?1  AND dn.STATUS = ?2  /*#pageable*/",            countQuery = "SELECT count(dn.id) FROM data_node dn LEFT JOIN data_device_rel ddr ON dn.id = ddr.data_id LEFT JOIN device_station_rel dsr ON ddr.device_id = dsr.device_id LEFT JOIN user_station us ON dsr.station_id = us.station_id WHERE us.organization_id = ?1 AND dn.STATUS = ?2 ",nativeQuery = true)    Page<TDataNode> userDataNodeList(int organizationId, int status, Pageable page);    @Query("select dn from TDataNode dn where  dn.status=?1")    Page<TDataNode> userDataNodeList(int status, Pageable page);

第一种使用nativeQuery，需要加上#pageable，不然会报错，spring源码如下，如果要使用分页，必须包含#pageable或者#sort

//// Source code recreated from a .class file by IntelliJ IDEA// (powered by Fernflower decompiler)//package org.springframework.data.jpa.repository.query;import javax.persistence.EntityManager;import javax.persistence.Query;import org.springframework.data.repository.query.EvaluationContextProvider;import org.springframework.data.repository.query.Parameters;import org.springframework.expression.spel.standard.SpelExpressionParser;final class NativeJpaQuery extends AbstractStringBasedJpaQuery {    public NativeJpaQuery(JpaQueryMethod method, EntityManager em, String queryString, EvaluationContextProvider evaluationContextProvider, SpelExpressionParser parser) {        super(method, em, queryString, evaluationContextProvider, parser);        Parameters<?, ?> parameters = method.getParameters();        boolean hasPagingOrSortingParameter = parameters.hasPageableParameter() || parameters.hasSortParameter();        boolean containsPageableOrSortInQueryExpression = queryString.contains("#pageable") || queryString.contains("#sort");        if (hasPagingOrSortingParameter && !containsPageableOrSortInQueryExpression) {            throw new InvalidJpaQueryMethodException("Cannot use native queries with dynamic sorting and/or pagination in method " + method);        }    }    protected Query createJpaQuery(String queryString) {        return this.getQueryMethod().isQueryForEntity() ? this.getEntityManager().createNativeQuery(queryString, this.getQueryMethod().getReturnedObjectType()) : this.getEntityManager().createNativeQuery(queryString);    }}

如果使用第二种hql，则不用做额外处理

最终jpa会在sql末尾自动拼接
order by dn.update_time desc limit **

官网文档
https://docs.spring.io/spring-data/jpa/docs/current/reference/html/

基于JdbcTemplate

jpa的动态条件查询Specification太复杂，可读性比较差，还不如直接用jdbcTemplate，封装的类如下

package common.util;import org.springframework.core.convert.converter.Converter;import org.springframework.data.domain.Page;import org.springframework.data.domain.Pageable;import org.springframework.data.domain.Sort;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowMapper;import java.util.Iterator;import java.util.List;/** * Created by wmf on 2017/5/23. */public class JPAPageUtils<T> {    public  Page<T> getPage(RowMapper<T> mapper, JdbcTemplate jt, String sql, int currentPage, int pageSize){        int totalRows = jt.queryForObject("select count(*) from ("+sql+") t9", Integer.class);        int total;        // 总行数 % 每页行数 = 0        if (totalRows % pageSize == 0) {            total= totalRows / pageSize;        } else {            // 总行数 % 每页行数 != 0            total= totalRows / pageSize + 1;        }        List<T> list = jt.query("select t9.* from ("+sql+") t9 limit ?,?",                new Object[]{(currentPage * pageSize), pageSize},mapper);        Page<T> result= new Page<T>() {            @Override            public Iterator<T> iterator() {                return null;            }            @Override            public int getTotalPages() {                return total;            }            @Override            public long getTotalElements() {                return totalRows;            }            @Override            public int getNumber() {                return currentPage;            }            @Override            public int getSize() {                return pageSize;            }            @Override            public int getNumberOfElements() {                return 0;            }            @Override            public List<T> getContent() {                return list;            }            @Override            public boolean hasContent() {                return false;            }            @Override            public Sort getSort() {                return null;            }            @Override            public boolean isFirst() {                return this.getNumber()==0;            }            @Override            public boolean isLast() {                return this.getTotalPages()==(this.getNumber()+1);            }            @Override            public boolean hasNext() {                return false;            }            @Override            public boolean hasPrevious() {                return false;            }            @Override            public Pageable nextPageable() {                return null;            }            @Override            public Pageable previousPageable() {                return null;            }            @Override            public <S> Page<S> map(Converter<? super T, ? extends S> converter) {                return null;            }        };        return result;    }}

获取mapper的写法

public static RowMapper<TStation> getMapper(){        RowMapper<TStation> mapper=new RowMapper<TStation>() {            @Override            public TStation mapRow(ResultSet rs, int i) throws SQLException {                TStation s = new TStation();                s.setId(rs.getInt("id"));                s.setName(rs.getString("name"));                return s;            }            public boolean isExistColumn(ResultSet rs, String columnName) {                try {                    if (rs.findColumn(columnName) > 0 ) {                        return true;                    }                }                catch (SQLException e) {                    return false;                }                return false;            }        };        return mapper;    }

使用

JPAPageUtils<TStation> station=new JPAPageUtils<TStation>();...station.getPage(getMapper(),jdbcTemplate,sb.toString(),number,size);