preface

This is the self-study notes of crazy God's mysql, which is only for reference, learning and continuous updating

The following is a video learning link:
https://www.bilibili.com/video/BV1NJ411J79W

10. JDBC (key)

10.1. Database driver

Driver: sound card, graphics card, database

Our program will deal with the database through database driver!

10.2,JDBC

In order to simplify the (unified database) operation of developers, SUN company provides a (Java database operation) specification, commonly known as JDBC

The implementation of these specifications is done by specific manufacturers

For developers, they only need to master the operation of JDBC interface!

Required packages:

java.sql

javax.sql

You also need to import a database driver package mysql-connector-java-5.1.47.jar

10.3. The first JDBC program

Create test database

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE users(
    `id` INT PRIMARY KEY,
    `name` VARCHAR(40),
    `password` VARCHAR(40),
    `email` VARCHAR(40),
    `birthday` DATE
);

INSERT INTO users(`id`, `name`, `password`, `email`, `birthday`)
VALUES(1, 'zhangsan', '123456', 'zs@sina.com', '1980-12-04'),
(2, 'lisi', '123456', 'lisi@sina.com', '1981-12-04'),
(3, 'wangwu', '123456', 'wangwu@sina.com', '1979-12-04');

1. Create a project

2. Import database driver

3. Write test code

package com.niu.lession01;

import java.sql.*;

// My first JDBC program
public class jdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1. Load driver
        Class.forName("com.mysql.jdbc.Driver"); // Fixed writing, load driven

        // 2. User information and url
        // useUnicode=true&characterEncoding=utf8&useSSL=true
        // Delete the secure connection parameter behind the url (with warning) or change it to false
        // [if the SQL version is greater than the connect version, set it to false]
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String username = "root";
        String password = "root";

        // 3. The Connection is successful. The database object Connection represents the database
        Connection connection = DriverManager.getConnection(url, username, password);

        // 4. Object executing SQL
        Statement statement = connection.createStatement();

        // 5. The object executing SQL executes SQL. There may be results. View the returned results
        String sql = "SELECT * FROM `users`";

        ResultSet resultSet = statement.executeQuery(sql);   // The returned result value. The result set encapsulates all our query results

        while (resultSet.next()) {
            System.out.println("id=" + resultSet.getObject("id"));
            System.out.println("name=" + resultSet.getObject("name"));
            System.out.println("password=" + resultSet.getObject("password"));
            System.out.println("birthday=" + resultSet.getObject("birthday"));
            System.out.println("=====================");
        }

        // 6. Release connection
        resultSet.close();
        statement.close();
        connection.close();

    }
}

Step summary:

1. Load driver

2. Connect to database DriverManger

3. Get the Statement object executing sql

4. Get the returned result set

5. Release connection

DriverManger

// DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver"); // Fixed writing, load driven
Connection connection = DriverManager.getConnection(url, username, password);

// connection represents the database
// Database settings auto commit
// Transaction commit
// Transaction rollback
connection.rollback();
connection.commit();
connection.setAutoCommit();

URL

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";

// mysql -- 3306
// Protocol: / / host address: port number / database name? Parameter1 & parameter2 & parameter3

// oracle -- 1521
// jdbc:oracle:thin:@localhost:1521:sid

Statement object executing SQL PrepareStatement object executing SQL

String sql = "SELECT * FROM `users`";

statement.executeQuery();  // The query operation returns a ResultSet
statement.execute(); // Execute any SQL
statement.executeUpdate(); // Update, insert, delete. This is used to return the number of affected rows

ResultSet query result set: encapsulates all query results

Gets the specified data type

resultSet.getObject(); // Use without knowing the column type
// If you know the type of column, use the type of column
resultSet.getShort();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getObject();
...

Traversal, pointer

resultSet.beforeFirst(); // Move to the front
resultSet.afterLast(); // Move to the back
resultSet.next(); // Move to next data
resultSet.previous(); // Move to previous line
resultSet.absolute(row); // Move to specified row

Release resources

