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);