Spring-Boot-JPA @Query动态拼接 ， jpql多表联查，只查询几个字段的结果封装

package com.demo.jpa_query_test.respository;

import com.demo.jpa_query_test.model.Stu;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.Page;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.jdbc.core.JdbcTemplate;

import java.awt.print.Pageable;
import java.util.List;
/**
 * 一个学生和班级多对一
 * 一个学生和汽车多对多
 */
public interface StuRespository extends JpaRepository<Stu,Integer> {
    //这是接口不能注入jdbcTemplate 要想用自己新建个studao类 可以把jdbcTemplate和StuRespository都注入进入


    @Override
    <S extends Stu> S save(S s);

    @Override
    List<Stu> findAll();

    //多对一查询，想要查询学生 根据其所在的班级id
    //inner join为内连接查询，左右都必须有值，右边的IClass必须返回实体，不然查询的结果为bull
    //不用写外键关系 s.iclass c 包含了外键关系，右表还必须这样写
    @Query(value = "select  s from Stu  s inner join s.iclass c where c.id = :size ")
    List<Stu> findByCarSize(@Param(value = "size") Integer size);

    //多对一查询，想要查询学生 根据其所在的班级id
    //left join为内连接查询，以左边为基础，右边的IClass可以返回null
    //不用写外键关系 s.iclass c 包含了外键关系，右表还必须这样写
    @Query(value = "select  s from Stu  s left join s.iclass c where c.id = :size ")
    List<Stu> findByCarSize2(@Param(value = "size") Integer size);



    /**转换后的hibernate语句
     * Hibernate: select stu0_.id as id1_2_, stu0_.c_id as c_id3_2_, stu0_.name as name2_2_ from stu stu0_ where (select count(carlist1_.stu_id) from sut_car_relation carlist1_ where stu0_.id=carlist1_.stu_id)>=?
     * //多对多查询，原始的sql要查询左表+中间表  或者右表+中间表
     *     //jpql 有size() concate()等函数可用
     *     //查询拥有汽车数大于2的学生
     */
    @Query(value = "select  s from Stu  s where size(s.carList)>= :size ")
    List<Stu> findByCarSize3(@Param(value = "size") Integer size);

    /**
     * 使用原生的sql 查询学生 按照拥有的汽车数排序，学生和汽车多对多
     * 先使用子查询 得到stu_id 和 统计数这个中间表 c 拿这个中间表（带汽车统计个数和学生id）和学生表内连接 得到交叉集合
     *
     * @return
     */
    @Query(value = " select s.* from stu s ,(select sc.stu_id ,count(sc.stu_id) as coun_t from sut_car_relation sc group  by sc.stu_id) as c where s.id = c.stu_id order by c.coun_t asc" ,nativeQuery = true)
    List<Stu> findByCarSize4();

    /**
     * 使用jpql查询没有 车的学生   is null 判断null   is empty只能判断实体的集合属性是否为空
     * @return
     */
    @Query(value = "select  s from Stu  s  where  s.carList is empty ")
    List<Stu> findByCarSize6();
    /**
     * 使用jpql就简单的多了  直接如下
     * @return
     */
    @Query(value = "select  s from Stu  s  order by size(s.carList) desc")
    List<Stu> findByCarSize5();
    /**
     * spring boot分页
     *  前边传入分页参数
     *       Pageable pageable = new PageRequest(currentPage, pagesize, Sort.Direction.DESC, "keywords");
     *       currentPage从0页开始
     */
    @Query(value = "from Stu  s  where  1=1")
    List<Stu> findByIPage_w();

    /**
     * 使用自带的分页page 该对象里面总条数。。这些都有了
     * //这是spring boot 2.0 pageable必写全路径，不然就
     * @Param on all parameters except Pageable and Sort typed once, or none at all_。。
     * @param name
     * @param pageable
     * @return
     */
    @Query(value = "select s from Stu s where s.name = :name")
    Page<Stu> findAllTest1(@Param(value = "name") String name,org.springframework.data.domain.Pageable pageable);

