Spring Querydsl Tutorial

Spring Data JPA and Querydsl enable developers to write type-safe queries. In this tutorial, we will build a Spring boot JPA application to demonstrate how to achieve it.
 
 
 
 
 
 
 
 
 
 

1. Introduction

Spring Framework (Spring) is a Java application framework that provides many useful services for building applications. Spring’s JdbcTemplate provides easy access to the data stored in a relational database management system (RDBMS). Spring Data JPA significantly reduces the amount of boilerplate code required to implement data access layers for various persistence stores.

Querydsl is an extensive Java framework, which allows for the generation of type-safe queries in syntax similar to Structured Query Language (SQL). Querydsl enables the compiler to verify that the classes and properties used in a query exist, and the types used in the operand’s operators are acceptable. It also prevents the SQL injection security vulnerability.

In this example, I will demonstrate how to use Querydsl to access data in a RDBMS with or without Spring Data JPA. I will also demonstrate how developers can benefit from Querydsl’s type-safe feature.

2. Technologies Used

The example code in this article was built and run using:

• Java 1.8.101 (1.8.x will do fine)
• Maven 3.3.9 (3.3.x will do fine)
• Eclipse Mars (Any Java IDE would work)
• Spring data 1.5.10.RELEASE
• Hibernate 5.0.12.Final
• H2 1.4.196
• Querydsl 4.1.3

3. Spring Boot JPA Maven Project

The easiest way to generate a Spring-boot JPA application is via the Spring starter tool. Please check my other article here for more details. A maven project will be generated and downloaded to your workstation. Import it into your Eclipse work space. You should have no errors building it and running it as a Spring Boot Application.

We will demonstrate the usage of Querydsl with seven steps:

1. Add Querydsl dependencies
2. Create entity classes
3. Verify generated QObjects
4. Create JpaRopositories
5. Create services
6. Create Daos
7. Alter Querydsl Demo Application

3.1 Dependency

We will alter the pom.xml for two purposes:

• Include maven plug-in: apt-maven-plugin. It is used for generating the meta model classes from the entities which are annotated with @Entity, @@Embeddable, etc.
• Include Querydsl libraries

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>jcg.zheng.demo</groupId>
	<artifactId>spring-querydsl-demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>spring-querydsl-demo</name>
	<description>Demo Spring Data JPA-QueryDsl Application</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.5.10.RELEASE</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
	
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-configuration-processor</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>com.querydsl</groupId>
			<artifactId>querydsl-apt</artifactId>

		</dependency>
		<dependency>
			<groupId>com.querydsl</groupId>
			<artifactId>querydsl-jpa</artifactId>
		</dependency>

	</dependencies>


	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
			<!-- Querydsl and Specifications -->
			<plugin>
				<groupId>com.mysema.maven</groupId>
				<artifactId>apt-maven-plugin</artifactId>
				<version>1.1.3</version>
				<executions>
					<execution>
						<goals>
							<goal>process</goal>
						</goals>
						<configuration>
							<outputDirectory>target/generated-sources/java</outputDirectory>
							<processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
						</configuration>
					</execution>
				</executions>
			</plugin>
		</plugins>
	</build>
</project>

3.2 Entity Classes

We will create two entity classes: Company and Contact. Both are annotated with @Entity, so JPAAnnotationProcessor can generate the QObjects.

Company has Id, name, and type attributes. It has a OneToMany relationship to Contact.

Company.java

package jcg.zheng.demo.querydsldemo.entity;

import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name = "COMPANY")
public class Company {

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

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

	@Column(name = "TYPE")
	@Enumerated(EnumType.STRING)
	private CompanyType type;

	@OneToMany(mappedBy = "company", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
	private List<Contact> contacts;

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public CompanyType getType() {
		return type;
	}

	public void setType(CompanyType type) {
		this.type = type;
	}

	public List<Contact> getContacts() {
		return contacts;
	}

	public void setContacts(List<Contact> contacts) {
		this.contacts = contacts;
	}

}

Contact contains first name, last name, and type. It has a ManyToOne relationship to Company.

Contact.java

package jcg.zheng.demo.querydsldemo.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

@Entity
@Table(name = "CONTACT")
public class Contact {

	@Id
	@GeneratedValue
	@Column(name = "Id")
	private Long id;

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

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

	@Column(name = "Type")
	@Enumerated(EnumType.STRING)
	private ContactType type;

	@ManyToOne
	@JoinColumn(name = "Company_Id")
	private Company company;

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public ContactType getType() {
		return type;
	}

	public void setType(ContactType type) {
		this.type = type;
	}

	public Company getCompany() {
		return company;
	}

	public void setCompany(Company company) {
		this.company = company;
	}

}

3.3 Generated Meta Model Classes

apt-maven-plugin contains JPAannotationProcessor which generates QObjects. Go to target/generated-sources to check out the generated QCompany and QContact. They contain the metadata for each entity table.

QCompany is a generated class for the Company entity.

QCompany.java

package jcg.zheng.demo.querydsldemo.entity;

import static com.querydsl.core.types.PathMetadataFactory.*;

import com.querydsl.core.types.dsl.*;

import com.querydsl.core.types.PathMetadata;
import javax.annotation.Generated;
import com.querydsl.core.types.Path;
import com.querydsl.core.types.dsl.PathInits;


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

    private static final long serialVersionUID = 257370107L;

    public static final QCompany company = new QCompany("company");

    public final ListPath<Contact, QContact> contacts = this.<Contact, QContact>createList("contacts", Contact.class, QContact.class, PathInits.DIRECT2);

    public final NumberPath<Long> id = createNumber("id", Long.class);

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

    public final EnumPath<CompanyType> type = createEnum("type", CompanyType.class);

    public QCompany(String variable) {
        super(Company.class, forVariable(variable));
    }

    public QCompany(Path<? extends Company> path) {
        super(path.getType(), path.getMetadata());
    }

    public QCompany(PathMetadata metadata) {
        super(Company.class, metadata);
    }

}

QContact is a generated for the Contact entity.

QContact.java

package jcg.zheng.demo.querydsldemo.entity;

import static com.querydsl.core.types.PathMetadataFactory.*;

import com.querydsl.core.types.dsl.*;

