Tags: Hibernate , MSSQL , Stored Procedure

Datatype Conversion is silent part of any project, yet very important. You'd like to read importance of Datatype Conversion [http://en.wikipedia.org/wiki/Cluster_(spacecraft)]

Case Scenario:

• Pass current date (java.util.Date) to database stored procedure in varchar / string format.
• Convert string date to datetime at database level. 
• Process our logic and return date in string format.
• Get date in string format at java.
• Convert string date in java to java.util.Date

IF (OBJECT_ID('SP_DateTime') IS NOT NULL)
  DROP PROCEDURE SP_DateTime
GO
CREATE PROCEDURE SP_DateTime @DateTimeString VARCHAR(100)
AS
BEGIN
 /**
  * @author javaQuery
  * @test EXEC SP_DateTime '2012-12-15 16:30'
  */
 DECLARE @v_Datetime DATETIME;
 SET @v_Datetime = CONVERT(varchar,@DateTimeString, 120);
 /* Compare dates and other business logic */
 SELECT CAST(@v_Datetime AS VARCHAR(100));
END

Step 2. Calling above stored procedure from Java (Hibernate: How To Call Stored Procedure In Hibernate).

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import org.hibernate.*;
import org.hibernate.cfg.Configuration;

public class SP_DATETIME {

    public static void main(String[] args) {
        /* Create hibernate configuration. */
        Configuration c = new Configuration();
        c.configure("hibernate.cfg.xml");

        /* Open session and begin database transaction for database operation. */
        SessionFactory sf = c.buildSessionFactory();
        Session session = sf.openSession();

        /* Call Stored Procedure */
        Query callStoredProcedure_MSSQL = session.createSQLQuery("EXEC SP_DateTime :param1");
        callStoredProcedure_MSSQL.setString("param1", String.valueOf(new Timestamp(new Date().getTime())));

        /* Get return value */
        List customResult = callStoredProcedure_MSSQL.list();
        if (customResult != null && !customResult.isEmpty()) {
            SimpleDateFormat formatter = new SimpleDateFormat("MMM dd yyyy hh:mma");
            try {
                Date d = formatter.parse(customResult.get(0));
                System.out.println(d.toString());
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        session.close();
    }
}

For other date format check http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

Question: Why we choose string rather than timestamp?
Answer: Consider if you have common class that pass call to stored procedure. You don't know which datatype will come as an argument to your method. We'll set every parameter as string.

Question: Why we are returning date as string from Stored Procedure.
Answer: When we get NULL from Database hibernate will try to convert NULL to date and will throw an exception.

Tags: Hibernate , MSSQL , Stored Procedure