// 6. Release connection
resultSet.close();
statement.close();
connection.close(); // Turn off when you run out of resources

10.4 statement object

The statement object in JDBC is used to send SQL statements to the database. To complete the addition, deletion, modification and query of the database, you only need to send the addition, deletion, modification and query statements to the database through this object.

The executeUpdate method of the Statement object is used to send the sql Statement of addition, deletion and modification to the database. After the executeUpdate is executed, an integer will be returned (that is, the addition, deletion and modification Statement causes several rows of data in the database to change).

The Statement.executeQuery method is used to send query statements to the database, and the executeQuery method returns the ResultSet object representing the query results.

CRUD operation - create

Use the executeUpdate(String sql) method to complete the data addition operation. The exemplary operations are as follows:

Statement statement = connection.createStatement();
String sql = "insert into user(...) values(...)";
int num = statement.executeUpdate(sql);
if(num > 0) {
    System.out.println("Insert succeeded!!");
}

CRUD operation - delete

Use the executeUpdate(String sql) method to delete data. The following are examples:

Statement statement = connection.createStatement();
String sql = "delete from user where id = 1";
int num = statement.executeUpdate(sql);
if(num > 0) {
    System.out.println("Delete succeeded!!");
}

CRUD operation - update

Use the executeUpdate(String sql) method to complete the data modification operation. The demonstration operations are as follows:

Statement statement = connection.createStatement();
String sql = "update user set name = '' where name = ''";
int num = statement.executeUpdate(sql);
if(num > 0) {
    System.out.println("Modified successfully!!");
}

CRUD operation - read

Use the executeUpdate(String sql) method to complete the data query operation. The demonstration operations are as follows:

Statement statement = connection.createStatement();
String sql = "select * from user where id=1";
ResultSet rs = statement.executeQuery(sql);
while(rs.next()) {
    // According to the data type of the obtained column, the corresponding methods of rs are called to map to the java object
}

code implementation

1. Extraction tool class

(1) . write db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=root

(2) . implement tool class JdbcUtils

package com.niu.lesson02.utils;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;


    static {
        try{
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            // 1. The driver is loaded only once
            Class.forName(driver);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // Get connection
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    // Release connection
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }


}

2. Write the method of addition, deletion and modification, executeUpdate

(1) . add TestInsert

package com.niu.lesson02;

import com.niu.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestInsert {
    public static void main(String[] args) {

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection(); // Get database connection
            st = conn.createStatement(); // Get the execution object of SQL
            String sql = "INSERT INTO `users`(`id`, `name`, `password`, `email`, `birthday`) \n" +
                    "VALUES(4, 'jiahui', '123456', 'jiahui@163.com', '1998-12-01')";

            int i = st.executeUpdate(sql);
            if (i > 0) {
                System.out.println("Insert succeeded!");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }

    }
}

(2) Delete, TestDelete

package com.niu.lesson02;

import com.niu.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestDelete {
    public static void main(String[] args) {

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection(); // Get database connection
            st = conn.createStatement(); // Get the execution object of SQL
            String sql = "DELETE FROM `users` WHERE id = '4'";

            int i = st.executeUpdate(sql);
            if (i > 0) {
                System.out.println("Delete succeeded!");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }

    }
}

(3) , change TestUpdate

package com.niu.lesson02;

import com.niu.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestUpdate {
    public static void main(String[] args) {

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection(); // Get database connection
            st = conn.createStatement(); // Get the execution object of SQL
            String sql = "UPDATE `users` SET `name` = 'lixiaofeng', `email` = '127836@163.com' WHERE id = 3";

            int i = st.executeUpdate(sql);
            if (i > 0) {
                System.out.println("Update succeeded!");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }

    }
}

3. Query executeQuery

package com.niu.lesson02;

import com.niu.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestSelect {
    public static void main(String[] args) {

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection(); // Get database connection
            st = conn.createStatement();  // Get the execution object of SQL
            String sql = "select * from users";

            rs = st.executeQuery(sql);  // A result set will be returned after the query
            while (rs.next()) {
                System.out.println("name = " + rs.getObject("name"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
}

Project specific structure diagram

SQL injection problem

SQL has a vulnerability. It will be attacked, resulting in data leakage and SQL will be spliced

package com.niu.lesson02;

import com.niu.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQL injection {

    public static void main(String[] args) {
        login("' or '1=1", "'or '1=1");  // Skill input

    }

    public static void login(String username, String password) {

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection(); // Get database connection
            st = conn.createStatement();  // Get the execution object of SQL
            String sql = "select * from users where name = '" + username + "' and password = '" + password + "'";

            rs = st.executeQuery(sql);  // A result set will be returned after the query
            while (rs.next()) {
                System.out.println("Login successful!!");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }

    }

}

10.5 PreparedStatement object

PreparedStatement prevents SQL injection. More efficient!

1. Add

package com.niu.lesson03;

import com.niu.lesson02.utils.JdbcUtils;
import java.util.Date;

import java.sql.*;

public class TestInsert {

    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();   // Get connection

            // difference
            // use? Placeholder instead of parameter
            String sql = "INSERT INTO `users`(`id`, `name`, `password`, `email`, `birthday`)" + "VALUES(?,?,?,?,?)";

            st = conn.prepareStatement(sql);    // Precompiled sql, write sql first, and then do not execute

            // Assign parameters manually
            st.setInt(1, 5);
            st.setString(2, "niu");
            st.setString(3, "123456");
            st.setString(4, "34570@163.com");
            // Note: sql.Date database (Java. sql.Date)
            //       Util.date Java new date(). Gettime() gets the timestamp
            st.setDate(5, new java.sql.Date(new Date().getTime()));

            // implement
            int i = st.executeUpdate();
            if (i > 0) {
                System.out.println("Insert succeeded!!");
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }

    }

}

2. Delete

package com.niu.lesson03;

import com.niu.lesson02.utils.JdbcUtils;

import java.sql.*;

public class TestDelete {

    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();   // Get database connection

            String sql = "delete from users where id = ?";

            st = conn.prepareStatement(sql);  // Precompile sql and then do not execute

            st.setInt(1, 4);
            int i = st.executeUpdate();     // Execute sql
            if (i > 0) {
                System.out.println("Delete succeeded!!");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }

    }

}

3. Renew

package com.niu.lesson03;

import com.niu.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestUpdate {

    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();

            String sql = "update users set `name` = ? where id = ?;";
            st = conn.prepareStatement(sql);

            st.setString(1, "Luo Ji");
            st.setInt(2, 3);

            int i = st.executeUpdate();
            if (i > 0) {
                System.out.println("Modified successfully!");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }


    }

}

4. Inquiry

package com.niu.lesson03;

import com.niu.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestSelect {

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            // PreparedStatement prevents the essence of SQL injection from treating the parameters passed in as characters
            // Suppose there are escape characters, such as' will be directly escaped

            String sql = "select * from users where id = ?";   // Write SQL

            st = conn.prepareStatement(sql);    // precompile

            st.setInt(1, 3);    // Transfer parameters
            rs = st.executeQuery();     // implement

            while (rs.next()) {
                System.out.println("id:" + rs.getObject("id"));
                System.out.println("name:" + rs.getObject("name"));
                System.out.println("password:" + rs.getObject("password"));
                System.out.println("===============");
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }

    }
    
}

5. Prevent SQL injection

package com.niu.lesson03;

import com.niu.lesson02.utils.JdbcUtils;

import java.sql.*;

public class SQL injection {

    public static void main(String[] args) {
//        login("'' or '1=1'", "123456");  //  Skill input
        login("Luo Ji", "123456");  // Skill input

    }

