JDBC : An Overview

JDBC stands for Java DataBase Connectivity. JDBC is an API. So before diving into the main content, Let’s get to know what API means.
API stands for Application Programming Interface which acts as a mediator between any applications and it ensures requests and responses.

JDBC API is used for connecting the java applications to the relational databases. Also, It is a Java-based data access technology used for Java database connectivity. However, The JDBC API converts the java calls to the database-specific calls and the database-specific calls to java calls.

All the classes related to the JDBC are contained in java.sql.* and javax.sql.* packages. so, The javax.sql package consists of additional classes and interfaces.

The standard steps for developing a JDBC Application:

1. Load and register driver class

• .forName(“driver class name”) this statement loads the specified driver class.
• When we load the class name the static block gets executed which consists of registration of the driver. 
• Since Java 1.6, JDBC version 4.0 API, there is no need to register the driver explicitly, We just need to set the classpath for JDBC, Java automatically detects the Driver class and loads it.

2. Establish a connection between the java application and the specific database.

• We need to be creating a connection object with the help of the DriverManager.getConnection() method which accepts 3 parameters such as URL, username, password.
• URL is related to database-specific. Such as for an oracle thin driver it is JDBC:oracle:thin:@localhost:1521:xe.
• Username, Password is a database username and password.

3. Creating a statement object.

• It deals with the SQL statements.

4. Execute the SQL query.
5. Process result from ResultSet

architecture of JDBC:

JDBC consists of two types of architecture that are 2-tier and 3-tier. Tier means the level. These are just the processing models of how the data is accessed from the database. In 2-tier the java application directly deals with the database. Whereas in 3-tier the java application deals with a middleware server and it deals with the database.

The number of drivers in JDBC:

1. Type-1 driver (JDBC-ODBC Bridge driver)

• From the Java application, the JDBC calls will be converted to ODBC calls and these ODBC calls will be converted to  Database specific calls and vice versa.
• It’s a Database Independent driver but it’s a platform-dependent driver.
• It’s a 2-tier architecture.

2. Type-2 driver (Native API driver or Partly Java driver)

• JDBC calls to Vendor specific calls and these calls can be directly understood by the database so it’s faster than type 1 driver since it requires only 1 level of conversion of calls.
• It is platform dependent and also database dependent.
• It’s a 2-tier architecture.

3. Type-3 driver (Network protocol driver)

• JDBC calls to middleware specific calls and then to database-specific calls.
• It’s the only driver that is both platform-independent and database independent. 
• It’s a 3-tier architecture.

4. Type-4 driver (Pure Java driver or Native protocol driver or Thin driver)

• It is a database dependent driver but platform-independent.
• It’s a 2-tier architecture.

Execute methods in JDBC:

1. executeQuery():

• Used to execute select queries. It returns a group of records stored in the ResultSet.
• Example: ResultSet rs=st.execureQuery(“select * from employees”); it returns all the records of employees table names and stores into the resultset object.

2. executeUpdate():

• Used to execute Non-select query (such as DML commands Insert, Delete, update).
• It returns the number of rows affected.
• Int x=st.executeUpdate(“delete from employees where esak>30000);

3. execute():

• Used for both select and non-select queries and it returns Boolean.
• Normally used when the SQL query is of unknown type or given dynamically.
• If the returned value is true it means it’s a select query and if it’s false then a non-select query.
• Boolean b=st.execute(dynamically provided sql query).
• if(b==true)
• so, ResultSet rs=st.getResultSet();
• else
• int rowCount=st.getUpdateCount();

4. executeBatch():

• Used for executing the batch operations.

Different types of statements in JDBC:

1. Statement

• Syntax is:   Statement st = con.createStatement();
• st is a statement object and con is a connection object.
• Statement objects are compiled and executed every time when a query needs to be processed.
• The SQL query is pass onto the statement object to execute methods.
• executeQuery(), executeUpdate(),execute() can be in use then.

2. PreparedStatement

• Syntax is : PreparedStatement pst = con.prepareStatement(SQL Query);
• pst is a PreparedStatement object. 
• In PreparedStatement objects, we need to be passing the SQL query at the time of creation.
• These objects are compile only once and can be executed many times. 
• It improves the performance when dealing with the same query multiple times as it will be compiled only once at the start and later on it will be executed whenever it is called.
• But when we need to process multiple queries we need to create another / recreate object for that specific SQL query.
• executeQuery(), executeUpdate(),execute() can be in use.

3. CallableStatement

• Syntax is Callable Statement cst = con.prepareCall(“call procedure_name(parameters)”);
• It is in use when we need to work with store procedures or functions.
• Only the execute() method can be in use.

Conclusion:

However, JDBC API can handle heterogeneous databases. Driver Manager handles communication with different drivers that connect to these databases.

Written by: Laxmi Narayana

Reviewed By: Soutik Maity

If you are Interested In Machine Learning You Can Check Machine Learning Internship Program
Also Check Other Technical And Non Technical Internship Programs