    /**
     * jpql 支持动态传参数，?1  或者:name 这种，同时只能使用一种
     *
     */


    @Query(value = "from Stu  s  where s.name = ?1 and s.id = ?2")
    Stu findByNameAndId(String name,String id);

    /***
     * 动态sql
     * 原始的sql
     * IF( expr1 , expr2 , expr3 )  expr1 的值为 TRUE，则返回值为 expr2 ,expr1 的值为FALSE，则返回值为 expr3
     * 例如   if(/"/"=:name,1=1, s.name like concat('%',:name,'%'))
     *        if(:stae is null,1=1, s.state = :state)
     *
     *  @Query(value = "select * from  tag_info T  where (1=1  and if(:tagState is null ,1=1,T.tag_state=:tagState)  and T.tag_type in :tagType and if(\"\"=:content,1=1,T.name like CONCAT('%',:content,'%'))) or (1=1 and T.id in :ids) order by :orderFiled :px limit :start , :ed", nativeQuery = true)
     *
     * IFNULL( expr1 , expr2 )   在 expr1 的值不为 NULL的情况下都返回 expr1，否则返回 expr2
     */
    //在拼接sql的过程中，传来的参数有字符串，Integer Boolean等 都可以判断为"" 或者为null
    //但 in 集合或者数组，没法判断 他们也可能为空，in () 报错 ，前台可以给个默认值，默全部，如果前台传来的为空 就用全部，不为空就用前台的，或者故意往集合添加一个无关紧要的

    /**
     * jpql无法动态拼接sql只能用原始的
     */


  //删除更新操作 要加@Modifying
    @Query("delete from Stu s where  s.name = ?1")
    @Modifying
    void delByName(String name);


}

只查询几个字段，让结果封装到到List<Map<String,Object>>中，或者封装到list<Object[ ]> 中，不支持自定义封装的vo  ，而且实体的实体的字段必须每个都有，不然封装就报错，

排除用实体封装几个字段的思想

public class Test(){
//类中注入 实体管理器，用实体管理器查询  返回的结果 List<Map<String, Object>> 有字段名字
    @PersistenceContext(unitName = "manageFactory")
    protected EntityManager em;
    public List<Map<String, Object>>getListMap(String sql){
        Query nativeQuery=em.createNativeQuery(sql);
        nativeQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List resultList=nativeQuery.getResultList();
        return resultList;
        }

}

 上边这种用实体管理器的原生查询，没有加入设置参数，下边是优化过的，可以防止sql 注入，还可以设置分页

