簡介
JPA / Hibernate雖然好用，但難免都會遇到一些複雜的query，無法用JPQL / HQL 完成，這時候就希望能有方法可以使用native sql，將resultset轉換成List<POJO>，而非預設的List<Object[]>的型態。在JPA 2.0之前的作法
在JPA 2.0之前的作法是將JPA unwrap成Hibernate (OpenJAP / EclipseLink我沒有用過)，再使用Hibernate設定resultTransformer轉換，範例如下：
public
List<AssetDevice> findByQueryBrandType() {
logger.info("queryBrandType() - start");
List<AssetDevice> brandTypeList = null;
StringBuffer queryString = new StringBuffer("");
queryString.append(" SELECT ASSET_DEVICE.BRAND as
brand");
queryString.append(" FROM ASSET_DEVICE ");
queryString.append(" WHERE ");
queryString.append("
ASSET_DEVICE.BRAND <> ''
AND ASSET_DEVICE.BRAND IS NOT
NULL ");
queryString.append(" AND ASSET_DEVICE.DEVICE_TYPE <>
'accessory' ");
queryString.append(" GROUP
BY ASSET_DEVICE.BRAND ");
queryString.append(" ORDER
BY ASSET_DEVICE.BRAND ");
// 執行查詢
Query query = entityManager.createNativeQuery(queryString.toString());
// 設定回傳資料型態
query.unwrap(SQLQuery.class).addScalar("brand", StandardBasicTypes.STRING)
.setResultTransformer(Transformers.aliasToBean(AssetDevice.class));
brandTypeList = query.getResultList();
logger.info("findByQueryBrandType() - end");
return brandTypeList;
}
在JPA 2.1之後的作法
在JPA 2.1之後的作法是使用@SqlResultSetMapping這一個annotation，在JPA 2.0之前其實已經有這一個annotation，但只可以使用在Entity的Class上，無法使用在POJO上。在JPA 2.1之後已經可以用在POJO上了，但寫法與標準的mapping方法不同，範例如下：
@SqlResultSetMapping(
name = "HeatMapOriginVo",
classes = {@ConstructorResult(
targetClass =
HeatMapOriginVo.class,
columns = {
@ColumnResult(name = "tileId", type = Long.class),
@ColumnResult(name = "x", type =
Integer.class),
@ColumnResult(name = "y", type =
Integer.class),
@ColumnResult(name = "w", type =
Double.class),
@ColumnResult(name = "s", type =
Double.class),
@ColumnResult(name = "e", type =
Double.class),
@ColumnResult(name = "n", type =
Double.class),
@ColumnResult(name = "countyId"),
@ColumnResult(name = "countyCode"),
@ColumnResult(name = "countyName"),
@ColumnResult(name = "townId"),
@ColumnResult(name = "townCode"),
@ColumnResult(name = "townName"),
@ColumnResult(name = "townZip"),
@ColumnResult(name = "villId", type = Integer.class),
@ColumnResult(name = "villCode"),
@ColumnResult(name = "villName"),
@ColumnResult(name = "travelerCount", type = Integer.class)}
)
})
這裡有幾個地方是第一次寫這一種需要注意的：
- @SqlResultSetMapping內的寫法，用的是"classes"，非一般查見的 "entities"
- @SqlResultSetMapping一定要寫在有宣告@Entity的類別上，如上範例我是寫在HeatMapHour這一個類別上，不是POJO的HeatMapOriginVo類別上(我個人是覺得很不合理啦！)。
- @ColumnResult裡面的name，是SQL Command內select子句的column name，不是POJO的property name(雖然兩者90%以上都一樣)。
- 建議非String的columnResult都需要宣告type，以免從資料庫型態轉換成java型態時錯誤。
- 要被mapping的POJO，如上上範例的HeatMapOriginVo，必須建立一個constructor，順序與數量都要與@SqlResultSetMapping內的相同(如上範例，我要寫一個HeatMapOriginVo的constructor，要有由tileId到travelerCount共18個參數的constructor!)
完成mapping設定與增加constructor，實際呼叫的範例如下：
public
List<HeatMapOriginVo> findByTargetTown(String endTownCode, Date startTime, Date endTime) {
List<HeatMapOriginVo> list = new ArrayList<HeatMapOriginVo>();
Map<String,
Object> parameters = new HashMap<String, Object>();
StringBuilder queryString = new StringBuilder();
queryString.append("select ")
.append(" tile.tile_id as tileId, tile.x, tile.y,
tile.w, tile.s, tile.e, tile.n, ")
.append(" tile.county_id as countyId,
tile.county_code as countyCode , tile.county_name as countyName, ")
.append(" tile.town_id as townId, tile.town_code as
townCode, tile.town_name as townName, tile.town_zip as townZip, ")
.append(" tile.vill_id as villId, tile.vill_code as
villCode, tile.vill_name as villName, ")
.append(" headMap.travelet_count as travelerCount
")
.append("from (select
start_tile_id as tile_id, sum(travelet_count) as travelet_count from
heat_map_v2_10minutes ")
.append(" where start_dt_in_hour >= :startTime
and end_dt_in_hour <= :endTime and end_town_code = :endTownCode ")
.append(" group by start_tile_id) headMap ")
.append("inner join tile_250m
tile on tile.tile_id = headMap.tile_id ");
parameters.put("endTownCode", endTownCode);
parameters.put("startTime", startTime);
parameters.put("endTime", endTime);
// 執行查詢
list = this.findNative(queryString.toString(), parameters, null, "HeatMapOriginVo");
return list;
}
@SuppressWarnings("unchecked")
public <S>
List<S> findNative(String queryString, Map<String, Object> parameters, Integer maxCount,
String sqlResultSetMappingName) {
Query query = null;
if (StringUtils.isBlank(sqlResultSetMappingName)) {
query = entityManager.createNativeQuery(queryString);
} else {
query = entityManager.createNativeQuery(queryString, sqlResultSetMappingName);
}
if (null != parameters && 0 < parameters.size()) {
bindQueryParameters(query, parameters);
}
if ((maxCount != null) && (maxCount >= 0)){
query.setMaxResults(maxCount);
}
return query.getResultList();
}
protected Query
bindQueryParameters(Query query, Map<String, Object> parameters) {
if (null != parameters && 0 < parameters.size()) {
Set<String> names = parameters.keySet();
for (String name : names) {
query.setParameter(name, parameters.get(name));
}
}
return query;
}
