JDBC overview

Basic introduction

Simulate JDBC

public class TestJdbc {
    public static void main(String[] args) {
        JdbcInterface jdbcInterface = new MysqlJdbcImpl();
        //Complete the operation of mysql
        jdbcInterface.getConnection();
        jdbcInterface.crud();
        jdbcInterface.close();
    }
}
//jdbc interface specified by java manufacturer
public interface JdbcInterface {
    //connect
    public Object getConnection();

    //CRUD
    public void crud();

    //Close connection
    public void close();
}
//mysql database implements jdbc interface (developed by mysql manufacturer)
public class MysqlJdbcImpl implements JdbcInterface {
    @Override
    public Object getConnection() {
        System.out.println("obtain mysql Connection of");
        return null;
    }

    @Override
    public void crud() {
        System.out.println("complete mysql Add, delete, modify and check");
    }

    @Override
    public void close() {
        System.out.println("close mysql Connection of");
    }
}

Benefits of JDBC

• JDBC is a set of interface API provided by java for database operation. java programmers only need to program for this set of interface Different database manufacturers need to provide different implementations for this set of interfaces
• If java directly accesses the database: feasible, but not recommended, because it means that java applications do not have better portability

JDBC API

JDBC quick start

JDBC programming steps: (execute the pre work steps first)

1. Register Driver - load Driver class (this class is in the com.mysql.jdbc.Driver path under the Driver package)
2. Get Connection – get Connection
3. Execute add, delete, modify query – send SQL to mysql for execution
4. Free resources -- close related connections

Front work:

• Create a folder under the project, such as libs
• Add mysql-connector-java-5.1.37-bin Copy the jar to this directory and click add to project... To add it to the project
• The jar package is the implementation of mysql database for java interface

//jdbc program to complete simple operation
public class Jdbc01 {
    public static void main(String[] args) throws SQLException {
        //Front work: create a folder under the project, such as libs
        // Add mysql Copy jar to this directory and click add to project Add to project
        //1. Register driver

        Driver driver = new Driver();//com.mysql.jdbc.Driver

        //2. Get connected
        //(1) jdbc:mysql: / / specify the presentation protocol and connect mysql through jdbc
        //(2) localhost host, which can be an ip address
        //(3) 3306 indicates the port that mysql listens to
        //(4) hsp_ Which database does db02 connect to mysql dbms
        //(5) The connection essence of MySQL is the socket connection learned earlier
        String url = "jdbc:mysql://localhost:3306/db02";
        //Put the user name and password into the Properties object
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "root");
        Connection connect = driver.connect(url, properties);
        //3. Execute sql
        String sql = "insert into actor values(null,'Lau Andy','male','1970-11-11','110')";
        sql = "update actor set name='Zhou Xingchi' where id=1";
        sql = "delete from actor where id=1";
        //Statement an object used to execute a static SQL statement and return the results it generates
        Statement statement = connect.createStatement();
        int rows = statement.executeUpdate(sql);//Execute sql statement. If it is dml statement, the number of affected rows will be returned

        System.out.println(rows > 0 ? "success" : "fail");

        //4. Close the connection resource
        statement.close();
        connect.close();
    }
}

Get database connection (5 ways)

• Method 1: register Driver (create Driver object) – > store user and password in configuration file (properties) – > use Driver Connect (URL, properties)
• Method 2: register the Driver (dynamically load the Driver class through reflection – > newinstance() - create the Driver object) – > store the user and password in the configuration file (properties) – > use the method Driver Connect (URL, properties)
• Method 3: create an object through reflection – > register the driver through the DriverManager static method – > get connected through the DriverManager static method
• Method 4: use reflection to load Driver – > class to load and execute static code block to complete registration – > get connection through DriverManager static method
• Method 5: add configuration file – > read URL, user, password and driver parameters from the configuration file – > perform the steps of method 4

Summary:

No matter which of the five methods, the driver is finally registered through the DriverManager static method, which is embodied in two ways:

• Indirect method: create or load the object of Driver class directly or through reflection, so as to load the Driver class, execute the static code block in its class [as shown below], and call the static method of DriverManager to register the Driver
• Direct method: without creating a Driver object, automatically call the class name under the Driver package through class Forname() to register

Tips:

