Thursday, 14 December 2017

ResultSet Interface in Java-JDBC

In the post Java JDBC Steps to Connect to DB we have already seen a complete example using the interfaces Driver, Connection, Statement and ResultSet provided by the JDBC API.

In this post we’ll see ResultSet interface in detail.

ResultSet interface

ResultSet interface resides in java.sql package and it represents the storage for the data you get by executing a SQL statement that queries the database.

A ResultSet object maintains a cursor pointing at the result data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, it returns false when there are no more rows in the ResultSet object.

Example code – Creating and iterating a ResultSet

ResultSet rs = stmt.executeQuery("Select * from Employee");
   
// Processing Resultset
while(rs.next()){
 System.out.println("id : " + rs.getInt("id") + " Name : " +  rs.getString("name") + " Age : " + rs.getInt("age")); 
}

By default, ResultSet object is not updatable and has a forward moving cursor only. Thus, you can iterate through it only once and only from the first row to the last row. But ResultSet interface provides parameters that can produce ResultSet objects that are scrollable and/or updatable.

Fields for scrollable ResultSet

ResultSet interface has fields that determine whether ResultSet object will be scrollable or not and will it be sensitive to the changes to the data that is represented by ResultSet or not.

  • TYPE_FORWARD_ONLY - The constant indicating the type for a ResultSet object whose cursor may move only forward.
  • TYPE_SCROLL_INSENSITIVE - The constant indicating the type for a ResultSet object that is scrollable but generally not sensitive to changes to the data that underlies the ResultSet. Which means you can move the cursor to an absolute position or relative to the current cursor position. If the data in the DB is changed by another thread/process that change won’t be reflected in the data stored in the ResultSet.
  • TYPE_SCROLL_SENSITIVE - The constant indicating the type for a ResultSet object that is scrollable and generally sensitive to changes to the data that underlies the ResultSet. Which means you can move the cursor to an absolute position or relative to the current cursor position. If the data in the DB is changed by another thread/process that change is reflected in the data stored in the ResultSet.

Fields for updatable ResultSet

  • CONCUR_READ_ONLY - The constant indicating the concurrency mode for a ResultSet object that may NOT be updated.
  • CONCUR_UPDATABLE - The constant indicating the concurrency mode for a ResultSet object that may be updated.

Example code

Let’s see an example with scrollable resultset, DB used here is MySql, schema is netjs and table is Employee.

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

public class JDBCResultSet {

 public static void main(String[] args) {
  try(Connection connection = DriverManager.getConnection(
      "jdbc:mysql://localhost:3306/netjs", "root", "admin")){
   // creating Statement
   Statement stmt = connection.createStatement(
                           ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);  
   
   // Executing Query
   ResultSet rs = stmt.executeQuery("Select * from Employee");
   System.out.println("Displaying all rows");
   // Processing Resultset
   while(rs.next()){
       System.out.println("id : " + rs.getInt("id") + " Name : " 
        + rs.getString("name") + " Age : " + rs.getInt("age")); 
   }
   // moving to 3rd row
   rs.absolute(3);
   System.out.println("Displaying 3rd row");
   System.out.println("id : " + rs.getInt("id") + " Name : " 
                          + rs.getString("name") + " Age : " + rs.getInt("age")); 
  }catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
}

Output

Displaying all rows
id : 5 Name : Tom Age : 35
id : 6 Name : Tim Age : 20
id : 7 Name : John Age : 25
id : 8 Name : Johnny Age : 35
id : 17 Name : Johnny Age : 65
Displaying 3rd row
id : 7 Name : John Age : 25

Getter Methods

You would have noticed in the examples how appropriate data type getter method is used (i.e. getInt, getString) for retrieving column values from the current row. You can retrieve value using either the index number of the column or the name of the column.

In general, using the column index will be more efficient. Columns are numbered from 1. Drawback is, any alteration in the table structure will mean change in the indexes in the Java code.

updater methods

There are also updater methods corresponding to the data types which are used when your ResultSet is updatable. Using updater methods you can update the column values then update the row in the DB. Updater methods are used in conjunction with updateRow and insertRow methods.

Example code

Let’s see an example to update a row and insert a row using ResultSet methods.

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

public class JDBCResultSetUpdate {

 public static void main(String[] args) {
  try(Connection connection = DriverManager.getConnection(
                   "jdbc:mysql://localhost:3306/netjs", "root", "admin")){
   // creating Statement
   Statement stmt = connection.createStatement(
                          ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);  
   
   // Executing Query
   ResultSet rs = stmt.executeQuery("Select * from Employee");
   System.out.println("Displaying all rows");
   // Processing Resultset
   while(rs.next()){
       System.out.println("id : " + rs.getInt("id") + " Name : " 
                               + rs.getString("name") + " Age : " + rs.getInt("age")); 
   }
   // moving to 3rd row
   rs.absolute(3);
   // updating age column for 3rd row
   rs.updateInt("age", 28);
   rs.updateRow();
   System.out.println("Displaying 3rd row");
   System.out.println("id : " + rs.getInt("id") + " Name : " 
                            + rs.getString("name") + " Age : " + rs.getInt("age"));
   
   /*** Inserting row  ***/
   // moves cursor to the insert row
   rs.moveToInsertRow(); 
     
   //rs.updateInt("id",18); //updates the first column using column name
   rs.updateString(2, "Bob"); //updates the second column using column index
   rs.updateInt("age",45);
   rs.insertRow();
   rs.moveToCurrentRow();
   
  }catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }

 }

}

Other fields of ResultSet interface

Some of the fields are already mentioned with examples, ResultSet has some other fields which are as follows -

  • CLOSE_CURSORS_AT_COMMIT - This constant indicates that open ResultSet will be closed when the current transaction is commited.
  • HOLD_CURSORS_OVER_COMMIT - This constant indicates that open ResultSet will remain open when the current transaction is commited.
  • FETCH_FORWARD - The constant indicating that the rows in a result set will be processed in a forward direction; first-to-last.
  • FETCH_REVERSE - The constant indicating that the rows in a result set will be processed in a reverse direction; last-to-first.
  • FETCH_UNKNOWN - The constant indicating that the order in which rows in a result set will be processed is unknown.

Methods of the ResultSet

Most of the often used methods of the ResultSet are already covered with the examples. Some of the other methods which are used for moving the cursor are as follows -

  • afterLast() - Moves the cursor to the end of this ResultSet object, just after the last row.
  • beforeFirst() - Moves the cursor to the front of this ResultSet object, just before the first row.
  • first() - Moves the cursor to the first row in this ResultSet object.
  • last() - Moves the cursor to the last row in this ResultSet object.
  • moveToCurrentRow() - Moves the cursor to the remembered cursor position, usually the current row.
  • moveToInsertRow() - Moves the cursor to the insert row.
  • next() - Moves the cursor froward one row from its current position.
  • previous() - Moves the cursor to the previous row in this ResultSet object.
  • relative(int rows) - Moves the cursor a relative number of rows, either positive or negative.

Reference : https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html

That's all for this topic ResultSet Interface in Java-JDBC. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. Connection Interface in Java-JDBC
  2. Statement Interface in Java-JDBC
  3. Java JDBC overview - JDBC Tutorial
  4. Types of JDBC Drivers
  5. Data access in Spring framework

You may also like -

>>>Go to Java advance topics page

Statement Interface in Java-JDBC

In the post Java JDBC Steps to Connect to DB we have already seen a complete example using the interfaces Driver, Connection, Statement and ResultSet provided by the JDBC API.

In this post we’ll see Statement interface in detail.

Statement interface

Statement interface resides in java.sql package and it is used to execute a static SQL statement and returning the result of the executed query.

Statement interface has two sub-interfaces CallableStatement and PreparedStatement.

PreparedStatement – PreparedStatement object stores the SQL statement in its pre-compiled state. That way it can efficiently execute the same SQL statement multiple times with different parameters.

