JDBC (Java Database Connectivity) is a Java API that manages database connection, query and command issuance, and processing of database result sets. Released as part of JDK 1.1 in 1997, JDBC was one of the earliest libraries developed for the Java language.
JDBC was originally designed as a client-side API that allows a Java client to interact with a data source. This has changed with JDBC 2.0, which includes an additional package that supports JDBC connections on the server side. Each new version of JDBC has since included updates to the package from the client (java.sql) and the server-side packagejavax.sql). JDBC 4.3, the latest version at the time of writing, was released as part of Java SE 9 in September 2017. JSR 221.
This article provides an overview of JDBC and JDBC drivers, followed by a practical introduction to using JDBC to connect a Java client to a lightweight relational database.
How JDBC works
As a developer, you can use JDBC to interact with a database from a Java program. JDBC acts as a bridge from your code to the database, as shown in Figure 1.
IDGFigure 1. JDBC connects Java programs to databases.
JDBC vs. ODBC
Previously, JDBC developers used Open Database Connectivity (ODBC), a language-independent standard approach to accessing a relational database management system or RDBMS. In a sense, JDBC is inspired by ODBC. The difference is that JDBC is specific to Java, offering a programming-level interface that handles the mechanics of Java applications communicating with a database.
JDBC architecture
The JDBC interface consists of two layers:
- The JDBC API supports communication between the Java application and the JDBC manager.
- The JDBC driver maintains communication between the JDBC manager and the database driver.
The JDBC API and JDBC driver have been significantly improved over the years, resulting in a feature-rich, productive and reliable library.
JDBC is the general API with which your application code interacts. Below is a JDBC-compliant driver for the database you are using.
Figure 2 illustrates the architecture of JDBC.
IDGFigure 2. The JDBC architecture consists of the JDBC API and JDBC drivers.
JDBC drivers
As an application programmer, you don’t have to immediately implement the driver you’re using, as long as it’s secure and official. However, it is useful to know that there are four types of JDBC drivers:
- JDBC-ODBC bridge driver: A thin Java layer that uses an ODBC driver under the hood.
- Native API driver: Provides a Java-to-client interface to the database client.
- Intermediate software driver: Universal interface (“middleware”) between Java and the provider-specific RDBMS protocol.
- Pure Java driver: A driver that implements a vendor-specific protocol directly in Java.
When you start thinking about architecture and performance, it will be useful to consider the type of driver you are using.
Simple database connections and queries
One of the advantages of programming in the Java ecosystem is that you will probably find a stable JDBC database connector for whatever database you choose. In this tutorial we will use SQLite to get to know JDBC, mainly because it is so easy to use.
The steps for connecting to a database with JDBC are as follows:
- Install or find the database you want to access.
- Turn on the JDBC library.
- Make sure the JDBC driver you need is in your class path.
- Use the JDBC library to get a connection to the database.
- Use the link to issue SQL commands.
- Close the connection when finished.
We will go through these steps together.
Step 1. Download and install SQLite
SQLite is a very compact database. It is not intended for industrial use, but it is a great choice for a quick try. SQLite uses a file as its functional database without requiring service installations or daemons.
To get started with this demo, download first SQLite sample database. Unzip .db file and save it somewhere you will never forget. This file contains both a functional file database and a sample schema and data that we can use.
Step 2. Import JDBC into your Java application
We could do our coding in the IDE, but direct coding in a text editor will better demonstrate the simplicity of JDBC. To get started, you will need to have a compatible JDK installation for your operating system.
Assuming you have the JDK installed, we can start by creating a simple Java program. Put the code shown in Listing 1 in your text editor. Call this file WhatIsJdbc.java.
Listing 1. A simple Java program
class WhatIsJdbc{
public static void main(String args[]){
System.out.println("Hello InfoWorld");
}
}
Now compile the code by entering the command: javac WhatIsJdbc.java. Compiling will output WhatIsJdbc.class file. Run this command-line file with the call: java WhatIsJdbc.
Once you have a basic Java program, you can include JDBC libraries. Put the code from Listing 2 at the beginning of your simple Java program.
Listing 2. Importing JDBC
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
Each of these imports provides access to a class that facilitates a standard connection to a Java database:
Connectionrepresents the connection to the database.DriverManagergets the connection to the database. (Another option isDataSourceused to join links.)SQLExceptionhandles SQL errors between the Java application and the database.ResultSetandStatementmodel the resulting datasets and SQL statements.
You will soon see each of them in action.
Step 3. Add the JDBC driver to your class path
You will then add the SQLite driver to your class path. Remember, ah JDBC driver is a class that implements the JDBC API for a specific database.
Go to GitHub page for SQLite driver and download the latest SQLite .jar. If you are using Maven or Gradle, or something similar, you can add the driver via Maven storage. Don’t forget to take the latest .jar file and save it somewhere you will remember.
The next time you run your Java program, you will download this .jar file via the class path. There are several ways to set the path to class. List 3 shows how to do this using the command line switch.
Listing 3. Running the SQLite driver in the Java class path
java.exe -classpath /path-to-driver/sqlite-jdbc-3.Note that we have set the path to the class to point to the driver and the local directory; this way Java will still find our class file.
Step 4. Get a database connection
The class path now has access to the driver. Then change your simple Java application file to look like the program in Listing 4.
Listing 4. Using the JDBC Connection class to connect to 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 WhatIsJdbc{
public static void main(String[] args) {
String url = "jdbc:sqlite:path-to-db/Compile and execute this code. If we assume that everything is going well, you will receive an affirmative message.
Now we are ready for some SQL commands.
Step 5. Query the database
With the live link object in hand, we can do something useful, such as querying the database. List 5 shows how to query SQLite using JDBC Connection and Statement objects.
Listing 5. Query 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 WhatIsJdbc{
public static void main(String[] args) {
String sql = "SELECT id, username FROM users WHERE id = ?";
String url = "jdbc:sqlite:path-to-db-file/In Listing 5 we use ours Connection object for obtaining a Statement object: conn.createStatement(). We then use this object to execute an SQL query: stmt.executeQuery(query).
IN executeQuery the command returns a ResultSet an object that we then use to view the data with while (rs.next()). In this example, you should see the album titles we asked for as an output.
Note that we also closed the connection by calling conn.close().
PreparedStatements, batch updates and transactions
So far, we’ve covered the basics of using JDBC to connect to a database and issue SQL commands. While Statementsand ResultSetworks well for general scenarios, you will probably need additional options for larger or more complex applications. Fortunately, the JDBC library continues to evolve to meet most database access needs.
https://www.infoworld.com/article/3388036/what-is-jdbc-introduction-to-java-database-connectivity.html#tk.rss_all
