Java Database Connectivity (JDBC)

JDBC ( Java Database Connectivity )

Database : A database is a separate application that stores a collection of data.

Why to Learn JDBC?

The JDBC library includes APIs we can use for :

1.Making a connection to a database.

2.Creating SQL or MySQL statements.

3.Executing SQL or MySQL queries in the database.

4.Viewing & Modifying the resulting records.

What is JDBC?

JDBC is a standard Java API for database connectivity between the Java programming language and a wide range of databases.

JDBC Architecture

 JDBC Architecture consists of two layers −

1. JDBC API − This provides the application-to-JDBC Manager connection.

2. JDBC Driver API − This supports the JDBC Manager-to-Driver Connection.

Common JDBC Components

The JDBC API provides the following interfaces and classes −

DriverManager 

Driver 

Connection 

Statement 

ResultSet

SQLException 

Types of Driver 

Driver divided into four categories as below

Type 1 − JDBC - ODBC Bridge Driver  (ODBC - Open DataBase Connectivity)

 - Using ODBC, requires configuring on your system a Data Source Name (DSN) that represents the target database.

Ex : JDK 1.2

Type 2 − JDBC-Native API 

- JDBC API calls are converted into native C/C++ API calls, which are unique to the database. 

Ex : Oracle Call Interface (OCI) driver 

Type 3 − JDBC-Net pure Java/Network Protocol driver

In a Type 3 driver, a three-tier approach is used to access databases. The JDBC clients use standard network sockets to communicate with a middleware application server.

Ex : network-protocol 

Type 4 − 100% Pure Java / Thin driver

The JDBC type 4 driver, also known as the Direct to Database Pure Java Driver, is a database driver implementation that converts JDBC calls directly into a vendor-specific database protocol.

The Type 2 JDBC driver is written in a language other than Java, often C++ or C. The Type 3 JDBC driver talks to a middleware server first, not the database directly. The Type 4 JDBC driver is a pure, direct Java-to-the-database implementation.

JDBC Connection 

Step for DB Connection there are five steps as below : 

1.Register/Load the Driver

-Use 1.Class.forName() method  Or 2.DriverManager.registerDriver()

- Ex : Class.forName("com.mysql.jdbc.Driver); 

- Ex : DriverManager.registerDriver( new com.mysql.jdbc.Driver);

 

2.Create connection

- use DriverManager.getConnection() method 

String URL = "jdbc:mysql://localhost:3306/DBName";

String USER = "username";

String PASS = "password"

Connection conn = DriverManager.getConnection(URL, USER, PASS);

3.Create statement

Here we use Statements,  PreparedStatement and CallableStatement Intertaces.

When we can each interface  .

Statement : The Statement interface cannot accept parameters.

PreparedStatement :- Use this when you plan to use the SQL statements many times.PreparedStatement interface accepts input parameters at runtime.

CallableStatement :- Use this when you want to access the database stored procedures. The CallableStatement interface can also accept runtime input parameters.

4.Execute queries

We Have three  execute  methods to execute query in java program.

1. boolean execute (String SQL) :  this method Use to execute SQL DDL statements means return boolean value either true or false.

2. int executeUpdate (String SQL) : this method Use  to execute SQL statements for which you expect to get a number of rows affected - for example, an INSERT, UPDATE, or DELETE statement.

3. ResultSet executeQuery (String SQL) − Returns a ResultSet object. Use this method when you expect to get a result set, as you would with a SELECT statement.

1. Statement

String query = "select * from employee";

Statement stmt = conn.createStatement( );

ResultSet rs =  stmt.executeQuery(query);

2. PreparedStatement

String query = "Update Employees SET age = ? WHERE id = ?";

PreparedStatement ps = conn.prepareStatement(query );

int i = ps.executeUpdate(query);

3. CallableStatement

String query = "{call getEmpName (?, ?)}";

CallableStatement  cs= conn.prepareCall(query );

ResultSet rs =  cs.executeQuery(query);

Three types of parameters exist in CallableStatement interface : IN(setMethod), OUT(getMethod), and INOUT(both).

5.Close connection

Use : conn.close() ; to close connection of JDBC.

The JDBC connection can now be closed after all is done. The resource has to be closed to avoid running out of connections. It can be done automatically using ‘conn.close();’. But for versions of Java 7 and above, it can be closed using a try-catch block