Native query using querydsl and JPA

For complex queries JPA query will take a lot of execution time if the size of data is huge. Writing complex native query is easy but are error prone. We will learn how to create a type safe native query using query builder Querydsl.

For querying student whose name starts with “r”.

1) sql query

select * from student where name like 'r%';

2) sql query using Querydsl

qeury.select(student).from(student).where(name.startsWith("r"));

In above query, sql query is executable in sql command line where as querydsl query will be converted to sql query before executing in sql command.

Here lets take another example with complex query.

I am going to search Customer and its respective customer login

For example we take twp entity Customer and CustomerLogin.

Customer.class

@Getter
@Setter
@Entity
@Table(name = "CUSTOMER")
public class Customer extends implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "ID", nullable = false)
    private Long id;
    @Column(name = "FIRST_NAME", length = 50)
    private String firstName;
    @Column(name = "MIDDLE_NAME", length = 50)
    private String middleName;
    @Column(name = "LAST_NAME", length = 50)
    private String lastName;
    @Column(name = "ADDRESS", length = 100)
    private String address;
    @Column(name = "EMAIL_ADDRESS", length = 100)
    private String emailAddress;
    @Column(name = "ACTIVE")
    private Character active;
    
    @Column(name = "MOBILE_NUMBER", length = 15)
    private String mobileNumber;
   
    @OneToMany(mappedBy = "customer", fetch = FetchType.EAGER)
    @Fetch(FetchMode.SUBSELECT)
    private List<CustomerLogin> customerLoginList;

}

CustomerLogin.class

@Getter
@Setter
@Entity
@Table(name = "CUSTOMER_LOGIN")
public class CustomerLogin implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "ID", nullable = false)
    private Long id;
    @Basic(optional = false)
    @Column(name = "USERNAME", nullable = false, length = 20)
    private String username;
    @Column(name = "PASSWORD", length = 200)
    private String password;
  
    @JoinColumn(name = "CUSTOMER_ID", referencedColumnName = "ID")
    @ManyToOne()
    private Customer customer;
   
}

From above two table we need to find First Name and Mobile Number
from Customer table whose username in Customer Login table is “admin”.

1) Sql Query

SELECT c.first_name, 
       c.mobile_number 
FROM   customer C 
JOIN   CUSTOMER_LOGIN cl 
ON     cl.customer_id = c.id 
WHERE  cl.username = 'admin'

2. Query dsl

 protected QCustomer customer = new QCustomer("C");
 protected QCustomerLogin customerLogin = new QCustomerLogin("CL");

JPASQLQuery query = new JPASQLQuery(em);

query
.select(customer.firstName,customer.mobileNumber)
.from(customer)
.innerJoin(customerLogin)
.on(customerLogin.customerId.eq(customer.id))
.where(customerLogin.username.eq("admin"))
Above query dsl query will also generate the above sql query.

Querydsl sql maven

Mavne dependencies for querydsl. Here querydsl-jpa and querydsl-sql are completely different. Querydsl-jpa builds JPA Query whereas Querydsl-sql builds native sql query.

        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-core</artifactId>
            <version>4.2.1</version>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-apt</artifactId>
            <version>4.2.1</version>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-jpa</artifactId>
            <version>4.2.1</version>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-sql</artifactId>
            <version>4.2.1</version>
        </dependency>

        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-sql-codegen</artifactId>
            <version>4.2.1</version>
        </dependency>

Plugin

For querydsl to build query, it needs Qentities. i.e for every entity it will create Qentity. For the Customer and CustomerLogin it will generate QCustomer and QCustomerLogin.

To generate Qentities, following maven plugin must be used

<plugin>
                <groupId>com.mysema.maven</groupId>
                <artifactId>apt-maven-plugin</artifactId>

                <executions>
                    <execution>
                        <phase>generate-sources</phase>
                        <goals>
                            <goal>process</goal>
                        </goals>
                        <configuration>
                            <!-- Specifies the directory in which the query types are generated -->
                            <outputDirectory>target/generated-sources</outputDirectory>
                            <!-- States that the APT code generator should look for JPA annotations -->
                            <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
                        </configuration>
                    </execution>
                </executions>
            </plugin>

Here, outputDirectory tag defines the location where Qentities will be generated. Lets look at the code generated by querydsl.

QCustomer.class


/**
 * QCustomer is a Querydsl query type for Customer
 */
@Generated("com.querydsl.codegen.EntitySerializer")
public class QCustomer extends EntityPathBase<Customer> {

    private static final long serialVersionUID = 434834068L;

    private static final PathInits INITS = PathInits.DIRECT2;

    public static final QCustomer customer = new QCustomer("customer");

  public final ListPath<CustomerLogin, QCustomerLogin> customerLoginList = this.<CustomerLogin, QCustomerLogin>createList("customerLoginList", CustomerLogin.class, QCustomerLogin.class, PathInits.DIRECT2);

    public final StringPath emailAddress = createString("emailAddress");

    public final StringPath firstName = createString("firstName");
 }

Above code is generated by Querydsl for Customer entity class.