JPA 原生查询示例
实体类
package com.onitroad.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.onitroad.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 + "]"; } }
DAO
public interface DepartmentDAO { public boolean addDepartment(DepartmentEntity dept); }
package com.onitroad.jpa.demo.dao; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; import com.onitroad.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.onitroad.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.onitroad.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; } }
测试
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.onitroad.jpa.demo.dao.DepartmentDAO; import com.onitroad.jpa.demo.dao.EmployeeDAO; import com.onitroad.jpa.demo.entity.DepartmentEntity; import com.onitroad.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(),"JackLi"); 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(),"JackLi"); 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("JackLi"); employee.setLastName("Gupta"); employee.setEmail("onitroad@gmail.com"); employee.setDepartment(department); employeeDAO.addEmployee(employee); } }
通过 EntityManager
接口的 createNativeQuery()
方法使用 JPA 原生查询(SQL SELECT 查询)。
本机查询与命名查询
原生查询是指实际的sql查询(指实际的数据库对象)。
这些查询是可以使用数据库客户端直接在数据库中执行的 sql 语句。命名查询是我们通过为其命名来定义查询的方式。
我们可以在 hibernate 的映射文件中定义它,也可以在实体级别使用注释。
JPA 执行原生查询
要执行上述 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 查询中使用的临时表。
定义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 的所有员工。