Multiple queries executed in java in single statement

Question

Hi I was wondering if it is possible to execute something like this using JDBC as it currently provides an exception even though it is possible in the MySQL query browser    SELECT FROM   TABLE INSERT INTO TABLE     While I do realize that it is possible with having the SQL query string being split and the statement executed twice but I was wondering if there is a one time approach for this       String url    jdbc mysql   localhost 3306        String dbName    databaseinjection       String driver    com mysql jdbc Driver       String sqlUsername    root        String sqlPassword    abc        Class forName driver  newInstance         connection   DriverManager getConnection url dbName  sqlUsername  sqlPassword

User · Answer

Why dont you try and write a Stored Procedure for this   You can get the Result Set out and in the same Stored Procedure you can Insert what you want   The only thing is you might not get the newly inserted rows in the Result Set if you Insert after the Select

User · Answer

Hint  If you have more than one connection property then separate them with    amp amp    To give you somthing like   url  jdbc mysql   localhost glyndwr autoReconnect true amp amp allowMultiQueries true    I hope this helps some one   Regards   Glyn

User · Answer

You can use Batch update but queries must be action i e  insert update and delete  queries   Statement s   c createStatement    String s1    update emp set name  abc  where salary 984   String s2    insert into emp values   Osama  1420      s addBatch s1   s addBatch s2        s executeBatch

User · Answer

I think this is the easiest way for multy selection update insert delete  You can run as many update insert delete as u want after select  you have to make a select first a dummy if needed   with executeUpdate str   just use new int count1 count2       and if u need a new selection close  statement  and  connection  and make new for next select  Like example   String str1    select   from users   String str9    INSERT INTO  port  device id  potition  port type  di p pt  VALUE     value1        value2        value3        value4       String str2    Select port id from port where device id      value1    and potition      value2    and port type      value3       try        Class forName  com mysql jdbc Driver   newInstance        theConnection  Connection  DriverManager getConnection dbURL dbuser dbpassword         theStatement   theConnection prepareStatement str1       ResultSet theResult   theStatement executeQuery        int count8   theStatement executeUpdate str9       theStatement close        theConnection close        theConnection DriverManager getConnection dbURL dbuser dbpassword       theStatement   theConnection prepareStatement str2       theResult   theStatement executeQuery         ArrayList lt Port gt  portList   new ArrayList lt Port gt         while  theResult next              Port port   new Port            port setPort id theResult getInt  port id              portList add port           I hope it helps

User · Answer

Based on my testing  the correct flag is  allowMultiQueries true

User · Answer

I was wondering if it is possible to execute something like this using JDBC    SELECT FROM   TABLE INSERT INTO TABLE     Yes it is possible  There are two ways  as far as I know  They are   By setting database connection property to allow multiple queries  separated by a semi-colon by default  By calling a stored procedure that returns cursors implicit    Following examples demonstrate the above two possibilities     Example 1    To allow multiple queries       While sending a connection request  you need to append a connection property allowMultiQueries true to the database url  This is additional connection property to those if already exists some  like autoReConnect true  etc   Acceptable values for allowMultiQueries property are true  false  yes  and no  Any other value is rejected at runtime with an SQLException     String dbUrl    jdbc mysql    test allowMultiQueries true       Unless such instruction is passed  an SQLException is thrown   You have to use execute  String sql   or its other variants to fetch results of the query execution     boolean hasMoreResultSets   stmt execute  multiQuerySqlString      To iterate through and process results you require following steps     READING QUERY RESULTS     label       while   hasMoreResultSets    stmt getUpdateCount      -1               if   hasMoreResultSets                   Resultset rs   stmt getResultSet                   handle your rs here              if has rs         else      if ddl dml                 int queryResult   stmt getUpdateCount                  if   queryResult    -1        no more queries processed                   break READING QUERY RESULTS                     no more queries processed                  handle success  failure  generated keys  etc here              if ddl dml                 check to continue in the loop           hasMoreResultSets   stmt getMoreResults               while results   Example 2  Steps to follow      Create a procedure with one or more select  and DML queries  Call it from java using CallableStatement  You can capture multiple ResultSets executed in procedure  DML results can t be captured but can issue another select to find how the rows are affected in the table       Sample table and procedure     mysql gt  create table tbl mq  i int not null auto increment  name varchar 10   primary key  i     Query OK  0 rows affected  0 16 sec   mysql gt  delimiter    mysql gt  create procedure multi query       - gt  begin     - gt   select count    as name count from tbl mq      - gt   insert into tbl mq  names   values    ravi         - gt   select last insert id        - gt   select   from tbl mq      - gt  end      - gt     Query OK  0 rows affected  0 02 sec  mysql gt  delimiter   mysql gt  call multi query     ------------    name count    ------------             0    ------------  1 row in set  0 00 sec    ------------------    last insert id      ------------------                   3    ------------------  1 row in set  0 00 sec    --- ------    i   name    --- ------    1   ravi    --- ------  1 row in set  0 00 sec   Query OK  0 rows affected  0 00 sec    Call Procedure from Java     CallableStatement cstmt   con prepareCall   call multi query         boolean hasMoreResultSets   cstmt execute      READING QUERY RESULTS        while   hasMoreResultSets               Resultset rs   stmt getResultSet               handle your rs here          while has more rs

[java] Multiple queries executed in java in single statement

Examples related to java

Examples related to mysql

Examples related to sql

Examples related to jdbc