Solution to the Failure of existsBy Query Method of Spring Boot JPA Repository

Introduction: Spring Boot is known as a sharp weapon for micro-service. After combining Spring Data and JPA, it is even more powerful, and it is unreasonable to develop quickly. This article will tell a diagnosis and analysis process and repair method of a strange problem in JPA.

Environmental introduction

JDK 1.8 Spring 4.2 Spring Boot 1.5.9

Problem description

In the Repository interface in Spring Data, an interface method is created to check the existence of data:

@Repository
public interface VideoEntityRepository extends JpaRepository<VideoEntity, Long> {
    ........
    public boolean existsByUserIdAndName(long userId, String name);
   }

The classes for VideoEntity are as follows:

@SuppressWarnings("serial")
@Table(name="flook_video")
@Entity
@Data
@EqualsAndHashCode(callSuper=true)
public class VideoEntity extends BaseEntity {
    @Column(name="user_id")
    private long userId;
    @Column(name="name")
    private String name;
    @Column(name="change_version")
    private double changeVersion;
}

When the method existsBy method is called, the result returned is false, which is incorrect. In my expectation, its execution result should not be wrong? So what's the problem?

Introduction to existsBy

Spring Data provides a number of very practical extensions, which realize the daily CRUD operations of database tables well, and provides a number of extension mechanisms. Based on a set of simple and easy-to-use naming rules, it realizes the database query operation of the scenario declaratively:

countByColumName
existsByColumnName

Spring Data helps dynamically generate SQL in both ways.

Based on @ Query mode

In addition to the two methods based on countBy/existsBy, you can directly use @ Query to mark specific SQL or JPQL to realize functions. Because this method needs some SQL, the functions are completely covered, but the workload is slightly large, which is not the best way.

Problem analysis

Because the underlying layer uses the implementation of the data access layer implemented by Hibernate/JPA/Spring Data, the best way of course is to look at the dynamically generated SQL, and find the generated SQL statement:

select videoentit0_.id as col_0_0_ from video videoentit0_ where videoentit0_.user_id=? and videoentit0_.name=? limit ?

The name of the table is video, which is incorrect. How did this happen?

The solution of a problem

After analysis, it is found that there are two classes with the same class name VideoEntity in the code. Although we do not reference errors in Repository, the problem should be that when Spring Data encounters two identical class names, it actually does not know how to generate SQL. In other words, it should dynamically generate and execute SQL based on class name instead of class path.

TODO: Find relevant parts based on the source code.

Problem solving

Rename the current VideoEntity to ensure that there is no duplicate name problem, even if the path of the class is not 1, but the class name is 1, it will also cause such a problem.

From one aspect, all DataBean in Spring Data need to use the class name of full path, otherwise there will be problems as well.

JpaRepository Query Specification

1. JpaRepository supports interface specification method name query

It means that if the query method defined in the interface conforms to its naming convention, it can be implemented without writing. Currently, the supported keywords are as follows.

Keyword	Sample	JPQL snippet
IsNotNull	findByAgeNotNull	... where x.age not null
Like	findByNameLike	... where x.name like ?1
NotLike	findByNameNotLike	... where x.name not like ?1
StartingWith	findByNameStartingWith	... where x.name like ?1(parameter bound with appended %)
EndingWith	findByNameEndingWith	... where x.name like ?1(parameter bound with prepended %)
Containing	findByNameContaining	... where x.name like ?1(parameter bound wrapped in %)
OrderBy	findByAgeOrderByName	... where x.age = ?1 order by x.name desc
Not	findByNameNot	... where x.name <> ?1
In	findByAgeIn	... where x.age in ?1
NotIn	findByAgeNotIn	... where x.age not in ?1
True	findByActiveTrue	... where x.avtive = true
Flase	findByActiveFalse	... where x.active = false
And	findByNameAndAge	... where x.name = ?1 and x.age = ?2
Or	findByNameOrAge	... where x.name = ?1 or x.age = ?2
Between	findBtAgeBetween	... where x.age between ?1 and ?2
LessThan	findByAgeLessThan	... where x.age < ?1
GreaterThan	findByAgeGreaterThan	... where x.age > ?1
After/Before	...	...
IsNull	findByAgeIsNull	... where x.age is null

