JDBC introduction

1. JDBC(Java Database Connectivity) is a common interface (a set of API s) independent of a specific database management system and general SQL database access and operation. It defines the standard Java class libraries used to access the database (java.sql,javax.sql). These class libraries can be used to access database resources in a standard and convenient way.
2. JDBC provides a unified way to access different databases and shields some details for developers.
3. The goal of JDBC is to enable Java programmers to use JDBC to connect to any database system that provides JDBC drivers, so that programmers do not need to know too much about the characteristics of a specific database system, which greatly simplifies and speeds up the development process.

JDBC programming steps

Get database connection

Element 1: Driver interface implementation class

• java. sql. The driver interface is the interface that all JDBC drivers need to implement. This interface is provided for database manufacturers. Different database manufacturers provide different implementations.

• In the program, you do not need to directly access the classes that implement the Driver interface, but the Driver manager class (java.sql.DriverManager) calls these Driver implementations.

  • Oracle driver: Oracle jdbc. driver. OracleDriver
  • mySql driver: com mySql. jdbc. Driver

Driver interface introduction

Loading and registering JDBC drivers

• Load driver: to load the JDBC driver, you need to call the static method forName() of Class class and pass the Class name of the JDBC driver to be loaded

  • Class.forName("com.mysql.jdbc.Driver");

• Register driver: DriverManager class is the driver manager class, which is responsible for managing drivers

  • Using drivermanager Register driver (COM. Mysql. JDBC. Driver) to register the driver

  • (automatic registration) it is usually not necessary to explicitly call the registerDriver() method of the DriverManager class to register an instance of the Driver class, because the Driver classes of the Driver interface contain static code blocks in which DriverManager will be called registerDriver() method to register an instance of itself. The following figure shows the source code of the Driver implementation class of MySQL:

Element 2: URL

Element 3: user name and password

• User and password can tell the database by "attribute name = attribute value"
• You can call the getConnection() method of the DriverManager class to establish a connection to the database

Example of database connection mode

public class ConnectionTest {

	// Mode 1:
	@Test
	public void test1() throws SQLException {
		// 1. Provide Java sql. The driver interface implements the object of the class
		Driver driver = new Driver(); // Do not import java sql. Driver (), which is the interface provided by java

		//2. Provide a url to indicate the specific operation data
		String url = "jdbc:mysql://192.168.56.10:3306/test";

		//3. Provide the object of Properties, indicating the user name and password
		Properties properties = new Properties();
		properties.setProperty("user","root");
		properties.setProperty("password","root");

		//4. Call the connect() of the driver to get the connection
		Connection connect = driver.connect(url, properties);
		System.out.println(connect); // com.mysql.jdbc.JDBC4Connection@67117f44

	}

	// Mode 2: iteration of mode 1: there is no third-party api in the following programs, so that the program has better portability
	@Test
	public void test2() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
		// 1. Provide Java sql. The driver interface implements the object of the class
		Class cls = Class.forName("com.mysql.jdbc.Driver");
		Driver driver = (Driver) cls.newInstance();

		//2. Provide a url to indicate the specific operation data
		String url = "jdbc:mysql://192.168.56.10:3306/test";

		//3. Provide the object of Properties, indicating the user name and password
		Properties properties = new Properties();
		properties.setProperty("user","root");
		properties.setProperty("password","root");

		//4. Call the connect() of the driver to get the connection
		Connection connect = driver.connect(url, properties);
		System.out.println(connect); // com.mysql.jdbc.JDBC4Connection@67117f44
	}

	// Method 3: register and manage drivers using DriverManager
	@Test
	public void test3() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
		// 1. Provide Java sql. The driver interface implements the object of the class
		Class cls = Class.forName("com.mysql.jdbc.Driver");
		Driver driver = (Driver) cls.newInstance();

		// 1. Register driver
		DriverManager.registerDriver(driver);

		//2. Provide a url to indicate the specific operation data
		String url = "jdbc:mysql://192.168.56.10:3306/test";

		//3. Provide the object of Properties, indicating the user name and password
		Properties properties = new Properties();
		properties.setProperty("user","root");
		properties.setProperty("password","root");

		//4. Call getConnection of DriverManager to get the connection
		//     public static Connection getConnection(String url,String user, String password) can be used without the properties class
		Connection connection = DriverManager.getConnection(url, properties);
		System.out.println(connection); // com.mysql.jdbc.JDBC4Connection@5d3411d

	}

	// Method 4: you can just load the driver without displaying the registered driver
	@Test
	public void test4() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
		// 1. Provide Java sql. The driver interface implements the object of the class
		Class cls = Class.forName("com.mysql.jdbc.Driver");
		Driver driver = (Driver) cls.newInstance();

//		// 1. Register driver
//		DriverManager.registerDriver(driver);
		//Why can the above operations be omitted?
		/*
		 * In the Driver implementation class of mysql, the following operations are declared:
		 * static {
				try {
					java.sql.DriverManager.registerDriver(new Driver());
				} catch (SQLException E) {
					throw new RuntimeException("Can't register driver!");
				}
			}
		 */

		//2. Provide a url to indicate the specific operation data
		String url = "jdbc:mysql://192.168.56.10:3306/test";

		//3. Provide the object of Properties, indicating the user name and password
		Properties properties = new Properties();
		properties.setProperty("user","root");
		properties.setProperty("password","root");

		//4. Call getConnection of DriverManager to get the connection
		//     public static Connection getConnection(String url,String user, String password) can be used without the properties class
		Connection connection = DriverManager.getConnection(url, properties);
		System.out.println(connection); // com.mysql.jdbc.JDBC4Connection@67117f44

	}


	//Mode 5 (final version): declare the four basic information required for database connection in the configuration file, and obtain the connection by reading the configuration file
	/*
	 * What are the benefits of this approach?
	 * 1.It realizes the separation of data and code. Decoupling is realized
	 * 2.If you need to modify the configuration file information, you can avoid repackaging the program.
	 */
	@Test
	public void test5() throws IOException, ClassNotFoundException, SQLException {
		// 1. Load configuration file
		InputStream resourceAsStream = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
		// 2. Read configuration information
		Properties properties = new Properties();
		properties.load(resourceAsStream);
		String user = properties.getProperty("user");
		String password = properties.getProperty("password");
		String url = properties.getProperty("url");
		String driverClass = properties.getProperty("driverClass");

		//3. Load drive
		Class.forName(driverClass);

		//4. Get connection
		Connection connection = DriverManager.getConnection(url, user, password);
		System.out.println(connection); // com.mysql.jdbc.JDBC4Connection@5679c6c6

	}
}

Implement CRUD operation

concept

• The database connection is used to send commands and SQL statements to the database server and accept the results returned by the database server. In fact, a database connection is a Socket connection.

• In Java There are three interfaces in the SQL package that define different methods of calling the database:

  • Statement: an object used to execute a static SQL statement and return the results it generates.
  • PrepatedStatement: the SQL statement is precompiled and stored in this object, which can be used multiple times to execute the statement efficiently.
  • CallableStatement: used to execute SQL stored procedures

int excuteUpdate(String sql): perform UPDATE operations INSERT, UPDATE, DELETE

Compare excute
ResultSet executeQuery(String sql): execute query operation SELECT

Disadvantages of using Statement to manipulate data table

• Create the Connection object by calling its createStatement() method. This object is used to execute static SQL statements and return execution results.

• However, using Statement to manipulate the data table has disadvantages:

  • Problem 1: there is string splicing operation, which is cumbersome
  • Problem 2: SQL injection problem

