In this paper, the content

  1. First introduced the significance of JDBC: Java provides a set of operation database interface specification.
  2. It then describes the methods used for JDBC in three parts: registering the driver, obtaining the connection, executing the SQL statement, and obtaining the results.
  3. Driver registration section, introduced Driver, DriverManager interface, and introduced JDBC 4 after using Java SPI automatic Driver registration principle.
  4. The Getting Connections section introduces the Connection and DataSource interfaces, and explains some of the considerations for connections and why you should use Connection pooling.
  5. The SQL Statement execution section describes the capabilities of Statement, PreparedStatement, and ResultSet interfaces for operating databases.

A picture

What is JDBC?

We’ve all used databases, and you’ve probably used the command line to connect to a database yourself. Take MySQL for example:

  1. MySQL server address, port, user name, password to connect to the MySQL server
  2. After connecting to MySQL, execute the REQUIRED SQL statement, and MySQL will return the corresponding data

To do this using the Java language, the java.sql package defines a standard set of interfaces, known as the JDBC specification. All database parties (not limited to MySQL) implement this specification. Users introduce the specification implementation of the database they want to connect to. Through the JDBC interface of Java, users can connect to the database and execute SQL statements to operate the database without caring about the different connection details corresponding to different database vendors.

The steps for using JDBC consist of registering the driver, obtaining the connection, executing the SQL statement, and obtaining the results. We will introduce it from these three parts.

Registration drive

Register the purpose of the driver

Driver refers to the Driver interface under the java.sql package, which defines two important methods:

// To get a database connection
Connection connect(String url, java.util.Properties info) throws SQLException;
// Determines whether the configured database URL to connect is supported by the driver's implementation
boolean acceptsURL(String url) throws SQLException;
Copy the code

When using JDBC to connect to a database, we usually configure a JDBC connection Properties, which must contain a URL parameter:

url=jdbc:mysql://localhost:3306/db_name
Copy the code

The acceptsURL method of the Driver determines whether the configuration drop URL is supported by the Driver, and the connect method uses the URL to connect to the database.

The JDBC interface has a key DriverManager class, which we will use directly later to obtain the database Connection. As the name implies, DriverManager is used to manage the Driver. The purpose of Driver registration is to register the Driver implementation of the JDBC implementation we want to use (such as mysql-connector-Java JAR package) into DriverManager, so that the user (Java application code writer) can obtain the database connection, Directly use interfaces in JDBC, regardless of the different implementations of different databases.

SPI automatically registers the driver

Many older code or blogs also register drivers in the following way:

Class.forName("com.mysql.cj.jdbc.Driver")  // Load the Dirver class. The static block registers itself with DriverManager when the Driver class is initialized
Copy the code

In fact, since JDBC4.0 (corresponding to JDK 1.6), DriverManager has supported automatic driver registration using SPI.

SPI is a set of interfaces provided by Java that are implemented or extended by a third party (the caller chooses the third party implementation as required). Users can specify which implementation of the interface to load in the meta-INF /services directory of the project

The Driver implementation class is specified in the meta-INF /services directory of the mysql-connector-Java JAR package

It is easily found in the DriverManager class, which fetches the Driver in the static block during class initialization

DriverManager will load the JDBC Driver implementation of the database connection package; Note, jDBC4 does not need explicit registration, when using DriverManager to obtain a connection will automatically detect the load driver;

// Driver.loadInitialDrivers
AccessController.doPrivileged(new PrivilegedAction<Void>() {
  // Omitted some code
  public Void run(a) {
    // SPI loads the Driver classServiceLoader<Driver> loadedDrivers = ServiceLoader.load(Driver.class); }});Copy the code

The Java SPI and mysql-connector-Java driver package automatically do this for us. No application code registration is required.

Obtain a connection

Connect (mysql-u -p); connect (mysql-u -p); connect (mysql-u -p);

Here are some things to note about getting connections:

  1. Obtaining a database connection is a heavy operation, which requires the three-way handshake between the application and the database server to establish a TCP connection, and the database server to verify the user name and password to allocate resources to the connection.
  2. The database Connection object is a resource occupation for both the application (such as MySQL client) and the database server (such as MySQL server). It needs to be explicitly released and cannot be created in large numbers.
  3. The same database connection can be used to execute multiple SQL statements and can execute multiple transactions consecutively.

Based on the above points, it can be concluded that database connections can and should be reused. In practical applications, connection pools are usually used to manage database connections, such as HikariDataSource, which implements java.sql. The application defines the database connection information, user name, password, connection pool size, connection timeout duration, and so on. The application can directly obtain the connection using HikariDataSource, which is responsible for database connection creation, release, and other life cycle management.

DataSource is a JDBC interface, which simply defines two methods to obtain a Connection. It can be used instead of DriverManager to obtain a Connection. Is used to do connection pool and so on

Execute SQL statements and get results

After obtaining the database Connection, we will use Connection to execute the SQL statement to complete the database operation, so let’s see what methods are defined in the JDBC Connection interface:

// java.sql.Connection

Create a Statement on the database connection
Statement createStatement(a) throws SQLException;

PreparedStatement; // Precompile a SQL Statement. The PreparedStatement interface inherits Statement
PreparedStatement prepareStatement(String sql) throws SQLException;
Copy the code

It introduces two other JDBC interfaces that execute SQL statements and get the result comparison core: Statement and PreparedStatement, which inherit from the former.

Statement A direct string used to execute SQL statements.

PreparedStatement adds two important points to a PreparedStatement:

  1. Support for pre-compiled SQL
  2. Parameter placeholders are supported to avoid SQL injection problems that may result from direct concatenation of SQL statements

Precompilation is a syntax supported by databases, which is supported by MySQL. The advantage of precompilation is that if a SQL statement is executed repeatedly but the parameters are different, the syntax and lexical parsing can be performed only once, which improves the performance

Take a look at the methods provided by the Statement interface:

// java.sql.Statement

// Execute SQL statements of type SELECT, return results in a ResultSet
ResultSet executeQuery(String sql) throws SQLException;
// Used to execute INSERT, UPDATE, and DELETED SQL statements
int executeUpdate(String sql) throws SQLException;
// To get things that are automatically generated by statement execution, such as auto-increment primary keys, and insert returned primary keys
ResultSet getGeneratedKeys(a) throws SQLException;
Copy the code

The PreparedStatement interface adds methods related to setting parameters because it needs to support precompilation:

// java.sql.PreparedStatement

// Execute the query because the SQL statement is precompiled and does not need to be passed in
ResultSet executeQuery(a) throws SQLException;
// Set the parameter to omit other types of setXXX() based on the position index in the SQL statement at precompilation time.
void setInt(int parameterIndex, int x) throws SQLException;
void setString(int parameterIndex, String x) throws SQLException;
Copy the code

Get the results

After executing a SQL Statement, you can retrieve a ResultSet, either by returning Statement. GetResultSet, or by using Statement. GetResultSet.

// For example, execute the traversal of the result returned by the query statement
ResultSet resultSet = statement.executeQuery("select * from user limit 10");

while (resultSet.next()){
 formatter.format(format,resultSet.getInt("id"),resultSet.getString("name"),resultSet.getInt("age"),simpleDateFormat.format(resultSet.getDate("birth")));
  System.out.println();
}

// Insert statement returns result processing
try(
  PreparedStatement preparedStatement = connection.prepareStatement("insert into user(name,age,birth) values(? ,? ,?) ", Statement.RETURN_GENERATED_KEYS)
){
  preparedStatement.setString(1, name);
  preparedStatement.setInt(2, age);
  preparedStatement.setDate(3.new Date(birth.getTime()));
  preparedStatement.execute();
  ResultSet resultSet = preparedStatement.getGeneratedKeys();
  if(resultSet.next()){
    Mysql > add primary key
    return resultSet.getInt(1);
  }else{
    throw new RuntimeException("Insert failed"); }}Copy the code

subsequent

I will write a blog closer to Mybatis to see how it uses JDBC and bring convenience to our daily development.