JPA自定义sql

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

Repository (总接口)CrudRepository (CRUD简单接口)PagingAndSortingRepository (分页排序接口)JpaRepository (继承上面所有接口)JpaSpecificationExecutor (条件查询接口)

jpa接口方法定义共三大类

默认的方法自定义有规则名的方法自定义sql

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

1.默认方法

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

saveexistsByIdfindByIdsaveAlldeleteByIdfindOnefindAllcount…

示例

@Repository public interface StudentRepository extends JpaRepository<Student, String> { }

配置

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

2.规则名方法

KeywordSampleJPQL snippetAndfindByLastnameAndFirstnamewhere x.lastname = ?1 and x.firstname = ?2OrfindByLastnameOrFirstnamewhere x.lastname = ?1 or x.firstname = ?2Is, EqualsfindByFirstnamefindByFirstnameIsfindByFirstnameEqualswhere x.firstname = ?1BetweenfindByStartDateBetweenwhere x.startDate between ?1 and ?2LessThanfindByAgeLessThanwhere x.age < ?1LessThanEqualfindByAgeLessThanEqualwhere x.age <= ?1GreaterThanfindByAgeGreaterThanwhere x.age > ?1GreaterThanEqualfindByAgeGreaterThanEqualwhere x.age >= ?1AfterfindByStartDateAfterwhere x.startDate > ?1BeforefindByStartDateBeforewhere x.startDate < ?1IsNull, NullfindByAge(Is)Nullwhere x.age is nullIsNotNull, NotNullfindByAge(Is)NotNullwhere x.age not nullLikefindByFirstnameLikewhere x.firstname like ?1NotLikefindByFirstnameNotLikewhere x.firstname not like ?1StartingWithfindByFirstnameStartingWithwhere x.firstname like ?1 (parameter bound with appended %)EndingWithfindByFirstnameEndingWithwhere x.firstname like ?1 (parameter bound with prepended %)ContainingfindByFirstnameContainingwhere x.firstname like ?1 (parameter bound wrapped in %)OrderByfindByAgeOrderByLastnameDescwhere x.age = ?1 order by x.lastname descNotfindByLastnameNotwhere x.lastname <> ?1InfindByAgeIn(Collection ages)where x.age in ?1NotInfindByAgeNotIn(Collection ages)where x.age not in ?1TruefindByActiveTrue()where x.active = trueFalsefindByActiveFalse()where x.active = falseIgnoreCasefindByFirstnameIgnoreCasewhere UPPER(x.firstame) = UPPER(?1)

示例：

@Repository public interface ToolsSysDao extends JpaRepository<ToolsSysEntity, String>, JpaSpecificationExecutor<ToolsSysEntity> { List<ToolsSysEntity> 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实体类的变量名(驼峰)，而不是数据库里的字段(下划线)

示例：

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

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

3-2.native

示例：

@Repository public interface ToolsSysDao extends PagingAndSortingRepository<ToolsSysEntity, String>, JpaSpecificationExecutor<ToolsSysEntity> { @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<Map<String, String>> findSysNameByVisibleFlagOrderByOrderNumDescLimit(boolean visibleFlag, int limitSize); }

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

4.推荐

个人博客

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

你觉本文有帮助，那就点个👍 你有疑问，那就留下您的💬 怕把我弄丢了，那就把我⭐ 电脑不方便看，那就把发到你📲