CallableStatement - This interface is used to execute SQL stored procedures.

You can get a Statement object by calling the Connection.createStatement() method on the Connection object.

Frequently used methods of the Statement

Mostly you will use the execute methods of the Statement interface to execute queries.

  1. boolean execute(String sql) - Executes the given SQL statement (it can be any kind of SQL query), which may return multiple results.
    Returns a boolean which is true if the first result is a ResultSet object; false if it is an update count or there are no results.
  2. ResultSet executeQuery(String sql) - Executes the given SQL statement, which returns a single ResultSet object. If you want to execute a Select SQL query which returns results you should use this method.
  3. int executeUpdate(String sql) - Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.
    Returns an int denoting either the row count for the rows that are inserted, deleted, updated or returns 0 if nothing is returned.
    Note:This method cannot be called on a PreparedStatement or CallableStatement.
  4. int[] executeBatch() - Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.

Example with execute(), executeUpdate and executeQuery methods

Let’s see an example where SQL statements are executed using execute(), executeUpdate and executeQuery methods. In the example -

Using execute() method a SQL statement is execute and then the boolean value is checked.

Using executeUpdate() method insert, update and delete statements are executed and row count of the affected rows is displayed.

Using executeQuery() method select statement is executed and the returned ResultSet is processed.

Java code

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

public class JDBCStmt {
 
 public static void main(String[] args) {
        Connection connection = null;
        try {
            // Loading driver
            Class.forName("com.mysql.jdbc.Driver");
   
            // Creating connection
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs", 
                         "root", "admin");
  
            // creating Statement
            Statement stmt = connection.createStatement();  
            
            /** execute method **/
            boolean flag = stmt.execute("Update Employee set age = 40 where id in (5, 6)");
            if(flag == false){
             System.out.println("Updated rows " + stmt.getUpdateCount() );
            }
            
            /** executeUpdate method **/
            // Insert
            int count = stmt.executeUpdate("Insert into employee(name, age) values('Kim', 23)");
            System.out.println("Rows Inserted " + count);
            
            // update
            count = stmt.executeUpdate("Update Employee set age = 35 where id = 17");
            System.out.println("Rows Updated " + count);
            
            //delete
            count = stmt.executeUpdate("Delete from Employee where id = 5");
            System.out.println("Rows Deleted " + count);
            
            /** executeQuery method **/
            // Executing Query
            ResultSet rs = stmt.executeQuery("Select * from Employee");
   
            // Processing Resultset
            while(rs.next()){
               System.out.println("id : " + rs.getInt("id") + " Name : " 
                + rs.getString("name") + " Age : " + rs.getInt("age")); 
            }
    
       } catch (ClassNotFoundException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
       } catch (SQLException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       }finally{
          if(connection != null){
           //closing connection 
           try {
            connection.close();
           } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
           }
          } // if condition
       }// finally

    }
}

Reference : https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html

That's all for this topic Statement Interface in Java-JDBC. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. Connection Interface in Java-JDBC
  2. Java JDBC overview - JDBC Tutorial
  3. Types of JDBC Drivers
  4. Data access in Spring framework

You may also like -

>>>Go to Java advance topics page

Tuesday, 12 December 2017

Connection Interface in Java-JDBC

In the post Java JDBC Steps to Connect to DB we have already seen a complete example using the interfaces Driver, Connection, Statement and ResultSet provided by the JDBC API.

In this post we’ll see Connection interface in detail.

Connection interface

Connection interface resides in java.sql package and it represents a session with a specific database you are connecting to. SQL statements that you want to execute, results that are returned all that happens with in the context of a connection.

You can get a Connection object by using the getConnection() method of the DriverManager class.

Using Connection class object -

  • You can get the object of Statement
  • You can get the information about the database it is connecting to
  • Connection also provides method for transaction management

Fields in the Connection interface

