EntityManager API 提供了创建 Query 实例以执行原生 SQL 语句的createNativeQuery方法。
实体User:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
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方法
//......
}
|
测试:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
|
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实例
Query query = em.createNativeQuery(sql);
//执行查询,返回的是对象数组(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实例,指定了返回的实体类型
Query query = em.createNativeQuery(sql,User.
class
);
//执行查询,返回的是实体列表,
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实例
Query query = em.createNativeQuery(sql);
//执行查询,返回的是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实例
Query query = em.createNativeQuery(sql);
//执行查询,返回的是查询属性值数组的集合
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();
}
}
|