使用JDBC在SQL中的日期之间进行搜索？ [英] Searching between dates in SQL with JDBC?

问题描述

我目前正在编写一个Java Swing应用程序，它从MYOB数据库文件中读取数据并在表格中显示某些信息。我已经能够成功生成所需的SQL语句，但是我无法添加日期之间的搜索功能（我们的数据库非常大，所以我们试图限制结果）。我的一个查询示例如下（用Java编写）：

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):

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"
                        + ";");

我有两个日期（它们实际上是Java中的字符串，但格式为dd / MM / yyyy） 。

I have two dates (they are actually Strings in Java but are formatted as dd/MM/yyyy).

我在 WHERE AND 子句c>使用 BETWEEN 语句但是我从JDBC获得以下错误 [MYOB ODBC]获取右操作数的字面值时出错。

I have tried using another AND clause in my WHERE with a BETWEEN statement but I get the following error from JDBC [MYOB ODBC]Error getting the literal value of right operand.

实际陈述如下：

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.

编辑：当前值 sdate 和 edate 分别是25/10/2013，因为它们默认为今天的日期。我已经设置了我正在测试的虚拟文件，以确保它将提供此日期范围的数据。另外，对于澄清，我希望搜索日期为包含。

Current value of sdate and edate respectively 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.

推荐答案

在您的日期使用报价：

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()))

Between运算符是包含的，但如果您的数据库字段实际上是时间戳，那么没有时间的日期是假设时间是00：00：00.000。因此，在这种情况下，为了使您的日期具有包容性，您可以在结束日期添加一天。从技术上讲，它还将包括第二天（00：00：00.000小时）的第一个即时，但根据您的应用程序，它可能就足够了。

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 instant of the next day (00:00:00.000 hour), but depending on your application it may be enough.

否则，您可以在开始日期上使用> = ，并在结束日期加一上使用< 日：

Otherwise you could use >= on "start date" and < on "end date plus one day":

"sales.Date >= '" + sdate + "' AND sales.Date < '" + edatePlusOne + "' "

这篇关于使用JDBC在SQL中的日期之间进行搜索？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！