http://blog.csdn.net/chenallen1025/article/details/9169543
EntityManager API 提供了创建 Query 实例以执行原生 SQL 语句的createNativeQuery方法。
实体User:
- package com.cndatacom.jpa.entity;
- import javax.persistence.Column;
- import javax.persistence.Entity;
- import javax.persistence.GeneratedValue;
- import javax.persistence.Id;
- import javax.persistence.Table;
- @Entity
- @Table(name="t_user")
- public class User {
- /**
- * 主键
- */
- @Id
- @GeneratedValue
- private Long id;
- /**
- * 名字
- */
- @Column(name="name",length=50)
- private String name;
- /**
- * 密码
- */
- @Column(name="password",length=20)
- private String password;
- /**
- * 邮箱
- */
- @Column(name="email",length=50)
- private String email;
- /**
- * 年龄
- */
- @Column(name="age",length=3)
- private int age;
- public User() {
- }
- //以下省略getter/setter方法
- //......
- }
测试:
- package com.cndatacom.jpa.test;
- import java.util.List;
- import javax.persistence.EntityManager;
- import javax.persistence.EntityManagerFactory;
- import javax.persistence.Persistence;
- import javax.persistence.Query;
- import org.junit.After;
- import org.junit.Before;
- import org.junit.Test;
- import com.cndatacom.jpa.entity.User;
- /**
- * 测试JPA原生SQL查询
- * @author Luxh
- */
- public class TestNativeQuery {
- EntityManagerFactory emf = null;
- @Before
- public void before() {
- //根据在persistence.xml中配置的persistence-unit name 创建EntityManagerFactory
- emf = Persistence.createEntityManagerFactory("myJPA");
- }
- @After
- public void after() {
- //关闭EntityManagerFactory
- if(null != emf) {
- emf.close();
- }
- }
- /**
- * 查询的结果是对象数组的集合
- */
- @Test
- public void testNativeQuery1() {
- EntityManager em = emf.createEntityManager();
- //定义SQL
- String sql = "SELECT * FROM t_user";
- //创建原生SQL查询QUERY实例
- <span style="color:#ff0000;">Query query = em.createNativeQuery(sql);</span>
- //执行查询,返回的是对象数组(Object[])列表,
- //每一个对象数组存的是相应的实体属性
- List objecArraytList = query.getResultList();
- for(int i=0;i<objecArraytList.size();i++) {
- Object[] obj = (Object[]) objecArraytList.get(i);
- //使用obj[0],obj[1],obj[2]...取出属性
- }
- em.close();
- }
- /**
- * 查询的结果是实体的集合
- */
- @Test
- public void testNativeQuery2() {
- EntityManager em = emf.createEntityManager();
- //定义SQL
- String sql = "SELECT * FROM t_user";
- //创建原生SQL查询QUERY实例,指定了返回的实体类型
- <span style="color:#ff0000;"> Query query = em.createNativeQuery(sql,User.class);
- </span> //执行查询,返回的是实体列表,
- List<User> userList = query.getResultList();
- em.close();
- }
- /**
- * 查询单个属性
- * 返回的是这个属性值的集合
- */
- @Test
- public void testNativeQuery3() {
- EntityManager em = emf.createEntityManager();
- //定义SQL
- String sql = "SELECT t.name FROM t_user t";
- //创建原生SQL查询QUERY实例
- <span style="color:#ff0000;"> Query query = em.createNativeQuery(sql);</span>
- //执行查询,返回的是String类型的集合,因为name这个属性是String类型
- List<String> resultList = query.getResultList();
- em.close();
- }
- /**
- * 查询多个属性
- * 返回的是这些属性值的数组的集合
- */
- @Test
- public void testNativeQuery4() {
- EntityManager em = emf.createEntityManager();
- //定义SQL
- String sql = "SELECT t.name,t.age,t.email FROM t_user t";
- //创建原生SQL查询QUERY实例
- <span style="color:#ff0000;"> Query query = em.createNativeQuery(sql); </span>
- //执行查询,返回的是查询属性值数组的集合
- List objecArraytList = query.getResultList();
- for(int i=0;i<objecArraytList.size();i++) {
- Object[] obj = (Object[]) objecArraytList.get(i);
- //使用obj[0],obj[1],obj[2]取出属性
- }
- em.close();
- }
- }