Java 使用 JDBC 在 SQL 中的日期之间搜索？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19583806/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Searching between dates in SQL with JDBC?
提问by Scott
I'm currently writing a Java Swing application that reads data in from a MYOB database file and displays certain information in a table. I've been able to successfully generate the required SQL statements but I'm having trouble adding the ability to search between dates (our database is pretty large so we are trying to limit results). An example of one of my queries is below (written in Java):
我目前正在编写一个 Java Swing 应用程序，它从 MYOB 数据库文件中读取数据并在表中显示某些信息。我已经能够成功生成所需的 SQL 语句，但是我无法添加在日期之间进行搜索的功能（我们的数据库非常大，因此我们试图限制结果）。我的一个查询示例如下（用 Java 编写）：
rs = stmt.executeQuery("SELECT sales.InvoiceNumber, sales.ShipToAddress, sales.Date "
+ "FROM sales, customers "
+ "WHERE sales.CardRecordID = customers.CardRecordID "
+ "AND customers.Name = 'Cash Sales' "
+ "ORDER BY sales.ShipToAddress ASC, sales.Date DESC"
+ ";");
I have two dates (they are actually Strings in Java but are formatted as dd/MM/yyyy).
我有两个日期（它们实际上是 Java 中的字符串，但格式为 dd/MM/yyyy）。
I have tried using another ANDclause in my WHEREwith a BETWEENstatement but I get the following error from JDBC [MYOB ODBC]Error getting the literal value of right operand.
我曾尝试AND在我WHERE的BETWEEN语句中使用另一个子句，但我从 JDBC 收到以下错误[MYOB ODBC]Error getting the literal value of right operand.
Actual statement is below:
实际声明如下：
rs = stmt.executeQuery("SELECT sales.InvoiceNumber, sales.ShipToAddress, sales.Date "
+ "FROM sales, customers "
+ "WHERE sales.CardRecordID = customers.CardRecordID "
+ "AND customers.Name = 'Cash Sales' "
+ "AND sales.Date BETWEEN " + sdate + " AND " + edate + " "
+ "ORDER BY sales.ShipToAddress ASC, sales.Date DESC"
+ ";");
Is anyone able to help me work out correct syntax for this? It's the last piece of functionality missing.
有没有人能帮我计算出正确的语法？这是最后一个功能缺失。
EDIT: Current value of sdateand edaterespectively are both 25/10/2013 as they default to today's date. I have set up the dummy file I am testing to ensure it will provide data with this date range. Also for clarficiation I would like the search dates to be INCLUSIVE.
编辑：当前值sdate和edate分别都25/10/2013，因为他们默认为今天的日期。我已经设置了我正在测试的虚拟文件，以确保它将提供具有此日期范围的数据。同样为了澄清，我希望搜索日期是包容性的。
采纳答案by Francis
Use quotes around your dates:
在您的日期周围使用引号：
rs = stmt.executeQuery("SELECT sales.InvoiceNumber, sales.ShipToAddress, sales.Date "
+ "FROM sales, customers "
+ "WHERE sales.CardRecordID = customers.CardRecordID "
+ "AND customers.Name = 'Cash Sales' "
+ "AND sales.Date BETWEEN '" + sdate + "' AND '" + edate + "' "
+ "ORDER BY sales.ShipToAddress ASC, sales.Date DESC"
+ ";");
Or it might be safer to use a prepared statement (if the dates come from untrusted inputs for example):
或者使用准备好的语句可能更安全（例如，如果日期来自不受信任的输入）：
SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
java.util.Date startDate = formatter.parse(sdate);
java.util.Date endDate = formatter.parse(edate);
PreparedStatement pstmt = connection.prepareStatement("SELECT sales.InvoiceNumber, sales.ShipToAddress, sales.Date "
+ "FROM sales, customers "
+ "WHERE sales.CardRecordID = customers.CardRecordID "
+ "AND customers.Name = 'Cash Sales' "
+ "AND sales.Date BETWEEN ? AND ? "
+ "ORDER BY sales.ShipToAddress ASC, sales.Date DESC");
pstmt.setDate(1, new java.sql.Date(startDate.getTime()))
pstmt.setDate(2, new java.sql.Date(endDate.getTime()))
The between operator is inclusive, but if your database field is actually a timestamp, then a date with no time is assumed to be at time 00:00:00.000. So, in such a case, for your dates to be inclusive, you can add one day to your end date. Technically it will also include the first instantof the next day (00:00:00.000 hour), but depending on your application it may be enough.
between 运算符是包含性的，但如果您的数据库字段实际上是时间戳，则假定没有时间的日期是 00:00:00.000。因此，在这种情况下，为了使您的日期包含在内，您可以将一天添加到您的结束日期。从技术上讲，它还包括第二天的第一个瞬间（00:00:00.000 小时），但根据您的应用程序，这可能就足够了。
Otherwise you could use >=on "start date" and <on "end date plus one day":
否则，您可以>=在“开始日期”和<“结束日期加一天”上使用：
"sales.Date >= '" + sdate + "' AND sales.Date < '" + edatePlusOne + "' "
回答by Gismo Ranas
回答by Asol Solomon
Class.forName("com.mysql.jdbc.Driver");
connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/hospitall","root","");
PreparedStatement ps=connection.prepareStatement("select * from patient where patientid='"+txtpatientid.getText()+"'");
Statement stm=connection.createStatement();
ResultSet rs=ps.executeQuery();
if(rs.next()){
String patientid=txtpatientid.getText();
String str="select * from patient where patientid='"+patientid+"'";
ResultSet result=stm.executeQuery(str);