    public static void login(String username, String password) {

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection(); // Get database connection
            // PreparedStatement prevents the essence of SQL injection from treating the parameters passed in as characters
            // Suppose there are escape characters, such as' will be directly escaped

            String sql = "select * from users where `name` = ? and `password` = ?";  // Mybatis
            st = conn.prepareStatement(sql);  // Get the execution object of SQL

            st.setString(1, username);
            st.setString(2, password);
            rs = st.executeQuery();  // A result set will be returned after the query
            while (rs.next()) {
                System.out.println("Login succeeded!!");
                System.out.println(rs.getObject("name"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }

    }

}

10.7. Use IDEA to connect to the database

Note here that you must import the previous package: mysql-connector-java-5.1.47.jar

After the connection is successful, you can select a database


Double click the database to view the table information

Update data

Write SQL

Connection failed, find the reason

10.8 affairs

Either all succeed or all fail

ACID principle

Atomicity: either complete or not complete

Consistency: the total number remains unchanged

Isolation: multiple processes do not interfere with each other

Persistence: once the commit is irreversible, it is persisted to the database

Isolation issues:

Dirty read: a transaction reads another uncommitted transaction

Non repeatable reading: in the same transaction, the data in the table is repeatedly read, and the table data changes

Virtual reading (unreal reading): in a transaction, data inserted by others is read, resulting in inconsistent results before and after reading

code implementation

1. Start transaction conn.setAutoCommit(false)

2. After a group of business is executed, submit the transaction

3. You can define the rollback statement displayed in the catch statement, but the default failure will rollback

/*Create account table*/
create table account(
    id int primary key auto_increment,
    name varchar(40),
    money float
);

/*Insert test data*/
insert into account(name, money) values('A', 1000);
insert into account(name, money) values('B', 1000);
insert into account(name, money) values('C', 1000);

package com.niu.lesson04;

import com.niu.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestTransaction1 {

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            // Turn off the automatic submission of the database and start the transaction automatically
            conn.setAutoCommit(false);      // Open transaction

            String sql1 = "update account set money = money - 100 where `name` = 'A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

           // int i = 1 / 0; 		//  report errors
            
            String sql2 = "update account set money = money + 100 where `name` = 'B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            // Business completion, commit transaction
            conn.commit();
            System.out.println("success!");

        } catch (SQLException throwables) {
            try {
                conn.rollback();       // Roll back the transaction if it fails
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }

    }

}

10.9. Database connection pool

Database connection - execution completed - release

Connection - release very wasteful system resources

Pooling Technology: prepare some pre prepared resources and connect the pre prepared resources

Minimum number of connections: 10

Maximum connections: 15

Waiting timeout: 100ms

Write a connection pool to implement an interface DataSourse

Implementation of open source data source (ready to use)

DBCP

C3P0

Druid: Alibaba

After using these database connection pools, we don't need to write code to connect to the database in the project development!

DBCP

Required jar package

commons-dbcp-1.4 , commons-pool-1.6

DBCP profile:

# File name: dbcpconfig.properties
# The name in the connection settings is defined in the DBCP data source
driver.ClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterCoding=utf8&useSSL=false
username=root
password=root

# Initialize connection
initialSize=10

# Maximum number of connections
maxActive=50

# Maximum idle connections
maxIdle=20

# Minimum idle connection
minIdle=5

# Timeout wait time in milliseconds 60000 MS / 1000 = 60 ms
maxWait=60000

#The format of the connection property attached when the JDBC driver establishes a connection must be: [property name = property;]
#Note: the user and password attributes will be explicitly passed, so there is no need to include them here.
connectionProperties=useUnicode=true;characterEncoding=UTF8

#Specifies the auto commit status of connections created by the connection pool.
defaultAutoCommit=true

#driver default specifies the read-only status of connections created by the connection pool.
#If this value is not set, the "setReadOnly" method will not be called. (some drivers do not support read-only mode, such as Informix)
defaultReadOnly=

#driver default specifies the transaction level (TransactionIsolation) of the connection created by the connection pool.
#The available values are one of the following: (see javadoc for details.) NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

Tools Utils:

package com.niu.lesson05.utils;

import org.apache.commons.dbcp.BasicDataSourceFactory;

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

public class JdbcUtilsDbcp {
    private static DataSource dataSource = null;

