JDBC Java Database Connectivity

JDBC is Java Database Connectivity API/Driver used to communicate with RDBMS(Relational Database).

It's inside java.sql.* package, which comes under (JSE Java Standard Edition), and javax.sql.* package, which comes under (JEE Java Enterprise Edition).

JDBC Outcomes

1. Connect a java app to any database.

2. Execute DDL & DML Commands

3. Perform Database Transaction.

4. Invoke stored procedure

Types of JDBC Drivers

1. Type 1 aka Bridge (There will be a bridge driver like ODBS between JDBC driver and database we never use this because it is very slow )

2. Type 2 aka Native ( Direct Connection between JDBC and database through native calls, it is fast )

3. Type 3 aka Middleware (JDBC Connection to Database via Middleware, middleware can be any middleware mostly it is paid)

4. Type 4 aka Pure Java (direct call to the database)we 

Prerequisites for establishing the connection to the database

1. Download the driver (jar)

2. IP address, port number, database instance, User ID( UID ), password (We will get this information from Database Administrator)

Steps for establishing Connection

                String URL = " ";

                String UID = " ";

                String PASS = " ";

                String DRIVER = " ";

1. Load the Driver class (  Class.forName("fully qualified Name of Driver Class");  )

2. Ask DriverManager to establish the connection. (Connection con = DriverManager.getConnection(url, uid, pass); )

Ex.

(TestConnection.java)

class TestConnection{

public void main(String args[]){

                String URL = " ";

                String UID = " ";

                String PASS = " ";

                String DRIVER = " ";

                try{

                        Class.getName(DRIVER); //if Driver name will be wrong it will raise checked ClassNotFound Execption

                        Connection con = DriverManager.getConnection(URL, UID, PASS);

                        System.out.println("connected "+con);

                    }catch(ClassNotFoundException | SQLException e){ e.printStackTrace(); }

        }

}

Interview Question

 Why we are Loading the Driver class?

Ans: To register Driver to Driver Manager.

Since we are creating the class object of the driver who will register the driver to the Driver manager?

Ans: The driver class has a static initializer that will register the driver to the Driver manager.

What is the structure of the URL path?

Ans: URL = "jdbc:<diver specific part>@<IP>:<port>/<dbinstance>"

How many Drivers can we Load at a time?

Ans: Any number

Then How Driver Manager will know which Driver to pick while creating a connection?

Ans: Based on the driver-specific part of the URL.

What will happen if we do as below?

Connection con1 = DriverManager.getConnection(URL, UID, PASS);

Connection con2 = DriverManager.getConnection(URL, UID, PASS);

Connection con3 = DriverManager.getConnection(URL, UID, PASS);

Ans: 3 parallel connection will get created.

How to release the resource?

Ans: In finally call con.close();

JDBC Statements

java.sql.Statement

java.sql.PreparedStatement (used for DDL/DML)

java.sql.CallableStatement (used Stored procedure)

Relation between Statements: PreparedStatement is a Statement and  CallableStatement is a PreparedStatement

java.sql.Statement

Statement stmt = con.createStatement();

int num = stmt.executeUpdate("DML/DDL");

ResultSet rs = stmt.executeQuery("Select........");

Why we will not use java.sql.Statement?

1. no pre-compilation

2. no auto data type conversion

3. not prone to SQL injection

Who create the Statements?

By seeing code it looks like we are asking connection to create statements but in reality, the Driver creates a Statement.

PreparedStatement

String sql1 = "insert into table (col1, col2, col3) values(?,?,?)"; // template sql

String sql2 = "update  table set col2 = ? where col3 = ?";

String sql3 = "select col1,col2 from table where col3 = ?";

//passing PreparedStatement to the database

PreparedStatement ps1 = con.prepareStatement(sql1);

PreparedStatement ps2 = con.prepareStatement(sql2);

PreparedStatement ps3 = con.prepareStatement(sql3);

//Setting values

ps1.setString(1, val1);

ps1.setDate(2, val2);

ps1.setFloat(3, val3);

//execute

ps1.execute();

//release the resource after execution in finally

For Select Statements

after calling execute call below statements to get the resultset

ResultSet rs = ps3.getResultSet();

while(rs.next())

{

        String c1 = rs.getString("col1");

        String c2 = rs.getString("col2");

}

What happens internally when statements like PreparedStatement ps1 = con.prepareStatement(sql1); is execute?

The driver will pass the statement to the Database and the database will create an execution path tree.

Note: Connection, Statement, Resultset, PreparedStatement all are interfaces.

What driver-jar contains mainly?

1 class for java.sql.Driver interface

1 class for java.sql.Connection interface

1 class for java.sql.Statement interface

1 class for java.sql.PreparedStatement interface

1 class for java.sql.CollableStatement interface

1 class for java.sql.ResultSet interface

Transaction management in JDBC?

A transaction is a unit of work that spans multiple SQL commands which either succeeds fully or is revoke fully.

Steps for Transaction Management

1. First inform the database that we are going to control the transaction & not to make each change permanent until we inform so.

2. Perform all SQL cmd executions.

3. if all SQL commands succeeds, then commit the changes to the database

4. if any exception/failure occurs, roll back. 

How?

1. Turn off auto commit feature

con.setAutoCommit(false);

2. if Exception occur call rollback

con.rollBack();

3. call commit at the end if every thing goes well

con.commit();

What we will do when we want to use one table value to insert into another table?

while creating prepareStatement give two-parameter 1 SQL statement and other to return generated key. After executing get the generated key.

PreparedStatement ps1 = con.prepareStatement(sql1, Statement.RETURN_GENRATED_KEYS);

.

.

.

ps1.execute();

ResultSet rs = ps1.getGenratedKey();

rs.next();

int sl = rs.getInt("sl_no"); //get the first column