Hot File

Native SELECT SQL Query Example in java

View: 193    Dowload: 0   Comment: 0   Post by: hanhga  
Author: none   Category: Javascript   Fields: Other

0 point/0 review File has been tested

In this example, we will learn to use native sql SELECT query in JPA using createNativeQuery() method of the EntityManagerinterface. Here we pass in the query string to be executed in underlying database and the entity type that will be returned as result. We will also use named SQL native queries in this example.

Introduction

In this example, we will learn to use native sql SELECT query in JPA using createNativeQuery() method of the EntityManagerinterface. Here we pass in the query string to be executed in underlying database and the entity type that will be returned as result. We will also use named SQL native queries in this example.

Named SQL Native Queries

Named SQL queries are defined using the @NamedNativeQuery annotation. This annotation may be placed on any entity and defines the name of the query as well as the query text. Like JPQL named queries, the name of the query must be unique within the persistence unit.

Named SQL native queries are defined like this:

@Entity(name="EmployeeEntity")
@Table (name="employee")
 
@NamedNativeQueries({
        @NamedNativeQuery(
                name    =   "getAllEmployees",
                query   =   "SELECT id, firstName, lastName, email, department.id, department.name " +
                            "FROM employee, department",
                            resultClass=EmployeeEntity.class
        ),
        @NamedNativeQuery(
                name    =   "getAllEmployeesByDeptId",
                query   =   "SELECT id, firstName, lastName, email, department.id, department.name " +
                            "FROM employee, department " + 
                            "WHERE department.id = ?",
                            resultClass=EmployeeEntity.class
        )
})
 
public class EmployeeEntity implements Serializable
{
    //more code
}   

Here we have defined two named queries getAllEmployees and getAllEmployeesByDeptId. First query returns all employees data, and second one will result all employees for a department id.

Executing native SQL queries

To execute above SQL queries, you will need to write below code in your DAOImpl class.

@Override
public List<EmployeeEntity> getAllEmployees() {
    List<EmployeeEntity> employees = manager.createNamedQuery("getAllEmployees", EmployeeEntity.class)
                                            .getResultList();
    return employees;
}
 
@Override
public List<EmployeeEntity> getAllEmployeesByDeptId(Integer id) {
    List<EmployeeEntity> employees = manager.createNamedQuery("getAllEmployeesByDeptId", EmployeeEntity.class)
                                            .setParameter(1, id)
                                            .getResultList();
    return employees;
}

There are two benefits to getting managed entities back from a SQL query.

The first is that a SQL query can replace an existing JP QL query and that application code should still work without changes.
The second benefit is that it allows the developer to use SQL queries as a method of constructing new entity instances from tables that may not have any object-relational mapping. For example, in many database architectures, there is a staging area to hold data that has not yet been verified or requires some kind of transformation before it can be moved to its final location.

Using JPA, a developer could start a transaction, query the staged data to construct entities, perform any required changes, and then commit. The newly created entities will get written to the tables mapped by the entity, not the staging tables used in the SQL query.

Complete Example

EmployeeEntity.java

package com.howtodoinjava.jpa.demo.entity;
 
import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;
import javax.persistence.Table;
  
@Entity(name="EmployeeEntity")
@Table (name="employee")
 
@NamedNativeQueries({
        @NamedNativeQuery(
                name    =   "getAllEmployees",
                query   =   "SELECT id, firstName, lastName, email, department.id, department.name " +
                            "FROM employee, department",
                            resultClass=EmployeeEntity.class
        ),
        @NamedNativeQuery(
                name    =   "getAllEmployeesByDeptId",
                query   =   "SELECT id, firstName, lastName, email, department.id, department.name " +
                            "FROM employee, department " + 
                            "WHERE department.id = ?",
                            resultClass=EmployeeEntity.class
        )
})
 
public class EmployeeEntity implements Serializable
{
    private static final long serialVersionUID = 1L;
  
    @Id
    @GeneratedValue
    private Integer id;
      
    private String firstName;
    private String lastName;
    private String email;
      
    @ManyToOne
    private DepartmentEntity department;
      
    public EmployeeEntity() {}
       
    public EmployeeEntity(String name, DepartmentEntity department) {
        this.firstName = name;
        this.department = department;
    }
       
    public EmployeeEntity(String name) {
        this.firstName = name;
    }
  
    //Setters and Getters
 