• MySQL driver 5.1.6 does not need class forName(“com.mysql.jdbc.Driver”);
• From jdk1 After 5, JDBC 4 is used, and there is no need to display the calling class Forname() registers the driver, but automatically calls meta-inf \ services \ java.exe under the driver jar package sql. Register the class name in the driver text
• It is suggested to write class Forname ("com.mysql.jdbc.Driver"), more explicit

//Five ways of connecting mysql with java
public class JdbcConn {
    //Mode 1
    @Test
    public void connect01() throws SQLException {
        Driver driver = new Driver();//Create driver object
        //Connect to ip + Database
        String url = "jdbc:mysql://localhost:3306/db02";
        //Save mysql user name and password
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "root");
        //Get connected
        Connection connect = driver.connect(url, properties);
        System.out.println(connect);
    }

    //Mode 2
    @Test
    public void connect02() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        //The Driver class is dynamically loaded through reflection, which is more flexible
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) aClass.newInstance();

        String url = "jdbc:mysql://localhost:3306/db02";

        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "root");
        Connection connect = driver.connect(url, properties);
        System.out.println(connect);
    }
    //Method 3 use DriverManager to replace driver for unified management
    @Test
    public void connect03() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) aClass.newInstance();

        String url = "jdbc:mysql://localhost:3306/db02";
        String password = "root";
        String user = "root";

        //Register drivers through the DriverManager static method
        DriverManager.registerDriver(driver);
        //Get connected through DriverManager static method
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
    }
    //Mode 4 Driver class automatically registers the driver when loading, which is simplified and recommended
    @Test
    public void connect04() throws ClassNotFoundException, SQLException {
        //Use reflection to load Driver, prompt: jdk1 5 after you start using JDBC 4, you don't need to load it and call it automatically
        Class.forName("com.mysql.jdbc.Driver");
        //Class loading and executing static code block to complete registration
        /*static {
            try {
                DriverManager.registerDriver(new Driver());
            } catch (SQLException var1) {
                throw new RuntimeException("Can't register driver!");
            }
        }*/
        String url = "jdbc:mysql://localhost:3306/db02";
        String password = "root";
        String user = "root";

        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("connection = " + connection);
    }
    //Method 5: add configuration files to make it more flexible
    @Test
    public void connect05() throws IOException, ClassNotFoundException, SQLException {
        //Get the configuration file information through the Properties object
        Properties properties = new Properties();
        properties.load(new FileReader("src\\mysql.properties"));
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");

        Class<?> aClass = Class.forName(driver);//Suggest writing
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("connection = " + connection);
    }
}

ResultSet result set

Basic introduction

debug view the internal structure of resultSet

As can be seen from the figure:

• The bottom layer of resultSet maintains the ArrayList set and stores multiple rows of records
• Each row of records is maintained by byte type two-dimensional array
• Each column (scalar) of this row is stored in byte []

Code implementation:

//Demonstrate that the select statement returns the result set and takes out the result
public class ResultSet_ {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        //Get the information of the configuration file through the Properties object
        Properties properties = new Properties();
        properties.load(new FileReader("chapter25\\src\\mysql.properties"));
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        //1. Register driver
        Class.forName(driver);
        //2. Get connected
        Connection connection = DriverManager.getConnection(url, user, password);
        //3. Get Statement
        Statement statement = connection.createStatement();
        //4. Organize SqL
        String sql = "select id,name,sex,borndate,phone from actor";

        //debug to get resultSet type: com mysql. jdbc. JDBC42resultSet@6e0e048a
        //There is rowdata -- > rows -- > elementdate in this object to store the data in each line of record
        //Execute the given SQL statement, which returns a single ResultSet object
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {// Move the cursor backward and return false if there are no more rows
            int id = resultSet.getInt(1);//Gets the first column of the row
//            int id = resultSet.getInt("id"); Get value by column name, recommended
            String name = resultSet.getString(2);
            String sex = resultSet.getString(3);
            Date date = resultSet.getDate(4);
            System.out.println(id+"\t"+name+"\t"+sex+"\t"+date);
        }
        //Close connection
        resultSet.close();
        statement.close();
        connection.close();
    }
}

Statement

Basic introduction

Demonstrate sql injection in mysql

create table admin
		(`name` varchar(32)not null unique,
		`pwd` varchar(32) not null default '')character set utf8;

insert into admin values('tom','123');
-- Find out if a management exists
select * from admin where `name`='tom' AND `pwd`='123';
-- sql injection:tom as 1' or , 123 as or '1'='1