@PersistenceContext
    EntityManager entityManager;
 
 @Override
 public Object findMeetingSignRecoredPage(Long meetingId, Integer pageIndex, Integer pageSize) {
 
        MeenoAssert.notNull(meetingId, ErrEnum.MEETING_ID_IS_EMPTY);
        Meeting meeting = this.meetingRepository.getOne(meetingId);
        MeenoAssert.notNull(meeting,ErrEnum.MEETING_IS_NOT_EXIST);
 
        Pageable pageable = PageUtils.getPageable(pageIndex,pageSize);
 
        StringBuilder sql = new StringBuilder();
        sql.append(" select ep.name,MAX(r.sign) from mnt_emp_rel_meeting as e  ");
        sql.append(" left join mnt_sign_record as r on(r.employee_id=e.employee_id)  ");
        sql.append(" left join mnt_employee as ep on(ep.id = e.employee_id) ");
        sql.append(" where e.meeting_id = ? ");
        sql.append(" order by  r.sign desc,r.create_date asc ");
 
 
        Query query = entityManager.createNativeQuery(sql.toString());
 
        query.setFirstResult(pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
        query.setParameter(1,meetingId);
 
        List<Object[]> list = query.getResultList();
        List<SignRecordView> listView = Lists.newArrayList();
 
        if(list != null && !list.isEmpty()){
            for (Object[] objects : list) {
                String empName = objects[0]==null?null:objects[0].toString();
                Integer sign = objects[1]==null?Constants.SIGN_RECORD_NO:Integer.parseInt(objects[1].toString());
                SignRecordView view = new SignRecordView();
                view.setEmployeeName(empName);
                view.setSign(sign);
                listView.add(view);
            }
        }
 
        //count
        StringBuilder countSql = new StringBuilder();
        countSql.append(" select count(distinct e.id) from mnt_emp_rel_meeting as e  ");
        countSql.append(" left join mnt_sign_record as r on(r.employee_id=e.employee_id)  ");
        countSql.append(" left join mnt_employee as ep on(ep.id = e.employee_id) ");
        countSql.append(" where e.meeting_id = ? ");
        countSql.append(" order by  r.sign desc,r.create_date asc ");
 
        Query countQuery = entityManager.createNativeQuery(countSql.toString());
        countQuery.setParameter(1,meetingId);
        Object singleResult = countQuery.getResultList().get(0);
        Integer count = singleResult==null?0:Integer.valueOf(singleResult.toString());
 
 
        Map<String, Object> resultPage = PageUtils.getResultPage(count, listView);
 
        return resultPage;
    }

 //这种返回的每一行是个数组，没有字段名称
@Query(value = "select project_name,id from automatic_project  order by create_date desc",nativeQuery = true)
    List<Object[]> findAllProjectName();

如果查询几个字段，包含多表中的各个几个字段，也可以用jpql查询的 自定义Model

public class MyModel implements Serializable {

    private String userName;
    private String name;
    private String gender;
    private String description;

    public MyModel() {};

    public MyModel(String userName, String name, String gender, String description) {
        this.userName = userName;
        this.name = name;
        this.gender = gender;
        this.description = description;
    }
}
//pers.zhuch.model自己的包路径
@Query(value = "select new pers.zhuch.model.MyModel(u.userName, ui.name, ui.gender, ui.description) from UserInfo ui, User u where u.id = ui.userId")
public List<MyModel> getAllRecord();


//如果不想封装自己的model可以直接用 map
第二种方法：在service 里边转换成 JSON
原理其实和第一种方法差不多，只是处理结果的方式不太一样，只是这种方法我们就不在 hql 中 new Model 了，直接写查询方法

@Query(value = "select new map(u.userName, ui.name, ui.gender, ui.description) from UserInfo ui, User u where u.id = ui.userId")
public List<Map<String, Object>> getCustomField();

用接口形式的vo 来封装部分结果

    @Query(value = "SELECT p.project_name as projectName,count(t.id) as taskNum FROM automatic_project p ,automatic_task t WHERE p.id = t.project_id GROUP BY t.project_id /*#pageable*/ ",countQuery = "SELECT p.project_name as projectName,count(t.id) as taskNum FROM automatic_project p ,automatic_task t WHERE p.id = t.project_id GROUP BY t.project_id ",nativeQuery = true)
    Page<Myvo> getAllByPageVo(Pageable pageable);


    @Query(value = "SELECT p.project_name as projectName,count(t.id) as taskNum FROM automatic_project p ,automatic_task t WHERE p.id = t.project_id GROUP BY t.project_id /*#pageable*/ ",nativeQuery = true)
     List<Myvo> getAllByPageList();


//接口形式的vo   注意get字段名
package com.trs.jxwy.financial.web.vo.automatic;

public interface Myvo {
    Integer getTaskNum();
    String getProjectName();
}

//service

 PageRequest pageable = new PageRequest(pageNo - 1, pageSize, Sort.Direction.DESC, "create_date");
 Page<Myvo> allByPageVo2=automaticProjectRepository.getAllByPageVo(pageable);
 List<Myvo> allByPageList=automaticProjectRepository.getAllByPageList();