CODEWITHSUNDEEP
javapostgresqlhibernatehibernate-mapping
Coltin
Coltin

Reputation: 3794

Join two tables with hibernate

I am looking to create a DAO which represents a join of two tables with Java Hibernate. Here is the SQL I'd like to represent (Postgres 9.6 incase that matters):

SELECT tableOneValue, tableTwoValue
FROM table_one, table_two
WHERE table_one_filter = 2 AND table_one_id = table_two_id;

These tables have a OneToOne relationship.

Table1.java

@Entity
@Data
@Table(name="table_one")
public class TableOneDao implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "table_one_id")
    private int tableOneId;

    @Column(name = "table_one_value")
    private String tableOneValue;


    @Column(name = "table_one_filter")
    private int tableOneFilter;
}

Table2.java

@Entity
@Data
@Table(name="table_two")
public class TableTwoDao implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "table_twp_id")
    private int tableTwpId;

    @Column(name = "table_two_value")
    private String tableTwoValue;
}

I'm very new to hibernate so maybe this isn't the right way to think with it. What I would love to do is define a SomeDao class where I can do: daoManager.findAll(SomeDao.class, Pair.of("tableOneFilter", 2));

This would return a List<SomeDao> where we get all the rows that satisfy tableOneFilter == 2.

Upvotes: 1

Views: 1599

Answers (3)

Yogesh Ghimire
Yogesh Ghimire

Reputation: 442

You can write a JPQL statement which is much better. Here is the sample solution:

SELECT NEW com.test.package.dao(t1.valueOne, t2.valueTwo)
FROM table_one t1 JOIN table_two t2
WHERE t1.filter = 2 AND t1.id = t2.id;

Please refer to this link and jump to the section where it mentions Result Classes (Constructor Expressions). Hope it helps. Thanks.

Upvotes: 0

gagan singh
gagan singh

Reputation: 1611

Here is a JPA query which will work with your existing entity structure with the latest version of hibernate.

SELECT t1.tableOneValue, t2.tableTwoValue FROM TableOneDao AS t1 JOIN TableTwoDao AS t2 ON t1.table_one_id = t2.table_two_id WHERE t1.table_one_filter = ?

Upvotes: 0

Sebastian D&#39;Agostino
Sebastian D&#39;Agostino

Reputation: 1675

You need to use the @OneToOne and @JoinColumn annotation.

Pay special attention to the userDetail attribute mapping.

For example, the user class:

@Entity
@Table(name = "USERS")
public class User {

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   @Column(name = "USR_ID")
   private long id;

   @Column(name = "USERNAME", nullable = false, unique = true)
   private String username;

   @Column(name = "PASSWORD")
   private String password;

   @OneToOne(cascade = CascadeType.ALL)
   @JoinColumn(name="USR_DET_ID")
   private UserDetail userDetail;

   // Add Constructor, Setter and Getter methods
}

And this user details class:

@Entity
@Table(name = "USER_DETAILS")
public class UserDetail {

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   @Column(name = "USR_DET_ID")
   private long id;

   @Column(name = "FIRST_NAME")
   private String firstName;

   @Column(name = "LAST_NAME")
   private String lastName;

   @Column(name = "EMAIL")
   private String email;

   @Column(name = "DBO")
   private LocalDate dob;

   // Add Constructor, Setter and Getter methods
}

Check the full code here.

Upvotes: 2

Related Questions