这篇文章主要记录 使用原生sql查询 并且把查询结果转为实体对象, 注意：这儿使用的数据库 是oracle数据库
这儿记录了三种查询：精确查询，模糊查询，分页查询。
1.把原生sql查询的结果转为实体类对象用的是Query query = em.createNativeQuery(sql, Student.class);
2.实体类 要使用@Entity 和 @Id 注解，否则会报错
新建实体类
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.riskunit_id = :riskunit_id
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 students = query.getResultList();
System.out.println("students===" + students.toString());
}
}
2. 模糊查询where t.riskunit_id like '%'||:riskunit_id||'%'
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 students = query.getResultList();
System.out.println("students===" + students.toString());
}
}
3.原生sql查询并分页 这里参考oracle sql3.oracle sql 分页实现
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 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注解
