JSP in the PreparedStatement Object Operation Database Use Tutorial
- 2021-09-24 23:18:09
- OfStack
The PreparedStatement interface inherits and differs from Statement in two ways:
The PreparedStatement instance contains the compiled SQL statement. This is to make the statement "ready". An SQL statement contained in an PreparedStatement object can have one or more IN parameters. The value of the IN parameter was not specified when the SQL statement was created. Instead, the statement reserves a question mark ("?") for each IN parameter As a placeholder. The value of each question mark must be supplied by the appropriate setXXX method before the statement is executed.
Because the PreparedStatement object is precompiled, it executes faster than the Statement object. Therefore, SQL statements that are executed multiple times are often created as PreparedStatement objects to improve efficiency.
As a subclass of Statement, PreparedStatement inherits all the functions of Statement. It also adds 1 set of methods to set the value sent to the database to replace the IN parameter placeholder. Meanwhile, the three methods execute, executeQuery, and executeUpdate have been changed so that they no longer require parameters. The Statement form of these methods (the form that accepts SQL statement parameters) should not be used for PreparedStatement objects.
1. Create an PreparedStatement object
The following code snippet (where con is an Connection object) creates an PreparedStatement object containing an SQL statement with two IN parameter placeholders:
PreparedStatement pstmt = con.prepareStatement("UPDATE table4 SET m = ? WHERE x = ?");
The pstmt object contains the statement "UPDATE table4 SET m =? WHERE x =? "It has been sent to DBMS and is ready for execution.
2. Pass IN parameters
Before executing the PreparedStatement object, you must set each? The value of the parameter. This can be done by calling the setXXX method, where XXX is the type corresponding to this parameter. For example, if the parameter has the Java type long, the method used is setLong. The first parameter of the setXXX method is the ordinal position of the parameter to be set, and the second parameter is the value to be set to that parameter. For example, the following code sets the first parameter to 123456789 and the second parameter to 100000000:
pstmt.setLong(1, 123456789);
pstmt.setLong(2, 100000000);
1 Once the parameter value of a given statement is set, it can be used to execute the statement multiple times until it is cleared by calling the clearParameters method. In the default mode of the connection (automatic submission is enabled), the statement is automatically submitted or restored when it completes.
The same PreparedStatement can be executed multiple times if the base database and driver remain open after statements are committed. If this point does not hold, it makes no sense to try to improve performance by using PreparedStatement objects instead of Statement objects.
Using pstmt (the PreparedStatement object created earlier), the following code illustrates how to set the values of two parameter placeholders and execute pstmt 10 times. As mentioned above, in order to achieve this 1, the database cannot shut down pstmt. In this example, the first parameter is set to "Hi" and kept constant. In the for loop, the second parameter is set to a different value each time: starting with 0 and ending with 9.
pstmt.setString(1, "Hi");
for (int i = 0; i < 10; i++) {
pstmt.setInt(2, i);
int rowCount = pstmt.executeUpdate();
}
3. Uniformity of data type in IN parameter
The XXX in the setXXX method is of type Java. It is an implicit JDBC type (like an SQL type) because the driver will map the Java type to the corresponding JDBC type (following the mapping specified in the § 8. 6.2 "Mapping Java and JDBC Types" table in this JDBCGuide) and send the JDBC type to the database. For example, the following code snippet sets the second parameter of the PreparedStatement object pstmt to 44, and the Java type is short:
pstmt.setShort(2, 44);
The driver sends 44 to the database as JDBC SMALLINT, which is a standard mapping of Java short type.
It is the programmer's responsibility to ensure that the Java type of each IN parameter is mapped to an JDBC type that is compatible with the JDBC data type required by the database. Consider the case where the database requires JDBC SMALLINT. If the method setByte is used, the driver sends JDBC TINYINT to the database. This is feasible because many databases can be converted from one related type to another, and generally TINYINT can be used anywhere where SMALLINT applies
Preprocessing statement object PreparedStatement, using PreparedStatement to add data, update data, delete data and query data
Example:
Step 1 Add data
<%@page language="java" contentType="text/html;charset=gb2312"%>
<%@page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<title> Get the first 2 Beginning with a record 3 A record </title>
</head>
<body>
<%
String url = "jdbc:mysql://localhost:3306/javaweb";// Connecting to the database url Address
String user = "root";// User name for logging in to the database
String password = "zhangda890126;;";// Password for the user name that logs in to the database
Connection conn = null;// Linked object
PreparedStatement pstmt = null;// Statement object
//ResultSet rs = null;// Result set object
try{
Class.forName("com.mysql.jdbc.Driver");// Loading JDBC Driver
conn = DriverManager.getConnection(url,user,password);// Linked database
}catch(ClassNotFoundException e){
out.println(" Driver class not found ");// Prompt information when an exception is thrown
}catch(SQLException e){
out.println(" Link MySQL Database failure ");// Deal with SQLException Anomaly
}
try{
String adduser = "INSERT INTO user (userid,username,password) VALUES(null,?,?)";// Add 1 Items of user information
pstmt = conn.<span style="color:#e53333;"><b>prepareStatement</b></span>(adduser);// Create a preprocessing statement object PreparedStatement
// Setting parameters
pstmt.setString(1,"YAO");
pstmt.setString(2,"yao");
// Execute statement
pstmt.executeUpdate();
}catch(SQLException e){
out.println(" Failed to add user information ");
}
try{
if(pstmt != null){
pstmt.close();
conn = null;
}
if(conn != null){
conn.close();
conn = null;
}
}catch(Exception e){
out.println(" Database shutdown failed ");
}
%>
</body>
</html>
Tip 1: Don't use the wrong case. The red mark is because the letters in front of me are capitalized, which takes a long time
Step 2 Update data
<%@page language="java" contentType="text/html;charset=gb2312"%>
<%@page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<title> Get the first 2 Beginning with a record 3 A record </title>
</head>
<body>
<%
String url = "jdbc:mysql://localhost:3306/javaweb";// Connecting to the database url Address
String user = "root";// User name for logging in to the database
String password = "zhangda890126;;";// Password for the user name that logs in to the database
Connection conn = null;// Linked object
PreparedStatement pstmt = null;// Statement object
//ResultSet rs = null;// Result set object
try{
Class.forName("com.mysql.jdbc.Driver");// Loading JDBC Driver
conn = DriverManager.getConnection(url,user,password);// Linked database
}catch(ClassNotFoundException e){
out.println(" Driver class not found ");// Prompt information when an exception is thrown
}catch(SQLException e){
out.println(" Link MySQL Database failure ");// Deal with SQLException Anomaly
}
try{
String updateuser = "UPDATE user SET password = ? WHERE userid = ?";// Add 1 Items of user information
pstmt = conn.prepareStatement(updateuser);// Create a preprocessing statement object PreparedStatement
// Setting parameters
pstmt.setString(1,"hello world");
pstmt.setInt(2,1);
// Execute statement
pstmt.executeUpdate();
}catch(SQLException e){
out.println(" Failed to add user information ");
}
try{
if(pstmt != null){
pstmt.close();
conn = null;
}
if(conn != null){
conn.close();
conn = null;
}
}catch(Exception e){
out.println(" Database shutdown failed ");
}
%>
</body>
</html>
Step 3 Delete data
<%@page language="java" contentType="text/html;charset=gb2312"%>
<%@page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<title> Get the first 2 Beginning with a record 3 A record </title>
</head>
<body>
<%
String url = "jdbc:mysql://localhost:3306/javaweb";// Connecting to the database url Address
String user = "root";// User name for logging in to the database
String password = "zhangda890126;;";// Password for the user name that logs in to the database
Connection conn = null;// Linked object
PreparedStatement pstmt = null;// Statement object
//ResultSet rs = null;// Result set object
try{
Class.forName("com.mysql.jdbc.Driver");// Loading JDBC Driver
conn = DriverManager.getConnection(url,user,password);// Linked database
}catch(ClassNotFoundException e){
out.println(" Driver class not found ");// Prompt information when an exception is thrown
}catch(SQLException e){
out.println(" Link MySQL Database failure ");// Deal with SQLException Anomaly
}
try{
String deleteuser = "DELETE FROM user WHERE userid = ?";// Add 1 Items of user information
pstmt = conn.prepareStatement(deleteuser);// Create a preprocessing statement object PreparedStatement
// Setting parameters
pstmt.setInt(1,2);
// Execute statement
pstmt.executeUpdate();
}catch(SQLException e){
out.println(" Failed to add user information ");
}
try{
if(pstmt != null){
pstmt.close();
conn = null;
}
if(conn != null){
conn.close();
conn = null;
}
}catch(Exception e){
out.println(" Database shutdown failed ");
}
%>
</body>
</html>
4. Query the data
<%@page language="java" contentType="text/html;charset=gb2312"%>
<%@page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<title> Get the first 2 Beginning with a record 3 A record </title>
</head>
<body>
<%
String url = "jdbc:mysql://localhost:3306/javaweb";// Connecting to the database url Address
String user = "root";// User name for logging in to the database
String password = "zhangda890126;;";// Password for the user name that logs in to the database
Connection conn = null;// Linked object
PreparedStatement pstmt = null;// Statement object
ResultSet rs = null;// Result set object
try{
Class.forName("com.mysql.jdbc.Driver");// Loading JDBC Driver
conn = DriverManager.getConnection(url,user,password);// Linked database
}catch(ClassNotFoundException e){
out.println(" Driver class not found ");// Prompt information when an exception is thrown
}catch(SQLException e){
out.println(" Link MySQL Database failure ");// Deal with SQLException Anomaly
}
try{
String queryAll = "SELECT * FROM user LIMIT ?,?;";// Add 1 Items of user information
pstmt = conn.prepareStatement(queryAll);// Create a preprocessing statement object PreparedStatement
// Setting parameters
pstmt.setInt(1,2);
pstmt.setInt(2,5);
// Execute statement
rs = pstmt.executeQuery();
while(rs.next()){
int userid = rs.getInt(1);
String username = rs.getString(2);
String userpassword = rs.getString(3);
out.println(" User's ID : "+userid+" User name: "+username+" User's password: "+userpassword+"<br />");
}
}catch(SQLException e){
out.println(" Failed to add user information ");
}
try{
if(pstmt != null){
pstmt.close();
conn = null;
}
if(conn != null){
conn.close();
conn = null;
}
}catch(Exception e){
out.println(" Database shutdown failed ");
}
%>
</body>
</html>