Spring Data JPA – Delete using single JPQL query/@Query Example

This tutorial shows how we can use @Query to delete entities using a single JPQL query.
Spring Data JPA Query method deleteBy approach (see Spring Data JPA – DeleteBy Example) do it in two steps (It first fetches the entities and then call delete on the fetched data to delete the data)

1) Dependencies

This examples needs the following dependencies.

<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>com.kalliphant.samples.springdata.jpa</groupid>
	<artifactid>CRUDSample</artifactid>
	<version>0.0.1-SNAPSHOT</version>
	<name>crudSample</name>

	<dependencies>

		<!-- Spring Data JPA -->
		<dependency>
			<groupid>org.springframework.data</groupid>
			<artifactid>spring-data-jpa</artifactid>
			<version>1.10.0.RELEASE</version>
		</dependency>

		<!-- Using H2 Database for this example. If you are using different database, 
			add DB specific dependency here -->
		<dependency>
			<groupid>com.h2database</groupid>
			<artifactid>h2</artifactid>
			<version>1.4.196</version>
		</dependency>

		<!-- JPA Provider - Hibernate -->
		<dependency>
			<groupid>org.hibernate</groupid>
			<artifactid>hibernate-entitymanager</artifactid>
			<version>4.3.8.Final</version>
		</dependency>

	</dependencies>
</project>

2) springconfig.xml - Spring Bean XML Configuration file

This configures the data source, JPA vendor for creating entity manager and directories to scan the repositories.
It should be in your class path.


<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xmlns:jpa="http://www.springframework.org/schema/data/jpa"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/data/jpa
    http://www.springframework.org/schema/data/jpa/spring-jpa.xsd">

	<!-- Using H2 in memory database, if you are using some other database, 
		make sure to change this and add the database specific drivers as dependencies 
		to your pom.xml file -->
	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="org.h2.Driver"></property>
		<property name="url"
			value="jdbc:h2:mem:kalliphant-app;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE"></property>
		<property name="username" value="dummy_user"></property>
		<property name="password" value="dummy_password"></property>
	</bean>

	<bean id="jpaVendorAdapter"
		class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
		<property name="showSql" value="true"></property>
		<property name="generateDdl" value="true"></property>
		<property name="database" value="H2"></property>
	</bean>

	<bean id="entityManagerFactory"
		class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
		<property name="dataSource" ref="dataSource"></property>
		<property name="jpaVendorAdapter" ref="jpaVendorAdapter"></property>
		<property name="packagesToScan" value="com.kalliphant.samples.springdata.jpa"></property>
	</bean>

	<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager"></bean>

	<jpa:repositories base-package="com.kalliphant.samples.springdata.jpa"></jpa:repositories>

</beans>

3) Entity class

Employee Entity class with JPA annotations

package com.kalliphant.samples.springdata.jpa;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "employee")
public class Employee {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Column(name = "emp_id")
	private Long empId;

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

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

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

	public Employee() {
	}

	public Employee(String firstName, String lastName, String designation) {
		super();
		this.firstName = firstName;
		this.lastName = lastName;
		this.designation = designation;
	}

	public Long getEmpId() {
		return empId;
	}

	public void setEmpId(Long empId) {
		this.empId = empId;
	}

	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 String getDesignation() {
		return designation;
	}

	public void setDesignation(String designation) {
		this.designation = designation;
	}

	@Override
	public String toString() {
		return "Employee [empId=" + empId + ", firstName=" + firstName + ", lastName=" + lastName + ", designation="
				+ designation + "]";
	}
}

4) EmployeeRepository class

Create an EmployeeRepository interface that extends from Spring Data CrudRepository interface and with the Query as shown below.

package com.kalliphant.samples.springdata.jpa;

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.transaction.annotation.Transactional;

public interface EmployeeRepository extends CrudRepository<Employee , Long> {
	@Modifying
	@Transactional
	@Query("delete from Employee e where firstName = ?1")
	void deleteUsingSingleQuery(String firstName);
}

5) DeleteByExample

This example shows how we can delete the entity using @Query/Single JPQL query.

package com.kalliphant.samples.springdata.jpa;

import java.util.Arrays;
import java.util.List;

import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class DeleteByExample {

	public static void main(String[] args) {
		AbstractApplicationContext context = new ClassPathXmlApplicationContext("springconfig.xml");
		EmployeeRepository repository = context.getBean(EmployeeRepository.class);

		// CREATE multiple employees at once using List
		System.out.println("\nInseting multiple Employees");
		List<Employee> employees = Arrays.asList(
				new Employee("John", "Joe", "Manager"),
				new Employee("Dave", "Harris", "Programmer"), 
				new Employee("Nael", "Jean", "Programmer"),
				new Employee("Consuelo", "Muñoz", "Programmer"), 
				new Employee("Ilona", "Koski", "Manager"),
				new Employee("Katie", "Kennedy", "Programmer"), 
				new Employee("Lucile", "Dufour", "Manager"),
				new Employee("Tim", "White", "Programmer"),
				new Employee("Kalliphant", "Bloge", "Programmer"));
		repository.save(employees);

		// COUNT employees
		System.out.println("\nGet count of Employees");
		System.out.println("Number of employees inserted : " + repository.count());

		// Delete User 'John'
		System.out.println("\nDeleting user 'John'");
		repository.deleteUsingSingleQuery("John");
		
		System.out.println("\nDeleted user 'John', getting count");
		System.out.println("Number of employees after delete : " + repository.count());

		context.close();
	}
}

Output

Run the application, you should get the below output.

       :
       :
Inseting multiple Employees
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)

Get count of Employees
Hibernate: select count(*) as col_0_0_ from employee employee0_
Number of employees inserted : 9

Deleting user 'John'
Hibernate: delete from employee where first_name=?

Deleted user 'John', getting count
Hibernate: select count(*) as col_0_0_ from employee employee0_
Number of employees after delete : 8

Note : Compare the output above with the output from Spring Data JPA – DeleteBy Example. Using @Query we can delete the entities with out fetching it first (See line 19 in above output and compare with the output from Spring Data JPA – DeleteBy Example)