Supplement 4 When the connection is closed by default, we will submit it automatically,

Each rollback is rolled back to the previous submission

Transfer operation without considering database transactions

When we finished the first transaction, we committed. As a result, an exception occurred, resulting in a direct exit. The subsequent transactions are no longer executed.

 DDL,DCL,DML

Concurrency of database

1. Dirty reading:

For T1 and T2 transactions, first T1 changes a field in the database to 1,

After that, T2 changed this field to 2, but did not submit to save. At this time, when T1 transaction reads this field again,

Reading is 2, which is dirty reading

2. Non repeatable:

At the beginning, this transaction reads the value of a field as 1

T1 updates and submits the value of the field and changes it to 2. When we directly read the field without closing the transaction T2, it is still 1 Only when we close the T2 transaction and restart a new transaction to read this field. Then the read value will be the value after T1 update and modification

3. Unreal reading

For both T1 and T2 transactions, when we first read the value of one field from transaction T1 as 1, and then another transaction T2 inserts multiple fields into the database, then transaction T1 reads the number of fields again as before, and the number of fields is not refreshed. Only when we close transaction T1 and reopen it again can we read the number of fields after T2 is inserted

Unrepeatable reading may be similar to phantom reading, but they are different. Because non repeatable reading is the update of a field, while phantom reading is the insertion of a field

Transfer operation considering database transactions

package JDBC;

import JDBC_Utils.jdbc_Util;

import java.lang.reflect.Field;
import java.sql.*;

public class TransactionTest {
    /**
     * //Considering the transfer operation after database transactions, version 1
     */
    //Write the general update method first
    public int update(Connection connection,String sql,Object...args) {
        PreparedStatement ps=null;
        try {
            //1. Establish connection
            connection=jdbc_Util.getConnection();
            //2. Get ps
            ps=connection.prepareStatement(sql) ;
            //3. Make mistakes
            for (int i =0;i<args.length;i++) {
                ps.setObject(i+1,args[i]);
            }
            //4.
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                //For thread pool, we need to restore transaction submission to automatic
                //If the Connection is not closed at this time, you need to restore its automatic submission status
                connection.setAutoCommit(true);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            jdbc_Util.closeResoure(connection,ps);
        }
        return 0;
    }
    public void test01()  {
        Connection connection=null;
        try {
            connection=jdbc_Util.getConnection();
            //Turn off the automatic submission first, and then we can only connect at last commit(); Submitted manually
            connection.setAutoCommit(false);
            String sql1 = "update user_table set balance = balance - 100 where user = ?";
            update(connection,sql1, "AA");

            //Analog network exception
            System.out.println(10 / 0);

            String sql2 = "update user_table set balance = balance + 100 where user = ?";
            update(connection,sql2, "BB");
            connection.commit();
        } catch (Exception e) {
            e.printStackTrace();
            //If an exception occurs, we catch it
            //Then the transaction cannot be committed, and we have to roll back to the last committed location
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            jdbc_Util.closeResoure(connection,null);
        }
    }
    /**
     * Version2: Second generation version
     */
    public void test03() throws Exception {
        Connection connection=jdbc_Util.getConnection();
        //Gets the isolation level of the current connection
        System.out.println(connection.getTransactionIsolation());
        //Set the isolation level of the database
        connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        //Cancel auto submit data
        connection.setAutoCommit(false);
        String sql="select user,password,balance from user_table where user =?";

    }
    public void test02 () throws Exception {
        Connection connection=jdbc_Util.getConnection();
        //Cancel auto submit data
        connection.setAutoCommit(false);
        String sql="update user_table set balance = ? where user = ?";
        update(connection,sql,5000,"CC");
        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 connection,Class<T> clazz,String sql,Object...args) throws Exception{
        PreparedStatement ps=null;
            ps=connection.prepareStatement(sql);
            for (int i=0;i<args.length;i++) {
                ps.setObject(i+1,args[i]);
            }
            ResultSet rs=ps.executeQuery();
            ResultSetMetaData rsd=rs.getMetaData();
            int columnCount= rsd.getColumnCount();
            if (rs.next()) {
                //Get object instance
                T t=clazz.newInstance();
                for (int i=0;i<columnCount;i++) {
                    //Get column value
                    Object cloumnValue=rs.getObject(i+1);
                    //Get the column name. If it is the alias of the column, get the alias of the column
                    String cloumnLabel=rsd.getColumnLabel(i+1);
                    Field field=clazz.getDeclaredField(cloumnLabel);
                    field.setAccessible(true);
                    field.set(t,cloumnValue);
            }
        }
            jdbc_Util.close(null,ps,rs);
        return null;
    }
}

