Statement and PreparedStatement

Statement

Statement: Statement is an important method for java to perform database operations. It is used to send SQL statements to be executed to the database based on the established database connection. Specific steps:

1. First import java.sql.*; this package.
2. Then load the driver, create a connection, and get the implementation object of the Connection interface, for example, the object name is conn.
3. conn then use the object to create an instance of the Statement, is:
Statement stmt = conn.creatStatement("SQL语句字符串");

The Statement object is used to send SQL statements to the database. There are actually three Statement objects, all of which serve as containers for executing SQL statements on a given connection:Statement, PreparedStatement (it inherits from Statement) and CallableStatement (it inherits from PreparedStatement). They are all dedicated to sending specific types of SQL statements:

The Statement object is used to execute simple SQL statements without parameters; the
PreparedStatement object is used to execute pre-compiled SQL statements with or without parameters; the
CallableStatement object is used to execute calls to stored procedures in the database.

To sum up: every time Statement executes sql statement, the database must execute the compilation of sql statement.It is best used when the query is executed only once and the result is returned, The efficiency is higher than PreparedStatement. 但存在sql注入风险. PreparedStatement is pre-compiled and executed. 在执行可变参数的一条SQL时,PreparedStatement要比Statement的效率高, Because DBMS pre-compiling a piece of SQL will of course be more efficient than compiling a piece of SQL multiple times. It has better security and effectively prevents SQL injection problems. 对于多次重复执行的语句,使用PreparedStatement效率会更高一点. The execution of SQL statements can take parameters, and supports batch execution of SQL. Due to the Cache mechanism, the pre-compiled statement will be placed in the Cache, and the same SQL statement can be directly taken out of the Cache when the same SQL statement is executed next time.

PreparedStatement pstmt  =  con.prepareStatement("UPDATE EMPLOYEES  SET name= ? WHERE ID = ?");
pstmt.setString(1, "李四");
pstmt.setInt(2, 1);
pstmt. executeUpdate();

Then CallableStatement extends the PreparedStatement interface, 用来调用存储过程and it provides 输入和输出参数support for the CallableStatement interface. The CallableStatement interface also supports the SQL query of the input parameters provided by the PreparedStatement interface.

PreparedStatement: The database will pre-compile the sql statement. When the same sql statement is executed next time, the database will not be pre-compiled, but directly use the database buffer to improve the efficiency of data access. If the sql statement is only executed once , Will not be reused in the future. PreparedStatement通过?来传递参数, To avoid the problem of SQL injection caused by spelling SQL, so the security is better.

In fact, these two do the same thing, that is, create an object and then call the executeQuery method through the object to execute the sql statement.

String sql = "select * from users where  username= '"+username+"' and userpwd='"+userpwd+"'";
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);

And the following is a part of the statement fragments that use the PrepareStatement method to create the pstmt object, and then query through this object.

String sql = "select * from users where  username=? and userpwd=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, userpwd);
rs = pstmt.executeQuery();

Compare PrepareStatement and Statement

The main difference between PrepareStatement and Statement is that the variables in the above sql statement are extracted. This is the first advantage I want to say, PrepareStatement can improve the readability of the code.

Statement can execute multiple SQL statements of different types in batches
conn = DbUtils.getConnection();
Statement sts = conn.createStatement();
sql1 = "insert into t_user(userid,username,password)values(seq_t_user.nextval,'a','b')";
sql2 = "update t_user set password='111' where userid=43";
// 添加批处理的sql语句
sts.addBatch(sql1);
sts.addBatch(sql2);
sts.executeBatch();
PreparedStatement: multiple SQL statements with the same type of changing parameters can be executed in batches
conn = DbUtils.getConnection();
sql = "insert into t_user(userid,username,password)values(seq_t_user.nextval,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
for(int i = 0 ; i < 10 ; i++){
	ps.setString(1, "a"+i);
	ps.setString(2, "b"+i);
	ps.addBatch();
}
ps.executeBatch();

ParperStatement提高了代码的灵活性和执行效率.
The PrepareStatement interface is a sub-interface of the Statement interface, which inherits all the functions of the Statement interface. itIt is mainly used to solve the efficiency problem of using the Statement object to execute the same SQL statement multiple times. The mechanism of the ParperStatement interface is to compile the SQL statement in advance when the database supports precompilation. When the SQL statement is executed multiple times, the compiled SQL statement can be executed directly, which greatly improves the flexibility and execution efficiency of the program. .

String sql = "select * from user where username= '"+varname+"' and userpwd='"+varpasswd+"'";
stmt = conn.createStatement();
rs = stmt.executeUpdate(sql);

This is to verify the username and password, right. But if we pass in'or '1' = 1'as a password, guess what will happen.

select * from user where username = 'user' and userpwd = '' or '1' = '1';
String sql = "select * from user where username= '"+varname+"' and userpwd='"+varpasswd+"'";
stmt = conn.createStatement();
rs = stmt.executeUpdate(sql);

The general process in the process of adding, deleting, modifying and checking the database:

(1) Create a Connection object and SQL query command string;
(2) Pass in SQL query commands to the Connection object to obtain the PreparedStatement object;
(3) Perform executeUpdate() or executeQurey() on the PreparedStatement object to obtain the result;
( 4) Close the PreparedStatement object and the Connection object successively.

CallableStatement

存储过程:
It is a set of SQL statements to complete specific functions, similar to a programming language, but also includes data types, process control, input and output, and its own function library (Is a set of SQL statements). A stored procedure can be said to be a record set. It is a code block composed of some T-SQL statements. These T-SQL statement codes implement some functions like a method (addition, deletion, modification, and checking of a single table or multiple tables), and then Give this code block a name and call it when you use this function.

 Class.forName(driver_class_name);
 Connection connection = DriverManager.getConnection(url, username, password);
 /**
  * 存储过程语法
  * 带参数:{call 存储过程名称 [参数1,参数2,.......]}
  * 不带参数:{call 存储过程名称}
  */
 String sql = "{call storage_name(?,?,?......)}";
 CallableStatement callableStatement = connection.prepareCall(sql);
 /**
  * 前两个参数是in,后一个参数是out
  * 类似于执行该语句:select name from user where id = x and sex = y
  * in x int,out name varchar(20)
  * 但是此时没有结果集,取查询的数据不能通过getResultSet()获取,而是通过 String name = callableStatement.getString(3); 获取
  */
 callableStatement.setString(1, "aaa");
 callableStatement.setInt(2, 2);
 callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
 
 // 返回的是一个boolean值
 boolean execute = callableStatement.execute();
 // 如果一个存储过程中包含多条语句,返回结果集可能有多个,此时需要逐个取出结果
 do {
     ResultSet resultSet = callableStatement.getResultSet();
     while (resultSet.next()) {
         for (int i = 0; i < resultSet.getMetaData().getColumnCount(); i++) {
             System.out.print(resultSet.getString(i) + "\t");
         }
         System.out.println();
     }
     System.out.println("=====================================");
     // 如果直接用while循环,则会忽略第一个结果集,故需要采用 do{}while()
 } while (callableStatement.getMoreResults());