affair

• Why do we have business?

Demonstrate transactions using the command line.

• Open a transaction

  start transaction;

• Submit or roll back transactions

  Commit; commit the transaction, and the data will be written to the database on disk Rollback; data rollback, back to its original state.

1. Turn off automatic submission.

1. Demonstration transaction

Demonstrating transactions in a code-based manner

[@Test](https://my.oschina.net/azibug)
public void testTransaction(){
	
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	try {
		conn = JDBCUtil.getConn();
		
		//Connection, transaction is automatically committed by default. Turn off automatic submission.
		conn.setAutoCommit(false);
		
		String sql = "update account set money = money - ? where id = ?";
		ps = conn.prepareStatement(sql);
		
		//Deduction, deduction ID of 100 yuan
		ps.setInt(1, 100);
		ps.setInt(2, 1);
		ps.executeUpdate();

		int a = 10 /0 ;

		//Add money, give ID 2 plus 100 yuan
		ps.setInt(1, -100);
		ps.setInt(2, 2);
		ps.executeUpdate();
		
		//Success: Submit the transaction.
		conn.commit();
		
	} catch (SQLException e) {
		try {
			//Incident: Roll back the transaction
			conn.rollback();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		e.printStackTrace();
		
	}finally {
		JDBCUtil.release(conn, ps, rs);
	}
}

The Characteristics of Transactions

* Atomicity

* Consistency

* Isolation

* Persistence

Transaction isolation level

Read uncommitted

Read submitted

Repeatable reading

Serializable

mySql's default isolation level is repeatable

Oracle's default isolation level is to read submitted

Read Unsubmitted Demo

1. Set the isolation level of window A to read uncommitted

1. Both windows open transactions separately

Read the presentation submitted

1. Set the isolation level of window A to read submitted

1. Both windows of A and B open transactions and perform update operations in B windows.

1. The query results executed in window A are inconsistent. One is before the transaction is committed in window B, and the other is after the transaction is committed in window B.

Serializable

• According to efficiency, from high to low

• Depending on the degree of interception, from high to low

Security Hidden Dangers of Transactions

* Reading

Dirty Reading

> One transaction reads uncommitted data from another transaction

Non-repeatable reading

> One transaction reads the data submitted by another transaction, resulting in inconsistencies between the two queries.

phantom read

> One transaction reads data from another transaction insert, resulting in inconsistent query results.

* Writing

Lost updates

Resolving Lost Updates

Pessimistic Lock

Optimistic Lock

Database connection pool

Custom database connection pool

code implementation

Problems arising:

1. You need to remember the addBack method extra.

2. Single case.

3. Interface-oriented programming is not possible. 

	UserDao dao = new UserDaoImpl();
	dao.insert();


	DataSource dataSource = new MyDataSource();

	Because there is no addBack method defined in the interface. 

4. How to solve it? Take addBack as the starting point.

Solve the problem of custom database connection pool.

How to extend a method?

1. Direct source modification is not possible.

2. Inheritance, we must know who the specific implementation of this interface is.

Use Decorator Mode

Generally speaking, the idea of Java object-oriented is to use interfaces and implementation classes, create objects that point to the interfaces that implement classes to call implementation methods, but if the methods of implementing classes themselves are not enough to meet the requirements, something needs to be added, without changing the interface and implementation classes or inheriting them, create another wrapper class implementation. The method of this interface is modified, and the interface object is passed in the place where the construction method is constructed. When creating the object pointing to the interface of the wrapper class, the parameter of the implementation class instance can be transferred to the parent class automatically. That is, the object is created or the object pointing to the interface of the implementation class. The object is unchanged, but the method is called. It's time to call methods in wrapper classes

Packaging class code

import java.sql.Array;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.NClob;
import java.sql.PreparedStatement;
import java.sql.SQLClientInfoException;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.Struct;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;

public class ConnectionWrap implements Connection{

	Connection connection=null;
	List<Connection> list=null;
	//Pass in the connection object parameter and list in the encapsulated object
	public ConnectionWrap(Connection conn,List<Connection> list) {
		super();
		this.connection=conn;
		this.list=list;
	}
//When the copy close method meets the requirement, it does not close the connection but returns it to the connection pool.
	@Override
	public void close() throws SQLException {
		System.out.println("The number of objects that are ready to be returned is"+list.size());
		list.add(connection);
		System.out.println("The number of objects returned is"+list.size());//It shows + 1 above.
	}
	
	@Override
	public PreparedStatement prepareStatement(String sql) throws SQLException {
		return connection.prepareStatement(sql);
	}
	
	@Override
	public Statement createStatement() throws SQLException {
		return connection.createStatement();
	}

Custom connection pool code

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

import javax.sql.DataSource;

import s20190719.jdbc.util.JDBCUtil;
import s2019072.connection.wrap.ConnectionWrap;

public class MyDatasource implements DataSource{
	//Create a list to place the connection object
	List<Connection> list = new ArrayList<Connection>();
	//Constructor automatically creates 10 connection objects in list when creating instances
	public  MyDatasource() {
		for (int i = 0; i < 10; i++) {
			Connection conn=JDBCUtil.getConn();
			list.add(conn);
		}
	}

	@Override
	public Connection getConnection() throws SQLException {
		//Increase capacity first when there are no connection objects in connection pool
		if (list.size()==0) {
			for (int i = 0; i < 5; i++) {
				Connection conn=JDBCUtil.getConn();
				list.add(conn);
			}
		}
		//Remove a connection object from the list and encapsulate it to the caller
		Connection conn = list.remove(0);
		Connection connection=new ConnectionWrap(conn, list);
		return connection;
	}

Open source connection pools (two commonly used)

DBCP connection pool

1. Importing jar files

2. Do not use configuration files:

	public void testDBCP01(){
	
		
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			
			//1. Building Data Source Objects
			BasicDataSource dataSource = new BasicDataSource();
			//What kind of database is connected, which database is accessed, user name, password.
			//jdbc:mysql://localhost/bank master protocol: subprotocol: //local/database
			dataSource.setDriverClassName("com.mysql.jdbc.Driver");
			dataSource.setUrl("jdbc:mysql://localhost/bank");
			dataSource.setUsername("root");
			dataSource.setPassword("root");
			
			
			//2. Get the connection object
			conn = dataSource.getConnection();
			String sql = "insert into account values(null , ? , ?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, "admin");
			ps.setInt(2, 1000);
			
			ps.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.release(conn, ps);
		}
		
	}

3. Use configuration file: Copy the configuration properties file to the src directory

Open to modify connection settings and other parameters as required

#connections setting up
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc
username=root
password=

#<! - Initialize the connection - >
initialSize=10

#Maximum number of connections
maxActive=50

#<! - Maximum idle connection
maxIdle=20

#<! - Minimum idle connection - >
minIdle=5

#<! - Overtime waiting time in milliseconds is 6000 milliseconds/1000 equal to 60 seconds - >
maxWait=60000

#The format of the join attribute attributes attached to the JDBC driver when establishing a connection must be as follows: [attribute name = property;] 
#Note that the attributes "user" and "password" will be passed explicitly, so they need not be included here.
connectionProperties=useUnicode=true;characterEncoding=gbk

#Specifies the auto-commit state of the connection created by the connection pool.
defaultAutoCommit=true

#driver default specifies the transaction level (Transaction Isolation) of the connection created by the connection pool.
#Available values are one of the following: (Details can be found in javadoc.) NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

	Connection conn = null;
	PreparedStatement ps = null;
	try {
		BasicDataSourceFactory factory = new BasicDataSourceFactory();
		Properties properties = new Properties();
		InputStream is = new FileInputStream("src//dbcpconfig.properties");
		properties.load(is);
		DataSource dataSource = factory.createDataSource(properties);
		
		//2. Get the connection object
		conn = dataSource.getConnection();
		String sql = "insert into account values(null , ? , ?)";
		ps = conn.prepareStatement(sql);
		ps.setString(1, "liangchaowei");
		ps.setInt(2, 100);
		
		ps.executeUpdate();
		
	} catch (Exception e) {
		e.printStackTrace();
	}finally {
		JDBCUtil.release(conn, ps);
	}

* C3P0

1. Copy jar files to lib directory

2. Do not use configuration file

	Connection conn = null;
	PreparedStatement ps = null;
	try {
		//1. Create data source
		ComboPooledDataSource dataSource = new ComboPooledDataSource();
		//2. Setting up information for connecting data
		dataSource.setDriverClass("com.mysql.jdbc.Driver");
		
		//Forget - > go to the previous code - > JDBC documentation
		dataSource.setJdbcUrl("jdbc:mysql://localhost/bank");
		dataSource.setUser("root");
		dataSource.setPassword("root");
		
		//2. Get the connection object
		conn = dataSource.getConnection();
		String sql = "insert into account values(null , ? , ?)";
		ps = conn.prepareStatement(sql);
		ps.setString(1, "admi234n");
		ps.setInt(2, 103200);
		
		ps.executeUpdate();
		
	} catch (Exception e) {
		e.printStackTrace();
	}finally {
		JDBCUtil.release(conn, ps);
	}

3. Use configuration file

Support property and xml formats, recommend xml

Configuration file name: c3p0-config.xml (fixed)

Configuration file path: src (class path)

Configuration file content: Specify rules, usually for different database configurations with different requirements

New ComboPooled Data Source ("Name"); // Use Configuration File "Named Configuration"

<c3p0-config>
    <!-- Named Configuration -->
    <named-config name="test">
        <!-- Four Basic Parameters for Connecting Database -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/mytest01</property>
        <property name="user">root</property>
        <property name="password">321</property>
        <!-- How many increments per time if the data connection in the pool is insufficient -->
        <property name="acquireIncrement">5</property>
        <!-- Number of Initial Connections -->
        <property name="initialPoolSize">10</property>
        <!-- Minimum number of connections -->
        <property name="minPoolSize">10</property>
        <!-- maximum connection -->
        <property name="maxPoolSize">100</property>
        <!-- JDBC Standard parameters to control loading within the data source PrepareStatements Number -->
        <property name="maxStatements">200</property>
        <!-- Maximum cache owned by a single connection in the connection pool statements number -->
        <property name="maxStatementsPerConnection">5</property>
    </named-config>
</c3p0-config>

Default configuration when not specified: new ComboPooled DataSource (); // Use configuration file "Default configuration"

<c3p0-config>
    <!-- Default configuration, which is used if no use is specified -->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/mytest01</property>
        <property name="user">root</property>
        <property name="password">321</property>
        <property name="acquireIncrement">50</property>
        <property name="initialPoolSize">100</property>
        <property name="minPoolSize">50</property>
        <property name="maxPoolSize">1000</property>
        <!-- intergalactoApp adopts a different approach to configuring statement caching -->
        <property name="maxStatements">0</property>
        <property name="maxStatementsPerConnection">5</property>
        <!-- he's important, but there's only one of him -->
        <user-overrides user="master-of-the-universe">
            <property name="acquireIncrement">1</property>
            <property name="initialPoolSize">1</property>
            <property name="minPoolSize">1</property>
            <property name="maxPoolSize">5</property>
            <property name="maxStatementsPerConnection">50</property>
        </user-overrides>
    </default-config>
</c3p0-config>

		//By default, the class loader looks for default-config branches in xml. So you don't need to write more code.
		ComboPooledDataSource dataSource = new ComboPooledDataSource();

		//2. Get the connection object
		conn = dataSource.getConnection();
		String sql = "insert into account values(null , ? , ?)";
		ps = conn.prepareStatement(sql);
		ps.setString(1, "admi234n");
		ps.setInt(2, 103200);

Further simplify the JDBCUtil code: (to get the connection in the method of the dao implementation class in one sentence conn=JDBCUtil.getConn())

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

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JDBCUtil2 {

	static ComboPooledDataSource datasource = null;

	static {
			datasource=new ComboPooledDataSource();
	}
//Redefining getconn method
	public static Connection getConn() throws SQLException {
			return datasource.getConnection();
	}
	
	public static void release(Connection conn, Statement st, ResultSet rs) {
		closeConn(conn);
		closeRs(rs);
		closeSt(st);
	}
	public static void release(Connection conn, Statement st) {
		closeConn(conn);
		closeSt(st);
	}
	private static void closeConn(Connection conn) {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn = null;
		}
	}
	private static void closeSt(Statement st) {
		try {
			if (st != null) {
				st.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			st = null;
		}
	}
	private static void closeRs(ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			rs = null;
		}
	}
}

DBUtils(QueryRunner)

Add, delete and modify update

			//dbutils only helps us simplify the CRUD code, but the creation and acquisition of connections. Not in his consideration
	QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());

	
	//increase
	//queryRunner.update("insert into account values (null , ? , ? )", "aa" ,1000);
	
	//delete
	//queryRunner.update("delete from account where id = ?", 5);
	
	//To update
	//queryRunner.update("update account set money = ? where id = ?", 10000000 , 6);

query

1. Anonymous Implementation Class of Direct new Interface

	QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());


	Account  account =  queryRunner.query("select * from account where id = ?", new ResultSetHandler<Account>(){

		[@Override](https://my.oschina.net/u/1162528)
		public Account handle(ResultSet rs) throws SQLException {
			Account account  =  new Account();
			while(rs.next()){
				String name = rs.getString("name");
				int money = rs.getInt("money");
				
				account.setName(name);
				account.setMoney(money);
			}
			return account;
		}
		 
	 }, 6);
	
	System.out.println(account.toString());

2. Use the implementation class that the framework has written directly.

* Query a single object BeanHandler<Account>(Account.class)

	QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());
	//Query a single object
	Account account = queryRunner.query("select * from account where id = ?", 
			new BeanHandler<Account>(Account.class), 8);


* Query multiple objects BeanListHandler<Account>(Account.class)

	QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());
	List<Account> list = queryRunner.query("select * from account ",
			new BeanListHandler<Account>(Account.class));

Common implementation classes of ResultSetHandler

The following two are the most frequently used

BeanHandler, which encapsulates the individual data queried into an object
 BeanListHandler, which encapsulates multiple queried data into a List < Object >

ArrayHandler,  The queried individual data is encapsulated in an array
ArrayListHandler,  The queried data is encapsulated into a set, in which the elements are arrays. 



MapHandler,  The individual data queried is encapsulated into one map
MapListHandler,The queried data is encapsulated into a collection, in which the elements are map.  




ColumnListHandler
KeyedHandler
ScalarHandler

# Summary

## Affairs

Demonstration using the command line

Using Code Demonstration

Dirty reading,

Not repeatable.

phantom read Lost updates

Pessimistic Lock
 Optimistic Lock

Four isolation levels
	Read uncommitted
	Read submitted
	Repeatable reading
	Serializable

## Data connection pool

• DBCP

  No configuration

  Use configuration

• C3P0

  No configuration

  Use configuration (must be mastered)

• Custom connection pool

  Decorator Model

##DBUtils

queryRunner.update();
queryRunner.query