The comprehensive application analysis of pre-compiling in PreparedStatement class of JDBC

The SQL statement is precompiled and stored in the PreparedStatement object. You can then use this object to execute the statement more efficiently than once

The advantages of precompiling
1,PreparedStatement is precompiled, for batch processing can greatly improve efficiency. Also known as the JDBC stored procedure.

2, the use of Statement objects. When only one-time access to the database is performed, the Statement object is used for processing. PreparedStatement objects are more expensive than statement, and do not provide additional benefits for one-time operations.

3,statement every execution of SQL statements, the relevant database to execute SQL statements compiled, PreparedStatement is precompiled, PreparedStatement support batch processing

4, The PreparedStatement object contains not only the SQL statement, but most of the time the statement has been precompiled, so that when it executes, only the DBMS runs the SQL statement without having to compile first. When you need to execute statement objects many times, the PreparedStatement object will greatly reduce the running time, and of course speed up the access to the database. This conversion is also a great convenience, without having to repeat the syntax of the SQL statement, but simply by changing the value of the variable in which the SQL statement can be executed again. The choice of PreparedStatement object is whether the same syntactic SQL statement has been executed several times, and the difference between the two times is just the difference between variables. If executed only once, it should be no different from the ordinary object, does not reflect the superiority of its precompilation.

5. PreparedStatement objects are more efficient than statement objects, especially if the same SQL statement with different parameters is executed multiple times. The PreparedStatement object allows the database to precompile SQL statements, which can save time and increase the readability of the code in subsequent runs.

Pre-compiled core code

Copy Code code as follows:


To build SQL queries by precompiling:


String sql = "SELECT * Student where year (birthday) between?" and? ";


PreparedStatement PS = conn.preparestatement (SQL);


ps.setstring (1, "1987");


ps.setstring (2, "1990");


rs = Ps.executequery ();

A simple example of precompiling
the first parameter of the following method is an SQL statement, and the 23rd parameter is the scope of the query to be queried.

Copy Code code as follows:


/**preparedstatement to compile the scope of the query column * * *


Public ResultSet startquery (string sql,string s1, string s2) {


getconnection ();


try {


pstatement = connection.preparestatement (sql);


pstatement.setstring (1, S1);


pstatement.setstring (2, S2);


RSet = Pstatement.executequery ();


} catch (SQLException e) {


TODO auto-generated Catch block


E.printstacktrace ();


  }


return rSet;


 }

Application

Copy Code code as follows:


System.out.println ("Birthday Range Query:");


ResultSet ResultSet = Jdemo1.startquery ("select * from T_userr where year (birthdate) between?" and? "," 1992 "," 1992 ");


Jdemo1.allresult (ResultSet);

Precompiled Extensions Paradigm One (SQL query)
below, we extend the above method to accommodate a broader picture.
The first argument is the SQL statement, and the second parameter is a list of parameters for the query. (stored in an array form)

Copy Code code as follows:


/** preparedstatement Pre-compiled query development version * * *


public ResultSet Startquerylook (String sql,object[] s) {


getconnection ();


try {


pstatement = connection.preparestatement (sql);


for (int i = 0; i < s.length; i++) {


Pstatement.setobject (i+1, s[i]);


   }


RSet = Pstatement.executequery ();


} catch (SQLException e) {


//TODO auto-generated catch block


E.printstacktrace ();


  }


return rSet;


 }

Application

Copy Code code as follows:


//Precompiled Query Development version Application 1


System.out.println ("Birthday Range Query:");


string[] S1 = {"1992", "1992"};


ResultSet ResultSet = Jdemo1.startquerylook ("select * from T_userr where year (birthdate) between?" and? ", S1);


Jdemo1.allresult (ResultSet);


//Precompiled Query Development version Application 2


System.out.println ("ID range query:");


string[] s2 = {"100", "200"};


ResultSet ResultSet2 = Jdemo1.startquerylook ("select * from T_userr where ID between?" and? ", S2);


Jdemo1.allresult (ResultSet2);

Pre-compiled extensions example two (SQL additions, deletions, modifications)

Copy Code code as follows:


/** PreparedStatement Pre-compiled version of the extension * * *


public int Startqueryall (String sql,object[] objarr) {


int count = 0;


getconnection ();


try {


pstatement = connection.preparestatement (sql);


if (objarr!=null && objarr.length>0) {


for (int i = 0; i < objarr.length; i++) {


Pstatement.setobject (i+1, objarr[i]);


    }


   }


count = Pstatement.executeupdate ();


} catch (SQLException e) {


//TODO auto-generated catch block


E.printstacktrace ();


} finally {


Close ();


  }


return count;


 }

Application

Copy Code code as follows:

  //Precompiled additions: Bulk increase
  for (int i = 0; i < i++) {
   object[] S3 = {10,10};
   jdemo1.startqueryall (INSERT into jdbctest (Username,password) VALUES (?,?), S3);
  }
  //Precompiled Extensions: Bulk Delete
  system.out.println ("Delete multiple:");
   Jdemo1.startqueryall ("Delete from T_userr where ID between?") and? ", New object[]{" 1010 "," 1030 "});