Retrieve sql date and store in java object which expects localDate

Question

I have to retrieve a Date from an oracle database and store it into a java object as localDate. This is the code I have tried:

public Sample findSampleById(Integer sampleId) {
    // TODO Auto-generated method stub
    PreparedStatement pStmt = null;
    ResultSet rs = null;
    Sample sample = new Sample();
    String sql = "select * from sample where sampleid =" + sampleId;
    
    try {
        pStmt = getConnection().prepareStatement(sql);
        rs = pStmt.executeQuery();
        while(rs.next()) {
            sample.setSampleId(rs.getInt("sampleid"));
            sample.setSampleKindId(rs.getInt("samplekindid"));
            sample.setExpirationDate(rs.getDate("expirationdate").toInstant().atZone(ZoneId.systemDefault()).toLocalDate());

        }
        
    } catch (SQLException e) {
        throw new DataException(e);
    }
    
    
    return sample;
}

Instead of this sample.setExpirationDate(rs.getDate("expirationdate").toInstant().atZone(ZoneId.systemDefault()).toLocalDate());

I have also tried this

java.sql.Date retrieved = java.sql.Date.valueOf(rs.getString("expirationDate")); sample.setExpirationDate(retrieved.Date());

Sample is a class with int sampleId, int sampleKindId and LocalDate expirationDate

My JUnit Test for the method tells me in the line where I ask for the expiration date that it throws an exception. Do I do anything wrong with the conversion?

Does this answer your question? [Convert between LocalDate and sql.Date](https://stackoverflow.com/questions/29750861/convert-between-localdate-and-sql-date) — Abra, Nov 09 '20 at 12:51
If you have a JDBC 4.2 compliant driver, then using `rs.getObject("expirationDate", LocalDate.class)` should be sufficient. Have you tried that? — Mark Rotteveel, Nov 09 '20 at 15:46

score 0 · Accepted Answer · answered Nov 09 '20 at 13:15

Method getDate, in interface java.sql.ResultSet, returns an instance of java.sql.Date.

You just need to call method toLocalDate on the value returned by method getDate.

I don't have your database so I simply tried selecting SYSDATE from the DUAL database table in Oracle. The following code worked fine.

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.LocalDate;

public class JdbcTest {

    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:..."; // Replace with your appropriate URL
        try (Connection conn = DriverManager.getConnection(url);
             Statement s = conn.createStatement();
             ResultSet rs = s.executeQuery("select sysdate from dual")) {
            if (rs.next()) {
                Date sysdate = rs.getDate(1);
                LocalDate ld = sysdate.toLocalDate();
                System.out.println("LocalDate: " + ld);
                System.out.println("  sysdate: " + sysdate);
            }
        }
        catch (Exception x) {
            x.printStackTrace();
        }
    }
}

Refer to Convert between LocalDate and sql.Date

Retrieve sql date and store in java object which expects localDate

1 Answers1