06 Basic Norms - How to Understand JDBC Relational Database Access Standards #

Starting today, we will start discussing another core technology in the Spring Boot ecosystem, which is the data access technology. Whether it is an internet application or traditional software, data storage and access are indispensable for any system.

The construction of the data access layer may involve a variety of different forms of data storage media. This course focuses on the most basic and commonly used data storage media, which is relational databases. For relational databases, the most widely used specification in Java is JDBC. Today, we will discuss this classic specification.

JDBC stands for Java Database Connectivity. It is designed to provide a unified standard that can be applied to various databases. This standard needs to be followed by different database vendors and provide their own implementation solutions for JDBC applications.

As a unified standard, the JDBC specification has a complete architectural system, as shown in the following figure:

Overall Architecture of JDBC Specification

From the above figure, we can see that Java applications access data through the API provided by JDBC. These APIs contain the core programming objects that developers need to master. Let’s take a look at them together.

What are the core programming objects in the JDBC specification? #

For everyday development, the core programming objects in the JDBC specification include DriverManager, DataSource, Connection, Statement, and ResultSet.

DriverManager #

As shown in the overall architecture diagram of JDBC specification, the DriverManager in JDBC is mainly responsible for loading various driver programs and returning the corresponding database connections (Connection) to the application according to different requests. The application then performs operations on the database by calling JDBC APIs.

The Driver interface in JDBC is defined as follows, in which the most important method is the connect method that gets the connection:

public interface Driver {

    // Get database connection
    Connection connect(String url, java.util.Properties info) throws SQLException;

    boolean acceptsURL(String url) throws SQLException;

    DriverPropertyInfo[] getPropertyInfo(String url, java.util.Properties info) throws SQLException;

    int getMajorVersion();

    int getMinorVersion();

    boolean jdbcCompliant();

    public Logger getParentLogger() throws SQLFeatureNotSupportedException;

}

For the Driver interface, different database vendors provide their own implementation solutions. For example, the Driver implementation class in MySQL is shown in the following code:

public class Driver extends NonRegisteringDriver implements java.sql.Driver {

    // Register the Driver through DriverManager
    static {
        try {
            java.sql.DriverManager.registerDriver(new Driver());
        } catch (SQLException E) {
            throw new RuntimeException("Can't register driver!");
        }
    }

    ...
}

Here, the DriverManager is used, which not only provides the registerDriver method for registering the Driver, but also provides the getConnection method for obtaining the Connection object from the specific Driver.

DataSource #

From the previous introduction, we know that in the JDBC specification, we can directly obtain a Connection through the DriverManager. We also know that the process of getting a Connection requires establishing a connection with the database, which can generate a large system overhead.

In order to improve performance, we usually first establish an intermediate layer to store the Connection generated by the DriverManager in a connection pool, and then obtain the Connection from the pool.

And we can think of DataSource as such an intermediate layer. It was introduced as a substitute for DriverManager and is the preferred method for obtaining a database connection.

In the JDBC specification, DataSource represents a data source and its core function is to obtain a database connection object, Connection. In the daily development process, we usually obtain a Connection based on DataSource. The definition of the DataSource interface is shown in the following code:

public interface DataSource extends CommonDataSource, Wrapper {

    Connection getConnection() throws SQLException;
    
    Connection getConnection(String username, String password) throws SQLException;

}

From the above code, we can see that the DataSource interface provides two overloaded methods for obtaining a Connection, and it inherits the CommonDataSource interface. CommonDataSource is the root interface in JDBC for defining data sources. In addition to the DataSource interface, it has two other sub-interfaces, as shown in the following figure:

Class Hierarchy of DataSource

Among them, DataSource is the basic interface officially defined for obtaining a Connection. XADataSource is used to obtain a Connection in a distributed transaction environment, and ConnectionPoolDataSource is used to obtain a Connection from the connection pool ConnectionPool.

The so-called ConnectionPool is like pre-generating a batch of Connections and storing them in a pool to improve the efficiency of obtaining Connections.

Please note that the DataSource interface also inherits the Wrapper interface. From the naming of the interface, we can infer that this interface acts as a wrapper. In fact, because many database vendors provide extensions beyond the standard JDBC API, the Wrapper interface can wrap a non-standard interface provided by a third-party vendor into a standard interface.

Taking the DataSource interface as an example, if we want to implement a customized data source class, MyDataSource, we can provide a MyDataSourceWrapper class that implements the Wrapper interface to complete the wrapping and adaptation, as shown in the following figure:

Extension of JDBC Specification through Wrapper interface

In the JDBC specification, in addition to DataSource, core objects such as Connection, Statement, and ResultSet also implement this Wrapper interface.

As a basic component, DataSource does not need developers to implement it themselves, because there are already many excellent implementation solutions in the industry, such as DBCP, C3P0, and Druid.

For example, Druid provides DruidDataSource, which not only provides connection pool functionality, but also provides other features such as monitoring. Its class hierarchy is shown in the following figure:

Class Hierarchy of DruidDataSource

Connection #

The purpose of DataSource is to obtain a Connection object. We can think of Connection as a session mechanism. Connection represents a database connection and is responsible for communication with the database.

All SQL executions are performed in a specific Connection environment, and it also provides a set of overloaded methods for creating Statement and PreparedStatement. On the other hand, Connection is also involved in transaction-related operations.

The Connection interface defines a rich set of methods, and the most important ones are shown in the following code:

public interface Connection extends Wrapper, AutoCloseable {

    // Create Statement
    Statement createStatement() throws SQLException;
    ...
}

// Create PreparedStatement
PreparedStatement prepareStatement(String sql) throws SQLException;

// Commit
void commit() throws SQLException;

// Rollback
void rollback() throws SQLException;

// Close connection
void close() throws SQLException;
}

Here we are dealing with the concrete Statement and PreparedStatement objects responsible for executing SQL statements. Let's continue to look further.

#### Statement/PreparedStatement

There are two types of Statement according to the JDBC specification: the regular Statement and the PreparedStatement that supports precompilation.

Precompilation means that the database's compiler precompiles the SQL statement and caches the precompiled result in the database. The next time the statement is executed, the compiled statement can be used by simply replacing the parameters, which greatly improves the execution efficiency of the SQL.

Of course, this precompilation also incurs some overhead, so in daily development, if the database is only accessed for one-time read or write operations, it is more appropriate to use the Statement object. However, if there are multiple executions involving the same SQL statement, the PreparedStatement can be used.

To query data from the database, we only need to call the executeQuery method of Statement or PreparedStatement object. 

This method takes the SQL statement as a parameter and returns a JDBC ResultSet object after execution. Of course, Statement and PreparedStatement also provide a large number of overloaded methods for executing SQL updates and queries, which we won't go into here.

Taking Statement as an example, its core methods are as follows:

```java
public interface Statement extends Wrapper, AutoCloseable {

	// Execute a query statement
	ResultSet executeQuery(String sql) throws SQLException;

	// Execute an update statement
	int executeUpdate(String sql) throws SQLException;

	// Execute an SQL statement
	boolean execute(String sql) throws SQLException;

	// Execute a batch update
	int[] executeBatch() throws SQLException;

}

Here we have also introduced the ResultSet, which represents the execution result.

ResultSet #

Once we have executed the SQL statement and obtained a ResultSet object through Statement or PreparedStatement, we can use the numerous utility methods defined in this object to retrieve the values of the executed SQL statements. The following code shows some of these methods:

public interface ResultSet extends Wrapper, AutoCloseable {

	// Get the next result
	boolean next() throws SQLException;

	// Get a result of a certain type
	Value getXXX(int columnIndex) throws SQLException;

	...

}

ResultSet provides the next() method for developers to traverse the entire result set. If the next() method returns true, it means that there is data in the result set and the corresponding result value can be retrieved using the getXXX() methods of the ResultSet object.

How to use the JDBC specification to access databases? #

For developers, the JDBC API is the main way to access databases. If we develop an execution flow for accessing a database using JDBC, the common code style is as follows (exception handling omitted):

// Create a pooled data source
PooledDataSource dataSource = new PooledDataSource();

// Set the MySQL Driver
dataSource.setDriver("com.mysql.jdbc.Driver");

// Set the database URL, username, and password
dataSource.setUrl("jdbc:mysql://localhost:3306/test");
dataSource.setUsername("root");
dataSource.setPassword("root");

// Get the connection
Connection connection = dataSource.getConnection();

// Execute a query
PreparedStatement statement = connection.prepareStatement("select * from user");

// Process the query result
ResultSet resultSet = statement.executeQuery();

while (resultSet.next()) {
    ...
}

// Close resources
statement.close();
resultSet.close();
connection.close();

In this code, data access to the core programming objects in the JDBC API we introduced earlier is completed. The above code mainly focuses on query scenarios, and for insert scenarios, we only need to replace a few lines of code in the “execute a query” and “process the query result” sections with code for insertion operations.

Finally, let’s summarize the entire development process for database access based on the JDBC specification, as shown in the following diagram:

Development process for database access based on the JDBC specification

For the previous code example, we clearly divide the data access using the JDBC specification into two parts: one is for preparing and releasing resources and executing SQL statements, and the other is for processing the results of SQL execution.

However, the former is actually repetitive for any data access. In the entire development process shown in the figure, only the code in the “process ResultSet” part needs to be customized by developers according to the specific business objects. This abstraction provides optimization space for the entire execution process. Template tool classes such as JdbcTemplate in the Spring framework have emerged to address this. We will introduce this template tool class in Lesson 07.

Summary and Preview #

The JDBC specification is a widely used standard specification for database access in the Java EE field. In today’s lecture, we analyzed the core programming objects of this specification and outlined the development process for accessing databases using the JDBC specification. I hope you can become familiar with this knowledge, as familiarity with the JDBC specification is the foundation for understanding the subsequent content.