前言： 我们都知道hibernate的常用的三种查询方式为：HQL查询 ，QBC（Query By Criteria）查询，但是这两种查询往往不能满足我们对复杂的多表进行查询，所以我们在需要复杂sql查询时 往往使用的是hibernate的第三种查询方式 ：原生sql查询。今天把之前遇到的用sql查询的方式以及sql如何拼接查询的方式大概做个总结：
（注意：为了能使文章发布成功sql语句的关键字全部谢伟错误的 请自行补全！）
findOneForJdbc()方法：
|
1 |
Map<String, Object> findOneForJdbc = sendMessageServiceI.findOneForJdbc("selct identifying_code from send_message where id in (selct max(id) from send_message where send_number =?)",userPhone); |
hibernate原生sql:
|
1 2 3 4 5 |
String sql = "selet d.departname,r.* from PeiHeBi r,t_s_depart d where r.td_id=d.id and r.id="+id+" "; Query query = this.getSession().createSQLQuery( sql ); query.setResultTransformer( Transformers.ALIAS_TO_ENTITY_MAP ); |
原生sql拼接查询：
|
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 |
String sql = "selet handle_step, COUNT(*) as count from PeiHeBi where 1=1 "; switch (role) { case "1": sql +=" and td_id = "+td_id; break; case "2": sql +=" and td_id in ("+td_id +")"; break; case "3": sql +=" and td_id in ("+td_id +")"; break; } sql+=" group by handle_step"; Query query = this.getSession().createSQLQuery( sql ); query.setResultTransformer( Transformers.ALIAS_TO_ENTITY_MAP ); |
用Stringbuffer的方式进行拼接方式查询：
|
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 |
StringBuffer sql = new StringBuffer(); sql.append("SELET departname,td_id,year,month,count1,weight,count2,ISNULL(one,0) + " from asphalt_abnormal_handle where + "LEFT JOIN (SELET id,departname from t_s_depart" + ") e on a.td_id=e.id where 1=1"); if(proId >0){ sql.append(" and a.pro_id="+proId); } if(tdId>0){ sql.append(" and a.td_id="+tdId); } if(year!=null&&!"".equals(year)){ sql.append(" and a.year="+year); } Query query = this.getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); return query.list(); |
HQL方式查询：
|
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 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
String sql = "selet "; if ("月".equals(queryType)) { sql += " year(a.zl_date) as year,month(a.zl_date) as month,"; } else if ("年".equals(queryType)) { sql += " year(a.zl_date) as year,"; } sql += " a.td_id as tdId,count(*) as count,b.handle_step as handleStep from prestress a,prestress_abnormal_handle b where a.is_delete='0' and a.id=b.data_id"; if (proId > 0) { sql += " and a.pro_id=:proId"; } if (tdId > 0) { sql += " and a.td_id=:tdId"; } if (waId > 0) { sql += " and a.wa_id=:waId"; } if (begin != null && !"".equals( begin )) { sql += " and a.zl_date>=:begin"; } if (end != null && !"".equals( end )) { sql += " and a.zl_date<=:end"; } sql += " group by a.td_id,b.handle_step"; if ("月".equals(queryType)) { sql += ",year(a.zl_date),month(a.zl_date) order by year(a.zl_date) desc,month(a.zl_date) desc"; } else if ("年".equals(queryType)) { sql += ",year(a.zl_date) order by year(a.zl_date) desc"; } Query query = this.getSession().createSQLQuery( sql ); if (proId > 0) { query.setParameter( "proId", proId ); } if (tdId > 0) { query.setParameter( "tdId", tdId ); } if (waId > 0) { query.setParameter( "waId", waId ); } if (begin != null && !"".equals( begin )) { query.setParameter( "begin", begin ); } if (end != null && !"".equals( end )) { query.setParameter( "end", end ); } query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); return query.list(); |
