Java and Access - use Prepared Statement and get date

Question

I'm doing a JavaAFX application using the MS Access 2007 database. I need to extract data from the database by filtering after the date.

This work good:

ResultSet rs = stat.executeQuery("SELECT * FROM Notification WHERE 
postDate>=#2018-01-18# AND getDate<=#2019-02-18#");

But this not:

private final static String GET_ALL = 
    "SELECT * FROM Notification WHERE postDate>=? AND getDate<=?";

public ArrayList<Notification> getAllNotification(LocalDate postDate, LocalDate getDate) {

    PreparedStatement prepStmt = conn.prepareStatement(GET_ALL);       
    prepStmt.setDate(1, java.sql.Date.valueOf(postDate));
    prepStmt.setDate(2, java.sql.Date.valueOf(getDate));

    ResultSet rs = prepStmt.executeQuery();
}

What I'm doing wrong or what I'm not doing. I am using of course the ucanaccess library in version 4.0.3. Any suggestion or tip?

Are you getting an error message or are you just getting results that are different from what you expect? — Gord Thompson, Feb 18 '19 at 17:36
`java.sql.Date` is poorly designed and long outdated. I don’t remember from which version UCanAccess supports `LocalDate` and the other types from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). You should obviously prefer to use `LocalDate´ if U Can. :-) — Ole V.V., Feb 19 '19 at 09:25

Faisal · Answer 1 · 2019-02-18T12:05:41.680

0

In MS Access for dates '#' is used as an identifier. You will have to explicitly specify the # in the start and end of each date. You can try this and it should work.

"SELECT * FROM Notification WHERE postDate>= #?# AND getDate<= #?#";

I would use the following syntax for this purpose.

private String GET_ALL = "";

public ArrayList<Notification> getAllNotification(LocalDate postDate, LocalDate getDate) {

    GET_ALL = "Select * FROM Notification WHERE postDate >= #" + postDate + "# AND getDate <= #" + getDate + "#";

    PreparedStatement prepStmt = conn.prepareStatement(GET_ALL);
    ResultSet rs = prepStmt.executeQuery();
}

edited Feb 18 '19 at 12:05

answered Feb 18 '19 at 11:38

Faisal

403
4
18

Also I'm unsure if you can insert arguments into a statement that has been marked as 'final'. Please do check that too. – Faisal Feb 18 '19 at 11:43
unfortunately "UCAExc:::4.0.3 unknown token:" You can, all other instructions as final work fine. – Light Feb 18 '19 at 11:43
Try constructing the query like this inside the method "SELECT * FROM Notification WHERE postDate>= #" + postDate + "# AND getDate<= #" + getDate + "#"; instead of using the ? wildcard. – Faisal Feb 18 '19 at 11:48
Yes this work fine, i try it, but i can try use class PreparedStatement :) – Light Feb 18 '19 at 11:52
did you remove the final keyword? Also see the edited answer. – Faisal Feb 18 '19 at 11:58
yes, i try delete final word but this word is no problem. Yes i can use string connection but i try use class PreparedStatement. Why access cant accept this :prepStmt.setDate(1, java.sql.Date.valueOf(postDate)); – Light Feb 18 '19 at 12:03
try printing the prepStmt before calling the executeQuery function and see if the SQL is correct and the parameters have been inserted. System.out.println(prepStmt); – Faisal Feb 18 '19 at 12:07
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/188598/discussion-between-faisal-and-light). – Faisal Feb 18 '19 at 12:15
1

"_Also I'm unsure if you can insert arguments into a statement that has been marked as 'final'_". I'm assuming you're referring to the `final` keyword in `private static final String GET_ALL = ...;`. The presence of this `final` is irrelevant to the `PreparedStatement`. All it does is say the `GET_ALL` variable cannot be reassigned. The `PreparedStatement` does not, _and cannot_, reassign the variable because Java is pass-by-value; also, `String` is immutable. – Slaw Feb 18 '19 at 16:46

Java and Access - use Prepared Statement and get date

1 Answers1