JDBC DAO Layer SQL Statement Operation

I. PrepareStatment(pstmt)
The precompiled mode of pstmt can only write the SQL statement for the first time when SQL needs to perform the same operation many times but the parameters change.
And distinguish it by pstmt.addBatch();
For example:

//I want to change the age of people with different names in the stu table many times.
PrepareStatment pstmt = conn.prepareStatment("UPDATE stu SET age=? WHERE name=?;");
pstmt.setInt(1,20);
//It means placeholder. When the parameters need to change, it means placeholder and inserts parameters of corresponding type. The type of age in the table is int, so setInt
pstmt.setString(2,"Zhang San")
//1 and 2 in () represent the placeholder? Position in the SQL statement, 1 represents the first? 2 represents the second?
pstmt.addBatch();//At this point, addBatch is used to indicate that the query statement above is in the cache and is executed at last.
pstmt.setInt(1,24);
pstmt.setString(2,"Li Si");
pstmt.addBatch();
//At this point, insert the second person's information, and truncate it by addBatch, and store the second person's information in the cache, and finally execute it.
pstmt.addBatch("DELETE FROM stu WHERE name = 'Wang Wu'");
//AddiBatch can also write SQL data directly and store it directly in the cache for final execution.
pstmt.executeBatch();//Execute Batch to perform all the above sql operations.

II. Transaction operations
1. Encapsulate several operations of the database into a single transaction. Only when the left and right conditions are executed without errors, can they be written to the database.
For example: bank transfers, A transfers to B, A's database performs modification operations, if B does not receive money, A and B's database will not change.

Connection conn = null;
        try {//By selecting the entire sql statement that needs to be operated as a transaction, wrapping it into a transaction through try catch, exceptions are caught before the system
            conn = Jdbcutil.getConnection();
            //Set submission mode to manual submission
            //Open a transaction and set the submission mode to false automatically, that is, change the automatic submission to manual
            conn.setAutoCommit(false);
            //Create a preprocessing object
            PreparedStatement pstmt = conn.prepareStatement("UPDATE money SET m =? WHERE `name`=?");
            pstmt.setObject(1,700);
            pstmt.setObject(2,"Cao Yunjin");
            pstmt.addBatch();
            QueryRunner qr = new QueryRunner();
            String name = "Cao Yunjin";   

            int a = 100/0;
            //Failure occurs in the simulation transfer process, the program will throw an exception at this time will not run downward, before the call to the following run rollback operation, and output"Transfer failure"
            pstmt.setObject(1,1300);
            pstmt.setObject(2,"Guo Degang");
            pstmt.addBatch();
            pstmt.executeBatch();
            conn.commit();
        } catch (Exception e) {
            //Roll back to the state where the transaction started
            try {
                conn.rollback();
                System.out.println("Transfer failure");
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }

3. DButils-Query Runner
1.dbutils is an open source jar package provided by Apache for easy operation of jdbc
2.QueryRunner class is the core class of the jar package. All database operation methods are in this class. Addition, deletion and modification checks use this class.
Query Runner Operational Database Addendum, Delete and Change Check is divided into two types:
(1) update (add, delete, change)

Connection conn = Jdbcutil.getConnection();
//Create a QueryRunner object to execute SQL statements
QueryRunner qr = new QueryRunner();
//Call the update method to execute add, delete, change sql statements
qr.update(conn, "insert into stu values(null,'Zhang Yida',55)");
/*qr.update(Database connection object, sql statement, if there is a placeholder in sql statement - here you can insert parameters (you can insert dynamic parameters)*/
conn.close();//Close the connection

(2) query (query)
1. The query method receives objects of ResultSetHandler interface type, which enables us to pass in different implementations of the interface, so that we can pass in parameters according to our requirements. That is to say, the interface is the protocol between us and the query method. The convention is that Query Runner can help us to centralize the results when the incoming object implements the ResultSetHandler interface. Data is encapsulated and returned to us, and because this is the reference of the interface as a parameter, then we can also customize a class T to implement the ResultSetHandler interface, transfer the object of T into it, and how to operate the data in the ResultSet, we can write in the handle method of T-class replication, which is also the extensibility provided by the ResultSetHandler interface.

Connection conn = Jdbcutil.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "select * from stu;";
Stu query = qr.query(conn, sql, new ResultSetHandler<Stu>() {
   @Override
   public Stu handle(ResultSet resultSet) throws SQLException {
       while (resultSet.next()) {
          Stu stu = new Stu();
          stu.setName(resultSet.getString(2));
          System.out.println(stu.getName());
       }
   return null;
     }
   });

1) BeanHandler: Query returns a single object

Stu query = qr.query(conn, sql, new BeanHandler<Stu>(Stu.class));

2) BeanListHandler: The query returns a list collection whose elements are specified objects.

List<Stu> query = qr.query(conn, sql, new BeanListHandler<>(Stu.class));

3) ArrayHandler, the first line of the query returning the result record, encapsulates an array of objects, that is, to return:Object

Object[] query1 = qr.query(conn, sql, new ArrayHandler());

4)ArrayListHandler, which encapsulates each row of the query as an object array and adds it to the list collection

List<Object[]> query = qr.query(conn, sql, new ArrayListHandler());

5) Scalar Handler

Object query1 = qr.query(conn, sql, new ScalarHandler<>());

6) The first record returned by the MapHandler query is encapsulated as a map

 List<Map<String, Object>> query = qr.query(conn, sql, new MapListHandler());