JPA自定义sql-白红宇

JPA自定义sql

阅读量：4302 次

发布时间：2019-05-27

本文共 4299 字，大约阅读时间需要 14 分钟。

JPA的接口定义可以继承的接口有

Repository (总接口)

CrudRepository (CRUD简单接口)

PagingAndSortingRepository (分页排序接口)

JpaRepository (继承上面所有接口)

JpaSpecificationExecutor (条件查询接口)

jpa接口方法定义共三大类

默认的方法

自定义有规则名的方法

自定义sql

浩瀚的网络中，你我的相遇也是种缘分，看你天资聪慧、骨骼精奇，就送你一场大造化吧，能领悟多少就看你自己了。

1.默认方法

当类继承了上面几个接口后，就会有很多默认的方法

save

existsById

findById

saveAll

deleteById

findOne

findAll

count

示例

@Repositorypublic interface StudentRepository extends JpaRepository
   
     {
   }

配置

server.port=8080spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driverspring.datasource.url=jdbc:mysql://localhost:3306/xxx_tool?useUnicode=true&characterEncoding=UTF-8spring.datasource.username=iworkhspring.datasource.password=iworkh123spring.jpa.hibernate.ddl-auto=updatespring.jpa.show-sql=true

2.规则名方法

Keyword	Sample	JPQL snippet
`And`	`findByLastnameAndFirstname`	`where x.lastname = ?1 and x.firstname = ?2`
`Or`	`findByLastnameOrFirstname`	`where x.lastname = ?1 or x.firstname = ?2`
`Is`, `Equals`	`findByFirstname` `findByFirstnameIs` `findByFirstnameEquals`	`where x.firstname = ?1`
`Between`	`findByStartDateBetween`	`where x.startDate between ?1 and ?2`
`LessThan`	`findByAgeLessThan`	`where x.age < ?1`
`LessThanEqual`	`findByAgeLessThanEqual`	`where x.age <= ?1`
`GreaterThan`	`findByAgeGreaterThan`	`where x.age > ?1`
`GreaterThanEqual`	`findByAgeGreaterThanEqual`	`where x.age >= ?1`
`After`	`findByStartDateAfter`	`where x.startDate > ?1`
`Before`	`findByStartDateBefore`	`where x.startDate < ?1`
`IsNull`, `Null`	`findByAge(Is)Null`	`where x.age is null`
`IsNotNull`, `NotNull`	`findByAge(Is)NotNull`	`where x.age not null`
`Like`	`findByFirstnameLike`	`where x.firstname like ?1`
`NotLike`	`findByFirstnameNotLike`	`where x.firstname not like ?1`
`StartingWith`	`findByFirstnameStartingWith`	`where x.firstname like ?1` (parameter bound with appended `%`)
`EndingWith`	`findByFirstnameEndingWith`	`where x.firstname like ?1` (parameter bound with prepended `%`)
`Containing`	`findByFirstnameContaining`	`where x.firstname like ?1` (parameter bound wrapped in `%`)
`OrderBy`	`findByAgeOrderByLastnameDesc`	`where x.age = ?1 order by x.lastname desc`
`Not`	`findByLastnameNot`	`where x.lastname <> ?1`
`In`	`findByAgeIn(Collection ages)`	`where x.age in ?1`
`NotIn`	`findByAgeNotIn(Collection ages)`	`where x.age not in ?1`
`True`	`findByActiveTrue()`	`where x.active = true`
`False`	`findByActiveFalse()`	`where x.active = false`
`IgnoreCase`	`findByFirstnameIgnoreCase`	`where UPPER(x.firstame) = UPPER(?1)`

示例：

@Repositorypublic interface ToolsSysDao extends JpaRepository
   
    ,        JpaSpecificationExecutor
    
      {
       List
     
       findAllBySysNameOrderByOrderNumDesc(String sysName);}

根据SysName来查，并OrderNum字段进行降序。(注意驼峰)

entity实体类

@Entity@Table(name = "tools_sys", schema = "iworkh_tool")public class ToolsSysEntity {
       @Id    @Column(length = 32, name = "id")    private String id;    @Column(length = 20, name = "sys_name")    private String sysName;    @Column(length = 2, name = "order_num")    private int orderNum = 1;    @Column(name = "visible_flag")    private boolean visibleFlag = true;    @Column(length = 500, name = "description")    private String description;    ...省略setter、getter方法...}

3.自定义sql

通过名称规则的方式，可以处理比较简单的sql，如果特别复杂的sql。需要通过sql编写来完成。jpa里支持通过自定义sql来完成。

自定sql是指：在方法上使用@Query注解，然后写sql

@Query注解中两个常用的属性

value (不用多讲，定义sql)

nativeQuery (true表示数据的sql，false表示HQL，默认值是false)

3-1.hql

hql大白话讲: sql里字段用定义entity实体类的变量名(驼峰)，而不是数据库里的字段(下划线)

示例：

@Repositorypublic interface ToolsSysDao extends PagingAndSortingRepository
   
    ,        JpaSpecificationExecutor
    
      {
    @Query(value = "SELECT sysName as name FROM ToolsSysEntity WHERE visibleFlag=:visibleFlag ORDER BY orderNum DESC")    List
     
      
       > findSysNameByVisibleFlagOrderByOrderNumDesc(boolean visibleFlag);}

native默认值是false,即HSQL。字段名要是entity定义的变量名(驼峰)，表名是类名.

3-2.native

示例：

@Repositorypublic interface ToolsSysDao extends PagingAndSortingRepository
   
    ,        JpaSpecificationExecutor
    
      {
       @Query(value = "SELECT sys_name as name FROM logistics_tools_sys WHERE visible_flag=:visibleFlag ORDER BY order_num DESC limit :limitSize", nativeQuery = true)    List
     
      
       > findSysNameByVisibleFlagOrderByOrderNumDescLimit(boolean visibleFlag, int limitSize);}