下边是今天探索PrepareStatement预编译where条件为in的sql语句的过程,在mysql环境中只有第四种方法实现了,Oracle中可能第三种也可以不过没有测试,如果有需要可以直接跳转。
1.通过拼接字符串设置参数×
今天在实现一个数据库批量更新的代码时,发现
String sql = "UPDATE t_demo SET columns='Well' WHERE column_id IN (?)";
这条语句中的参数在使用PrepareStatement来预编译之后,是不可以传入一个拼接字符串的,比如
String criteria="'a','b','c'";
prepareStatement.setString(1,criteria);
想要达到的效果是和执行下边一条语句一样:
UPDATE t_demo SET columns='Well' WHERE column_id IN ('a','b','c')
但是最后发现这样并不起作用,最后思考后恍然大悟。
因为PrepareStatement是预编译的,所以在编译完sql语句后发现这个sql只有一个参数,所以在设置参数的时候会默认(’a’,’b’,’c’)为一个参数,就会去查找column_id为“’a’,’b’,’c’”的数据,结果当然是不符合期望的。
2. 通过Vector设置参数×
这个是网上找到的一个方法,我的用法如下,但是最后使用后发现也是不起作用的,但是也没有报错。不知道是不是我的问题。不过还是贴上来代码供大家研究
String arr[]={"a","b","c"};
Vector v=new Vector(Arrays.asList(arr));
prepareStatement.setObject(1,v);
prepareStatement.executeUpdate();
3. 通过PrepareStatement的setArray()方法
这个也是在网上发现的方法,这个是不支持mysql数据库的,在mysql环境下使用会报SQLFeatureNotSupportedException异常
可能支持Oracle(没有测试)。
代码如下。
String arr[]={"a","b","c"};
Array v=conn.createArrayOf("VARCHAR", arr);
prepareStatement.setArray(1, v);
prepareStatement.executeUpdate();
4. 设置多个参数√
上边几个方法都在我的mysql环境中阵亡了之后,就只好使用笨方法了。那就是在in的条件中多加几个“?”。
因为我这次任务处理的数据比较多达8w多条,所以我分批处理。代码如下:
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 SQLException {
// 下边为条件,因为数据太多存在文件中
// 读取文件然后通过字符串的split方法转为数组
// TestChange是自己写的一个读文件并转为字符串的类,由于不是重点所以就不上代码了
String content = TestChange.readString3("D:/demo.txt");
String pNo[] = content.split(",");
// 设置根据参数多少多少设置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);
// 分批处理数组中的数据
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);
// 循环设置参数
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 num=stmt.executeUpdate();
Date endTime=new Date();
Long usedTime=endTime.getTime()-beginTime.getTime();
System.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();
}
}
// 设置SQL语句,一次处理多少条数据就设置多少个“?”
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());
}
}
注意:上边的代码仅说明了这种方法的思路和大致实现,但具有漏洞,NUM在执行到最后一批时还是会为100,但可能没有那么多参数需要设置。
比如我有122个数据需要update,NUM设置为100。也就是第一次设置了100个?,第二次还会继续设置100个?,但是在setString的时候后边的78个就会因为没有重新set而还会是原来的那几个,因为在我的需求中没有影响,所以没有处理,各位大佬们在参考的时候要注意
欢迎交流mail: helloleif@foxmail.com