-- Check autocommit Whether auto-commit is turned off
> SHOW VARIABLES LIKE 'autocommit';  
"autocommit"	"ON"

-- The current terminal temporarily turns off autocommit
> SET autocommit = off;

-- start transaction
> START TRANSACTION;

-- Commit or roll back the transaction
commit;     --- Commit the transaction and the data will be written to the database on disk
rollback ;  --- The data is rolled back, back to the original state.

CREATE TABLE account(
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(64) NOT NULL,
    `money` FLOAT DEFAULT 0
);

INSERT INTO account VALUES (null,'WeiyiGeek',1000),(null,'muzi',1000);

package top.weiyigeek.Web;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import top.weiyigeek.Util.db;

public class Test_transaction {
  @Test
  public void transaction() {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
      conn = db.getConn();
      
      //1. Connection: Transactions are automatically committed by default.  Turn off autocommit.
      conn.setAutoCommit(false);
      
      String sql = "update account set money = money - ? where id = ?";
      ps = conn.prepareStatement(sql);
      
      //2. Deduct money: deduct 100 yuan with ID 1
      ps.setInt(1, 100);
      ps.setInt(2, 1);
      ps.executeUpdate();
      
      //3. Set the exception to view the impact of transaction submission
      int a = 10 /0 ;
      
      //4. Add money, add 100 yuan to ID 2
      ps.setInt(1, -100);
      ps.setInt(2, 2);
      ps.executeUpdate();
      
      //5. Success: Commit the transaction.
      conn.commit();
      
    } catch (SQLException e) {
      try {
        //6. If an exception occurs in step 3, enter catch{} to roll back the transaction
        conn.rollback();
        System.out.println("\nSQL Transaction commit exception, transaction has been rolled back automatically!");
      } catch (SQLException e1) {
        e1.printStackTrace();
      }
      e.printStackTrace();
      
    }finally {
      db.release(conn, ps, rs);
    }
  }
}

-- Inquire
SQL > select @@tx_isolation;
-- +-----------------+
-- | @@tx_isolation  |
-- +-----------------+
-- | REPEATABLE-READ |
-- +-----------------+
-- 1 row in set (0.00 sec)

-- Fix setting the transaction isolation level of the current window to
SQL > set session transaction isolation level read uncommitted;
-- Query OK, 0 rows affected (0.00 sec)

-- 1.Modify the current isolation level to`read uncommitted`
db_window-1 > set session transaction isolation level read uncommitted;
-- Query OK, 0 rows affected (0.00 sec)

-- 2.Check whether the modification is successful
db_window-1 > select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

-- 3.The isolation level of terminal 2 is repeatable read by default
db_window-2 > select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

db_window-2 > update account set money = money + 100 where id = 1;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 4.open transaction
db_window-2 > start transaction;
Query OK, 0 rows affected (0.00 sec)
db_window-1 > start transaction;
Query OK, 0 rows affected (0.00 sec)


db_window-2 > update account set money = money - 100 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 5.Terminal 1 has read data that has not been submitted by terminal 2
db_window-1 > select * from account;
| id | name      | money |
|  1 | WeiyiGeek |  1100 |
|  2 | muzi      |  900 |


-- 6.Commit transaction (actual terminal 1 has read data from memory)
db_window-2 > commit;

-- 1.Set the isolation level for terminal 1
db_window-1 > set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

db_window-1 > select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

-- 2.Terminal 1 starts the transaction
db_window-1 > start transaction;
Query OK, 0 rows affected (0.00 sec)

-- 3.Terminal 1 queries raw data
db_window-1 > select * from account;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | WeiyiGeek |  1000 |
|  2 | muzi      |  1000 |
+----+-----------+-------+
2 rows in set (0.00 sec)

-- 4.Terminal 2 starts transaction
db_window-2 > select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

db_window-2 > start transaction;
Query OK, 0 rows affected (0.00 sec)


-- 5.Terminal 2 performs the update operation
db_window-2 > update account set money = money - 100 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