2. JpaRepository related query function

a. Spring DataJPA framework will intercept the redundant prefixes of method names, such as find, findBy, read, readBy, get and getBy, and then parse the rest.

b. If you create the following query: findByUserDepUuid (), when the framework parses the method, it first eliminates findBy, and then parses the remaining attributes, assuming that the query entity is Doc.

1: First, judge whether userDepUuid (according to POJO specification, the first letter becomes lowercase) is an attribute of the query entity, if so, it means that the query is carried out according to the attribute; If there is no such attribute, continue to step 2;

2. Intercept the string beginning with the first uppercase letter from right to left here is Uuid), then check whether the remaining string is one attribute of the query entity, if so, it means that the query is carried out according to the attribute; If there is no such attribute, repeat step 2 and continue to intercept from right to left; Finally assume user is one attribute of the query entity;

3: Then processing the remaining part (DepUuid), first judging whether the type corresponding to user has depUuid attribute, if so, it means that the method finally queries according to the value of "Doc. user. depUuid"; Otherwise, continue to intercept from right to left according to the rules of step 2, and finally indicate that the query is based on the value of "Doc. user. dep. uuid".

4: There may be a special case, for example, Doc contains one user attribute and one userDep attribute, which will lead to confusion. You can explicitly add "_" between attributes to express the intention explicitly, such as "findByUser_DepUuid ()" or "findByUserDep_uuid ()"

c. Special parameters: You can also add paging or sorting parameters directly to the parameters of the method, such as:

Page<UserModel>findByName(String name, Pageable pageable);
List<UserModel>findByName(String name, Sort sort);

d. NamedQueries of JPA can also be used as follows:

1: Use @ NamedQuery on entity classes:

@NamedQuery(name ="UserModel.findByAge",query = "select o from UserModel
o where o.age >=?1")

2: Define a method with the same name in the Repository interface of the DAO implemented by ourselves. The example is as follows:

publicList<UserModel> findByAge(int age);

3: Then you can use it. Spring will first find out if there is an NamedQuery with the same name. If there is, it will not be resolved according to the method defined by the interface.

e. You can also use @ Query to specify local queries by setting nativeQuery to true, for example:

@Query(value="select* from tbl_user where name like %?1" ,nativeQuery=true)
publicList<UserModel> findByUuidOrAge(String name);

Note: The current version of local query does not support page turning and dynamic sorting

f. Use named parameters, just use @ Param, such as:

@Query(value="selecto from UserModel o where o.name like %:nn")
publicList<UserModel> findByUuidOrAge(@Param("nn") String name);

g. Also supports the Query statement of the update class, just add @ Modifying, such as:

@Modifying
@Query(value="updateUserModel o set o.name=:newName where o.name like %:nn")
public intfindByUuidOrAge(@Param("nn") String name,@Param("newName")String
newName);

Note:

1: The return value of the method should be int, indicating the number of rows affected by the update statement

2: Transactions must be added at the place of call, and no transaction can be executed normally

f. Order in which queries are created

When Spring Data JPA creates a proxy object for an interface, which strategy should it take first if it finds that multiple of the above conditions are available at the same time?

< jpa:repositories > The query-lookup-strategy properties are provided to specify the order of lookups.

It has the following three values:

1: create-if-not-found: If the method specifies a query statement via @ Query, the query is implemented using that statement; If not, find out if a qualified named query is defined, and if so, use the named query; If neither is found, the query is created by resolving the method name. This is the default value for the querylookup-strategy property

2: create: Create a query by parsing method names. Even if there is a matching named query, or the method passes through the

Query statements specified by @ Query will be ignored

3: use-declared-query: If the method specifies a query statement via @ Query, the query is implemented using that statement; If not, find out if a qualified named query is defined, and if so, use the named query; If neither is found, an exception is thrown