Java, JDBC core technology, transaction, thread pool

Article catalog

JDBC core technology

1, Overview

1. Data persistence

  • Persistence: save data to a power down storage device for later use. Data persistence means that the data in memory is stored on the hard disk to be "solidified", and the implementation process of persistence is mostly completed through various relational databases.
  • The main application of persistence is to store the data in memory in the relational database, but also in the disk file and XML data file.

2. Data storage technology in Java

  1. In Java, data access technology can be divided into the following categories:
    • JDBC direct access to database
    • JDO (Java Data Object) technology
    • Third party O/R tools, such as Hibernate, Mybatis, etc
  2. JDBC is the cornerstone for Java to access database. JDO, Hibernate, MyBatis, etc. are just better encapsulation of JDBC

3. Introduction to JDBC

  • JDBC (Java Database Connectivity) is a common interface (a set of API s) independent of specific database management system and general SQL database for accessing data and operation. It defines the standard Java class library for accessing database( Java.sql , Javax.sql )Using these libraries, we can access database resources in a standard way.
  • JDBC provides a unified way to access different databases and shields some details for developers
  • The goal of JDBC is to enable Java programmers to connect to any database system that provides JDBC drivers using JDBC
  • If there is no JDBC, the Java program accesses the database as follows:

  • When JDBC and Java access the database:

4. JDBC architecture

  • The JDBC interface (API) consists of two levels:
    • Application oriented API: Java API, abstract interface, for application developers to use (connect database, execute SQL statements, get results)
    • Database oriented API: Java Driver API for developers to develop database drivers

5. JDBC programming steps

2, Get database connection

public class ConnectionTest {
    /**
     * Mode 1
     * @throws SQLException
     */
    @Test
    public void testConnection1() throws SQLException {
        //1. Get the implementation class object of Driver
        Driver driver=new com.mysql.jdbc.Driver();
        //2, Provide the database to connect to: stu is one of the database names
        String url="jdbc:mysql://localhost:3306/stu";
        //3. Encapsulate user name and password
        Properties info=new Properties();
        info.setProperty("user","root");
        info.setProperty("password","123456");
        Connection conn = driver.connect(url, info);
        System.out.println(conn);
    }
    /**
     * Mode 2: iteration of mode 1: there is no third-party API in the following programs, so that the programs have better portability
     */
    @Test
    public void testConnection2() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        //1. Get Driver implementation class object
        Class aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver= (Driver) aClass.newInstance();
        //2. Provide the database to be connected
        String url="jdbc:mysql://localhost:3306/stu";
        Properties info=new Properties();
        info.setProperty("user","root");
        info.setProperty("password","123456");
        //4. Get connection
        Connection conn = driver.connect(url, info);
        System.out.println(conn);
    }
    /**
     * Mode 3: iteration mode 2: replace Driver with DriverManger
     * @throws Exception
     */
    @Test
    public void testConnection3() throws Exception {
        //1. Get Driver implementation class object
        Class aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver= (Driver) aClass.newInstance();
        //2. Get basic information
        String url="jdbc:mysql://localhost:3306/stu";
        String user="root";
        String password="123456";
        //3. Registration driver
        DriverManager.registerDriver(driver);
        //4. Get connection
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);
    }
    /**
     * Mode 4: only load driver, no explicit registration driver
     * @throws Exception
     */
    @Test
    public void testConnection4() throws Exception {
        //1. Get basic information
        String url="jdbc:mysql://localhost:3306/stu";
        String user="root";
        String password="123456";
        //2. Load Driver
        Class.forName("com.mysql.jdbc.Driver");
        //3. Get connection
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);
    }
    /**
     * Final edition
     */
    @Test
    public void testConnection5() throws ClassNotFoundException, SQLException, IOException {
        //1. Read basic information in configuration file
        FileInputStream fs = new FileInputStream("jdbc.properties");
        Properties ps = new Properties();
        ps.load(fs);
        String user = ps.getProperty("user");
        String password = ps.getProperty("password");
        String url = ps.getProperty("url");
        String driver = ps.getProperty("driver");
        //2. Load driver
        Class.forName(driver);
        //3. Get connection
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);
    }
}

3, PrepareStatement

