The statement

I write my own blog for the purpose of trying to summarize and review later. The mistakes are not intentional misdirection, but weak strength. If readers find mistakes, they can comment on them so I can think about them and correct them. Thank you very much.

This blog to summarize JDBC: the rest of the content of database transaction | DAO and its subclasses | | database connection pool DBUtils implementation CRUD interested can go to the video is still silicon valley

1. Database transactions

Transaction: REFERS to a set of logical units of operation (one or more DML operations) that make data transition from one state to another. When multiple operations are performed in a transaction, either all operations are committed, meaning the changes are saved permanently. Or the database management system will abandon all operations and the transaction will rollback to its original state

The code is

  • To cancel automatic submission of data:conn.setAutoCommit(false);
  • Manually set the location of the commit (i.e. the scope of the transaction) :conn.commit();
  • Rollback data:conn.rollback();
@Test public void testUpdateWithTx() { Connection conn = null; try { conn = JDBCUtils.getConnection(); //1. Cancel automatic commit of data conn.setautoCommit (false); String sql1 = "update user_table set balance = balance - 100 where user = ?" ; update(conn,sql1, "AA"); String sql2 = "update user_table set balance = balance + 100 where user = ?" ; update(conn,sql2, "BB"); //2. Submit data conn.mit (); } catch (Exception e) { e.printStackTrace(); //3. Rollback data try {conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); // Try {conn.setautocommit (true); // Try {conn.setautocommit (true); } catch (SQLException e) { e.printStackTrace(); } JDBCUtils.closeResource(conn, null); }}Copy the code

Problem: Since a transaction contains multiple DML operations, it makes sense to establish a connection for memory reasons. Therefore, there is no need to create a new connection in the body of the add, delete, modify method. Instead, connection should be passed in as a parameter at call time.

Public int update(Connection conn,String SQL, Object... Args) {// The number of placeholders in SQL is the same as the length of the morphable parameter! PreparedStatement ps = null; Try {// 1. Precompile the SQL statement and return an instance of PreparedStatement ps = conn.prepareStatement(SQL); // 2. Fill placeholder for (int I = 0; i < args.length; i++) { ps.setObject(i + 1, args[i]); // Be careful with the parameter declaration error!! } // 3. Execute return ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } finally {// 4. Jdbcutils. closeResource(null, ps); } return 0; }Copy the code
Public <T> T getInstance(Connection conn,Class<T> clazz,String SQL, Object) public <T> T getInstance(Connection conn,Class<T> clazz,String SQL, Object... args) { PreparedStatement ps = null; ResultSet rs = null; try { ps = conn.prepareStatement(sql); for (int i = 0; i < args.length; i++) { ps.setObject(i + 1, args[i]); } rs = ps.executeQuery(); ResultSetMetaData ResultSetMetaData RSMD = rs.getMetadata (); Int columnCount = rsmd.getColumnCount(); // Obtain the number of columns in the result set using ResultSetMetaData. if (rs.next()) { T t = clazz.newInstance(); For (int I = 0; i < columnCount; I ++) {// columValue = rs.getobject (I + 1); ColumnName = rsmD.getColumnName (I + 1); String columnLabel = rsmd.getColumnLabel(i + 1); // columValue: by reflection Field Field = clazz.getDeclaredField(columnLabel); field.setAccessible(true); field.set(t, columValue); } return t; } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(null, ps, rs); } return null; }Copy the code

DAO and its subclasses

I feel very interesting in learning this process, the specification code is really clever, but I will not repeat here, because the code of the course is also CRUD function encapsulation, the actual use does not need to write, you can use other frameworks. We’ll talk about that in the next section.

Database connection pool (emphasis)

The benefits of connection pooling are straightforward. It is to prepare a certain number of connections in advance to put in the connection pool, need to take, not put back. This eliminates the need for frequent creation and destruction.

Implementation method:

DBUtils implements CRUD operations

Import the jar package

Test add, delete, and modify operations using QueryRunner in an existing JAR

@Test public void testInsert() { Connection conn = null; try { QueryRunner runner = new QueryRunner(); conn = JDBCUtils.getConnection(); String sql = "insert into customers(name,email,birth)values(? ,? ,?) "; Int insertCount = runner. Update (conn, SQL," CAI ","[email protected]","1997-09-08"); System.out.println(" add "+ insertCount +" entry "); } catch (SQLException e) { e.printStackTrace(); }finally{ JDBCUtils.closeResource(conn, null); }}Copy the code

Test the operation of the query using QueryRunner in an off-the-shelf JAR

// Test query /* * BeanHander: is an implementation class of the ResultSetHandler interface that encapsulates a record in a table. */ @Test public void testQuery1(){ Connection conn = null; try { QueryRunner runner = new QueryRunner(); conn = JDBCUtils.getConnection(); String sql = "select id,name,email,birth from customers where id = ?" ; BeanHandler<Customer> handler = new BeanHandler<>(Customer.class); Customer customer = runner.query(conn, sql, handler, 23); System.out.println(customer); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ JDBCUtils.closeResource(conn, null); }}Copy the code

In fact, there are a variety of types of queries, the general level is impossible to completely write their own, in fact, learn to see and change should be able to. Here are some other methods used for query results. Hopefully, when you see them, you will know what is implemented, or which method is used to implement it, and then go to the manual.

/* * BeanListHandler: an implementation class of the ResultSetHandler interface that encapsulates a collection of records in a table. * /Copy the code
/* * MapHander: is the implementation class of the ResultSetHandler interface, corresponding to a record in the table. * Use the fields and their values as keys and value */ in the mapCopy the code
/* * MapListHander: is the implementation class of the ResultSetHandler interface, corresponding to multiple records in the table. * Use the fields and their values as keys and values in the map. Add these maps to the List */Copy the code
/* * ScalarHandler: Used to query the special value */Copy the code

In addition, use the dbutils utility class in the dbutils.jar package to close resources such as connections:

public static void closeResource1(Connection conn,Statement ps,ResultSet rs){
    DbUtils.closeQuietly(conn);
    DbUtils.closeQuietly(ps);
    DbUtils.closeQuietly(rs);
}
Copy the code

conclusion

This is the summary of THE CONTENT of JDBC. After writing it, I feel that it is a failure. The original idea was just a few small parts, but I couldn’t help but follow the notes of the course. As a last ditch effort, I’d like to add that as far as JDBC content is concerned, it’s only practical to look at how CRUD operations are implemented by specific database connection pools. Other blogs summed it up more pointedly. Druid operations.