java - Postgresql 格式化日期

Question

我有以下内容

DateFormat dformat = new SimpleDateFormat("yyyy-M-d");
            dformat.setLenient(false);
            Date cin = dformat.parse(cinDate);

和sql函数

create or replace function search(_checkIn date, _checkOut date) returns setof Bookings as $$
declare
    r Bookings;
begin
    for r in
    select * from Bookings
    loop
        if ((_checkIn between r.checkIn and r.checkOut) or (_checkOut between r.checkIn and r.checkOut)) then
            return next r;
        end if;
    end loop;
    return;
end;
$$ language plpgsql;

postgresql 的日期格式是标准的(默认)

create table Bookings (
    id          serial,
    status      bookingStatus not null,
    pricePaid   money not null,
    firstName   text,
    lastName    text,
    address     text,
    creditCard  text,
    checkOut    date not null,
    checkIn     date not null,
    room        integer not null,
    extraBed    boolean not null default false,

    foreign key (room) references Rooms(id),
    primary key (id)
);

我正在尝试将日期解析到函数中以便它可以为我返回一个表，我似乎遇到了日期格式问题(这就是我认为我收到此错误的原因)

org.postgresql.util.PSQLException:错误:“Feb”处或附近的语法错误

所以我想知道如何解决这个问题，我不知道如何正确格式化日期

编辑:

我这样调用查询

           try {
                String searchQuery = "SELECT * FROM Rooms r where r.id not in (select * from search(" + cin +", " + cout +"))";
                PreparedStatement ps = conn.prepareStatement(searchQuery);
                rs = ps.executeQuery();
            } catch (SQLException e) {
                e.printStackTrace();
            }

所以我认为错误是因为我格式化日期的方式不对，postgres 不会读取它

Answer 1

这听起来像是您通过将参数直接连接到字符串中来传递参数。这是一个非常糟糕的主意，因为它会导致 SQL 注入(inject)。总是use PreparedStatements使用 ? 占位符来传递参数，切勿通过将它们直接连接到查询字符串中来直接传递它们(更重要的是，您需要 ' 分隔符)。

你可以有这样的东西:

 PreparedStatement stmt
     = con.prepareStatement("SELECT id FROM Bookings WHERE checkIn=?")
 stmt.setDate(1, new java.sql.Date(cin.getTime()));
      // ? parameters are indexed from 1
 ResultSet results = stmt.executeQuery();

或者，PostgreSQL 内部日期转换通常相当好且灵活。您可以使用 PostgreSQL 将字符串参数转换为日期:

 PreparedStatement stmt
     = con.prepareStatement("SELECT id FROM Bookings WHERE checkIn=CAST(? AS DATE)");
 stmt.setString(1, cinDate);
 ResultSet results = stmt.executeQuery();

这很灵活，但根据日期格式可能不会产生您需要的准确结果(有关日期转换格式的详细信息，您可以查看 PostgreSQL 手册)。不过，您使用的输入格式应该可以正常工作(例如，直接在 PostgreSQL 中尝试 SELECT CAST('2012-05-01' AS DATE)，这将返回正确的 PostgreSQL 日期。 )

请注意，在使用 new java.sql.Date(cin.getTime()) 时，您可能会遇到时区问题。您也可以使用 java.sql.Date.valueOf(...)。

为了澄清，在您的编辑之后:

这是行不通的，因为日期是 SQL 语法本身的一部分，而不是字符串或日期:"SELECT * FROM Rooms r where r.id not in (select * from search("+ cin + ", "+ cout +"))"

你至少需要使用 ' 引号: "SELECT * FROM Rooms r where r.id not in (select * from search("' + cin + "', '"+ cout +"'))" 。在这里，在某种程度上，您可以期望参数格式正确，但不要这样做。此外，仍然需要使用 CAST('...' AS DATE) 或 '...'::DATE 来转换字符串。

最简单的方法肯定是:

String searchQuery = "SELECT * FROM Rooms r where r.id not in (select SOMETHING from search(CAST(? AS DATE), CAST(? AS DATE)))";
PreparedStatement ps = conn.prepareStatement(searchQuery);
ps.setString(1, cinDate);
ps.setString(2, coutDate);

(正如 a_horse_with_no_name 在评论中指出的那样，由于您的内部选择，一般查询无论如何都不起作用。)