Converting java.sql.Date values retrieved via JDBC into java.time.LocalDate?

Question

I'm trying to retrieve date information from a Microsoft Access database using UCanAccess. I need to put it in a LocalDate column in a tableview and I'm doing this

Date date = res.getDate(3);
LocalDate dateEchantillonnage = LocalDate.parse( new SimpleDateFormat("yyyy-MM-dd").format(date) );

but it is causing me two problems:

The first one is that it gives a NullPointerException because sometimes the date can be null, so I want to know if there is a way to remedy to this.

The second one is when I replace

Date date = res.getDate(3);
LocalDate dateEchantillonnage = LocalDate.parse( new SimpleDateFormat("yyyy-MM-dd").format(date) );

with

Date date = new Date(2015-07-01);
LocalDate dateEchantillonnage = LocalDate.parse( new SimpleDateFormat("yyyy-MM-dd").format(date) );

it always displays the date as: 1970-01-01

this looks unrelated to javafx? For conversion of Date to LocalDate see http://stackoverflow.com/q/21242110/203657 — kleopatra, Aug 05 '15 at 10:40
You could check if your JDBC driver supports the JDBC 4.2 conversion for the `java.time`-package:, eg `getObject(idx, LocalDate.class)`. But support varies per driver. — Mark Rotteveel, Aug 06 '15 at 20:59
@MarkRotteveel - Good suggestion. However, I just tried it with UCanAccess 3.0.0 and unfortunately it didn't work. — Gord Thompson, Aug 07 '15 at 02:15
Did you try just doing `LocalDate dateEchantillonnage = date.toLocalDate();` ...? — Gord Thompson, Aug 07 '15 at 02:39
p.s. `new Date(2015-07-01)` is the same as `new Date(2007)` (2015 - 7 - 1 = 2007), which is 2007 ms after the epoch, or `1970-01-01 00:00:02.007 UTC` — Gord Thompson, Aug 07 '15 at 02:55
@GordThompson oh... date.toLocalDate(); actually does the trick better since it doesn't change the date's format. — whites, Aug 07 '15 at 08:35

score 1 · Answer 1 · answered Aug 07 '15 at 14:19

1

Optional<LocalDate> dateEchantillonnage = date == null
    ? Optional.empty()
    : Optional.of(date.toLocalDate());

answered Aug 07 '15 at 14:19

Joop Eggen

96,344
7
73
121

Lukas Eder · Accepted Answer · 2015-08-07T05:47:49.983

Fix the NPE:

Date date = res.getDate(3);
LocalDate dateEchantillonnage = date == null
    ? null
    : LocalDate.parse( new SimpleDateFormat("yyyy-MM-dd").format(date) );

Fix the date always turning out to be 1970-01-01:

Date date = Date.valueOf("2015-07-01"); // You passed an int 2015-07-01 = 2007 :)
LocalDate dateEchantillonnage = date == null
    ? null
    : LocalDate.parse( new SimpleDateFormat("yyyy-MM-dd").format(date) );

Gord Thompson · Answer 3 · 2015-08-07T15:57:01.527

The following code works for me with UCanAccess 3.0.0 under JDK 1.8.0_51:

public static void main(String[] args) {
    String dbFilePath = "C:/Users/Public/UCanAccessDemo.accdb";
    String connUrl = "jdbc:ucanaccess://" + dbFilePath;
    try (Connection conn = DriverManager.getConnection(connUrl)) {
        String sql = "SELECT Date1 FROM DateTest ORDER BY ID";
        try (Statement st = conn.createStatement()) {
            try (ResultSet rs = st.executeQuery(sql)) {
                while (rs.next()) {
                    LocalDate dateEchantillonnage = null;
                    try {
                        dateEchantillonnage = rs.getDate(1).toLocalDate();
                    } catch (NullPointerException npe) {
                        // do nothing
                    }
                    System.out.println(dateEchantillonnage);
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace(System.err);
    }
}

Converting java.sql.Date values retrieved via JDBC into java.time.LocalDate?

3 Answers3