select * from admin where `name`='1' or' AND `pwd`='or '1'='1';

explain:

When querying, you can use universal user name (1 'or) and password (or' 1 '='1) when entering the parameters of filter conditions

Demonstrating SQL injection in jdbc

//Demonstrate the injection problem of statement
public class Statement_ {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        Scanner sc = new Scanner(System.in);

        System.out.println("enter one user name:");//1' or
        String name = sc.nextLine();//next(): when a space or 'is received, it means the end. You can only use nextLine
        System.out.println("Please input a password:");// or '1' = '1
        String pwd = sc.nextLine();

        Properties properties = new Properties();
        properties.load(new FileReader("chapter25\\src\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        Class.forName(driver);

        Connection connection = DriverManager.getConnection(url, user, password);
        Statement statement = connection.createStatement();
        String sql = "select name,pwd from admin where name='"+name+"' and pwd='"+pwd+"'";
        ResultSet resultSet = statement.executeQuery(sql);

        if (resultSet.next()) { //If a record is queried, it indicates that the management exists
            System.out.println("congratulations,Login successful");
        } else {
            System.out.println("i 'm sorry,Login failed");
        }

        resultSet.close();
        statement.close();
        connection.close();

    }
}

PreparedStatement

Basic introduction

Pretreatment benefits

1. No longer use + to splice sql statements to reduce syntax errors
2. It effectively solves the problem of sql injection
3. The compilation times are greatly reduced and the efficiency is high

Query application cases:

//Demonstrate the use of PreparedStatement: effectively prevent statement injection
public class PreparedStatement_ {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        Scanner sc = new Scanner(System.in);

        System.out.println("enter one user name:");//1' or
        String name = sc.nextLine();//next(): when a space or 'is received, it means the end. You can only use nextLine
        System.out.println("Please input a password:");// or '1' = '1
        String pwd = sc.nextLine();

