On the difference and advantages between createstatement and preparestatement in JDBC.

I have some humble opinions, you do not like to spray.

Let's talk about what these two are doing. In fact, the two jobs are the same, that is, create an object and then invoke the ExecuteQuery method through the object to execute the SQL statement. Said is the difference between createstatement and preparestatement, but actually said is the difference between statement and Preparestatement, I believe that we have seen a lot of this information and blog, I am here to mention a few points, As you can see, when you remember, you don't see it as a supplement ~ start talking about their differences. The most obvious difference is that the SQL statement is executed in a different format. Let's go up two pieces of code to see the difference between them:

Code background: We have a database with a user table with username,userpwd two columns. We need to find out these two columns of data.

This is the use of the Createstatement method to create the Stmt object, and then through some of the statement fragments of his query.

String sql = "SELECT * from Users where  username= '" "+username+" ' and Userpwd= ' "" +userpwd+ "";
stmt = Conn.createstatement ();
rs = stmt.executequery (SQL);

The following is the use of the Preparestatement method to create the Pstmt object, and then through a part of the object query fragment of the statement.

String sql = "SELECT * from Users where username=?" and userpwd=? ";
pstmt = conn.preparestatement (sql);
Pstmt.setstring (1, username);
Pstmt.setstring (2, userpwd);
rs = Pstmt.executequery ();

Believe that writing to this, we can see many people, the original preparestatement and statement the main difference is to the above SQL statements in the variables out. That's the first big thing I've got to say, preparestatement can improve the readability of your code. What the. You don't think there's anything that's going to improve readability. Well, let's take a look at the following two pieces of code, after you talk.

Code background: We have a database with a book table with Bookid,bookname,bookauthor,booksort,bookprice five columns. We're going to add some data to this table.

Statement edition

String sql = "INSERT into book (Bookid,bookname,bookauthor,booksort,bookprice) VALUES (' +var1+" ',
                                ' "+var2+" ', "+ var3+ "," "+var4+", "+var5+" ";
stmt = Conn.createstatement ();
rs = stmt.executeupdate (SQL);

Parperstatement Edition

String sql = "INSERT into book (Bookid,bookname,bookauthor,booksort,bookprice) VALUES (?,?,?,?,?)";
pstmt = conn.preparestatement (sql);
Pstmt.setstring (1,VAR1);
Pstmt.setstring (2,VAR2);
Pstmt.setstring (3,VAR3);
Pstmt.setstring (4,VAR4);
Pstmt.setstring (5,VAR5);
Pstmt.executeupdate ();

What do you think. Anyway, when I hit this line of code, the whole quote comma just gave me a thrill.

Here's the 2nd advantage. Parperstatement improves code flexibility and execution efficiency.

The Preparestatement interface is a sub-interface of the statement interface, and he inherits all the functionality of the statement interface. It is mainly 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 statements in advance when the database supports precompilation, and when executing this SQL statement, you can execute the compiled SQL statement directly, which greatly improves the flexibility and execution efficiency of the program. Last but not least, a great advantage over statement is that it is safe.

What are you talking about? It's not safe, so I'll give you a line of code, and you can tell me what it is.

String sql = "SELECT * from user where username= '" +varname+ "' and Userpwd= '" "+varpasswd+" ";
stmt = Conn.createstatement ();
rs = stmt.executeupdate (SQL);

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

SELECT * FROM user where username = ' user ' and userpwd = ' or ' 1 ' = ' 1 ';

Found it. This is a perpetual truth, because 1 is always equal to 1. So you can get permission anyway. Wow. That's bad. It's not the worst, you see.

String sql = "SELECT * from user where username= '" +varname+ "' and Userpwd= '" "+varpasswd+" ";
stmt = Conn.createstatement ();
rs = stmt.executeupdate (SQL);

This is still the line of code. This time we put ' or ' 1 ' = 1 ';d ROP table book; Wow. It's broken again. This time the table was deleted directly. However, if you use preparestatement, this problem will not arise. The data you pass in does not intersect with the original data at all, nor does it occur.

This is the end of the article. Hope can help~