04. Specify variable & amp; batch processing for JDBC Programming

04. Specify variable & batch processing for JDBC Programming

Boolean	Execute() Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement.
ResultSet	ExecuteQuery() Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.
Int	ExecuteUpdate() Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.
Void	SetDouble(Int parameterIndex, double x) Sets the designated parameter to the given Java double value.
Void	SetFloat(Int parameterIndex, float x) Sets the designated parameter to the given Java float value.
Void	SetInt(Int parameterIndex, int x) Sets the designated parameter to the given Java int value.
Void	SetLong(Int parameterIndex, long x) Sets the designated parameter to the given Java long value.
Void	SetNull(Int parameterIndex, int sqlType) Sets the designated parameter to SQL NULL.
Void	SetShort(Int parameterIndex, short x) Sets the designated parameter to the given Java short value.
Void	SetSQLXML(Int parameterIndex, SQLXML xmlObject) Sets the designated parameter to the given java. SQL. SQLXML object.
Void	SetString(Int parameterIndex, String x) Sets the designated parameter to the given Java String value.
Void	SetURL(Int parameterIndex, URL x) Sets the designated parameter to the given java.net. URL value

1. Specify the variables in the SQL statement

1. PreparedStatement InterfaceThe PreparedStatement interface inherits Statement. The PreparedStatement instance contains compiled SQL statements, so the execution speed is faster than the Statement object. The SQL statement contained IN the PreparedStatement object can have one or more IN parameters. The value of the IN parameter is not specified when the SQL statement is created. On the contrary, this statement reserves a question mark ("?") for each IN parameter. As a placeholder. The value of each question mark must be provided through the appropriate setXXX method before the statement is executed. As a subclass of Statement, PreparedStatement inherits all functions of Statement. The three methods, execute, executeQuery, and executeUpdate, have been changed so that they no longer need parameters. Because the PreparedStatement object has been pre-compiled, its execution speed is faster than the Statement object. Therefore, SQL statements executed multiple times are often created as PreparedStatement objects to improve efficiency. Therefore, in JDBC applications, you should always use PreparedStatement instead of Statement, that is, do not use Statement whenever possible. Interface PreparedStatement (java. SQL)

2. Advantages

(1) code readability and maintainabilityAlthough using PreparedStatement to replace Statement will produce a few more lines of code, such code is much better than directly using Statement in terms of readability and maintainability. The following uses Statement and PrepareStatement to execute an SQL Statement.
Stmt.exe cuteUpdate ("insert into tb_name (col1, col2, col2, col4) values ('" + var1 + "', '" + var2 + "'," + var3 + ", '"+ var4 + "')");
// Stmt is the Statement object instance perstmt = con. prepareStatement ("insert into tb_name (col1, col2, col2, col4) values (?,?,?,?) ");
Perstmt. setString (1, var1 );
Perstmt. setString (2, var2 );
Perstmt. setString (3, var3 );
Perstmt. setString (4, var4 );
Perstmt.exe cuteUpdate (); // prestmt is an instance of the PreparedStatement object.

(2) improve performance as much as possible with PreparedStatementIf the statement is cached by the Execution Code Compiled by the DB compiler, you do not need to compile the statement as long as it is the same pre-compiled statement in the next call, you only need to pass the parameters directly into the compiled statement Execution Code (equivalent to a function) and then get the execution. this does not mean that only the pre-compiled statements executed multiple times in a Connection are cached. Instead, if the pre-compiled statement syntax matches the cache. at any time, you can directly execute it without re-compiling. in statement statements, even if they are the same operation, the chance of matching the entire statement is very small because the data of each operation is different, and it is almost impossible to match the statement. for example:
Insert into tb_name (col1, col2) values ('11', '22 ');
Insert into tb_name (col1, col2) values ('11', '23 ');
Even if the operation is the same but the data content is different, the entire statement itself cannot match and has no significance for the cache statement. The fact is that no database will cache the code executed after compiling a common statement. Of course, not all pre-compiled statements will be cached. The database itself will use a policy, such as the usage frequency, to determine when to stop caching the existing pre-compiled results, to store more space for new pre-compiled statements.

(3) The most important thing is to greatly improve security.Even so far, some people still do not know the basic SQL syntax. string SQL = "select * from tb_name where name = '" + varname + "' and passwd = '" + varpasswd + "'"; if we pass in ['or '1' = '1] As varpasswd. the username is random and you can see what it will become? Select * from tb_name = 'free' and passwd = ''or '1' = '1'; Because '1' = '1' must be true, any verification can be performed. what's more: When ['; drop table tb_name;] is passed in as varpasswd, select * from tb_name = 'random' and passwd = ''; drop table tb_name; some databases won't let you succeed, but many databases can execute these statements. if you use precompiled statements. nothing you input will match the original statement. as long as pre-compiled statements are fully used, you do not need to worry about the incoming data. however, if you use a common statement, you may need to make painstaking judgment and worry over the drop,; and so on.

