CODEWITHSUNDEEP
hibernatejpaormjpa-2.0jpql
lecarpetron dookmarion
lecarpetron dookmarion

Reputation: 711

How to translate this jdbc query to JPA (JPQL)

This is the query. I hope this would just be simple enough to convert. I'm just new to JPA, specifically JPQL and we wanted to convert this query.

select t1.field1, t1.field2, t1.field3 from TABLE_ONE t1, TABLE_TWO t2, TABLE_THREE t3 where t1.field2 = t2.field1 and t1.field3 = t3.field1 and t1.field1 = '123'

I have started creating the entity objects...

@Entity
@Table(name="TABLE_ONE")
public class TableOne {
       @Column(name="field1")
       private String field1;

       @Column(name="field2")
       private String field2;

Same for classes TableTwo and TableThree.

I'm confused if you need to use the @OneToMany or @OneToOne tag to translate that query in JPQL. I guess it won't be that practical to create those tags just for that specific query. This is JPA 2.0 by the way

Any help is appreciated. Thank you.

Upvotes: 0

Views: 1070

Answers (1)

MWiesner
MWiesner

Reputation: 9043

According to the JPA 2.0 specification (Section 11.1.6), you don't need to use the @OneToManyor @OneToOne annotation for class attributes of type String, as:

The Basic annotation is the simplest type of mapping to a database column. The Basic annotation can be applied to a persistent property or instance variable of any of the following types: Java primitive types, wrappers of the primitive types, java.lang.String, ..., java.util.Date, java.util.Calendar, java.sql.Date, java.sql.Time, java.sql.Timestamp, byte[], Byte[], char[], Character[], enums, and any other type that implements Serializable.

As described in Section 2.8, the use of the Basic annotation is optional for persistent fields and properties of these types. If the Basic annotation is not specified for such a field or property, the default values of the Basic annotation will apply.

This is guaranteed behavior by a JPA-conform ORM implementation. Moreover, as you use @Column annotations, the persistence for String class attributes is also given, however, @Column can be used to influence database specific properties (e.g., field length, name, nullable, etc...) of a certain attribute more explicitly.

The translation of the native JDBC query should work for you like so:

String jpqlConformQueryString = 
    "SELECT t1 from TableOne t1, TableTwo t2, TableThree t3 WHERE "+
    "t1.field2 = t2.field1 AND "+
    "t1.field3 = t3.field1 AND "+
    "t1.field1 = :field1";

// create a typed query to fetch only objects of that type
TypedQuery<TableOne> query = em.createQuery(jpqlConformQueryString, TableOne.class);

// set the value of the parameter "field1" to a value you choose...
query.setParameter("field1", "123");

// retrieve objects (transformed tuples) matching previously defined and parameterized query 
List<TableOne> result = query.getResultList();

// process the result list further 
// ...

This way, the list "result" will only reference objects that match the criteria as specified in the variable jpqlConformQueryString and as limited by the explicit parameter for t1.field1.

Note well: You don't have to select certain attributes like in plain JDBC code. Instead you select the whole object and use each object's getter/setter methods to access or manipulate the value that has been read from the database.

Upvotes: 3

Related Questions