jpa return non entity pojo

使用jpa的时候,有时候想要返回一个pojo对象,可能是一个实体的部分属性,也有可能是两个或多个实体联合查询出来的结果,需要把他们中的部分属性给返回。曾一度,我是使用mybatis再来查询,然后再返回。后来,觉得再写一个mapper麻烦,毕竟项目是以jpa为主,所以,Google,然后再Stack Overflow上看到了一个回答。就是使用SqlResultSetMapping

具体使用方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
@SqlResultSetMapping(
name="groupDetailsMapping",
classes={
@ConstructorResult(
targetClass= GroupPersonWorkerInfo.class,
columns={
@ColumnResult(name="workerName"),
@ColumnResult(name="postType"),
@ColumnResult(name="price",type = BigIntegerType.class),
@ColumnResult(name="groupName"),
@ColumnResult(name="permanentAddress"),
}
)
}
)
@NamedNativeQuery(name="getGroupDetails", query="select worker.worker_name as workerName,worker.post_type as postType," +
" worker.price ,worker.permanent_address as permanentAddress ,group_person.group_name as groupName from group_person,worker where group_person.worker_id = worker.worker_id " +
" and group_person.construction_team_id= :param1 limit :param2 , :param3", resultSetMapping="groupDetailsMapping")

pojo

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
@NoArgsConstructor
@Data
public class GroupPersonWorkerInfo {

private String workerName;
/**
* 工种
*/
private String postType;
/**
* 工价
*/
@Type(type = "org.jadira.usertype.moneyandcurrency.joda.PersistentMoneyMinorAmount",
parameters = {@org.hibernate.annotations.Parameter(name = "currencyCode", value = "CNY")})
@JsonSerialize(using = MoneySerializer.class)
@JsonDeserialize(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;
}

}

注意几点

  1. 注解使用的位置
    上述注解只能使用再标注了实体类上面,标注在class实体类上面
  2. 注意顺序
    因为我使用的是构造结果
    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. 注意类型
    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. 使用方法
1
2
3
@Query(nativeQuery = true, name = "getGroupDetails")
List<GroupPersonWorkerInfo> findAllByOfGroupPersonTeamId(@Param("param1") Long teamId,
@Param("param2") Long firstLimit,@Param("param3") int limit );

直接用name指定一下使用的是哪一个NamedNativeQuery

  1. 官网给的例子

    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 &gt; 25) AND (order_item = i.id)",
    * "OrderResults");
    *
    * &#064;SqlResultSetMapping(name="OrderResults",
    * entities={
    * &#064;EntityResult(entityClass=com.acme.Order.class, fields={
    * &#064;FieldResult(name="id", column="order_id"),
    * &#064;FieldResult(name="quantity", column="order_quantity"),
    * &#064;FieldResult(name="item", column="order_item")})},
    * columns={
    * &#064;ColumnResult(name="item_name")}
    * )
    * </pre>
  2. 如果只想返回某一列中的数据
    比如说,有一个user表,但是我只想返回一个username属性的值,返回类型List,可以这样操作

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    @SqlResultSetMapping(
    name="usernameMapping",
    columns={
    @ColumnResult(
    name = "username",
    type = StringType.class
    )
    }
    )
    @NamedNativeQuery(name="getUsernameList", query="SELECT user_table_entity.username from user_table_entity,worker,construction_team , join_worker_entity " +
    " 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
@Query(name="getUsernameList",nativeQuery=true)
List<String> findAllByTeamId(Long teamId);

0%