CODEWITHSUNDEEP
sqlspringhibernatejdbcjava.util.date
Rakesh Gourineni
Rakesh Gourineni

Reputation: 1421

Store HH:MM:SS with Date field in web application(Spring+Hibernate)

I am working on a REST API using Spring and Hibernate. For the PUT operation I need to store the time along with the date. From my investigation, I found out that the application is returning the time along with the date. But while writing this field in database table, only the date field is visible and the time field is shown as default 00:00:00. In the database the DateTime column is declared by TIMESTAMP field.

MyApproach

The application is using the Date field(java.util.Date import). So I tried to change the Date field to Timestamp field(import to juva.sql.Timestamp) as it stores both time and date. But I couldn't solve it.

Please find my hibernatequery code: This code is to insert data in the database(PUT operation)

public Subscriber insertSubscriber(Subscriber subscriber, LookupKeys lookupKeys) {

    // This value cannot be null
    if (subscriber.getLastModifiedTime() == null) subscriber.setLastModifiedTime(new Date());

    // Initialize our INSERT statement
    StringBuilder queryBuilder = new StringBuilder("INSERT INTO USERS (");
    queryBuilder.append(OracleStandardAttributeNames.id.toString());

    // Set the CDID column(s)
    for (int i = 0; i < lookupKeys.getKeyCount(); i++) {
        queryFriendlyNames(queryBuilder, null, lookupKeys.getKey(i).getColumnName(), subscriber.getCustomerDefinedId().get(i), false);
    }

    //SimpleDateFormat sdf = new SimpleDateFormat("YYYY-mm-DD HH-mm-ss") ;
    //sdf.format(subscriber.getLastModifiedTime());
    queryFriendlyNames(queryBuilder, lookupKeys, OracleStandardAttributeNames.firstName.toString(), subscriber.getFirstName(), false);
    queryFriendlyNames(queryBuilder, lookupKeys, OracleStandardAttributeNames.lastName.toString(), subscriber.getLastName(), false);

    queryFriendlyNames(queryBuilder, lookupKeys, OracleStandardAttributeNames.email.toString(), subscriber.getEmailAddress(), false);

    queryFriendlyNames(queryBuilder, lookupKeys, OracleStandardAttributeNames.divisionMembership.toString(), subscriber.getDivisionMembership(), false);
    queryFriendlyNames(queryBuilder, lookupKeys, OracleStandardAttributeNames.divisionSubscriptionStatus.toString(), subscriber.getDivisionSubscriptionStatus(), false);

    queryFriendlyNames(queryBuilder, lookupKeys, OracleStandardAttributeNames.lastReferredTime.toString(), subscriber.getLastReferredTime(), false);
    queryFriendlyNames(queryBuilder, lookupKeys, OracleStandardAttributeNames.postalCode.toString(), subscriber.getPostalCode(), false);
    queryFriendlyNames(queryBuilder, lookupKeys, OracleStandardAttributeNames.prefersMobile.toString(), subscriber.getPrefersMobile(), false);
    queryFriendlyNames(queryBuilder, lookupKeys, OracleStandardAttributeNames.lastSuppressedTime.toString(), subscriber.getLastSuppressedTime(), false);
    queryFriendlyNames(queryBuilder, lookupKeys, OracleStandardAttributeNames.emailFormat.toString(), subscriber.getEmailProgram(), false);
    queryFriendlyNames(queryBuilder, lookupKeys, OracleStandardAttributeNames.deadEmailTime.toString(), subscriber.getDeadEmailTime(), false);
    queryFriendlyNames(queryBuilder, lookupKeys, OracleStandardAttributeNames.dateCreated.toString(), subscriber.getDateCreated(), false);
    queryFriendlyNames(queryBuilder, lookupKeys, OracleStandardAttributeNames.lastModifiedTime.toString(), subscriber.getLastModifiedTime(), false);
    //queryFriendlyNames(queryBuilder, lookupKeys, OracleStandardAttributeNames.lastModifiedTime.toString(), sdf.format(subscriber.getLastModifiedTime()), false);
    queryFriendlyNames(queryBuilder, lookupKeys, OracleStandardAttributeNames.subscribed.toString(), subscriber.getSubscribed(), false);
    queryFriendlyNames(queryBuilder, lookupKeys, OracleStandardAttributeNames.lastModifiedOnWebTime.toString(), subscriber.getLastModifiedOnWebTime(), false);
    queryFriendlyNames(queryBuilder, lookupKeys, OracleStandardAttributeNames.lastSubscribedTime.toString(), subscriber.getLastSubscribedTime(), false);
    queryFriendlyNames(queryBuilder, lookupKeys, OracleStandardAttributeNames.lastUnsubscribedTime.toString(), subscriber.getLastUnsubscribedTime(), false);

    queryBuilder.append(") VALUES (");
    queryBuilder.append("USERIDSEQUENCE.NEXTVAL");

    for (int i = 0; i < lookupKeys.getKeyCount(); i++) {
        queryFriendlyValues(queryBuilder, null, "customerDefinedId" + i, subscriber.getCustomerDefinedId().get(i), false);
    }

    queryFriendlyValues(queryBuilder, lookupKeys, "firstName", subscriber.getFirstName(), false);
    queryFriendlyValues(queryBuilder, lookupKeys, "lastName", subscriber.getLastName(), false);

    // We do not want to include "EMAIL" twice.
    if (!lookupKeys.isEmailAKey()) {
        queryFriendlyValues(queryBuilder, lookupKeys, "email", subscriber.getEmailAddress(), false);
    }

    queryFriendlyValues(queryBuilder, lookupKeys, "divisionMembership", subscriber.getDivisionMembership(), false);
    queryFriendlyValues(queryBuilder, lookupKeys, "divisionSubscriptionStatus", subscriber.getDivisionSubscriptionStatus(), false);
    queryFriendlyValues(queryBuilder, lookupKeys, "lastReferredTime", subscriber.getLastReferredTime(), false);
    queryFriendlyValues(queryBuilder, lookupKeys, "postalCode", subscriber.getPostalCode(), false);
    queryFriendlyValues(queryBuilder, lookupKeys, "prefersMobile", subscriber.getPrefersMobile(), false);
    queryFriendlyValues(queryBuilder, lookupKeys, "lastSuppressedTime", subscriber.getLastSuppressedTime(), false);
    queryFriendlyValues(queryBuilder, lookupKeys, "emailProgram", subscriber.getEmailProgram(), false);
    queryFriendlyValues(queryBuilder, lookupKeys, "deadEmailTime", subscriber.getDeadEmailTime(), false);
    queryFriendlyValues(queryBuilder, lookupKeys, "dateCreated", subscriber.getDateCreated(), false);
    queryFriendlyValues(queryBuilder, lookupKeys, "lastModifiedTime", subscriber.getLastModifiedTime(), false);
    //queryFriendlyValues(queryBuilder, lookupKeys, "lastModifiedTime", sdf.format(subscriber.getLastModifiedTime()), false);
    queryFriendlyValues(queryBuilder, lookupKeys, "subscribed", subscriber.getSubscribed(), false);
    queryFriendlyValues(queryBuilder, lookupKeys, "lastModifiedOnWebTime", subscriber.getLastModifiedOnWebTime(), false);
    queryFriendlyValues(queryBuilder, lookupKeys, "lastSubscribedTime", subscriber.getLastSubscribedTime(), false);
    queryFriendlyValues(queryBuilder, lookupKeys, "lastUnsubscribedTime", subscriber.getLastUnsubscribedTime(), false);

    queryBuilder.append(")");

    // Set the statement parameters
    Session session = (Session) entityManager.getDelegate();
    org.hibernate.Query hibernateQuery = session.createSQLQuery(queryBuilder.toString());

    for (int i = 0; i < lookupKeys.getKeyCount(); i++) {
        hibernateQuery.setParameter("customerDefinedId" + i, subscriber.getCustomerDefinedId().get(i));
    }

    if (subscriber.getFirstName() != null && !lookupKeys.isColumnNameALookupKey(OracleStandardAttributeNames.firstName.toString()))
        hibernateQuery.setString("firstName", subscriber.getFirstName());
    if (subscriber.getLastName() != null && !lookupKeys.isColumnNameALookupKey(OracleStandardAttributeNames.lastName.toString()))
        hibernateQuery.setString("lastName", subscriber.getLastName());
    if (subscriber.getEmailAddress() != null && !lookupKeys.isColumnNameALookupKey(OracleStandardAttributeNames.email.toString()))
        hibernateQuery.setString("email", subscriber.getEmailAddress());
    if (subscriber.getDivisionMembership() != null && !lookupKeys.isColumnNameALookupKey(OracleStandardAttributeNames.divisionMembership.toString()))
        hibernateQuery.setLong("divisionMembership", subscriber.getDivisionMembership());
    if (subscriber.getDivisionSubscriptionStatus() != null && !lookupKeys.isColumnNameALookupKey(OracleStandardAttributeNames.divisionSubscriptionStatus.toString()))
        hibernateQuery.setLong("divisionSubscriptionStatus", subscriber.getDivisionSubscriptionStatus());
    if (subscriber.getPostalCode() != null && !lookupKeys.isColumnNameALookupKey(OracleStandardAttributeNames.postalCode.toString()))
        hibernateQuery.setString("postalCode", subscriber.getPostalCode());
    if (subscriber.getPrefersMobile() != null && !lookupKeys.isColumnNameALookupKey(OracleStandardAttributeNames.prefersMobile.toString()))
        hibernateQuery.setInteger("prefersMobile", subscriber.getPrefersMobile());
    if (subscriber.getLastSuppressedTime() != null && !lookupKeys.isColumnNameALookupKey(OracleStandardAttributeNames.lastSuppressedTime.toString()))
        hibernateQuery.setTimestamp("lastSuppressedTime", Utils.getGMTDateFromLocalDate(subscriber.getLastSuppressedTime()));
    if (subscriber.getEmailProgram() != null && !lookupKeys.isColumnNameALookupKey(OracleStandardAttributeNames.emailFormat.toString()))
        hibernateQuery.setLong("emailProgram", subscriber.getEmailProgram());
    if (subscriber.getDeadEmailTime() != null && !lookupKeys.isColumnNameALookupKey(OracleStandardAttributeNames.deadEmailTime.toString()))
        hibernateQuery.setTimestamp("deadEmailTime", Utils.getGMTDateFromLocalDate(subscriber.getDeadEmailTime()));
    if (subscriber.getDateCreated() != null && !lookupKeys.isColumnNameALookupKey(OracleStandardAttributeNames.dateCreated.toString()))
        hibernateQuery.setTimestamp("dateCreated", Utils.getGMTDateFromLocalDate(subscriber.getDateCreated()));
    if (subscriber.getLastModifiedTime() != null && !lookupKeys.isColumnNameALookupKey(OracleStandardAttributeNames.lastModifiedTime.toString()))
        hibernateQuery.setTimestamp("lastModifiedTime", Utils.getGMTDateFromLocalDate(subscriber.getLastModifiedTime()));
       // hibernateQuery.setTimestamp("lastModifiedTime", Utils.parseDateISO8601(subscriber.getLastModifiedTime()));
    if (subscriber.getSubscribed() != null && !lookupKeys.isColumnNameALookupKey(OracleStandardAttributeNames.subscribed.toString()))
        hibernateQuery.setInteger("subscribed", subscriber.getSubscribed());
    if (subscriber.getLastModifiedOnWebTime() != null && !lookupKeys.isColumnNameALookupKey(OracleStandardAttributeNames.lastModifiedOnWebTime.toString()))
        hibernateQuery.setTimestamp("lastModifiedOnWebTime", Utils.getGMTDateFromLocalDate(subscriber.getLastModifiedOnWebTime()));
    if (subscriber.getLastSubscribedTime() != null && !lookupKeys.isColumnNameALookupKey(OracleStandardAttributeNames.lastSubscribedTime.toString()))
        hibernateQuery.setTimestamp("lastSubscribedTime", Utils.getGMTDateFromLocalDate(subscriber.getLastSubscribedTime()));
    if (subscriber.getLastUnsubscribedTime() != null && !lookupKeys.isColumnNameALookupKey(OracleStandardAttributeNames.lastUnsubscribedTime.toString()))
        hibernateQuery.setTimestamp("lastUnsubscribedTime", Utils.getGMTDateFromLocalDate(subscriber.getLastUnsubscribedTime()));
    if (subscriber.getLastReferredTime() != null && !lookupKeys.isColumnNameALookupKey(OracleStandardAttributeNames.lastReferredTime.toString()))
        hibernateQuery.setTimestamp("lastReferredTime", Utils.getGMTDateFromLocalDate(subscriber.getLastReferredTime()));

    // Execute the INSERT
    hibernateQuery.executeUpdate();

    // Return managed Subscriber
    return findByCustomerDefinedId(subscriber.getCustomerDefinedId(), lookupKeys);
}

Please let me know of a possible apporach. Thanks in advance.

Regards, Rakesh

Upvotes: 0

Views: 326

Answers (1)

Ralph
Ralph

Reputation: 120871

If you store a java.lang.Date in a Database, then you need to tell Hibernate or JPA what "part" of the Date should be stored. In JPA and Hibernate (with Annotations) this is done by @Temporal annotation.

  • TIME - time of day
  • DATE - only the date
  • TIMESTAMP - date an time

For example:

@Temporal(TemporalType.TIMESTAMP)
private Date myDate;

@See: Hibernate Reference about Temporal

Upvotes: 2

Related Questions