JDBC (Java Database Connectivity) in Java

JDBC stands for Java Database Connectivity. It is an API for the Java programming language. It allows the client to access the database and also establishes how the client may do so. It can work on a number of operating systems or platforms, like Windows, Mac, etc. It is basically a connection between the database and the application.



If you want to interact with database using c & c++, you need to use database specific libraries in your application directly. Later, if you want to migrate the database, you need to rewrite the entire application using real database specific libraries. This increases the maintenance of the application.

To avoid this, Microsoft has introduced ODBC Driver ODC (open database connectivity). Given below are some points related to the ODBC Driver.

  • With the ODBC Driver, you don’t need to use the application directly, because ODBC itself contain various database vendor specific libraries.

  • Your application now contacts the ODBC Driver instead of using database specific libraries directly. This reduces maintenance issues.

  • But ODBC has a limitation, i.e., the ODBC setup is available only on the Windows OS and neither has it shown good performance.

To avoid these limitations and to provide a uniform method to interact with any database, Sun has provided us with the JDBC API and JDBC Drivers.

jdbc api components diagram

Here are the steps to write a JDBC program:

    Step 1: Load the Driver class
    Step 2: Establish the connection
    Step 3: Create the required statement
    Step 4: Prepare the Required SQL statement
    Step 5: Submit the SQL statement to Database
    Step 6: Process the Results
    Step 7: Release the Resources

Types of JDBC statement:

There are three types of JDBC statements. They are:

  • Statement
  • PreparedStatement
  • CallableStatement
  • 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
Total time taken for executing a single query for all four steps is:

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

  • 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)


  • 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
  • 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.


Example that invokes stored procedure with IN parameters:


Example that invokes stored procedure with IN and OUT parameters:


operations of statement, prepared statement and callable statements in java

  • 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.

  • 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.


Question.

In java.sql package, there is a majority of interface only. How will instances be created in these interfaces?

Answer.


  • It takes the url and checks whether driver class is loaded for this url.

  • If not loaded then it gives this error:NosuitableDriver error

  • If loaded then

  • It creates the object of subclass of connection interfaces related vendor oracleConnection/MySQLconnection class.
    Given below are vendor implementations:


Question.

I have loaded oracle Driver and MySQL, which Database connection will be established when trying to get the connection?

Answer. Depending on the url you are passing as the parameters to getConnection() method, the corresponding database connection will be established.

Sample code:

    class.forName("oracle.jdbc.driver.OracleDriver"); 
    class.forName("com.MySQL.jdbc.Driver"); 
    connection con=DM.getConnection(URL);
      
  • 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
  • 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:


Now, ResultSet is forward only and read only.

  • 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.

next() isAfterLast() isBeforeFirst() isLast() isFirst()
previous() absolute() relative() afterLast() beforeFirst()
getxx() close() first() last()

You can specify the ResultSets as scrollable explicitly as follows:

Now ResultSet is scrollable and read only.


absolute() :

  • It will directly it will go to the that record rs.absolute(4), (which is specified)

relative() :

  • It means it will go to that record which is specified depending on the absolute value.
      Example:
      rs.absolute(4); rs.relative(-3) rs.relative(?)
      getxx() :- To get me column value

Now, ResultSet is scrollable and updatable.


movetoInsertRow() :

  • First set will contain empty row in the Resultset object.

Update() :

  • The only corresponding row of the ResultSet will be updated.
  • Make sure that the table contains a primary key, otherwise the updatable record will not be applicable.

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.