Introduction to JDBC

Java Database Connectivity(JDBC) is an Application Programming Interface(API) used to connect Java application with Database. JDBC is used to interact with various type of Database such as Oracle, MS Access, My SQL and SQL Server. JDBC can also be defined as the platform-independent interface between a relational database and Java programming. It allows java program to execute SQL statement and retrieve result from database.

The JDBC API consists of classes and methods that are used to perform various operations like: connect, read, write and store data in the database. In this tutorial we will learn the JDBC with examples.

You can get idea of how JDBC connect Java Application to the database by following image.

What's new in JDBC 4.0

Java introduced JDBC 4.0, a new version which is advance specification of JDBC. It provides the following advance features

• Connection Management
• Auto loading of Driver Interface.
• Better exception handling
• Support for large object
• Annotation in SQL query.

JDBC 4.1

The JDBC 4.1 version was introduced with Java SE 7 and includes the following features:

• Allows to use a try-with-resources statement to automatically close resources of type Connection, ResultSet etc.
• Introduced the RowSetFactory interface and the RowSetProvider class to create all types of row sets supported by your JDBC driver.

JDBC 4.2

version is introduced with Java SE 8 and includes the following features:

• The REF_CURSOR support.
• Added an interface java.sql.DriverAction
• Added an interface java.sql.SQLType
• Added an Enum java.sql.JDBCType
• Add Support for large update counts
• Improved the existing interfaces: Driver, DriverManage, DatabaseMetaData.
• Interfaces and classes enhanced for RowSet1.2:

JDBC 4.3

• Added support to Statement for enquoting literals and simple identifiers
• Added Sharding support
• Enhanced Connection to be able to provide hints to the driver that a request, an independent unit of work, is beginning or ending
• Enhanced DatabaseMetaData to determine if Sharding is supported
• Added the method drivers to DriverManager to return a Stream of the currently loaded and available JDBC drivers

JDBC Driver

JDBC Driver is required to establish connection between application and database. It also helps to process SQL requests and generating result. The following are the different types of driver available in JDBC which are used by the application based on the scenario and type of application.

• Type-1 Driver or JDBC-ODBC bridge
• Type-2 Driver or Native API Partly Java Driver
• Type-3 Driver or Network Protocol Driver
• Type-4 Driver or Thin Driver

JDBC-ODBC bridge

Type-1 Driver act as a bridge between JDBC and other database connectivity mechanism(ODBC). This driver converts JDBC calls into ODBC calls and redirects the request to the ODBC driver.

Note: In Java 8, the JDBC-ODBC Bridge has been removed.

Advantage

• Easy to use
• Allow easy connectivity to all database supported by the ODBC Driver.

Disadvantage

• Slow execution time
• Dependent on ODBC Driver.
• Uses Java Native Interface(JNI) to make ODBC call.

Native API Driver

This type of driver make use of Java Native Interface(JNI) call on database specific native client API. These native client API are usually written in C and C++.

Advantage

• faster as compared to Type-1 Driver
• Contains additional features.

Disadvantage

• Requires native library
• Increased cost of Application

Network Protocol Driver

This driver translate the JDBC calls into a database server independent and Middleware server-specific calls. Middleware server further translate JDBC calls into database specific calls.

Advantage

• Does not require any native library to be installed.
• Database Independency.
• Provide facility to switch over from one database to another database.

Disadvantage

• Slow due to increase number of network call.

Thin Driver

This is Driver called Pure Java Driver because. This driver interact directly with database. It does not require any native database library, that is why it is also known as Thin Driver.

Advantage

• Does not require any native library.
• Does not require any Middleware server.
• Better Performance than other driver.

Disadvantage

• Slow due to increase number of network call.

DriverManager class

In Java, the DriverManager class it an interface between the User and the Driver. This class is used to have a watch on driver which is been used for establishing the connection between a database and a driver. The DriverManager class have a list of Driver class which are registered and are called as DriverManager.registerDriver().