db_window-2 > update account set money = money + 100 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 6.Terminal 1 performs a query operation (the data has not changed)
db_window-1 > select * from account;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | WeiyiGeek |  1000 |
|  2 | muzi      |  1000 |
+----+-----------+-------+
2 rows in set (0.00 sec)

-- 7.Terminal 2 executes commit
db_window-2 > commit;
Query OK, 0 rows affected (0.05 sec)

-- 8.After submission, terminal 1 queries the data and finds that it has changed
db_window-1 > select * from account;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | WeiyiGeek |   900 |
|  2 | muzi      |  1100 |
+----+-----------+-------+
2 rows in set (0.00 sec)

-- 1.Terminal 1 sets the isolation to repeat the read and view the current isolation
db_window-1 > set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

db_window-1 > select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ | 
+-----------------+
1 row in set (0.00 sec)

-- 2.Terminal 1 displays raw data
db_window-1 > select * from account;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | WeiyiGeek |  1000 |
|  2 | muzi      |  1000 |
+----+-----------+-------+
2 rows in set (0.00 sec)

-- 3.Terminal 1 starts transaction
db_window-1 > start transaction;
Query OK, 0 rows affected (0.00 sec)


-- 4.Terminal 2 checks the current isolation and opens a transaction
db_window-2 > select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

db_window-2 > start transaction;
Query OK, 0 rows affected (0.00 sec)

-- 5.Terminal 2 performs the update operation
db_window-2 > update account set money = money - 100 where id = 1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

db_window-2 > update account set money = money + 100 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 6.Terminal 1 executes the query statement and the result remains unchanged
db_window-1 > select * from account;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | WeiyiGeek |  1000 |
|  2 | muzi      |  1000 |
+----+-----------+-------+
2 rows in set (0.00 sec)

-- 7.Terminal 2 executes submit data
db_window-2 > commit;
Query OK, 0 rows affected (0.08 sec)


-- 8.Terminal 1 executes the query data and still does not change(The query results are consistent with the previous query results and will not change.)
db_window-1 > select * from account;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | WeiyiGeek |  1000 |
|  2 | muzi      |  1000 |
+----+-----------+-------+
2 rows in set (0.00 sec)

-- 1.Terminal 1 settings Serializable Serializable isolation level
db_window-1 > set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

db_window-1 > select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set (0.00 sec)

-- 2.Terminal 2 current isolation
db_window-2 > select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

-- 3.Terminal 1 queries raw data
db_window-1 > select * from account;
| id | name      | money |
|  1 | WeiyiGeek |  1000 |
|  2 | muzi      |  1000 |

-- 4.Terminal 2 starts the transaction first
db_window-2 > start transaction;
Query OK, 0 rows affected (0.00 sec)

-- 5.Terminal 1 starts the transaction next
db_window-1 > start transaction;
Query OK, 0 rows affected (0.00 sec)


-- 6.Terminal 2 executes CRUD SQL(There is no problem)
db_window-2 > insert into account values (null,'mariadb',1000);
Query OK, 1 row affected (0.00 sec)


