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

时间：2024-01-15 19:01:32

一、问题背景

分账表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中用left join（左外连接）查询映射中没有关联关系的两个表记录问题


也就是说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;

		// 构建返回Map
		Map<String, Object> returnMap = new HashMap<String, Object>();
		// 构建参数Map
		Map<String, String> params = new HashMap<String, String>();
		// 编写SQL
		StringBuffer 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进行设置。


相关文章