How to tell if timestamp from Postgres in Java is null

Question

The code below is a snippet from a back-end REST service I have touched on. I'm wondering how to tell if a timestamp I'm getting is null. I've learned through trial and error that if the timestamp is null in the Postgres database that when I bring it into Java that if its null it will get instantiated as a new DateTime at the current time of operation. What can I do to check get and set nulls dynamically according to model data?

public List<SubscriptionEntityModel> getAllSubscriptions(PagingInfoDomainModel paging) throws Exception {
    Database db = new Database();

    String stmt = "{call sp_subscriptions_get_all(?, ?)}";

    if(AppConfig.data.isDebug) {
        logger.debug("Running database operation.. " + stmt);
    }

    CallableStatement sproc = db.dbEndpoint.prepareCall(stmt);
    sproc.setInt(1, paging.pageCurrent);
    sproc.setInt(2, paging.pageItemCount);

    ResultSet rs = sproc.executeQuery();

    List<SubscriptionEntityModel> subscriptions = new ArrayList<SubscriptionEntityModel>();
    while(rs.next()) {
        SubscriptionEntityModel subscription = new SubscriptionEntityModel();
        subscription.subscriptionId = rs.getInt("subscription_id");
        subscription.name = rs.getString("name");
        subscription.emailAddress = rs.getString("email_address");
        subscription.phoneNumber = rs.getString("phone_number");
        subscription.organizationId = rs.getInt("organization_id");

        subscription.createdAt = new DateTime(rs.getTimestamp("created_at"));
        subscription.expiredAt = new DateTime(rs.getTimestamp("expired_at"));

        subscriptions.add(subscription);
    }

    sproc.close();
    db.destroy();

    return subscriptions;
}

Luciano van der Veekens · Accepted Answer · 2017-06-01 19:59:21Z

3

Check out ResultSet#wasNull

https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#wasNull()

Reports whether the last column read had a value of SQL NULL.

Hence you can use it after rs.getTimestamp() to check if the value read was SQL NULL, but note that ResultSet#getTimestamp already returns a null reference if that was the case.

https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#getTimestamp(int)

the column value; if the value is SQL NULL, the value returned is null

The problem you're facing is that when you pass a null reference to the constructor of DateTime, it will be interpreted as now.

edited Jun 1, 2017 at 19:59

answered Jun 1, 2017 at 19:47

Luciano van der Veekens

6,6474 gold badges30 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

davidxxx · Accepted Answer · 2017-06-02 09:35:34Z

1

java.sql.Timestamp getTimestamp(String columnLabel) throws SQLException;

will return null if the SQL field is NULL.

The problem is the way you instantiate your DateTime object.
With JodaTime (I give this example as you don't specify the used library), executing the following code creates indeed a DateTime instance with the actual date time :

java.sql.Timestamp timestamp = null;
DateTime dateTime = new DateTime(timestamp);

So to solve your problem, what you could do is checking the returned value by the resultset.
If it is not null, use it to create the DateTime object.
Otherwise don't use it and leave to nullthe DateTime field of the SubscriptionEntityModel you are setting properties.

So instead of doing :

subscription.createdAt = new DateTime(rs.getTimestamp("created_at"));

You should do :

 java.sql.Timestamp createdAt = rs.getTimestamp("created_at");
 if (createdAt != null){
   subscription.expiredAt = new DateTime(createdAt);
 }

edited Jun 2, 2017 at 9:35

answered Jun 1, 2017 at 19:52

davidxxx

132k23 gold badges231 silver badges228 bronze badges

1 Comment

Dan Over a year ago

this was very helpful! thank you very much for helping me understand the problem.

Collectives™ on Stack Overflow

How to tell if timestamp from Postgres in Java is null

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related