JDBC in Java | Board Infinity

Introduction

Java code may interface to relational databases like Postgres, Oracle, MySQL, H2 Database, and others thanks to the Java JDBC API (Java Database Connectivity). Relational databases may be accessed and updated using the JDBC API. Stored procedures can also be called, and metadata about the database can be obtained. All Java programs that want to utilize JDBC have access to it thanks to the Java JDBC API, which is a component of the fundamental Java SE SDK.

JDBC is not DB Specific

The Java JDBC API formalizes how to access a database, run queries against it, explore the results of those searches, perform modifications in the database, invoke stored procedures, and retrieve metadata from the database. It is "standardized," in the sense that the code is consistent across various database products. Therefore, switching to a different database will be much simpler if your project ever requires it.

JDBC Is Not Independent of SQL

The SQL that is supplied to the database is not standardized by JDBC. As a JDBC API user, you are the one that writes the SQL. Because each database's SQL dialect will differ slightly from the others, your SQL must be completely independent of all databases.

JDBC is Not For Non-Relational Databases

Relational databases, or databases you connect with using normal SQL, are the focus of the Java JDBC API. MongoDB, Cassandra, Dynamo, and other non-relational databases are not intended uses of the JDBC API. Such databases are accessible from Java programs, but you should check the drivers they offer for Java.

Popular Relational Databases

Here is a list of well-known relational databases that are free source and for sale that includes JDBC drivers so you may use them from Java:

H2Database
MariaDB
PostgreSQL
Derby
Oracle
Microsoft
SQL Server

JDBC Core Concepts

The Java JDBC API's fundamental ideas are as follows:

Connection
Statement for the JDBC Driver
PreparedStatement
Callable Statement
Batch Updates for the ResultSet
Transactions DatabaseMetaData

These ideas are each given a more thorough explanation in their respective lessons. However, I will just provide a brief outline of how these essential elements are employed in the sections that follow. Once you have a foundational knowledge of JDBC, you can next dig into the additional lessons.

JDBC Example

The following JDBC code demonstrates how to load the JDBC driver, establish a database connection, generate a Statement, run a query, and iterate the returning ResultSet:

import java.sql.*;

public class JdbcExample {

public static void main(String[] args) throws ClassNotFoundException {
Class.forName("com.mysql.Driver");

String url = "jdbc:mysql:~/test";

String user = "sa";
String password = "";

try(Connection connection = DriverManager.getConnection(url, user, password)) {
try(Statement statement = connection.createStatement()){
String sql = "select * from people";
try(ResultSet result = statement.executeQuery(sql)){
while(result.next()) {
String name = result.getString("name");
long age = result.getLong ("age");
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}

}
}

write your code here: Coding Playground

If you don't understand everything, don't worry. The sections below provide explanations for each component of this example.

Load JDBC Driver

Class.forName("org.h2.Driver");

The JDBC driver class name for the specified JDBC driver is contained in the Java String parameter passed to the Class forName() method. You must identify the appropriate class name for your JDBC driver. Since each database often has its own JDBC driver, you will need to seek up the name of the JDBC driver class (if you are using pre-Java 6). There are several types of JDBC drivers. Each variety has distinct benefits and drawbacks. You must check the types of drivers that your database provider provides.

Open Database Connection

You must first establish a DB connection before you can interact with it. Here is a brief illustration of how to open a JDBC connection, which is covered in more detail in my lesson on JDBC connections:

String url = "jdbc:h2:~/test"; //database specific url.
String user = "sa";
String password = "";

Connection connection = DriverManager.getConnection(url, user, password);

When you open a database connection, you usually either update the database by adding new records or updating those that already exist, or you query the database, which means you read records from it. In the parts that follow, both activities will be briefly discussed.

Create Statement

You must build a JDBC Statement or JDBC PreparedStatement in order to update or query the database, depending on your needs. An illustration of generating a JDBC Statement instance is provided here:

Statement statement = connection.createStatement();

Update the Database

Updates to the database can be made after creating a JDBC Statement or JDBC PreparedStatement instance. Here is an illustration of how to use a Statement instance to update the database:

Statement statement = connection.createStatement();
String sql = "update people set name='John' where id=123";
int rowsAffected = statement.executeUpdate(sql);

Query the Database

Additionally, a JDBC Statement or PreparedStatement object can be used to query the database. When you query a database, you receive a JDBC ResultSet in return, which you can use to access the query's outcome. Here is an illustration of how to use JDBC to run a query against a database:

Statement statement = connection.createStatement();
String sql = "select * from people";
ResultSet result = statement.executeQuery(sql);
while(result.next()) {
String name = result.getString("name");
long age = result.getLong ("age");
}

Close Database Connection

You must once again end the connection after using the JDBC database connection. Both inside your application and the database server, a JDBC connection might use a significant amount of resources. It is crucial to again shut off the database connection after use. A JDBC connection's close() function is used to terminate it. Using the following code, a JDBC connection may be closed:

connection.close();