Java Code House

This is a very popular question often asked in an interview. You also might be in a situation where you have to store the current date retrieved by java.util.Date in a database table where the datatype of the column is DATE . In that case you have to convert it to java.sql.Date . Also you might have to fetch date from database and then store it in a java.util.Date object . So this is a very important topic. Since both of these classes store the value of date in long milliseconds, so it is very easy to convert from one type to the other. Both of these classes have a method called getTime() which is used for conversion.

public class DateConverter {

    public static void main(String[] args) {
      
        //creating instances of java.util.Date representing current date and time
        java.util.Date now = new java.util.Date();
        System.out.println("Value of java.util.Date : " + now);
      
        //converting java.util.Date to java.sql.Date in Java
        java.sql.Date sqlDate = new java.sql.Date(now.getTime());
        System.out.println("Converted value of java.sql.Date : " + sqlDate);
      
        //converting java.sql.Date to java.util.Date back
        java.util.Date utilDate = new java.util.Date(sqlDate.getTime());
        System.out.println("Converted value of java.util.Date : " + utilDate);
    }
}

In our last post we discussed about how to obtain a database connection.After we have it, we can carry out different JDBC operations. Here we will discuss about

• How to insert new records/rows in database tables (INSERT operation)
• How to find a record/row in database tables (SELECT operation)
• How to update records/rows in tables (UPDATE operation)
• How to delete a record/row from tables (DELETE operation)

Different JDBC operations

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class StudentService {

 private Connection con;
 public StudentService(Connection con){
  this.con = con;  //database connection object
 }
 //Inserting new row in table with given id and name
 public void insertRecord(int id, String name) throws SQLException{
  con.setAutoCommit(false);
  String sql = "INSERT INTO student VALUES(?,?)";
  PreparedStatement ps = con.prepareStatement(sql);
  ps.setInt(1, id);
  ps.setString(2, name);
  ps.executeUpdate();
  con.commit();
  ps.close();
 }
 //Find a record based on id
 public void findRecord(int id) throws SQLException{
  String sql = "SELECT * FROM student WHERE id=?";
  PreparedStatement ps = con.prepareStatement(sql);
  ps.setInt(1, id);
  ResultSet rs = ps.executeQuery();
  if(rs.next())
   System.out.println(rs.getInt("id")+"\t"+rs.getString("name"));
  else
   System.out.println("No records found");
  ps.close();
 }
 //Find all the records
 public void findAllRecord() throws SQLException{
  con.setAutoCommit(false);
  String sql = "SELECT * FROM student";
  PreparedStatement ps = con.prepareStatement(sql);
  ResultSet rs = ps.executeQuery();
  while(rs.next())
   System.out.println(rs.getInt("id")+"\t"+rs.getString("name"));
  ps.close();
 }
 //Update a record with new name based on id
 public void updateRecord(int id, String name) throws SQLException{
  con.setAutoCommit(false);
  String sql = "SELECT id,name FROM student WHERE id=?";
  PreparedStatement ps=con.prepareStatement(sql,
    ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_UPDATABLE);
  ps.setInt(1, id);
  ResultSet rs=ps.executeQuery();
  rs.updateString("name", name);
  rs.updateRow();
  con.commit();
  ps.close();
 }
 //Delete a record based on id
 public void deleteRecord(int id) throws SQLException{
  con.setAutoCommit(false);
  String sql = "DELETE FROM student WHERE id=?";
  PreparedStatement ps = con.prepareStatement(sql);
  ps.setInt(1, id);
  ps.executeUpdate();
  con.commit();
  ps.close();
 }
}

All those who are new to programming have dealt with data provided either from a file or command line. But you might ne in a situation where you have to store and access data from a persistent storage more easily and which is not a file, e.g. database. It fully depends on vendors and databse administrators how the data will be stored on the disk. It provides a much easier way to access and save data and coders dont have to think about storage. But before we go into saving and accessing data from a database, the first step is to create a connection with the databse from your java program. Here we will be using Oracle 11g Express edition as our database. We will use JDBC to obtain the connection.

JDBC Architecture

     Just as we need a device driver to use a hardwarte from a software, similarly we will need a driver to access the database. There are different types of drivers : type 1, type 2 , type 3 and type 4. For more about this visit Wikipedia JDBC driver . We will be using type 4 JDBC driver which is completely written in Java. If you have already installed Oracle you will find that in

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public final class Provider {
 static{
  try {
   Class.forName("oracle.jdbc.OracleDriver");
   System.out.println("Driver loaded successfully");
  } catch (ClassNotFoundException e) {
   System.out.println("Failed to load driver");
  }
 }
 
 public static Connection getCon(String userid, String pass) throws SQLException{
  return DriverManager.getConnection(
    "jdbc:oracle:thin:@localhost:1521:xe", userid, pass);
 }
}