Hot File

JPA – Native UPDATE SQL Query Example in java

View: 100    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 UPDATE 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.

Introduction

In this example, we will learn to use native sql UPDATE 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 use named SQL native queries for defining the queries in one place in this example.

Writing SQL UPDATE query as named native query

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 native queries for SQL UPDATE statements are defined like this:

@Entity(name="EmployeeEntity")
@Table (name="employee")
 
@SqlResultSetMapping(name="updateResult", columns = { @ColumnResult(name = "count")})
 
@NamedNativeQueries({
        @NamedNativeQuery(
                name    =   "updateEmployeeName",
                query   =   "UPDATE employee SET firstName = ?, lastName = ? WHERE id = ?"
                ,resultSetMapping = "updateResult"
        )
})
 
public class EmployeeEntity implements Serializable
{
    //more code
}   

Here we have defined one named native query updateEmployeeName. It can be used to update the employee’s first name and last name in database directly.

Executing native SQL queries

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

@PersistenceContext
private EntityManager manager;
 
@Override
public boolean updateEmployeeName( Integer id, String firstName, String lastName ) 
{
    try
    {
        manager.createNamedQuery("updateEmployeeName", EmployeeEntity.class)
        .setParameter(1, firstName)
        .setParameter(2, lastName)
        .setParameter(3, id)
        .executeUpdate();
         
        return true;
    }
    catch (Exception e)
    {
        return false;
    }
}

Complete Example

EmployeeEntity.java

package com.howtodoinjava.jpa.demo.entity;
 
import java.io.Serializable;
import javax.persistence.ColumnResult;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;
import javax.persistence.SqlResultSetMapping;
import javax.persistence.Table;
  
@Entity(name="EmployeeEntity")
@Table (name="employee")
 
@SqlResultSetMapping(name="updateResult", columns = { @ColumnResult(name = "count")})
@NamedNativeQueries({
        @NamedNativeQuery(
                name    =   "updateEmployeeName",
                query   =   "UPDATE employee SET firstName = ?, lastName = ? WHERE id = ?"
                ,resultSetMapping = "updateResult"
        )
})
 
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;
      
    public EmployeeEntity() {}
 
    //Setters and Getters
     
    @Override
    public String toString() {
        return "EmployeeVO [id=" + id + ", firstName=" + firstName
                + ", lastName=" + lastName + ", email=" + email + "]";
    }
}

EmployeeDAO.java

public interface EmployeeDAO 
{
    public boolean updateEmployeeName(Integer id, String firstName, String lastName);
     
    public EmployeeEntity getEmployeeById(Integer id);
 
    public boolean addEmployee(EmployeeEntity employee);
}

EmployeeDAOImpl.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.EmployeeEntity;
 
@Repository
@Transactional
public class EmployeeDAOImpl implements EmployeeDAO 
{
 
    @PersistenceContext
    private EntityManager manager;
     
    @Override
    public boolean updateEmployeeName( Integer id, String firstName, String lastName ) 
    {
        try
        {
            manager.createNamedQuery("updateEmployeeName", EmployeeEntity.class)
            .setParameter(1, firstName)
            .setParameter(2, lastName)
            .setParameter(3, id)
            .executeUpdate();
             
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
 
    @Override
    public boolean addEmployee(EmployeeEntity employee) {
        try
        {
            manager.persist(employee);
        } 
        catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }
 
    @Override
    public EmployeeEntity getEmployeeById(Integer id) {
        EmployeeEntity employee = null;
        try
        {
            employee = manager.find(EmployeeEntity.class, id);
        } 
        catch (Exception e) {
            e.printStackTrace();
        }
        return employee;
    }
}

TestEmployeeDAO.java

package com.jpa.demo.test;
 
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
 
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.EmployeeDAO;
import com.howtodoinjava.jpa.demo.entity.EmployeeEntity;
 
@ContextConfiguration(locations = "classpath:application-context-test.xml")
@RunWith(SpringJUnit4ClassRunner.class)
public class TestEmployeeDAO 
{
     
    @Autowired
    private EmployeeDAO employeeDAO;
     
    @PersistenceContext
    private EntityManager manager;
     
    /*@Autowired
    private DepartmentDAO departmentDAO;*/
     
    @Test
    @Transactional
    @Rollback(true)
    public void testUpdateEmployee()
    {
        //Setup some test data in IM (in-memory) database
        setupData();
         
        EmployeeEntity employee = employeeDAO.getEmployeeById(1);
                 
        //Now check if we got correct data
        Assert.assertEquals(employee.getFirstName(),"Lokesh");
        Assert.assertEquals(employee.getLastName(),"Gupta");
         
        /***********Now update the first name and last name***********/
         
        employeeDAO.updateEmployeeName(1, "NewFirstName", "NewLastName");
         
        //Update the employee entity instance in current persistence session
        manager.refresh(employee);
         
        //Now check if we got correct data
        Assert.assertEquals(employee.getFirstName(),"NewFirstName");
        Assert.assertEquals(employee.getLastName(),"NewLastName");
    }
     
    public void setupData()
    {
        EmployeeEntity employee = new EmployeeEntity();
        employee.setFirstName("Lokesh");
        employee.setLastName("Gupta");
        employee.setEmail("howtodoinjava@gmail.com");
         
        employeeDAO.addEmployee(employee);
    }
}

Output of above testcases will be:

JPA – Native UPDATE SQL Query Example in java

JPA – Native UPDATE SQL Query Example in java Posted on 09-08-2016  In this example, we will learn to use native sql UPDATE 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. 5/10 100

Comment:

To comment you must be logged in members.

Files with category

  • JUnit 5 State Of The Union using java

    View: 275    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: 261    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: 134    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: 143    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: 142    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: 110    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: 4724    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: 257    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