java - 无法使用单个连接执行多个准备语句(数据源)

Question

我试图通过预准备语句使用同一连接执行多个查询，但无法完全实现!

代码片段:

public class PostPrReqDaoImpl implements PostPrReqDaoInterface {

    @Override
    public boolean validate(PostPrReqBean pprb1,PostPrReqBean pprb2,PostPrReqBean pprb3) {
        System.out.println("Inside PostPrReq Dao");
        int resstat=1;
        //snuname,snuusrname,snupass,snuempid,snuemail,snudob,snuskill,snuexp,snudesg;
        java.io.PrintWriter out = null;
        try {
            out = pprb1.getBeanresponse().getWriter();
        } catch (IOException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        pprb1.getBeanresponse().setContentType("text/html");


        Connection conn=null;

        try {
            /* get the DataSource from using the JNDI name */
            Class.forName("com.mysql.jdbc.Driver");
            InitialContext ctx = new InitialContext();
            DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/Test");
            //username, pass,name, id,dob ,skillset, exp,designation ,email
            //Create connection and then continue as usual other JDBC calls 
            conn=ds.getConnection();

            System.out.println("Connection succesful!");

            if(resstat == 1)
            {
                System.out.println();

                String sqlproj1 = "INSERT INTO tblproject (name,code,location,type,lastdate)"
                    + " VALUES (?,?,?,?,?)";
              //Create a PreparedStatment with that sql and insert the values with index:
                PreparedStatement preparedStatement1a = conn.prepareStatement(sqlproj1);
                preparedStatement1a.setString(1,pprb1.getBeanprojname());
                preparedStatement1a.setString(2,pprb1.getBeanprojcode());
                preparedStatement1a.setString(3,pprb1.getBeanprojlocation());
                preparedStatement1a.setString(4,pprb1.getBeanprojtype());
                preparedStatement1a.setString(5,pprb1.getBeanprojlastdate());
                preparedStatement1a.executeUpdate(); 
                preparedStatement1a.clearParameters();
                String sqlreq1="INSERT INTO tblrequirements (code,designation,skillset,exp,ncand)"
                        + " VALUES (?,?,?,?,?)";
                 //Create a PreparedStatment with that sql and insert the values with index:
                PreparedStatement preparedStatement1b = conn.prepareStatement(sqlreq1);
                preparedStatement1b.setString(1,pprb1.getBeanprojcode());
                preparedStatement1b.setString(2,pprb1.getBeandesg());
                preparedStatement1b.setString(3,pprb1.getBeanskill());
                preparedStatement1b.setInt(4,pprb1.getBeanexp());
                preparedStatement1b.setInt(5,pprb1.getBeanncand());
                preparedStatement1a.executeUpdate();
                preparedStatement1b.executeUpdate();
                System.out.println("Data 1 inserted successfully!");

                String sqlproj2 = "INSERT INTO tblproject (name,code,location,type,lastdate)"
                        + " VALUES (?,?,?,?,?)";
                  //Create a PreparedStatment with that sql and insert the values with index:
                PreparedStatement preparedStatement2a = conn.prepareStatement(sqlproj2);
                preparedStatement2a.setString(1,pprb2.getBeanprojname());
                preparedStatement2a.setString(2,pprb2.getBeanprojcode());
                preparedStatement2a.setString(3,pprb2.getBeanprojlocation());
                preparedStatement2a.setString(4,pprb2.getBeanprojtype());
                preparedStatement2a.setString(5,pprb2.getBeanprojlastdate());

                String sqlreq2="INSERT INTO tblrequirements (code,designation,skillset,exp,ncand)"
                            + " VALUES (?,?,?,?,?)";
                 //Create a PreparedStatment with that sql and insert the values with index:
                PreparedStatement preparedStatement2b = conn.prepareStatement(sqlreq2);
                preparedStatement2b.setString(1,pprb2.getBeanprojcode());
                preparedStatement2b.setString(2,pprb2.getBeandesg());
                preparedStatement2b.setString(3,pprb2.getBeanskill());
                preparedStatement2b.setInt(4,pprb2.getBeanexp());
                preparedStatement2b.setInt(5,pprb2.getBeanncand());
                preparedStatement2a.executeUpdate(); 
                preparedStatement2b.executeUpdate();
                System.out.println("Data 2 inserted successfully!");

                String sqlproj3= "INSERT INTO tblproject (name,code,location,type,lastdate)"
                        + " VALUES (?,?,?,?,?)";
                //Create a PreparedStatment with that sql and insert the values with index:
                PreparedStatement preparedStatement3a = conn.prepareStatement(sqlproj3);
                preparedStatement3a.setString(1,pprb3.getBeanprojname());
                preparedStatement3a.setString(2,pprb3.getBeanprojcode());
                preparedStatement3a.setString(3,pprb3.getBeanprojlocation());
                preparedStatement3a.setString(4,pprb3.getBeanprojtype());
                preparedStatement3a.setString(5,pprb3.getBeanprojlastdate());
                String sqlreq3="INSERT INTO tblrequirements (code,designation,skillset,exp,ncand)"
                        + " VALUES (?,?,?,?,?)";
                 //Create a PreparedStatment with that sql and insert the values with index:
                PreparedStatement preparedStatement3b = conn.prepareStatement(sqlreq3);
                preparedStatement3b.setString(1,pprb3.getBeanprojcode());
                preparedStatement3b.setString(2,pprb3.getBeandesg());
                preparedStatement3b.setString(3,pprb3.getBeanskill());
                preparedStatement3b.setInt(4,pprb3.getBeanexp());
                preparedStatement3b.setInt(5,pprb3.getBeanncand());
                preparedStatement3a.executeUpdate();
                preparedStatement3b.executeUpdate();
                System.out.println("Data 3 inserted successfully!");

                preparedStatement1a.clearParameters();
                preparedStatement1b.clearParameters();
                preparedStatement2a.clearParameters();
                preparedStatement2b.clearParameters();
                preparedStatement3a.clearParameters();
                preparedStatement3b.clearParameters();

            }

        } catch (Exception e){

            out.println("Failed!"+ e);
            resstat=0;
        }

        if(resstat==1)
            return true;
        else
            return false;
    }

}

它正在执行前两个查询，但从第三个查询开始不执行。

Answer 1

我在这里看到一个潜在的问题:

            ...
            preparedStatement1a.executeUpdate(); 
            preparedStatement1a.clearParameters(); // you clear the parameters of
                                                   // preparedStatement1a
            ...
            preparedStatement1a.executeUpdate(); // then you try to execute it 
                                                 // again without parameters
            preparedStatement1b.executeUpdate();