The difference between statement, PreparedStatement, PreparedStatement, and batch processing in Java

First of all, the SQL statement that was executed after the database was connected: usually after the database is connected, we get the object of the statement class or the object of his subclass (PreparedStatement Class), through which we can manipulate the database using the method it provides.

Statement provides three ways to execute SQL statements:

1,execute: can be executed in any SQL statement, but more cumbersome, usually we do not choose this one but if you do not know the type of SQL statement, it can only use execute to execute the SQL statement.

2,executeupdata: Primarily used to execute DML and DDL statements, the execution DML statement returns the number of rows affected by the SQL statement, and the execution DDL statement returns 0.

3,executequery: Only query statements can be executed. Returns the collection object that represents the result of the query after execution resultset.

Here's what to say:

Statement and PreparedStatement objects can be used to execute SQL statements, but in many ways we still use the PreparedStatement class for more objects, PreparedStatement is a subclass of statement, he can precompile SQL statements, and pre-compiled SQL statements are stored in PreparedStatement objects. You can then use the PreparedStatement object to execute SQL statements efficiently, multiple times.

Objects that use the PreparedStatement class also have the above three methods of executing SQL statements, but these three methods do not require parameters. For specific process comparison, see: http://www.cnblogs.com/duhuo/p/4276226.html

Specific as follows:

1. using the Statement Object

Scope of use: fewer times when executing similar SQL (with the same structure, with different values)

Pros: Simple syntax

Disadvantage: The hard coding efficiency is low and the security is poor.

Principle: Hard coding, similar to SQL is compiled each time it executes

Sample Execution Procedure:

  Public voidEXEC (Connection conn) {Try{Long BeginTime=System.currenttimemillis (); Conn.setautocommit (false);//set up manual commitStatement St=conn.createstatement ();  for(inti=0;i<10000;i++) {String SQL= "INSERT into T1 (ID) VALUES (" +i+ ")";                St.executeupdate (SQL); } Long endTime=System.currenttimemillis (); System.out.println ("Statement:" + (Endtime-begintime)/1000+ "seconds");//Calculation TimeSt.close ();        Conn.close (); } Catch(SQLException e) {e.printstacktrace (); }   }

Execution time: statement: seconds

2. precompiled PreparedStatement

Scope of use: When executing similar SQL statements more often (for example, a user logs on, the table is frequently manipulated). ) statement, just the exact value is different, called Dynamic SQL

Pros: The statement compiles only once, reducing the number of compilations. Increased security (blocking SQL injection)

Disadvantage: When executing a non-similar SQL statement, the speed is slower.

Rationale: Similar SQL compiles only once, reducing compilation times

Case Execution Process:

  Public voidEXEC2 (Connection conn) {Try{Long BeginTime=System.currenttimemillis (); Conn.setautocommit (false);//Manually SubmitPreparedStatement PST= Conn.preparestatement ("INSERT into T1 (ID)" VALUES (?) ");  for(inti=0;i<10000;i++) {Pst.setint (1, i);                   Pst.execute ();                } conn.commit (); Long EndTime=System.currenttimemillis (); System.out.println ("PST Elapsed time:" + (Endtime-begintime) + "seconds");//Calculation TimePst.close ();         Conn.close (); } Catch(SQLException e) {e.printstacktrace (); }    }

Execution time: PST spents: seconds

3. using PreparedStatement + Batch processing

Scope of Use: Update database tables multiple records at a time

Pros: Reduce the number of interactions with the SQL engine and increase efficiency again, similar statements are compiled only once, reducing compilation times. Increased security (blocking SQL injection)

Disadvantages:

Principle: Batch processing: Reduce the number of interactions with the SQL engine, one pass to SQL engine multiple SQL.

Noun Explanation:

PL/SQL Engine: An engine that executes PL/SQL code in Oracle and finds standard SQL in execution to be handled by the SQL engine.

SQL Engine: The engine that executes standard SQL.

Case Execution Process:

 Public voidEXEC3 (Connection conn) {Try{conn.setautocommit (false); Long BeginTime=System.currenttimemillis (); PreparedStatement PST= Conn.preparestatement ("INSERT into T1 (ID)" VALUES (?) ");  for(inti=1;i<=10000;i++) {Pst.setint (1, i); Pst.addbatch ();//batch processing, packaging                if(i%1000==0) {//you can set different sizes, such as 50,100,500,1000, etc.Pst.executebatch ();                      Conn.commit ();                Pst.clearbatch (); }//End of If           }//End of forPst.executebatch (); Long EndTime=System.currenttimemillis (); System.out.println ("Pst+batch:" + (Endtime-begintime) + "MS");           Pst.close ();      Conn.close (); } Catch(SQLException e) {e.printstacktrace (); }}

Execution time: Pst+batch spents:485 Ms

In general terms:

PreparedStatement has the following three advantages over the use of statement:

1,PreparedStatement precompiled SQL statement. Better performance.

2,PreparedStatement without "stitching" SQL statements, compile simpler.

3,preparedstatement can prevent SQL injection, security is good.

The difference between statement, PreparedStatement, PreparedStatement, and batch processing in Java