今天在用hibernate进行like查询时,遇到了sql语句类似这样的:
'xxx' 'xxx%' '%xxx' '%xxx%'
每种情况都要考虑的时候,直接写查询不出结果(我带单引号查不出结果)
其中SQL SERVER2014中:
SQL语句为:
select p.p_order, p.id
from order_product p
where (p.flange_pie_staff like '7' or p.flange_pie_staff like '7,%' or p.flange_pie_staff like '%,7' or p.flange_pie_staff like '%,7,%')
and p.fit_finish is null and (p.status_id=8 or p.status_id =9);
javaweb中HQL 为:
注意红色部分
public static List<Object[]> selectPOder(int staffId) {
//--根据员工id查找到产品序号
Session session = null;
try {
session = HibernateSessionFactory.getSession();
//此处查id就足够了,但是只查id的时候会报错 java.lang.String cannot be cast to [Ljava.lang.Object
// 情况描述:在执行sql查询取其返回的数据时出现的;我用的是数组来取它的Result,其他地方都好好的;但在某些地方就不对!
// 出现原因:sql语句执行的查询结果只有一列时就会出现该问题!当有多个列时用数组取没有问题,如果只有一列会默认为string或者其他类型!
// 解决办法: 查询的时候多取一列..OrderProduct
String find = "select p.POrder, p.id from OrderProduct p ";
String sql = " where (p.flangePieStaff like ? or p.flangePieStaff like ? or p.flangePieStaff like ? or p.flangePieStaff like ?) "
+ "and p.fitFinish is null and (p.statusId=8 or p.statusId =9) ";
Query query = session.createQuery(find+sql);
// query.setString(0, "%" + staffName + "%");
//假如给17,27,37……员工派工,那么7号员工一定不能接到任务,需要做处理
String que0 = "'" +staffId+ "'";// '7'
String que1 = "'" +staffId+ ",%'";// '7,%'
String que2 = "'%," +staffId+ "'";// '%,7'
String que3 = "'%," +staffId+ ",%'";// '%,7,%'
query.setString(0, que0);
query.setString(1, que1);
query.setString(2, que2);
query.setString(3, que3);
@SuppressWarnings("unchecked")
List<Object[]> list = query.list();
System.out.println("que0="+que0+" que1="+que1+" que2="+que2+" que3="+que3);
System.out.println("selectPOder ="+list.size());
return list;
} catch (HibernateException e) {
e.printStackTrace();
return null;
} finally {
if (session != null) {
session.close();
}
}
}
纠结了一早上,百度了查了很多,最终解决了:原因如下:
在SQL中用单引号是正确的,但是在hibernate中进行模糊查询时,单引号是不能加上的
还记得上面红色的代码吗?把单引号去掉就好了:
String que0 = "" +staffId+ "";// '7'
String que1 = "" +staffId+ ",%";// '7,%'
String que2 = "%," +staffId+ "";// '%,7'
String que3 = "%," +staffId+ ",%";// '%,7,%'
结果:
Hibernate: select orderprodu0_.p_order as col_0_0_, orderprodu0_.id as col_1_0_ from Legend.dbo.order_product orderprodu0_
where (orderprodu0_.flange_pie_staff like ? or orderprodu0_.flange_pie_staff like ? or orderprodu0_.flange_pie_staff like ?
or orderprodu0_.flange_pie_staff like ?) and (orderprodu0_.fit_finish is null) and (orderprodu0_.status_id=8 or orderprodu0_.status_id=9)
que0=7 que1=7,% que2=%,7 que3=%,7,%
selectPOder =1
在此记录一个坑。群里大佬们都说我的方式选的不好,没见过连用4个like的查询,让我用正则表达式,在此抱歉,小弟刚学javaweb,
并且对数据库是一窍不通啊。我会慢慢学习进步的,如有帮助请点赞,如果更好的办法请评论。