S.No.	Method	Description
1	public static void registerDriver(Driver driver)	It is used for Registering the Driver with the Driver Manager.
2	public static void deregisterDriver(Driver driver)	It is used for Deregistering the Driver with the Driver Manager.
3	public static Connection getConnection(String Url)	It is used for establishing a connection with the given URL.
4	public static Connection getConnection(String Url, String username, String password)	It is used for establishing the connection with the given URL, username and password.

Connection interface

In Java, The Connection interface is used for creating the session between the application and the database. This interface contains Statement, PreparedStatement and DatabaseMetaData. The connection objects are used in Statement and the DatabaseMetaData. commit(), rollback() etc.. are some of the methods of Connection Interface.

S.No.	Method	Description
1	public Statement createStatement()	It is used for creating an object of statement for executing the SQL queries.
2	public Statement createStatement(intresultSetType,intresultSetConcurrency)	It is used for creating objects for the ResultSet from the given type and concurrency.
3	public void setAutoCommit(boolean status)	It is used for setting the commit status. By default, it is always true.
4	public void commit()	It is used to save the changes which have been commit or rollback permanent
5	public void rollback()	It is used to delete the changes which have been commit or rollback permanent
6	public void close()	It is used to delete the changes which have been commit or rollback permanent

Statement interface

In Java, The Statement interface is used for executing queries using the database. This interface is a factory of ResultSet. It is used to get the Object of ResultSet. Methods of this interface is given below.

S.No.	Method	Description
1	public ResultSetexecuteQuery(String sql)	It is used for executing the SELECT query
2	public intexecuteUpdate(String sql)	It is used for executing any specified query
3	public boolean execute(String sql)	It is used when multiple results are required.
4	public int[] executeBatch()	It is used for executing the batch of commands.

ResultSet interface

In Java, the ResultSet Interface is used for maintaining the pointer to a row of a table. In starting the pointer is before the first row. The object can be moved forward as well as backward direction using TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE in createStatement(int,int). Methods of this interface is given below.

S.No.	Method	Description
1	public boolean next()	It is used for moving the cursor to the next position from the current position.
2	public boolean previous()	It is used for moving the cursor to the previous position from the current position.
3	public boolean first()	It is used for moving the cursor to the first position from the current position.
4	public booleanlast()	It is used for moving the cursor to the Last position from the current position.
5	public booleanabsolute(int row)	It is used for moving the cursor to the specified position from the current position.
6	public booleanrelative(int row)	It is used for moving the cursor to the relative row number from the current position.
7	public intgetInt(intcolumnIndex)	It is used to get the data from the specified position.
8	public intgetInt(String columnName)	It is used to get the data from the specified column name of the current row.
9	public StringgetString(intcolumnIndex)	It is used to get the data from the specified column name of the current row in form of an integer.
10	public StringgetString(StringcolumnIndex)	It is used to get the data from the specified column name of the current row in form of string.

PreparedStatement interface

In Java, The PreparedStatement interface is a subinterface of Statement. It is mainly used for the parameterized queries. A question mark (?) is passed for the values. The values to this question marks will be set by the PreparedStatement. Methods of this interface is given below.

S.No.	Method	Description
1	public void setInt(intparamIndex, int value)	It is used for setting the integer value for the given parameter index.
2	public void setString(intparamIndex, String value)	It is used for setting the String value for the given parameter index.
3	public void setFloat(intparamIndex, float value)	It is used for setting the Float value for the given parameter index.
4	public void setDouble(intparamIndex, double value)	It is used for setting the Double value for the given parameter index.
5	public intexecuteUpdate()	It is used for executing a query.
6	public ResultSetexecuteQuery()	It is used for executing the select query.

ResultSetMetaData Interface

The ResultSetMetaData interface is used to get metadata from the ResultSet object. Metadata are the data about data. Methods of this interface is given below.

S.No.	Method	Description
1	public intgetColumnCount()throws SQLException	It is used to get the total number of columns.
2	public String getColumnName(int index)throws SQLException	It is used to get the name of the column of a specified column index.
3	public StringgetColumnTypeName(int index)throws SQLException	It is used to get the name of the column of a specified index.
4	public StringgetTableName(int index)throws SQLException	It is used to get the name of a table from the specified column index

---