Hibernate中用left join（左外连接）查询映射中没有关联关系的两个表记录问题

一、问题背景

分账表split_summary结构如下：

create table SPLIT_SUMMARY(  uuid                VARCHAR2(32) not null,  star_tdate          VARCHAR2(26) default '',  end_date            VARCHAR2(26) default '',  store_id            VARCHAR2(32) default '',  order_total_price   NUMBER(13,2) default 0.00,  product_total_price NUMBER(13,2) default 0.00,  store_fz_price      NUMBER(13,2) default 0.00,  shop_fz_price       NUMBER(13,2) default 0.00,  status              CHAR(1) default '0',  create_date         VARCHAR2(26) default '',  note                VARCHAR2(512) default '')

商户表store结构如下：

create table STORE(  store_id                   VARCHAR2(32) default '' not null,  store_name                 VARCHAR2(254) default ''）

两表在Hibernate映射中没有关联关系，split_summary中的store_id可能在store表中，也可能不在，要查询所有的split_summary中的记录以及store中store_id和split_summary表中store_id相匹配的记录。很容易想到在oracle使用左连接进行查询，sql语句如下：

select ss.uuid,ss.store_id, s.store_name  from split_summary ss left join store s on ss.store_id=s.store_id where 1=1 order by ss.uuid desc

二、Hibernate中的左连接查询

Hibernate实战对连接说明如下：

也就是说Hibernate中是不支持on关键字的，如果使用就会报错。Hibernate中的连接条件使用映射关联做的，如下的栗子

Hibernate左连接的栗子

from Item i left join i.bids b with b.amount > 100 where i.description like '%Foo%'

而上述中的split_summary和store在映射中是没有定义关联关系的，怎么解决？

三、解决方案

想到了Hibernate的Native SQL查询

native sql的定义

protected Map<String, Object> getQuerySQL(HttpServletRequest request,ActionForm form) {OrderSplitForm sform = (OrderSplitForm) form;// 构建返回MapMap<String, Object> returnMap = new HashMap<String, Object>();// 构建参数MapMap<String, String> params = new HashMap<String, String>();// 编写SQLStringBuffer sql = new StringBuffer("select ss.uuid,ss.store_id,s.store_name,ss.SHOP_FZ_PRICE,ss.STORE_FZ_PRICE," +"ss.ORDER_TOTAL_PRICE,ss.PRODUCT_TOTAL_PRICE,ss.status,ss.STAR_TDATE,ss.END_DATE," +"ss.note from split_summary ss left join store s on ss.store_id = s.store_id where 1=1");// 将账单与店铺关联起来, 使用左连接，解决不是商户的问题//sql.append(" and ss.storeId = store.id");// 店铺编号查询条件if (!StringUtil.isEmpty(sform.getStoreId())) {sql.append(" and ss.store_Id = :storeId");params.put("storeId", sform.getStoreId());}// 店铺名查询条件if (!StringUtil.isEmpty(sform.getStoreName())) {sql.append(" and s.store_Name like :storeName");params.put("storeName", "%" + sform.getStoreName() + "%");}// 开始时间查询条件if (!StringUtil.isEmpty(sform.getStartDate())) {sql.append(" and ss.STAR_TDATE >= :startDate");params.put("startDate", sform.getStartDate());}// 结束时间查询条件if (!StringUtil.isEmpty(sform.getEndDate())) {sql.append(" and ss.END_DATE <= :endDate");params.put("endDate", sform.getEndDate());}// 分账单状态if (!StringUtil.isEmpty(sform.getStatus())) {sql.append(" and ss.status = :status");params.put("status", sform.getStatus());}// 按照创建时间倒序排序// sql.append(" order by ss.create_Date desc");sql.append(" order by ss.uuid desc");returnMap.put("sql", sql.toString());returnMap.put("param", params);return returnMap;}

Hibernate中查询的执行

/** *  Native SQL查询 * @param nativeSQL  sql * @param map         参数绑定map * @param page        分页查询参数 * @param typeMap     标量查询返回类型 * @return * @throws Exception */public List listByNativeSQL(String nativeSQL, Map<String, Object> map, Pager page, Map<String, Type> typeMap)throws Exception {List list = null;try {session = sessionFactory.openSession();SQLQuery query = session.createSQLQuery(nativeSQL);if (map != null) {for (String key : map.keySet()) {if (nativeSQL.indexOf(":" + key) != -1) {query.setParameter(key, map.get(key));System.out.println("param[" + key + "]==="+ map.get(key));}}}if (typeMap != null) {for (String key : typeMap.keySet()) {query.addScalar(key, typeMap.get(key));}}if (page != null) {query.setFirstResult(page.getFromRow());query.setMaxResults(page.getRowsPerPage());} else {query.setFirstResult(0);query.setMaxResults(20);}list = query.list();if (page != null) {SQLQuery countQuery = session.createSQLQuery(countSql(nativeSQL));if (map != null) {for (String key : map.keySet()) {if (nativeSQL.indexOf(":" + key) != -1) {countQuery.setParameter(key, map.get(key));System.out.println("param[" + key + "]==="+ map.get(key));}}}if (countQuery != null) {List countlist = countQuery.list();if (countlist != null && countlist.size() > 0) {page.setTotalRow(((Number) countlist.get(0)).intValue());}}}} catch (Exception e) {e.printStackTrace();PubLogs.dbLogError(new StringBuffer("获取查询列表失败!").append("PubHibernate.list(nativeSQL)").append("nativeSQL=" + nativeSQL), e);throw e;} finally {if (session != null && session.isOpen()) {session.close();}}if (list != null) {covertNullToSpace(list);}return list;}

使用createSQLQuery进行Native SQL进行查询。

其中包含了返回参数类型的转换addScalar

if (typeMap != null) {for (String key : typeMap.keySet()) {query.addScalar(key, typeMap.get(key));}}

typeMap定义如下

public class SplitSummary extends PubBean {// 结算账单开始日期private String startDate = "";// 结算账单结束日期private String endDate = "";// 店铺编号private String storeId = "";// 订单总金额private double orderTotalPrice = 0.00;// 商品总价private double productTotalPrice = 0.00;// 店铺分账金额private double storeFzPrice = 0.00;// 平台分账金额private double shopFzPrice = 0.00;// 状态private String status = "";// 创建时间private String createDate = "";// 备注private String note = "";/** * nativeSQL查询时返回字段的类型 * @return */public static Map<String, Type> getTypeMap() {Map<String, Type> map = new LinkedHashMap<String, Type>();map.put("uuid", Hibernate.STRING);map.put("store_id", Hibernate.STRING);map.put("store_name", Hibernate.STRING);map.put("SHOP_FZ_PRICE", Hibernate.DOUBLE);map.put("STORE_FZ_PRICE", Hibernate.DOUBLE);map.put("ORDER_TOTAL_PRICE", Hibernate.DOUBLE);map.put("PRODUCT_TOTAL_PRICE", Hibernate.DOUBLE);map.put("status", Hibernate.STRING);map.put("STAR_TDATE", Hibernate.STRING);map.put("END_DATE", Hibernate.STRING);map.put("note", Hibernate.STRING);return map;}public String getStartDate() {return startDate;}public void setStartDate(String startDate) {this.startDate = startDate;}public String getEndDate() {return endDate;}public void setEndDate(String endDate) {this.endDate = endDate;}public String getStoreId() {return storeId;}public void setStoreId(String storeId) {this.storeId = storeId;}public double getOrderTotalPrice() {return orderTotalPrice;}public void setOrderTotalPrice(double orderTotalPrice) {this.orderTotalPrice = orderTotalPrice;}public double getProductTotalPrice() {return productTotalPrice;}public void setProductTotalPrice(double productTotalPrice) {this.productTotalPrice = productTotalPrice;}public double getStoreFzPrice() {return storeFzPrice;}public void setStoreFzPrice(double storeFzPrice) {this.storeFzPrice = storeFzPrice;}public double getShopFzPrice() {return shopFzPrice;}public void setShopFzPrice(double shopFzPrice) {this.shopFzPrice = shopFzPrice;}public String getStatus() {return status;}public void setStatus(String status) {this.status = status;}public String getCreateDate() {return createDate;}public void setCreateDate(String createDate) {this.createDate = createDate;}public String getNote() {return note;}public void setNote(String note) {this.note = note;}}

四、遇到的坑

1、Hibernate中不支持关键字on，连接条件是通过映射关联进行的，例如：映射文件中配置<one-to-many>或者使用注解@OneToMany

2、oracle中表命名别名不能使用as关键字，直接命名即可，例如：split_summary ss

3、addScalar设置返回类型时，要把查询的全部设置，否则设置几个返回几个；还要保证设置参数返回类型的顺序，查询和类型要一致，本例中采用了LinkedHashMap进行设置。