PreparedStatement is a sub-interface of Statement, and its name shows that it is a "ready" Statement, so it represents a pre-compiled SQL statement; the object to obtain it can be obtained by the preparedStatement (String sql) method in Connection, where the parameters are SQL statements with placeholders, so it is in it. Some setXxx() methods to supplement placeholder variable values are provided.
Many people here are wondering why I use the PreparedStatement interface, since I can use the Statement interface to update and other operations. Presumably, when each of us uses the Statement interface, we have to compile SQL statements. So one of the biggest problems in writing is... Spelling SQL statements is so frustrating that it is easy to make mistakes when you are not careful, and it is also troublesome to maintain. For example, the following SQL statement (example from a program I wrote myself)
Now, there is such a table in the local database
Now I want to insert Student's data into the table. If we use the Stetement interface, the sql statement written should be as follows:
public void addStudent(Student student) {
String sql = "INSERT INTO examstudent VALUES ("
+student.getFlowID()
+",'"+student.getType()
+"','"+student.getIDcard()
+"','"+student.getExamcard()
+"','"+student.getStudentName()
+"','"+student.getLocation()
+"',"+student.getGrade()+")";
}This is the most disgusting point when using the Statement interface. We all want to solve this problem. PreparedStatement perfectly solves this problem. Let's take the update operation as an example. When we generate the PreparedStatement object, sql statements with placeholders are passed in as parameters, such as the sql statements in the above example converted into usable ones. The result of the precompiled sql statement is as follows:
Stringsql="INSERT
INTO examstudent VALUES (?,?,?,?,?,?,?)";
Then the setXxx() method is called to set the value of the attribute corresponding to each placeholder. This method has two parameters, one is the index value of a placeholder (the index value starts from 1), the other is the specific value of the placeholder variable, and finally run the executeUpdate() in PreparedStatement to perform the update operation. The specific examples are as follows:
Now there is a table named animal in the local database, as follows:
Now the specific code for inserting information into the database using PreparedStatement is as follows:
Note that the configuration files used to connect JDBC in the code are mentioned in another article of mine: http://blog.csdn.net/qq_38785658/article/details/72915587
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
import org.junit.Test;
public class TestPreparedStatement {
@Test
public void PreparedStatementTest() throws Exception{
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1. Connecting to the database
con = getConnection();
//2. Preparing sql statements
String sql = "INSERT INTO Animal (id,name,age) VALUES (?,?,?)";
//3. Get the preparedStatement object
ps = con.prepareStatement(sql);
//4. Invoke SetXxx() method to insert data
ps.setInt(1, 6);//First placeholder
ps.setString(2, "egg");//Second placeholder
ps.setInt(3, 8);//Third placeholder
//5. Perform update operations
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally{
release(rs, ps, con);//close database
}
}
public Connection getConnection() throws Exception{//Get the database connection
String driverClass = null;
String url = null;
String user = null;
String password = null;
Properties properties = new Properties();
InputStream in = this.getClass().getClassLoader().getResourceAsStream("jdbc.properties");
properties.load(in);
driverClass = properties.getProperty("driver");
url = properties.getProperty("jdbcurl");
user = properties.getProperty("user");
password = properties.getProperty("password");
Class.forName(driverClass);
return DriverManager.getConnection(url, user, password);
}
public void release(ResultSet rs , PreparedStatement ps , Connection con){//Release database connections
if(rs != null){
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
if(ps != null){
try {
ps.close();
} catch (Exception e) {
e.printStackTrace();
}
if(con != null){
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
}That's how PreparedStatement performs update operations. Similarly, query operations are similar, so I won't write - -
In addition, there are several benefits to using this interface
It can prevent sql injection
(2) It is more efficient than Statement.