JDBC (JAVA DATABASE CONNECTIVITY)

stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.The JDBC library includes APIs for the  following tasks that are commonly associated with database usage.

·                     Making a connection to a database.

·                     Creating SQL or MySQL statements.

·                     Executing SQL or MySQL queries in the database.

·                     Viewing & Modifying the resulting records.

 

Applications of JDBC

            All of the different executable files that are created by java are able to use a JDBC driver to access a database, and take advantage of the stored data.

JDBC Architecture

JDBC Architecture consists of two layers

·                     JDBC API: This provides the application-to-JDBC Manager connection.

·                     JDBC Driver API: This supports the JDBC Manager-to-Driver Connection.

 

The JDBC API uses a driver manager and database-specific drivers to provide transparent connectivity to heterogeneous databases. The JDBC driver manager ensures that the correct driver is used to access each data source. The driver manager is capable of supporting multiple concurrent drivers connected to multiple heterogeneous databases.

JDBC Components

JDBC API

Using JDBC API, front end java applications can execute query and fetch data from connected database. JDBC API can also connect with multiple application with same database or same application with multiple Databases which can be reside in different computers(distributed environment).

JDBC Driver Manager

' Driver Manager' of JDBC interface defines 'objects' which is used to connect java application with 'JDBC Driver'.' Driver Manager' manages the 'JDBC Driver' loaded in client's system. 'Driver Manager' loads the most appropriate driver among all the Drivers for creating a connection.

 

classes and interfaces supporting connectivity between interface and database

·                     DriverManager

·                     Driver

·                     Connection

·                     Statement

·                     PreparedStatement

·                     Callable Statement

·                     ResultSet

·                     SQLException

 

Driver Manager

"Driver Manger" Manages all the Drivers found in JDBC environment, load the most appropriate driver for connectivity.

Driver

            This interface handles the communications with the database server. we will interact directly with Driver objects very rarely. Instead, we use DriverManager objects, which manages objects of this type. It also abstracts the details associated with working with Driver objects.

Connection

·                     This interface with all methods for contacting a database. 

·                     Connection class creates objects which represents connection and it's object also helps in creating object of Statement, PreparedStatement and CallableStatement classes

Statement

Statement object is used to execute query and also store it's value to "Resultset" object.

 

Prepared Statement

It can be used in place of "Statement", PreparedStatement's performance is high as compared to "Statement" class, represents a precompiled SQL statement .

 

Callable Statement

Callable statement support stored procedure of RDBMS' ,using it's object you can execute stored procedure of database application

 

ResultSet

Resultset object is used to store the result retrieved from database using "Statement" or "PreparedStatement" , etc

 

SQLException

SqlException class is used to represent error or warning during access from database or during connectivity.

Java Database Connectivity 

Steps

1. Register the driver class

1. The forName() method of Class class is used to register the driver class. This method is used to dynamically load the driver class.
2. Syntax: public static void forName(String className)throws ClassNotFoundException

Example to register the OracleDriver class:

                        Class.forName("oracle.jdbc.driver.OracleDriver");

                                    Here, Java program is loading oracle driver to establish database connection.

2. Create the connection object

1. The getConnection() method of DriverManager class is used to establish connection with the database
2. Syntax:

·         public static Connection getConnection(String url)throws SQLException

·         public static Connection getConnection(String url,String name,String password)throws SQLException

Example with Oracle database

            Connection con=DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe","system","password");

3. Create the Statement object

1. The createStatement() method of Connection interface is used to create statement. The object of statement is responsible to execute queries with the database
2. Syntax:

·         public Statement createStatement()throws SQLException

Example:

            Statement stmt=con.createStatement();

 

4 Execute the query

1. The executeQuery() method of Statement interface is used to execute queries to the database. This method returns the object of ResultSet that can be used to get all the records of a table
2. Syntax:

public ResultSet executeQuery(String sql)throws SQLException 

Example:

                        ResultSet rs=stmt.executeQuery("select * from emp"); 

                        while(rs.next())

                        { 

                        System.out.println(rs.getInt(1)+" "+rs.getString(2));    

                        } 

 

4.                  Close the connection object

a.      By closing connection object statement and ResultSet will be closed automatically. The close() method of Connection interface is used to close the connection.

b.      Syntax:

  public void close()throws SQLException

Example:

                        con.close();

 

 

Update the Database

There are two types of updates we can perform on a database:

·                     Update record values and Delete records

·                     The executeUpdate() method is used for both of these types of updates.

Updating Records

Statement statement = connection.createStatement();

String    sql       = "update people set name='John' where id=123";

int rowsAffected    = statement.executeUpdate(sql);

Deleting Records

Statement statement = connection.createStatement();

String    sql       = "delete from people where id=123";

int rowsAffected    = statement.executeUpdate(sql);

 

Statement interface

The Statement interface provides methods to execute queries with the database.Commonly used methods of Statement interface are:

 

public ResultSet executeQuery(String sql):-

                                     is used to execute SELECT query. It returns the object of ResultSet.

 

public int executeUpdate(String sql):-

                                      is used to execute specified query, it may be create, drop, insert, update, delete etc.

 

public boolean execute(String sql):

                                     is used to execute queries that may return multiple results.

 

public int[] executeBatch():-

                                    is used to execute batch of commands.

 

ResultSet interface

The object of ResultSet maintains a cursor pointing to a row of a table. Initially, cursor points to before the first row.

Commonly used methods of ResultSet interface

public boolean next():      

is used to move the cursor to the one row next from the current position.

public boolean previous():

is used to move the cursor to the one row previous from the current position

public boolean first():      

is used to move the cursor to the first row in result set object.

public boolean last():

      is used to move the cursor to the last row in result set object.

public boolean absolute(int row):

is used to move the cursor to the specified row number in the ResultSet object.

public boolean relative(int row):

is used to move the cursor to the relative row number in the ResultSet object, it may be positive or negative.

public int getInt(int columnIndex):

is used to return the data of specified column index of the current row as int.

public int getInt(String columnName):

is used to return the data of specified column name of the current row as int.

public String getString(int columnIndex):

is used to return the data of specified column index of the current row as String.

public String getString(String columnName):    

is used to return the data of specified column name of the current row as String.

 

Java ResultSetMetaData Interface

            The metadata means data about data i.e. we can get further information from the data. If you have to get metadata of a table like total number of column, column name, column type etc. , ResultSetMetaData interface is useful because it provides methods to get metadata from the ResultSet object.

Commonly used methods of ResultSetMetaData interface

public int getColumnCount()throws SQLException:   

it returns the total number of columns in the ResultSet object.

public String getColumnName(int index)throws SQLException:       

it returns the column name of the specified column index.

public String getColumnTypeName(int index)throws SQLException:

it returns the column type name for the specified index.

public String getTableName(int index)throws SQLException:           

it returns the table name for the specified column index.

 

JDBC - Select Records Example