import com.querydsl.core.types.PathMetadata;
import javax.annotation.Generated;
import com.querydsl.core.types.Path;
import com.querydsl.core.types.dsl.PathInits;


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

    private static final long serialVersionUID = 258412446L;

    private static final PathInits INITS = PathInits.DIRECT2;

    public static final QContact contact = new QContact("contact");

    public final QCompany company;

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

    public final NumberPath<Long> id = createNumber("id", Long.class);

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

    public final EnumPath<ContactType> type = createEnum("type", ContactType.class);

    public QContact(String variable) {
        this(Contact.class, forVariable(variable), INITS);
    }

    public QContact(Path<? extends Contact> path) {
        this(path.getType(), path.getMetadata(), PathInits.getFor(path.getMetadata(), INITS));
    }

    public QContact(PathMetadata metadata) {
        this(metadata, PathInits.getFor(metadata, INITS));
    }

    public QContact(PathMetadata metadata, PathInits inits) {
        this(Contact.class, metadata, inits);
    }

    public QContact(Class<? extends Contact> type, PathMetadata metadata, PathInits inits) {
        super(type, metadata, inits);
        this.company = inits.isInitialized("company") ? new QCompany(forProperty("company")) : null;
    }

}

3.4 JpaRepositories

In this step, we will create two repositories: CompanyJpaRepository and ContactQuerydslRepository.

CompanyJpaRepository interface extends from JpaRepository. We will add the findAll method. We will add findByTypeAndName with @Query annotation to demonstrate why type-safe query is better than the SQL query with @Query annotation.

CompanyJpaRepository.java

package jcg.zheng.demo.querydsldemo.repository;

import java.util.List;

import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import jcg.zheng.demo.querydsldemo.entity.Company;
import jcg.zheng.demo.querydsldemo.entity.CompanyType;

/**
 * Demo class for JpaRepository with static Query String and Dynamic Query
 * @author Mary.Zheng
 *
 */
@Repository
public interface CompanyJpaRepository extends JpaRepository<Company, Long> {

	@Query("SELECT req FROM Company req  WHERE req.type=(:type) AND req.name= (:name)")
	List<Company> findByTypeAndName(@Param("type") CompanyType type, @Param("name") String name);

	List<Company> findAll(Specification<Company> specification);

}

ContactQuerydslRepository interface extends from JpaRepository and QueryDslPredicateExecutor.  With that extension, we can write a dynamic query with Querydsl Predicate. We will add the findByCompany method.

ContactQuerydslRepository.java

package jcg.zheng.demo.querydsldemo.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.querydsl.QueryDslPredicateExecutor;

import jcg.zheng.demo.querydsldemo.entity.Company;
import jcg.zheng.demo.querydsldemo.entity.Contact;


public interface ContactQuerydslRepository extends JpaRepository<Contact, Long>, QueryDslPredicateExecutor<Contact> {
	
	public List<Contact> findByCompany(Company company);
	
}

3.5 Services

3.5.1 Company Service

In this step, we will create a company service which utilizes the CompanyJpaRepository‘s findAll method to create a dynamic query with Specification to search companies with a name and type. We have to implement toPredicate to construct the type-safe query.

CompanyService.java

package jcg.zheng.demo.querydsldemo.service;

import java.util.List;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import jcg.zheng.demo.querydsldemo.repository.CompanyJpaRepository;
import jcg.zheng.demo.querydsldemo.entity.Company;
import jcg.zheng.demo.querydsldemo.entity.CompanyType;

@Service
public class CompanyService {

	@Autowired
	private CompanyJpaRepository companyJpaRepo;

	public List<Company> searchByNameAndType(String companyName, CompanyType type) {
		List<Company> companies = companyJpaRepo.findAll(new Specification<Company>() {
			@Override
			public Predicate toPredicate(Root<Company> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
				Predicate matchType = cb.equal(root.<CompanyType> get("type"), type);
				Predicate returnPredicate = cb.and(matchType);
				if (!StringUtils.isEmpty(companyName)) {
					Predicate matchName = cb.equal(root.<String> get("name"), companyName);
					returnPredicate = cb.and(matchType, matchName);
				}
				return returnPredicate;
			}
		});
		return companies;
	}

	public Company save(Company company) {
		return companyJpaRepo.save(company);
	}
	
	public void delete(Company company){
		companyJpaRepo.delete(company);
	}

}

3.5.2 Contact Service

In this step, we will create contact service which utilizes the ContactQuerydslRepository‘s findAll method to create a dynamic query with QContact to search contacts by a name with Predicate. The logic here is simpler comparing to the logic in step 3.5.1.

ContactService.java

package jcg.zheng.demo.querydsldemo.service;

import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.querydsl.core.types.Predicate;

import jcg.zheng.demo.querydsldemo.entity.QContact;
import jcg.zheng.demo.querydsldemo.repository.ContactQuerydslRepository;
import jcg.zheng.demo.querydsldemo.entity.Company;
import jcg.zheng.demo.querydsldemo.entity.Contact;
import jcg.zheng.demo.querydsldemo.entity.ContactType;

@Service
public class ContactService {

	@Autowired
	private ContactQuerydslRepository contactDslRepo;

	public List<Contact> searchByName(String firstName, String lastName) {
		List<Contact> ret = new ArrayList<>();
		QContact contEquation = QContact.contact;
		Predicate cnt = contEquation.firstName.contains(firstName);
		Iterable<Contact> contacts = contactDslRepo.findAll(cnt);
		for (Contact e : contacts) {
			ret.add(e);
		}
		return ret;
	}

	public Contact save(Company company, String firstName, String lastName, ContactType type) {

		Contact contact = new Contact();
		contact.setCompany(company);
		contact.setFirstName(firstName);
		contact.setLastName(lastName);
		contact.setType(type);

		return contactDslRepo.save(contact);
	}

	public void delete(Contact contact) {
		contactDslRepo.delete(contact);
	}

}

I highlighted the CompanyService.searchByNameAndType at step 3.5.1 and ContactService.searchByName in step 3.5.2. The ContactService uses com.querydsl.core.types.Predicate and QContact with 9 lines of code. The CompanyService uses javax.persistence.criteria.Predicate and Specification with 13 lines of code. The logic in the Querydsl is simpler.

3.6 Daos

3.6.1 Company Dao

We create CompanyDao interface with the findByName method.

CompanyDao.java

package jcg.zheng.demo.querydsldemo.dao;

import jcg.zheng.demo.querydsldemo.entity.Company;

public interface CompanyDao {

	Company findByName(String companyName);

}

We create CompanyDaoImpl Spring bean which implements Companydao with JdbcTemplate.

Note: CompanyRowMapper class implements RowMapper.

CompanyDaoImpl.java

package jcg.zheng.demo.querydsldemo.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;

import jcg.zheng.demo.querydsldemo.dao.CompanyDao;
import jcg.zheng.demo.querydsldemo.entity.Company;
import jcg.zheng.demo.querydsldemo.entity.CompanyType;

