@Query
annotation is used to perform both JPA and native queries.
For the
below entity
@Entity
@Table(name
= "my_employee")
public
class Employee {
@Id
@GeneratedValue(strategy =
GenerationType.AUTO)
@Column(name = "employee_id")
private int id;
.....
.....
}
Example
1: JPA Query
@Query("SELECT
e.firstName FROM Employee e")
List<String>
getFirstNames();
As you
see, we are using Entity class name ‘Employee’ to execute JPA query.
Example
2: Native Query
@Query(value
= "SELECT e.first_name FROM my_employee e", nativeQuery=true)
List<String>
getFirstNamesNativeQuery();
You should
use actual table name ‘my_employee’ and column name ‘first_name’ while
executing native query. You should set nativeQuery to true, while executing
native query.
Find the
below working application.
Step 1:
Create
'com.sample.app.model' and define Employee.java like below.
package com.sample.app.model; 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 = "my_employee") public class Employee { @Id @GeneratedValue(strategy = GenerationType.AUTO) @Column(name = "employee_id") private int id; @Column(name = "first_name") private String firstName; @Column(name = "last_name") private String lastName; @Column(name = "age") private int age; @Column(name = "salary") private double salary; 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; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public double getSalary() { return salary; } public void setSalary(double salary) { this.salary = salary; } public static EmployeeBuilder builder() { return new EmployeeBuilder(); } public static class EmployeeBuilder { private Employee emp; public EmployeeBuilder() { emp = new Employee(); } public EmployeeBuilder firstName(String firstName) { emp.setFirstName(firstName); return this; } public EmployeeBuilder lastName(String lastName) { emp.setLastName(lastName); return this; } public EmployeeBuilder age(int age) { emp.setAge(age); return this; } public EmployeeBuilder salary(double salary) { emp.setSalary(salary); return this; } public Employee build() { return emp; } } @Override public String toString() { StringBuilder builder = new StringBuilder(); builder.append("Employee [id=").append(id).append(", firstName=").append(firstName).append(", lastName=") .append(lastName).append(", age=").append(age).append(", salary=").append(salary).append("]"); return builder.toString(); } }
Step 2: 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.Query; import org.springframework.data.repository.CrudRepository; import com.sample.app.model.Employee; public interface EmployeeRepository extends CrudRepository<Employee, Integer> { @Query("SELECT e.firstName FROM Employee e") List<String> getFirstNames(); @Query(value = "SELECT e.first_name FROM my_employee e", nativeQuery=true) List<String> getFirstNamesNativeQuery(); }
Step 3: Create application.properties file
under 'src/main/resources' folder.
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
Step 4: Define App.java class under
com.sample.app package.
package com.sample.app; 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.model.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 employeeRepository) { return (args) -> { Employee emp1 = Employee.builder().firstName("Ram").lastName("Gurram").age(32).salary(100000.23).build(); Employee emp2 = Employee.builder().firstName("Joel").lastName("Chelli").age(43).salary(60000).build(); Employee emp3 = Employee.builder().firstName("Gopi").lastName("Battu").age(45).salary(1000000).build(); Employee emp4 = Employee.builder().firstName("Bomma").lastName("Srikanth").age(39).salary(60000).build(); Employee emp5 = Employee.builder().firstName("Surendra").lastName("Sami").age(32).salary(100000.23).build(); employeeRepository.save(emp1); employeeRepository.save(emp2); employeeRepository.save(emp3); employeeRepository.save(emp4); employeeRepository.save(emp5); System.out.println("Running JPA Query"); List<String> firstNames = employeeRepository.getFirstNames(); for(String name : firstNames) { System.out.println(name); } System.out.println("Running native query"); firstNames = employeeRepository.getFirstNamesNativeQuery(); for(String name : firstNames) { System.out.println(name); } }; } }
Total
project structure looks like below.
Run App.java, you can see below messages in console.
. ____ _ __ _ _ /\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \ ( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \ \\/ ___)| |_)| | | | | || (_| | ) ) ) ) ' |____| .__|_| |_|_| |_\__, | / / / / =========|_|==============|___/=/_/_/_/ :: Spring Boot :: (v2.1.6.RELEASE) Ram Joel Gopi Bomma Surendra Running native query Ram Joel Gopi Bomma Surendra
You can
download complete working application from this link.
No comments:
Post a Comment