We encapsulate the method into a DAO package

Basedo class

package JDBC_DAO;

import JDBC_Utils.jdbc_Util;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public abstract class BaseDAO {
    /**
     * Encapsulates various general operations on the data table
     */
    //1. General addition, deletion and modification operations -- version 2.0 (considering previous transactions)
    //Because we consider the transaction here, the Connection object here may perform more than one operation. This transaction may include multiple operations such as adding, deleting and so on
    //So we passed the Connection object
    public int update(Connection connection,String sql,Object...args) {
        PreparedStatement ps=null;
        try {
            ps=connection.prepareStatement(sql);
            for (int i=0;i< args.length;i++) {
                ps.setObject(i+1,args[i]);
            }
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbc_Util.closeResoure(null,ps);
        }
        return 0;
    }
    //2. / / general query operation, used to return a record in the data table (version 2.0: consider the previous transaction)
    public <T> T getInstance(Connection connection, Class<T> clazz,String sql,Object...args) throws Exception{
        PreparedStatement ps= null;
        ps=connection.prepareStatement(sql);
        for (int i=0;i< args.length;i++) {
            ps.setObject(i+1,args[i]);
        }
        ResultSet rs =ps.executeQuery();
        ResultSetMetaData rsd=rs.getMetaData();
        int columnCount= rsd.getColumnCount();
        if (rs.next()) {
            T t = clazz.newInstance();
            for (int i = 0; i < columnCount; i++) {
                Object cloumnValue = rs.getObject(i + 1);
                String cloumnLabel = rsd.getColumnLabel(i + 1);
                Field field = clazz.getDeclaredField(cloumnLabel);
                field.setAccessible(true);
                field.set(t, cloumnValue);
            }
            return t;
        }
        jdbc_Util.close(null,ps,rs);
        return null ;
    }
    // General query operation, which is used to return a set composed of multiple records in the data table (version 2.0: consider previous transactions)
    public <T> List<T> getForList(Connection connection, Class<T> clazz, String sql, Object...args) throws Exception{
        PreparedStatement ps= null;
        ps=connection.prepareStatement(sql);
        for (int i=0;i< args.length;i++) {
            ps.setObject(i+1,args[i]);
        }
        ResultSet rs =ps.executeQuery();
        ResultSetMetaData rsd=rs.getMetaData();
        int columnCount= rsd.getColumnCount();
        ArrayList<T> list = new ArrayList<>();
        if (rs.next()) {
            T t = clazz.newInstance();
            for (int i = 0; i < columnCount; i++) {
                Object cloumnValue = rs.getObject(i + 1);
                String cloumnLabel = rsd.getColumnLabel(i + 1);
                Field field = clazz.getDeclaredField(cloumnLabel);
                field.setAccessible(true);
                field.set(t, cloumnValue);
            }
            list.add(t);
        }
        jdbc_Util.close(null,ps,rs);
        return list;
    }
    //General method for querying special values
    public <E> E getValue(Connection connection,String sql,Object...args) throws Exception{
        PreparedStatement ps =null ;
        ps=connection.prepareStatement(sql) ;
        ResultSet rs=ps.executeQuery();
        if (rs.next()) {
            //What we usually pass over is a table. We only need to query a special value, so there is only one column name,
            // Then you can directly getObject(1)
            //It indicates that the query is the first data, that is, this special data
            return (E) rs.getObject(1);
        }
        return null;
    }
}

Interface

Write out the functions you want to realize in the form of interface

package JDBC_DAO;

import JDBC_Bean.Customer;

import java.sql.Connection;
import java.util.Date;
import java.util.List;
@SuppressWarnings({"all"})
public interface CustomerDAO {

    /*
     * This interface is used to standardize common operations on the customers table
     */
        /**
         *
         * @Description Add cust object to database
         */
        void insert(Connection conn, Customer cust);
        /**
         *
         * @Description Delete a record in the table for the specified id
         */
        void deleteById(Connection conn,int id);
        /**
         *
         * @Description For cust objects in memory, modify the records specified in the data table
         */
        void update(Connection conn,Customer cust);
        /**
         *
         * @Description Query the specified id to get the corresponding Customer object
         */
        Customer getCustomerById(Connection conn,int id);
        /**
         *
         * @Description A collection of all records in a query table
         */
        List<Customer> getAll(Connection conn);
        /**
         *
         * @Description Returns the number of data entries in the data table
         */
        Long getCount(Connection conn);

