Inserting into multiple tables using a single query in preparestatement

Question

This is my code

String query = "insert into Branch (CompanyID,BranchName,BranchAddress,Email,Phone,ContactPerson) values (?,?,?,?,?,?);insert into Activities(EmployeeID,ActivityType,Activities) values (?,?,?)";

            // System.out.println(mobile);//BranchName      | BranchAddress      | Email      | Phone      | ContactPerson

            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, compid);
            pstmt.setString(2, name);
            pstmt.setString(3, address);
            pstmt.setString(4, email);
            pstmt.setString(5, mobile);
            pstmt.setString(6, contactperson);
            pstmt.setString(7, empid);
            pstmt.setString(8, acttype);
            pstmt.setString(9, activity);
            System.out.println(query+"-----"+acttype);
            pstmt.execute();
            status = true;

If I insert without dynamic values it gets inserted otherwise I get the below error

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into Activities(EmployeeID,ActivityType,Activities) values ('6','createbr' at line 1
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into Activities(EmployeeID,ActivityType,Activities) values ('6','createbr' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155).........

How can I overcome this ??? I am able to insert using this statement below

insert into Branch (CompanyID,BranchName,BranchAddress,Email,Phone,ContactPerson) values ('1','sample','11 cross','fakemail@gmail.com','5467897656','Deigo');insert into Activities(EmployeeID,ActivityType,Activities) values ('6','createdbranch','cearted branch sample');

But I want it to be dynamic.Please help

One way to overcome this is to run two separate insert statements using separate preparedstatements. Any reason you want them in one? You could get the same effect with two statement in a transaction. I haven't found any documentation that you can do two parameterized insert statements in a single statement. — Sam M, Apr 13 '16 at 06:16
@SamM - Is it a good practice to open and close two preparedstatement? — Raja Dhasan, Apr 13 '16 at 06:17
@SamM - Is it a good practice to open and close two preparedstatement? — Raja Dhasan, Apr 13 '16 at 06:18
yes, it is standard practice to use multiple preparedstatements. Remember to close it in between calls. And you can wrap them in a single database transaction if you need to. — Sam M, Apr 13 '16 at 13:52

score 2 · Accepted Answer · answered Apr 13 '16 at 06:19

2

allowMultiQueries=true&rewriteBatchedStatements=true needs to be added to the connection params to allow execution of multiple queries.

For example, the url may be like:

jdbc:mysql://xxx.xxx.xxx.xxx:nnnnn/db_name?allowMultiQueries=true&rewriteBatchedStatements=true&user=xxxx&password=yyyy

answered Apr 13 '16 at 06:19

Dylan Su

5,975
1
16
25

1

- Thanks it worked ..but I did not add rewriteBatchedStatements=true, will it affect any thing? – Raja Dhasan Apr 13 '16 at 06:55
1

It is fine. `rewriteBatchedStatements` sometimes may be faster. But for your case, I think there is no difference. – Dylan Su Apr 13 '16 at 06:58

Inserting into multiple tables using a single query in preparestatement

1 Answers1