Shulou(Shulou.com)06/02 Report--

This article introduces you how to understand Spring Data JPA query methods and method name query rules, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Spring Data JPA query method and method name query rules

Spring Data JPA

Create a query by parsing the method name

When executing the query, the Spring Data JPA framework parses the method name. When parsing to prefixes such as get, getBy, find, findBy, read, readBy, it will first truncate these prefixes, and then parse the rest. The remaining part is divided into two types: one is only the attribute name, the other is the attribute name + condition. The conditions are easy to parse, and the key to parsing lies in attribute names. Here is a specific example to help you better understand the rules of attribute name resolution.

Example of parsing rules: for example, the entity is Product and the method is findByGoodsTypeDetail ()

1. First intercept the findBy, and then parse the remaining attributes

2. First determine whether goodsTypeDetail (according to the POJO specification, the first letter is lowercase, the same below) is an attribute of Product. If so, it means to query according to this attribute. If not, proceed to step 3.

3. Truncate the string at the beginning of the first uppercase letter from right to left (Detail in this method), and then compare whether the remaining string (goodsType in this method) is an attribute of Product. If so, query based on this attribute; if not, repeat the third step and continue to intercept from right to left (TypeDetail here, leaving goods), and loop to the end If goods is an attribute of Product, then goods is not a constant type, but an object type.

4. If the string TypeDetail is left, first determine whether there is a typeDetail attribute in the goods object. If so, it means that the method is finally querying according to the value of "Product.goods.typeDetail". If there is no such attribute, then continue to intercept from right to left according to the rule of step 3, and finally indicate that the query is based on the value of "Product.goods.type.detail".

However, this parsing rule is not perfect, and there is bug. If you don't pay attention to it, you may fall into this pit. For example, there is an attribute called goods in Product, and there is also an attribute called goodsType. There will be confusion during parsing, but you can add "_" between attributes to solve this problem. Note: "_" is added to the query method, not to the attribute name. For example, "findByGoods_TypeDetail ()" (when there is no goods_TypeDetail in Product, it tells the parser that Goods is an object) or "findByGoodsType_Detail ()" (when there is no goodsType_Detail in Product, it tells the parser that GoodsType is an object).

When querying, you often need to use multiple attributes to query at the same time, and the query conditions are also different. Spring Data JPA provides some keywords for conditional query. I have sorted out all the commonly used ones as follows:

Keyword

Corresponding to SQL keyword

Example

Column name

Query by column name

FindByName (String name); automatically parses the column names after findBy, and then queries based on the column names.

In

Equivalent to in in SQL

FindByNameIn (Collection nameList); parameters can be collections, arrays, indefinite length parameters

Like

Equivalent to like in SQL

FindByNameLike (String name)

NotLike is equivalent to not likefindByNameNotLike (String name) in SQL

And

Equivalent to and in SQL

FindByNameAndPwd (String name, String pwd)

Or

Equivalent to or in SQL

FindByIdOrCode (String id, String code)

Between

Equivalent to between in SQL

FindByNumBetween (int max, int min)

OrderBy

Equivalent to order by in SQL

FindByNameOrderByNumAsc (String name)

IsNull

Equivalent to is null in SQL

FindByNameIsNull ()

IsNotNull is equivalent to is not nullfindByNameIsNotNull () in SQL; NotNull is equivalent to is not nullfindByNameNotNull () in SQL;-- like IsNotNull, it is recommended to use IsNotNullNot equivalent to! = findByNameNot (String name) in SQL; NotIn is equivalent to not infindByNameNotIn (Collection nameList) in SQL; parameters can be sets, arrays, indefinite length parameters; LessThan is equivalent to SQL

FindByNumGreaterThan (int num)

Create a query using @ Query

1. Use the location number query provided by @ Query: the format is ": location number", and then the parameters in the method are written according to the location number order of the JPQL query statement. As follows:

Public interface ProductDao extends Repository {@ Query ("select * from Product p where p.id =? 1") public Product findById (Long id); @ Query ("select * from Product p where p.type =? 1 and p.name =? 2") public Page findByTypeAndName (Integer type,String name,Pageable pageable);}

2. Use @ Query named parameter query: format is ": variable", and use @ Param in front of the method parameter to correspond the method parameter to the named parameter in JPQL. As follows:

Public interface ProductDao extends Repository {@ Query ("from Product p where p.goodsname =: name") public Product findByGoodsName (@ Param ("name") String name); @ Query ("from Product p where p.num")

< :num") public Page findByNumLessThan( @Param("num")Integer num,Pageable pageable); } 3、 使用 @Modifying 将查询操作标识为更新操作：在使用 @Query 的同时使用 @Modifying ，这样会生成一个更新的操作，而非查询。如下： @Query("update Product p set p.name = ?1 where p.id = ?2") @Modifying public int updateName(String name, int id);JPA 常用查询方法记录 以这张表为例： +-------------+--------------+------+-----+-------------------+----------------+| Field | Type | Null | Key | Default | Extra |+-------------+--------------+------+-----+-------------------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || role | varchar(45) | NO | | NULL | || permissions | varchar(512) | NO | | NULL | || create_time | datetime | NO | | CURRENT_TIMESTAMP | || status | varchar(45) | NO | | NULL | || role_name | varchar(45) | NO | | NULL | |+-------------+--------------+------+-----+-------------------+----------------+CrudRepository 默认带的查询方法 @Repositorypublic interface RoleRepository extends CrudRepository {} @Entity@Table(name = "role", catalog = "message_push")public class RoleData implements java.io.Serializable { @Id @GeneratedValue private Integer id; private String role; private String permissions; private Long create_time; private Integer status; // getter setter 构造函数从略 }简单的扩展-以字段为关键字进行查询 list findByXXX(xxx) 其中 XXX 对应数据库中的字段，例如： @Repositorypublic interface RoleRepository extends CrudRepository { List findByRole(String role); List findByStatus(String status);} 还可以多字段AND 查询： @Repositorypublic interface RoleRepository extends CrudRepository { List findByRoleAndStatus(String role, String status);} 在 application.properties 中加入以下配置 spring.jpa.show-sql=true 可以看到SQL语句： Hibernate: select roledata0_.id as id1_0_, roledata0_.create_time as create_t2_0_, roledata0_.permissions as permissi3_0_, roledata0_.role as role4_0_, roledata0_.status as status5_0_ from message_push.role roledata0_ where roledata0_.role=? and roledata0_.status=? 当然 or 也是可以： List findByRoleOrStatus(String role, String status); Hibernate: select roledata0_.id as id1_0_, roledata0_.create_time as create_t2_0_, roledata0_.permissions as permissi3_0_, roledata0_.role as role4_0_, roledata0_.status as status5_0_ from message_push.role roledata0_ where roledata0_.role=? or roledata0_.status=? 使用@Query 进行复杂查询 例如： @Query(value = "select * from role where role = ?1", nativeQuery = true)List searchByRole(String role); 或 sql in 用法 @Query(value = "select * from role where role in (?1) and status = 'valid'", nativeQuery = true)List searchByRoleList(List targetList); 又或 sql like 用法： @Query(value = "select * from role where role like %?1%", nativeQuery = true)List searchByRole(String keyWord);使用 Specification 进行复杂查询 先来看一下 JpaSpecificationExecutor 接口 以 findAll(Specification) 为例进行说明： Specification 可以理解为一个查询条件。findAll 以这个条件为基准进行查询，也就是我们在sql 里写的 whre xxx 转为 Specification 来写。 首先要让我们的 repository 继承 JpaSpecificationExecutor @Repositorypublic interface RoleRepository extends CrudRepository, JpaSpecificationExecutor { 接下来，将这个查询 [ select * from role where role like '%a%' ] 转为一个简单的 Specification。 final Specification spec = new Specification () { @Override public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder criteriaBuilder) { Predicate predicate = criteriaBuilder.like(root.get("role"), "%a%"); return predicate; } }; 然后直接按如下方式调用即可： roleRepository.findAll(spec); Specification 里又衍生出了好几个类，分别介绍一下： Predicate 因为我们实现 Specification 接口时，只需要实现 Predicate toPredicate() 方法。而 Specification 上文中我们当做搜索条件来理解了，那么也可以简单的把 Predicate 视为搜索条件。 CriteriaBuilder 用于构建搜索条件 Predicater 的。 回想一下SQL搜索条件怎么写 where attribute = xxwhere attribute >

Xx where attribute

< xxwhere attribute like %xx% 注意这里有三要素： attribute 搜索指定的数据库字段 操作符 大于 小于 等于 具体数据 CriteriaBuilder提供了一系列静态方法构建这三要素。 比如 CriteriaBuilder.like(数据库字段, 具体数据) CriteriaBuilder.equal(数据库字段, 具体数据) 其中 数据库字段 不能直接写字符串，需要下一个工具类 Root 的 get 方法获取。 Root root.get( String attributeName ) 参数 attributeName 就是数据库里的字段名 现在相信读者可以理解 我们刚才写的 那个完整的 Specification了。 再下来再上一个稍微复杂点的例子： [ select * from role where role like '%a%' and (id >

11 or id < 8)] final Specification spec = new Specification () {@ Override public Predicate toPredicate (Root root, CriteriaQuery query, CriteriaBuilder criteriaBuilder) {Predicate roleLikeaPredicate = criteriaBuilder.like (root.get ("role"), "% a%"); Predicate idLessThan8Predicate = criteriaBuilder.lessThan (root.get ("id"), 8); Predicate idGreaterThan12Predicate = criteriaBuilder.greaterThan (root.get ("id"), 11) Predicate idCombindedPredicate = criteriaBuilder.or (idLessThan8Predicate, idGreaterThan12Predicate); Predicate predicate = criteriaBuilder.and (idCombindedPredicate, roleLikeaPredicate); return predicate;}}

In fact, it is very simple, that is, there is more criteriaBuilder.or criteriaBuilder.and to synthesize multiple Predicate into a new Predicate.

The last example:

You can return Predicate directly through root.get (xx) .in (List list).

Final Specification spec2 = new Specification () {@ Override public Predicate toPredicate (Root root, CriteriaQuery query, CriteriaBuilder criteriaBuilder) {List alist = new ArrayList (); alist.add ("admin"); Predicate predicate = root.get ("role") .in (alist); return predicate;}} On how to understand the Spring Data JPA query method and method name query rules to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.