今天在做数据分发同步工具的时候遇到了点问题，MySQL5按照字段的时间段范围查询不能成功的问题。
现在问题还没有解决，下面是我专门为了一个低级的查询写了个JDBC测试，连接池什么的统统不要，将问题的范围尽量缩小。
原因是PreparedStatement设置java.sql.Date参数没有被认为传递的是合法的Date，而查询不到数据。如果你不用JDBC、不用PreparedStatement，你是发现不到这个问题的。
一、环境
MySQL5.1.30
Java 1.5
SQL脚本
CREATE TABLE genreparam (
genre_id int(11) NOT NULL,
updatenum bigint(20) DEFAULT '0',
ishd bigint(1) DEFAULT NULL,
kind bigint(20) DEFAULT NULL,
clicks bigint(20) DEFAULT '0',
create_time datetime NOT NULL,
update_time datetime NOT NULL,
PRIMARY KEY (genre_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
genre_id int(11) NOT NULL,
updatenum bigint(20) DEFAULT '0',
ishd bigint(1) DEFAULT NULL,
kind bigint(20) DEFAULT NULL,
clicks bigint(20) DEFAULT '0',
create_time datetime NOT NULL,
update_time datetime NOT NULL,
PRIMARY KEY (genre_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
二、测试
要求传递两个参数，用来控制update_time字段的范围，查询数据库记录。
测试代码：
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
public class TestJDBC {
private static final String genreparam_sql = "" +
"select genre_id, updatenum, ishd, kind, clicks, create_time, update_time\n" +
" from genreparam\n" +
" where update_time BETWEEN ? AND ? \n" +
" limit ?," + 300;
private static final String genreparam1_sql = "" +
"select genre_id, updatenum, ishd, kind, clicks, create_time, update_time\n" +
" from genreparam\n" +
" where DATE_FORMAT(update_time, '%Y-%c-%e %T') BETWEEN ? AND ? \n" +
" limit ?," + 300;
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
String user = "vcom";
String pswd = "vcom";
String dburl = "jdbc:mysql://192.168.104.163:3306/VOD3_01";
Connection conn = DriverManager.getConnection(dburl, user, pswd);
System.out.println(conn == null);
// List<Genreparam> genList = new ArrayList<Genreparam>();
Calendar c = Calendar.getInstance();
c.set(1900, 1, 1);
Date _start = new Date(c.getTimeInMillis());
Date _end = new Date(System.currentTimeMillis());
System.out.println(_start);
System.out.println(_end);
PreparedStatement pstmt = conn.prepareStatement(genreparam1_sql);
pstmt.setDate(1, _start);
pstmt.setDate(2, _end);
pstmt.setString(1, "1900-2-1 00:00:00");
pstmt.setString(2, "2010-2-1 00:00:00");
pstmt.setInt(3, 0);
ResultSet rs = pstmt.executeQuery();
int i = 0;
while (rs.next()) {
// Genreparam genreparam = new Genreparam();
// genreparam.setGenre_id(rs.getLong("genre_id"));
// genreparam.setUpdatenum(rs.getLong("updatenum"));
// genreparam.setIshd(rs.getInt("ishd"));
// genreparam.setKind(rs.getInt("kind"));
// genreparam.setClicks(rs.getLong("clicks"));
// genreparam.setCreate_time(rs.getDate("create_time"));
// genreparam.setUpdate_time(rs.getDate("update_time"));
// genList.add(genreparam);
i++;
}
rs.close();
conn.close();
System.out.println("查询记录总数为:" + i);
}
}
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
public class TestJDBC {
private static final String genreparam_sql = "" +
"select genre_id, updatenum, ishd, kind, clicks, create_time, update_time\n" +
" from genreparam\n" +
" where update_time BETWEEN ? AND ? \n" +
" limit ?," + 300;
private static final String genreparam1_sql = "" +
"select genre_id, updatenum, ishd, kind, clicks, create_time, update_time\n" +
" from genreparam\n" +
" where DATE_FORMAT(update_time, '%Y-%c-%e %T') BETWEEN ? AND ? \n" +
" limit ?," + 300;
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
String user = "vcom";
String pswd = "vcom";
String dburl = "jdbc:mysql://192.168.104.163:3306/VOD3_01";
Connection conn = DriverManager.getConnection(dburl, user, pswd);
System.out.println(conn == null);
// List<Genreparam> genList = new ArrayList<Genreparam>();
Calendar c = Calendar.getInstance();
c.set(1900, 1, 1);
Date _start = new Date(c.getTimeInMillis());
Date _end = new Date(System.currentTimeMillis());
System.out.println(_start);
System.out.println(_end);
PreparedStatement pstmt = conn.prepareStatement(genreparam1_sql);
pstmt.setDate(1, _start);
pstmt.setDate(2, _end);
pstmt.setString(1, "1900-2-1 00:00:00");
pstmt.setString(2, "2010-2-1 00:00:00");
pstmt.setInt(3, 0);
ResultSet rs = pstmt.executeQuery();
int i = 0;
while (rs.next()) {
// Genreparam genreparam = new Genreparam();
// genreparam.setGenre_id(rs.getLong("genre_id"));
// genreparam.setUpdatenum(rs.getLong("updatenum"));
// genreparam.setIshd(rs.getInt("ishd"));
// genreparam.setKind(rs.getInt("kind"));
// genreparam.setClicks(rs.getLong("clicks"));
// genreparam.setCreate_time(rs.getDate("create_time"));
// genreparam.setUpdate_time(rs.getDate("update_time"));
// genList.add(genreparam);
i++;
}
rs.close();
conn.close();
System.out.println("查询记录总数为:" + i);
}
}
传递java.sql.Date参数总查不到数据，于是修改为传递字符串，才勉强获取到了。。
false
1900-02-01
2009-07-31
查询记录总数为:4
Process finished with exit code 0
1900-02-01
2009-07-31
查询记录总数为:4
Process finished with exit code 0
写SQL查：
SELECT *
FROM genreparam
WHERE DATE_FORMAT(update_time, '%Y-%c-%e %T') BETWEEN '1900-2-1 00:00:00' AND '2010-2-5 00:00:00'
FROM genreparam
WHERE DATE_FORMAT(update_time, '%Y-%c-%e %T') BETWEEN '1900-2-1 00:00:00' AND '2010-2-5 00:00:00'
查询了4条记录。
三、思考与解决
究竟是什么原因呢，日期不能直接做大小比较吗？
最终发现，MySQL对日期的比较直接可以用字符串来比较，比如：
select genre_id, updatenum, ishd, kind, clicks, create_time, update_time
from genreparam
where update_time < '2010-2-1 00:00:00 '
from genreparam
where update_time < '2010-2-1 00:00:00 '
虽然，'2010-2-1 00:00:00 '不是日期类型，但仍然可以做比较。
下面看看如果将字符串转为日期呢，能否比较呢？
select genre_id, updatenum, ishd, kind, clicks, create_time, update_time
from genreparam
where update_time < TIMESTAMP('2010-2-1 00:00:00 ')
from genreparam
where update_time < TIMESTAMP('2010-2-1 00:00:00 ')
执行结果依然正确。
这就非常让人迷惑了，为什么不能直接传递一个java.sql.Date参数就挂了呢？
在此，实在很无奈，就写个格式化方法吧，将Date参数改为传String参数就可以了。
private static final String data_format = "yyyy-MM-dd kk:mm:ss";
public static String data2ISOString(java.util.Date date) {
SimpleDateFormat sf = new SimpleDateFormat(data_format);
return sf.format(date);
}
public static String data2ISOString(java.util.Date date) {
SimpleDateFormat sf = new SimpleDateFormat(data_format);
return sf.format(date);
}
这样，问题就彻底得到解决了。
本文转自 leizhimin 51CTO博客，原文链接：http://blog.51cto.com/lavasoft/185888，如需转载请自行联系原作者