    @Override
    public String toString() {
        return "EmployeeVO [id=" + id + ", firstName=" + firstName
                + ", lastName=" + lastName + ", email=" + email
                + ", department=" + department + "]";
    }
}

DepartmentEntity.java

package com.howtodoinjava.jpa.demo.entity;
 
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
 
@Entity(name="DepartmentEntity")
@Table (name="department")
public class DepartmentEntity implements Serializable {
  
    private static final long serialVersionUID = 1L;
      
    @Id
    @GeneratedValue
    private Integer id;
    private String name;
      
    public DepartmentEntity(){
    }
package com.howtodoinjava.jpa.demo.entity;
 
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
 
@Entity(name="DepartmentEntity")
@Table (name="department")
public class DepartmentEntity implements Serializable {
  
    private static final long serialVersionUID = 1L;
      
    @Id
    @GeneratedValue
    private Integer id;
    private String name;
      
    public DepartmentEntity(){
    }

DepartmentDAO.java

public interface DepartmentDAO 
{
    public boolean addDepartment(DepartmentEntity dept);
}

DepartmentDAOImpl.java

package com.howtodoinjava.jpa.demo.dao;
 
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.howtodoinjava.jpa.demo.entity.DepartmentEntity;
 
@Repository
@Transactional
public class DepartmentDAOImpl implements DepartmentDAO {
 
    @PersistenceContext
    private EntityManager manager;
 
    @Override
    public boolean addDepartment(DepartmentEntity dept) {
        try {
            manager.persist(dept);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }
}

EmployeeDAO.java

public interface EmployeeDAO 
{
    public List<EmployeeEntity> getAllEmployees();
     
    public List<EmployeeEntity> getAllEmployeesByDeptId(Integer id);
 
    public boolean addEmployee(EmployeeEntity employee);
}

EmployeeDAOImpl.java

 
 
package com.howtodoinjava.jpa.demo.dao;
 
import java.util.List;
 
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
 
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
 
import com.howtodoinjava.jpa.demo.entity.EmployeeEntity;
 
@Repository
@Transactional
public class EmployeeDAOImpl implements EmployeeDAO {
     
    @PersistenceContext
    private EntityManager manager;
 
    @Override
    public List<EmployeeEntity> getAllEmployees() {
        List<EmployeeEntity> employees = manager.createNamedQuery("getAllEmployees", EmployeeEntity.class)
                                                .getResultList();
        return employees;
    }
 @Override
    public List<EmployeeEntity> getAllEmployeesByDeptId(Integer id) {
        List<EmployeeEntity> employees = manager.createNamedQuery("getAllEmployeesByDeptId", EmployeeEntity.class)
                                                .setParameter(1, id)
                                                .getResultList();
        return employees;
    }
 
    @Override
    public boolean addEmployee(EmployeeEntity employee) {
        try{
            manager.persist(employee);
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
        return true;
    }
}

TestEmployeeDAO.java

package com.jpa.demo.test;
 
import java.util.List;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;
import com.howtodoinjava.jpa.demo.dao.DepartmentDAO;
import com.howtodoinjava.jpa.demo.dao.EmployeeDAO;
import com.howtodoinjava.jpa.demo.entity.DepartmentEntity;
import com.howtodoinjava.jpa.demo.entity.EmployeeEntity;
 
@ContextConfiguration(locations = "classpath:application-context-test.xml")
@RunWith(SpringJUnit4ClassRunner.class)
public class TestEmployeeDAO 
{
     
    @Autowired
    private EmployeeDAO employeeDAO;
     
    @Autowired
    private DepartmentDAO departmentDAO;
     
    @Test
    @Transactional
    @Rollback(true)
    public void testGetAllEmployees()
    {
        //Setup some test data in IM (in-memory) database
        setupData();
         
        List<EmployeeEntity> employees = employeeDAO.getAllEmployees();
         
        //Validate that data is found
        Assert.assertEquals(employees.size(), 1);
        EmployeeEntity employeeEntity = employees.get(0);
         
        //Now check if we got correct data
        Assert.assertEquals(employeeEntity.getFirstName(),"Lokesh");
        Assert.assertEquals(employeeEntity.getLastName(),"Gupta");
        Assert.assertEquals(employeeEntity.getDepartment().getName(),"Human Resource");
    }
     