@Component
public class CompanyDaoImpl implements CompanyDao {

	private static final String SQL_SEARCH_COMPANY_BY_NAME = "Select id CompanyID, name CompanyName, type from Company where name = :companyName";

	@Autowired
	private NamedParameterJdbcTemplate jdbcTemplate;

	@Override
	public Company findByName(String companyName) {
		Company ret = null;
		try {
			ret = jdbcTemplate.queryForObject(SQL_SEARCH_COMPANY_BY_NAME,
					Collections.singletonMap("companyName", companyName), new CompanyRowMapper());
		} catch (IncorrectResultSizeDataAccessException e) {
			// ignore
		}
		return ret;
	}

	private static final class CompanyRowMapper implements RowMapper<Company> {
		public Company mapRow(ResultSet resultSet, int rowNum) throws SQLException {
			final Company company = new Company();
			company.setId(resultSet.getLong("CompanyID"));
			company.setName(resultSet.getString("CompanyName"));
			company.setType(CompanyType.valueOf(resultSet.getString("type")));

			return company;
		}
	}

}

3.6.2 ContactDao

We define ContactQuerydslDao interface which has three methods: getContactById, getContactFromTypedQuery, and getContactFromDynamicQuery.

ContactQuerydslDao.java

package jcg.zheng.demo.querydsldemo.dao;

import java.util.List;

import jcg.zheng.demo.querydsldemo.entity.Contact;

public interface ContactQuerydslDao {

	List<Contact> getContactById(Long id);

	List<Contact> getContactFromTypedQuery(Long id);

	List<Contact> getContactFromDynamicQuery(String firstName, String lastName);

}

We create ContactDaoImpl Spring bean to implements Contactdao interface. GetContactById and getContactFromDynamicQuery use QContact and com.querydsl.jpa.impl.JPAQuery which have less code and simpler logic when comparing to getContactFromTypedQuery uses CriteriaBuilder.

ContactQuerydslDaoImpl.java

package jcg.zheng.demo.querydsldemo.dao.impl;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.ParameterExpression;
import javax.persistence.criteria.Root;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.querydsl.core.types.dsl.BooleanExpression;
import com.querydsl.jpa.impl.JPAQuery;

import jcg.zheng.demo.querydsldemo.entity.QContact;
import jcg.zheng.demo.querydsldemo.dao.ContactQuerydslDao;
import jcg.zheng.demo.querydsldemo.entity.Contact;

/**
 * Compare the JPAQuery to TypedQuery ( non-JpaRepository)
 * 
 * @author Mary.Zheng
 *
 */
@Component
public class ContactQuerydslDaoImpl implements ContactQuerydslDao {

	@Autowired
	EntityManager em;

	@Override
	public List<Contact> getContactById(Long id) {
		QContact contact = QContact.contact;
		JPAQuery<Contact> query = new JPAQuery<Contact>(em);
		query.from(contact).where(contact.id.eq(id));
		return query.fetch();
	}

	@Override
	public List<Contact> getContactFromTypedQuery(Long id) {

		CriteriaBuilder builder = em.getCriteriaBuilder();

		CriteriaQuery<Contact> contactQuery = builder.createQuery(Contact.class);
		Root<Contact> root = contactQuery.from(Contact.class);
		ParameterExpression<Long> value = builder.parameter(Long.class);
		contactQuery.select(root).where(builder.lt(root.get("id"), value));

		TypedQuery<Contact> query = em.createQuery(contactQuery);
		query.setParameter(value, id);

		return query.getResultList();
	}

	@Override
	public List<Contact> getContactFromDynamicQuery(String firstName, String lastName) {
		QContact contact = QContact.contact;
		JPAQuery<Contact> query = new JPAQuery<Contact>(em);

		BooleanExpression matchFirstName = contact.firstName.eq(firstName);
		BooleanExpression matchLastName = contact.lastName.eq(lastName);

		query.from(contact).where(matchFirstName, matchLastName).orderBy(contact.lastName.asc());

		return query.fetch();
	}

}

Note: I alter the jpaVendorAdapter to set setGenerateDdl and setShowSql to true, so the SQL queries can be displayed for all the test cases.

3.7 Demo Application

We alter generated QuerydslDemoApplication to include several Spring beans.

QuerydslDemoApplication.java

package jcg.zheng.demo.querydsldemo;

import javax.sql.DataSource;

import org.aspectj.lang.annotation.Aspect;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.Database;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;

@SpringBootApplication
@EnableJpaRepositories(basePackages = "jcg.zheng.demo.querydsldemo")
@EnableAspectJAutoProxy
@Aspect
public class QuerydslDemoApplication {

	public static void main(String[] args) {
		SpringApplication.run(QuerydslDemoApplication.class, args);
	}

	@Bean
	public DataSource dataSource() {
		return new EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.H2).build();
	}
	
	@Bean
	public NamedParameterJdbcTemplate namedParameterJdbcTemplate( DataSource dataSource) {
		return new NamedParameterJdbcTemplate(dataSource);
	
	}

	@Bean
	public JpaVendorAdapter jpaVendorAdapter() {
		HibernateJpaVendorAdapter bean = new HibernateJpaVendorAdapter();
		bean.setDatabase(Database.H2);
		bean.setGenerateDdl(true);
		bean.setShowSql(true);
		return bean;

	}

	@Bean
	public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource,
			JpaVendorAdapter jpaVendorAdapter) {
		LocalContainerEntityManagerFactoryBean bean = new LocalContainerEntityManagerFactoryBean();
		bean.setDataSource(dataSource);
		bean.setJpaVendorAdapter(jpaVendorAdapter);
		bean.setPackagesToScan("jcg.zheng.demo.querydsldemo");

		return bean;
	}

}

4. Demo

We are going to demo the Querydsl usage via six test classes.

4.1 CompanyJpaRepositoryTest

Create a Junit test class for CompanyJpaRepository.

CompanyJpaRepositoryTest.java

package jcg.zheng.demo.querydsldemo.repository;

import static org.junit.Assert.assertEquals;

import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import jcg.zheng.demo.querydsldemo.TestData;
import jcg.zheng.demo.querydsldemo.entity.Company;
import jcg.zheng.demo.querydsldemo.entity.CompanyType;

@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.NONE)
public class CompanyJpaRepositoryTest extends TestData {

	@Autowired
	private CompanyJpaRepository comRepo;

