Precompiled comprehensive application resolution in the PreparedStatement class for JDBC

Advantages of precompilation
1. Preparedstatements are precompiled and can greatly improve efficiency for batch processing; also called JDBC stored procedures.

2, Use the Statement object. The Statement object is used when only one access to the database is performed. PreparedStatement objects are more expensive than statements and offer no additional benefit for one-time operations.

3, Every time a statement executes an SQL statement, the relevant database compiles the SQL statement. A preparedstatement is precompiled,& PI;   Preparedstatement supports batch processing

4, The PreparedStatement object not only contains the SQL statement, but most of the time the statement is precompiled, so when it is executed, only the DBMS needs to run the SQL statement without compiling it first. When you need to execute a Statement object multiple times, the PreparedStatement object significantly reduces runtime and, of course, speeds up database access. This transformation also provides the convenience of not having to repeat the syntax of the SQL statement, but simply changing the values of the variables in it to reexecute the SQL statement. The choice of a PreparedStatement object depends on whether the SQL statement with the same syntax has been executed multiple times, and the difference between the two is simply a variable. If it is executed only once, it should be no different from a normal object, showing the advantages of precompilation.

5. A PreparedStatement object is more efficient than a Statement object, especially if the same SQL Statement with different parameters is executed multiple times. The PreparedStatement object allows the database to precompile SQL statements, saving time and increasing the readability of the code on subsequent runs.

Precompiled core code

//Pre-compiled way to build SQL queries:
String sql = "select * from 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 precompilation
The first parameter of the following method is an SQL statement, and the second and third parameters are the range to be queried.

 
 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

  System.out.println(" Birthday range enquiry: ");
  ResultSet resultSet = jDemo1.StartQuery("select * from t_userr where year(birthdate) between ? and ?","1992","1992");
  jDemo1.AllResult(resultSet);

Precompiled extended example 1 (SQL queries)
Below, we extend the above approach to fit a wider range of situations.
The first parameter is the SQL statement, and the second parameter is a list of parameters for the query. (stored as an array)

 
 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

  //Precompiled query extension application 1
  System.out.println(" Birthday range enquiry: ");
  String[] s1 = {"1992","1992"};
  ResultSet resultSet = jDemo1.StartQueryLook("select * from t_userr where year(birthdate) between ? and ?",s1);
  jDemo1.AllResult(resultSet);
  //Precompiled query extension application 2
  System.out.println("ID Range enquiry: ");
  String[] s2 = {"100","200"};
  ResultSet resultSet2 = jDemo1.StartQueryLook("select * from t_userr where id between ? and ?",s2);
  jDemo1.AllResult(resultSet2);

Precompiled extended example 2 (SQL add, delete, modify)

 
 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

  //Precompiled additions, deletions, and extensions: bulk increases
  for (int i = 0; i < 10; i++) {
   Object[] s3 = {10,10};
   jDemo1.StartQueryAll("insert into jdbctest(username,password) values(?,?)",s3);
  }
  //Precompiled additions, deletions, and extensions: batch deletions
  System.out.println(" Delete multiple: ");
  jDemo1.StartQueryAll("delete from t_userr where id between ? and ?",new Object[]{"1010","1030"});