Connection interface provides a set of fields for specifying transaction isolation level -

  • TRANSACTION_NONE - A constant indicating that transactions are not supported.
  • TRANSACTION_READ_COMMITTED - A constant indicating that dirty reads are prevented; non-repeatable reads and phantom reads can occur.
  • TRANSACTION_READ_UNCOMMITTED - A constant indicating that dirty reads, non-repeatable reads and phantom reads can occur.
  • TRANSACTION_REPEATABLE_READ - A constant indicating that dirty reads and non-repeatable reads are prevented; phantom reads can occur.
  • TRANSACTION_SERIALIZABLE - A constant indicating that dirty reads, non-repeatable reads and phantom reads are prevented.

Frequently used methods of the Connection

Some of the frequently used methods of the Connection are as follows -

For creating statement

  • createStatement() - Creates a Statement object for sending SQL statements to the database.
  • prepareStatement(String sql) - Creates a PreparedStatement object for sending parameterized SQL statements to the database.
  • prepareCall(String sql) - Creates a CallableStatement object for calling database stored procedures.
There are also overloaded variant of these methods where you can specify the type of ResultSet and its concurrency level.

For getting information about the DB

  • getMetaData() - Returns a DatabaseMetaData object containing metadata about the connected database.

For transaction management

  • setAutoCommit(boolean autoCommit) - Sets this connection's commit mode to true or false.
  • setTransactionIsolation(int level) - Attempts to changes the transaction isolation level for this Connection object to the one given.
  • rollback() - Undoes all changes made in the current transaction and releases any database locks currently held by this Connection object.
  • commit() - Makes all changes made since the previous commit/rollback permanent and releases any database locks currently held by this Connection object.

Reference : https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html

That's all for this topic Connection Interface in Java-JDBC. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. Java JDBC overview - JDBC Tutorial
  2. Types of JDBC Drivers
  3. Data access in Spring framework

You may also like -

>>>Go to Java advance topics page

Saturday, 9 December 2017

Java JDBC Steps to Connect to DB

JDBC API provides a set of interfaces and classes for connecting to DB, creating SQL statement, executing created SQL statement in database, returning the results and processing that ResultSet.

Interfaces provided by JDBC for these steps are Driver (That is the interface implemented by the database vendors to provide a JDBC driver for specific databases), Connection, Statement and ResultSet.

The steps for connecting to DB and fetching results using JDBC can be summarized as follows -

  • Loading driver
  • Creating connection to DB
  • Creating Statement
  • Executing Query
  • Processing ResultSet
  • Closing connection

Loading driver

First thing you need to do is to load a JDBC driver for the DB you are connecting to. You can use forName() method provided by class Class to do that.

General form using Class.forName()

class.forName(“JDBC Driver Class”);

Using registerDriver() method

You can also use registerDriver() method provided by DriverManager class to load the appropriate driver. Note that it is a static method.

General form using registerDriver() method

DriverManager.registerDriver(Driver class object);

Driver classes for some of the databases are as follows -

  • MySql – com.mysql.jdbc.Driver (You need to download MySQL Connector/J jar mysql-connector-java-5.1.39.jar (Please check for the latest version) which will have the JDBC driver).
  • Oracle – oracle.jdbc.driver.OracleDriver (You need to download ojdbc8.jar or higher version for Java 8 and JDBC 4.2).
  • DB2 – com.ibm.db2.jcc.DB2Driver (You need to download db2jcc.jar for the DB2 JDBC driver).

Example

For loading Oracle driver using Class.forName() method -

Class.forName(“oracle.jdbc.driver.OracleDriver”);

Since the driver class is not explicitly used in the program that’s why JVM won’t load it automatically. JVM will automatically load the classes that are used in the program. That is the reason you need to load driver class using class.forName() that way you explicitly tell JVM to load this driver class.

For loading MySql driver using resgisterDriver method -

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

If you are using JDBC 4.x (Version Java 6 or higher) then actually you don’t need to explicitly load the JDBC driver. As Automatic loading of JDBC drivers is supported from JDBC 4 you just need to have the appropriate jar in the class path. So loading driver step is optional from Java 6 onwards make sure that automatic loading is supported by the driver you are using.

