CODEWITHSUNDEEP
javasqlhibernatehql
Jay
Jay

Reputation: 1678

Hibernate Query: Get data for Class from joined tables

I have 2 classes setup with Hibernate: (Simplified below for clarity)

ClassOne

@Entity
@Table( name = "SCHEMA.TBLONE" )
public class ClassOne {

@Id
@Column( name = "DCDINO" )
private String idField;
@Column( name = "DCGNUM", nullable = false )
private String fieldTwo;
@OneToOne( mappedBy = "classOne" )
@Cascade( value = org.hibernate.annotations.CascadeType.ALL )
private ClassTwo classTwo;

ClassTwo

@Entity
@Table( name = "SCHEMA.TBLTWO" )
public class ClassTwo {

@Id
@Column( name = "DDDINO", nullable = false )
private String idField;
@Column( name = "DDFRNM", nullable = false )
private String fieldTwo;
@OneToOne
@PrimaryKeyJoinColumn
private ClassOne classOne;

As can be seen, the 2 tables are joined and share the 'idField'.
Everything is joined together correctly and becomes populated as expected when I make a Query to the database like so: (With correct parameters)

Query query = session
    .createQuery( "FROM ClassOne WHERE 
idField= :idField AND fieldTwo = :fieldTwo" );
query.setString( "idField", idField );
query.setString( "fieldTwo", fieldTwo );

The problem I am having is that I now need to be able to populate ClassOne but without knowing the value of idField. I know the values of fieldTwo for both classes but have absolutely no idea how I would write this into the form of a Query.

I've had a look at Query by Example but have been unable to make it work. I'm aware that I could make a Query to each table with the individual variables and populate the object this way. But, I believe it would be quicker / easier / less work for the database if I could do this in just one.

Could anybody point me in the right direction to achieve this? I'm new to working with SQL and databases and don't know where to start looking.

Upvotes: 1

Views: 1122

Answers (3)

Leewhitty
Leewhitty

Reputation: 48

If I understand your question correctly you should be able to include fields from class two in your where clause like so:

Query query = session
    .createQuery( "FROM ClassOne c1 WHERE c1.classTwo.fieldTwo = :fieldTwo" );

query.setString( "fieldTwo", fieldTwo );

Upvotes: 2

Miles
Miles

Reputation: 110

I find the question quite confusing but if I understand correctly you might want to get the ClassOne from the relationship as follows:

The assumption is that you know ClassTwo's id

Query query = session
.createQuery( "SELECT c1 FROM ClassOne c1 JOIN ClassTwo c2 ON c1 = c2.classOne WHERE 
c2.fieldTwo= :fieldTwo );
query.setString( "fieldTwo", fieldTwo );

Upvotes: 0

Moataz Mohammad
Moataz Mohammad

Reputation: 36

you don't need join if you select from single entity, so your code may be just like this "FROM ClassOne WHERE fieldTwo = :fieldTwo"

Upvotes: 0

Related Questions