    @Test
    @Transactional
    @Rollback(true)
    public void testGetAllEmployeesByDeptId()
    {
        //Setup some test data in IM (in-memory) database
        setupData();
         
        List<EmployeeEntity> employees = employeeDAO.getAllEmployeesByDeptId(1);
         
        //Validate that data is found
        Assert.assertEquals(employees.size(), 1);
        EmployeeEntity employeeEntity = employees.get(0);
         
        //Now check if we got correct data
        Assert.assertEquals(employeeEntity.getFirstName(),"Lokesh");
        Assert.assertEquals(employeeEntity.getLastName(),"Gupta");
        Assert.assertEquals(employeeEntity.getDepartment().getName(),"Human Resource");
    }
     
    public void setupData()
    {
        DepartmentEntity department = new DepartmentEntity("Human Resource");
        departmentDAO.addDepartment(department);
         
        EmployeeEntity employee = new EmployeeEntity();
        employee.setFirstName("Lokesh");
        employee.setLastName("Gupta");
        employee.setEmail("howtodoinjava@gmail.com");
        employee.setDepartment(department);
         
        employeeDAO.addEmployee(employee);
    }
}

Output of above testcases will be:

Drop me your questions and comments.

Happy Learning !!

Native SELECT SQL Query Example in java

Native SELECT SQL Query Example in java Posted on 09-08-2016  In this example, we will learn to use native sql SELECT query in JPA using createNativeQuery() method of the EntityManagerinterface. Here we pass in the query string to be executed in underlying database and the entity type that will be returned as result. We will also use named SQL native queries in this example. 5/10 193

Comment:

To comment you must be logged in members.

Files with category

  • JUnit 5 State Of The Union using java

    View: 471    Download: 0   Comment: 0   Author: none  

    JUnit 5 State Of The Union using java

    Category: Javascript
    Fields: Other

    4.5/1 review
    JUnit 5 has been under development for about 14 months now and the prototype is almost a year old. Time to summarize what happened so far, where the project stands, and where it’s going.

  • Getting Started with Dropwizard using java

    View: 542    Download: 0   Comment: 0   Author: none  

    Getting Started with Dropwizard using java

    Category: Javascript
    Fields: Other

    4.5/1 review
    Dropwizard is a framework for building RESTful web services in Java. In this tutorial we’re going to have a look at how to get started with developing a Dropwizard application by building a new service from scratch.

  • Build Query NULL Value in MySql

    View: 248    Download: 0   Comment: 0   Author: none  

    Build Query NULL Value in MySql

    Category: Javascript
    Fields: Other

    2.5/2 review
    Misunderstanding NULL is common mistake beginners do while writing MySql query. While quering in MySql they compare column name with NULL. In MySql NULL is nothing or in simple word it isUnknown Value so if you use comparison operator for NULL values...

  • Manage Your JavaScript Application State with MobX

    View: 255    Download: 0   Comment: 0   Author: none  

    Manage Your JavaScript Application State with MobX

    Category: Javascript
    Fields: Other

    2.25/2 review
    This article was peer reviewed by Michel Weststrate and Aaron Boyer. Thanks to all of SitePoint’s peer reviewers for making SitePoint content the best it can be!

  • Build Bringing Pages to Life with the Web Animations API

    View: 263    Download: 0   Comment: 0   Author: none  

    Build Bringing Pages to Life with the Web Animations API

    Category: Javascript
    Fields: Other

    4.5/2 review
    This article is by guest author Dudley Storey. SitePoint guest posts aim to bring you engaging content from prominent writers and speakers of the JavaScript community.

  • How to Style Google Custom Search Manually

    View: 237    Download: 0   Comment: 0   Author: none  

    How to Style Google Custom Search Manually

    Category: Javascript
    Fields: Other

    0/0 review
    Website owners very often decide on using Google’s Custom Search Engine (GCSE) for searching through their content instead of using built-in and/or custom search functionality. The reason is simple – it’s much less work, and most often it does the...

  • Test React Components Using Jest

    View: 4859    Download: 0   Comment: 0   Author: none  

    Test React Components Using Jest

    Category: Javascript
    Fields: Other

    4.5/1 review
    This article is by guest author Jack Franklin. SitePoint guest posts aim to bring you engaging content from prominent writers and speakers of the JavaScript community.

  • Programming Question Reverse String without using String function

    View: 641    Download: 0   Comment: 0   Author: none  

    Programming Question Reverse String without using String function

    Category: Javascript
    Fields: Other

    0/0 review
    Write a program to reverse string without using string function. You don’t have to use any in-built string library function. This problem can be solved by multiple approaches. Let’s check it.

 
Newsletter Email

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd