The Java API known as JDBC (Java Database Connectivity) controls how to establish a connection to a database, send commands and queries, and handle result sets received from the database. One of the first Java language libraries was JDBC, which was introduced in 1997 as a part of JDK 1.1.
This article provides an introduction to JDBC and JDBC drivers before going over how to connect a Java client to a simple relational database using JDBC. Learn practical implementations of the JDBC through our Java Training in Chennai at SLA Institute.
What is JDBC? – Intro to the Relational Databases in Java
JDBC was first intended to be a client-side API that would allow a Java client to communicate with a data source. JDBC 2.0 brought about a shift in this by introducing an optional package that supported server-side JDBC connections. Updates to both the client-side package (java.sql) and the server-side package (javax.sql) have been included in every new JDBC version since then. The most recent version of JDBC, JSR 221, was made available in September 2017 as part of Java SE 9.
How does Java JDBC Works?
In a Java program, a developer can utilize JDBC to communicate with a database. As depicted in this picture, JDBC serves as a connection between your code and the database.
ODBC vs JDBC
Before JDBC, programmers accessed relational database management systems, or RDBMS, using Open Database Connectivity (ODBC), a language-independent standard technique. JDBC draws part of its inspiration from ODBC. The distinction is that JDBC (Java Database Connectivity) is only compatible with Java and provides a programming-level interface for managing how Java programs interact with databases.
JDBC Architecture
There are two layers in the JDBC interface:
- The Java application and the JDBC manager can communicate thanks to the JDBC API.
- The JDBC manager and database driver can communicate with each other thanks to the JDBC driver.
A feature-rich, fast, and dependable library is the result of years of intensive JDBC API and driver refinement.
The common API that your application code uses is called JDBC. The JDBC-compliant driver for the database you’re using is located underneath that.
The architecture for JDBC is shown in the picture below.
JDBC Drivers
As a software programmer, as long as the driver you use is secure and authorized, you don’t need to worry right away about how it was implemented. The four different types of JDBC drivers are nevertheless important to be aware of:
JDBC-ODBC Bridge Driver: The ODBC driver is used in the background by the JDBC-ODBC bridge driver, a thin Java layer.
Native API Driver: Offers a Java-to-native database client interface.
Middleware Driver: A common interface (or “middleware”) between Java and the vendor-specific protocol of the RDBMS.
Pure Java Driver: A driver that directly implements the vendor-specific protocol in Java is referred to as a pure Java driver.
Simple Database Connections and Queries
One advantage of developing in the Java ecosystem is the likelihood of finding a reliable JDBC database connection for any database you decide on. Because SQLite is so user-friendly, we’ll utilize it in this article to get acquainted ourselves with Java Database Connectivity.
The following are the steps for connecting to a database using JDBC:
- Install the database you want to access or find it.
- Put the Java JDBC library in.
- Make sure the classpath contains the JDBC driver you require.
- To establish a connection to the database, use the JDBC library.
- SQL commands can be executed using the connection.
- Once you’re done, close the connection.
Step 1: Download and Install SQLite
A relatively small database is SQLite. Although not intended for usage in production, it is a wonderful option for quickly testing out ideas. Without requiring the installation of any services or daemons, SQLite employs a file as its operational database.
Download the SQLite example database before beginning this presentation. The .db file should be saved somewhere you won’t forget to unzip it. This document includes a working file-based database as well as example data and a schema that we may utilize.
SQLite and Java JDBC
Despite NoSQL’s popularity, relational databases continue to be the most common kind of data store. An organized repository made up of tables with columns and rows as well as the connections between the tables is referred to as a relational database. Data architects carry out CRUD (create, read, update, and delete) activities on records in a relational database using SQL, or structured query language. JDBC is a Java to SQL adaptor layer that provides Java developers with a standard interface for connecting to databases, sending commands and queries, and handling answers.
Step 2: Import JDBC into the Java Application
We could use an IDE for our coding, but writing our code directly in a text editor will better show how straightforward JDBC is. You must have a suitable JDK installation for your operating system before you can proceed.
We can begin by developing a straightforward Java program if you have a JDK set up. Copy and paste the List 1 code into your text editor. The file is called ExampleJdbc.java.
List 1: Example Java Program
class ExampleJdbc{
public static void main(String args[]){
System.out.println(“Hello JDBCUsers”);
}
}
Now run the command: javac ExampleJdbc.java to build the code. The ExampleJdbc.class file will be produced during compilation. Use the call java ExampleJdbc on the command line to run this file.
The JDBC libraries can be incorporated once you have a fundamental Java program. Add the code from List 2 to the beginning of your straightforward Java program.
List 2: JDBC Imports
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
These imports each give access to a class that supports the common Java database connection:
- Connection: It is a representation of the database connection.
- DriverManager: It establishes the database connection. (DataSource, another choice, is utilized for connection pooling.)
- SQLException: Any SQL issues that occur between a Java application and a database are handled by SQLException.
- ResultSet and Statement Model: The data result sets and SQL statements are modeled by them.
Step 3: Include the JDBC Driver in the Classpath
The SQLite driver can now be added to your classpath. A JDBC driver, keep in mind, is a class that implements the JDBC API for a particular database.
Visit the SQLite driver’s website to download the most recent SQLite.jar. You can add the driver via the Maven repository if you use Maven, Gradle, or a comparable tool. Get the latest recent.jar file and put it somewhere.
List 3: Execute the SQLite Driver on the Java Classpath
java.exe -classpath /path-to-driver/sqlite-jdbc-3.23.1.jar:. ExampleJdbc
Java will still be able to find our class file because we’ve set the classpath to point at the driver and the local directory.
Step 4: Accessing a Java Database Connection
The driver is now available on the classpath. Next, modify your straightforward Java application file so that it resembles the following List 4 program.
List 4: Imply the JDBC class to connect with SQLite
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
String sql = “SELECT id, username FROM users WHERE id = ?”;
List users = new ArrayList<>();
try (Connection con = DriverManager.getConnection(myConnectionURL);
PreparedStatement ps = con.prepareStatement(sql)) {
ps.setInt(1, userId);
try (ResultSet rs = ps.executeQuery()) {
while(rs.next()) {
users.add(new User(rs.getInt(“id”), rs.getString(“name”)));
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return users;
class ExampleJdbc{
public static void main(String[] args) {
String url = “jdbc:sqlite:path-to-db/chinook/chinook.db”;
try (Connection conn = DriverManager.getConnection(url){
System.out.println(“Got it!”);
} catch (SQLException e) {
throw new Error(“Problem”, e);
}
}
}
Note: “No Suitable Driver Found” Error
If you’ve encountered a problem that reads, “No suitable driver found for jdbc:sqlite,” you should check your classpath once more to make sure it points to the driver you downloaded. For new JDBC users, a failed driver connection is the most frequent problem. Just fix it; don’t worry about it.
Step 5: Manipulating Relational Database using JDBC
We can do a beneficial action, like querying the database, once we have the live connection object in our possession. List 5 demonstrates how to use the JDBC Connection and Statement objects to query SQLite.
Get expertise with SQL basics through our MySQL Training in Chennai at SLA
List 5: Manipulating the Database with JDBC
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
class ExampleJdbc{
public static void main(String[] args) {
String sql = “SELECT id, username FROM users WHERE id = ?”;
String url = “jdbc:sqlite:path-to-db-file/chinook/chinook.db”;
try (Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement()) {
try {
ResultSet rs = stmt.executeQuery(“select * from albums”;);
while (rs.next()) {
String name = rs.getString(“title”);
System.out.println(name);
}
} catch (SQLException e ) {
throw new Error(“Problem”, e);
}
} catch (SQLException e) {
throw new Error(“Problem”, e);
}
}
}
In List 5, we utilize the conn.createStatement() method of our Connection object to get a Statement object. Then, using stmt.executeQuery(query), we use this object to run a SQL query.
We use the ResultSet object that the executeQuery command returns to loop through the data using the while (rs.next()) statement. You should see the album names we ran this example’s query on as output. We can see that we also called conn.close() to end the connection.
Wrapping Up
The JDBC API is used by Java programs to interact with databases and alter their data. The Java application can connect to a database thanks to the JDBC driver. JDBC can be used with any other table-based data source, however, it is almost always used with relational databases. Kick-start your developer career by learning the Best Java Training in Chennai at SLA.
