Storing Date / Timestamp values in database (db2) using java

The following tutorial explains how the date value is stored in a date field of any database table (here DB2)  using java code and also covers that how the timestamp value is stored in timestamp column. In DB2, Date value can be stored in a date column in db2 table by converting the date string to Java sql date. Also when a date string is directly stored in a date column, it is automatically converted to the date value represented by the string. Date string needs to be converted to JDBC date escape format (YYYY-MM-DD) which can be a input to java.sql.date.valueOf() method to convert to a Date value. Suppose If we accept the date format as DD/MM/YYYY as input string , then we have to convert to the date format YYYY-MM-DD using Java. For converting the date , following java code may be used.


SimpleDateFormat in = new SimpleDateFormat("dd/MM/yyyy");
SimpleDateFormat out = new SimpleDateFormat("yyyy-MM-dd");
String dt = inputDate; // DD/MM/YYYY
String dateStringOut="";
try {
Date date = in.parse(dt);
dateStringOut= out.format(date); // YYYY-MM-DD
} catch (Exception e) {
System.out.println("Exception is :" + e);
} 

I have used the table Med_Master table that has the following fields.

MED_CODE VARCHAR 10 No

MED_NAME VARCHAR 20 No

STOCK BIGINT 8 No

EXPIRY_DATE DATE 4 No

STOCK_UPDATE DATE 4 No

CREATED_ON TIMESTAMP 10 No

MODIFIED_ON TIMESTAMP 10 No

CAT_CODE VARCHAR 5 No

PACKED_ON DATE 4 No

DB2 DDL statement to create the table (med_master ) with the above fields

Create table med_master (MED_CODE VARCHAR (10) , MED_NAME VARCHAR (20), STOCK BIGINT , EXPIRY_DATE DATE , STOCK_UPDATE DATE, CREATED_ON TIMESTAMP ,MODIFIED_ON TIMESTAMP , CAT_CODE VARCHAR (5), PACKED_ON DATE 4 )

In our example , Date and Timestamp values are stored in db2 table using following ways.

1. Date string is accepted in the format DD/MM/YYYY through key board and converted to JDBC date escape format (YYYY-MM-DD) and stored using setString 

2. Date string is accepted in the format DD/MM/YYYY through key board and converted to YYYY-MM-DD format, then converted to SQL Date using java.sql.Date.valueOf method and stored using setDate

3. Current date (java.util.Date) is converted to java.sql.Date using new java.sql.Date(_today.getTime())) OR using java.sql.Date.valueOf () method (similar to 2nd step) and stored using setDate.

4. Current date (java.util.Date) is converted to YYYY-MM-DD format, then converted to timestamp using new Timestamp(_today.getTime()) and stored using setTimestamp.

Note : When a date value is stored in a character string column, it is converted to a date string representing that value, and padded with blanks at the end if needed. Truncation is not allowed so that the column must be wide enough to hold the whole string.

The source code is as follows.

*/


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;
import java.util.Date;
import java.util.Scanner;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;

public class StoreDb2Date {
public static void main(String[] argv) {

try {
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
} catch (ClassNotFoundException e) {
           System.out.println("Please check the Classpath to include DB2 Driver location");
           return;
       }

Connection conn = null;
PreparedStatement pstmt = null;

try {
conn = DriverManager.getConnection("jdbc:db2:sales");

Scanner sc = new Scanner (System.in);
System.out.println("Enter ItemCode, Medicine Name, Packed on (dd/mm/yyyy) , Expiry Date (dd/mm/yyyy) , Quantity , Category Code one by one");
String itemCode=sc.next();
String medName=sc.next();
String pkdOn=sc.next();
String expDate=sc.next();
int qty=sc.nextInt();
String catCode=sc.next();

Date today = new Date();
java.util.Date _today = today;
Timestamp createdon = new Timestamp(_today.getTime());
SimpleDateFormat in = new SimpleDateFormat("dd/MM/yyyy");
SimpleDateFormat out = new SimpleDateFormat("yyyy-MM-dd");
String _expDate="";
String _pkdOn="";
try {
    Date date = in.parse(expDate);
    Date date1 = in.parse(pkdOn);
   _expDate= out.format(date);
   _pkdOn= out.format(date1);
   } catch (Exception e)
      { System.out.println("Exception is :" + e); }

String todayDate="";
try {
       todayDate = out.format(_today);
     } catch (Exception e)
          {    System.out.println("Exception is :" + e);
             }

String strQueryInsert="Insert into MED_MASTER (MED_CODE, MED_NAME, STOCK, EXPIRY_DATE, STOCK_UPDATE, CREATED_ON, MODIFIED_ON, CAT_CODE,PACKED_ON) values(?,?,?,?,?,?,?,?,?)";

pstmt=conn.prepareStatement(strQueryInsert);
pstmt.setString(1, itemCode);
pstmt.setString(2, medName);
pstmt.setInt(3, qty);
pstmt.setString(4, _expDate);
pstmt.setDate(5, new java.sql.Date(_today.getTime())); // OR java.sql.Date.valueOf(todayDate));
pstmt.setTimestamp(6, createdon);
pstmt.setTimestamp(7,createdon);
pstmt.setString(8,catCode);
pstmt.setDate(9, java.sql.Date.valueOf(_pkdOn));
int success=pstmt.executeUpdate();

   } catch (SQLException e) {
           System.out.println("Error Storing data");
         }
     }
  }
}

You may also like

Leave a Reply