SQL statement precompile

Pre compilation of SQL statements can prevent SQL injection and improve security because SQL statements have been precompiled before the program runs. Before the first operation of the database, SQL statements have been analyzed, compiled and optimized by the database. The corresponding execution plan will also be cached and allow the database to query in a parameterized form. When the program runs, parameters will be transferred dynamically. When PreprareStatement is given, even if there are sensitive characters such as or '1=1' in the parameter, the database will be processed as the attribute value of a parameter and a field instead of as an SQL instruction, which plays the role of SQL injection.

I. loading drive

Class.forName("com.mysql.jdbc.Driver");//Load driver

II. Establish connection database object

Connection conn=null;
conn = DriverManager.getConnection("jdbc:mysql:///test","root","root");

3. Define the SQL statement. The parameter is?

 String sql="select * from student1 where stunum=?";

4. Create precompiled execution object

PreparedStatement psm = null;
psm=conn.prepareStatement(sql);

V. set parameter values in sql statements

psm.setString(1,stuNum);

6. Define the result set and put the query result of the execution object into rs

ResultSet rs = null;
rs=psm.executeQuery();

7. Printing results

while (rs.next()){
                String stunum=rs.getString(1);
                String stuname=rs.getString(2);
                String stuclass=rs.getString(3);
                System.out.println("Query the student information as follows:");
                System.out.println(stunum+" "+stuname+" "+stuclass);
 }

VIII. Close resources

            if (rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (psm!=null){
                try {
                    psm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }