使用jpa的时候,有时候想要返回一个pojo对象,可能是一个实体的部分属性,也有可能是两个或多个实体联合查询出来的结果,需要把他们中的部分属性给返回。曾一度,我是使用mybatis再来查询,然后再返回。后来,觉得再写一个mapper麻烦,毕竟项目是以jpa为主,所以,Google,然后再Stack Overflow上看到了一个回答。就是使用SqlResultSetMapping
具体使用方法
1 | ( |
pojo1
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
public class GroupPersonWorkerInfo {
private String workerName;
/**
* 工种
*/
private String postType;
/**
* 工价
*/
"org.jadira.usertype.moneyandcurrency.joda.PersistentMoneyMinorAmount", (type =
parameters = {"currencyCode", value = "CNY")}) .hibernate.annotations.Parameter(name =
(using = MoneySerializer.class)
(using = MoneyDeserializer.class)
private Money price;
private String groupName;
/**
* 户籍地址
*/
private String permanentAddress;
public GroupPersonWorkerInfo(String workerName, String postType, BigInteger price, String groupName, String permanentAddress) {
this.workerName = workerName;
this.postType = postType;
this.price = Money.ofMinor(CurrencyUnit.of("CNY"), price.longValue());
this.groupName = groupName;
this.permanentAddress = permanentAddress;
}
}
注意几点
- 注解使用的位置
上述注解只能使用再标注了实体类上面,标注在class实体类上面 - 注意顺序
因为我使用的是构造结果1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18/**
* The name given to the result set mapping, and used to refer
* to it in the methods of the {@link Query} and
* {@link StoredProcedureQuery} APIs.
*/
String name();
/** Specifies the result set mapping to entities. */
EntityResult[] entities() default {};
/**
* Specifies the result set mapping to constructors.
* @since Java Persistence 2.1
*/
ConstructorResult[] classes() default {};
/** Specifies the result set mapping to scalar values. */
ColumnResult[] columns() default {};
所以这个columnresult写的是按照pojo中构造函数的顺序
- 注意类型
1
2
3
4
5
6
7
8
9
10/** (Required) The name of a column in the SELECT clause of a SQL query */
String name();
/**
* (Optional) The Java type to which the column type is to be mapped.
* If the <code>type</code> element is not specified, the default JDBC type
* mapping for the column will be used.
* @since Java Persistence 2.1
*/
Class type() default void.class;
type是使用的org.hibernate.type,比如我的price,使用的是BigIntegerType.class
- 使用方法
1 | true, name = "getGroupDetails") (nativeQuery = |
直接用name指定一下使用的是哪一个NamedNativeQuery
官网给的例子
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22* <pre>
*
* Example:
* Query q = em.createNativeQuery(
* "SELECT o.id AS order_id, " +
* "o.quantity AS order_quantity, " +
* "o.item AS order_item, " +
* "i.name AS item_name, " +
* "FROM Order o, Item i " +
* "WHERE (order_quantity > 25) AND (order_item = i.id)",
* "OrderResults");
*
* @SqlResultSetMapping(name="OrderResults",
* entities={
* @EntityResult(entityClass=com.acme.Order.class, fields={
* @FieldResult(name="id", column="order_id"),
* @FieldResult(name="quantity", column="order_quantity"),
* @FieldResult(name="item", column="order_item")})},
* columns={
* @ColumnResult(name="item_name")}
* )
* </pre>如果只想返回某一列中的数据
比如说,有一个user表,但是我只想返回一个username属性的值,返回类型List,可以这样操作 1
2
3
4
5
6
7
8
9
10
11
12
13
14(
name="usernameMapping",
columns={
(
name = "username",
type = StringType.class
)
}
)
"getUsernameList", query="SELECT user_table_entity.username from user_table_entity,worker,construction_team , join_worker_entity " + (name=
" where construction_team.id = worker.construction_team_id " +
" and join_worker_entity.worker_id = worker.worker_id " +
" and user_table_entity.user_id = join_worker_entity.user_id " +
" and construction_team_id = ?1", resultSetMapping="usernameMapping")
repository中的写法,跟前面的一样1
2"getUsernameList",nativeQuery=true) (name=
List<String> findAllByTeamId(Long teamId);