Hibernate SQL多表查询

SQL 语句：
  public static Query countShopSmsRecharge(Session session, Long startTime, Long endTime, String money, String other, Long shopId) {

    StringBuffer hql = new StringBuffer("select s.id as id, s.shop_id as shopId ,s.recharge_time as rechargeTime,s.recharge_number as rechargeNumber,s.sms_balance as smsBalance,s.recharge_amount as rechargeAmount,s.state as state," +
        "s.pay_time as payTime,u.user_name as userName  from sms_recharge as s,bcuser.user as u where s.user_id=u.id ");
    if (other != null && !"".equals(other)) {
      hql.append("and u.user_name  like:userName ");
    }
    if (money != null && !"".equals(money)) {
      hql.append("and s.recharge_amount=:money ");
    }
    if (shopId != null) {
      hql.append("and s.shop_id=:shopId ");
    }
    if (startTime != null) {
      hql.append(" and s.recharge_time >=:startTime  ");
    }
    if (endTime != null) {
      hql.append(" and s.recharge_time<=:endTime ");
    }
    SQLQuery sqlQuery = (SQLQuery) session.createSQLQuery(hql.toString()).setLong("shopId", shopId);
    if (other != null && !"".equals(other)) {
      sqlQuery.setString("userName", "%" + other + "%");
    }
    if (money != null && !"".equals(money)) {
      sqlQuery.setDouble("money", Double.parseDouble(money));
    }
    if (startTime != null) {
      sqlQuery.setLong("startTime", startTime);
    }
    if (endTime != null) {
      sqlQuery.setLong("endTime", endTime);
    }
    return sqlQuery;
  }

 

转换为实体DTO
 
  public List countShopSmsRecharge(String startTime, String endTime, String money, String other, Long shopId) {
    Session session = this.getSession();
    try {
      SQLQuery query = null;
      try {
        query = (SQLQuery) SQL.countShopSmsRecharge(session, DateUtil.convertDateStringToDateLong("yyyy-MM-dd", startTime), DateUtil.convertDateStringToDateLong("yyyy-MM-dd", endTime), money, other, shopId)
            .setResultTransformer(Transformers.aliasToBean(SmsRechargeDTO.class));
      } catch (ParseException e) {
        LOG.error(e.getMessage(), e);  //To change body of catch statement use File | Settings | File Templates.
      }
      query.addScalar("shopId", StandardBasicTypes.LONG);
      query.addScalar("userName", StandardBasicTypes.STRING);
      query.addScalar("smsBalance", StandardBasicTypes.DOUBLE);
      query.addScalar("rechargeAmount", StandardBasicTypes.DOUBLE);
      query.addScalar("rechargeTime", StandardBasicTypes.LONG);
      query.addScalar("state", StandardBasicTypes.LONG);

      return query.list();
    } finally {
      release(session);
    }
  }