Creating connection

Once you have registered the driver, second step is to open a connection to the DB. For that you can use the static method getConnection() of the java.sql.DriverManager class.

General form of getConnection method

DriverManager.getConnection(DB_URL, DBuser, password)

URL Pattern for some of the databases

  • Oracle – jdbc:oracle:<drivertype>:@<database>

    As example - Connecting user scott with password tiger to a database with SID orcl through port 1521 of host myhost, using the Thin driver.

    Connection connection = DriverManager.getConnection
         ("jdbc:oracle:thin:@myhost:1521:orcl", "scott", "tiger");
    
  • MySQL – jdbc:mysql://hostname:portnumber/dbName

    As example - Connecting user root with password admin to a database test through port 3306 of host localhost.

    Connection connection = DriverManager.getConnection
         ("jdbc:mysql://localhost:3306/test", "root", "admin");
    
  • DB2 – jdbc:db2://hostname:portnumber/dbName

    As example - Connecting user dbadmin with password dbadmin to a database mydb through port 5021 of host myhost.

    Connection connection = DriverManager.getConnection
         ("jdbc:db2://myhost:5021/mydb","dbadmin","dbadmin");
    

Creating Statement

Once Connection object is created that can be used to create an object of Statement. Statement object is needed for specifying the SQL statement that has to be executed by the DB.

Statement statement = connection.createStatement();

Executing Query

To execute a query you need to call execute method of the Statement class.

You can call executeUpdate(String sql) method for INSERT, DELETE, UPDATE or DDL (Data Definition Language) SQL statements. This method returns an integer representing the number of rows affected by the SQL statement so you will know how many rows are inserted, deleted or updated.

You can call executeQuery(String SQL) method for SELECT sql queries. This method returns a ResultSet.

As example -

  1. To create a table employee
    statement.executeUpdate(“CREATE TABLE employee (id int(11), name varchar(35), age int(11))”);
    
  2. To get data for all employees
    ResultSet rs = statement.executeQuery("Select * from Employee"); 
    

Processing ResultSet

Once the query is executed and you have the ResultSet you access the data in a ResultSet object through a cursor. This cursor is a pointer that points to one row of data in the ResultSet object. Initially, the cursor is positioned before the first row. You can use next method to move to the next row of the ResultSet. There are various getter methods based on data type to get the value of the current row.

As example -

If you want to iterate the ResultSet returned from the above query for getting all the employees.

while(rs.next()){
 System.out.println("id : " + rs.getInt("id") + " Name : " + rs.getString("name") + " Age : " + rs.getInt("age"));
}

In the example getter method are using the column labels to retrieve the values you can also use the column indexes to get the values, index starts from 1.

while(rs.next()){
 System.out.println("id : " + rs.getInt(1) + " Name : " + rs.getString(2) + " Age : " + rs.getInt(3));
}

Closing the connection

Once you have the processed the ResultSet you can close the connection.

connection.close();

It’s better to close an opened connection in a finally block. That ensures that the connection is closed even if there is an exception in the code.

Putting everything together

Let’s put all these steps together in a Java program. DB used here is MySql, schema is netjs and table is Employee.

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

public class JDBCCon {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            // Loading driver
            Class.forName("com.mysql.jdbc.Driver");
            // Another way
            //DriverManager.registerDriver(new com.mysql.jdbc.Driver());
   
            // Creating connection
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs", 
                         "root", "admin");
  
            // creating Statement
            Statement stmt = connection.createStatement();  
   
            // Executing Query
            ResultSet rs = stmt.executeQuery("Select * from Employee");
   
            // Processing Resultset
            while(rs.next()){
               System.out.println("id : " + rs.getInt("id") + " Name : " 
                + rs.getString("name") + " Age : " + rs.getInt("age")); 
            }
    
       } catch (ClassNotFoundException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
       } catch (SQLException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       }finally{
          if(connection != null){
             //closing connection 
             try {
               connection.close();
             } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
             }
          }
       }

    }

}

