JDBC

JDBC stands for Java Database Connectivity. JDBC is a Java API to connect and execute the
query with the database. It is a part of JavaSE (Java Standard Edition). JDBC API uses JDBC
drivers to connect with the database. There are four types of JDBC drivers:

 JDBC-ODBC Bridge Driver,

 Native Driver,
 Network Protocol Driver, and
 Thin Driver

We can use JDBC API to access tabular data stored in any relational database. By the help of
JDBC API, we can save, update, delete and fetch data from the database. It is like Open
Database Connectivity (ODBC) provided by Microsoft.

The current version of JDBC is 4.3. It is the stable release since 21st September, 2017. It is
based on the X/Open SQL Call Level Interface. The java.sql package contains classes and
interfaces for JDBC API. A list of popular interfaces of JDBC API are given below:

 Driver interface
 Connection interface
 Statement interface
 PreparedStatement interface
 CallableStatement interface
 ResultSet interface
 ResultSetMetaData interface
 DatabaseMetaData interface
 RowSet interface

A list of popular classes of JDBC API are given below:

 DriverManager class
 Blob class
 Clob class
 Types class

Why Should We Use JDBC

Before JDBC, ODBC API was the database API to connect and execute the query with the
database. But, ODBC API uses ODBC driver which is written in C language (i.e. platform
dependent and unsecured). That is why Java has defined its own API (JDBC API) that uses
JDBC drivers (written in Java language).

We can use JDBC API to handle database using Java program and can perform the following
activities:

1. Connect to the database

2. Execute queries and update statements to the database
3. Retrieve the result received from the database.

Java Database Connectivity with 5 Steps

5 Steps to connect to the database in java

1. Register the driver class

2. Create the connection object
3. Create the Statement object
4. Execute the query
5. Close the connection object

There are 5 steps to connect any java application with the database using JDBC. These steps
are as follows:

 Register the Driver class

 Create connection
 Create statement
 Execute queries
 Close connection

1) Register the driver class

The forName() method of Class class is used to register the driver class. This method is used
to dynamically load the driver class.

Syntax of forName() method

public static void forName(String className)throws ClassNotFoundException

Example to register the OracleDriver class

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

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

2) Create the connection object

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

Syntax of getConnection() method

1) public static Connection getConnection(String url)throws SQLException

2) public static Connection getConnection(String url,String name,String password)

throws SQLException

Example to establish connection with the Oracle database

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

3) Create the Statement object

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

Syntax of createStatement() method

1. public Statement createStatement()throws SQLException

Example to create the statement object

1. Statement stmt=con.createStatement();

4) Execute the query

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.

Syntax of executeQuery() method

1. public ResultSet executeQuery(String sql)throws SQLException

Example to execute query

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

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

5) Close the connection object

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

Syntax of close() method

public void close()throws SQLException

Example to close connection

con.close();

It avoids explicit connection closing step.

Java as a database front end

 Java offers several benefits to the developer creating a front -end application for a
database server. Java is ‘Write Once Run Everywhere’ language. This means that Java
programs may be deployed without recompilation on any computer architectures and
operating systems that possesses a Java Virtual Machine.
 In addition there is a cost associated with deployment and maintenance of the
hardware and software of any system (client) the corporation owns Systems such as
Windows PC, Macintosh and Unix desktop centric clients (fat clients) can cost
corporations between $10,000 to $15,000 per installation seat. Java technology has
made it possible for any company to use smaller system footprint. These systems are
based on Java chip set and run any and all Java programs from built -in Java operating
system.

 Java based clients (thin clients) that operate with minimum of hardware resources, yet
run the complete Java environment are expected to cost around $70 per seat.
According to studies, saving for the corporations moving 10,000 fat clients to thin
clients systems could be much as $100 million annually. There are many industrial-
strength DBMS available in the market. These include Oracle DB2, Sybase and many
 other popular brands. The challenge to Sun Microsystems faced in the late 1990s was
to develop a way for Java developer to write a high level code that accesses all
popular DBMSs.
 The Sun Microsystems met the challenge in 1996 with the creation of JDBC driver for
JDBC API. Both were created out of necessity, because until then Java wasn’t
industrial strength programming language since Java was unable to access the DBMS.
 The JDBC driver developed by Sun wasn’t driver at all. It was specification that
described the detail functionality of JDBC driver. DBMS manufacturers and third-
party vendors encouraged to build JDBC drivers that confirmed to Sun’s
specifications. Those firm that built JDBS drivers for their product could tap into
growing Java applications market. The specifications required a JDBC driver to be a
translator that converted low-level proprietary DBMS messages to low-level
messages understood by JDBC API and vice-versa.
 This meant that Java programmer could use highlevel Java data -objects defined in the
JDBC API to write a routine that interacted with the DBMS. Java data objects convert
the routine into low-level message that conform to the JDBC driver specification and
send them to the JDBC driver. The JDBC driver translates the routine into low-level
messages that understood and processed by DBMS.

Database client-server methodology

Relational databases are the most common DBMS. A main characteristic of a relational
database is the absolute separation between physical and logical data. Data is accessed
through the associated logical model to avoid supplying physical storage locations and to
reduce the limitations imposed by using physical information.

Database client/server architecture

Relational databases allow the definition of relations and integrity rules between data sets.
E.F. Codd developed this model at the IBM San Jose Research Lab in the 1970s. A language
to handle, define, and control data was also developed at the IBM lab: SQL. SQL stands for
Structured Query Language.

SQL is a query language that interacts with a DBMS. It allows data access without supplying
physical access plans, data retrieval as sets of records, and the performing of complex
computations on the data.

Software Architectures

The first generation of client-server architectures is called two-tiered. It contains two active
components: the client, which requests data, and the server, which delivers data. Basically,
the application’s processing is done separately for database queries and updates, and for user
interface presentations. Usually the network binds the back end to the front end, although
both tiers could be present on the same hardware. For example, hundreds or thousands of
airline seat reservation applications can connect to a central DBMS to request, insert, or
modify data.

While the clients process the graphics and data entry validation, the DBMS does all the data
processing. Actually, it is inadvisable to overload the database engine with data processing
that is irrelevant to the server, thus some processing usually also happens on the clients. The
typical client-server architecture is shown in Figure below:

Two-tier client server architecture

The two tiers are often called as Application layer includes JDBC drivers, business logic and
user interfaces whereas second layer i.e. Database layer consists of RDBMS server.

Advantages:

 It is simple in design.
 Client-side scripting offloads work onto the client

Drawbacks:

 Fat client.
 It is inflexible.

Although the two-tiered architecture is common, another design is starting to appear more
frequently. To avoid embedding the application’s logic at both the database side and the
client side, a third software tier may be inserted. In three -tiered architectures, most of the
business logic is frozen in the middle tier. In this architecture, when the business activity or
business rules change, only the middleware must be modified. Figure below illustrates the
three-tier architecture.

Three-tier client/server architecture

Advantages:

 Flexible: It can change one part without affecting others.

 It can connect to different databases without changing code.
 Specialization: presentation / business logic / data management.
 It can cache queries.
 It can implement proxies and firewalls.

Drawbacks:

 Higher complexity
 Higher maintenance
 Lower network efficiency
 More parts to configure (and buy)

JDBC SELECT Query Example

SQL SELECT query are executed to fetch data stored in relational databases. It requires
following steps:

1) Make a database connection

2) Execute the SQL Query
3) Fetch the data from result set

Pre-requisites include setting up a database schema and creating a table at least.

CREATE SCHEMA 'JDBCDemo' ;

CREATE TABLE 'JDBCDemo'.'EMPLOYEE'
(
'ID' INT NOT NULL DEFAULT 0 ,
'FIRST_NAME' VARCHAR(100) NOT NULL ,
'LAST_NAME' VARCHAR(100) NULL ,
'STAT_CD' TINYINT NOT NULL DEFAULT 0
);

Let’s write above steps in code:

1) Make a database connection
Class.forName("com.mysql.jdbc.Driver");

connection = DriverManager

.getConnection("jdbc:mysql://localhost:3306/JDBCDemo", "root",
"password");

2) Execute the SQL Query

This is the main step and core part in the post. It requires creating a Statement object and
then using it’s executeQuery() method.

Statement selectStmt = connection.createStatement();

ResultSet rs = selectStmt

.executeQuery("SELECT ID,FIRST_NAME,LAST_NAME,STAT_CD FROM EMPLOYEE

WHERE ID <= 10");

3) Fetch the data from result set

You can use various getXXX() methods available in ResultSet. But if you want to make it
generic then use getString() method and parse the data as and when needed.

ResultSet rs = selectStmt

.executeQuery("SELECT ID,FIRST_NAME,LAST_NAME,STAT_CD FROM EMPLOYEE

WHERE ID <= 10");

while(rs.next())

System.out.println(rs.getString(1)); //First Column

System.out.println(rs.getString(2)); //Second Column

System.out.println(rs.getString(3)); //Third Column

System.out.println(rs.getString(4)); //Fourth Column

}
JDBC SQL INSERT Query Example

SQL INSERT query are executed to push/store data stored in relational databases. It requires
following steps:

1) Make a database connection

2) Execute the SQL INSERT Query

CREATE SCHEMA 'JDBCDemo' ;

CREATE TABLE 'JDBCDemo'.'EMPLOYEE'
(
'ID' INT NOT NULL DEFAULT 0 ,
'FIRST_NAME' VARCHAR(100) NOT NULL ,
'LAST_NAME' VARCHAR(100) NULL ,
'STAT_CD' TINYINT NOT NULL DEFAULT 0
);

Let’s write above steps in code:

1) Make a database connection

Though we have already learned about it in making JDBC connection, lets recap with this
simple code snippet.

Class.forName("com.mysql.jdbc.Driver");

connection = DriverManager

.getConnection("jdbc:mysql://localhost:3306/JDBCDemo", "root",
"password");

2) Execute the SQL INSERT Query

This is the main step and core part in ths post. It requires creating a Statement object and then
using it’s execute() method.

Statement stmt = connection.createStatement();

stmt.execute("INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME,STAT_CD) VALUES

(1,'Lokesh','Gupta',5)");

Above statement will execute an insert statement in database we are connected to.
JDBC SQL DELETE Query Example

SQL DELETE query are executed to remove/delete data stored in relational databases. It
requires following steps:

1) Make a database connection

2) Execute the SQL DELETE Query

Pr-requisites include setting up a database schema and creating a table first.

CREATE SCHEMA 'JDBCDemo' ;

CREATE TABLE 'JDBCDemo'.'EMPLOYEE'

'ID' INT NOT NULL DEFAULT 0 ,

'FIRST_NAME' VARCHAR(100) NOT NULL ,

'LAST_NAME' VARCHAR(100) NULL ,

'STAT_CD' TINYINT NOT NULL DEFAULT 0

);

Let’s write above steps in code:

1) Make a database connection

Though we have already learned about it in making JDBC connection, lets recap with this
simple code snippet.

Class.forName("com.mysql.jdbc.Driver");

connection = DriverManager

.getConnection("jdbc:mysql://localhost:3306/JDBCDemo", "root",
"password");

2) Execute the SQL DELETE Query

This is the main step and core part in the post. It requires creating a Statement object and then
using it’s execute() method.

Statement stmt = connection.createStatement();

stmt.execute("DELETE FROM EMPLOYEE WHERE ID >= 1");

Above statement will execute delete statement in database we are connected to. This will
remove all records which match by where clause.