Getting started with JDBC

After establishing connection with the database, JDBC contains interface like Callable Statement, Prepared Statement and Statement which has properties and method that will allow interaction with PL/SQL and SQL commands to receive and send data into the database.

The Statement Objects: The statement object involves two main parts Creating Statement Object and Closing Statement Object.

Statement stmtmnt = null;
try {   
 stmtmnt = conn1.createStatement( );   
 . . .
}catch (SQLException e) {   
 . . .
}
finally {   
 . . .
}

After Creating Statement Object the SQL Statement it can be executed one of three execution methods: 

• Execute boolean (String SQL): If result set object is retrieved then True Boolean value is returned or else False.
• int executeUpdate(String SQL) : The number of rows affected by the execution of SQL statement is returned. UPDATE, INSERT or DELETE statement are the example of using this.
• executeQuery ResultSet (String SQL) : ResultSet Object is returned. The SELECT statement results returned with it.

After the result set is returned in any of the three execution method statement object must be closed. The Close will ensure cleanup of the object. Below code explains it:

Statement stmtmnt = null;
try {   
 stmtmnt = conn1.createStatement( );   
 . . .
}catch (SQLException e) {   
 . . .
}
finally {   
 stmtmnt.close();
}

The Prepared Statement Objects: The Prepared Statement provides additional functionality like argument supplying dynamically. The ? allow parameter passing into the statement.The methods execute(), executeQuery() and  executeUpdate() works well with Prepared Statement Objects.

 
PreparedStatement pstmtmnt = null;
try {   
 String SQL = "Update USER SET user_age = ? WHERE user_id = ?";   
 pstmtmnt = conn.prepareStatement(SQL);   
 . . .
}
catch (SQLException e) {   
 . . .
}
finally {   
 . . .
}

After the result set is returned the Prepared Statement object should be closed. 

PreparedStatement pstmtmnt = null;
try {
 String SQL = "Update USER SET user_age = ? WHERE user_id = ?";
 pstmtmnt = conn.prepareStatement(SQL);
 . . .
}
catch (SQLException e) {
 . . .
}
finally {
 pstmtmnt.close();
}

The Callable Statement Objects: This can be used while calling Stored Procedure from the database.

CREATE OR REPLACE PROCEDURE get_UserName    
(USER_ID IN NUMBER, USER_FIRST OUT VARCHAR) AS
BEGIN  
  SELECT User_first
  INTO USER_FIRST  
  FROM USER  
  WHERE USER_ID = USER_ID;
END;

The CallableStatement object uses three type of parameter: OUT, IN and INOUT. 

The Connection.prepareCall() method is used for initiating it. 

CallableStatement cstmtmnt = null;
try {
 String SQL = "{call get_UserName (?, ?)}";
 cstmtmnt = conn1.prepareCall (SQL);
 . . .
}
catch (SQLException e) {
 . . .
}
finally {
 . . .
}

After the execution method statement object must be closed. The Close will ensure cleanup of the object. Below code explains it:

CallableStatement cstmtmnt = null;
try {
 String SQL = "{call get_UserName (?, ?)}";
 cstmtmnt = conn.prepareCall (SQL);
 . . .
}
catch (SQLException e) {
 . . .
}
finally {
 cstmtmnt.close();
}