You can put DB credential information in a properties file and read it from there. Refer How to read Properties file in Java to see how to read properties file in Java.

Using try-with-resources

If you are using Java 7 or above, you can use a try-with-resources statement to automatically close Connection, Statement, and ResultSet objects, regardless of whether an SQLException has been thrown.

Example code

If we modify the above code to include try-with-resources then we can get rid of finally block used to close the connection.

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

public class JDBCCon {

    public static void main(String[] args) {
       try(Connection connection = DriverManager.getConnection(
           "jdbc:mysql://localhost:3306/netjs", "root", "admin"))
       {
          // creating Statement
          Statement stmt = connection.createStatement();  
   
          // Executing Query
          ResultSet rs = stmt.executeQuery("Select * from Employee");
   
          // Processing Resultset
          while(rs.next()){
             System.out.println("id : " + rs.getInt("id") + " Name : " 
                   + rs.getString("name") + " Age : " + rs.getInt("age")); 
          }
       }catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
       }
    }
}

Here note that Class.forName("com.mysql.jdbc.Driver"); statement is also excluded as that is also optional, from JDBC 4.0 drivers which are in the class path are automatically loaded.

That's all for this topic Java JDBC Steps to Connect to DB. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. Java JDBC overview - JDBC Tutorial
  2. Types of JDBC Drivers
  3. Insert\Update using JDBCTemplate in Spring framework

You may also like -

>>>Go to Java advance topics page

Java JDBC overview - JDBC Tutorial

JDBC is the Java API for developing Java applications that access relational databases.

Why use JDBC

JDBC provides developers with a uniform interface to connect with different relational databases like Oracle, MySQL, DB2, Access etc.

JDBC provides a set of interfaces and classes that standardize the interaction with different databases and abstracts you as a developer with the inner working of the proprietary databases. You just need to know the standard JDBC steps to connect to database, query it in order to fetch results or update DB. Note here that the SQL may differ according to the DB used.

JDBC Driver

In Order to connect to database JDBC uses JDBC driver. Since JDBC driver acts as a connector between JDBC and proprietary databases JDBC drivers are DB specific and generally provided by the DB vendor itself.

As example – In order to connect to connect to MySql DB you will need a MySql JDBC connector driver which is bundled in the mysql-connector-javaXXX.jar.

The interaction of JDBC with the database using JDBC driver can be pictorially represented as follows -

JDBC Drivers

Packages in JDBC API

The JDBC API is comprised of two packages:

  • java.sql - Referred to as the JDBC core API
  • javax.sql - Referred to as the JDBC Optional Package API
You automatically get both packages when you download the Java Platform Standard Edition (Java SE).

Changes in JDBC 4.x

The current version of JDBC which comes bundled with Java 8 is JDBC 4.2. There are some noticeable changes in the 4.x versions like -

  1. Addition of the java.sql.SQLType Interface
  2. Addition of the java.sql.JDBCType Enum – Using SQLType interface and JDBCType Enum you can identify the generic SQL types like CLOB, REF_CURSOR, TINYINT, VARCHAR etc.
  3. You can use try-with-resources statement to automatically close resources of type Connection, ResultSet, and Statement.
  4. Automatic loading of JDBC drivers on the class path.

Steps for connecting to DB

JDBC API provides a set of interfaces and classes for connecting to DB, creating SQL statement, executing created SQL statement in database, returning the results and processing that ResultSet.

These steps can be summarized as follows -

  • Loading driver
  • Creating connection to DB
  • Creating Statement
  • Executing Query
  • Processing ResultSet
  • Closing connection

Refer Java JDBC Steps to Connect to DB to see these steps in details and a JDBC example Java program.

A pictorial representation of these steps can be represented as follows.

JDBC steps

That's all for this topic Java JDBC overview - JDBC Tutorial. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. Types of JDBC Drivers
  2. Java JDBC Steps to Connect to DB
  3. Data access in Spring framework
  4. Select query using JDBCTemplate in Spring framework

