JPA自定义sql
发布日期:2021-07-30 08:09:15 浏览次数:5 分类:技术文章

本文共 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);}

hql里不支持limit语法,可以使用native为true,写数据库sql。这时需要注意字段要是数据库字段了(下划线)。

4.推荐

能读到文章最后,首先得谢谢您对本文的肯定,你的肯定是对博主最大的鼓励。

你觉本文有帮助,那就点个👍

你有疑问,那就留下您的💬
怕把我弄丢了,那就把我⭐
电脑不方便看,那就把发到你📲

转载地址:https://blog.csdn.net/u011622109/article/details/108962313 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!

上一篇:BigDecimal正确使用了吗?
下一篇:JPA多条件动态查询

发表评论

最新留言

第一次来,支持一个
[***.219.124.196]2024年11月04日 19时45分40秒