Basic use of JDBC + c3p0 + DBCP

Time:2020-2-13

1. overview

This article mainly discusses the basic use of JDBC, including statement, Preparedstatement, JDBC connection, MySQL creating user created data table, c3p0 connection and configuration, DBCP connection and configuration

2. MySQL processing

Here, JDBC uses MySQL as DBMS. Please install MySQL first. If not, please click here to download. The installation tutorial is here. The author uses MySQL version 8.0.17

(1) New user

Create a new user at random. For example, the new user created by the author here is AA, and the password is aa123bb

create user 'aa'@'localhost' identified by 'aa123bb'

(2) Create data table

Establish data table and database for testing

create database db;
use db;

create table db
(
    id int PRIMARY key,
    name char(20)
);

(3) User rights

To authorize the newly created user:

grant select,update,delete,insert on db.* to 'aa'@'localhost';

2.JDBC

(1) jar package

Version 8.0.17 is here

Download all versions here

(2) connection

First register the driver. The driver needs a URL, user name and password. The user name and password are created in the previous step. The URL contains the IP address, port and database name

private static final boolean mysqlVersionGreaterThen8 = true;
private static final String driver = "com.mysql" + (mysqlVersionGreaterThen8 ? ".cj" : "") + ".jdbc.Driver";
private static final String ip = "127.0.0.1";
private static final String port = "3306";
private static String databaseName = "db";
private static String url;
private static String username = "aa";
private static String password = "k041400r";
private static Connection connection = null;

public static Connection getConnection() {
    try {
        url = "jdbc:mysql://" + ip + ":" + port + "/" + databaseName;
        Class.forName(driver);
        return connection = DriverManager.getConnection(url, username, password);
    } catch (Exception e) {
        e.printStackTrace();
    }
    return null;
}

Note that the driver of the old version of MySQL is com.mysql.jdbc.driver, and the new version is com.mysql.cj.jdbc.driver. The URL format is also included:

jdbc:mysql://ip:port/database

(3)Statement

After getting the database connection, use the createstatement method to create a statement

  • For select, use executeQuery (SQL) of statement to return resultset
  • For update, delete, insert, use executeupdate (SQL) of statement

Where SQL is the SQL statement to be executed, a string

public void useStatement() {
    try {
        useStatementInsert();
        useStatementSelect();
        useStatementUpdate();
        useStatementSelect();
        useStatementDelete();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public void useStatementInsert() throws SQLException {
    String sql = "insert into db(id,name) values(1,'23')";
    Statement statement = connection.createStatement();
    statement.executeUpdate(sql);
}

public void useStatementDelete() throws SQLException {
    String sql = "delete from db";
    Statement statement = connection.createStatement();
    statement.executeUpdate(sql);
}

public void useStatementSelect() throws SQLException {
    String sql = "select * from db";
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery(sql);
    ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
    int count = resultSetMetaData.getColumnCount();
    while (resultSet.next()) {
        for (int i = 1; i <= count; ++i) {
            System.out.println(resultSet.getObject(i));
        }
    }
}

public void useStatementUpdate() throws SQLException {
    Statement statement = connection.createStatement();
    String sql = "update db set id = 3,name = '555' where id = 1";
    statement.executeUpdate(sql);
}

Here, GetMetadata used for resultset can obtain various type information of result set, including type, number of fields, etc

(4)PreparedStatement

Preparedstatement is basically the same as statement. When calling, first use preparestatement (SQL) of connection to create, and then

  • For select, use executequery() to return a resultset
  • For update, delete, insert uses executeupdate()
public void usePrepareStatement() {
    try {
        usePrepareStatementInsert();
        usePrepareStatementSelect();
        usePrepareStatementUpdate();
        usePrepareStatementSelect();
        usePrepareStatementDelete();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public void usePrepareStatementInsert() throws SQLException {
    String sql = "insert into db(id,name) values(1,'23')";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.executeUpdate();
}

public void usePrepareStatementDelete() throws SQLException {
    String sql = "delete from db";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.executeUpdate();
}

public void usePrepareStatementSelect() throws SQLException {
    String sql = "select * from db";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    ResultSet resultSet = preparedStatement.executeQuery();
    ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
    int count = resultSetMetaData.getColumnCount();
    while (resultSet.next()) {
        for (int i = 1; i <= count; ++i)
            System.out.println(resultSet.getObject(i));
    }
}

public void usePrepareStatementUpdate() throws SQLException {
    String sql = "update db set id = 3,name = '555' where id = 1";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.executeUpdate();
}

(5) affairs

Connection has a setautocommit() method. Set it to false to turn off autocommit. When all statements are ready, you can use commit() to commit at one time
Implementation of rollback can be used with savepoint

3.C3P0

(1) jar package

Two:

  • c3p0
  • mchange-commons

(2) Profile

Create a file called c3p0.properties under SRC:

c3p0.driverClass=com.mysql.cj.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql://127.0.0.1:3306/db
c3p0.user=aa
c3p0.password=aa123bb

Here you can change it according to your own needs

(3) tool category

import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;

public class DbUtil
{
    private static ComboPooledDataSource C3P0dataSource = new ComboPooledDataSource("c3p0.properties");
    public static void releaseConnection(Connection connection)
    {
        try
        {
            if(connection != null)
                connection.close();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }

    public static Connection getC3P0Connection()
    {
        try
        {
            return C3P0dataSource.getConnection();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        return null;
    }
}

4.DBCP

(1) jar package

Three:

  • commons-dbcp
  • commons-logging
  • commons-pool

(2) Profile

Create a new dbcp.properties under SRC:

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/db
username=aa
password=k041400r
initialSize=10
maxActive=50
maxIdle=15
minIdle=10
maxWait=60000
connectionProperties=useUnicode=true;characterEncoding=utf8
defaultAutoCommit=true

They are driver, URL, user name, password, number of initialized connections, maximum number of connections, maximum number of idle connections, minimum number of idle connections, maximum number of waiting for the actual situation, connection properties (code is set here), and automatic submission

(3) tool category

import org.apache.commons.dbcp2.BasicDataSourceFactory;

import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
import javax.sql.DataSource;

public class DbUtil {
    private static DataSource DBCPdataSource;
    static {
        try {
            InputStream inputStream = DbUtil.class.getClassLoader().getResourceAsStream("dbcp.properties");
            Properties properties = new Properties();
            properties.load(inputStream);
            DBCPdataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getDBCPConnection() {
        try {
            return DBCPdataSource.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void releaseConnection(Connection connection) {
        try {
            if (connection != null)
                connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

First load the properties file, then use the properties load method to load it into a properties object, and finally hand it to the basicdatasourcefactory for processing

5. source code

Including jar package, configuration file, SQL file and test code

  • github
  • Code cloud