3. Source Code practice:

Import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. SQLException;/* MySQL database programming * instance (4): Use PreparedStatement to flexibly specify the variable */public class JDBC_PreparedStatement {public static void main (String [] args) in the SQL statement) {if (args. length! = 3) // The Input is incorrect. The {System. out. println ("Parament Error, Please Input Again! "); System. exit (-1);} String nameParam = args [0]; // obtain the first parameter int ageParam = Integer of the command line. parseInt (args [1]); // obtain the second parameter of the command line and convert it to an Integer int scoreParam = Integer. parseInt (args [2]); // obtain the third parameter of the command line and convert it to an integer. // 0. string url = "jdbc: mysql: // localhost: 3306/jdbc_test_db"; // Database URL (unique resource identification identifier) String DBusername = "root "; // database username String DBpasswd = "111111"; // Database Password // 1. load the database Driver and register the Driver to DriverManager. try {Class. forName ("com. mysql. jdbc. Driver ");} catch (ClassNotFoundException e) {e. printStackTrace ();} // 2. connect to the database through the database URL Connection conn = null; PreparedStatement prestmt = null; try {conn = DriverManager. getConnection (url, DBusername, DBpasswd); // 3. get the PreparedStatement object String SQL = "insert into test (name, age, score) values (?,?,?) "; Prestmt = conn. prepareStatement (SQL); prestmt. setString (1, nameParam); // you can specify prestmt for each variable. setInt (2, ageParam); prestmt. setInt (3, scoreparam1_prestmt.exe cute ();} catch (SQLException e) {e. printStackTrace ();} // 5. release JDBC resources if (prestmt! = Null) // close the Declaration {try {prestmt. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (conn! = Null) // close the connection {try {conn. close () ;}catch (SQLException e) {e. printStackTrace ();}}}}

(1) set command line parametersRight-click the project and choose Run as> Open Run Dialog> Main Class and select "JDBC_PreparedStatement"
(2) running result

Ii. Batch Processing SQL statements (1) Source Code

Import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. SQLException;/* MySQL database programming * instance (5): JDBC batch processing DML statements. try Statement, PreparedStatement */public class JDBC_Batch {public static void main (String [] args) {// 0. string url = "jdbc: mysql: // localhost: 3306/jdbc_test_db"; // Database URL (unique resource identification identifier) String DBusername = "root "; // database username String DBpasswd = "111111 ";// Database Password // 1. load the database Driver and register the Driver to DriverManager. try {Class. forName ("com. mysql. jdbc. driver ");} catch (ClassNotFoundException e) {e. printStackTrace ();} // 2. connect to the database through the database URL Connection conn = null; PreparedStatement prestmt = null; try {conn = DriverManager. getConnection (url, DBusername, DBpasswd); // 3. get the PreparedStatement object String SQL = "insert into test (name, age, score) values (?,?,?) "; Prestmt = conn. prepareStatement (SQL); prestmt. setString (1, "aaa"); prestmt. setInt (2, 19); prestmt. setInt (3, 551_1_prestmt.exe cute (); //. add the First insert record prestmt. setString (1, "bbb"); prestmt. setInt (2, 20); prestmt. setInt (3, 663471_prestmt.exe cute (); //. add the second insert record prestmt. setString (1, "ccc"); prestmt. setInt (2, 21); prestmt. setInt (3, 7720.1_prestmt.exe cute (); //. add the third insert record} catch (SQLException e) {e. printStackTrace ();} // 5. release JDBC Resources Source if (prestmt! = Null) // close the Declaration {try {prestmt. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (conn! = Null) // close the connection {try {conn. close () ;}catch (SQLException e) {e. printStackTrace ();}}}}

(2) running result

Description and analysis: here we use SQL statements to insert data statically. We can also use the Statement addBatch method to implement JDBC batch processing of SQL statements.
Statement stmt = conn. createStatement ();
Stmt. addBatch ("insert into test (name, age, score) values ('ppp ', 30,88 )");
Stmt. addBatch ("insert into test (name, age, score) values ('ooo', 31,89 )");
Stmt. addBatch ("insert into test (name, age, score) values ('qqq', 32, 90 )");
Stmt.exe cuteBatch (); // Execute SQL statements in batches