You may also like -

>>>Go to Java advance topics page

Types of JDBC Drivers

JDBC driver is an implementation of the Driver interface in the java.sql package.

Every database vendor should provide a JDBC driver for their DBMS and each JDBC driver should supply a class that implements the Driver interface.

There are many possible implementations of JDBC driver, these implementations can be categorized into four types.

  • Type 1 driver
  • Type 2 driver
  • Type 3 driver
  • Type 4 driver

Type 1 Driver

Type 1 driver is a type of JDBC driver that implements the JDBC API as a mapping to another DB access API.

As example - The JDBC-ODBC Bridge driver that maps JDBC API requests to ODBC requests. Here note that ODBC (Open Database Connectivity) is another standard API for accessing databases which is developed by Microsoft.

Type 1 drivers type are generally dependent on a native library, which limits their portability.

DB access through Type 1 driver

Type 2 Driver

Type 2 drivers are written partly in Java and partly in native code. Native client library specific to the data source to which connection is made is used by Type 2 JDBC drivers.

As example - Oracle's OCI (Oracle Call Interface) client-side driver is an example of a Type 2 driver.

Since native code is used by Type 2 drivers, their portability is limited.

DB access through Type 2 driver

Type 3 Driver

Type 3 driver has a client that is written in Java and that connects to a middleware server using a database independent protocol. Middleware server in turn communicates with the data source.

The drawback of Type 3 driver is that the middleware server has to be DB specific.

There are two stages in Type 3 driver. First, where Java application connects to Type 3 driver which in turn connects to middleware server. Its the sever which translates the request to DB specific request making the whole process slower.

DB access through Type 3 driver

Type 4 Driver

Type 4 drivers are written completely in Java so no native code library or middleware server is needed, that is why type 4 drivers are also known as thin drivers. Type 4 drivers themselves implement the network protocol for a specific data source.

DB access through Type 4 driver

Reference : https://docs.oracle.com/javase/tutorial/jdbc/basics/gettingstarted.html

That's all for this topic Types of JDBC Drivers. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. Java JDBC overview - JDBC Tutorial
  2. Java JDBC Steps to Connect to DB
  3. Data access in Spring framework

You may also like -

>>>Go to Java advance topics page

Tuesday, 5 December 2017

Livelock in Java Multi-Threading

Livelock in Java multi-threading is a situation where two or more threads are acting on a response to an action of each other and not able to make any progress because of that.

How livelock is different from deadlock in Java multi-threading is that in case of deadlock threads get blocked whereas in case of livelock threads are active but busy responding to each other thus not making any progress.

Example of livelock

Let’s see an example of livelock in threads to understand it. A classic example to explain livelock is a multi-threaded application to deposit and withdraw money from accounts with a provision to rollback the transaction if transaction doesn’t go through.

Java code

import java.util.concurrent.locks.ReentrantLock;

public class LivelockDemo {

    public static void main(String[] args) {
        Account acct1 = new Account(101, 5000);
        Account acct2 = new Account(102, 7000);
        // Creating two threads
        Thread thread1 = new Thread(new Operation(acct1, acct2, 100));
        Thread thread2 = new Thread(new Operation(acct2, acct1, 100));

        thread1.start();
        thread2.start();
    }

}

class Account{
    int acctNum;
    int balance;
    ReentrantLock lock = new ReentrantLock();
    Account(int acctNum, int balance){
        this.acctNum = acctNum;
        this.balance = balance;
    }
    
