过滤date类型的数据时SQL(HQL)语句创建的几种方式

 

 这几天一直在做quartz调度的开发，在做数据查询时经常用到对date类型字段的过滤，SQL(HQL)的拼接一直是头疼的问题，这里做个总结，只有记录才有进步。

   SQL(HQL)的拼接在日常的使用中无非就那么两种方式，一种是使用字符串拼接，还有一种是使用查询参数。在这里强烈建议使用参数的方式来拼接SQL语句，防止SQL注入的安全问题，虽然最安全的方式是“不把任何没有经过验证的数据传入服务端”但是在实际的开发中真正能做到的又有多少呢。好了，全是废话，开始正题。

   对于date类型字段的过滤要保证一点：拼接完成的SQL中传入或拼接上的值在SQL执行时必须是sql.Date类型的。

   方式一：使用字符串拼接。

  字符串拼接最后生成的只能是字符串，所以可以使用TO_DATE函数，将函数名接入SQL语句中，让SQL在执行时把字符串转换成java.sql.Date。

 

java.sql.Date startDate = new java.sql.Date(1000000000);  
java.sql.Date endDate = new java.sql.Date(1100000000);  
StringBuffer sbuf = new StringBuffer();  
sbuf.append("from XXEntity t where t.time >= TO_DATE('")  
    .append(startDate)  
    .append("' , 'yyyy-mm-dd') and t.time <= TO_DATE('")  
    .append(endDate)  
    .append("' , 'yyyy-mm-dd')");  
String sql = sbuf.toString();  
System.out.println(sql);

 

 最终打印的语句是：

 

from XXEntity t where t.time >= TO_DATE('1970-01-12' , 'yyyy-mm-dd') and t.time <= TO_DATE('1970-01-14' , 'yyyy-mm-dd')

 

 

从这条打印结果也不难发现：java.sql.Date 的toString()方法得到的时间字符串只有yyyy-mm-dd这种形式。所以如果你要操作代有时分秒的数据请用 timestamp类型存储，这时你要用SimpleDateFormat这个类做时间   格式的转换。这里只讨论date类型的数据。

    看下面一段代码：

 

 

 

java.util.Date startDate1 = new java.util.Date();  
java.util.Date endDate1 = new java.util.Date();  
StringBuffer sbuf1 = new StringBuffer();  
sbuf1.append("from XXEntity t where t.time >= TO_DATE('")  
         .append(startDate1)  
         .append("' , 'yyyy-mm-dd') and t.time <= TO_DATE('")  
         .append(endDate1)  
         .append("' , 'yyyy-mm-dd')");  
String sql1 = sbuf1.toString();  
System.out.println(sql1);

 

  打印的结果如下：

 

from XXEntity t where t.time >= TO_DATE('Fri Aug 03 20:16:56 CST 2012' , 'yyyy-mm-dd') and t.time <= TO_DATE('Fri Aug 03 20:16:56 CST 2012' , 'yyyy-mm-dd')  

 这sql是没法执行的。所以在做sql拼接的时候要么使用java.sql.Date,要么使用SimpleDateFormat类做格式化处理，不然拼接的语句是不能执行的。

 

 方式二：使用参数。

   使用参数的方式传入变量的值是博主力推的方式，安全，灵活。

   1.直接传入java.sql.Date类型的值：

 

java.sql.Date startDate = new java.sql.Date(1000000000);  
java.sql.Date endDate = new java.sql.Date(1100000000);  
String sqlString = "from XXEntity t where t.time >= :startTime and t.time <= :endTime";  
Query query = session.createQuery(sqlString);  
query.setParameter("startTime", startDate).setParameter("endTime", endDate);  
query.list();

  注意这里面startDate和endDate是做为java.sql.Date类型处理的，因为t.time是一个date类型，所以只能和date类  型的数据进行>=等运算。

 

    2.使用字符串做为参数：

 

java.sql.Date startDate = new java.sql.Date(1000000000);  
java.sql.Date endDate = new java.sql.Date(1100000000);  
String sqlString = "from XXEntity t where t.time >= to_date(:startTime,'yyyy-mm-dd') and t.time <= (:endTime , 'yyyy-mm-dd') ";  
Query query = session.createQuery(sqlString.toString());  
query.setParameter("startTime", startDate).setParameter("endTime", endDate.toString());  
query.list();

 这里startTime和endTime都必须是字符串类型的。所以做设置参数的时候要把startDate和endDate做类型转换。

 

 这里还有一个值得注意的地方： to_date(:startTime,'yyyy-mm-dd')一定不能写成 to_date(':startTime' , 'yyyy-  mm-dd')。不然会出现找不到name为startTime的参数的错误。这个问题的原因是在使用参数  的时候，jdbc会根据  你传入值  的类型拼接sql语句，startTime最后的值一定会是 'yyyy-mm-dd'的形式，不会是yyyy-dd-mm的。

 另外：发现有些博客中提到如果使用between的方式查询，在startTime和endTime相等的情况下是无法查出数据    的，小弟没有测试，有兴趣的可以亲测一下。

 第一次写技术博客，以后会不断记下自己学习过程的点点滴滴，这里有些需要完善的地方希望前辈们能够给我多  些提点。