We can
execute queries using @Query annotation. If you are performing UPDATE/DELETE
queries, you should annotate that query method with @Transactional and
@Modifying annotations.
public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
@Transactional
@Modifying
@Query("UPDATE Employee SET firstName=UPPER(firstName) WHERE id in :ids")
public int toUpperEmployeeFirstNames(@Param(value = "ids") List<Integer> ids);
@Transactional
@Modifying
@Query("DELETE FROM Employee WHERE id in :ids")
public int deleteEmployees(@Param(value = "ids") List<Integer> ids);
}
'toUpperEmployeeFirstNames'
method convert employee first names to upper case for given employee ids.
'deleteEmployees'
method take list of employee ids as input and delete the employees that matches
to given ids.
Find the below
working application.
package com.sample.app.entity;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name = "employees")
public class Employee {
@Id
@GeneratedValue
@Column(name = "EMPLOYEE_ID")
private int id;
@Column(name = "EMPLOYEE_FIRST_NAME")
private String firstName;
@Column(name = "EMPLOYEE_LAST_NAME")
private String lastName;
public Employee() {
}
public Employee(String firstName, String lastName) {
this.firstName = firstName;
this.lastName = lastName;
}
public int getId() {
return id;
}
public void setId(int 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;
}
@Override
public String toString() {
return "Employee [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + "]";
}
}
EmployeeRepository.java
package com.sample.app.repository;
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;
import com.sample.app.entity.Employee;
public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
@Transactional
@Modifying
@Query("UPDATE Employee SET firstName=UPPER(firstName) WHERE id in :ids")
public int toUpperEmployeeFirstNames(@Param(value = "ids") List<Integer> ids);
@Transactional
@Modifying
@Query("DELETE FROM Employee WHERE id in :ids")
public int deleteEmployees(@Param(value = "ids") List<Integer> ids);
}
App.java
package com.sample.app;
import java.util.Arrays;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import com.sample.app.entity.Employee;
import com.sample.app.repository.EmployeeRepository;
@SpringBootApplication
public class App {
public static void main(String args[]) {
SpringApplication.run(App.class, args);
}
@Bean
public CommandLineRunner demo(EmployeeRepository empRepo) {
return (args) -> {
Employee emp1 = new Employee("Naresh", "Deva");
Employee emp2 = new Employee("Nani", "Kulkarni");
Employee emp3 = new Employee("Ram", "Kishore");
empRepo.saveAll(Arrays.asList(emp1, emp2, emp3));
empRepo.findAll().forEach(System.out::println);
int updatedRecords = empRepo.toUpperEmployeeFirstNames(Arrays.asList(1, 2));
System.out.println("\nTotal records updated : " + updatedRecords + "\n");
empRepo.findAll().forEach(System.out::println);
int deletedRecords = empRepo.deleteEmployees(Arrays.asList(2, 3));
System.out.println("\nTotal records deleted : " + deletedRecords + "\n");
empRepo.findAll().forEach(System.out::println);
};
}
}
application.properties
logging.level.root=WARN logging.level.org.hibernate=ERROR ## H2 specific properties spring.h2.console.enabled=true spring.h2.console.path=/h2 spring.datasource.url=jdbc:h2:file:~/db/myOrg.db;DB_CLOSE_ON_EXIT=FALSE;DB_CLOSE_DELAY=-1; spring.datasource.username=krishna spring.datasource.password=password123 spring.datasource.driverClassName=org.h2.Driver ## JPA specific properties # Creates the schema, destroying previous data. spring.jpa.hibernate.ddl-auto=create spring.jpa.database-platform=org.hibernate.dialect.H2Dialect spring.jpa.show-sql=false spring.jpa.properties.hibernate.format_sql=false ## Database connection pooling properties # Number of ms to wait before throwing an exception if no connection is available. spring.datasource.max-wait=10000 # Maximum number of active connections that can be allocated from this pool at the same time. spring.datasource.tomcat.max-active=10 spring.datasource.tomcat.max-idle=5 spring.datasource.tomcat.min-idle=3
Total
project structure looks like below.
Run
App.java, you can see below messages in console.
Employee [id=1, firstName=Naresh, lastName=Deva] Employee [id=2, firstName=Nani, lastName=Kulkarni] Employee [id=3, firstName=Ram, lastName=Kishore] Total records updated : 2 Employee [id=1, firstName=NARESH, lastName=Deva] Employee [id=2, firstName=NANI, lastName=Kulkarni] Employee [id=3, firstName=Ram, lastName=Kishore] Total records deleted : 2 Employee [id=1, firstName=NARESH, lastName=Deva]
You can
download complete working application from this link.
No comments:
Post a Comment