    /**
     * Method for depositing amount
     * @param amount
     * @return
     */
    public boolean deposit(int amount){
        System.out.println("In deposit method");
        if(this.lock.tryLock()){
            try {
                // Simulating some delay
                Thread.sleep(500);
            } catch (InterruptedException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            System.out.println("deposit in " + this.acctNum + " for " 
              + Thread.currentThread().getName());
            this.balance = balance + amount;
            return true;
        }
        return false;
    }
    
    /**
     * Method for withdrawing amount
     * @param amount
     * @return
     */
    public boolean withdraw(int amount){
        System.out.println("In withdraw method");
        if(this.lock.tryLock()){
            try {
                // Simulating some delay
                Thread.sleep(500);
            } catch (InterruptedException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            System.out.println("Withdrawn from " + this.acctNum + " for " 
              + Thread.currentThread().getName());
            this.balance = balance - amount;
            return true;
        }
        return false;
    }
    
    public boolean transact(Account targetAcct, int amount){
        //System.out.println("In transact method " + targetAcct);
        boolean flag = false;
        // If you can withdraw from the source account and
        // deposit it into target account then only return true        
        if(this.withdraw(amount) && targetAcct.deposit(amount)){
                flag = true;
        }else{
            // Rollback and deposit the withdrawn amount back in source account    
            System.out.println("Failed to deposit in " + targetAcct.acctNum + 
              " depositing back in account " + this.acctNum);
            this.deposit(amount);
            
        }
        return flag;
    }
}

class Operation implements Runnable{
    Account sourceAccount;
    Account targetAccount;
    int amount;
    Operation(Account sourceAccount, Account targetAccount, int amount){
        this.sourceAccount = sourceAccount;
        this.targetAccount = targetAccount;
        this.amount = amount;
    }

    @Override
    public void run() {
        
        sourceAccount.transact(targetAccount, amount);
    }
    
}

Output

In withdraw method
In withdraw method
Withdrawn from 102 for Thread-1
In deposit method
Withdrawn from 101 for Thread-0
In deposit method
Failed to deposit in 101 depositing back in account 102
In deposit method
Failed to deposit in 102 depositing back in account 101
In deposit method
deposit in 102 for Thread-1
deposit in 101 for Thread-0

Here you can see that you have two threads where one is withdrawing amount from Account 101 and depositing it in 102 and the other thread is withdrawing amount from Account 102 and depositing it in 101. Threads are failing to complete the transaction because of not able to get the lock and rolling back the transaction.

In the above example transaction is not run in the loop so the threads make just one attempt, you can change the run method as follows and see how both threads keep trying and rolling back the transactions without making any real progress.

public void run() {
 while(!sourceAccount.transact(targetAccount, amount)){
  continue;
 } 
}

If you run your code with these changes in run() method you will have to terminate your program to come out of the loop.

Output

In withdraw method
In withdraw method
Withdrawn from 101 for Thread-0
Withdrawn from 102 for Thread-1
In deposit method
In deposit method
Failed to deposit in 101 depositing back in account 102
In deposit method
Failed to deposit in 102 depositing back in account 101
In deposit method
deposit in 102 for Thread-1
In withdraw method
deposit in 101 for Thread-0
In withdraw method
Withdrawn from 101 for Thread-0
In deposit method
Withdrawn from 102 for Thread-1
In deposit method
Failed to deposit in 102 depositing back in account 101
In deposit method
Failed to deposit in 101 depositing back in account 102
In deposit method
deposit in 102 for Thread-1
In withdraw method
deposit in 101 for Thread-0
In withdraw method
Withdrawn from 102 for Thread-1
In deposit method
Withdrawn from 101 for Thread-0
In deposit method
Failed to deposit in 101 depositing back in account 102
In deposit method
Failed to deposit in 102 depositing back in account 101
In deposit method
deposit in 101 for Thread-0
In withdraw method
deposit in 102 for Thread-1
In withdraw method
Withdrawn from 101 for Thread-0
In deposit method
Withdrawn from 102 for Thread-1
In deposit method

That's all for this topic Livelock in Java Multi-Threading. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. Thread Starvation in Java Multi-Threading
  2. Why wait(), notify() and notifyAll() methods are in Object class
  3. Race condition in Java multi-threading
  4. ReentrantLock in Java concurrency
  5. Java Multi-threading interview questions

You may also like -

>>>Go to Java advance topics page