        Properties properties = new Properties();
        properties.load(new FileReader("chapter25\\src\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        Class.forName(driver);

        Connection connection = DriverManager.getConnection(url, user, password);
        //First organize the field value as? sql statement -- > used when constructing PreparedStatement object
        String sql = "select name,pwd from admin where name=? and pwd=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //Call method to? assignment
        preparedStatement.setString(1, name);
        preparedStatement.setString(2, pwd);
        //When executing the select statement, you can no longer fill in sql as a parameter
        ResultSet resultSet = preparedStatement.executeQuery();


        if (resultSet.next()) { //If a record is queried, it indicates that the management exists
            System.out.println("congratulations,Login successful");
        } else {
            System.out.println("i 'm sorry,Login failed");
        }

        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

DML case:

//Demonstrate preprocessing using dml statements
    @SuppressWarnings({"all"})
public class PreparedStatementDML_ {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        Scanner sc = new Scanner(System.in);

        System.out.println("enter one user name:");//1' or
        String name = sc.nextLine();//next(): when a space or 'is received, it means the end. You can only use nextLine
        System.out.println("Please input a password:");// or '1' = '1
        String pwd = sc.nextLine();

        Properties properties = new Properties();
        properties.load(new FileReader("chapter25\\src\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        Class.forName(driver);

        Connection connection = DriverManager.getConnection(url, user, password);
        //First organize the field value as? sql statement -- > used when constructing PreparedStatement object
        String sql = "select name,pwd from admin where name=? and pwd=?";
        sql = "insert into admin values(?,?)";
        sql = "update admin set pwd=? where name=?";//Notice the change in the order in which the two fields are assigned
        sql = "delete from admin where name =?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //Call method to? assignment
        preparedStatement.setString(1, name);
        preparedStatement.setString(2, pwd);
        //Execute DML statement
        int update = preparedStatement.executeUpdate();

        System.out.println(update>0?"Successful execution":"The number of rows affected is 0");

        
        preparedStatement.close();
        connection.close();

    }
}

Summary of JDBC related API s

Summary: the above interfaces are imported in mysql-connector-java-5.1.37-bin Jar, which is provided by mysql

Encapsulating JDBC utils

In JDBC operation, obtaining connection and releasing resources are often used, which can be encapsulated into the tool class of JDBC connection: JDBC utils

Function: complete mysql connection and close resources

Code implementation:

//Tool class to complete the connection and closing of mysql resources (because the connection and closing are performed only once, using static members)
public class JDBCUtils {
    private static String url;
    private static String user;
    private static String pwd;
    private static String driver;

    //Initialize read attribute value
    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileReader("src\\mysql.properties"));
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            pwd = properties.getProperty("password");
            driver = properties.getProperty("driver");
        } catch (IOException e) {
            //In actual development, we can deal with it like this
            //1. Turn compile exception into run exception
            //The exception can be captured by default or handled by the caller
            throw new RuntimeException(e);
        }
    }

    //Get connected
    public static Connection getConnect() {
        try {
            return DriverManager.getConnection(url, user, pwd);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    //Close related resources (if no object is passed in null)
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}

• Use the JDBCUtils tool class to complete CRUD

//Demonstrate the use of JDBC utils tool class to complete dml and select
public class JDBCUtils_Use {
    @Test
    public void testSelect() throws SQLException {//If you use trycatch capture, pay attention to the scope of the reference (variable) of the resource to be closed
        //Use the tool class static method to get the connection
        Connection connect = JDBCUtils.getConnect();
        System.out.println(connect.getClass());//(implementation class of Collection in MySQL) class com mysql. jdbc. JDBC4Connection
        //sql
        String sql = "select id,name,sex,borndate,phone from actor where id=?";
        PreparedStatement preparedStatement = connect.prepareStatement(sql);
        //? assignment
        preparedStatement.setInt(1,2);
        //Query (1) to obtain the result set
        ResultSet resultSet = preparedStatement.executeQuery();
        System.out.println(resultSet.getClass());
        //Pointer down
        resultSet.next();

        //Processing result set
        int id = resultSet.getInt("id");
        String name = resultSet.getString("name");
        String sex = resultSet.getString("sex");
        Date borndate = resultSet.getDate("borndate");
        String phone = resultSet.getString("phone");
        System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
        //close resource
        JDBCUtils.close(resultSet, preparedStatement, connect);
    }

    @Test
    public void testDML() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnect();
            String sql = "update actor set name='Zhou Xingchi' where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 2);
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }
}

affair

Basic introduction

Application examples

Simulate classic transfer business

• Possible problems with not using transactions: by default, connection is automatically submitted by default, resulting in an exception after one sql statement is executed. Due to the characteristics of try catch, the subsequent sql statements are not executed, resulting in transfer errors
• Using transactions to maintain consistency, execute all or fallback all, this will not happen

Code implementation:

//Using transactions in jdbc
public class Transaction_ {
    @Test
    public void noTransaction() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnect();
            String sql = "update account set balance=balance-100 where id=1";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();//This method has no parameters. By default, it executes the sql in the method that obtains the preparedstatement by the connection
            int i=1/0;//Transfer failed, Huateng lost 100 yuan
            String sql2 = "update account set balance=balance+100 where id=2";
            preparedStatement.executeUpdate(sql2);//This method gives priority to executing its own sql2
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }

    @Test
    public void transaction() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //By default, connection is automatically submitted by default
            connection = JDBCUtils.getConnect();
            //Open transaction
            connection.setAutoCommit(false);

            String sql = "update account set balance=balance-100 where id=1";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();//This method has no parameters. By default, it executes the sql in the method that obtains the preparedstatement by the connection
            int i=1/0;//If the transfer fails, the transaction will be terminated, and Huateng will make money without loss
            String sql2 = "update account set balance=balance+100 where id=2";
            preparedStatement.executeUpdate(sql2);//This method gives priority to executing its own sql2

            //Commit transaction
            connection.commit();
        } catch (Exception e) {
            //Here we can roll back, that is, undo the executed SQL
            //The default rollback is to the state where the transaction started
            System.out.println("An exception occurred during execution. The execution was revoked sql");
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }
}

Batch processing

Basic introduction

Application examples

Preparation: create tables in mysql

create table admin2(
		id int primary key auto_increment,
		username varchar(32) not null,
		`password` varchar(32) not null);

jdbc code implementation:

//Batch processing of jdbc
//Summary: execute multiple SQL statements:
//Method 1: after executing the sql every time,
//-->Just update the content of the sql statement (assign a new value to sql or change the sql parameters with setString and other methods)
//-->Then execute the changed sql
//Method 2: add (addBatch(Str/no paramters)) modified sql to the set and execute in batch

public class Batch_ {
    @SuppressWarnings({"all"})
    @Test
    public void noBatch() throws SQLException {
        Connection connect = JDBCUtils.getConnect();
        String sql = "insert into admin2 values(null ,?,?)";
        PreparedStatement preparedStatement = connect.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            preparedStatement.setString(1, "jack[" + i + "]");
            preparedStatement.setString(2, "123");
            preparedStatement.executeUpdate();
        }
        long end = System.currentTimeMillis();
        System.out.println("Time consuming without batch processing:" + (end - start));//6358ms
        JDBCUtils.close(null, preparedStatement, connect);
    }

