Recent Posts

Wednesday, 14 November 2018

Inserting Date Values into Database

     Sometimes as the part of programing requirement, we have to insert and retrieve Date like DOB, DOJ, DOM etc. with respect to database.
     It is not recommended to maintain date values in the form of String, because comparisons will become difficult. In Java we have two Date classes
1. java.util.Date
2. java.sql.Date.
     java.sql.Date is the child class of java.util.Date.
     java.sql.Date is specially designed class for handling Date values wrt database. Other than database operations, if we want to represent Date in our java program then we should go for java.util.Date. java.util.Date can represent both Date and Time where as java.sql.Date represents only Date but not time.
**
 * 
 * @author ashok.mariyala
 *
 */
public class DateTest { 
   public static void main(String[] args) { 
      java.util.Date udate = new java.util.Date();
      System.out.println("Util Date : "+ udate);
      long l = udate.getTime(); 
      java.sql.Date sdate = new java.sql.Date(l);
      System.out.println("SQL Date : "+ sdate); 
   } 
}
Output
Util Date : Thu Nov 15 09:15:39 IST 2018
SQL Date : 2018-11-15
Note
     In sql package Time class is availble to represent Time values and TimeStamp class is available to represent both Date and Time.

Inserting Date Values into Database
     Various databases follow various styles to represent Date.
E.g:
Oracle: dd-MMM-yy 15-NOV-18
MySQL : yyyy-mm-dd 2018-11-15

     If we use simple Statement object to insert Date values then we should provide Date value in the database supported format,which is difficult to the programmer.
     If we use PreparedStatement,then we are not required to worry about database supported form, just we have to call
pst.setDate (2, java.sql.Date);
     This method internally converts date value into the database supported format. Hence it is highly recommended to use PreparedStatement to insert Date values into database.

Steps to insert Date value into Database
DB: create table users(name varchar2(10), dob date);

1. Read Date from the end user(in String form)
   System.out.println("Enter DOB(dd-mm-yyyy):");
   String dob = sc.next();
  
2. Convert date from String form to java.util.Date form by using SimpleDateFormat object.
   SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
   java.util.Date udate = sdf.parse(dob);
  
3. convert date from java.util.Date to java.sql.Date
   long l = udate.getTime();
   java.sql.Date sdate = new java.sql.Date(l);
  
4. set sdate to query
   pst.setDate(2, sdate);
import java.sql.*;
import java.util.*;
import java.text.*;
**
 * 
 * @author ashok.mariyala
 *
 */
public class DateInsertTest { 
   public static void main(String[] args) throws Exception { 
      Class.forName("oracle.jdbc.OracleDriver");
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger"); 
      Scanner sc = new Scanner(System.in); 
      System.out.println("Enter User Name:"); 
      String uname = sc.next(); 
      System.out.println("Enter DOB(yyyy-MM-dd):"); 
      String dob = sc.next();
      java.sql.Date sdate = java.sql.Date.valueOf(dop); 
      String sqlQuery = "insert into users values(?,?)"; 
      PreparedStatement pst = con.prepareStatement(sqlQuery); 
      pst.setString(1,uname); 
      pst.setDate(2,sdate); 
      int rowCount = pst.executeUpdate(); 
      if(rowCount == 0) 
         System.out.println("Record Not inserted"); 
      else
         System.out.println("Record inserted"); 
      con.close();
   }
}

Next Tutorial : Retrieving Date values from the database

Previous Tutorial : Batch Update

No comments:

Post a Comment