- 1, When operating the database, using the prepared statement will greatly improve the performance advantages of operating the database as follows:
1). Readability and maintainability of the code. (use placeholders to represent parameters?)
2) can maximize performance (precompile), MySQL does not support performance optimization of PreparedStatement
3) ensure safety
- 2, Precompiled and efficient embodiment (database server sent by sql)
Security Analysis > syntax analysis > syntax compilation > select execution > return result set (result)
Precompiled pool: first, judge whether the sql statement sent to DBMS exists in the precompiled pool. If it exists, select the execution steps. If it does not exist, execute it according to the above procedure, and store the sql in the precompiled pool (Note: Oracle supports precompiling, mysql does not support it)
3, mysql example
// Batch not used //InnoDB:15369ms //MyISAM:8464ms @Test public void testSaveByStatement() throws Exception { Connection conn = JdbcUtil.getConnection(); Statement st = conn.createStatement(); long begin = System.currentTimeMillis(); for (int j = 1; j < 5000; j++) { String sql = "insert into t_student (name,age) values('tom',"+ j+")"; st.executeUpdate(sql); } long end = System.currentTimeMillis(); System.out.println(end - begin); JdbcUtil.realse(null, st, conn); } // Complete with batch //InnoDB:14641ms //MyISAM:7464ms @Test public void testBatchSaveByStatement() throws Exception { Connection conn = JdbcUtil.getConnection(); Statement st = conn.createStatement(); long begin = System.currentTimeMillis(); for (int j = 1; j < 5000; j++) { String sql = "insert into t_student(name,age) values('tom',"+ j+")"; st.addBatch(sql); // Add to batch if(j % 200 == 0){ st.executeBatch(); // implement st.clearBatch(); // Clear batch } } long end = System.currentTimeMillis(); System.out.println(end - begin); JdbcUtil.realse(null, st, conn); } // Batch not used //InnoDB:13630 //MyISAM:12323 @Test public void testSaveByPrepareStatement() throws Exception { String sql = "insert into t_student(name,age) values('tom',?)"; Connection conn = JdbcUtil.getConnection(); PreparedStatement ps = conn.prepareStatement(sql); long begin = System.currentTimeMillis(); for (int j = 1; j < 5000; j++) { ps.setInt(1, j); ps.executeUpdate(); ps.clearParameters(); } long end = System.currentTimeMillis(); System.out.println(end - begin); JdbcUtil.realse(null, ps, conn); } // Complete with batch //InnoDB:11118 //MyISAM:4546 @Test public void testBatchSaveByByPrepareStatement() throws Exception { String sql = "insert into t_student(name,age) values('tom',?)"; Connection conn = JdbcUtil.getConnection(); PreparedStatement ps = conn.prepareStatement(sql); long begin = System.currentTimeMillis(); for (int j = 1; j < 5000; j++) { ps.setInt(1, j); ps.addBatch(); if(j % 200 == 0){ ps.executeBatch(); ps.clearBatch(); } } long end = System.currentTimeMillis(); System.out.println(end - begin); JdbcUtil.realse(null, ps, conn); }