        /**
         *
         * @Description Returns the largest birthday in the data table
         */
        Date getMaxBirth(Connection conn);

    }

Implementation class:

Implement the interface by calling the method encapsulated in the BaseDAO class

package JDBC_DAO;

import JDBC_Bean.Customer;

import java.sql.Connection;
import java.util.Date;
import java.util.List;

public class CustomerDAOImpl extends BaseDAO implements CustomerDAO{
// public int update(Connection connection,String sql,Object...args) {
    @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());
    }
//  public <T> T getInstance(Connection connection, Class<T> clazz,String sql,Object...args) throws Exception{
    @Override
    public Customer getCustomerById(Connection conn, int id) {
        String sql="select name,email,birth from customers where id = ?" ;
        Customer customer= null;
        try {
            customer = getInstance(conn, Customer.class,sql,id);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            return customer;
        }
    }

    @Override
    public List<Customer> getAll(Connection conn) {
        String sql="select name,email,birth from customers where 1=1";
        List<Customer> list= null;
        try {
            list = getForList(conn, Customer.class,sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public Long getCount(Connection conn) {
        String sql="select count(*) from customers";
        try {
            return getValue(conn,sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Date getMaxBirth(Connection conn) {
        String sql="select max(birth) from customers";
        try {
            return getValue(conn,sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

Gets the generic parameters of the parent class

Get the type of the parent class with generics,

After that, the strong conversion is performed, and then the method is called according to the variable after the strong conversion to obtain the generic parameters of the parent class. The return type is a generic array

After that, you can get the parent generic by strongly converting the first parameter of the generic

Who does this stand for?

There is only one object represented by this. When the object from which class new is called, we will default that this is the object representing which class

Suppose there are two classes a and B, a inherits class B, writes an eat method in class B, and we write this in the eat method, Then when we call the eat method with new class A, this represents class a object.

When we call the eat method from class new B, this represents class B object

How to import the external resource jar package into the project library we need?

 

Creation of properties configuration file:

Note: no double quotation marks, no punctuation. The properties file is actually a text file,

When setting the file name later, we should remember to write it as properties

Database connection pool technology

After importing the jar package, proceed to the database connection pool technology

package JDBC_Pools;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.File;
import java.io.FileInputStream;
import java.sql.*;
import java.util.Properties;

public class jdbc_Util {
    /**
     *  Database connection pool technology using C3P0
     *
     */
    public void testGetConnectionByC3P0() throws SQLException {
        ComboPooledDataSource cpds = new ComboPooledDataSource("C3P0");
        Connection conn = cpds.getConnection();
        System.out.println(conn);
    }

    /**
     * Database connection pool technology using DBCP
     *
     */
    private static DataSource source ;

    /**
     * In order to save the consumption and rational utilization of resources, we only create one data connection pool at a time
     * Then we put the factory creation operation of the database connection pool in the static code block
     * static It is loaded only as the class is loaded, and the class is loaded only once. Therefore, the database connection pool is created only once
     */
    static {
        try {
            Properties properties = new Properties();
            //Get the information in the configuration file
            //Method 1: using class loader
            // InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("DBCP.properties");
            //Mode 2: src / DBCP Properties means the properties file in the src directory
            FileInputStream fis = new FileInputStream(new File("src/DBCP.properties"));
            properties.load(fis);
            source= BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //Use profile
    public void testGetConnectionByDBCP() throws Exception {
        Connection connection= source.getConnection();
        System.out.println(connection);
    }

The most commonly used Druid connection method in today's enterprises

package JDBC_Pools;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;

public class Druid_test {
    /**
     * Using Druid's database connection pool technology [technology now used in the enterprise]
     *
     */
    private static DataSource source;
    static {
        try {
            FileInputStream fis = new FileInputStream(new File("src/Druid.properties"));
            Properties properties = new Properties();
            properties.load(fis);
            DataSource source = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public void testGetConnectionByDruid() throws Exception {
        Connection connection= source.getConnection();
        System.out.println(connection);
    }
}

Commons dbutils JDBC open source tool class

Commons dbutils is an open source JDBC tool class library provided by Apache organization, which encapsulates the operations of adding, deleting, modifying and querying databases

Code test the tool:

package JDBC_Pools;

import JDBC_Bean.Customer;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class Queery_Runner_test {
    public void testInsert() {
        Connection connection=null;
        try {
            QueryRunner runner = new QueryRunner();
            connection= jdbc_Util.getConnection();
            String sql = "insert into customers(name,email,birth) " +
                    "values(?,?,?)";
            int insertCount= runner.update(connection,sql,"leomessi"
            ,"d","1987-6-24");
            System.out.println("add to"+insertCount+"Data bar");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbc_Util.closeResoure(connection,null);
        }
    }
    /*
     * BeanHander:Is the implementation class of ResultSetHandler interface, which is used to encapsulate a record in the table.
     */
    public void test02() {
        Connection connection =null;
        try {
            connection=jdbc_Util.getConnection();
            QueryRunner runner=new QueryRunner();
            BeanHandler<Customer> beanHandler=new BeanHandler<>(Customer.class);
            String sql="select * from customers where id =?";
            Customer customer = runner.query(connection,sql, beanHandler, 3);
            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbc_Util.closeResoure(connection,null);
        }
    }
    /*
     * BeanListHandler:It is the implementation class of ResultSetHandler interface, which is used to encapsulate the collection composed of multiple records in the table.
     */
    public void test03() {
        Connection connection =null;
        try {
            connection=jdbc_Util.getConnection();
            QueryRunner queryRunner=new QueryRunner();
            BeanListHandler<Customer> beanListHandler=new BeanListHandler<>(Customer.class);
            String sql="select * from customers where id<?";
            List<Customer> list=queryRunner.query(connection,sql,beanListHandler,10);
            for (int i = 0; i <list.size() ; i++) {
                System.out.println(list.get(i));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbc_Util.closeResoure(connection,null);
        }
    }
    /*
     * MapHander:It is the implementation class of ResultSetHandler interface and corresponds to a record in the table.
     * Take the field and the value of the corresponding field as the key and value in the map
     */
    public void test04() {
        Connection connection=null;
        try {
            connection=jdbc_Util.getConnection();
            QueryRunner runner = new QueryRunner();
            MapHandler mapHandler=new MapHandler();
            String sql="select * from customers where id =10";
            Map<String,Object> map=runner.query(connection,sql,mapHandler,23);
            System.out.println(map);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbc_Util.closeResoure(connection,null);
        }
    }
    /*
     * MapListHander:It is the implementation class of ResultSetHandler interface and corresponds to multiple records in the table.
     * Take the field and the value of the corresponding field as the key and value in the map. Add these maps to the List
     */
    public void test05() {
        Connection connection=null;
        try {
            connection=jdbc_Util.getConnection();
            QueryRunner runner = new QueryRunner();
            String sql="select * from customers where id<10";
            MapListHandler mapListHandler=new MapListHandler();
            //What we return here is a set. Each element in the set is a key value pair of Map type. The key corresponds to the column name and the value corresponds to the column value
            List<Map<String,Object>> map=runner.query(connection,sql,mapListHandler,10);
            for (int i=0;i<map.size();i++) {
                System.out.println(map.get(i));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbc_Util.closeResoure(connection,null);
        }
    }
    /*
     * ScalarHandler:Used to query special values
     */
    public void testQuery5(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = jdbc_Util.getConnection();

            String sql = "select count(*) from customers";

            ScalarHandler handler = new ScalarHandler();

            Long count = (Long) runner.query(conn, sql, handler);
            System.out.println(count);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
           jdbc_Util.closeResoure(conn, null);

        }

    }

    public void testQuery6(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = jdbc_Util.getConnection();

            String sql = "select max(birth) from customers";

            ScalarHandler handler = new ScalarHandler();
            Date maxBirth = (Date) runner.query(conn, sql, handler);
            System.out.println(maxBirth);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            jdbc_Util.closeResoure(conn, null);

        }
    }
    /*
     * Implementation class of custom ResultSetHandler
     */
    public void test07() {
        Connection connection=null;
        try {
            connection=jdbc_Util.getConnection();
            QueryRunner runner=new QueryRunner();
            String sql="select * from customers where id = ?";
            ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>(){
                @Override
                public Customer handle(ResultSet resultSet) throws SQLException {
                    if (resultSet.next()) {
                        int id=resultSet.getInt(1);
                        String name=resultSet.getString("name");
                        String email=resultSet.getString("email");
                        Date date=resultSet.getDate("birth");
                        Customer customer = new Customer(id, name, email, date);
                        return customer;
                    }
                    return null;
                }
            };
            Customer customer=runner.query(connection,sql,handler,20);
            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbc_Util.closeResoure(connection,null);
        }
    }
}