1. affairs

Consider multiple sql statements as a whole, ACID (atomicity, consistency, isolation, persistence)

Connection conn = ...
conn.setAutoCommit( true | false );

// Automatic submission, default value is true
The so-called automatic commit is to add a commit statement automatically by jdbc after adding or deleting sql statements for each execution to make the transaction commit (the change takes effect)

stmt.executeUpdate("insert into..."); // commit;
stmt.executeUpdate("update ..."); // commit;
stmt.executeUpdate("delete ..."); // commit;

When calling conn.setAutoCommit(false) means that the transaction is submitted manually, subsequent SQLs are treated as a transaction.

try {
	stmt.executeUpdate("insert into...");  // ok	
	stmt.executeUpdate("update ..."); // abnormal
	conn.commit(); // Manual submission of transactions (changes take effect)
} catch(Exception e) {
	conn.rollback(); // Manual rollback of transactions
}

Example:

Connection conn = null;
PreparedStatement stmt = null;
try {
    conn = Utils.getConnection();
    // Let the transaction submit manually (control the transaction yourself)
    conn.setAutoCommit(false);
    stmt = conn.prepareStatement("delete from emp where empno=?");
    stmt.setInt(1, 7900);
    stmt.executeUpdate();
    System.out.println("delete from emp where empno=7900");
    int i = 1/0;

    stmt.setInt(1, 7902);
    stmt.executeUpdate();
    System.out.println("delete from emp where empno=7902");

    // Let all operations succeed, then commit it
    conn.commit();
} catch(Exception e) {
    e.printStackTrace();
    if(conn != null) {
        try {
            // Once there is an exception, roll back the previous operation
            conn.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    }
} finally {
    Utils.close(stmt, conn);
}

2. Improvement of efficiency

2.1 Make full use of precompiled sql

1. Using PreparedStatement Interface in jdbc
2. For mysql, you need to add two parameters to the connection string: & useServerPrepStmts = true & cachePrepStmts = true

2.2 batch processing (increase, delete, upgrade, invalid for query)

Be careful:
For mysql to enable batch processing, you need to add parameters: & rewriteBatchedStatements = true

public static void main(String[] args) {
	Connection conn = null;
	PreparedStatement stmt = null;
	try {
		conn = Utils.getConnection();
		long start = System.currentTimeMillis();
		stmt = conn.prepareStatement("insert into bigtable(id,name) values(?,?)");
		int max = 500;
		for (int i = 0; i < 1501; i++) {
			stmt.setInt(1,i);
			stmt.setString(2,"aaa");
	//                stmt.executeUpdate(); // Calling the executeUpdate method once will interact with the database server once
			stmt.addBatch(); // Add insert statements to batch packages
			if( (i+1) % 500 == 0 ) { // Batch processing packages up to 500 shipments at a time
				stmt.executeBatch();
			}
		}
		stmt.executeBatch(); // Send all sql in the batch package to the server at one time
		long end = System.currentTimeMillis();
		System.out.println(end-start);
	} catch(Exception e) {
		e.printStackTrace();
	} finally {
		Utils.close(stmt, conn);
	}
}

2.3 fetchSize packet size (query related)

mysql default query, will query all records are packaged in the ResultSet to return, when the result records too many, efficiency, memory will be affected.
You need to add: & useCursorFetch = true & defaultFetchSize = 100 to enable cursor-based ResultSet queries

2.4 Consider efficiency improvement from the perspective of database

For queries, index:
create index index index name on table name (column name);
create index idx_bigtable_id on bigtable(id);

1. Building Index for Frequent Queries
2. Index will affect the efficiency of addition and deletion. If the query operation is much higher than the addition and deletion, it is suitable for index establishment.
3. create table table table name (id int primary key)
   Indexing automatically occurs on primary key columns
4. Indexing on foreign key columns helps to improve the efficiency of table joins
5. If a function is applied to a query column, the index is not used
   select * from bigtable where abs(-2) = 263161;
   -2
   -1
   1
   2
   3
6. Fuzzy query
   select * from table where column like'%...' / Can't go indexed/
   select * from table where column like'aaaa%...' / Can walk indexing/

ctrl

2.5 Database Connection Pool

Create some database connections in advance, borrow them from the connection pool when you use them, and return them to the connection pool every time you use them, instead of closing them.
Interface and implementation of connection pool:
javax.sql.DataSource;//interface
DataSource.getConnection()// Get an idle connection from the connection pool, the pool connection
DriverManager.getConnection()// Create a new connection directly to the database.

Realization:
Application Servers
Tomcat has built-in connection pool
weblogic has built-in connection pool
websphere has built-in connection pool

Third-party Independent Connection Pool Implementation
apache dbcp
c3p0
alibaba druid Druid

Summary:
The role of connection pool: 1. Reuse of connection 2. Limit the upper limit of connection, so as not to drag down the whole database.
The design patterns involved: 1) the reuse of connections reflects the hedonic pattern 2) the change of close method behavior reflects the decorator pattern

Template keeps reusable code inside the method and passes in the changed part as a parameter to achieve code reuse.
Parameters can be of either a normal type or an interface type (which can contain multiple varying statements)
For example, the following code can be extracted as follows:

Before extraction:

public void insert(Student stu){
	Connection conn = null;
	PreparedStatement stmt = null;
	try {
		conn = Utils.getConnection();
		String sql = "insert into student(sname,birthday,sex)values(?,?,?)";
		System.out.println(sql);
		stmt = conn.prepareStatement(sql);
		// set method to sql statement in the ____________ Placeholder assignment
		stmt.setString(1, stu.getSname());
		Date birthday = stu.getBirthday();
		stmt.setDate(2, new java.sql.Date(birthday.getTime()));
		stmt.setString(3, stu.getSex());
		stmt.executeUpdate();
	} catch (Exception e) {
		e.printStackTrace();
	} finally{
		Utils.close(stmt,conn);
	}
}

After extraction:

public static void update(String sql, Object... args){ // Where Object... is equivalent to Object []
    Connection conn = null;
    PreparedStatement stmt = null;
    try {
        conn = Utils.getConnection();
        System.out.println(sql);
        stmt = conn.prepareStatement(sql);
        // set method to sql statement in the ____________ Placeholder assignment
        int idx = 1;
        // Traversing through all?
        for (Object arg : args) {
            if(arg instanceof java.util.Date) {
                java.util.Date date = (java.util.Date) arg;
                java.sql.Date sdate = new java.sql.Date(date.getTime());
                stmt.setObject(idx, sdate);
            } else {
                stmt.setObject(idx, arg);
            }
            idx ++;
        }
        stmt.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    } finally{
        Utils.close(stmt,conn);
    }
}

The example of the call after extraction becomes simple:

String sql = "insert into student(sname,birthday,sex)values(?,?,?)";
update(sql, stu.getSname(), stu.getBirthday(), stu.getSex());

String sql = "update student set sname=?, birthday=?, sex=? where sid=?";
update(sql, stu.getSname(), stu.getBirthday(), stu.getSex(), stu.getSid());

String sql = "delete from student where sid=?";
update(sql, sid);