JDBC (Java Database Connectivity) in Java

Statement

• Statement is an interface available in java.sql package


• The statement object can be created using one of the following methods of connection interface:
  • Statement createStatement();
  • Statement createStatement(int,int);
  • Statement createStatement(int,int,int);


• Once the statement object is created, you can call one of the following methods of statement interface:
  • ResultSet executeQuery(String)
  • int executeUpdate(String)
  • boolean execute(String)

1. The executeQuery()method can be used to submit the selected SQL statement to the SQL Engine.
   This method returns the Resultset object which contains the number of records returned by the given selected SQL statement.

2. The executeUpdate() method can be used to submit insert, update, and delete SQL statement to SQL Engine.
   This method returns the integer number which represents the number of record affected by the given SQL statement.

3. The execute() method can be used to submit insert, update, delete SQL statement to SQL Engine.
   This method returns the Boolean value which represents whether the given operation is insert/update/delete (false) OR Fetch (true).
   • Using one statement object, you can submit one or more SQL statements
   • When you submit the SQL statement to SQL Engine using statement object, the SQL statement will be compiled and executed every time.

Java ## DB:

• 5 seconds to reach to DB
• 5 seconds to compile the sql query
• 5 seconds to execute the sql query
• 5 seconds to load data from DB

=5ms+5ms+5ms+5ms
=20ms
1000 Queries =1000*20
=20,000

PreparedStatement

• PreparedStatement is an interface available in java.sql package and it extends the Statement interface.

• The PreparedStatement object can be created using one of the following methods of connection interface:

  • PreparedStatement (String);
  • PreparedStatement (String, int, int);
  • PreparedStatement(String,int,int,int);



• Once the preparedStatement object is created, you can call one of the following methods of preparedStatement interface:

  • ResultSet executeQuery()
  • int executeUpdate()
  • boolean execute()



• Using one preparedStatement object, you can submit only one type of SQL statement.

• When you submit the SQL statement to SQL Engine using preparedStatement object, the SQL statement will be compiled only once the first time, and will be executed every time without compilation.
  For first query, time taken for executing prepared statement query will be:

  1Query = 1+2+3+4
  =5ms+5ms+5ms+5ms
  =20ms

  For the second query onwards, time taken for executing prepared statement query will be:

  1Query = 1+3+4
  5+5+5 = 15ms
  1000-15000 = 5000ms (saving time)

package com.javabykiran.JDBC; 
import java.sql.*;
class JDBCLab {
  public static void main(String[] as) { 
   int sid = Integer.parseInt(as[0]); 
   String sn = as[1];
   String em = as[2];
   long ph = Long.parseLong(as[3]); 
   String ci = as[4];
   double fee = Double.parseDouble(as[5]); 
   Connection con = null; 
   PreparedStatement ps1 = null; 
   PreparedStatement ps2 =  null; 
   ResultSet rs = null;
   try {
      con = DBUtil.getMySQLConnection();
      ps1 = con.prepareStatement("insert into b27students 
            values(?,?,?,?,?,?)");
      ps1.setInt(1, sid); 
      ps1.setString(2, sn); 
      ps1.setString(3, em); 
      ps1.setLong(4, ph); 
      ps1.setString(5, ci); 
      ps1.setDouble(6, fee);
      int x = ps1.executeUpdate(); 
      if (x == 1) {
         System.out.println("Record is inserted");
    } else {
         System.out.println("sorry, Record is not inserted");
    }
    ps2 =con.prepareStatement("select * from jbkStudentns"); 
    rs = ps2.executeQuery();
      while (rs.next()) {
         sid = rs.getInt(1); 
         sn = rs.getString(2); 
         em = rs.getString(3); 
         ph = rs.getLong(4); 
         ci = rs.getString(5);
         fee = rs.getDouble(6);
         System.out.println(" " + sid + "!t" + sn + "!t" + em 
         + "!t" + ph + "!t" + ci + "!t" + fee);
        }
  } catch (Exception e) { 
       e.printStackTrace();
  } finally {
       DBUtil.cleanup(con, ps1, rs); 
       DBUtil.cleanup(con, ps2, rs);
    }
  }
}

• PreparedStatement is also a sub interface of Statement. So, we pass (con,ps1,rs) in the cleanup method of DBUtil class.Where
  • con is Connection object
  • rs is a ResultSet
  • ps1 is a Statement

CallableStatement

• The CallableStatement is an interface available in java.sql package and is an extension of the preparedStatement interface.

• The CallableStatement object can be created using one of the following methods of connection interface:

  • CallableStatement preparecall(String);
  • CallableStatement preparecall(String,int,int,);
  • CallableStatement preparecall(String,int,int,int,);



• Once callableStatement object is created, you can call one of the following methods of callableStatement interface:

  • ResultSet executeQuery()
  • int executeUpdate()
  • boolean execute()



• CallableStatement is mainly used to execute stored procedures running in the database.
  Using one CallableStatement object. You can submit only one call one stored procedure.

• Stored procedure is a set of pre-compiled procedures i.e, when you create the procedure, it will be compiled and stored in the database memory. When you call the procedure it will be executed directly.

/*
Create table hello ( 
  a number(4),
  b number(4), 
  tot number(8)
);
Create or replace procedure p1 (a in number, b in number)
as
  tot number(8); 
  begin
    tot = a+b:
    insert into hello values (a,b,tot); 
  end;
*/

package com.javabykiran.JDBC; 
import java.sql.CallableStatement; 
import java.sql.Connection;
public class JDBCLab5 {
  public static void main(String as[]) { 
    int a = Integer.parseInt(as[0]);
    int b = Integer.parseInt(as[1]);
    Connection con = null; 
    CallableStatement cs = null;
    try {
       con = DBUtil.getOracleConnection(); 
       cs = con.prepareCall("{callp1(?,?)}"); 
       cs.setInt(1, a);
       cs.setInt(2, b);
       cs.executeUpdate();
  } catch (Exception e) { 
       e.printStackTrace();
  } finally { 
       DBUtil.cleanup(con, cs);
    }
  }
}

*/
create or replace procedure p2(a IN number, b IN number, 
tot OUT number, dif OUT number, mul OUT number, div OUT number) 
as
begin 
  tot:=a=b; 
  dif:=a-b;
  mul:=a*b;
  div:=a/b; 
end;
*/

package com.javabykiran.JDBC; 
import java.sql.CallableStatement; 
import java.sql.Connection;
import java.sql.Types; 

public class JDBCLab6 {
  public static void main(String[] args) { 
  int a = Integer.parseInt(args[0]);  
  int b = Integer.parseInt(args[1]); 
  Connection con = null; 
  CallableStatement cs = null;

  try {
      con = DBUtil.getOracleConnection();
      cs = con.prepareCall("{call p2(?,?,?,?,?,?(}");
      cs.setInt(1, a);
      cs.setInt(2, b);
      cs.registerOutParameter(3, Types.INTEGER);
      cs.registerOutParameter(4, 4);
      cs.registerOutParameter(5, Types.INTEGER);
      cs.registerOutParameter(6, 4); 
      cs.executeUpdate();

      int c = cs.getInt(3); 
      int d = cs.getInt(4);
      int e = cs.getInt(5); 
      int f = cs.getInt(6);

      System.out.println(c); 
      System.out.println(d);
      System.out.println(e); 
      System.out.println(f);
      
} catch (Exception e) { 
      e.printStackTrace();
      
} finally { 
      DBUtil.cleanup(con, cs);
  }
 }
}

Database Metadata

• It is an interface available in java.sql package, which provides various useful methods to get information about your database, which you are linked to.

• You can create database metadata object as follows:

  • DatabaseMetaData dbmd=con.getMetaData();

  • By using MetaData we can get any information about Database.

Resultset Metadata

• ResultsetMetaData is an interface available in java.sql package, which gives the information about Resultset object, like number of columns available in Resultset, names of the columns name of the table from where column is fetched etc.

• You can create the ResultsetMetaData object as follows:-

  • ResultsetMetaData rsmd=rs.getMetaData();
  • DatabaseMetaData and ResultsetMetaData are interfaces in java.sql.package



• Oracle has provided subclasses for these interface in oracle.jdbc.driver package called oracleDatabaseMetaData and oracleResultsetMetaData.

• MySQL has provided sub classes for these interfaces in com.mysql.jdbc package called DatabaseMetaData and ResultsetMetaData.

class DriverManager {
  static Connection getconnection(url,un,pw)
    {
    }
}

Class OracleConnection implements java.sql.Connection { 
    Statement createStatement ()
    {
        return new oracleStatement ();
    }
}

Class MySQLConnection implements java.sql.connection {
    Statement createStatement ()
    {
        return new MySQLstatement();
    }
}

    class.forName("oracle.jdbc.driver.OracleDriver"); 
    class.forName("com.MySQL.jdbc.Driver"); 
    connection con=DM.getConnection(URL);
      

ResultSet

• Resultset is a package which is in package java.sql package.

• The Resultset object can be used to store multiple records returned by select statement.

• When Resultset record is created initially result set cursor points to before to the first record.

Types of ResultSet:

Depending on the ResultSet cursor movement, you can divide the ResultSet into 2 types:

• Forward only Resultsets
• Scrollable Resultsets

Forward only ResultSets

• When Resultset is forward, only then you can move the Resultset cursor, that too only in the forward direction.

• You can invoke the following methods on forward only Resultsets:

• next();
• getXX(); [XX can be String or Int or according to data type ]
• close();

next() :

Checks whether next Record is available or not.

If it is available then it :
1. moves the pointer to next Record
2. returns true

If it is not available then :
1. moves the pointer to next Record
2. returns false

By default, ResultSets are forward only, you can specify the Resultsets as forward only explicitly as follows:

Statement stmt = con.createStatement(); 
Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
                 ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery(sql);

Now, ResultSet is forward only and read only.

Scrollable ResultSet

• When Resultset is scrollable, you can move the Resultset cursor both in the forward direction and in the reverse direction a number of many times.

• You can invoke the following methods on scrollable ResultSets.

Statement stmt = con.createStatement();
Statement stmt = con.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE,
                 ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery(sql);

Now ResultSet is scrollable and read only.

Statement stmt = con.createStatement(); 
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                 ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(sql);

Batch update set

Below given is a process to execute statements in JDBC:

• load SQL

• compile SQL

• process SQL

• load Data
  

  With Statement
  • 1SQL= 5+5+5+5=20ms
  
  With Prepared Statements
  • 1SQL=5+0+5+5=15ms
  
  With Batch update
  • 1SQL=0+0+5+5=15ms



• If you want to submit multiple queries to the database one by one, a lot of time will get wasted on the requesting response.

• Instead of submitting SQL statements one by one, we can submit multiple statements all at once to the database as a batch using Batch update concepts.

• To implement the batch update, you can use the following methods of statement interface:
  • void addbatch(sql)
  • int[] executebatch()



• Using Batch updates, we can submit multiple insert, update and delete statements.