1. Operation and access to database

  • The database connection is used to send commands and SQL statements to the database server and accept the results returned by the database server.
  • stay Java.sql There are three interfaces in the package that define different ways to call the database:
    • Statement: object used to execute a static SQL statement and return the result it generates
    • PrepatedStatement: SQL statement is precompiled and stored in this object, which can be used to execute the statement efficiently multiple times
    • CallableStatement: used to execute SQL stored procedures

2. Disadvantages of using Statement to operate data table

  • Create the object by calling the createStatement() method of the Connection object. This object executes a static SQL statement and returns the execution result

  • The following methods are defined in the Statement interface to execute SQL statements

    int excuteUpdate(String sql);//Perform UPDATE operations INSERT, UPDATE, DELETE
    ResultSet executeQuery(String sql);//Execute query operation SELECT
    
  • There are disadvantages in using Statement to operate data table:

    • Problem 1: there are string splicing operations, cumbersome
    • Problem 2: SQL injection
  • SQL injection is to inject illegal SQL statement segments OR commands (such as select user, password from user) into user input data by using some systems without sufficient verification of user input data_ Table where user ='a'OR1='ADN password ='OR'1 '='1'), so as to use the system to complete malicious actions against the SQL Engine

  • For Java, to prevent SQL injection, use PreparedStatement instead of Statement

3. PreparedStatement VS Statement

  • Code readability and maintainability
  • PreparedStatement maximizes performance:
    • DBServe provides performance optimization for precompiled statements. Because precompiled statements are likely to be reused, the statements are cached in the execution code compiled by the compiler of DBServer. The next time they are called, as long as they are the same compiled statements, they do not need to be compiled, as long as the parameters are directly passed into the compiled statement execution code, they will be executed
    • In Statement statements, even if the same operation but because the data content is different, the whole Statement itself cannot match. There is no meaning of caching Statement, and no database will cache the compiled execution code of ordinary statements. This will compile the incoming Statement every time it is executed.
  • PreparedStatement prevents SQL injection

4. Use PreparedStatement to operate data table

4.1 add data