• SQL injection is to inject illegal SQL Statement segments OR commands into the user input data (such as: SELECT user, password FROM user_table WHERE user = 'a' OR 1 = 'AND password =' OR '1' = '1') without sufficient inspection of the user input data, so as to use the system's SQL Engine to complete malicious acts. For Java, to prevent SQL injection, just replace the Statement with Preparedstatement (extended from the Statement).

Code demonstration

@Test
    public void testLogin() {

//        Scanner scanner = new Scanner(System.in);

//        System.out.print("please enter user name:");
//        String user = scanner.nextLine();
//        System.out.print("please enter password:");
//        String password = scanner.nextLine();

        String user = "1' or ";
        String password = "=1 or '1' = '1";
        //SELECT user,password FROM user_table WHERE user = '1' or ' AND password = '=1 or '1' = '1'
        String sql = "SELECT user,password FROM user_table WHERE user = '"+ user +"' AND password = '"+ password +"'";
        User returnUser = get(sql,User.class);
        if(returnUser != null){
            System.out.println("Login succeeded");
        }else{
            System.out.println("The user name does not exist or the password is incorrect");
        }

    }

    // Query the data table using Statement
    public <T> T get(String sql, Class<T> clazz) {
        T t = null;

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;


        try {
            // 1. Load configuration file
            InputStream resourceAsStream = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
            // 2. Read configuration information
            Properties properties = new Properties();
            properties.load(resourceAsStream);
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String url = properties.getProperty("url");
            String driverClass = properties.getProperty("driverClass");
            // 3. Load drive
            Class.forName(driverClass);
            // 4. Get connection
            connection = DriverManager.getConnection(url, user, password);

            // Create the Connection object by calling its createStatement() method. This object is used to execute static SQL statements and return execution results.
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);

            // Get metadata of result set
            ResultSetMetaData metaData = resultSet.getMetaData();
            // Gets the number of columns in the result set
            int columnCount = metaData.getColumnCount();

            if (resultSet.next()) {
                t = clazz.newInstance();

                for (int i = 0; i < columnCount; i++) {
                    // Gets the name of the column
                    // String columnName = metaData.getColumnName(i+1);
                    // // 1.  Get the alias of the column. It is recommended to get the column alias - > the name of the column
                    String columnLabel = metaData.getColumnLabel(i + 1);

                    // 2. Obtain the data in the corresponding data table according to the column name
                    Object objectVal = resultSet.getObject(columnLabel);

                    // 3. Encapsulate the data obtained from the data table into the object
                    Field declaredField = clazz.getDeclaredField(columnLabel);
                    declaredField.setAccessible(true);
                    declaredField.set(t, objectVal);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }

        return null;
    }

Use of PreparedStatement

introduce

• The PreparedStatement object can be obtained by calling the preparedStatement(String sql) method of the Connection object

• PreparedStatement interface is a sub interface of Statement, which represents a precompiled SQL Statement

• The parameters in the SQL statement represented by the PreparedStatement object are marked with a question mark (?) Call the setXxx() method of the PreparedStatement object to set these parameters The setXxx() method has two parameters. The first parameter is the index of the parameter in the SQL statement to be set (starting from 1), and the second parameter is the value of the parameter in the SQL statement to be set

PreparedStatement vs Statement

• PreparedStatement maximizes performance:

  • DBServer provides performance optimization for precompiled statements. Because the precompiled statement may be called repeatedly, the execution code of the statement compiled by the DBServer compiler is cached. As long as it is the same precompiled statement in the next call, it does not need to be compiled. As long as the parameters are directly passed into the compiled statement execution code, it will be executed.
  • In the statement statement statement, even if it is the same operation, because the data content is different, the whole statement itself cannot match, and there is no meaning of caching the statement The fact is that no database caches the compiled execution code of ordinary statements. In this way, the incoming statement will be compiled once every execution.
  • (syntax check, semantic check, translation into binary commands, cache)

• PreparedStatement prevents SQL injection

Add, delete and modify operations using PreparedStatement

@Test
    public void test(){

        String name = "zgc";
        String email = "74034506@qq.com";
        String birthday = "1995-09-08";
        String sql = "insert into customers(name,email,birth)values(?,?,?)";
        int insertCount = update(sql,name,email,birthday);
        if(insertCount > 0){
            System.out.println("Added successfully");

        }else{
            System.out.println("Add failed");
        }
    }


    //General add, delete and change operations (embodiment 1: add, delete and change; embodiment 2: for different tables)
    public int update(String sql,Object ... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int insertCount = 0;

        try {
            //1. Get database connection
            connection = JDBCUtils.getConnection();

            //2. Get the instance of PreparedStatement (or: precompiled sql statement)
            preparedStatement = connection.prepareStatement(sql);

            //3. Fill placeholder
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1,args[i]);
            }

            //4. Execute sql statement
            insertCount = preparedStatement.executeUpdate();

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

            //5. Close resources
            if (preparedStatement != null){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return insertCount;
    }

Query operation using PreparedStatement

@Test
    public void test1() {

        int id = 19;

        String sql = "select id,name,email from customers where id = ?";
        Customer one = getOne(Customer.class, sql, id);
        System.out.println(one);

        String sql2 = "select id,name,email from customers where id < ?";
        List<Customer> list = getList(Customer.class, sql2, id);
        System.out.println(list);
    }

    /**
     * Query - single record
     * @param clazz
     * @param sql
     * @param args
     * @param <T>
     * @return
     */
    public <T> T getOne(Class<T> clazz, String sql, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        // 1. Get database connection
        try {
            connection = JDBCUtils.getConnection();
            // 2. Precompile the sql statement to obtain the PreparedStatement object
            preparedStatement = connection.prepareStatement(sql);
            // 3. Fill placeholder
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            // 4. Execute executeQuery() to get the result set: ResultSet
            resultSet = preparedStatement.executeQuery();
            // 5. Get metadata of result set: ResultSetMetaData
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            // 6.1 get columncount and columnlabel through ResultSetMetaData; Get column value through ResultSet
            if (resultSet.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    Object objectVal = resultSet.getObject(i + 1);
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, objectVal);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 7. Close resources
            JDBCUtils.closeResource(connection, preparedStatement, resultSet);
        }

        return null;
    }

    /**
     * Query - N records
     * @param clazz
     * @param sql
     * @param args
     * @param <T>
     * @return
     */
    public <T> List<T> getList(Class<T> clazz, String sql, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        // 1. Get database connection
        try {
            connection = JDBCUtils.getConnection();
            // 2. Precompile the sql statement to obtain the PreparedStatement object
            preparedStatement = connection.prepareStatement(sql);
            // 3. Fill placeholder
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            // 4. Execute executeQuery() to get the result set: ResultSet
            resultSet = preparedStatement.executeQuery();
            // 5. Get metadata of result set: ResultSetMetaData
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            // 6.1 get columncount and columnlabel through ResultSetMetaData; Get column value through ResultSet
            ArrayList<T> list = new ArrayList<>();
            while (resultSet.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    Object objectVal = resultSet.getObject(i + 1);
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, objectVal);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 7. Close resources
            JDBCUtils.closeResource(connection, preparedStatement, resultSet);
        }

        return null;
    }

ResultSet

• The query needs to call the executeQuery() method of PreparedStatement, and the query result is a ResultSet object

• The ResultSet object encapsulates the result set of database operation in the form of logical table, and the ResultSet interface is provided and implemented by the database manufacturer

• What the ResultSet returns is actually a data table. There is a pointer to the front of the first record in the data table.

• The ResultSet object maintains a cursor pointing to the current data row. Initially, the cursor is before the first row and can be moved to the next row through the next() method of the ResultSet object. Call the next () method to check whether the next line is valid. If valid, the method returns true and the pointer moves down. Equivalent to a combination of hasNext() and next () methods of the Iterator object.

• When the pointer points to a row, you can get the value of each column by calling getXxx(int index) or getXxx(int columnName).

  • For example: getInt(1), getString("name")
  • Note: the indexes in the relevant Java API s involved in the interaction between Java and database start from 1.

• Common methods of ResultSet interface:

  • boolean next()
  • getString()

ResultSetMetaData

• An object that can be used to get information about the types and properties of columns in a ResultSet object

• ResultSetMetaData meta = resultSet.getMetaData();

  • getColumnName(int column): gets the name of the specified column

  • getColumnLabel(int column): gets the alias of the specified column

  • getColumnCount(): returns the number of columns in the current ResultSet object.

  • getColumnTypeName(int column): retrieves the database specific type name of the specified column.

  • getColumnDisplaySize(int column): indicates the maximum standard width of the specified column, in characters.

  • isNullable(int column): indicates whether the value in the specified column can be null.

  • isAutoIncrement(int column): indicates whether the specified columns are automatically numbered so that they are still read-only.

Operation BLOB type field

introduce

• In MySQL, BLOB is a large binary object. It is a container that can store a large amount of data. It can hold data of different sizes.

• PreparedStatement must be used for inserting data of BLOB type, because data of BLOB type cannot be spliced with strings.

• Four BLOB types of MySQL (they are the same except that they are different in the maximum amount of information stored)
  
  If an error is reported after specifying the related Blob type: xxx too large, find my. In the mysql installation directory INI file plus the following configuration parameters: max_allowed_packet=16M. Also note: modified my After the. INI file, you need to restart the mysql service.
  

Code test

public class BlobTest {
    //Insert a Blob type field into the data table customers
    @Test
    public void testInsert() throws Exception{

        Connection connection = JDBCUtils.getConnection();

        String sql ="insert into customers(name,email,birth,photo) values(?,?,?,?) ";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1,"Yuan Hao");
        preparedStatement.setObject(2, "yuan@qq.com");
        preparedStatement.setObject(3,"1992-09-08");
        FileInputStream fileInputStream = new FileInputStream(new File("Wechat pictures_20210622200806.jpg"));
        preparedStatement.setBlob(4,fileInputStream );

        preparedStatement.execute();

        JDBCUtils.closeResource(connection,preparedStatement);
    }

    //Query the Blob type field in the data table customers - and download it
    @Test
    public void testQuery() throws Exception {
        Connection connection = JDBCUtils.getConnection();

        String sql ="select id,name,email,birth,photo from customers where id = ? ";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1,20);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()){
            //			Mode 1:
            //			int id = rs.getInt(1);
            //			String name = rs.getString(2);
            //			String email = rs.getString(3);
            //			Date birth = rs.getDate(4);
            //Mode 2:
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String email = resultSet.getString("email");
            Date birth = resultSet.getDate("birth");

            Customer customer = new Customer(id, name, email, birth);
            System.out.println(customer);

            //Download Blob type fields and save them locally as files
            Blob photo = resultSet.getBlob("photo");
            InputStream binaryStream = photo.getBinaryStream();
            FileOutputStream fileOutputStream = new FileOutputStream(new File("Wechat pictures_20210622200806_1.jpg"));
            byte[] bytes = new byte[1024];
            int len;
            while ((len = binaryStream.read(bytes)) != -1){
                fileOutputStream.write(bytes,0,len);
            }

            System.out.println(customer);


        }

        JDBCUtils.closeResource(connection,preparedStatement);
    }


}

