JDBC之谨慎使用动态SQL语句传参

前言

这几天在用jdbc连接MySQL数据库，循环单次提交相似的SQL效率相当的低下，后来便把目光放在了Batch上面，想通过addBatch()优化数据提交的速度。

结果这个方法直接让我的屎山代码溃散不堪…

主要原因是我在编写SQL语句的时候，只想到了PreparedStatement允许使用参数，将多个变量赋值到参数中，但没有想过这些未知参数如果数量不确定的话，会有什么后果。

接下来会阐述下程序编写sql语句时思路的错误，以及因此而导致的问题

最后给出一个不完美，但已经是我能想到的尽可能不推翻先前想法的解决方案

起因

JDBC中的PreparedStatement是个好东西，只用在语句中加个”?”，就可以将值传入语句，然后予以提交。

因此，为了实现弹性效果，我直接在一个Service类用一个ArrayList装了数个值，而不同方法有不同的String变量装一个sql语句，每个语句中的”?”都不相同。这个想法是，我只用在各个方法中定义好需要放进去的数量，原样传入Dao类中，Dao类一个方法里就可以将不同的需求转换成所需语句，传入数据库了。

既简洁，又弹性，我觉得相当符合我的想象。

然而，问题在于，我没事先阅读PreparedStatement中的addBatch语句的用法，也没有想过PreparedStatement是用来作固定语句的参数绑定的，而我这种弹性的写法，对于PreparedStatement来说，真是相当的…操蛋…

对于我的想象力来说是很美好的，但对JDBC和SQL来说，我属于是Too young too simple了

来看一段事发地带：

String bookSql = "INSERT INTO "+BOOK_TABLE+" (Type,Tittle,Author,Publisher,Category,Isbn) VALUES (?,?,?,?,?,?)";

booklist是一个Map，里面存着Interger(用来标记顺序)和 ArrayList<String>Values

pstmt = conn.prepareStatement(bookSql);
ArrayList<String> list = bookList.get(i);
for (String information : list)
{
    pstmt.setString(bookList.get(i).indexOf(information) + 1, information);
}
pstmt.addBatch();

看起来相当的简洁而美好…（至少我自己认为这很美好，写的时候直接不用脑子就敲出来了）

但事实上，如果按照这个写法，你将永远只能存入bookList中最后一行的数据…

问题

我们都知道，prepareStatement是继承的Statement类，而相对应，addBatch()也是继承的Statement中的addBatch，这对于理解addBatch的逻辑较为重要，也是本问题出现的原因所在。

addBatch()乍一看，并没有要求你提交什么sql语句进去，好像跟一个数组/集合一样，只用把PreparedStatement调用即可，然后它就会将你的setString语句进行记录，并最后允许你用一行executeBatch一并提交。

但实际上，addBatch中，如果我们看Statement的addBatch(String sql)就会知道，addBatch要求传入的是语句，每次调用存一行语句进入Batch。

而在PreparedStatement中，addBatch根据你的pstmt.set设定不同的参数，最后合并为一个语句。这意味着，如果你使用的setString是固定的：

pstmt.setString(1, bookList.get(key).get(0));
pstmt.setString(2, bookList.get(key).get(1));
pstmt.setString(3, bookList.get(key).get(2));
pstmt.setString(4, bookList.get(key).get(3));
pstmt.setString(5, bookList.get(key).get(4));
pstmt.setString(6, bookList.get(key).get(5));

那么就毫无问题，Batch会将参数并入语句中存储。

但如果是像事发地带，进行循环传参的话…

Batch没办法认为这是一句同一句话…

因为每次不只是参数不同，而且语句也不一样（至少Batch是这么认为的），况且也不是由addBatch在合并参数，而是foreach在做，传给addBatch的只是一段语句而已

对于addBatch(String sql)而言，这是完全没问题的，但对于addBatch而言，这是为时过早的艺术

所以Batch就一直在新建一行新的，而将原先存储的语句丢出去，因为每次循环得到的语句都跟上次的不同，而非是相同语句不同参数。

最后传出的，只剩下了最后一次传入batch的语句。

最后我只好把它临时写成了固定的…

然后用一堆判断确定arraylist的大小，而决定赋值…

//Next is the worst shit hill in this project
pstmt.setString(1, bookList.get(key).get(0));
pstmt.setString(2, bookList.get(key).get(1));
pstmt.setString(3, bookList.get(key).get(2));
pstmt.setString(4, bookList.get(key).get(3));
pstmt.setString(5, bookList.get(key).get(4));
pstmt.setString(6, bookList.get(key).get(5));
//Next is extra field, add lines as you want.
if (bookList.get(key).size() == 7)
{
    pstmt.setString(7, bookList.get(key).get(6));
}
if (bookList.get(key).size() == 8)
{
    pstmt.setString(8, bookList.get(key).get(7));
}
if (bookList.get(key).size() == 9)
{
    pstmt.setString(9, bookList.get(key).get(8));
}
if (bookList.get(key).size() == 10)
{
    pstmt.setString(10, bookList.get(key).get(9));
}
pstmt.addBatch();

解决

但这样的代码太丑了，真的，我要是把这种东西拿给任何人看，先不说脏别人的眼，先能把我自己给整自闭了

之前说过，PreparedStatement是一种固定的语句，用于绑定参数

按照官网的说法”A SQL statement is precompiled and stored in a PreparedStatement object.”

所以使用弹性的方式写PreparedStatement本身就是离经叛道的x

只好作罢，选用回Statement写这一段代码，抛弃setString，转用String类自己的方法进行赋值

StringBuilder sql = new StringBuilder(bookSql + "(");
for (String information:bookList.get(key))
{
    sql.append("'").append(information).append("'").append(",");
    if(bookList.get(key).indexOf(information)==bookList.get(key).size()-1)
    {
        sql = new StringBuilder(sql.substring(0, sql.length() - 1));
        sql.append(")");
    }
}
stmt.addBatch(sql.toString());

真就是用String一点点拼接上去…

我觉得这也挺蠢的，而且造成了SQL注入的风险（PreparedStatement传值本就是为了规避这种情况）

暂时如此吧，之后写用户传入数据的时候就得小心些，得限制用户的输入内容，不然传入一段String的SQL注入就GG了

尾言

切记要给jdbc连接语句中加入启用batch的参数，否则batch还是一行一行递交的。

前言

起因

问题

解决

尾言

Send Comment Edit Comment