How do I properly get a SQL Datetime back into my Java application?

Question

I'm having trouble with retrieving queries from my SQL database. I can get the blasted things added to the database, but I'm having an inordinate amount of difficulties performing the reverse. Three things, in order:

The SQL Table itself:

CREATE TABLE patientInstructions (
    id          INT UNSIGNED AUTO_INCREMENT,
    lastUpdated datetime NOT NULL,
    PatientID          BIGINT UNSIGNED NOT NULL,
    HCPID              BIGINT UNSIGNED NOT NULL,
    OVid               BIGINT UNSIGNED NOT NULL,
    urlLink             VARCHAR(250) NOT NULL,
    linkInstructions    VARCHAR(500) NOT NULL,
    linkName            VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
) AUTO_INCREMENT=1 ENGINE=MyISAM;

The method call that is failing (I'm getting -1L instead of the actual data value stored in the database, which is why I know there's a problem in the first place):

public String getLastUpdated(long ovID) throws DBException {
        try {
            return psiDAO.getLastUpdated(ovID);
        } catch (myOwnException e) {
            e.printStackTrace();
            return "-1L";
        }
    }

And finally, the method call which is failing:

public String getLastUpdated(long ovId) throws DBException {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = factory.getConnection();
            ps = conn.prepareStatement("SELECT * FROM patientInstructions"
                    + " WHERE ovId=?");
            ps.setLong(1, ovId);
            ResultSet rs = ps.executeQuery();
            java.util.Date updated = new java.util.Date();
            updated = rs.getTime("lastUpdated");
            return updated.toString();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DBException(e);
        } finally {
            DBUtil.closeConnection(conn, ps);
        }
    }

What Java object matches a SQL Datetime? I've tried rs.getTimestamp, rs.getDate, etc. but haven't had any more success (though I'm not ruling out that I botched those up either). Am I making a mistake transferring the data from the resultset back to Java object?

Solution 1

You must call ResultSet.next() on a ResultSet before accessing each row, including the first. You're not doing that here; the exception message should actually be telling you this.

Solution 2

There are three classes: java.sql.Date, java.sql.Time, and java.sql.Timestamp. I'm not sure which one most closely corresponds to a DATETIME in SQL. All three derive from java.util.Date, which is why you are able to assign the result of calling rs.getTime to one.

I don't have a setup here that would allow me to test it, but I would try using one of the types from java.sql.* and see what results that gives you.

Whoops! I posted my answer before Ernest's and I didn't even notice the missing rs.next(). That is definitely your problem, and I bet java.util.Date will work just fine for you, with that change.

Solution 3

Since your datatype is datetime, you will need to use getTimestamp()... see here for more datatype mapping information.

So, your code should look something like this:-

...
ResultSet rs = ps.executeQuery();

String updated = "";

if (rs.next()) {
    updated = rs.getTimestamp("lastUpdated").toString();
}

rs.close();
ps.close();

return updated;