    @Test//Don't forget to change the url parameter of the configuration file? rewriteBatchedStatements=true
    public void batch() throws SQLException {
        Connection connect = JDBCUtils.getConnect();
        String sql = "insert into admin2 values(null ,?,?)";
        PreparedStatement preparedStatement = connect.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            preparedStatement.setString(1, "jack[" + i + "]");
            preparedStatement.setString(2, "123");
            //Add the sql statement to the batch package - > see the source code
            /*
            //1. //First, create ArrayList - elementdata = > Object []
            //2. elementData => Object[] It will store our preprocessed sql statements
            //3. When the elementData is full, expand the capacity according to 1.5
            //4. When added to the specified value, executeBatch is executed
            //5. Batch processing will reduce the network overhead of sending sql statements and reduce the number of compilations, so the efficiency is improved
            public void addBatch() throws SQLException {
                synchronized(this.checkClosed().getConnectionMutex()) {
                    if (this.batchedArgs == null) {

                        this.batchedArgs = new ArrayList();
                    }

                    for(int i = 0; i < this.parameterValues.length; ++i) {
                        this.checkAllParametersSet(this.parameterValues[i], this.parameterStreams[i], i);
                    }

                    this.batchedArgs.add(new PreparedStatement.BatchParams(this.parameterValues, this.parameterStreams, this.isStream, this.streamLengths, this.isNull));
                }
            }

             */

            preparedStatement.addBatch();//Put SQL statements in preparedStatement into collection
            if ((i + 1) % 1000 == 0) {
                preparedStatement.executeBatch();
                preparedStatement.clearBatch();
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("Time consuming to use batch processing:" + (end - start));//142ms
        JDBCUtils.close(null, preparedStatement, connect);
    }
}

Database connection pool

Problem elicitation

5k connection to database:

1. Write a program to complete the operation of connecting MySQL 5000 times
2. See what's wrong and how long it takes Database connection pool

//Test the speed of traditional connection
public class ConQuestion {
    @Test
    public void testCon() {
        System.out.println("Start connection...");
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            Connection connect = JDBCUtils.getConnect();//If you do not close the connection: Too many connections

            JDBCUtils.close(null,null,connect);
        }
        long end = System.currentTimeMillis();
        System.out.println("The traditional method takes 5000 times:"+(end-start));//13197ms
    }
}

problem analysis

Database connection pool type

Note: after using the third-party database connection pool, the connection pool, connection and other interfaces and the corresponding methods of the interface are changed to be implemented by the third party (in the jar driver package)

C3P0 application example

Front work:

• C3p0-0.9.1.2 Copy the jar to the specified directory (libs) under the project, click add to project... And add it to the project. The package provides the implementation of the database connection pool interface – > the implementation type of the database connection pool interface becomes com mchange. v2. c3p0. Combopooleddatasource – > the implementation type of the connection interface becomes com mchange. v2. c3p0. impl. NewProxyConnection
• Set c3p0 config XML is copied to the project directory (src), which specifies the relevant parameters (url,user,password,driver and drive path) of the connection database and connection pool

//Demonstrate using c3p0
public class C3P0_ {
    //Method 1: relevant parameters, specify user, URL, password, etc. in the program
    @Test
    public void testC3P0_01() throws Exception {
        //1. Create a data source object
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();//com.mchange.v2.c3p0.ComboPooledDataSource
        //2. Through the configuration file mysql Properties to get information about the connection
        Properties properties = new Properties();
        properties.load(new FileReader("src\\mysql.properties"));

        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        //Set relevant parameters for the data source comboPooledDataSource
        //Note: connection management is managed by the comboPooledDataSource
        comboPooledDataSource.setDriverClass(driver);
        comboPooledDataSource.setJdbcUrl(url);
        comboPooledDataSource.setUser(user);
        comboPooledDataSource.setPassword(password);
        //Set the number of initialization connections
        comboPooledDataSource.setInitialPoolSize(10);
        //maximum connection
        comboPooledDataSource.setMaxPoolSize(50);

        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            Connection connection = comboPooledDataSource.getConnection();

            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("c3p0 5000 connections time consuming:"+(end-start));//1119

    }