public class PreparedStatementUpdateTest {
    @Test
    public void test() {
        Connection conn = null;
        PreparedStatement pps = null;
        try {
            FileInputStream fs = new FileInputStream("jdbc.properties");
            Properties ps = new Properties();
            ps.load(fs);
            String user=ps.getProperty("user");
            String password=ps.getProperty("password");
            String url=ps.getProperty("url");
            String driver=ps.getProperty("driver");
            //2. Load driver
            Class.forName(driver);
            //3. Get connection
            conn = DriverManager.getConnection(url, user, password);
            //4. Precompiled SQL statement, return PreparedStatement instance
            String sql="insert into book(name,author,price,type)values(?,?,?,?)";
            pps = conn.prepareStatement(sql);
            //5. Fill space
            pps.setString(1,"Romance of the Three Kingdoms");
            pps.setString(2,"Luo Guanzhong");
            pps.setString(3,"30");
            pps.setString(4,"novel");
            //6. Perform action
            pps.execute();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7. Close resources
            try {
                if(pps!=null){
                    pps.close();
                }

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

4.2 batch operation

  • update and delete have the effect of batch operation
  • Batch operation here mainly refers to batch insertion
public class InsertTest {
    @Test
    public void test(){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            //Method 3: set not to allow automatic submission of data
            conn.setAutoCommit(false);

            String sql="insert into goods(name)values(?)";
            ps = conn.prepareStatement(sql);
            final int value=1000000;
            long start = System.currentTimeMillis();
            for (int i = 0; i <value ; i++) {
                ps.setObject(1,"name_"+i);
                //Mode 1
//                ps.execute();
                //Mode 2
                //1. Save SQL
                ps.addBatch();
                if(i%500==0){
                    //2. Execute batch
                    ps.executeBatch();
                    //3. Empty batch
                    ps.clearBatch();
                }

            }
            //Unified submission of data
            conn.commit();
            long end = System.currentTimeMillis();
            System.out.println("Execution time:"+(end-start));
            //Mode 1 execution time: 29135
            //Mode 2 execution time: 279 1000000 --- execution time: 9568
            //Mode 3: 1000000 --- execution time: 5000
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,ps);
        }
    }
}
  • Note: MYSQL server turns off batch operation by default. You need to turn on batch processing by adding parameters after the configuration file url.

4, Operation BLOB type field

1. BLOB type

  • In MySQL, BLOB is a binary large object, a container that can store a large amount of data, and it can hold data of different sizes
  • PreparedStatement must be used for data type of BLOB insertion, because data of BLOB type cannot be spliced with string
  • Four BLOB types of MySQL (they are the same except for the maximum amount of information stored)
type Size in bytes
TinyBlob Maximum 255
Blob 65K Max
MediumBlob Up to 16M
LongBlob 4 G Max
  • In actual use, different BLOB types are defined according to the data size to be saved
  • If the storage file is too large, the database performance will be degraded

2. Operation on BLOB type data

2.1 insertion operation

public class BlobTest {
    /**
     * Insert Blob type fields into data table customer
     * @throws Exception
     */
    @Test
    public void testInsert() throws Exception {
        Connection conn = JDBCUtils.getConnection();
        String sql="insert into customers(name,image)values(?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setObject(1,"Landlord's cat");
        FileInputStream fs = new FileInputStream(new File("Landlord's cat 1.jpg"));
        ps.setObject(2,fs);
        ps.execute();
        JDBCUtils.closeResource(conn,ps);
    }
}

2.2 query operation

public class BlobTest {    
    @Test
    public void testQuery() {
        Connection conn = null;
        PreparedStatement ps = null;
        FileOutputStream fos =null;
        InputStream bs =null;
        ResultSet rs =null;
        try {
            conn = JDBCUtils.getConnection();
            String sql="select id,name,image from customers where id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,2);
            rs = ps.executeQuery();
            if(rs.next()){
                //1. Mode 1
                /*int id=rs.getInt(1);
                String name = rs.getString(2);*/
                //2. Mode 2
                int id = rs.getInt("id");
                String name = rs.getString("name");
                Customer cust = new Customer(id, name);
                System.out.println(cust.toString());
                //Download the Blob type field and save it locally as a file
                Blob image = rs.getBlob("image");
                bs = image.getBinaryStream();
                fos = new FileOutputStream(new File("zjl2.jpg"));
                byte[] buffer = new byte[1024];
                int len;
                while((len=bs.read(buffer))!=-1){
                    fos.write(buffer,0,len);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(rs!=null){
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(fos!=null){
                    fos.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            try {
                if(bs!=null){
                    bs.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            JDBCUtils.closeResource(conn,ps);
        }
    }
}

5, Database transactions

1. Database transaction introduction

  • Transaction: a set of logical operation units that transform data from one state to another
  • Transaction processing (transaction operation): ensure that all transactions are executed as a unit of work. Even if there is a failure, the execution mode cannot be changed. When multiple operations are performed in a transaction, all the transactions are committed, and the changes are saved permanently; or the database management system will give up all the changes, and the whole transaction * * rolls back * * to the original state
  • In order to ensure the consistency of data in the database, the manipulation of data should be a discrete group of logical units. When they are all completed, the consistency of data can be maintained. When some operations of this unit fail, the whole transaction should be regarded as an error, so the operations after the starting point should be all rolled back to the starting state.

2. Data submission

  • Once the data is submitted, it cannot be rolled back
  • Once DDL operation is executed, it will be submitted automatically
  • By default, once executed, DML automatically commits
    • Cancel the automatic submission of DML operation by setting autocommit = false
  • Data is automatically submitted when the connection is closed by default
/**
 * @Description TODO
 * @Author YunShuaiWei
 * @Date 2020/6/22 11:44
 * @Version
 **/
public class TransactionUpdate {
    @Test
    public void testUpdate() {
        Connection conn = null;
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        try {
            conn = JDBCUtils.getConnection();
            //Turn off auto submit
            conn.setAutoCommit(false);
            String sql1 = "update account set money=money-100 where id=?";
            String sql2 = "update account set money=money+100 where id=?";
            ps1 = conn.prepareStatement(sql1);
            ps1.setInt(1, 2);
            ps1.execute();
            //Simulation exception
            int i = 1 / 0;
            ps2 = conn.prepareStatement(sql2);
            ps2.setInt(1, 3);
            ps2.execute();
            //Submission of data
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if (conn != null) {
                    //Transaction rollback
                    conn.rollback();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        } finally {
            try {
                if (ps1 != null) {
                    ps1.close();
                }
                if (ps2 != null) {
                    ps2.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

3. ACID attribute of transaction

  1. Atomicity
    • A transaction is an indivisible unit of work. Operations in a transaction either occur or do not occur
  2. Consistency
    • The database must be changed from one consistency state to another
  3. Isolation
    • The execution of a transaction cannot be interfered by other transactions, that is, the operations and data used within a transaction are isolated from other concurrent transactions, and the executed transactions cannot interfere with each other
  4. Durability
    • Once a transaction is committed, its changes to the data in the database are permanent, and subsequent operations and database failures should not have any impact on it

4. Isolation level of transaction

Isolation level describe
Read uncommitted Allow transactions to read changes that have not been committed by other transactions. Problems such as dirty reading, non repeatable reading and unreal reading will occur
Read committed Only allow transactions to read changes that have been committed by other transactions, which can avoid dirty reads, but non repeatable reads and unreal reads may still occur
Repeatable read Ensure that transactions can read the same value from a field multiple times. During the duration of this transaction, prohibit other transactions from updating this field, which can avoid dirty reading and non repeatable reading. However, the problem of unreal reading still exists
(SERIALIZABLE) Ensure that transactions can read the same rows from a table. During the duration of this transaction, prohibit other transactions to insert, update and delete the table. All concurrency problems can be avoided, but the performance is very low

6, Database connection pool

1. Problem introduction

  • When developing web programs based on database, the traditional pattern is basically as follows:
    • Establish a connection in the main program
    • sql operation
    • Disconnect database
  • Existing problems
    • A normal JDBC database Connection is obtained by using DriverManager. Each time you establish a Connection to the database, you need to load the Connection into memory, and then verify the user name and password. When you need to connect to the database, you need to request one from the database, and then disconnect after the execution. This way will consume a lot of resources and time, and the database Connection resources are not well reused
    • For each database connection, it must be disconnected after use. Otherwise, if the program fails to shut down due to an exception, it will cause memory leakage in the database system, and eventually restart the database
    • This kind of development cannot control the number of connection objects created, and system resources will be allocated without any scruples. For example, too many connections may lead to memory leakage and server crash

2. Overview

  • 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: build a "buffer pool" for database connection. Put a certain number of connections in the buffer pool in advance. When you need to establish a database connection, just take one out of the "buffer pool" and put it back after use
  • The database connection pool is responsible for allocating, managing, and releasing database connections, allowing applications to reuse an existing database connection instead of re establishing one
  • When the database connection pool is initialized, a certain number of database connections will be created and put into the connection pool. The number of these database connections is set by the minimum number of database connections. Whether these databases are used or not, the connection pool will always guarantee at least so many connections. The maximum number of database connections in the connection pool is limited to the maximum number of connections the connection pool can hold. When the number of requests from the application to the connection pool exceeds the maximum number of connections, these requests will be added to the waiting queue

3. Multiple open source database connection pools

  • Database connection pool usage of JDBC javax.sql.DataSource To show that datasource is only an interface, which is usually implemented by the server (Weblogic, WebSphere, Tomcat) and some open source organizations:
    • DBCP is a database connection pool provided by Apache. The connection pool of DBCP database provided by Tomcat server is faster than c3p0
    • C3P0 is a database connection pool provided by an open source organization, which is relatively slow and stable
    • Proxool is an open source project database connection pool under sourceforge. It has the function of monitoring the connection pool status
    • BoneCP is a database connection pool provided by an open source organization, which is fast
    • Druid is a database connection pool provided by Ali, which integrates the advantages of DCCP, C3P0 and Proxool
  • DataSource is usually called data source. It consists of 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

3.1 C3P0

  • Test connection
/**
 * @Description TODO
 * @Author YunShuaiWei
 * @Date 2020/6/22 21:21
 * @Version
 **/
public class DataSourceTest {
    @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://localhost:3306/ysw");
        cpds.setUser("root");
        cpds.setPassword("****");
        //Number of initial database connections
        cpds.setInitialPoolSize(10);
        //Get connection
        Connection conn = cpds.getConnection();
        System.out.println(conn);
    }

    //Use profile
    @Test
    public void testGetConnection1() throws Exception {
        ComboPooledDataSource cpds = new ComboPooledDataSource("hello");
        Connection conn = cpds.getConnection();
        System.out.println(conn);

    }
}
  • configuration file
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>

    <!-- This app is massive! -->
    <named-config name="hello">
        <property name="user">root</property>
        <property name="password">****</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/ysw</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <!--When there are not enough connections in the database connection pool, c3p0 Number of connections applied to the database server at one time-->
        <property name="acquireIncrement">5</property>
        <!--Number of connections at initialization time in the database connection pool-->
        <property name="initialPoolSize">10</property>
        <!--Maintain minimum connections-->
        <property name="minPoolSize">50</property>
        <!--Maintain maximum connections-->
        <property name="maxPoolSize">1000</property>
        <!--The most maintained database connection pool Statement Number of-->
        <property name="maxStatements">50</property>
        <!--The maximum number of Statement Number of-->
        <property name="maxStatementsPerConnection">5</property>
    </named-config>
</c3p0-config>

3.2 Druid

  • Test connection
	@Test
    public void druidTest() throws Exception {
        Properties p = new Properties();
        FileInputStream fs = new FileInputStream(new File("druid.properties"));
        p.load(fs);
        DataSource ds = DruidDataSourceFactory.createDataSource(p);
        Connection conn = ds.getConnection();
        System.out.println(conn);
    }
  • druid.properties file
url=jdbc:mysql://localhost:3306/ysw
username=root
password=****
driverClassName=com.mysql.jdbc.Driver

7, Apache dbutils

1. Introduction

  • Commons dbutils is an open source JDBC tool class library provided by Apache organization. It is a simple encapsulation of JDBC with low learning cost
  • Encapsulates the operation of adding, deleting, modifying and querying the database

2. Test

  • JDBC utils tool class
public class JDBCUtils {
    //close resource
    public static void closeResource(Connection conn, PreparedStatement pps){
        try {
            if(pps!=null){
                pps.close();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /**
     * Get connection through druid connection pool
     * @Param []
     * @return java.sql.Connection
     **/
    public static Connection getDruidConnection(){
        Properties ps = new Properties();
        Connection conn = null;
        try {
            FileInputStream fs = new FileInputStream(new File("druid.properties"));
            ps.load(fs);
            DataSource ds = DruidDataSourceFactory.createDataSource(ps);
            conn = ds.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
}
  • CRUD
/**
 * @Description TODO
 * @Author YunShuaiWei
 * @Date 2020/6/23 10:19
 * @Version
 **/
public class dbUtilsTest {
    //Insert test
    @Test
    public void insertTest() {
        Connection conn = null;
        int i = 0;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getDruidConnection();
            String sql = "insert into account(name,money)values(?,?)";
            i = runner.update(conn, sql, "yunsw", "1000");
            System.out.println("Successfully added " + i + " Records!");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    //Query test
    @Test
    public void testQuery() {
        QueryRunner runn = new QueryRunner();
        Connection conn = JDBCUtils.getDruidConnection();
        String sql = "select * from account where id=?";
        BeanHandler<AccountDAO> handler = new BeanHandler<>(AccountDAO.class);
        AccountDAO query = null;
        try {
            query = runn.query(conn, sql, handler, 7);
            System.out.println(query);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    //Query multiple records: BeanListHandler
    @Test
    public void testQueryBeanListHandler() {
        QueryRunner runner = new QueryRunner();
        Connection conn = JDBCUtils.getDruidConnection();
        String sql = "select id,name,money from account where money<?";
        BeanListHandler handler = new BeanListHandler(AccountDAO.class);
        try {
            List<AccountDAO> list = (List<AccountDAO>) runner.query(conn, sql, handler, 10000);
            list.forEach(System.out::println);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    //Query special value
    @Test
    public void countTestScalarHandler() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getDruidConnection();
            String sql = "select Max(money) from account";
            ScalarHandler handler = new ScalarHandler();
            Double query = (Double) runner.query(conn, sql, handler);
            System.out.println(query);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }
}

Tags: Database JDBC SQL MySQL

Posted on Tue, 23 Jun 2020 03:15:26 -0400 by Chris-the dude