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();本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
