我的应用中有以下代码
String sql = "SELECT colA, colB, colC " +
"FROM " + tblName + " WHERE UserId = " + userId +
" AND InsertTimestamp BETWEEN " + lastDate +
" AND " + DataProcessor.TODAY + " ORDER BY UserId, Occurred";
try{
if(null == conn)
openDatabaseConnection();
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery(); <------- this is the line which throws the SQL exception
retArray = this.getArrayListFromResultSet(rs);
}catch(SQLException sqle){
JSONObject parms = new JSONObject();
eh.processSQLException(methodName, sqle, sql, parms);
}
因此,当我在调试器中运行我的应用程序时,我会收到以下异常消息
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:您的SQL语法有错误;在第1行的"00:00:00.0 AND 2014-08-20 00:00:00.0 ORDER BY UserId,Occurred"附近,请查看与MySQL服务器版本对应的手册,以获取要使用的正确语法
我有理由相信有一个简单合理的解决方案,但我一直没能找到
我试着在MySQL手册中查找解决方案或不同的格式。
我尝试过通过SQL中的TIMESTAMP()函数和DATE()函数来运行时间戳,但这两个函数都没有帮助。
我从Java代码中提取了完整的SQL,并在MySQL Workbench中运行,没有出现任何问题。所以现在我向专家寻求帮助。
String sql = "SELECT colA, colB, colC " +
"FROM " + tblName + " WHERE UserId = ?" +
" AND InsertTimestamp BETWEEN ?" +
" AND ? ORDER BY UserId, Occurred";
try {
if(null == conn) {
openDatabaseConnection();
}
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1, userId);
stmt.setDate(2, lastDate);
stmt.setDate(3, DataProcessor.TODAY);
ResultSet rs = stmt.executeQuery();
retArray = this.getArrayListFromResultSet(rs);
} catch(SQLException sqle) {
JSONObject parms = new JSONObject();
eh.processSQLException(methodName, sqle, sql, parms);
}
不管怎样,我认为你是在按相反的顺序设定日期。你应该先放"today",然后放lastDate。虽然我不知道你的限制。。。