Java Database Connectivity – JDBC Tutorial

Incorporating a database in Java is an interesting concept. Do you wonder how fun it is to work with the many functionalities that Java provides? The presence of a database in Java is like adding a feather to its cap. This article will let you know more about java database connectivity and database operations like INSERT, SELECT, UPDATE, and DELETE that you can perform using Java.

What is JDBC?

JDBC is the abbreviation of Java Database Connectivity. It lets you perform basic fundamental database activities. The basic tasks are INSERT, SELECT, UPDATE, and DELETE articulations in SQL dialect. The procedures that we apply here are not much different from that of the other database frameworks.

Prerequisites to perform database operations:

• JDK
• Oracle Database
• JDBC driver for Oracle Database

These are the prerequisites that you need before running a Java database program on your system.

User creation and granting permission in Oracle Database:

1. Open the Oracle utilizing cmd. Compose the SQL plus in cmd and press Enter.

2. Now, create a client ID that is secured by a watchword. This client ID is denoted as a youngster client.

3. To make the client recognized, provide required authorizations to youngster client. For straightforwardness, you can allow the database manager privilege to the youngster client.

conn/ as sysdba;
grant dba to;

Sample example for Java JDBC:

CREATE TABLE userid(
id varchar2(10) NOT NULL PRIMARY KEY,
pwd varchar2(15) NOT NULL,
name varchar2(30),
mailid varchar2(30)
);

Purpose of JDBC:

Certain enterprise applications created JAVA with the help of EE(Enterprise Edition) technology. The interaction with the database is vital for these applications to store and retrieve various information. This smooth interaction would take place only with the presence of efficient database connectivity.

We can achieve this using ODBC (Open Database Connectivity) driver. This driver interacts with the JDC to communicate with various databases like MS Access, SQL, MySql, Oracle, etc.

JDBC Applications

Various executables can be written using Java. Some of these are:

• Java Applets
• Java Applications
• Java Servlets
• Enterprise JavaBeans(EJBs)
• Java ServerPages (JSPs)

The JDBC driver helps these executables to access a database and use the stored data. It offers various features like ODBC by letting Java programs contain database-independent code.

JDBC 4.0 Packages:

The JDBC consists of two primary packages: 4.0 java.sql and javax.sql. At present, JDBC 4.0 is the latest JDBC version. With the help of these packages, you can use main classes to interact with the data.

Some of the features available in these packages are:

• Annotations
• Automatic database driver loading.
• Connection and statement interface enhancements.
• Enhanced BLOB/CLOB functionality.
• Exception handling improvements.
• National character set support.
• SQL ROWIND access.
• SQL 2003 XML data type support.

JDBC Components:

The JDBC comprises four main components that let us interact with the database.

1. JDBC API

It consists of all the classes, methods, and interfaces that are helpful to communicate with the database easily. Furthermore, it also provides a couple of packages that include Java SE(Standard Edition) and Java EE(Enterprise Edition) platforms to exhibit the WORA(Write Once Run Anywhere) features.

2. JDBC Driver Manager

This Driver Manager of JDBC loads a database-specific driver in the application. It creates a connection with the database. It also establishes a database-specific call to the database to process the user request.

3. JDBC Test Suite

This Test Suite of JDBC plays a vital role in testing the operations like insertion, deletion, and updation performed by the JDBC Drivers.

4. JDBC-ODBC Bridge Drivers

It connects the database drivers to the database and translates the JDBC method call to the ODBC method call. It also makes use of the package with the native library to access the ODBC features.

Architecture of JDBC

1. Application:

Applications are Java Applet or Servlet that communicates with a data source.

2. JDBC API:

The API includes classes, methods, and interfaces that let the Java programs execute the SQL statements and fetch results from the database. Some of the primary classes and interfaces are:

• CallableStatement
• Connection
• Driver
• DriverManager
• PreparedStatement
• ResultSet
• SQL data
• Statement

3. Driver Manager:

It plays a vital role in the JDBC architecture. It uses certain database-specific drivers for databases.

4. JDBC Drivers:

It let us communicate with a data source via JDBC. The JDBC driver intelligently interacts with the data source.“

Types of JDBC Architecture:

The types of processing models in JDBC architecture are two-tier and three-tier. It helps us to access the database.

1. Two-tier model:

The Java application links with the data source. The JDBC driver provides communication between the application and the data source. As a user sends a query, the answers are given in the form of results.

This data source can be located on another machine via a network where the user is connected. This structure is known as client/server configuration where the user machine is the client and the machine with the data source is the server.

2. Three-tier model:

Here, the user queries go to the middle-tier services. From the middle-tier service, the commands go to the data source. Then, the results of the query get back to the middle tier. Then, it reaches the user.

Principal JDBC Interfaces and Classes:

In this part, we will discuss the various interfaces and classes present in JDBC.

1. forName():

Here, the driver’s class document is stacked into the memory at runtime. The need for utilizing new or production of a question can be avoided.

Class.forName(“oracle.jdbc.driver.OracleDriver”);

2. DriverManager:

Using this class, we can enlist drivers for a particular database to compose and set up the database that is associated with the server. This is done using the getConnection() strategy.

3. Association:

The interface is related to the database that allows us to execute questions, recover comes about, get metadata regarding the database, close the association, and so on.

Association con = DriverManager.getConnection
(“jdbc:oracle:thin:@localhost:1521:orcl”, “login”, “pwd”);

Statement and PreparedStatement:

These interfaces are used to run the static SQL question and parameterized SQL inquiry separately. A proclamation is the super interface of the PreparedStatement interface.

Boolean Execute(String sql):

It executes a general SQL articulation. We can use it to return genuine if the question restores a ResultSet. It returns false if the inquiry restores a refresh tally or returns nothing. this strategy can be used with a Statement as it were.

Int executeUpdate(String sql):

It executes INSERT, UPDATE or DELETE proclamation. It also returns a refresh account demonstrating various lines influenced. For example, 1 push embedded, 2 columns refreshed, or 0 lines influenced.

Statement stmt = con.createStatement();
String q1 = “Embed into userID values ('" +id+ "', '" +pwd+ "', '" +name+ "', '" +mailid+ "')"; 
int x = stmt.executeUpdate(q1);”

ResultSet executeQuery(String sql):

It executes a SELECT proclamation and returns a ResultSet question that has the comes about returned by the inquiry.

Statement stmt = con.createStatement();
String q1 = “select * from userid WHERE is= ‘” +id+ “’ Also, pwd = “’ +pwd+’”;	
ResultSet rs = stmt.executeQuery(q1);

4. ResultSet:

It contains table details returned by a SELECT question. Utilize this protest emphasize over columns in the outcome set utilizing the straightaway() technique.

5. SQLException:

The checked special case is proclaimed to be thrown by all the above-mentioned strategies. We must handle these exemptions in a better manner using the classes’ techniques.

Steps to create JDBC application:

1. Importing the packages: Firstly, you must import certain packages that require the database application to run in the system. Mostly, the java.sql.* package is imported.

2. Registering the JDBC driver: You must initialize the driver to open a communication channel with the database.

3. Opening a connection: The method DriverManager.getConnection() is used to create a connection object. This represents a physical connection with the database.

4. Executing a query: It requires using an object of type statement for building and submitting an SQL statement to the database.

5. Extracting data from the result set: Using the ResultSet.getXXX() methods, we can retrieve the data from the result set.

6. Cleaning the environment: This step is to close all the database resources that rely on the JVM’s garbage collection.

Program to establish database connection:

1. Connecting to the Database:

import java.sql.*;
public class connect
{
public static void main(String args[])
{
try
{       
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");
if (con != null) 
System.out.println("Connected"); 
else 
System.out.println("Not Connected");
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}

Output

2. Implementing Insert Statement:

import java.sql.*;
public class insert
{
public static void main(String args[])
{
String id = "id1";
String pwd = "pwd1";
String name = "FirstCode";
String mailid = "[email protected]"; 
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("
jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");
Statement stmt = con.createStatement();
String q1 = "insert into userid values('" +id+ "', '" +pwd+"', '" +name+ "', '" +mailid+ "')";
int x = stmt.executeUpdate(q1);
if (x > 0) 
System.out.println("Successfully Inserted"); 
else 
System.out.println("Insert Failed"); 
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}

Output

3. Implementing Update Statement:

3. Implementing Update Statement: 
import java.sql.*;
public class update
{
public static void main(String args[])
{
String id = "id1";
String pwd = "pwd1";
String newPwd = "newpwd";
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("
jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");
Statement stmt = con.createStatement(); 
String q1 = "UPDATE userid set pwd = '" + newPwd +"' WHERE id = '" +id+ "' AND pwd = '" + pwd + "'";
int x = stmt.executeUpdate(q1); 
if (x > 0) 
System.out.println("Password Successfully Updated"); 
else 
System.out.println("ERROR OCCURED :("); 
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}

Output

4. Implementing Delete Statement:

import java.sql.*;
public class delete
{
public static void main(String args[])
{
String id = "id2";
String pwd = "pwd2";
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("
jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");
Statement stmt = con.createStatement(); 
String q1 = "DELETE from userid WHERE id = '" + id +"' AND pwd = '" + pwd + "'"; 
int x = stmt.executeUpdate(q1); 
if (x > 0) 
System.out.println("One User Successfully Deleted"); 
else
System.out.println("ERROR OCCURED :("); 
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}

Output

5. Implementing Select Statement:

import java.sql.*;
public class select
{
public static void main(String args[])
{
String id = "id1";
String pwd = "pwd1";
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("
jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");
Statement stmt = con.createStatement();
String q1 = "select * from userid WHERE id = '" + id +"' AND pwd = '" + pwd + "'";
ResultSet rs = stmt.executeQuery(q1);
if (rs.next())
{
System.out.println("User-Id : " + rs.getString(1));
System.out.println("Name :" + rs.getString(3));
System.out.println("E-mail :" + rs.getString(4));
}
else
{
System.out.println("No such user ID is registered");
}
con.close();
}
catch(Exception e)
{
System.out.println(e);}}}

Output

Conclusion

This was all about java database connectivity (JDBC). With the help of these database operations, you can CONNECT to a database, INSERT statements, execute SELECT, execute UPDATE statements, and execute Delete statements.