spring boot 集成jpa 多种分页查询

一、查询分为两种：

    1、面向对象查询 

     2、自定义查询

1、面向对象查询简介

      引入jar包

<dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-commons</artifactId>
           <!-- <version>2.1.1.RELEASE</version>-->
        </dependency>
<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

创建查询dao层类

 server层

返回参数

 面向对象查询完成。

二、自定查询实现

利用EntityManager 实现自定义查询组装

import com.szhg.engineering.utils.ColumnToBean;
import com.szhg.engineering.utils.PageData;
import org.hibernate.query.internal.NativeQueryImpl;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import java.util.List;
import java.util.Map;

/**
 * <p>Description: </p>  
 * @author xu_jinhang
 * @versions 1.5.0
 * @date 2020/4/23 18:43
 */
@SuppressWarnings("all")
@Repository
@Service
public class PageHelper {

    @PersistenceContext(unitName = "default")
    protected EntityManager em;

    @Transactional(propagation = Propagation.SUPPORTS, rollbackFor = Exception.class)
    public <T> PageData<T> findByPageWidthSQL(String countSQL, String dataSQL, Map<String, Object> params, int pageNo, int pageSize, Class<T> clazz) {
        Query q1 = em.createNativeQuery(countSQL);
        Query q2 = em.createNativeQuery(dataSQL);
        if (params.size() > 0) {
            for (String key : params.keySet()) {
                q1.setParameter(key, params.get(key));
                q2.setParameter(key, params.get(key));
            }
        }
        long dataTotal = ((Number) q1.getSingleResult()).longValue();

        q2.setFirstResult((pageNo - 1) * pageSize).setMaxResults(pageSize);
        q2.unwrap(NativeQueryImpl.class).setResultTransformer(new ColumnToBean(clazz));
        List<T> dataList = q2.getResultList();
        PageData<T> pageData = new PageData<>(pageNo, pageSize, dataTotal, dataList);
        return pageData;
    }

    @Transactional(propagation = Propagation.SUPPORTS, rollbackFor = Exception.class)
    public <T> T findOneWidthSQL(String sql, Map<String, Object> params, Class<T> clazz) {
        Query q = em.createNativeQuery(sql);
        for (Map.Entry<String, Object> entry : params.entrySet()) {
            q.setParameter(entry.getKey(), entry.getValue());
        }
        List<T> list = q.unwrap(NativeQueryImpl.class).setResultTransformer(new ColumnToBean(clazz)).list();
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    @Transactional(propagation = Propagation.SUPPORTS, rollbackFor = Exception.class)
    public long countBySQL(String sql, Map<String, Object> params) {
        Query q = em.createNativeQuery(sql);
        if (params.size() > 0) {
            for (String key : params.keySet()) {
                q.setParameter(key, params.get(key));
            }
        }
        long dataTotal = ((Number) q.getSingleResult()).longValue();
        return dataTotal;
    }

    @Transactional(propagation = Propagation.SUPPORTS, rollbackFor = Exception.class)
    public <T> List<T> findListWidthSQL(String sql, Map<String, Object> params, Class<T> clazz) {
        Query q = em.createNativeQuery(sql);
        for (Map.Entry<String, Object> entry : params.entrySet()) {
            q.setParameter(entry.getKey(), entry.getValue());
        }
        List<T> list = q.unwrap(NativeQueryImpl.class).setResultTransformer(new ColumnToBean(clazz)).list();
        return list;
    }

    @Transactional(propagation = Propagation.SUPPORTS, rollbackFor = Exception.class)
    public <T> PageData<T> findByPageWidthHQL(St