    static {
        try{
            InputStream in = JdbcUtilsDbcp.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);

            // Create data source factory pattern -- > create
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // Get connection
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();      // Get connection from data source
    }

    // Release connection
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }


}

Test code:

package com.niu.lesson05;

import com.niu.lesson02.utils.JdbcUtils;
import com.niu.lesson05.utils.JdbcUtilsDbcp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class TestDbcp {

    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtilsDbcp.getConnection();   // Get the connection. Replace it with Dbcp here

            // difference
            // use? Placeholder instead of parameter
            String sql = "INSERT INTO `users`(`id`, `name`, `password`, `email`, `birthday`)" +
                    "VALUES(?,?,?,?,?)";

            st = conn.prepareStatement(sql);    // Precompiled sql, write sql first, and then do not execute

            // Assign parameters manually
            st.setInt(1, 4);
            st.setString(2, "niu");
            st.setString(3, "123456");
            st.setString(4, "34570@163.com");
            // Note: sql.Date database (Java. sql.Date)
            //       Util.date Java new date(). Gettime() gets the timestamp
            st.setDate(5, new java.sql.Date(new Date().getTime()));

            // implement
            int i = st.executeUpdate();
            if (i > 0) {
                System.out.println("Insert succeeded!!");
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtilsDbcp.release(conn, st, rs);        // Change here to Dbcp
        }

    }

}

C3P0

Required jar package

c3p0-0.9.5.5.jar,mchange-commons-java-0.2.19.jar

C3P0 configuration file

<?xml version="1.0" encoding="UTF-8" ?>

<c3p0-config>
    <!--c3p0 Default (default) configuration for
    If in code ComboPooledDataSource ds=new ComboPooledDataSource();This means that c3p0 Default for (default)-->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false</property>
        <property name="user">root</property>
        <property name="password">root</property>

        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>

    <!--c3p0 Named configuration for
    If in code ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");This means that name yes MySQL Configuration of-->
    <named-config name="MySQL">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false</property>
        <property name="user">root</property>
        <property name="password">root</property>

        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>
</c3p0-config>

Utility class Utils

package com.niu.lesson05.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

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

public class JdbcUtilsC3p0 {
    private static ComboPooledDataSource dataSource = null;

    static {
        try{
            // Code version configuration
//            dataSource = new ComboPooledDataSource();
//            dataSource.setDriverClass();
//            dataSource.setUser();
//            dataSource.setPassword();
//            dataSource.setJdbcUrl();
//
//            dataSource.setMaxPoolSize();
//            dataSource.setMinPoolSize();

            // Create data source factory pattern -- > create
            dataSource = new ComboPooledDataSource("MySQL");    // Configuration file writing
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // Get connection
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();      // Get connection from data source
    }

    // Release connection
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }

}

Test code:

package com.niu.lesson05;

import com.niu.lesson05.utils.JdbcUtilsC3p0;
import com.niu.lesson05.utils.JdbcUtilsDbcp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class TestC3p0 {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtilsC3p0.getConnection();   // Get the connection. Here it is changed to C3P0

            // difference
            // use? Placeholder instead of parameter
            String sql = "INSERT INTO `users`(`id`, `name`, `password`, `email`, `birthday`)" +
                    "VALUES(?,?,?,?,?)";

            st = conn.prepareStatement(sql);    // Precompiled sql, write sql first, and then do not execute

            // Assign parameters manually
            st.setInt(1, 7);
            st.setString(2, "niu");
            st.setString(3, "123456");
            st.setString(4, "34570@163.com");
            // Note: sql.Date database (Java. sql.Date)
            //       Util.date Java new date(). Gettime() gets the timestamp
            st.setDate(5, new java.sql.Date(new Date().getTime()));

            // implement
            int i = st.executeUpdate();
            if (i > 0) {
                System.out.println("Insert succeeded!!");
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtilsC3p0.release(conn, st, rs);        // Change here to C3P0
        }

    }
}

summary

No matter what data source is used, the essence is the same. The DataSource interface will not change, and the method will not change