jpa@query对象sql，plsql

jpa@query对象sql

@Query是JPA中的一种注解，用于自定义查询语句。其主要作用是允许用户使用自己的SQL查询来获取数据，而不是直接从实体类中查询。详细步骤如下：

1.在Repository接口中定义一个抽象的方法。

2.在该方法上添加@Query注解，并填写自定义查询语句。

3.可以使用命名参数、占位符等方式来指定查询参数。

4.如果查询结果并不是实体类对象，则可以使用@SqlResultSetMapping和@ColumnResult注解将查询结果映射成实体类。

对于一般的查询，如果直接在@query主街上加上nativeQuery = true，经过我的测试，发现jpa对于手写sql并不是很友好，经常出现数据库可以查出来，但是手写sql却查不出来，这样我百思不得其解。（当然最后问题是找到了，因为我脚本生成数据的时候，没有给一个字段赋值，但是查询的时候却使用了这个字段，在数据库里面这个字段所有的值都为null值时，@query并不会去进行匹配）

我建议直接用plsql去查询，语法跟sql差不多，而且能做到通过对象进行查询，也是非常的方便（狗头）

下面直接上代码:

对于简单的查询

@Query(value = "select d from Dept d where d.ancestors = '1'")
    Dept find1(String name);

对于简单的修改

@Query(value = "update Dept d set d.ancestors = '1' where d.name like %?1% ")
    Dept set1(String name);

对于简单的删除

@Query(value = "delete Dept d  where d.name like %?1% ")
    Dept delete1(String name);

那么我们要实现多条件拼接查询该怎么使用呢，类似于mysql当中的函数ifnull(),find_in_set()

这个我也是考虑了很久，百度了各种解决方案。

首先对于复杂的查询

实现类似于mysql ifnull()

@Query(value = "select d from Dept d where (?1 ='' or d.name like %?1%) and (?2 = '' or d.isDeleted = ?2) and (?3 is null or d.updatedTime >= ?3) and (?4 is null or d.updatedTime < ?4) order by d.updatedTime")
    Page<Patient> selectComplex1(String name, String status, LocalDateTime startTimeByDay, LocalDateTime secondDayStartTime, PageRequest of);

这里一定要注意，and (【某个参数】 is null or d.updatedTime >= 【某个参数】)
表示这个值为nul的时候不进行拼接，如果不是null就走or后面的。

注意：

如果前段不传入这个值，那么这个值就是null，如果说参数的key拼接在了地址栏，那么这个参数就是个空穿，所以这里面is null和 =‘’ 是分开使用的。但是找了半天这个破玩意，真像给他一拳打s。
（关于这个问题跟前端沟通处理）

那么如何实现类似于mysql中find_in_set()函数的使用类似的需求呢
比如现在有一个字符串储存在数据库中是这样的格式：

String ancestors = "1,2,3,4,5";

然后我们要进行查询，可以这么写

@Query(value = "select d from  Dept d where d.ancestors like %?1%")
    Page<Dept> selectComplex1(String ancestor);

这时候前端传参 1，就能定位出来，但是问题并没有解决，假设有两条数据，算了不假设了，直接说结论
首先在数据库中，保证数据是这样的格式

String ancestors = "1,2,3,4,5,";

查询语句不变

@Query(value = "select d from  Dept d where d.ancestors like %?1%")
    Page<Dept> selectComplex1(String ancestor);

前端传参需要拼接上后面的,
例如：我想查询’1’，那么前端传参就要传’1,'（多了个逗号）

下面是jpql的通用函数

字符串函数

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
@Query(“SELECT u FROM User u WHERE LOWER(u.name) LIKE LOWER(:name)”)
List<User> findByNameIgnoreCase(@Param(“name”) String name);
}

上述示例中使用 LOWER 函数将用户姓名转换为小写字母，并通过 LIKE 进行模糊查询，以此实现不区分大小写的查询功能。

日期函数

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
@Query(“SELECT u FROM User u WHERE u.createTime >= :startTime AND u.createTime <= :endTime”)
List<User> findByCreateTimeBetween(@Param(“startTime”) LocalDateTime startTime, @Param(“endTime”) LocalDateTime endTime);
}

上述示例中使用 BETWEEN、AND、>=、<= 等关键字进行范围查询，即查询 createTime 在 startTime 和 endTime 之间的 User 对象。

数学函数
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
@Query(“SELECT AVG(u.age) FROM User u”)
Double getAverageAge();
}

上述示例中使用 AVG 函数计算 User 实体对象的平均年龄。

聚合函数
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
@Query(“SELECT COUNT(u) FROM User u WHERE u.enable = true”)
Long countEnabledUser();
}

排除查询
@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
@Query("SELECT o FROM Order o WHERE o.status = ‘Shipped’ "
+ “EXCEPT SELECT o1 FROM Order o1 WHERE o1.customer.name LIKE %:name%”)
List<Order> findShippedOrdersNotBelongingTo(@Param(“name”) String name);
}

上述示例中，我们使用了 EXCEPT 关键字来排除匹配查询条件的订单数据。实现了：“查找状态为已出货（Shipped）的订单数据，并排除所有属于“name”顾客的订单”。

需要注意的是，EXCEPT 关键字在 JPQL 语句中，并不是所有的 JPA 实现都支持，如 Hibernate 就不支持该关键字。如果需要实现这种功能，可以使用左外连接（LEFT OUTER JOIN）等其他方式来代替。

多表联合查询
 @Query(value = "select u from User u left join UserPath up on u.id = up.userId where (?1 ='' or u.name like %?1%" )
    Page<Patient> myApproval(String name);

多表联合查询（内连接）
 @Query(value = "select u from User u inner join UserPath up on u.id = up.userId where (?1 ='' or u.name like %?1%" )
    Page<Patient> myApproval(String name);

having用法
@Repository
public interface StudentRepository extends JpaRepository<Student, Long> {
@Query(“SELECT s.name,COUNT(e) FROM Student s JOIN Exams e WHERE e.grade < 60 GROUP BY s HAVING COUNT(e) >= 3”)
List<Object[]> findStudentFailMoreThan3();
}