How to execute native query with spring-data-jpa on entity with persistence relations

Question

I'm trying to use spring-data-jpa against a mysql 5.7 database to find entities using a regular expression. My native query on a jpaRepository method is producing errors.

I'm replacing an old custom-built c++ server used for licensing with Spring. I cannot change the database structure nor the api. I'm using spring-boot-starter-data-jpa:2.1.4.RELEASE, which users hibernate-core:5.3.9.Final and spring-data-jpa:2.1.6:RELEASE. My api implements the following endpoint: licenses/search/{fieldName}:{regex}/{limit}/{offset} for example: licenses/search/edition.name:"^Edition X$"/1/0

My DBLicense entity has a @OneToMany relationship with DBEdition.

At first I tried writing a query method in a LicenseRepository, as described here:

@Repository
public interface LicenseRepository extends JpaRepository<DBLicense, Long> {
...
List<DBLicense> findByEditions_NameRegex(String searchStr, Pageable pageRequest);
...
}

But I kept receiving the following error: unsupported keyword regex (1): [matchesregex, matches, regex]. The documentation indicates that regex might not be supported, and to check store-specific docs, which I could not find. Other answers led me to try the @Query annotation.

Because JPQL does not support regex, I opted to use the native query:

@Repository
public interface LicenseRepository extends JpaRepository<DBLicense, Long> {
...
    @Query(value = "select l.* from licenses as l join licenseeditions as le on l.LicenseID=le.LicenseID join editions as e on le.EditionID=e.EditionID where e.Name regexp :searchStr limit :offset, :limit", nativeQuery = true)
    List<DBLicense> findByEditions_NameRegex(@Param("searchStr") String searchStr, @Param("offset") Integer offset, @Param("limit") Integer limit);
...
}

and I receive the following error:

2019-07-18 11:46:50.145  WARN 24524 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: S0022
2019-07-18 11:46:50.146 ERROR 24524 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : Column 'ParentID' not found.

My DBLicense class:

@Entity
@Table(name = "licenses")
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class DBLicense {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "LicenseID")
...
    @ManyToOne
    @JoinTable(name = "licensekinships", joinColumns = @JoinColumn(name = "ChildID", referencedColumnName = "LicenseID"), inverseJoinColumns = @JoinColumn(name = "ParentID", referencedColumnName = "LicenseID"))
    private DBLicense parentLicense;
...
    @OneToMany
    @JoinTable(name = "licenseeditions", joinColumns = @JoinColumn(name = "LicenseID", referencedColumnName = "LicenseID"), inverseJoinColumns = @JoinColumn(name = "EditionID", referencedColumnName = "EditionID"))
    @Setter(AccessLevel.NONE)
    @Builder.Default
    private List<DBEdition> editions = new ArrayList<DBEdition>();
}

The query executes successfully in mysql (I checked the log), and an error is thrown sometime after it returns inside Spring.

Notice that none of the tables referenced in my @Query (i.e. licenses,licenseeditions,editions) contain a 'ParentID' column. 'ParentID' is found on licensekinships, which is the relationship table of the many to one relationship between licenses and licenses.

Does my native query need to account for all the other relationship annotations on DBLicense? That's problematic because there are a LOT (the built-in LicenseRepository findById method executes no less than 59 queries!).

Answer 1

If you're using the hibernate/javax.persistence relationship annotations on your entities (i.e. @OneToOne, @OneToMany, @ManyToOne, @ManyToMany), and you attempt to use native queries, then you may have the same issue as presented in the question post.

This will happen especially if you have a complex schema wherein one entity shares a relationship with another, which in turn has further relationships, and you're trying to return one of those entities from a native query. To fix this error, you will need to provide enough information in your native query for spring-data-jpa to resolve the relationships which are present in your entities.

For example, consider the following class objects:

@Entity
@Table(name = "entity_a")
public class EntityA {
  @Column
  private int entityA_field
  ...
  @ManyToOne
  private EntityB entityB
}

and

@Entity
@Table(name = "entity_b")
public class EntityB {
  @Column
  private int entityB_field
  ...
  @ManyToOne
  private EntityC entityC
}

the JpaRepository built-in findById method for a EntityA id might execute multiple database queries. Using a sql database, for example:

select a.*, b.* from entity_a as a left outer join entity_b as b on a.id = b.id;
select b.*, c.* from entity_b as b left outer join entity_c as c on b.id = c.id;

you will need to mimic that first query's joins and columns. Luckily, you can see the pseudo-sql generated by spring-data-jpa by turning on logging:

1. find and open your application.properties (or application.yaml) file. Usually is located in "src/main/resources".
2. Add the following line if not present: spring.jpa.show-sql=true, then save.
3. Make a repository for the entity returned by your native query. For example, if your native query returns an EntityA, then your repository might look like:

@Repository
public interface MyRepository extends JpaRepository<EntityA, Long> {}

4. Call your repository findById method (from a controller, or a test) and check the console output. A number of queries will be logged to the console. Your native query needs to provide the same columns and implement the same joins as the first of these queries.