JPA原生sql（动态条件，分页）

JPA原生sql查询（动态条件，分页）

JPA原生sql查询的几种方式

eg1：

dao层

@Query(value="    SELECT b.FirstName ,b.LandArea ,c.LandColor\n" +
        "    FROM\n" +
        "            (SELECT top 100 a.FirstName, Sum(a.LandArea) as LandArea  FROM dbo.XH_PlanCodeInfo a\n" +
        "   GROUP BY a.FirstName ORDER BY Sum(a.LandArea) DESC) b\n" +
        "    LEFT JOIN dbo.X_LandColor c on b.FirstName = c.LandTypeName",nativeQuery = true)
List<Object> getFirstPieDatas();

service层处理

List<Object> collection =  collection = planCodeInfoRepository.getFirstPieDatas()；
List<GisPie> gisPieList = new ArrayList<GisPie>();
for (Object o : collection) {
            GisPie gisPie = new GisPie();
            Object[] ol = (Object[]) o; //转换成对象数组
            gisPie.setName((String) ol[0]);
            gisPie.setValue(((BigDecimal) ol[1]).divide( new BigDecimal("1000000") ,2));
            gisPie.setItemStyle(new Color((String) ol[2]));
            gisPieList.add(gisPie);
}

eg2:

直接在service层里写sql

@Autowired
private EntityManager em;

StringBuilder dataSql = new StringBuilder("SELECT HouseType as name,  sum(Area_SH) as value FROM [dbo].[HF_ResidentialLandInfo] GROUP BY HouseType ORDER BY value DESC");
Query dataQuery = em.createNativeQuery(dataSql.toString(), GisPieSimple.class);
List<GisPieSimple> gisPieSimpleList = dataQuery.getResultList();

eg3:

原生sql加动态条件加分页，写在service层

public Page<XJHBuilding> findByPageAndParams(XJHBuildingSearchModel param, Pageable pageable) {
    StringBuilder dataSql = new StringBuilder("SELECT * FROM [dbo].[XJH_Building]");
    StringBuilder countSql = new StringBuilder("SELECT COUNT(1) FROM [dbo].[XJH_Building]");

    //拼接where条件
    StringBuilder whereSql = new StringBuilder(" WHERE 1 = 1");
    if (BaseUtil.stringNotNull(param.getBuildingName())) {
        whereSql.append(" and BuildingName LIKE  CONCAT('%',:buildingName,'%')");
    }

    if (BaseUtil.stringNotNull(param.getCompanyName())) {
        whereSql.append("and No in \n" +
                "(SELECT DISTINCT BuildingNo  FROM [dbo].[XJH_Company] \n" +
                "where CompanyName LIKE CONCAT('%',:companyName,'%'))");
    }

    if (BaseUtil.stringNotNull(param.getBusinessAddress())) {
        whereSql.append("and No in \n" +
                "(SELECT DISTINCT BuildingNo  FROM [dbo].[XJH_Company] \n" +
                "where BusinessAddress LIKE CONCAT('%',:businessAddress,'%'))");
    }

    //组装sql语句
    dataSql.append(whereSql);
    countSql.append(whereSql);

    //创建本地sql查询实例
    Query dataQuery = em.createNativeQuery(dataSql.toString(), XJHBuilding.class);
    Query countQuery = em.createNativeQuery(countSql.toString());

    //设置参数
    if (BaseUtil.stringNotNull(param.getBuildingName())) {
        dataQuery.setParameter("buildingName", param.getBuildingName());
        countQuery.setParameter("buildingName", param.getBuildingName());
    }

    if (BaseUtil.stringNotNull(param.getCompanyName())) {
        dataQuery.setParameter("companyName", param.getCompanyName());
        countQuery.setParameter("companyName", param.getCompanyName());
    }

    if (BaseUtil.stringNotNull(param.getBusinessAddress())) {
        dataQuery.setParameter("businessAddress", param.getBusinessAddress());
        countQuery.setParameter("businessAddress", param.getBusinessAddress());
    }

    //设置分页
    dataQuery.setFirstResult((int)pageable.getOffset());
    dataQuery.setMaxResults(pageable.getPageSize());

    BigInteger count =  new BigInteger(String.valueOf(countQuery.getSingleResult()));
    Long total = count.longValue();
    List<XJHBuilding> content = total > pageable.getOffset() ? dataQuery.getResultList() : Collections.<XJHBuilding> 			emptyList();
    return new PageImpl<>(content, pageable, total);
}