JDBC Data Update

Source: Internet
Author: User
Tags sql injection stmt

In JDBC, it is common to use the object of the statement class to update the database (add, delete, check, change) operations

            //1. Get a database connectionConnection =getconnection (); //2. Preparing SQL statementsString sql =NULL; //sql = "INSERT into custom (Name,birth) VALUES (' No.3 ', ' 1999-9-9 ')"; //sql = "DELETE from custom where ID = 3";sql = "UPDATE custom set name = ' abc ' WHERE ID = 2"; //3. Get Statement Objectstatement =connection.createstatement (); //4 (1), execute SQL statement, Statement.executeupdate () can only execute INSERT, delete, UPDATE statement, cannot execute SELECT statementstatement.executeupdate (SQL); //4 (2),objects of the ResultSet class are used to hold query resultsResultSet ResultSet = statement.executequery (sql);

PreparedStatement and statement

1, PreparedStatement interface inherits Statement, preparedstatement instance contains compiled SQL statement, so it executes faster than Statement object;
2. As a subclass of Statement, PreparedStatement inherits all the functions of Statement. Three methods Execute, ExecuteQuery, and executeupdate have been changed so that they no longer require parameters;
3, in the JDBC application, if you are already a slight level developer, you should always replace with PreparedStatement;

General selection of PreparedStatement for the following reasons:

I. Readability and maintainability of code
Although using PreparedStatement to replace statement will make the code a few more lines, but such code from the readability or maintainability is more than the direct use of statement code much higher grade;

Using statement:

Stmt.executeupdate ("INSERT into Tb_name (COL1,COL2,COL2,COL4) VALUES ('" +var1+ "', '" +var2+ "'," +var3+ ", '" +var4+ "')"); // stmt is an statement object instance

Using PreparedStatement:

 perstmt = con.preparestatement ("INSERT into Tb_name (COL1,COL2,COL2,COL4) VALUES (?,?,?,?)" );p erstmt.setstring ( 1,var1); Perstmt.setstring ( 2,var2);p erstmt.setstring ( 3 ,var3);p erstmt.setstring ( 4,VAR4);//setxxx () Methods according to different data types have different choice perstmt.executeupdate ();  // 

Second, PreparedStatement do the most possible to improve performance.
Statements are cached by the compiler-compiled execution code of the DB, so the next call will not need to compile as long as it is the same precompiled statement, as long as the parameters are passed directly into the compiled statement execution code (the equivalent of a function) will be executed. This is not to say that only a precompiled statement that executes more than once in a connection is cached, but for the entire DB, as long as the precompiled statement syntax matches the cache, it can be executed at any time without having to compile again. In statement statements, even the same operation, and because the data for each operation is different so that the chances of matching the entire statement are minimal, it is almost impossible to match. Like what:
Insert into Tb_name (col1,col2) VALUES (' 11 ', ' 22 ');
Insert into Tb_name (col1,col2) VALUES (' 11 ', ' 23 ');
Even though the same operation is not the same as the data content, the entire statement itself does not match, there is no meaning of the cached statement. The fact is that there is no database that executes code caches after the normal statement is compiled.
Of course not. So the precompiled statements are bound to be cached, and the database itself uses a strategy, such as frequency, to determine when the pre-compiled results are no longer cached. To save more space to store new precompiled statements.

Third, the most important point is to greatly improve the security, prevent SQL injection

Even so far, some people don't even know the basic semantics of SQL syntax.
String sql = "SELECT * from Tb_name where name= '" +varname+ "' and passwd= '" +varpasswd+ "'";
If we pass [' or ' 1 ' = ' 1] in as varpasswd. User name feel free to see what will become?

SELECT * from tb_name = ' random ' and passwd = ' or ' 1 ' = ' 1 ';
Because ' 1 ' = ' 1 ' is sure to be true, so you can pass any validation. What's more:
Put [';d rop table tb_name;] Incoming in as VARPASSWD:
SELECT * from tb_name = ' random ' and passwd = ';d rop table tb_name; some databases are not going to make you successful, but there are many databases that can make these statements executable.

And if you use precompiled statements. Anything you pass in will not have any matching relationship with the original statement. As long as you use precompiled statements all the time, you don't have to worry about the incoming data. If you use ordinary statement, you may want to make a decision on the drop,;

JDBC Data Update

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.