Batch insert

introduce

• JDBC batch processing statements include the following three methods:

  • addBatch(String): add SQL statements or parameters that need batch processing;
  • executeBatch(): execute batch processing statements;
  • clearBatch(): clear cached data

• The rewritebackedstatements parameter should be added to the url of the JDBC connection of MySQL, and ensure that 5.1 13 or above, in order to achieve high-performance batch insertion. By default, the MySQL JDBC driver ignores the executeBatch() statement and breaks up a group of sql statements that we expect to execute in batches and sends them to the MySQL database one by one. In fact, batch insertion is a single insertion, which directly leads to low performance.

Code test

public class InsertTest{
    // Method 1: use Statement
    @Test
    public void test1() throws Exception {
        long start = System.currentTimeMillis();
        Connection connection = JDBCUtils.getConnection();
        Statement statement = connection.createStatement();
        for (int i = 0; i < 10000; i++) {
            String sql = "insert into goods(name) values("+i+")";
            statement.execute(sql);
        }
        JDBCUtils.closeResource(connection,statement);

        long end = System.currentTimeMillis();
        System.out.println(end-start); // 6068
    }


    // Method 2: use PreparedStatement
    @Test
    public void test2() throws Exception {
        long start = System.currentTimeMillis();
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into goods(name) values(?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < 10000; i++) {
            preparedStatement.setObject(1,i);
            preparedStatement.execute();
        }
        JDBCUtils.closeResource(connection,preparedStatement);

        long end = System.currentTimeMillis();
        System.out.println(end-start); // 5882
    }


    // Method 3: use addBatch() / executeBatch() / clearBatch()
    @Test
    public void test3() throws Exception {
        long start = System.currentTimeMillis();
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into goods(name) values(?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < 10000; i++) {
            preparedStatement.setObject(1,i);
            //1. Save sql
            preparedStatement.addBatch();
            if (i % 200 == 0){
                //2. Implementation
                preparedStatement.executeBatch();
                //3. Empty
                preparedStatement.clearBatch();
            }
        }
        JDBCUtils.closeResource(connection,preparedStatement);

        long end = System.currentTimeMillis();
        System.out.println(end-start); // 5655
    }


    // Method 4: use addBatch() / executeBatch() / clearBatch()-
    // The mysql server turns off batch processing by default. We need a parameter to enable mysql to turn on batch processing support? Rewritebackedstatements = true is written after the url of the configuration file
    // Setautocommit (false) / commit () using Connection
    @Test
    public void test4() throws Exception {
        long start = System.currentTimeMillis();
        Connection connection = JDBCUtils.getConnection();

        //1. Set not to submit data automatically
        connection.setAutoCommit(false);

        String sql = "insert into goods(name) values(?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < 10000; i++) {
            preparedStatement.setObject(1,i);
            //1. Save sql
            preparedStatement.addBatch();
            if (i % 200 == 0){
                //2. Implementation
                preparedStatement.executeBatch();
                //3. Empty
                preparedStatement.clearBatch();
            }
        }

        //2. Submission of data
        connection.commit();

        JDBCUtils.closeResource(connection,preparedStatement);


        long end = System.currentTimeMillis();
        System.out.println(end-start); // 1637
    }

Summary:
The rewritebackedstatements parameter is set to true
Connection's setAutoCommit(false) / commit(), which is not submitted automatically, is the fastest

affair

introduce

 * 1.What is database transaction?
 * Transaction: a set of logical units of operation,To transform data from one state to another.
 * 		> A group of logical operation units: one or more DML Operation.
 * 
 * 2.Principle of transaction processing: ensure that all transactions are executed as a unit of work. Even if there is a failure, this execution mode cannot be changed.
 * When multiple operations are performed in a transaction, either all transactions are committed(commit)，Then these changes are saved permanently
 * Come down; Or the database management system will discard all modifications and roll back the whole transaction(rollback)To the initial state.
 * 
 * 3.Once the data is submitted, it cannot be rolled back
 * 
 * 4.What actions will result in automatic submission of data?
 * 		>DDL Once the operation is executed, it will be submitted automatically.
 * 			>set autocommit = false yes DDL Operation failure
 * 		>DML By default, once executed, it is automatically submitted.
 * 			>We can pass set autocommit = false Cancel by DML Automatic submission of actions.
 * 		>By default, data will be submitted automatically when the connection is closed
 * 5.If at this time Connection If it is not closed and may be reused, you need to restore its automatic submission status setAutoCommit(true). Especially when using database connection pool technology close()It is recommended to restore the auto commit state before using the method.
 * 6. start transaction There are implicit settings autocommit Action for 0

* DML(Data Manipulation Language)Data manipulation language:
	Scope of application: perform some simple operations on the data in the database, such as insert,delete,update,select etc..

* DDL(Data Definition Language)Data definition language:
	Scope of application: for some objects in the database(For example, database,table)Manage, e.g Create,Alter and Drop.

Code test

//******************Transfer operation without considering database transactions**************************
	/*
	 * For datasheet user_ For table:
	 * AA Transfer 100 from user to BB user
	 * 
	 * update user_table set balance = balance - 100 where user = 'AA';
	 * update user_table set balance = balance + 100 where user = 'BB';
	 */
	@Test
	public void testUpdate(){
		
		String sql1 = "update user_table set balance = balance - 100 where user = ?";
		update(sql1, "AA");
		
		//Analog network exception
		System.out.println(10 / 0);
		
		String sql2 = "update user_table set balance = balance + 100 where user = ?";
		update(sql2, "BB");
		
		System.out.println("Transfer succeeded");
	}

	// General addition, deletion and modification operations -- version 1.0
	public int update(String sql, Object... args) {// The number of placeholders in sql is the same as the length of variable parameters!
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			// 1. Get database connection
			conn = JDBCUtils.getConnection();
			// 2. Precompile the sql statement and return the instance of PreparedStatement
			ps = conn.prepareStatement(sql);
			// 3. Fill placeholder
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);// Beware of parameter declaration error!!
			}
			// 4. Implementation
			return ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			
			//Modify it to auto submit data
			//Mainly for the use of database connection pool
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			// 5. Closure of resources
			JDBCUtils.closeResource(conn, ps);

		}
		return 0;

	}
	
	//********************Transfer operation after considering database transactions*********************
	
	@Test
	public void testUpdateWithTx() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			System.out.println(conn.getAutoCommit());//true
			//1. Cancel automatic submission of data
			conn.setAutoCommit(false);
			
			String sql1 = "update user_table set balance = balance - 100 where user = ?";
			update(conn,sql1, "AA");
			
			//Analog network exception
			System.out.println(10 / 0);
			
			String sql2 = "update user_table set balance = balance + 100 where user = ?";
			update(conn,sql2, "BB");
			
			System.out.println("Transfer succeeded");
			
			//2. Submission of data
			conn.commit();
			
		} catch (Exception e) {
			e.printStackTrace();
			//3. Rollback data
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally{
			
			JDBCUtils.closeResource(conn, null);
		}
		
	}
	
	// General addition, deletion and modification operations -- version 2.0 (considering previous transactions)
	public int update(Connection conn,String sql, Object... args) {// The number of placeholders in sql is the same as the length of variable parameters!
		PreparedStatement ps = null;
		try {
			// 1. Precompile the sql statement and return the instance of PreparedStatement
			ps = conn.prepareStatement(sql);
			// 2. Fill placeholder
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);// Beware of parameter declaration error!!
			}
			// 3. Implementation
			return ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 4. Closure of resources
			JDBCUtils.closeResource(null, ps);

		}
		return 0;

	}

Isolation level

ACID property of the transaction

1. Atomicity
   Atomicity means that a transaction is an indivisible unit of work, and operations in a transaction either occur or do not occur.

2. Consistency
   Transactions must transition the database from one consistency state to another.

3. Isolation
   Transaction isolation means that the execution of a transaction cannot be disturbed by other transactions, that is, the operations and data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions cannot interfere with each other.

4. Durability
   Persistence means that once a transaction is committed, its changes to the data in the database are permanent, and other subsequent operations and database failures should not have any impact on it.

Database concurrency

problem

• For multiple transactions running at the same time, when these transactions access the same data in the database, if the necessary isolation mechanism is not taken, various concurrency problems will be caused:

  • Dirty read: for two transactions T1 and T2, T1 reads the fields that have been updated by T2 but have not been committed. After that, if T2 rolls back, the content read by T1 is temporary and invalid.
  • Non repeatable reading: for two transactions T1 and T2, T1 reads a field, and then T2 updates the field. After that, T1 reads the same field again, and the value is different.
  • Phantom reading: for two transactions T1 and T2, T1 reads a field from a table, and T2 inserts some new rows into the table. After that, if T1 reads the same table again, several more rows will appear.

• Isolation of database transactions: the database system must have the ability to isolate and run various transactions concurrently, so that they will not affect each other and avoid various concurrency problems.

• The degree to which a transaction is isolated from other transactions is called the isolation level. The database specifies multiple transaction isolation levels. Different isolation levels correspond to different interference levels. The higher the isolation level, the better the data consistency, but the weaker the concurrency.

Four isolation levels

• Mysql supports four transaction isolation levels. The default transaction isolation level of Mysql is REPEATABLE READ.
• Oracle supports two transaction isolation levels: read committed and serial. The default transaction isolation level of Oracle is read committed.
  

Set isolation level in MySql

• Every time you start a MySQL program, you get a separate database connection Each database connection has a global variable @@tx_isolation, indicating the current transaction isolation level. To set the isolation level, you need to restart mysql.

• To view the current isolation level:

  SELECT @@tx_isolation;
  

• Set the isolation level of the current mySQL connection:

  set  transaction isolation level read committed;
  

• Set the global isolation level of the database system:

  set global transaction isolation level read committed;
  

The command line demonstrates isolation levels

Read uncommitted

Read submitted (recommended)

Repeatability (mysql default)

Code demonstration isolation level

	@Test
	public void testTransactionSelect() throws Exception{
		
		Connection conn = JDBCUtils.getConnection();
		//Gets the isolation level of the current connection
		System.out.println(conn.getTransactionIsolation());
		//Set the isolation level of the database:
		conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
		//Cancel auto submit data
		conn.setAutoCommit(false);
		
		String sql = "select user,password,balance from user_table where user = ?";
		User user = getInstance(conn, User.class, sql, "CC");
		
		System.out.println(user);
		
	}
	
	@Test
	public void testTransactionUpdate() throws Exception{
		Connection conn = JDBCUtils.getConnection();
		
		//Cancel auto submit data
		conn.setAutoCommit(false);
		String sql = "update user_table set balance = ? where user = ?";
		update(conn, sql, 5000,"CC");
		
		Thread.sleep(15000);
		System.out.println("End of modification");
	}
	
	//General query operation, used to return a record in the data table (version 2.0: consider previous transactions)
	public <T> T getInstance(Connection conn,Class<T> clazz,String sql, Object... args) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}

			rs = ps.executeQuery();
			// Get metadata of result set: ResultSetMetaData
			ResultSetMetaData rsmd = rs.getMetaData();
			// Get the number of columns in the result set through ResultSetMetaData
			int columnCount = rsmd.getColumnCount();

			if (rs.next()) {
				T t = clazz.newInstance();
				// Process each column in a row of data in the result set
				for (int i = 0; i < columnCount; i++) {
					// Get column value
					Object columValue = rs.getObject(i + 1);

					// Gets the column name of each column
					// String columnName = rsmd.getColumnName(i + 1);
					String columnLabel = rsmd.getColumnLabel(i + 1);

					// The columnName attribute specified to the t object is assigned a value of columnvalue: through reflection
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columValue);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps, rs);

		}

		return null;
	}

DAO and related implementation classes

introduce

Function: in order to realize the modularization of functions, it is more conducive to code maintenance and upgrading.

BaseDao

/*
 * DAO: data(base) access object
 * Encapsulates common operations for data tables
 */
public abstract class BaseDAO<T> {
	
	private Class<T> clazz = null;
	
//	public BaseDAO(){
//		
//	}
	
	{	
		//Gets the generic type in the parent class inherited by the subclass of the current BaseDAO
		Type genericSuperclass = this.getClass().getGenericSuperclass();
		ParameterizedType paramType = (ParameterizedType) genericSuperclass;
		
		Type[] typeArguments = paramType.getActualTypeArguments();//Gets the generic parameter of the parent class
		clazz = (Class<T>) typeArguments[0];//The first argument to the generic
		
	}
	
	
	// General addition, deletion and modification operations -- version 2.0 (considering previous transactions)
	public int update(Connection conn, String sql, Object... args) {// The number of placeholders in sql is the same as the length of variable parameters!
		PreparedStatement ps = null;
		try {
			// 1. Precompile the sql statement and return the instance of PreparedStatement
			ps = conn.prepareStatement(sql);
			// 2. Fill placeholder
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);// Beware of parameter declaration error!!
			}
			// 3. Implementation
			return ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 4. Closure of resources
			JDBCUtils.closeResource(null, ps);

		}
		return 0;

	}

	// General query operation, used to return a record in the data table (version 2.0: consider previous transactions)
	public T getInstance(Connection conn, String sql, Object... args) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {

			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}

			rs = ps.executeQuery();
			// Get metadata of result set: ResultSetMetaData
			ResultSetMetaData rsmd = rs.getMetaData();
			// Get the number of columns in the result set through ResultSetMetaData
			int columnCount = rsmd.getColumnCount();

			if (rs.next()) {
				T t = clazz.newInstance();
				// Process each column in a row of data in the result set
				for (int i = 0; i < columnCount; i++) {
					// Get column value
					Object columValue = rs.getObject(i + 1);

					// Gets the column name of each column
					// String columnName = rsmd.getColumnName(i + 1);
					String columnLabel = rsmd.getColumnLabel(i + 1);

					// The columnName attribute specified to the t object is assigned a value of columnvalue: through reflection
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columValue);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps, rs);

		}

		return null;
	}
	// General query operation, used to return a set composed of multiple records in the data table (version 2.0: consider previous transactions)
	public List<T> getForList(Connection conn, String sql, Object... args) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {

			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}

			rs = ps.executeQuery();
			// Get metadata of result set: ResultSetMetaData
			ResultSetMetaData rsmd = rs.getMetaData();
			// Get the number of columns in the result set through ResultSetMetaData
			int columnCount = rsmd.getColumnCount();
			// Create collection object
			ArrayList<T> list = new ArrayList<T>();
			while (rs.next()) {
				T t = clazz.newInstance();
				// Process each column in a row of data in the result set: assign a value to the attribute specified by the t object
				for (int i = 0; i < columnCount; i++) {
					// Get column value
					Object columValue = rs.getObject(i + 1);

					// Gets the column name of each column
					// String columnName = rsmd.getColumnName(i + 1);
					String columnLabel = rsmd.getColumnLabel(i + 1);

					// The columnName attribute specified to the t object is assigned a value of columnvalue: through reflection
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columValue);
				}
				list.add(t);
			}

			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps, rs);

		}

		return null;
	}
	//A general method for querying special values
	public <E> E getValue(Connection conn,String sql,Object...args){
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			for(int i = 0;i < args.length;i++){
				ps.setObject(i + 1, args[i]);
				
			}
			
			rs = ps.executeQuery();
			if(rs.next()){
				return (E) rs.getObject(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.closeResource(null, ps, rs);
			
		}
		return null;
		
	}	
}

Dao(Customer example)

/*
 * This interface is used to standardize common operations on the customers table
 */
public interface CustomerDAO {
	/**
	 * 
	 * @Description Add cust object to database
	 * @author shkstart
	 * @date 11:00:27 am
	 * @param conn
	 * @param cust
	 */
	void insert(Connection conn,Customer cust);
	/**
	 * 
	 * @Description Delete a record in the table for the specified id
	 * @author shkstart
	 * @date 11:01:07 am
	 * @param conn
	 * @param id
	 */
	void deleteById(Connection conn,int id);
	/**
	 * 
	 * @Description For cust objects in memory, modify the records specified in the data table
	 * @author shkstart
	 * @date 11:02:14 am
	 * @param conn
	 * @param cust
	 */
	void update(Connection conn,Customer cust);
	/**
	 * 
	 * @Description Query the specified id to get the corresponding Customer object
	 * @author shkstart
	 * @date 11:02:59 am
	 * @param conn
	 * @param id
	 */
	Customer getCustomerById(Connection conn,int id);
	/**
	 * 
	 * @Description A collection of all records in a query table
	 * @author shkstart
	 * @date 11:03:50 am
	 * @param conn
	 * @return
	 */
	List<Customer> getAll(Connection conn);
	/**
	 * 
	 * @Description Returns the number of data entries in the data table
	 * @author shkstart
	 * @date 11:04:44 am
	 * @param conn
	 * @return
	 */
	Long getCount(Connection conn);
	
	/**
	 * 
	 * @Description Returns the largest birthday in the data table
	 * @author shkstart
	 * @date 11:05:33 am
	 * @param conn
	 * @return
	 */
	Date getMaxBirth(Connection conn);
	
}	

DaoImpl(Customer example)

public class CustomerDAOImpl extends BaseDAO<Customer> implements CustomerDAO{
	
	
	@Override
	public void insert(Connection conn, Customer cust) {
		String sql = "insert into customers(name,email,birth)values(?,?,?)";
		update(conn, sql,cust.getName(),cust.getEmail(),cust.getBirth());
	}

	@Override
	public void deleteById(Connection conn, int id) {
		String sql = "delete from customers where id = ?";
		update(conn, sql, id);
	}

	@Override
	public void update(Connection conn, Customer cust) {
		String sql = "update customers set name = ?,email = ?,birth = ? where id = ?";
		update(conn, sql,cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId());
	}

	@Override
	public Customer getCustomerById(Connection conn, int id) {
		String sql = "select id,name,email,birth from customers where id = ?";
		Customer customer = getInstance(conn, sql,id);
		return customer;
	}

	@Override
	public List<Customer> getAll(Connection conn) {
		String sql = "select id,name,email,birth from customers";
		List<Customer> list = getForList(conn, sql);
		return list;
	}

	@Override
	public Long getCount(Connection conn) {
		String sql = "select count(*) from customers";
		return getValue(conn, sql);
	}

	@Override
	public Date getMaxBirth(Connection conn) {
		String sql = "select max(birth) from customers";
		return getValue(conn, sql);
	}

}

The code block of the parent class, and the subclass will execute once every time new
Object type with generics as return parameters

Database connection pool

The necessity of JDBC database connection pool

• Ordinary JDBC database connections are obtained using DriverManager. Each time a Connection is established to the database, the Connection must be loaded into memory, and then the user name and password must be verified (it takes 0.05s ~ 1s). When you need a database Connection, ask for one from the database and disconnect it after execution. This way will consume a lot of resources and time. The Connection resources of the database have not been well reused. If hundreds or even thousands of people are online at the same time, frequent database Connection operation will occupy a lot of system resources, and even cause server crash.
• For each database connection, it must be disconnected after use. Otherwise, if the program fails to close due to exceptions, it will lead to memory leakage in the database system and eventually restart the database. (recall: what is Java's memory leak?)
• This development cannot control the number of connection objects created, and system resources will be allocated without consideration. If there are too many connections, it may also lead to memory leakage and server crash.

Database connection pool technology

• In order to solve the problem of database connection in traditional development, database connection pool technology can be used.

• The basic idea of database connection pool is to establish a "buffer pool" for database connections. Put a certain number of connections in the buffer pool in advance. When you need to establish a database connection, just take one from the buffer pool and put it back after use.

• Database connection pool is responsible for allocating, managing and releasing database connections. It allows applications to reuse an existing database connection instead of re establishing one.

• During initialization, the database connection pool will create a certain number of database connections into the connection pool. The number of these database connections is set by the minimum number of database connections. No matter whether these database connections are used or not, the connection pool will always ensure that there are at least so many connections. The maximum number of database connections in the connection pool limits the maximum number of connections that the connection pool can occupy. When the number of connections requested by the application from the connection pool exceeds the maximum number of connections, these requests will be added to the waiting queue.

Advantages of database connection pool technology

1. Resource reuse

Because the database connection can be reused, it avoids frequent creation and releases a lot of performance overhead caused by the connection. On the basis of reducing system consumption, on the other hand, it also increases the stability of system operation environment.

2. Faster system response

During the initialization of the database connection pool, several database connections have often been created and placed in the connection pool for standby. At this time, the initialization of the connection has been completed. For business request processing, the existing available connections are directly used to avoid the time overhead of database connection initialization and release process, so as to reduce the response time of the system

3. New means of resource allocation

For systems where multiple applications share the same database, the maximum number of available database connections of an application can be limited through the configuration of database connection pool at the application layer to avoid an application monopolizing all database resources

4. Unified connection management to avoid database connection leakage

In the more perfect implementation of database connection pool, the occupied connections can be forcibly recovered according to the pre occupation timeout setting, so as to avoid the possible resource leakage in the conventional database connection operation

Multiple open source database connection pools

• The database connection pool of JDBC uses javax sql. DataSource means that DataSource is just an interface, which is usually implemented by servers (Weblogic, WebSphere, Tomcat) and some open source organizations:
  • dbcp is a database connection pool provided by Apache. The tomcat server has its own dbcp database connection pool. The speed is relatively c3p0 fast, but hibernate 3 no longer provides support due to its own BUG.
  • C3P0 is a database connection pool provided by an open source organization. The * * speed is relatively slow and the stability is OK** hibernate is officially recommended
  • Proxool is an open source project database connection pool under sourceforge. It has the function of monitoring the status of the connection pool, and its stability is c3p0 poor
  • BoneCP is a database connection pool provided by an open source organization with high speed
  • Druid is a database connection pool provided by Alibaba. It is said to be a database connection pool integrating the advantages of DBCP, C3P0 and Proxool. However, it is uncertain whether it is faster than BoneCP
• DataSource is usually called data source, which includes connection pool and connection pool management. Traditionally, DataSource is often called connection pool
• DataSource is used to replace DriverManager to obtain Connection, which is fast and can greatly improve database access speed.
• Special attention:
  • The data source is different from the database connection. There is no need to create multiple data sources. It is the factory that generates the database connection. Therefore, the whole application only needs one data source.
  • After the database access is completed, the program closes the database connection as before: conn.close(); However, Conn. Close () does not close the physical connection to the database. It only releases the database connection and returns it to the database connection pool.

C3P0 database connection pool

public class C3P0Test {
    //Mode 1:
    @Test
    public void testGetConnection() throws Exception{
        //Get c3p0 database connection pool
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        cpds.setDriverClass( "com.mysql.jdbc.Driver" );
        cpds.setJdbcUrl( "jdbc:mysql://192.168.56.10:3306/test" );
        cpds.setUser("root");
        cpds.setPassword("root");
        //Manage the database connection pool by setting relevant parameters:
        //Sets the number of connections in the database connection pool at initial
        cpds.setInitialPoolSize(10);

        Connection conn = cpds.getConnection();
        System.out.println(conn);

        //Destroy c3p0 database connection pool
//		DataSources.destroy( cpds );
    }


    //Method 2: use configuration file
    @Test
    public void testGetConnection1() throws SQLException {
        ComboPooledDataSource cpds = new ComboPooledDataSource("hellc3p0"); // id of the corresponding xml file
        Connection conn = cpds.getConnection();
        System.out.println(conn);
    }
}

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

    <named-config name="hellc3p0">
        <!-- Provides 4 basic information for obtaining connections -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://192.168.56.10:3306/test</property>
        <property name="user">root</property>
        <property name="password">root</property>

        <!-- Basic information for database connection pool management -->
        <!-- When there are not enough connections in the database connection pool, c3p0 Number of connections requested from the database server at one time -->
        <property name="acquireIncrement">5</property>
        <!-- c3p0 The number of connections initialized in the database connection pool -->
        <property name="initialPoolSize">10</property>
        <!-- c3p0 Minimum number of connections maintained by database connection pool -->
        <property name="minPoolSize">10</property>
        <!-- c3p0 The maximum number of connections maintained by the database connection pool -->
        <property name="maxPoolSize">100</property>
        <!-- c3p0 Maximum number of database connection pools maintained Statement Number of -->
        <property name="maxStatements">50</property>
        <!-- Maximum number of users per connection Statement Number of -->
        <property name="maxStatementsPerConnection">2</property>

    </named-config>
</c3p0-config>

DBCP database connection pool

public class DBCPTest {
    /**
     *
     * @Description Database connection pool technology for testing DBCP
     * @author shkstart
     * @throws SQLException
     * @date 3:20:50 PM
     */
    //Method 1: not recommended
    @Test
    public void testGetConnection() throws SQLException {
        //The database connection pool of DBCP is created
        BasicDataSource source = new BasicDataSource();

        //Set basic information
        source.setDriverClassName("com.mysql.jdbc.Driver");
        source.setUrl("jdbc:mysql://192.168.56.10:3306/test");
        source.setUsername("root");
        source.setPassword("root");

        //You can also set other related properties related to database connection pool management:
        source.setInitialSize(10);
        source.setMaxActive(10);
        //. . . 

        Connection conn = source.getConnection();
        System.out.println(conn);
    }

    //Method 2: Recommended: use configuration file
    @Test
    public void testGetConnection1() throws Exception{
        Properties pros = new Properties();

        //Mode 1:
//		InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");
        //Mode 2:
        FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));


        pros.load(is);
        DataSource source = BasicDataSourceFactory.createDataSource(pros);

        Connection conn = source.getConnection();
        System.out.println(conn);
    }

}

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.56.10:3306/test
username=root
password=root

initialSize=10

Druid database connection pool

introduce

Druid is a database connection pool implementation on Alibaba's open source platform. It combines the advantages of C3P0, DBCP, Proxool and other DB pools, and adds log monitoring. It can well monitor the connection of DB pool and the execution of SQL. It can be said that Druid is a DB connection pool for monitoring, which can be said to be one of the best connection pools at present.

Code test

public class DruidTest {

    // Get connection method 1:
    @Test
    public void getConnectionFromText() throws Exception{
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        druidDataSource.setUrl("jdbc:mysql://192.168.56.10:3306/test?rewriteBatchedStatements=true");
        druidDataSource.setUsername("root");
        druidDataSource.setPassword("root");

        DruidPooledConnection connection = druidDataSource.getConnection();
        System.out.println(connection); // com.mysql.jdbc.JDBC4Connection@d70c109
    }

    // Get connection mode 2: Recommended
    @Test
    public void getConnectionFromFile() throws Exception{
//        InputStream resourceAsStream = DruidTest.class.getClassLoader().getResourceAsStream("druid.properties");
        FileInputStream fileInputStream = new FileInputStream("src//druid.properties");
        Properties properties = new Properties();
//        properties.load(resourceAsStream);
        properties.load(fileInputStream);
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

        Connection connection = dataSource.getConnection();
        System.out.println(connection); // com.mysql.jdbc.JDBC4Connection@366e2eef
    }
}

// druid.properties
url=jdbc:mysql://192.168.56.10:3306/test?rewriteBatchedStatements=true
username=root
password=root
driverClassName=com.mysql.jdbc.Driver

initialSize=10
maxActive=10

Code Demonstration - the whole application only needs one data source

/**
	 * Using Druid database connection pool technology
	 */
	private static DataSource source1;
	static{
		try {
			Properties pros = new Properties();
			
			InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
			
			pros.load(is);
			
			source1 = DruidDataSourceFactory.createDataSource(pros);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static Connection getConnection3() throws SQLException{
		
		Connection conn = source1.getConnection();
		return conn;
	}

Detailed configuration parameters

to configure	default	explain
name		The significance of configuring this attribute is that if there are multiple data sources, they can be distinguished by name during monitoring. If there is no configuration, a name will be generated in the format: "DataSource -" + system identityHashCode(this)
url		The url to connect to the database is different from database to database. For example: MySQL: JDBC: mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20. 149.85:1521:ocnauto
username		User name to connect to the database
password		Password to connect to the database. If you don't want the password written directly in the configuration file, you can use ConfigFilter. See here for details: https://github.com/alibaba/druid/wiki/ Using ConfigFilter
driverClassName		Automatic identification according to url is optional. If druid is not configured, dbType will be automatically identified according to url, and then corresponding driverclassname will be selected (under recommended configuration)
initialSize	0	The number of physical connections established during initialization. Initialization occurs when the display calls the init method or the first getConnection
maxActive	8	Maximum number of connection pools
maxIdle	8	It is no longer used, and the configuration has no effect
minIdle		Minimum number of connection pools
maxWait		Maximum wait time to get a connection, in milliseconds. After maxWait is configured, the fair lock is enabled by default, and the concurrency efficiency will be reduced. If necessary, you can use a non fair lock by configuring the useUnfairLock attribute to true.
poolPreparedStatements	false	Whether to cache preparedStatement, that is, PSCache. PSCache greatly improves the performance of databases that support cursors, such as oracle. It is recommended to close under mysql.
maxOpenPreparedStatements	-1	To enable PSCache, it must be configured to be greater than 0. When greater than 0, poolPreparedStatements is automatically triggered and modified to true. In Druid, there will be no problem that PSCache in Oracle occupies too much memory. You can configure this value to be larger, such as 100
validationQuery		The sql used to check whether the connection is valid requires a query statement. If validationQuery is null, testonmirror, testOnReturn, and testwhiteidle will not work.
testOnBorrow	true	When applying for a connection, execute validationQuery to check whether the connection is valid. This configuration will reduce performance.
testOnReturn	false	When returning the connection, execute validationQuery to check whether the connection is valid. This configuration will reduce the performance
testWhileIdle	false	It is recommended to configure to true, which will not affect performance and ensure security. Check when applying for a connection. If the idle time is greater than timebetween evictionrunsmillis, run validationQuery to check whether the connection is valid.
timeBetweenEvictionRunsMillis		It has two meanings: 1) the destroy thread will detect the connection interval; 2) the judgment basis of testwhiteidle. See the description of testwhiteidle property for details
numTestsPerEvictionRun		No longer used, a DruidDataSource only supports one EvictionRun
minEvictableIdleTimeMillis
connectionInitSqls		sql executed during physical connection initialization
exceptionSorter		According to dbType, it is automatically recognized that when the database throws some unrecoverable exceptions, the connection is discarded
filters		The attribute type is string. The extension plug-ins are configured by alias. The commonly used plug-ins are: filter for monitoring statistics: stat, filter for log: log4j, filter for defending sql injection: wall
proxyFilters		The type is List. If both filters and proxyFilters are configured, it is a combination relationship, not a replacement relationship

JDBC utils after using data source

public class JDBCUtils {
	/**
	 * 
	 * @Description Get connection to database
	 * @author shkstart
	 * @date 9:11:23 am
	 * @return
	 * @throws Exception
	 */
	public static Connection getConnection() throws Exception {
		// 1. Read the four basic information in the configuration file
		InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

		Properties pros = new Properties();
		pros.load(is);

		String user = pros.getProperty("user");
		String password = pros.getProperty("password");
		String url = pros.getProperty("url");
		String driverClass = pros.getProperty("driverClass");

		// 2. Load drive
		Class.forName(driverClass);

		// 3. Get connection
		Connection conn = DriverManager.getConnection(url, user, password);
		return conn;
	}
	
	/**
	 * 
	 * @Description Database connection pool technology using C3P0
	 * @author shkstart
	 * @date 3:01:25 PM
	 * @return
	 * @throws SQLException
	 */
	//Only one database connection pool needs to be provided.
	private static ComboPooledDataSource cpds = new ComboPooledDataSource("hellc3p0");
	public static Connection getConnection1() throws SQLException{
		Connection conn = cpds.getConnection();
		
		return conn;
	}
	
	/**
	 * 
	 * @Description Using DBCP database connection pool technology to obtain database connection
	 * @author shkstart
	 * @date 3:35:25 PM
	 * @return
	 * @throws Exception
	 */
	//Create a DBCP database connection pool
	private static DataSource source;
	static{
		try {
			Properties pros = new Properties();
			FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
			pros.load(is);
			source = BasicDataSourceFactory.createDataSource(pros);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static Connection getConnection2() throws Exception{
		
		Connection conn = source.getConnection();
		
		return conn;
	}
	
	/**
	 * Using Druid database connection pool technology
	 */
	private static DataSource source1;
	static{
		try {
			Properties pros = new Properties();
			
			InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
			
			pros.load(is);
			
			source1 = DruidDataSourceFactory.createDataSource(pros);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static Connection getConnection3() throws SQLException{
		
		Connection conn = source1.getConnection();
		return conn;
	}
	
	
	/**
	 * 
	 * @Description Close the connection and Statement
	 * @author shkstart
	 * @date 9:12:40 am
	 * @param conn
	 * @param ps
	 */
	public static void closeResource(Connection conn,Statement ps){
		try {
			if(ps != null)
				ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 
	 * @Description Close resource operation
	 * @author shkstart
	 * @date 10:21:15 am
	 * @param conn
	 * @param ps
	 * @param rs
	 */
	public static void closeResource(Connection conn,Statement ps,ResultSet rs){
		try {
			if(ps != null)
				ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(rs != null)
				rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 
	 * @Description Use DbUtils Dbutils tool class provided in. Jar to close resources
	 * @author shkstart
	 * @date 4:53:09 PM
	 * @param conn
	 * @param ps
	 * @param rs
	 */
	public static void closeResource1(Connection conn,Statement ps,ResultSet rs){
		DbUtils.closeQuietly(conn);
		DbUtils.closeQuietly(ps);
		DbUtils.closeQuietly(rs);
	}
}

Apache dbutils implements CRUD operations

introduce

• Commons dbutils is an open source JDBC tool class library provided by Apache organization. It is a simple encapsulation of JDBC with very low learning cost. Using dbutils can greatly simplify the workload of JDBC coding without affecting the performance of the program.

• API introduction:

  • org.apache.commons.dbutils.QueryRunner
  • org.apache.commons.dbutils.ResultSetHandler
  • Tools: org apache. commons. dbutils. DbUtils

• API package description:
  

Use of major API s

DbUtils tool class

DbUtils: provides tool classes for routine work such as closing connections and loading JDBC drivers. All methods in them are static. The main methods are as follows:

• public static void close(…) throws java.sql.SQLException: the DbUtils class provides three overloaded shutdown methods. These methods check whether the supplied parameter is NULL, and if not, they close the Connection, Statement, and ResultSet.
• Public static void closequiet (...): this kind of method can not only avoid closing when the Connection, Statement and ResultSet are NULL, but also hide some SQLEeception thrown in the program.
• public static void commitAndClose(Connection conn)throws SQLException: used to commit the connected transaction and close the connection
• Public static void commitandclosequiet (connection conn): used to submit a connection and then close the connection. SQL exceptions are not thrown when closing the connection.
• public static void rollback(Connection conn)throws SQLException: conn is allowed to be null because a judgment is made inside the method
• public static void rollbackAndClose(Connection conn)throws SQLException
• rollbackAndCloseQuietly(Connection)
• public static boolean loadDriver(java.lang.String driverClassName): this party loads and registers the JDBC driver, and returns true if successful. Using this method, you do not need to catch this exception ClassNotFoundException.

QueryRunner class

• This class simplifies SQL query. It can be used together with ResultSetHandler to complete most database operations and greatly reduce the amount of coding.

• The QueryRunner class provides two constructors:

  • Default constructor
  • You need a javax sql. Datasource is used as the constructor of parameters

• Main methods of QueryRunner class:

  • to update
    • public int update(Connection conn, String sql, Object... params) throws SQLException: used to perform an update (insert, update or delete) operation.
    • ...
  • insert
    • public T insert(Connection conn,String sql,ResultSetHandler rsh, Object... params) throws SQLException: only INSERT statements are supported, where RSH - the handler used to create the result object from the resultset of auto generated keys Return value: An object generated by the handler That is, the automatically generated key value
    • ...
  • Batch processing
    • public int[] batch(Connection conn,String sql,Object[][] params)throws SQLException: INSERT, UPDATE, or DELETE statements
    • public T insertBatch(Connection conn,String sql,ResultSetHandler rsh,Object[][] params)throws SQLException: only INSERT statements are supported
    • ...
  • query
    • public Object query(Connection conn, String sql, ResultSetHandler rsh,Object... params) throws SQLException: executes a query operation. In this query, each element value in the object array is used as the replacement parameter of the query statement. This method handles the creation and closing of PreparedStatement and ResultSet by itself.
    • ...

Code test

public class QueryRunnerTest {
    // Test add
    @Test
    public void testInsert() throws Exception {
        Connection connection3 = JDBCUtils2.getConnection3();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "insert into customers(name,email,birth)values(?,?,?)";
        queryRunner.update(connection3,sql,"zgc","740354xxxx@qq.com","1990-10-20");
        JDBCUtils2.closeResource(connection3,null);
    }
}

ResultSetHandler interface and implementation class

• This interface is used to handle Java sql. Resultset to convert the data into another form as required.

• The ResultSetHandler interface provides a separate method: object handle (Java. SQL. Resultset. RS).

• Main implementation classes of the interface:

  • ArrayHandler: converts the first row of data in the result set into an object array.
  • ArrayListHandler: convert each row of data in the result set into an array and store it in the List.
  • **BeanHandler: * * encapsulates the first row of data in the result set into a corresponding JavaBean instance.
  • **BeanListHandler: * * encapsulate each row of data in the result set into a corresponding JavaBean instance and store it in the List.
  • ColumnListHandler: store the data of a column in the result set in the List.
  • KeyedHandler(name): encapsulate each row of data in the result set into a map, and then save these maps into a map. The key is the specified key.
  • **MapHandler: * * encapsulates the first row of data in the result set into a Map. key is the column name and value is the corresponding value.
  • **MapListHandler: * * encapsulate each row of data in the result set into a Map, and then store it in the List
  • **ScalarHandler: * * query single value object

Code test

/*
 * Test query: query a record
 * 
 * Implementation class using ResultSetHandler: BeanHandler
 */
@Test
public void testQueryInstance() throws Exception{
	QueryRunner runner = new QueryRunner();

	Connection conn = JDBCUtils.getConnection3();
		
	String sql = "select id,name,email,birth from customers where id = ?";
		
	//
	BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
	Customer customer = runner.query(conn, sql, handler, 23);
	System.out.println(customer);	
	JDBCUtils.closeResource(conn, null);
}

/*
 * Test query: query a set composed of multiple records
 * 
 * Implementation class using ResultSetHandler: BeanListHandler
 */
@Test
public void testQueryList() throws Exception{
	QueryRunner runner = new QueryRunner();

	Connection conn = JDBCUtils.getConnection3();
		
	String sql = "select id,name,email,birth from customers where id < ?";
		
	//
	BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
	List<Customer> list = runner.query(conn, sql, handler, 23);
	list.forEach(System.out::println);
		
	JDBCUtils.closeResource(conn, null);
}

*
 * custom ResultSetHandler Implementation class of
 */
@Test
public void testQueryInstance1() throws Exception{
	QueryRunner runner = new QueryRunner();

	Connection conn = JDBCUtils.getConnection3();
		
	String sql = "select id,name,email,birth from customers where id = ?";
		
	ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>() {

		@Override
		public Customer handle(ResultSet rs) throws SQLException {
			System.out.println("handle");
//			return new Customer(1,"Tom","tom@126.com",new Date(123323432L));
				
			if(rs.next()){
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String email = rs.getString("email");
				Date birth = rs.getDate("birth");
					
				return new Customer(id, name, email, birth);
			}
			return null;
				
		}
	};
		
	Customer customer = runner.query(conn, sql, handler, 23);
		
	System.out.println(customer);
		
	JDBCUtils.closeResource(conn, null);
}

JDBC summary

@Test
public void testUpdateWithTx() {
		
	Connection conn = null;
	try {
		//1. Get connection(
		//① Handwritten connection: JDBC utils getConnection();
		//② Use database connection pool: C3P0;DBCP;Druid
		//2. Perform a series of CRUD operations on the data table
		//① Use PreparedStatement to realize general addition, deletion, modification and query operations (version 1.0 \ version 2.0)
//version2. Public void update (connection Conn, string SQL, object... Args) {}
//version2. Query public < T > t getInstance (connection Conn, class < T > clazz, string SQL, object... Args) {}
		//② Use the QueryRunner class provided in the jar package provided by dbutils
			
		//Submit data
		conn.commit();
			
	
	} catch (Exception e) {
		e.printStackTrace();
			
			
		try {
			//Undo Data 
			conn.rollback();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
			
	}finally{
		//3. Close the connection
		//① JDBCUtils.closeResource();
		//② Using the dbutils class provided in the jar package provided by dbutils provides the related operation of closing
			
	}
}