Java, JDBC core technology, transaction, thread pool

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

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
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

Atomicity
- A transaction is an indivisible unit of work. Operations in a transaction either occur or do not occur
Consistency
- The database must be changed from one consistency state to another
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
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>  <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>  <property name="acquireIncrement">5</property>  <property name="initialPoolSize">10</property>  <property name="minPoolSize">50</property>  <property name="maxPoolSize">1000</property>  <property name="maxStatements">50</property>  <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); } } }