PreparedStatement的addBatch方法抛出异常

[绝望的奶黄包]

插入的是List对象pointDataList，每个pointDataList.对象里面还包含一个List<TestDataPointConclusionBean>.

String insertDataSql = "insert into tdpointdata(id,subjectid,tempcode,chromosomecode,chromosomeposition,ref,alts,alt,pfirst,afnormal,callnormal,afdisease,calldisease) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement pst = null;
//省略jdbc连接，try catch ，关闭自动提交等无关代码
pst = conn.prepareStatement(insertDataSql);
for (int i = 0; i < pointDataList.size(); i++) {
                pst.setLong(1, dataId);
                pst.setLong(2, pointDataList.get(i).getSubjectId());
                pst.setString(3, pointDataList.get(i).getTempCode());
                pst.setString(4, pointDataList.get(i).getChromosomeCode());
                pst.setLong(5, pointDataList.get(i).getChromosomePosition());
                pst.setString(6, pointDataList.get(i).getRef());
                pst.setString(7, pointDataList.get(i).getAlts());
                pst.setString(8, pointDataList.get(i).getAlt());
                pst.setDouble(9, pointDataList.get(i).getPfirst());
                pst.setFloat(10, pointDataList.get(i).getAfnormal());
                pst.setInt(11, pointDataList.get(i).getCallnormal());
                pst.setFloat(12, pointDataList.get(i).getAfdisease());
                pst.setInt(13, pointDataList.get(i).getCalldisease());
                //第一次可以执行addBatch
                pst.addBatch();
                List<TestDataPointConclusionBean> pointConclusionList = pointDataList.get(i).getPointDataConclusions();
                if (CollectionUtils.isEmpty(pointConclusionList)) {
                    dataId++;
                    continue;
                }
                for (int j = 0; j < pointConclusionList.size(); j++) {
                    //这里抛出异常
                    pst.addBatch(insertConSql + "(" + conclusionId + maintenanceSql + dataId + "," + pointConclusionList.get(j).toString() + ")");
                    conclusionId++;
                }
                dataId++;
                pst.executeBatch();
                conn.commit();
            }

对象名有点长。懒得看的话就当做是学生-成绩表，学生表的主键是成绩表的外键。
现在插入一个学生后，再开始批量插入成绩数据。就会进入异常。
在此处进入异常


org.postgresql.util.PSQLException: 在 PreparedStatement 上不能使用获取查询字符的查询方法。

[绝望的奶黄包]

刚才仔细查看了一下API，发现PreparedStatement 只重写了addBatch的无参方法，也就是只能使用一句sql，通过设置不同的参数来达到批量编译。那么混合的插入sql有什么好的方式么。 正确的操作应该是所有的学生批量插入，再将所有成绩批量插入。 但是成绩中关联了学生的id 所以只能插入一个学生，然后再插入学生的成绩。 再插入一个学生，再插入学生的成绩 这种混合方式应该如何处理

[颐牟权月]

        PreparedStatement pst1 = null;
        PreparedStatement pst2 = null;
        try {
            pst1 = conn.prepareStatement(insertDataSql);
            pst2 = conn.prepareStatement(insertConSql);
            for (int i = 0; i < pointDataList.size(); i++) {
                pst1.setLong(1, dataId);
                //.......
                pst1.addBatch();
                List<TestDataPointConclusionBean> pointConclusionList = pointDataList.get(i).getPointDataConclusions();
                if (CollectionUtils.isEmpty(pointConclusionList)) {
                    dataId++;
                    continue;
                }
                for (int j = 0; j < pointConclusionList.size(); j++) {
                    if (!pointConclusionList.get(j).isValid()) {
                        continue;
                    }
                    pst2.setLong(1, conclusionId);
                    //......
                    conclusionId++;
                }
                dataId++;
                //每2000条数据提交一次
                if ((i > 0 && i % 10 == 0) || i == pointDataList.size() - 1) {
                    pst2.executeBatch();
                    conn.commit();
                }
            }
            pst1.executeBatch();
            conn.commit();
        } catch (Exception e) {
            throw new RayseException(TmmsErrCode.TMMS_DATA_GENE_IMPORT_FAIL);
        } finally {
            try {
                if (pst1 != null) {
                    pst1.close();
                }
                if (pst2 != null) {
                    pst2.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                
            }
        }

[绝望的奶黄包]

附上第二次插入的拼接sql，其实在异常里面也能看到sql，此处再贴出来

insert into tdpointdataconclusion (id,createdate,creator,datastatus,lastmodifier,lastmodifydate,testdatapointid,extrasamplecode,type,conclusion) values (883640,Tue Dec 20 00:00:00 CST 2016,2,1,0,null,3605,'GT_47',1,1)

[huahua.Dr]

兄弟，报异常的地方原因是不能使用prepareStatement接口去实现addBatch()对个sql语句，addBatch()方法不接受参数的输入（即使传入的参数也无法执行，没有重写这个方法因此不支持），你可以查看一下官方文档API，大概意思是说：addBatch()只是使用适当的setXXX()方法将设置的绑定参数添加到批处理中，（对于CallbleStatement或OracleCallableStatement对象也是一样）

For prepared statements, update batching is used to batch multiple executions of the same statement with different sets of bind parameters. For a PreparedStatement or OraclePreparedStatement object, the addBatch() method takes no input--it simply adds the operation to the batch using the bind parameters last set by the appropriate setXXX() methods. (This is also true for CallableStatement or OracleCallableStatement objects, but remember that in the Oracle implementation of standard update batching, you will probably see no performance improvement in batching callable statements.)

For example (again assuming a Connection instance conn):

...
PreparedStatement pstmt =
conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");

pstmt.setInt(1, 2000);
pstmt.setString(2, "Milo Mumford");
pstmt.addBatch();

pstmt.setInt(1, 3000);
pstmt.setString(2, "Sulu Simpson");
pstmt.addBatch();
...

At this point, two operations are in the batch.

Because a batch is associated with a single prepared statement object, you can batch only repeated executions of a single prepared statement, as in this example.

可以使用createStatement来实现这个功能，但性能就不好说了。。。。
...
Statement stmt = conn.createStatement();

stmt.addBatch("INSERT INTO emp VALUES(1000, 'Joe Jones')");
stmt.addBatch("INSERT INTO dept VALUES(260, 'Sales')");
stmt.addBatch("INSERT INTO emp_dept VALUES(1000, 260)");
...

[huahua.Dr]

“对个sql语句”改成 “多个sql语句”，谢谢