	@Test
	public void it_should_find_company_byTypeAndName_after_save_it() {
		comRepo.deleteAll();
		String name = "test company";
		comRepo.save(buildTestCompany(name, CompanyType.VENDOR));

		List rep = comRepo.findByTypeAndName(CompanyType.VENDOR, name);
		assertEquals(1, rep.size());
		assertEquals(name, rep.get(0).getName());
		assertEquals(CompanyType.VENDOR, rep.get(0).getType());
		assertEquals(0, rep.get(0).getContacts().size());
	}
}

4.2 ContactQuerydslRepositoryTest

Create a Junit test class for ContactQuerydslRepository.

ContactQuerydslRepositoryTest.java

package jcg.zheng.demo.querydsldemo.repository;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;

import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import jcg.zheng.demo.querydsldemo.TestData;
import jcg.zheng.demo.querydsldemo.entity.Company;
import jcg.zheng.demo.querydsldemo.entity.CompanyType;
import jcg.zheng.demo.querydsldemo.entity.Contact;
import jcg.zheng.demo.querydsldemo.entity.ContactType;
import jcg.zheng.demo.querydsldemo.service.CompanyService;
import jcg.zheng.demo.querydsldemo.service.ContactService;

@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.NONE)
public class ContactQuerydslRepositoryTest extends TestData {

	@Autowired
	private ContactQuerydslRepository demo;

	@Autowired
	private ContactService contactService;

	@Autowired
	private CompanyService companyService;

	@Test
	public void it_should_find_contact_after_saved() {
		Company company = companyService.save(buildTestCompany("Zheng JCG", CompanyType.CUSTOMER));
		Contact mary = contactService.save(company, "Mary", "Zheng", ContactType.PRIMARY);
	
		long contactId = 1L;
		Contact found = demo.findOne(contactId);
		assertNotNull(found);
		assertEquals("Mary", found.getFirstName());
		assertEquals("Zheng", found.getLastName());
		assertEquals(ContactType.PRIMARY, found.getType());
		
		Contact alex = contactService.save(company, "Alex", "Zheng", ContactType.SEONDARY);
		List rets = demo.findByCompany(company);

		assertNotNull(rets);
		assertEquals(2, rets.size());
		
		contactService.delete(mary);
		contactService.delete(alex);	
	}

}

4.3 CompanyServiceTest

Create a Junit test class for CompanyService.

CompanyServiceTest.java

package jcg.zheng.demo.querydsldemo.service;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;

import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import jcg.zheng.demo.querydsldemo.TestData;
import jcg.zheng.demo.querydsldemo.entity.Company;
import jcg.zheng.demo.querydsldemo.entity.CompanyType;
import jcg.zheng.demo.querydsldemo.service.CompanyService;

@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.NONE)
public class CompanyServiceTest extends TestData {

	@Autowired
	private CompanyService companyService;

	@Test
	public void it_should_find_zero_company_when_searchByNameAndType_with_non_exist_data() {
		List found = companyService.searchByNameAndType("companyName", CompanyType.CUSTOMER);
		assertTrue(found.isEmpty());
	}

	@Test
	public void it_should_find_company_when_searchByNameAndType_with_exist_data() {
		String name = "Mary Company";
		Company company = companyService.save(buildTestCompany(name, CompanyType.CUSTOMER));
		List found = companyService.searchByNameAndType(name, CompanyType.CUSTOMER);
		assertFalse(found.isEmpty());
		assertEquals(name, found.get(0).getName());
		
		companyService.delete(company);
	}

}

4.4 ContactServiceTest

Create a Junit test class for ContactService.

ContactServiceTest.java

package jcg.zheng.demo.querydsldemo.service;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;

import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import jcg.zheng.demo.querydsldemo.TestData;
import jcg.zheng.demo.querydsldemo.entity.Company;
import jcg.zheng.demo.querydsldemo.entity.CompanyType;
import jcg.zheng.demo.querydsldemo.entity.Contact;
import jcg.zheng.demo.querydsldemo.entity.ContactType;
import jcg.zheng.demo.querydsldemo.service.CompanyService;
import jcg.zheng.demo.querydsldemo.service.ContactService;

@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.NONE)
public class ContactServiceTest extends TestData {

	@Autowired
	private ContactService contactService;

	@Autowired
	private CompanyService companyService;

	@Test
	public void it_should_find_zero_contact_when_searchByName_with_non_exist_data() {
		List found = contactService.searchByName("Bob", null);
		assertTrue(found.isEmpty());
	}

	@Test
	public void it_should_find_contact_when_searchByName_with_exist_data() {

		Company company = companyService.save(buildTestCompany("Zheng company", CompanyType.CUSTOMER));
		Contact contact = contactService.save(company, "John", "Zheng", ContactType.PRIMARY);
		assertNotNull(contact);
		List found = contactService.searchByName("John", null);
		assertEquals(1, found.size());
		assertEquals("John", found.get(0).getFirstName());
		assertEquals("Zheng", found.get(0).getLastName());
		assertEquals(ContactType.PRIMARY, found.get(0).getType());
		assertEquals("Zheng company", found.get(0).getCompany().getName());

		contactService.delete(contact);

	}

}

4.5 CompanyDaoImplTest

Create a Junit test class for CompanyDaoImpl.

CompanyDaoImplTest.java

package jcg.zheng.demo.querydsldemo.dao.impl;

import static org.junit.Assert.*;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import jcg.zheng.demo.querydsldemo.TestData;
import jcg.zheng.demo.querydsldemo.dao.CompanyDao;
import jcg.zheng.demo.querydsldemo.entity.Company;
import jcg.zheng.demo.querydsldemo.entity.CompanyType;
import jcg.zheng.demo.querydsldemo.service.CompanyService;

@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.NONE)

public class CompanyDaoImplTest extends TestData {

	@Autowired
	private CompanyService companyService;

	@Autowired
	private CompanyDao comDao;

	@Test
	public void it_should_find_null() {
		String companyName = "Bad Company";
		Company foundCom = comDao.findByName(companyName);
		assertNull(foundCom);
	}

	@Test
	public void it_should_find_after_save() {
		String companyName = "Zheng Company";
		companyService.save(buildTestCompany(companyName, CompanyType.CUSTOMER));
		Company foundCom = comDao.findByName(companyName);
		assertNotNull(foundCom);
		assertEquals(companyName, foundCom.getName());
		assertEquals(CompanyType.CUSTOMER, foundCom.getType());
		assertNull(foundCom.getContacts());
	}

}

4.6 ContactQuerydslDaoImplTest

Create a Junit test class for ContactQuerydslDaoImpl.

ContactQuerydslDaoImplTest.java

package jcg.zheng.demo.querydsldemo.dao.impl;

import static org.junit.Assert.assertTrue;

import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import jcg.zheng.demo.querydsldemo.dao.ContactQuerydslDao;
import jcg.zheng.demo.querydsldemo.entity.Contact;

@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.NONE)
public class ContactQuerydslDaoImplTest {
	
	@Autowired
	private ContactQuerydslDao contactDao;

	@Test
	public void it_should_not_found_Contact_when_not_exist_Querydsl() {
		List found = contactDao.getContactById(1L);
		assertTrue(found.isEmpty());
	}
	
	@Test
	public void it_should_not_found_Contact_when_not_exist_TypedQuery() {
		List found = contactDao.getContactFromTypedQuery(1L);
		assertTrue(found.isEmpty());
	}
	
	@Test
	public void it_should_not_found_Contact_when_not_exist_DynamicQuery(){
		List found = contactDao.getContactFromDynamicQuery("Alex", "Zheng");
		assertTrue(found.isEmpty());
	}

}

4.7 Run Test Classes

Execute mvn install.

Want to master Spring Framework ?

Subscribe to our newsletter and download the Spring Framework Cookbook right now!

In order to help you master the leading and innovative Java framework, we have compiled a kick-ass guide with all its major features and use cases! Besides studying them online you may download the eBook in PDF format!

Download NOW!

Thank you!

We will contact you soon.

Test Cases Output

21:48:32.672 [main] DEBUG org.springframework.core.env.StandardEnvironment - Adding PropertySource 'Inlined Test Properties' with highest search precedence

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::       (v1.5.10.RELEASE)

2018-04-08 21:48:33.146  INFO 16968 --- [           main] j.z.d.q.dao.impl.CompanyDaoImplTest      : Starting CompanyDaoImplTest on SL2LS431841 with PID 16968 (started by shu.shan in C:\MZheng_Java_workspace\Java Code Geek Examples\spring-querydsl-demo)
2018-04-08 21:48:33.147  INFO 16968 --- [           main] j.z.d.q.dao.impl.CompanyDaoImplTest      : No active profile set, falling back to default profiles: default
2018-04-08 21:48:33.184  INFO 16968 --- [           main] s.c.a.AnnotationConfigApplicationContext : Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@480d3575: startup date [Sun Apr 08 21:48:33 CDT 2018]; root of context hierarchy
2018-04-08 21:48:34.035  INFO 16968 --- [           main] f.a.AutowiredAnnotationBeanPostProcessor : JSR-330 'javax.inject.Inject' annotation found and supported for autowiring
2018-04-08 21:48:34.244  INFO 16968 --- [           main] o.s.j.d.e.EmbeddedDatabaseFactory        : Starting embedded database: url='jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false', username='sa'
2018-04-08 21:48:34.783  INFO 16968 --- [           main] j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'default'
2018-04-08 21:48:34.808  INFO 16968 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
	name: default
	...]
2018-04-08 21:48:35.015  INFO 16968 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate Core {5.0.12.Final}
2018-04-08 21:48:35.018  INFO 16968 --- [           main] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
2018-04-08 21:48:35.020  INFO 16968 --- [           main] org.hibernate.cfg.Environment            : HHH000021: Bytecode provider name : javassist
2018-04-08 21:48:35.094  INFO 16968 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
2018-04-08 21:48:35.287  INFO 16968 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.H2Dialect
2018-04-08 21:48:36.184  INFO 16968 --- [           main] org.hibernate.tool.hbm2ddl.SchemaUpdate  : HHH000228: Running hbm2ddl schema update
2018-04-08 21:48:36.198  INFO 16968 --- [           main] rmationExtractorJdbcDatabaseMetaDataImpl : HHH000262: Table not found: COMPANY
2018-04-08 21:48:36.198  INFO 16968 --- [           main] rmationExtractorJdbcDatabaseMetaDataImpl : HHH000262: Table not found: COMPANY
2018-04-08 21:48:36.207  INFO 16968 --- [           main] rmationExtractorJdbcDatabaseMetaDataImpl : HHH000262: Table not found: CONTACT
2018-04-08 21:48:36.208  INFO 16968 --- [           main] rmationExtractorJdbcDatabaseMetaDataImpl : HHH000262: Table not found: CONTACT
2018-04-08 21:48:36.285  INFO 16968 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2018-04-08 21:48:36.603  INFO 16968 --- [           main] o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory
2018-04-08 21:48:37.319  INFO 16968 --- [           main] j.z.d.q.dao.impl.CompanyDaoImplTest      : Started CompanyDaoImplTest in 4.643 seconds (JVM running for 5.87)
Hibernate: insert into COMPANY (ID, NAME, TYPE) values (null, ?, ?)
Tests run: 2, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 5.177 sec - in jcg.zheng.demo.querydsldemo.dao.impl.CompanyDaoImplTest
Running jcg.zheng.demo.querydsldemo.dao.impl.ContactQuerydslDaoImplTest
2018-04-08 21:48:37.652  INFO 16968 --- [           main] o.s.t.c.support.AbstractContextLoader    : Could not detect default resource locations for test class [jcg.zheng.demo.querydsldemo.dao.impl.ContactQuerydslDaoImplTest]: no resource found for suffixes {-context.xml, Context.groovy}.
2018-04-08 21:48:37.653  INFO 16968 --- [           main] t.c.s.AnnotationConfigContextLoaderUtils : Could not detect default configuration classes for test class [jcg.zheng.demo.querydsldemo.dao.impl.ContactQuerydslDaoImplTest]: ContactQuerydslDaoImplTest does not declare any static, non-private, non-final, nested classes annotated with @Configuration.
Hibernate: select contact0_.Id as Id1_1_, contact0_.Company_Id as Company_5_1_, contact0_.First_Name as First_Na2_1_, contact0_.Last_Name as Last_Nam3_1_, contact0_.Type as Type4_1_ from CONTACT contact0_ where contact0_.Id<?
Hibernate: select contact0_.Id as Id1_1_, contact0_.Company_Id as Company_5_1_, contact0_.First_Name as First_Na2_1_, contact0_.Last_Name as Last_Nam3_1_, contact0_.Type as Type4_1_ from CONTACT contact0_ where contact0_.Id=?
Hibernate: select contact0_.Id as Id1_1_, contact0_.Company_Id as Company_5_1_, contact0_.First_Name as First_Na2_1_, contact0_.Last_Name as Last_Nam3_1_, contact0_.Type as Type4_1_ from CONTACT contact0_ where contact0_.First_Name=? and contact0_.Last_Name=? order by contact0_.Last_Name asc
Tests run: 3, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 0.208 sec - in jcg.zheng.demo.querydsldemo.dao.impl.ContactQuerydslDaoImplTest
Running jcg.zheng.demo.querydsldemo.QuerydslDemoApplicationTests
2018-04-08 21:48:37.885  INFO 16968 --- [           main] o.s.t.c.support.AbstractContextLoader    : Could not detect default resource locations for test class [jcg.zheng.demo.querydsldemo.QuerydslDemoApplicationTests]: no resource found for suffixes {-context.xml, Context.groovy}.
2018-04-08 21:48:37.885  INFO 16968 --- [           main] t.c.s.AnnotationConfigContextLoaderUtils : Could not detect default configuration classes for test class [jcg.zheng.demo.querydsldemo.QuerydslDemoApplicationTests]: QuerydslDemoApplicationTests does not declare any static, non-private, non-final, nested classes annotated with @Configuration.
Tests run: 2, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 0.023 sec - in jcg.zheng.demo.querydsldemo.QuerydslDemoApplicationTests
Running jcg.zheng.demo.querydsldemo.repository.CompanyJpaRepositoryTest
2018-04-08 21:48:37.915  INFO 16968 --- [           main] o.s.t.c.support.AbstractContextLoader    : Could not detect default resource locations for test class [jcg.zheng.demo.querydsldemo.repository.CompanyJpaRepositoryTest]: no resource found for suffixes {-context.xml, Context.groovy}.
2018-04-08 21:48:37.915  INFO 16968 --- [           main] t.c.s.AnnotationConfigContextLoaderUtils : Could not detect default configuration classes for test class [jcg.zheng.demo.querydsldemo.repository.CompanyJpaRepositoryTest]: CompanyJpaRepositoryTest does not declare any static, non-private, non-final, nested classes annotated with @Configuration.
Hibernate: select company0_.ID as ID1_0_, company0_.NAME as NAME2_0_, company0_.TYPE as TYPE3_0_ from COMPANY company0_
Hibernate: select contacts0_.Company_Id as Company_5_1_0_, contacts0_.Id as Id1_1_0_, contacts0_.Id as Id1_1_1_, contacts0_.Company_Id as Company_5_1_1_, contacts0_.First_Name as First_Na2_1_1_, contacts0_.Last_Name as Last_Nam3_1_1_, contacts0_.Type as Type4_1_1_ from CONTACT contacts0_ where contacts0_.Company_Id=?
Hibernate: delete from COMPANY where ID=?
Hibernate: insert into COMPANY (ID, NAME, TYPE) values (null, ?, ?)
Hibernate: select company0_.ID as ID1_0_, company0_.NAME as NAME2_0_, company0_.TYPE as TYPE3_0_ from COMPANY company0_ where company0_.TYPE=? and company0_.NAME=?
Hibernate: select contacts0_.Company_Id as Company_5_1_0_, contacts0_.Id as Id1_1_0_, contacts0_.Id as Id1_1_1_, contacts0_.Company_Id as Company_5_1_1_, contacts0_.First_Name as First_Na2_1_1_, contacts0_.Last_Name as Last_Nam3_1_1_, contacts0_.Type as Type4_1_1_ from CONTACT contacts0_ where contacts0_.Company_Id=?
Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 0.13 sec - in jcg.zheng.demo.querydsldemo.repository.CompanyJpaRepositoryTest
Running jcg.zheng.demo.querydsldemo.repository.ContactQuerydslRepositoryTest
2018-04-08 21:48:38.069  INFO 16968 --- [           main] o.s.t.c.support.AbstractContextLoader    : Could not detect default resource locations for test class [jcg.zheng.demo.querydsldemo.repository.ContactQuerydslRepositoryTest]: no resource found for suffixes {-context.xml, Context.groovy}.
2018-04-08 21:48:38.069  INFO 16968 --- [           main] t.c.s.AnnotationConfigContextLoaderUtils : Could not detect default configuration classes for test class [jcg.zheng.demo.querydsldemo.repository.ContactQuerydslRepositoryTest]: ContactQuerydslRepositoryTest does not declare any static, non-private, non-final, nested classes annotated with @Configuration.
Hibernate: insert into COMPANY (ID, NAME, TYPE) values (null, ?, ?)
Hibernate: call next value for hibernate_sequence
Hibernate: insert into CONTACT (Company_Id, First_Name, Last_Name, Type, Id) values (?, ?, ?, ?, ?)
Hibernate: select contact0_.Id as Id1_1_0_, contact0_.Company_Id as Company_5_1_0_, contact0_.First_Name as First_Na2_1_0_, contact0_.Last_Name as Last_Nam3_1_0_, contact0_.Type as Type4_1_0_, company1_.ID as ID1_0_1_, company1_.NAME as NAME2_0_1_, company1_.TYPE as TYPE3_0_1_ from CONTACT contact0_ left outer join COMPANY company1_ on contact0_.Company_Id=company1_.ID where contact0_.Id=?
Hibernate: select contacts0_.Company_Id as Company_5_1_0_, contacts0_.Id as Id1_1_0_, contacts0_.Id as Id1_1_1_, contacts0_.Company_Id as Company_5_1_1_, contacts0_.First_Name as First_Na2_1_1_, contacts0_.Last_Name as Last_Nam3_1_1_, contacts0_.Type as Type4_1_1_ from CONTACT contacts0_ where contacts0_.Company_Id=?
Hibernate: call next value for hibernate_sequence
Hibernate: insert into CONTACT (Company_Id, First_Name, Last_Name, Type, Id) values (?, ?, ?, ?, ?)
Hibernate: select contact0_.Id as Id1_1_, contact0_.Company_Id as Company_5_1_, contact0_.First_Name as First_Na2_1_, contact0_.Last_Name as Last_Nam3_1_, contact0_.Type as Type4_1_ from CONTACT contact0_ left outer join COMPANY company1_ on contact0_.Company_Id=company1_.ID where company1_.ID=?
Hibernate: select company0_.ID as ID1_0_0_, company0_.NAME as NAME2_0_0_, company0_.TYPE as TYPE3_0_0_, contacts1_.Company_Id as Company_5_1_1_, contacts1_.Id as Id1_1_1_, contacts1_.Id as Id1_1_2_, contacts1_.Company_Id as Company_5_1_2_, contacts1_.First_Name as First_Na2_1_2_, contacts1_.Last_Name as Last_Nam3_1_2_, contacts1_.Type as Type4_1_2_ from COMPANY company0_ left outer join CONTACT contacts1_ on company0_.ID=contacts1_.Company_Id where company0_.ID=?
Hibernate: select contact0_.Id as Id1_1_0_, contact0_.Company_Id as Company_5_1_0_, contact0_.First_Name as First_Na2_1_0_, contact0_.Last_Name as Last_Nam3_1_0_, contact0_.Type as Type4_1_0_ from CONTACT contact0_ where contact0_.Id=?
Hibernate: select company0_.ID as ID1_0_1_, company0_.NAME as NAME2_0_1_, company0_.TYPE as TYPE3_0_1_, contacts1_.Company_Id as Company_5_1_3_, contacts1_.Id as Id1_1_3_, contacts1_.Id as Id1_1_0_, contacts1_.Company_Id as Company_5_1_0_, contacts1_.First_Name as First_Na2_1_0_, contacts1_.Last_Name as Last_Nam3_1_0_, contacts1_.Type as Type4_1_0_ from COMPANY company0_ left outer join CONTACT contacts1_ on company0_.ID=contacts1_.Company_Id where company0_.ID=?
Hibernate: select contact0_.Id as Id1_1_0_, contact0_.Company_Id as Company_5_1_0_, contact0_.First_Name as First_Na2_1_0_, contact0_.Last_Name as Last_Nam3_1_0_, contact0_.Type as Type4_1_0_ from CONTACT contact0_ where contact0_.Id=?
Hibernate: select company0_.ID as ID1_0_1_, company0_.NAME as NAME2_0_1_, company0_.TYPE as TYPE3_0_1_, contacts1_.Company_Id as Company_5_1_3_, contacts1_.Id as Id1_1_3_, contacts1_.Id as Id1_1_0_, contacts1_.Company_Id as Company_5_1_0_, contacts1_.First_Name as First_Na2_1_0_, contacts1_.Last_Name as Last_Nam3_1_0_, contacts1_.Type as Type4_1_0_ from COMPANY company0_ left outer join CONTACT contacts1_ on company0_.ID=contacts1_.Company_Id where company0_.ID=?
Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 0.068 sec - in jcg.zheng.demo.querydsldemo.repository.ContactQuerydslRepositoryTest
Running jcg.zheng.demo.querydsldemo.service.CompanyServiceTest
2018-04-08 21:48:38.143  INFO 16968 --- [           main] o.s.t.c.support.AbstractContextLoader    : Could not detect default resource locations for test class [jcg.zheng.demo.querydsldemo.service.CompanyServiceTest]: no resource found for suffixes {-context.xml, Context.groovy}.
2018-04-08 21:48:38.143  INFO 16968 --- [           main] t.c.s.AnnotationConfigContextLoaderUtils : Could not detect default configuration classes for test class [jcg.zheng.demo.querydsldemo.service.CompanyServiceTest]: CompanyServiceTest does not declare any static, non-private, non-final, nested classes annotated with @Configuration.
Hibernate: insert into COMPANY (ID, NAME, TYPE) values (null, ?, ?)
Hibernate: select company0_.ID as ID1_0_, company0_.NAME as NAME2_0_, company0_.TYPE as TYPE3_0_ from COMPANY company0_ where company0_.TYPE=? and company0_.NAME=?
Hibernate: select contacts0_.Company_Id as Company_5_1_0_, contacts0_.Id as Id1_1_0_, contacts0_.Id as Id1_1_1_, contacts0_.Company_Id as Company_5_1_1_, contacts0_.First_Name as First_Na2_1_1_, contacts0_.Last_Name as Last_Nam3_1_1_, contacts0_.Type as Type4_1_1_ from CONTACT contacts0_ where contacts0_.Company_Id=?
Hibernate: select company0_.ID as ID1_0_1_, company0_.NAME as NAME2_0_1_, company0_.TYPE as TYPE3_0_1_, contacts1_.Company_Id as Company_5_1_3_, contacts1_.Id as Id1_1_3_, contacts1_.Id as Id1_1_0_, contacts1_.Company_Id as Company_5_1_0_, contacts1_.First_Name as First_Na2_1_0_, contacts1_.Last_Name as Last_Nam3_1_0_, contacts1_.Type as Type4_1_0_ from COMPANY company0_ left outer join CONTACT contacts1_ on company0_.ID=contacts1_.Company_Id where company0_.ID=?
Hibernate: delete from COMPANY where ID=?
Hibernate: select company0_.ID as ID1_0_, company0_.NAME as NAME2_0_, company0_.TYPE as TYPE3_0_ from COMPANY company0_ where company0_.TYPE=? and company0_.NAME=?
Tests run: 2, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 0.061 sec - in jcg.zheng.demo.querydsldemo.service.CompanyServiceTest
Running jcg.zheng.demo.querydsldemo.service.ContactServiceTest
2018-04-08 21:48:38.199  INFO 16968 --- [           main] o.s.t.c.support.AbstractContextLoader    : Could not detect default resource locations for test class [jcg.zheng.demo.querydsldemo.service.ContactServiceTest]: no resource found for suffixes {-context.xml, Context.groovy}.
2018-04-08 21:48:38.199  INFO 16968 --- [           main] t.c.s.AnnotationConfigContextLoaderUtils : Could not detect default configuration classes for test class [jcg.zheng.demo.querydsldemo.service.ContactServiceTest]: ContactServiceTest does not declare any static, non-private, non-final, nested classes annotated with @Configuration.
Hibernate: select contact0_.Id as Id1_1_, contact0_.Company_Id as Company_5_1_, contact0_.First_Name as First_Na2_1_, contact0_.Last_Name as Last_Nam3_1_, contact0_.Type as Type4_1_ from CONTACT contact0_ where contact0_.First_Name like ? escape '!'
Hibernate: insert into COMPANY (ID, NAME, TYPE) values (null, ?, ?)
Hibernate: call next value for hibernate_sequence
Hibernate: insert into CONTACT (Company_Id, First_Name, Last_Name, Type, Id) values (?, ?, ?, ?, ?)
Hibernate: select contact0_.Id as Id1_1_, contact0_.Company_Id as Company_5_1_, contact0_.First_Name as First_Na2_1_, contact0_.Last_Name as Last_Nam3_1_, contact0_.Type as Type4_1_ from CONTACT contact0_ where contact0_.First_Name like ? escape '!'
Hibernate: select company0_.ID as ID1_0_0_, company0_.NAME as NAME2_0_0_, company0_.TYPE as TYPE3_0_0_, contacts1_.Company_Id as Company_5_1_1_, contacts1_.Id as Id1_1_1_, contacts1_.Id as Id1_1_2_, contacts1_.Company_Id as Company_5_1_2_, contacts1_.First_Name as First_Na2_1_2_, contacts1_.Last_Name as Last_Nam3_1_2_, contacts1_.Type as Type4_1_2_ from COMPANY company0_ left outer join CONTACT contacts1_ on company0_.ID=contacts1_.Company_Id where company0_.ID=?
Hibernate: select contact0_.Id as Id1_1_0_, contact0_.Company_Id as Company_5_1_0_, contact0_.First_Name as First_Na2_1_0_, contact0_.Last_Name as Last_Nam3_1_0_, contact0_.Type as Type4_1_0_ from CONTACT contact0_ where contact0_.Id=?
Hibernate: select company0_.ID as ID1_0_1_, company0_.NAME as NAME2_0_1_, company0_.TYPE as TYPE3_0_1_, contacts1_.Company_Id as Company_5_1_3_, contacts1_.Id as Id1_1_3_, contacts1_.Id as Id1_1_0_, contacts1_.Company_Id as Company_5_1_0_, contacts1_.First_Name as First_Na2_1_0_, contacts1_.Last_Name as Last_Nam3_1_0_, contacts1_.Type as Type4_1_0_ from COMPANY company0_ left outer join CONTACT contacts1_ on company0_.ID=contacts1_.Company_Id where company0_.ID=?
Tests run: 2, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 0.057 sec - in jcg.zheng.demo.querydsldemo.service.ContactServiceTest
2018-04-08 21:48:38.274  INFO 16968 --- [       Thread-2] s.c.a.AnnotationConfigApplicationContext : Closing org.springframework.context.annotation.AnnotationConfigApplicationContext@480d3575: startup date [Sun Apr 08 21:48:33 CDT 2018]; root of context hierarchy
2018-04-08 21:48:38.277  INFO 16968 --- [       Thread-2] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
2018-04-08 21:48:38.278  INFO 16968 --- [       Thread-2] o.s.j.d.e.EmbeddedDatabaseFactory        : Shutting down embedded database: url='jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false'