    //The second way is to use the profile template
    //1. C3p0 provided by c3p0 config. Copy XML to src directory
    //2. This file specifies the relevant parameters for connecting to the database and connection pool
    @Test
    public void testc3p0_02() throws SQLException {
        //Tip: the constructor parameter of this method cannot be empty and is consistent with the named config name of the configuration file
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("hello");
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            Connection connection = comboPooledDataSource.getConnection();

            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("c3p0 5000 connections time consuming:"+(end-start));//1018
    }
}

Druid application example

Front work:

• Add druid-1.1.10 Copy the jar to the specified directory (libs) under the project, click add to project... And add it to the project. The package provides the implementation of the database connection pool interface – > the implementation type of the database connection pool interface becomes com alibaba. druid. pool. Druiddatasource – > the implementation type of the connection interface becomes com alibaba. druid. pool. DruidPooledConnection
• Set Druid Copy the properties to the project directory (src), which specifies the relevant parameters (url,user,password,driver, drive path) for connecting to the database and connection pool

use:

//Use of Druid database connection management
public class Druid_ {
    @Test
    public void testDruid() throws Exception {
        Properties properties = new Properties();
        properties.load(new FileReader("src\\druid.properties"));

        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            Connection connection = dataSource.getConnection();

            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("druid 5000 connections time consuming:"+(end-start));//741
    }
}

Druid tool class

Description: change the JDBC utils tool class to Druid (Druid) implementation

Function: create a data source object – > through the configuration file Druid Properties get the information about the connection – > get the connection reference – > close the connection (put the object referenced by the connection back to the connection pool)

//Tool class based on druid database connection pool
public class JDBCUtilsByDruid {
    private static DataSource dataSource;

    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileReader("src\\druid.properties"));

            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        Connection connection=null;
        try {
            connection=dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                //Connection is an interface. Due to different third parties, the implementation classes of Collection are different
                //The closing mechanism here is provided by the original Collection implementation class in mysql
                //Instead, the implementation class in Druid provides a closing mechanism
                //The difference between the two: the latter breaks the reference and puts the collection object back into the connection pool
                //The former: directly disconnect the JDBC database
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

Using the Druid utility class

public class JDBCUtilsByDruid_USE {
    @Test
    public void testSelect() throws SQLException {//If you use trycatch capture, pay attention to the scope of the reference (variable) of the resource to be closed
        //Use the tool class static method to get the connection
        Connection connect = JDBCUtilsByDruid.getConnection();
        System.out.println(connect.getClass());//(implementation class of Collection in Druid) class com alibaba. druid. pool. DruidPooledConnection
        //sql
        String sql = "select id,name,sex,borndate,phone from actor where id=?";
        PreparedStatement preparedStatement = connect.prepareStatement(sql);
        //? assignment
        preparedStatement.setInt(1,2);
        //Query (1) to obtain the result set
        ResultSet resultSet = preparedStatement.executeQuery();

        //If you close the connection before operating the result set, that is, close the result set, you will get an exception
        //-->Operation not allowed after ResultSet closed
        //-->Therefore, the read results should be encapsulated in the collection before closing

        //Pointer down
        resultSet.next();
        //Processing result set
        int id = resultSet.getInt("id");
        String name = resultSet.getString("name");
        String sex = resultSet.getString("sex");
        Date borndate = resultSet.getDate("borndate");
        String phone = resultSet.getString("phone");
        System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
        //close resource
        JDBCUtilsByDruid.close(resultSet, preparedStatement, connect);
    }
}

Apache-DBUtils

Analyze problems

1. The resultSet result set cannot be used after the connection is closed
2. resultSet is not conducive to data management

Try to solve it yourself

Manually encapsulate records: create a set – > the records in the resultSet will be read cyclically and saved in the set created in advance

@Test
public void testSelectToArrayList() throws SQLException {
    ArrayList<Actor> list = new ArrayList<>();
    Connection connection = JDBCUtilsByDruid.getConnection();
    String sql = "select * from actor where id>?";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setInt(1,0);
    ResultSet resultSet = preparedStatement.executeQuery();
    while (resultSet.next()) {
        int id = resultSet.getInt("id");
        String name = resultSet.getString("name");
        String sex = resultSet.getString("sex");
        Date borndate = resultSet.getDate("borndate");
        String phone = resultSet.getString("phone");
        list.add(new Actor(id, name, sex, borndate, phone));
    }
    JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);
    System.out.println(list);
}

Tool introduction

Functions: implicitly compile pre-processing SQL statements – > after executing SQL statements, implicitly process CRUD results (encapsulate query records) and directly return the set / object storing records – > implicitly close the result set – > implicitly close the preparedstatement

Tip: when using DBUtils and Druid tool classes at the same time, it is not necessary to display the closed result set and preparedstatement

Front work:

Add commons-dbutils-1.3 Copy the jar to the specified directory (libs) under the project, and click add to project... To add it to the project

Application example:

//Use Apache dbutils tool class + druid to complete the crud operation on the table
public class DBUtils_USE {
    @Test
    //If the returned result is multiple rows
    public void selectManyTest() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2. Using DBUtils class and interface, first introduce the jar s related to DBUtils and add them to the Project
        //3. Create QueryRunner
        QueryRunner queryRunner = new QueryRunner();//org.apache.commons.dbutils
        String sql = "select * from actor where id>?";
        //(1) The query method is to execute the sql statement to get the resultset -- encapsulated in the -- > ArrayList set
        //(2) Return set
        //(3) connection: connection
        //(4) sql: executed sql statement
        //(5) New beanlisthandler < > (actor. Class): encapsulate resultset - > actor object - > into ArrayList
        //    The underlying layer uses reflection mechanism to obtain the attributes of Actor class, and then encapsulates them
        //(6) Is given in sql statement 1? Assignment can have multiple values, because it is a variable parameter object params
        //(7) The resultset obtained from the bottom layer will be closed in query and Preparedstatement will be closed
        /**
         * Analyze queryrunner Query method:
         * public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
         *         PreparedStatement stmt = null;//Define PreparedStatement
         *         ResultSet rs = null;//Receive the returned ResultSet
         *         Object result = null;//Return to ArrayList
         *
         *         try {
         *             stmt = this.prepareStatement(conn, sql);//Create PreparedStatement
         *             this.fillStatement(stmt, params);//Query sql? assignment
         *             rs = this.wrap(stmt.executeQuery());//Execute sql and return resultset
         *             result = rsh.handle(rs);//Returned resultset -- > ArrayList [result] [use reflection to process the incoming class object]
         *         } catch (SQLException var33) {
         *             this.rethrow(var33, sql, params);
         *         } finally {
         *             try {
         *                 this.close(rs);//Close resultset
         *             } finally {
         *                 this.close((Statement)stmt);//Close the preparedstatement object
         *             }
         *         }
         *
         *         return result;
         *     }
         */
        List<Actor> list = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 0);
        System.out.println(list);
        JDBCUtilsByDruid.close(null,null,connection);
    }
    @Test
    //The returned result is a single line
    public void selectSingle() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from actor where id=?";
        // Because we return a single line record < --- > single object, the handler used is BeanHandler
        Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 3);
        System.out.println(actor);
        JDBCUtilsByDruid.close(null,null,connection);
    }
    @Test
    //The return result is scalar (single row and single column)
    public void selectsaclar() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select name from actor where id=?";
        //Return single row and single column, return Object, and use ScalarHandler as handler
        Object o = queryRunner.query(connection, sql, new ScalarHandler(),3);
        System.out.println(o);
        JDBCUtilsByDruid.close(null,null,connection);
    }
    //Demonstrate that Apache dbutils + Druid completes DML (update, insert, delete)
    @Test
    public void dml() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        String sql = "update actor set name=? where id=?";
        sql = "insert into actor values(null,?,?,?,?)";
        sql = "delete from actor where id=?";
        QueryRunner queryRunner = new QueryRunner();
        //(1) The dml operation is queryrunner update()
        //(2) The returned value is the number of rows affected (affected: affected)
        //        queryRunner.update(connection, sql, "Andy Lau", 2);
//        queryRunner.update(connection, sql, "dawn", "male", "1971-11-11", "114");
        int affectedRow = queryRunner.update(connection, sql, 2);
        System.out.println(affectedRow>0?"Successful execution":"Execution does not affect the table");

        JDBCUtilsByDruid.close(null,null,connection);
    }
}

Supplement: type mapping relationship between table and JavaBean

DAO

problem

explain

BasicDAO application instance

Code implementation:

domain layer:

package com.hspedu.dao_.domain;

import java.util.Date;
//javaBean/domain
public class Actor {
    private Integer id;
    private String name;
    private String sex;
    private Date borndate;
    private String phone;

    public Actor() {
    }

    public Actor(Integer id, String name, String sex, Date borndate, String phone) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.borndate = borndate;
        this.phone = phone;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Date getBorndate() {
        return borndate;
    }

    public void setBorndate(Date borndate) {
        this.borndate = borndate;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    @Override
    public String toString() {
        return "\nActor{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", borndate=" + borndate +
                ", phone='" + phone + '\'' +
                '}';
    }
}

utils layer

package com.hspedu.dao_.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

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

/**
 * @ClassName
 * @Description
 * @Author zxk
 * @DateTime 2022-02-19-21:24
 * @Version
 *///Tool class based on druid database connection pool
public class JDBCUtilsByDruid {
    private static DataSource dataSource;

    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileReader("src\\druid.properties"));

            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        Connection connection=null;
        try {
            connection=dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                //Connection is an interface. Due to different third parties, the implementation classes of Collection are different
                //The closing mechanism here is provided by the original Collection implementation class in mysql
                //Instead, the implementation class in Druid provides a closing mechanism
                //The difference between the two: the latter breaks the reference and puts the collection object back into the connection pool
                //The former: directly disconnect the JDBC database
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

dao layer

package com.hspedu.dao_.dao;

import com.hspedu.dao_.utils.JDBCUtilsByDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
//Develop BasicDAO, which is the parent class of other Daos
public class BasicDAO<T>{
    private QueryRunner qr = new QueryRunner();//Apache DB tool class
    private Connection connection ;

    //Develop a general dml method for any table
    public int update(String sql, Object... parameters) {
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.update(connection, sql, parameters);
        } catch (SQLException e) {
            //The compilation exception - > Run exception is thrown, which solves the compilation problem that the method cannot receive the returned statement (SCOPE)
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }

    //Return multiple objects (that is, the result of the query is multiple rows) for any table
    /**
     *
     * @param sql sql Statement, can there be?
     * @param clazz clazz Pass in the Class object of a Class, such as actor Class
     * @param parameters parameters Incoming? The specific value of can be multiple
     * @return According to actor Class returns the corresponding ArrayList collection
     */
    public List<T> queryMulti(String sql, Class<T> clazz, Object... parameters) {
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.query(connection, sql, new BeanListHandler<>(clazz), parameters);

        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }
    }

    //General method of querying single line results
    public T querySingle(String sql, Class<T> clazz, Object... parameters) {
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.query(connection,sql, new BeanHandler<>(clazz), parameters);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }

    //The method of querying single row and single column, that is, the method of returning single value
    public Object queryScalar(String sql, Object... parameters) {
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.query(connection, sql, new ScalarHandler(), parameters);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }

    }
}

package com.hspedu.dao_.dao;

import com.hspedu.dao_.domain.Actor;

public class ActorDAO extends BasicDAO<Actor> {
    //1. There is the method of BasicDAO
    //2. Special methods can be written according to business requirements
}

view layer (test)

package com.hspedu.dao_.test;

import com.hspedu.dao_.dao.ActorDAO;
import com.hspedu.dao_.domain.Actor;
import org.junit.jupiter.api.Test;

import java.util.List;

public class TestDAO {
    @Test
    public void testActorDAO() {
        ActorDAO actorDAO = new ActorDAO();
        //4. dml operation insert, update, delete
        int update = actorDAO.update("update actor set name='Zhou Xingchi' where id=?", 3);
        System.out.println(update>0?"Successful execution":"Number of rows not affected");
        //1. Query multi line records
        List<Actor> actors = actorDAO.queryMulti("select * from actor where id>?", Actor.class, 0);
        for (Actor actor : actors) {
            System.out.println(actor);
        }
        //2. Query single line records
        Actor actor = actorDAO.querySingle("select * from actor where id=?", Actor.class, 4);
        System.out.println(actor);
        //3. Query single line and single column
        Object o = actorDAO.queryScalar("select * from actor where id=?", 4);
        System.out.println(o);

    }
}