在此JPA 原生查询示例 中，我们将学习使用JPA 原生查询（SQL SELECT 查询）使用< createNativeQuery() 接口的 code>$$$ 方法。我们将传入要在底层数据库中执行的查询字符串和将作为结果返回的实体类型。在此示例中，我们还将使用命名的 sql 本机查询。
本机查询与命名查询
1. Native query是指实际的sql查询（指实际的数据库对象）。这些查询是可以使用数据库客户端直接在数据库中执行的sql语句。
2。 命名查询 是您通过给查询命名来定义查询的方式。您可以在 hibernate 的映射文件中定义它，也可以在实体级别使用注释。
1.定义JPA原生查询
命名 SQL 查询是使用 @NamedNativeQuery 注释定义的。此注释可以放置在任何实体上并定义查询的名称以及查询文本。与 JPQL 命名查询一样，查询的名称在持久性单元中必须是唯一的。
命名 SQL 本机查询定义如下：
@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
}
这里我们定义了两个命名查询 getAllEmployees 和 getAllEmployeesByDeptId。第一个查询返回所有员工数据，第二个查询将返回所有员工的部门 ID。
2.执行原生查询
要执行上述 SQL 查询，您需要在 DAOImpl 类中编写以下代码。
@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;
}
对于返回实体的 SQL 查询，需要注意的一件事是生成的实体实例由持久性上下文管理，就像 JPQL 查询的结果一样。如果您修改返回的实体之一，当持久性上下文与事务相关联时，它将被写入数据库。
因此，确保完全构建实体所需的所有必要数据是查询的一部分非常重要。如果您从查询中遗漏了一个字段，或者将其默认为某个值，然后修改生成的实体，则您可能会覆盖已存储在数据库中的正确版本。
从 SQL 查询中获取托管实体有两个好处。
- 首先是 SQL 查询可以替换现有的 JP QL 查询，并且应用程序代码无需更改即可继续工作。
- 第二个好处是，它允许开发人员使用 SQL 查询作为从可能没有任何对象关系映射的表中构造新实体实例的方法。
例如，在许多数据库架构中，有一个暂存区用于保存尚未验证或需要某种转换才能移动到最终位置的数据。
使用 JPA，开发人员可以启动事务、查询暂存数据以构建实体、执行任何所需的更改，然后提交。新创建的实体将写入实体映射的表，而不是 SQL 查询中使用的登台表。
3. JPA 原生查询示例
3.1.实体类
package com.cundage.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 + "]";
}
}
package com.cundage.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(){
}
public DepartmentEntity(String name) {
super();
this.name = name;
}
@OneToMany(mappedBy="department",cascade=CascadeType.PERSIST)
private List<EmployeeEntity> employees = new ArrayList<EmployeeEntity>();
//Setters and Getters
@Override
public String toString() {
return "DepartmentVO [id=" + id + ", name=" + name + "]";
}
}
3.2.道
public interface DepartmentDAO
{
public boolean addDepartment(DepartmentEntity dept);
}
package com.cundage.jpa.demo.dao;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.cundage.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;
}
}
public interface EmployeeDAO
{
public List<EmployeeEntity> getAllEmployees();
public List<EmployeeEntity> getAllEmployeesByDeptId(Integer id);
public boolean addEmployee(EmployeeEntity employee);
}
package com.cundage.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.cundage.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;
}
}
3.3.演示
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.cundage.jpa.demo.dao.DepartmentDAO;
import com.cundage.jpa.demo.dao.EmployeeDAO;
import com.cundage.jpa.demo.entity.DepartmentEntity;
import com.cundage.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("cundage@gmail.com");
employee.setDepartment(department);
employeeDAO.addEmployee(employee);
}
}
程序输出。
Hibernate: drop table department if exists Hibernate: drop table employee if exists Hibernate: create table department (id integer generated by default as identity (start with 1), name varchar(255), primary key (id)) Hibernate: create table employee (id integer generated by default as identity (start with 1), email varchar(255), firstName varchar(255), lastName varchar(255), department_id integer, primary key (id)) Hibernate: alter table employee add constraint FK4722E6AE5591DEEE foreign key (department_id) references department HHH000230: Schema export complete Hibernate: insert into department (id, name) values (default, ?) binding parameter [1] as [VARCHAR] - Human Resource Hibernate: insert into employee (id, department_id, email, firstName, lastName) values (default, ?, ?, ?, ?) binding parameter [1] as [INTEGER] - 1 binding parameter [2] as [VARCHAR] - cundage@gmail.com binding parameter [3] as [VARCHAR] - Lokesh binding parameter [4] as [VARCHAR] - Gupta Hibernate: SELECT id, firstName, lastName, email, department.id, department.name FROM employee, department WHERE department.id = ? binding parameter [1] as [INTEGER] - 1 Found [1] as column [id] Hibernate: insert into department (id, name) values (default, ?) binding parameter [1] as [VARCHAR] - Human Resource Hibernate: insert into employee (id, department_id, email, firstName, lastName) values (default, ?, ?, ?, ?) binding parameter [1] as [INTEGER] - 2 binding parameter [2] as [VARCHAR] - cundage@gmail.com binding parameter [3] as [VARCHAR] - Lokesh binding parameter [4] as [VARCHAR] - Gupta Hibernate: SELECT id, firstName, lastName, email, department.id, department.name FROM employee, department Found [2] as column [id]
把你的问题和意见告诉我。
快乐学习！！
参考：
标签2： JPA地址：https://www.cundage.com/article/jpa-native-query-example-select.html
相关阅读
在 JPA 中，要删除一个实体，实体本身必须被管理，这意味着它存在于持久性上下文中。这意味着调用应用程序应该已经加载或访问了实体，现在正在发出删除它的命令。 这通常不是问题，因为应用程序通常会在...
在JPA delete entity example中，我们将学习delete JPA entities using @NamedNativeQuery created with createN...
在此JPA 原生查询示例 中，我们将学习使用JPA 原生查询（SQL SELECT 查询）使用< createNativeQuery() 接口的 code>l;$$$ 方法。我们将传入...
在此JPA executeUpdate 示例中，我们将学习使用jpa 存储库中的更新查询，该存储库是使用$$ 的createNativeQuery() 方法创建的$ 接口。在这里，我们传入要在基...
在 JPA 中，一旦一个实体被持久化在数据库中，通常要做的下一件事就是找到再次。让我们看看如何使用实体管理器找到实体。 使用 EntityManager.find() 实际上，只有一行是重要的：...
在这个例子中，我们将学习配置 Spring MVC 和 Hibernate with JPA 以及其他一些东西，例如验证和自定义属性编辑器。过去我被要求写这个教程的次数已经够多了，所以就在这里。...
在JPA中，实体的映射信息必须在运行时可供ORM提供者访问，以便在将数据写入存储时，可以从实体实例中获取映射信息。同样，当从存储中加载实体状态时，提供者运行时必须能够将数据映射到新的实体实例中。...
Spring Boot Data JPA 排序教程展示了如何在 Spring Data JPA 中对查询结果进行排序。查询结果使用 ORDER BY 子句或 Sort 对象进行排序。 春天 是一...
Spring Data JPA 允许您通过使用 Repository 接口快速开发数据访问层。有时您需要从 Spring Data JPA 访问 EntityManager。这篇文章向您展示了如...
我们已经在之前的教程中了解了 mapping associated entities in hibernate，例如 one-to-one mapping 和 < strong>l;一...
热门文章
最新发布：