JPA EntityManager查询--使用原生sql 并且把查询结果转为实体对象

这篇文章主要记录 使用原生sql查询 并且把查询结果转为实体对象, 注意：这儿使用的数据库 是oracle数据库

这儿记录了三种查询：精确查询，模糊查询，分页查询。

1.把原生sql查询的结果转为实体类对象用的是 Query query = em.createNativeQuery(sql, Student.class);

 2.实体类 要使用@Entity 和 @Id 注解，否则会报错

新建实体类

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

import javax.persistence.Entity; import javax.persistence.Id;   @Entity public class Student {     @Id     private Long classNo;     private Long studentNo;     private String remark;     //省略了getter setter方法     @Override     public String toString() {         return "RiskUnit [classNo=" + classNo + ", studentNo=" + studentNo + ", remark=" + remark + "]";     }    }

 

1. 精确查询   where t.class_no = :class_no

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

import java.util.List;   import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import javax.persistence.Query;    public class JpaDemoTest {     @PersistenceContext     private EntityManager em;       public void testJpaNativeQuery() {           // sql         String sql = "select t.class_no  classNo ,t.student_no studentNo ,t.remark remark  from t_student t where t.class_no = :class_no ";         // 执行查询 并把结果专为实体类         Query query = em.createNativeQuery(sql, Student.class);         // 参数值         query.setParameter("class_no", 400002);         // 获取查询结果         List<Student> students = query.getResultList();         System.out.println("students===" + students.toString());     } }

　　

2. 模糊查询    where t.class_no like '%'||:class_no||'%' "

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

import java.util.List;   import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import javax.persistence.Query;    public class JpaDemoTest {     @PersistenceContext     private EntityManager em;       public void testJpaNativeQuery() {           // sql         String sql = "select t.class_no  classNo ,t.student_no studentNo ,t.remark remark  from t_student t where t.class_no like '%'||:class_no||'%' ";         // 执行查询 并把结果专为实体类         Query query = em.createNativeQuery(sql, Student.class);         // 参数值         query.setParameter("class_no", 400002);         // 获取查询结果         List<Student> students = query.getResultList();         System.out.println("students===" + students.toString());     } }

　　

3. 原生sql查询并分页  这里参考oracle sql 3.oracle sql 分页实现

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

import java.util.List; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import javax.persistence.Query;   public class JpaDemoTest {     @PersistenceContext     private EntityManager em;       public void testJpaNativeQuery() {         Integer pageNo = 2;         Integer pageSize = 10;         String sql = "select t.class_no  classNo ,t.student_no studentNo ,t.remark remark  from t_student t where t.class_no like '%' || :class_no || '%' order by t.class_no ";         sql = " select * from ( select a.*, rownum as a_rownum from (" + sql + ") a where rownum <= "                 + (pageNo + 1) * pageSize + ") b where b.a_rownum > " + pageNo * pageSize;         Query query = em.createNativeQuery(sql, Student.class);         query.setParameter("class_no", 40000);         List<Student> students = query.getResultList();         System.out.println("student===" + students.toString());     } }

　　

4. 有几种可能hibernate或者sql的报错：

1.Caused by: java.sql.SQLSyntaxErrorException: ORA-01747: invalid user.table.column, table.column, or column specification

 查询sql的字段名与数据库关键字冲突了，仔细查看sql 是因为where t.like 写错了

2.javax.persistence.PersistenceException: org.hibernate.MappingException: Unknown entity: com.mx.JpaDemo.entity.RiskUnit
需要在实体类RiskUnit上加上@Entity注解 

3.Caused by: org.hibernate.AnnotationException: No identifier specified for entity: com.mx.JpaDemo.entity.RiskUnit

需要在实体类加上@Id注解