-- 7.Terminal 1 waits for the transaction of terminal 2 to complete and executes, otherwise it will block including(CURD)
db_window-1 > update account set money = money - 100 where id = 3;
-- wait for terminal 2 Commit commit transaction 
Query OK, 1 row affected (46.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 8.After the transaction execution of terminal 2 is completed, the above update statement can be executed
db_window-2 > commit;
Query OK, 0 rows affected (0.07 sec)

-- 9.Terminal 1 can execute any arbitrary CURD statement
db_window-1 > select * from account;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | WeiyiGeek |  1000 |
|  2 | muzi      |  1000 |
|  3 | mariadb   |   900 |
+----+-----------+-------+
3 rows in set (0.00 sec)

// Call the connection pool using: /User/src/top/weiyigeek/Web/Test_Pool.java
public class Test_Pool {
  @Test
  public void testPool() throws SQLException {
    Connection conn = null;
    PreparedStatement ps =null;
    //Note that it is necessary to avoid repeated application of objects here, so you need to use a singleton;
    CustomDatasource ds = CustomDatasource.getInstance();
    try {
      //Get the link object of the resource pool
      conn = ds.getConnection();
      String sql = "SELECT * FROM user LIMIT 0,10";
      ps = conn.prepareStatement(sql);
      ResultSet rs = ps.executeQuery();
      while(rs.next()) {
        System.out.println("ID: " + rs.getInt("id") + " , Name = " + rs.getString("name")+", Log in time = " + rs.getDate("uptime"));
      }
      rs.close();
    } catch (Exception e) {
      // TODO: handle exception
      e.printStackTrace();
    } finally {
      try {
        //Close the PreparedStatementd object
        ps.close();
      } catch (Exception e2) {
        // TODO: handle exception
        e2.printStackTrace();
      }
      //return object
      //ds.addBack(conn); //This method is not used after decoration
      conn.close();
    }
  }
}

/**
 * @Desc:Decorate design pattern database connection close return
 * @author WeiyiGeek
 * @CreatTime 2:48:41 PM
 */
public class ConnectionWrap implements Connection {
  //1. The construction method accesses the connection connection and the connection pool collection
  Connection connection = null;
  List <Connection> list ;
  public ConnectionWrap(Connection connection , List <Connection> list) {
    super();
    this.connection = connection;
    this.list = list;
  }
  
  //2. Parse the prepared SQL statement
  @Override
  public PreparedStatement prepareStatement(String sql) throws SQLException {
    return connection.prepareStatement(sql);
  }
  
  //3. Override the Close method
  @Override
  public void close() throws SQLException {
    // TODO Auto-generated method stub
    //connection.close(); //You can also call the original close
    System.out.println("Someone has come to return the connection object> Before returning, the number of available connections in the pool:"+list.size());
    list.add(connection);
    System.out.println("Someone has come to return the connection object> After the return, the number of available connections in the pool:"+list.size());
    }

    ......   
}

import top.weiyigeek.Util.db;
public class CustomDatasource implements DataSource {
  // The method of database resource pool establishment
  //0. Create objects of this class and provide public access methods to the outside world
  private static CustomDatasource s = new CustomDatasource();
  public static CustomDatasource getInstance() {
    return s;
  }
  // 1. Create a collection of storage connection pool objects
  List <Connection> list = new ArrayList<Connection>();
  
   public CustomDatasource()  {
     //2. Establish a connection object and store it in the resource pool
     for(int i = 0; i < 10;i++) {
      Connection conn;
      try {
        conn = db.getConn();
        list.add(conn);
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
     }
  }
  
  //3. The method for obtaining connections published by the connection pool 
  @Override
  public Connection getConnection() throws SQLException {
    // TODO Auto-generated method stub
    //4. To determine whether the resource pool is used up, add 3 connection objects to the resource pool (of course, the resource pool is limited)
     if (list.size() == 0 ) {
       try {
         for(int i = 0; i < 5; i++)
           list.add(db.getConn());
      } catch (SQLException e) {
        e.printStackTrace();
      }
     }
    //return and remove the first element
    Connection conn = list.remove(0); 
    //The decoration mode wraps the object when it is thrown out. (The key point is worth learning)
    Connection connection = new ConnectionWrap(conn, list);
    return connection;
  }

  
  //5. Return the connection object
  public void addBack(Connection conn) {
    list.add(conn);
  }

    .....
}

package top.weiyigeek.DBCP;
/**
 * 
 * @Desc: OBCP Apache Use of open source data connection management frameworks
 * @author WeiyiGeek
 * @CreatTime 12:45:22 PM
 */
public class OBCPDemo1 {
  @SuppressWarnings("resource")
  @Test
  public void testOBCP () {
    //1. Build the data source object
    BasicDataSource dataSource = new BasicDataSource();
    
    //2. Get the connection object
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    
    //3. Database configuration (manual)
    dataSource.setDriverClassName("org.mariadb.jdbc.Driver");
    //Main Protocol Subprotocol Database IP:Port Database
    dataSource.setUrl("jdbc:mariadb://127.0.0.1:3306/student");
    dataSource.setUsername("root");
    dataSource.setPassword("");
    
    try {
      conn = dataSource.getConnection();
      //4. Parse the SQL statement
      ps = conn.prepareStatement("SELECT * FROM user ORDER BY id DESC LIMIT 0,?");
      ps.setInt(1, 10);
      rs = ps.executeQuery();
      while(rs.next()) {
        System.out.println(rs.getInt("id") + " ---- " + rs.getString("name") +  " ---- " + rs.getInt("grade"));
      }
      rs.close();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } finally {
      //db.release(conn, ps, rs);
      //It is recommended to rewrite the close method to release resources
    }
  }
}

#connection settings
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc
username=root
password=

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

#Maximum number of connections
maxActive=50

#<!-- Maximum idle connections -->
maxIdle=20

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

#<!-- Timeout wait time in milliseconds 6000ms/1000 equals 60 seconds -->
maxWait=60000


#The format of the connection property attribute attached when the JDBC driver establishes a connection must be as follows: [property name=property;] 
#Note: The "user" and "password" properties are passed explicitly, so there is no need to include them here.
connectionProperties=useUnicode=true;characterEncoding=utf8

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

#The driver default specifies the transaction level (TransactionIsolation) of the connection created by the connection pool.
#Available values ​​are one of the following: (See the javadoc for details.) NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

/**
 * 
 * @Desc: OBCP Read the configuration file to configure the database connection
 * @author WeiyiGeek
 * @CreatTime 1:26:18 PM
 */
public class OBCPDemo2 {
  
  @Test
  public void testDemo2() throws Exception {
    //1. Use Properties to read the data in the configuration file
    Properties prop = new Properties();
    InputStream is = this.getClass().getClassLoader().getResourceAsStream("dbcp.properties");  //Read jdbc.properties in the project into the input stream
    prop.load(is);

    //2. Build the connection object
    BasicDataSourceFactory factory = new BasicDataSourceFactory();
    DataSource datasource = factory.createDataSource(prop);  //Introduce properties file
    
    //3. Get the connection object
    Connection conn = datasource.getConnection();
    PreparedStatement ps = conn.prepareStatement("SELECT * FROM user LIMIT 0,10");
    ResultSet rs = ps.executeQuery();
    
    //4. Print out the data read from the database
    while(rs.next()) {
      System.out.println(rs.getInt("id") + " ---- " + rs.getString("name") +  " ---- " + rs.getInt("grade"));
    }
    
    //5. Close the connection
    System.out.println("connection closed");
    rs.close();
    ps.close();
    conn.close();
  }
}

import top.weiyigeek.Util.db;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Demo1 {
  @Test
  public void Demo1() throws PropertyVetoException,Exception {
    //1. Create DataSource
    ComboPooledDataSource cpds = new ComboPooledDataSource();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    
    //2. Set database connection information
    cpds.setDriverClass("org.mariadb.jdbc.Driver");
    cpds.setJdbcUrl("jdbc:mariadb://127.0.0.1:3306/student");
    cpds.setUser("root");
    cpds.setPassword("");

    //3. Get the connection object
    conn = cpds.getConnection();
    ps = conn.prepareStatement("SELECT * FROM user LIMIT 0,?");
    ps.setInt(1, 20);

    //4. Execute the SQL query
    rs = ps.executeQuery();
    System.out.println("serial number - Name - grade");
    while (rs.next()) {
      System.out.println(rs.getInt("id") + " - " + rs.getString("name") + " - " + rs.getShort("grade"));
    }
    
    //5. Close the connection
    db.release(conn, ps, rs);
  }
}

<c3p0-config>
  <default-config>
    <property name="driverClass">org.mariadb.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mariadb://127.0.0.1:3306/student</property>
    <property name="user">root</property>
    <property name="password"></property>

    <property name="automaticTestTable">con_test</property>
    <property name="checkoutTimeout">30000</property>
    <property name="idleConnectionTestPeriod">30</property>
    <property name="initialPoolSize">10</property>
    <property name="maxIdleTime">30</property>
    <property name="maxPoolSize">100</property>
    <property name="minPoolSize">10</property>
    <property name="maxStatements">200</property>

    <user-overrides user="test-user">
      <property name="maxPoolSize">10</property>
      <property name="minPoolSize">1</property>
      <property name="maxStatements">0</property>
    </user-overrides>
  </default-config>
<!--Multiple databases can be configured-->
  <named-config name="mysql">
    <property name="driverClass">org.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/student</property>
    <property name="user">root</property>
    <property name="password"></property>
  </named-config>

<!--Multiple databases can be configured-->
  <named-config name="oracle">
    <property name="driverClass">oracle.jdbc.driver.OracleDriver</property>
    <property name="jdbcUrl">jdbc:oracle:thin:@192.168.10.25:1521:student</property>
    <property name="user">master</property>
    <property name="password"></property>
  </named-config>
</c3p0-config>

package top.weiyigeek.C3P0;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import top.weiyigeek.Util.db;
/**
 * 
 * @Desc: C3P0 Read database connection and connection pool configuration through xml file configuration (actually similar to Demo1)
 * @author WeiyiGeek
 * @CreatTime 2:38:55 PM
 */
public class C3P0Demo2 {
  
  @Test
  public void demo2() throws SQLException {
    //1. Create a DataSource and read and set the database connection information (c3p0-config.xml)
    ComboPooledDataSource cpds = new ComboPooledDataSource();
    //ComboPooledDataSource cpds = new ComboPooledDataSource("oracle"); //You can connect to the specified database in the configuration file
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    //2. Get the connection object
    conn = cpds.getConnection();
    ps = conn.prepareStatement("SELECT count(*) FROM user");

    //3. Execute the SQL query
    rs = ps.executeQuery();
    System.out.println("serial number - Name - grade");
    while (rs.next()) {
      System.out.println("total number of students: "+rs.getInt(1) );
    }
    //4. Close the connection
    db.release(conn, ps, rs);
  }
}

March 19, 2020 2:44:34 afternoon com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
 information: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> con_test, breakAfterAcquireFailure -> false, checkoutTimeout -> 30000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hgevwha81v0n140s2kcd6|33b37288, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> org.mariadb.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hgevwha81v0n140s2kcd6|33b37288, idleConnectionTestPeriod -> 30, initialPoolSize -> 10, jdbcUrl -> jdbc:mariadb://127.0.0.1:3306/student, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 30, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 100, maxStatements -> 200, maxStatementsPerConnection -> 0, minPoolSize -> 10, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
serial number - Name - grade
 total number of students: 84
 Database connection closed and resources released

import com.mchange.v2.c3p0.ComboPooledDataSource;
/***
 * @Desc: The database connection tool class adopts the c3p0 method (simplifies our code)
 * @author WeiyiGeek
 * @CreatTime 1:28:57 PM
 */
public class db1 {
  static ComboPooledDataSource cpds = null;
  static {
    cpds = new ComboPooledDataSource();
  }
  
  /**
	 * Get link object in C3P0
	 * @return Connection
	 */
  public static Connection getConn() throws SQLException {
    return cpds.getConnection();
  }
  
  /**
	 * Fun:Close the database connection and release resources (note: the order of closing)
	 * @param conn
	 * @param st
	 * @param rs
	 */
  public static void release(Connection conn,Statement st, ResultSet rs) {
    closeRs(rs);
    closeSt(st);
    closeConn(conn);
    System.out.println("\n Database connection closed and resources released\n");
  }
  
  //Private static method - release query result set
  private static void closeRs(ResultSet rs) {
      try {
        if(rs != null)
          rs.close();
      } catch (SQLException e) {
        e.printStackTrace();
      } finally {
        rs = null;
      }
    }
    
  //Private static method - release the statement object
  private static void closeSt(Statement st) {
      try {
        if(st != null)
          st.close();
      } catch (SQLException e) {
        e.printStackTrace();
      } finally {
        st = null;
      }
    }
    
  //Private static method - closes database connection
  private static void closeConn(Connection conn) {
    try {
      if(conn != null)
        conn.close();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      conn = null;
    }
  }
}

Account a = new Account();
Account al = Account.class.newInstance();

Person res = qr.query("select * from person", new BeanHandler<Person>(Person.class));    
List<Person> lp = runner.query("SELECT * FROM person ", new BeanListHandler<Person>(Person.class));

Long count = (Long)runner.query("SELECT COUNT(*) FROM person",new ScalarHandler());
return count.intValue();

package top.weiyigeek.DBUtil;
public class Account {
  private int id;
  private String name;
  private float money;
  public int getId() {
    return id;
  }
  public void setId(int id) {
    this.id = id;
  }
  public String getName() {
    return name;
  }
  public void setName(String name) {
    this.name = name;
  }
  public float getMoney() {
    return money;
  }
  public void setMoney(float money) {
    this.money = money;
  }
  
  @Override
  public String toString() {
    return "Account [serial number=" + id + ", Name=" + name + ", salary=" + money + "]";
  } 
}

package top.weiyigeek.DBUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/*
 * Function: DButil CURD Case realization 
 */
public class DBUtilDemo1 {
  //call implementation
  public static void main(String[] args) throws SQLException {
    ComboPooledDataSource cpds = new ComboPooledDataSource();
    //Example 1. Insertion test
    if(insert(cpds)>0) {
      System.out.println("Inserted successfully");
    }else {
      System.err.println("Insert failed");
    }
    
    //Example 2. Test deletion
    if(delete(cpds)>0) {
      System.out.println("successfully deleted");
    }else {
      System.err.println("failed to delete");
    }
    
    //Example 3. Test update
    if(update(cpds)>0) {
      System.out.println("update completed");
    }else {
      System.err.println("update failed");
    }
    
    
    //Example 4. Test anonymous implementation class to return query results
    Account account = queryone(cpds);
    System.out.println(account.toString());
    
    
    //Example 5. Return the result by implementing the interface of ResultSetHandler in DBuntil;
    List<Account> ls = querytwo(cpds);
    for(Account worker:ls) {
      System.out.println(worker.toString());
    }

  }
  
  //(1) Function: Test SQL insert statement such as method name
  public static int insert(DataSource cpds) throws SQLException {
    QueryRunner qr = new QueryRunner(cpds);
    int flag = qr.update("INSERT INTO account VALUES (null,?,?)","Zhang Wei",1024);
    return flag;
  }
  
  //(2) Function: Test SQL delete statement such as method name
  public static int delete(DataSource cpds) throws SQLException {
    QueryRunner qr = new QueryRunner(cpds);
    int flag = qr.update("DELETE FROM account where name = ?","Zhang Wei");
    return flag;
  }
  
  //(3) Function: Test SQL update statement such as method name
  public static int update(DataSource cpds) throws SQLException {
    QueryRunner qr = new QueryRunner(cpds);
    int flag = qr.update("UPDATE account SET money=money+100 where id = ? and name = ?",1,"WeiyiGeek");
    return flag;
  }
  
  //(4) Function: Test SQL query statement such as method name
  public static Account queryone(DataSource cpds) throws SQLException {
    QueryRunner qr = new QueryRunner(cpds);
    //Anonymous class implementation
    Account account = qr.query("SELECT * FROM account WHERE id = ?", new ResultSetHandler<Account>() {
      @Override
      public Account handle(ResultSet rs) throws SQLException {
        // TODO Auto-generated method stub
        Account account = new Account();
        while(rs.next()) {
          int id = rs.getInt("id");
          String name = rs.getString("name");
          float money = (float)rs.getInt("money");
          account.setId(id);
          account.setName(name);
          account.setMoney(money);
        }
        return account;
      }
    }, 1);
    
    return account;
  }
  
  //(5) In ResultSetHandler, resultset is converted into other objects for implementation.
  public static List<Account> querytwo(DataSource cpds) throws SQLException {
    QueryRunner qr = new QueryRunner(cpds);
    //- BeanHander - returns an object;
    //- BeanListHander - returns a collection with objects;
    List<Account> list= qr.query("SELECT * FROM account", new BeanListHandler<Account>(Account.class));
    return list;
  }
}