Results :

Tests run: 13, Failures: 0, Errors: 0, Skipped: 0

Note: You can see the SQL queries generated by Hibernate.

5. Change Requirements

So far, we have built a Spring boot project with two database tables, two repositories, two Daos, two services, and seven test classes with Querydsl, JpaRepository, and JdbcTemplate technologies.

DBA reviewed the two database tables and required two changes:

• Change the table name with prefix “T_”
• Change the primary key ID field name to {TableName}_ID

IT manager reviewed the code and required one change:

• Change the Contact class name to Person to differentiate from the Email contact

6. Querydsl Type-Safe Queries

6.1 Alter Entity Classes

We will rename the Company  and Contact entities’ table name to T_COMPANY and T_CONTACT. We will rename the column Id to COMPANY_ID and CONTACT_ID respectively.  We will rename the Contact class to Person Class. This change will trigger JPAAnnotationProcessor to generate QPerson as replacement of the QContact.

Updated Company class.

Company.java

package jcg.zheng.demo.querydsldemo.entity;

import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name = "T_COMPANY")
public class Company {

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

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

	@Column(name = "TYPE")
	@Enumerated(EnumType.STRING)
	private CompanyType type;

	@OneToMany(mappedBy = "company", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
	private List contacts;

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public CompanyType getType() {
		return type;
	}

	public void setType(CompanyType type) {
		this.type = type;
	}

	public List getContacts() {
		return contacts;
	}

	public void setContacts(List contacts) {
		this.contacts = contacts;
	}

}

Updated Person class.

Person.java

package jcg.zheng.demo.querydsldemo.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

@Entity
@Table(name = "T_CONTACT")
public class Person {

	@Id
	@GeneratedValue
	@Column(name = "CONTACT_Id")
	private Long id;

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

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

	@Column(name = "Type")
	@Enumerated(EnumType.STRING)
	private ContactType type;

	@ManyToOne
	@JoinColumn(name = "Company_Id")
	private Company company;

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public ContactType getType() {
		return type;
	}

	public void setType(ContactType type) {
		this.type = type;
	}

	public Company getCompany() {
		return company;
	}

	public void setCompany(Company company) {
		this.company = company;
	}

}

6.2 Compilation Error

Immediately, there will be compilation errors at ContactQuerydslDaoImpl and ContactService because the QContact was replaced by QPerson. We need to fix the code by using QPerson.

Compilation Error

[ERROR] /C:/MZheng_Java_workspace/Java Code Geek Examples/spring-querydsl-demo/src/main/java/jcg/zheng/demo/querydsldemo/service/ContactService.java:[11,42] cannot find symbol
[ERROR] symbol:   class QContact
[ERROR] location: package jcg.zheng.demo.querydsldemo.entity
[ERROR] /C:/MZheng_Java_workspace/Java Code Geek Examples/spring-querydsl-demo/src/main/java/jcg/zheng/demo/querydsldemo/dao/impl/ContactQuerydslDaoImpl.java:[18,42] cannot find symbol
[ERROR] symbol:   class QContact
[ERROR] location: package jcg.zheng.demo.querydsldemo.entity
[ERROR] -> [Help 1]

6.3 Run-time Error

After we fix above compilation errors, we will execute mvn install to build the project, then run-time errors will be caught by the test cases in CompanyDaoImplTest because it has the invalid SQL query due to Company table renamed. Without thess test cases, this invalid query will slip in production code and cause production outages.

Run Time Error

Results :

Tests in error: 
  CompanyDaoImplTest.it_should_find_after_save:39 » BadSqlGrammar PreparedStatem...
  CompanyDaoImplTest.it_should_find_null:31 » BadSqlGrammar PreparedStatementCal...

Tests run: 13, Failures: 0, Errors: 2, Skipped: 0

7. Summary

In this example, we built a Spring boot project to demonstrate Spring Data JPA with Querydsl to allow developers to write type-safe queries in a RDBMS. We also compared it to Spring’s JdbcTemplate and concluded that Querydsl is easier to use.

Both Spring Data and Querydsl support nosql database which is not covered in this example. Just like Brom’s quote – “Everything comes with a price.” There are some pitfalls when using Querydsl. Please click here for more details.

8. References

• http://www.querydsl.com/
• https://blog.jooq.org/tag/querydsl/
• http://www.querydsl.com/static/querydsl/4.0.7/reference/html_single/
• https://github.com/querydsl/querydsl/issues/304
• https://javabeat.net/spring-data-jpa-querydsl-integration/
• http://blog.mysema.com/

9. Download the Source Code

This example consists of a Spring Boot JPA Querydsl project prior to step 6‘s change.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.