java - 无法使用数据源连接执行多个 SQL 查询

Question

我尝试执行多个查询，但只执行了部分查询。

这是我的代码:

public class PostPrReqDaoImpl implements PostPrReqDaoInterface {

    @Override
    public boolean validate(PostPrReqBean pprb1,PostPrReqBean pprb2,PostPrReqBean pprb3,PostPrReqBean pprb4) {
        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");



        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 
            Connection conn=ds.getConnection();
            System.out.println("Connection succesful!");
            Statement s1= conn.createStatement();
            Statement s2= conn.createStatement();
            Statement s3= conn.createStatement();
            Statement s4= conn.createStatement();

            if(resstat == 1)
            {

                String sqlproj = "INSERT INTO tblproject (name,code,location,type,lastdate) " +
                        "VALUES ('" + pprb1.getBeanprojname() + "' , "
                        + "'" + pprb1.getBeanprojcode() +"' ,"
                        + "'" + pprb1.getBeanprojlocation() +"' ,"
                        + "'" + pprb1.getBeanprojtype() +"' ,"
                        + "'" + pprb1.getBeanprojlastdate() +"')";

                String sqlreq1="INSERT INTO tblrequirements (code,designation,skillset,exp,ncand)" +
                        "VALUES ('" + pprb1.getBeanprojcode() + "' , "
                        + "'" + pprb2.getBeandesg() +"' ,"
                        + "'" + pprb2.getBeanskill() +"' ,"
                        + "'" + pprb2.getBeanexp() +"' ,"
                        + "'" + pprb2.getBeanncand() +"')";

                String sqlreq2="INSERT INTO tblrequirements (code,designation,skillset,exp,ncand)" +
                        "VALUES ('" + pprb1.getBeanprojcode() + "' , "
                        + "'" + pprb3.getBeandesg() +"' ,"
                        + "'" + pprb3.getBeanskill() +"' ,"
                        + "'" + pprb3.getBeanexp() +"' ,"
                        + "'" + pprb3.getBeanncand() +"')";

                String sqlreq3="INSERT INTO tblrequirements (code,designation,skillset,exp,ncand)" +
                        "VALUES ('" + pprb1.getBeanprojcode() + "' , "
                        + "'" + pprb4.getBeandesg() +"' ,"
                        + "'" + pprb4.getBeanskill() +"' ,"
                        + "'" + pprb4.getBeanexp() +"' ,"
                        + "'" + pprb4.getBeanncand() +"')";


                s1.executeUpdate(sqlproj);
                System.out.println("Proj updation successful!");
                s2.executeUpdate(sqlreq1);
                System.out.println("Req1 updation successful!");
                s3.executeUpdate(sqlreq2);
                System.out.println("Req2 updation successful!");
                s4.executeUpdate(sqlreq3);
                System.out.println("Req3 updation successful!");
            }

        } catch (Exception e){

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

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

}

仅执行前 2 个查询(即控制台 o/p 为:项目更新成功!Req1 更新成功!");

这是我的两个表结构

Table tblproject:

Field        Type           Null     Key
-----------------------------------------
name        varchar(100)    YES         
code        varchar(100)    NO       PRI        
location    varchar(100)    YES         
type        varchar(100)    YES         
lastdate    varchar(11)     YES     

Table tblrequirements:

Field        Type           Null     Key
-----------------------------------------
code         varchar(100)    NO         
designation  varchar(100)   YES         
skillset     varchar(100)   YES         
exp          int(11)         NO         
ncand        int(11)         NO         
slno         varchar(45)     NO       PRI       

我哪里出错了？

Answer 1

请尝试批量执行多个插入语句。下面给出的示例

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

        pstmt.setString(1, "some value 1");
        pstmt.setString(2, "other value 1");
        pstmt.addBatch();

        pstmt.setString(1, "some value 2");
        pstmt.setString(2, "other value 2");
        pstmt.addBatch();

        pstmt.setString(1, "some value 3");
        pstmt.setString(2, "other value 3");
        pstmt.addBatch();

        pstmt.executeBatch();

在您的代码中，SQL 语句将无缘无故地被多次评估。也不是真正可读且简单。