Friday 20 September 2019

Spring data jpa: Extract specific fields from table

Below snippet extract firstName and lastName from employee table.

@Query("SELECT e.firstName, e.lastName " + "FROM Employee e " + "WHERE e.id in :ids ")
List<Object[]> findFirstAndLastNames(@Param(value = "ids") List<Integer> ids);

Find the below working application.

Step 1: Create new maven project ‘springDataExtractFields’.

Step 2: Update pom.xml with maven dependencies.

pom.xml
<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>springdataExtractFields</groupId>
 <artifactId>springdataExtractFields</artifactId>
 <version>1</version>

 <parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-parent</artifactId>
  <version>2.1.6.RELEASE</version>
 </parent>

 <properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
 </properties>


 <dependencies>

  <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa -->
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-data-jpa</artifactId>
  </dependency>


  <!-- https://mvnrepository.com/artifact/com.h2database/h2 -->
  <dependency>
   <groupId>com.h2database</groupId>
   <artifactId>h2</artifactId>
  </dependency>

 </dependencies>
</project>

Step 3: Create a package ‘com.sample.entity’ and define class Employee.

Employee.java    
package com.sample.app.entity;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

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

 @Id
 private int id;
 private String firstName;
 private String lastName;

 public Employee() {
 }

 public Employee(int id, String firstName, String lastName) {
  super();
  this.id = id;
  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 + "]";
 }

}

Step 4: Create a package ‘com.sample.app.repository’ and define EmployeeRepository interface like below.

EmployeeRepository.java    
package com.sample.app.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.sample.app.entity.Employee;

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
 @Query("SELECT e.firstName, e.lastName " + "FROM Employee e " + "WHERE e.id in :ids ")
 List<Object[]> findFirstAndLastNames(@Param(value = "ids") List<Integer> ids);
}

Step 5: Define App.java.

App.java
package com.sample.app;

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

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(1, "Harini", "Maj");
   Employee emp2 = new Employee(2, "Surya", "Konagandla");
   Employee emp3 = new Employee(3, "Ram", "Gunju");
   Employee emp4 = new Employee(4, "Rahim", "Khan");
   Employee emp5 = new Employee(5, "Joel", "Chelli");

   empRepo.saveAll(Arrays.asList(emp1, emp2, emp3, emp4, emp5));

   List<Object[]> emps = empRepo.findFirstAndLastNames(Arrays.asList(2, 3, 5));

   if (emps == null || emps.isEmpty())
    return;

   for (Object[] obj : emps) {
    System.out.println(obj[0] + "," + obj[1]);
   }
  };

 }

}


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=true
#spring.jpa.properties.hibernate.format_sql=true

## 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.

Surya,Konagandla
Ram,Gunju
Joel,Chelli

You can download complete working application from this link.
    

Previous                                                    Next                                                    Home

No comments:

Post a Comment