Spring Data Jpa Join with multiple table without @Query

Question

I am newbie for Spring boot especially for Spring Boot Data JPA

I want to write JPA method without @Query

I know how to select through @Query However, there are no much about pure data JPA method like

Integer findByLevelPointBetweenSomethingAndSomething2(Integer userId);

 @Entity
 @Table(name = "point")
 public class Point {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "point_id", nullable = false)
private Integer pointId;

@Column(name = "user_id", nullable = false)
private Integer userId;

@Column(name = "point", nullable = false)
private Integer point;

@Column(name = "user_account_point_type_id", nullable = false)
private Integer pointTypeId;

@Column(name = "date_created", nullable = false)
private Date createdDate;

This is Point Entity which store point

and Level entity

 @Entity

 @Table(name = "level", schema = "public")
  public class Level {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "level_id", nullable = false)
private Integer levelId;

@Column(name = "user_id", nullable = false)
private Integer userId;

@Column(name = "level_type_id", nullable = false)
@JoinColumn(name ="level_type_id")
private Integer levelTypeId;


@Column(name = "reg_date", nullable = false)
private Date regDate;

and level type is consisted as 4 type (1-bronze/2-silver/3-gold/4-platinum) i level type is checked when point is stored

and this is the query that i make

select ult.level_type_id from level_type ult where (select sum(up.point) from level ull,point up,level_type ult where ull.level_type_id=ult.level_type_id and ull.user_id=up.user_id and up.user_id= 73) between ult.level_start_point AND ult.level_end_point

result is level_type as Integer form

this query i want to make as JPA @Query() X!!!

LIKE THIS @Repository

 public interface LevelTypeRepository extends 
JpaRepository<LevelType,Integer> {

Integer findByLevelPointMaxBetweenLevelPointMinAndLevelPointMax(Integer 
userId);

}

However it is not working and don't know how to join through three tables with one parameters Moreover, parameter(user_id) between level_start_point and level_end_point

Is it difficult to express through JPA ?

Dhrumil Patel · Accepted Answer · 2019-04-05 07:55:30Z

3

Yes, It is not possible without @Query.

you have to go for either JPA QL or HQL.

Instead of the direct database table, it uses Java entity class which are mapped with database tables.

you are using table name in your query which is wrong.

Use the Entity class name instead of the table name and use the value of the entity's property instead of the table's column name.

For Example

@Query("SELECT l.levelId FROM Level l where l.userId = :uid")

answered Apr 5, 2019 at 7:55

Dhrumil Patel

3621 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Defaulter · Accepted Answer · 2019-04-05 07:21:44Z

0

Does database has this join relationship i.e is there foreign_key Primary_Key relation in DB between the tables ? If yes, you can do it without using @Query annotation. If not, JPA will not join table by itself thus a custom query is required and @Query tag becomes necessary.

answered Apr 5, 2019 at 7:21

Defaulter

3781 gold badge4 silver badges17 bronze badges

Collectives™ on Stack Overflow

Spring Data Jpa Join with multiple table without @Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related