1. JDBC Driver

JDBC-driven loading:

1. Loading through containers:

For Java applications with containers, the corresponding driver jar packages can be placed directly in the lib directory of the container, such as web applications using Tomcat as a container, and the driver can be copied to the lib subdirectory of tomcat.

2. Application Runtime Loading:

If you want the application to load the appropriate drivers on its own, you need maven to specify the classpath when searching for the driver jar for the application when hitting jar/war packages. See:

https://maven.apache.org/shared/maven-archiver/examples/classpath.html

https://www.cnblogs.com/snaildev/p/8341610.html

Example 1 (There is a lib directory under the executable jar package to store the dependent jar package):

<build>
        <finalName>image</finalName>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-jar-plugin</artifactId>
                <version>2.4</version>
                <configuration>
                    <archive>
                        <manifest>
                            <addClasspath>true</addClasspath>
                            <classpathPrefix>lib/</classpathPrefix>
                            <mainClass>some.package.MainClass</mainClass>
                        </manifest>
                    </archive>
                </configuration>
            </plugin>
        </plugins>
    </build>

When a business loads JDBC drivers on its own, it needs to do the following to load the drivers before applying JDBC:

Class.forName("com.mysql.jdbc.Driver");
// Then the following begin to use jdbc

2. Manage connections to the database through a connection pool (a type of DataSource)

The connection pools currently commonly used are Druid or Hikari, which are described below:

1. Druid connection pool:

maven dependency:

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.11</version>
</dependency>

Sample code:

DruidDataSource createSource(String name, String jdbcUrl, String userName, String password,
        maxActive, minIdle, maxWait, scanInterval, minActiveTime) {
    DruidDataSource src = DruidDataSource();
    src.setName(name);
    src.setUrl(jdbcUrl);
    src.setUsername(userName);
    src.setPassword(password);
    src.setDriverClassName(); 
    src.setInitialSize(minIdle);
    src.setMaxActive(maxActive);
    src.setMinIdle(minIdle);
    src.setMaxWait(maxWait); 
    src.setTimeBetweenEvictionRunsMillis(scanInterval); 
    src.setMinEvictableIdleTimeMillis(minActiveTime);   
    src.setTestWhileIdle(); 
    src.setTestOnBorrow();  
    src.setTestOnReturn();
    src.setPoolPreparedStatements(); 
    src.setMaxPoolPreparedStatementPerConnectionSize(); 
    src.setValidationQuery(); 
    src.setRemoveAbandoned(); 
    src.setRemoveAbandonedTimeout(); 
    src.setKeepAlive(); 
    if(!isDataSourceOk(src)){
        LOGGER.error("Data source "+ name + "test failed");
    }
    return src;
}
private static boolean isDataSourceOk(DataSource source){
    try (Connection connection = source.getConnection();
         PreparedStatement stmt = connection.prepareStatement("select 1");
         ResultSet resultSet = stmt.executeQuery()){
        resultSet.next();
        return true;
    }catch (Throwable e){
        return false;
    }
}

2. Hikari Connection Pool

maven dependency:

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

Sample code:

HikariDataSource createSource(String name, String jdbcUrl, String userName, String password,
        connectionTimeout, idleTimeout, maxLifeTime, poolSize) {
    HikariDataSource src = HikariDataSource();
    src.setPoolName(name);
    src.setJdbcUrl(jdbcUrl);
    src.setUsername(userName);
    src.setPassword(password);
    src.setDriverClassName(); 
    src.setConnectionTimeout(connectionTimeout);
    src.setIdleTimeout(idleTimeout); 
    src.setMaxLifetime(maxLifeTime); 
    src.setMaximumPoolSize(poolSize);
    if(!isDataSourceOk(src)){
        LOGGER.error("Data source "+ name + "test failed");
    }
    return src;
}

3. Processing connections

1. First get the connection through the connection pool, for example:

try(Connection getConnection() SQLException {
    assert DataSourceManager.getDataSource() != null;
    return DataSourceManager.getDataSource().getConnection();
}

2. Submitting SQL Requests for Connections

When the Connection object is obtained from the Connection pool (wheeless DruidDataSource or HirakiDataSource), the default submission method is automatic submission, that is, the call to connection.getAutoCommit() at this time must return true;

After the Connection object returns to the connection pool, the next time it requests a Connection from the connection pool, the default submission method is automatic.You need to know when to submit automatically and when to submit manually.

In general, see what happens with the same Connection object between the request for the Connection object and the return of the Connection object:

(1) Only read operations, no write operations: with automatic submission;

(2) Write a single record of a single table only once: with automatic submission;

An example of the automatically submitted code is as follows

try(Connection connection = ()) {
    block.apply(connection); // Do all query or only one update for only one record
}

(3) Write multiple records of the same table, or write records of different tables separately: determine if transactional support is required based on rollback and performance requirements; and if transactional support is required, manual submission is required;

Example of manual submission:

try(Connection connection = ()) {
    boolean success = false;
    try{
        T t = block.apply(connection); // Use this connection process one transaction
        doCommit(connection);
        success = true;
        return t;
    }finally{
        if(!success) {
            doRollback(connection); // If possible, support rollback when failed
        }
    }
}

//The connection submission mode may have been modified in the block of the complex process described above. To maintain code compatibility, the doCommit() and doRollback() above are designed as follows:

public static void doCommit(Connection connection) throws SQLException {
    if (!connection.getAutoCommit()) {
        connection.commit();
    }
}
private static void doRollback(Connection connection) throws SQLException {
    if (SUPPORT_ROLLBACK && !connection.getAutoCommit()) {
        connection.rollback();
    }
}

(4) Time-consuming operation with a relatively large amount of data, at which time the materiality and rollback depending on the database can no longer be achieved; in this case, submissions should be made several times, and rollback is provided by the application layer;

Examples are as follows:

try(Connection connection = ()) {
    boolean success = false;
    try{
        block1.accept(connection);
        doCommit(connection);
        block2.accept(connection);
        doCommit(connection);
        success = true;
    }finally{
        if(!success) {
            block2.clear(connection);
            block1.clear(connection);
        }
    }
}