JDBC

The JDBC interfaces provided with the Java standard library actually define a group of interfaces, and a specific jdbc driver is actually a class that implements these interfaces

In Java code, if you want to access MySQL, you must write code to operate the JDBC interface. Note that the JDBC interface comes with the Java standard library, so it can be compiled directly. The specific jdbc driver is provided by the database manufacturer

In fact, the JDBC driver of MySQL is a jar package, which is also written in pure Java. The code we write only needs to refer to the Java. XML provided by the Java standard library The relevant interfaces under the SQL package can indirectly access the MySQL server through the network through the jar package of MySQL driver. All complex network communications are encapsulated in the JDBC driver. Therefore, the Java program itself only needs to introduce a jar package of MySQL driver to normally access the MySQL server:

connect

Add a Maven dependency directly:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
    <scope>runtime</scope>
</dependency>

The MySQL URL is:

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/alumni?serverTimezone=GMT&useSSL=true&useUnicode=true&characterEncoding=utf8
jdbc.username=root
jdbc.password=root

To obtain a database connection, use the following code:

// The URL of JDBC connection. Different databases have different formats:
String JDBC_URL = "jdbc:mysql://localhost:3306/test";
String JDBC_USER = "root";
String JDBC_PASSWORD = "password";
// Get connection:
Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
// TODO: accessing database
// Close connection:
conn.close();

The core code is the static method getConnection() provided by DriverManager. DriverManager will automatically scan the classpath to find all JDBC drivers, and then automatically select a suitable driver according to the URL we passed in.

Because JDBC connection is an expensive resource, it should be released in time after use. Using try (resource) to automatically release JDBC connections is a good method:

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    ...
}

CRUD

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    // query
    try (PreparedStatement ps = conn.prepareStatement("SELECT id, grade, name, gender FROM students WHERE gender=? AND grade=?")) {
        // Set each placeholder? Value of
        ps.setObject(1, "M"); // Note: the index starts at 1
        ps.setObject(2, 3);
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                long id = rs.getLong("id"); 
                long grade = rs.getLong("grade");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
            }
        }
    }
    // Add, delete, use executeUpdate()
    try (PreparedStatement ps = conn.prepareStatement(
            "INSERT INTO students (id, grade, name, gender) VALUES (?,?,?,?)"),
        	Statement.RETURN_GENERATED_KEYS)) { // Enable JDBC driver to return self incrementing primary key
        // Note: the index starts at 1
        ps.setObject(1, 999); 
        ps.setObject(2, 1); 
        ps.setObject(3, "Bob"); 
        ps.setObject(4, "M"); 
        // Returns the number of affected rows
        int n = ps.executeUpdate(); 
        // Get self incrementing primary key value
        try (ResultSet rs = ps.getGeneratedKeys()) {
            if (rs.next()) {
                long id = rs.getLong(1); 
            }
        }
    }
}

Batch

try (PreparedStatement ps = conn.prepareStatement("INSERT INTO students (name, gender, grade, score) VALUES (?, ?, ?, ?)")) {
    // Set parameters repeatedly for the same PreparedStatement and call addBatch():
    for (Student s : students) {
        ps.setString(1, s.name);
        ps.setBoolean(2, s.gender);
        ps.setInt(3, s.grade);
        ps.setInt(4, s.score);
        ps.addBatch(); // Add to batch
    }
    // Execute batch:
    int[] ns = ps.executeBatch();
    for (int n : ns) {
        System.out.println(n + " inserted."); // Number of results per SQL execution in batch
    }
}

affair

Connection conn = openConnection();
try {
    // Turn off auto submit:
    conn.setAutoCommit(false);
    // Execute multiple SQL statements:
    insert(); update(); delete();
    // Commit transaction:
    conn.commit();
} catch (SQLException e) {
    // Rollback transaction:
    conn.rollback();
} finally {
    conn.setAutoCommit(true);
    conn.close();
}

Set the isolation level of transactions:

// Set the isolation level to READ COMMITTED:
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

Connection pool

Reuse the created connections through the Connection Pool

JDBC connection pool has a standard interface javax sql. Datasource, note that this class is located in the Java standard library, but it is only an interface. To use JDBC connection pool, we must select an implementation of JDBC connection pool. Common JDBC connection pools are:

• HikariCP
• C3P0
• BoneCP
• Druid

At present, HikariCP is the most widely used. First, add the dependencies of HikariCP as follows:

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>2.7.1</version>
</dependency>

Next, we need to create a DataSource instance, which is the connection pool:

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/test");
config.setUsername("root");
config.setPassword("password");
config.addDataSourceProperty("connectionTimeout", "1000"); // Connection timeout: 1 second
config.addDataSourceProperty("idleTimeout", "60000"); // Idle timeout: 60 seconds
config.addDataSourceProperty("maximumPoolSize", "10"); // Maximum connections: 10
DataSource ds = new HikariDataSource(config);

Note that creating a DataSource is also a very expensive operation, so usually the DataSource instance is always stored as a global variable and runs through the life cycle of the application.

When obtaining the Connection, put drivermanage Change getconnection() to DS getConnection():

try (Connection conn = ds.getConnection()) { // Get connection here
    ...
} // Close the connection here

Generally, the connection pool provides a large number of parameters that can be configured, such as the minimum and maximum number of active connections maintained, specifying that a connection will automatically close after being idle for a period of time, etc. these parameters need to be configured reasonably according to the load of the application. In addition, most connection pools provide detailed real-time status for monitoring.