Implementation of multiple parameters in PrepareStatement SQL statement in,

Implementation of multiple parameters in PrepareStatement SQL statement in,

The following is the process of exploring the SQL statement with PrepareStatement pre-compiling where condition as in today. Only the fourth method is implemented in the mysql environment. The third method may be available in Oracle but not tested, if necessary, you can jump directly.

1. Set the parameter × by concatenating strings

Today, when implementing code for batch updating a database, we found that

String sql = "UPDATE t_demo SET columns='Well' WHERE column_id IN (?)";

After PrepareStatement is used to pre-compile the parameters in this statement, a concatenated string cannot be passed in, for example

String criteria="'a','b','c'";prepareStatement.setString(1,criteria);

The result is the same as executing the following statement:

UPDATE t_demo SET columns='Well' WHERE column_id IN ('a','b','c')

However, this did not work at last, and I suddenly realized it.
Because PrepareStatement is pre-compiled, after compiling an SQL statement, it is found that this SQL statement has only one parameter. Therefore, when setting parameters, the default value ('A', 'B ', if 'C') is a parameter, the system queries the data whose column_id is "'A', 'B', or 'C'. Of course, the results do not meet the expectations.

2. Set parameter X through Vector

This is a method found on the Internet. My usage is as follows, but it does not work after use, but no error is reported. I don't know if it's my problem. However, you still need to paste the code for your research.

String arr[]={"a","b","c"};Vector v=new Vector(Arrays.asList(arr));prepareStatement.setObject(1,v);prepareStatement.executeUpdate();

3. Use the setArray () method of PrepareStatement

This is also a method found on the Internet. This method does not support mysql databases.SQLFeatureNotSupportedExceptionException
Oracle may be supported (not tested ).
The Code is as follows.

String arr[]={"a","b","c"};Array v=conn.createArrayOf("VARCHAR", arr);prepareStatement.setArray(1, v);prepareStatement.executeUpdate();

4. Set multiple parameters √

After the above methods were killed in my mysql environment, I had to use the stupid method. That is to add a few more "?" in the in condition.
Because I processed more than data records in this task, I processed them in batches. The Code is as follows:

Import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. SQLException; import java. util. date; public class Test {static String url = ...; static String user = ...; static String password = ...; static String SQL = "UPDATE t_demo SET columns = 'well' WHERE column_id IN (_ SQL)"; static int NUM = 100; public static void main (String [] args) throws SQLExceptio N {// The following is a condition, because there is too much data in the file, // read the file and convert it to an array using the string split method. // TestChange is a read file written by yourself and converted into a string class, because it is not important, the code will not be available. String content = TestChange. readString3 ("D:/demo.txt"); String pNo [] = content. split (","); // sets the SQL setSql (); Connection conn = null; PreparedStatement stmt = null; try {Class. forName ("com. mysql. jdbc. driver "); conn = DriverManager. getConnection (url, user, password); conn. setAutoCommit (false); Stmt = conn. prepareStatement (SQL); // process data in the array in batches for (int begin = 0; begin <pNo. length; begin + = NUM) {Date beginTime = new Date (); int end = (begin> (pNo. length-NUM )? PNo. length :( begin + NUM); String arr [] = (String []) Arrays. copyOfRange (pNo, begin, end); // loop Setting Parameter int flag = 1; for (String criteria: arr) {if (flag = 1) System. out. print ("First:" + criteria + ","); if (flag = arr. length) System. out. println ("Last:" + criteria); stmt. setString (flag, criteria); flag ++;} int num1_stmt.exe cuteUpdate (); Date endTime = new Date (); Long usedTime = endTime. getTime ()-beginTime. getTime (); S Ystem. out. println ("Data to be change:" + begin + "-" + end + ", changed:" + num + ", used time:" + usedTime);} conn. commit (); System. out. println ("Well Done! ");} Catch (Exception e) {e. printStackTrace (); conn. rollback ();} finally {stmt. close (); conn. close () ;}// set the SQL statement. How many pieces of data can be set at a time?" Private static void setSql () {StringBuffer sb = new StringBuffer (); for (int I = 0; I <NUM; ++ I) {sb. append ("?, ");} Sb. deleteCharAt (sb. lastIndexOf (", "); SQL = SQL. replace (" _ SQL ", sb. toString ());}}

Note: The code above only shows the idea and general implementation of this method. However, with the vulnerability, NUM will still be 100 when it is executed to the last batch, but there may not be so many parameters to set.
For example, if I have 122 pieces of data to be updated, set NUM to 100. That is, the first time 100 ?, The second time, we will continue to set 100 ?, However, when setString is used, the 78 backend servers will be the original ones because they are not re-set, because they are not affected in my needs, so they are not processed, you should pay attention to it during your reference.
Welcome to mail: helloleif@foxmail.com

Top

1

Step on

0

View comments