Multiple queries executed in java in single statement
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
, andDML
queries. - Call it from java using
CallableStatement
. - You can capture multiple
ResultSet
s executed in procedure.
DML results can't be captured but can issue anotherselect
to find how the rows are affected in the table.
Sample table and procedure:
mysql> 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> delimiter //
mysql> create procedure multi_query()
-> begin
-> select count(*) as name_count from tbl_mq;
-> insert into tbl_mq( names ) values ( 'ravi' );
-> select last_insert_id();
-> select * from tbl_mq;
-> end;
-> //
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> 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
How to run multiple queries in single statement
That is a multiple queries.
so you need a connection string that allows that
String dbUrl = "jdbc:mysql:///test?allowMultiQueries=true";
Further remove all \ n and as i said in the comment replace "
String sql = "START TRANSACTION;" +
"SELECT placed_money FROM bank_accounts WHERE uuid = '" + uuid + "';" +
"UPDATE bank_accounts SET placed_money = 0 WHERE uuid = '" + uuid + "';" +
"COMMIT;";
How to execute multiple SQL statements from java
you can achieve that using Following example uses addBatch & executeBatch commands to execute multiple SQL commands simultaneously.
Batch Processing allows you to group related SQL statements into a batch and submit them with one call to the database. reference
When you send several SQL statements to the database at once, you reduce the amount of communication overhead, thereby improving performance.
- JDBC drivers are not required to support this feature. You should use the
DatabaseMetaData.supportsBatchUpdates()
method to determine if the target database supports batch update processing. The method returns true if your JDBC driver supports this feature. - The addBatch() method of Statement, PreparedStatement, and CallableStatement is used to add individual statements to the batch. The
executeBatch()
is used to start the execution of all the statements grouped together. - The executeBatch() returns an array of integers, and each element of the array represents the update count for the respective update statement.
- Just as you can add statements to a batch for processing, you can remove them with the clearBatch() method. This method removes all the statements you added with the
addBatch()
method. However, you cannot selectively choose which statement to remove.
EXAMPLE:
import java.sql.*;
public class jdbcConn {
public static void main(String[] args) throws Exception{
Class.forName("org.apache.derby.jdbc.ClientDriver");
Connection con = DriverManager.getConnection
("jdbc:derby://localhost:1527/testDb","name","pass");
Statement stmt = con.createStatement
(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
String insertEmp1 = "insert into emp values
(10,'jay','trainee')";
String insertEmp2 = "insert into emp values
(11,'jayes','trainee')";
String insertEmp3 = "insert into emp values
(12,'shail','trainee')";
con.setAutoCommit(false);
stmt.addBatch(insertEmp1);//inserting Query in stmt
stmt.addBatch(insertEmp2);
stmt.addBatch(insertEmp3);
ResultSet rs = stmt.executeQuery("select * from emp");
rs.last();
System.out.println("rows before batch execution= "
+ rs.getRow());
stmt.executeBatch();
con.commit();
System.out.println("Batch executed");
rs = stmt.executeQuery("select * from emp");
rs.last();
System.out.println("rows after batch execution= "
+ rs.getRow());
}
}
refer http://www.tutorialspoint.com/javaexamples/jdbc_executebatch.htm
Execute multiple SQL statements in java
Following example uses addBatch
& executeBatch
commands to execute multiple SQL commands simultaneously.
import java.sql.*;
public class jdbcConn {
public static void main(String[] args) throws Exception{
Class.forName("org.apache.derby.jdbc.ClientDriver");
Connection con = DriverManager.getConnection
("jdbc:derby://localhost:1527/testDb","name","pass");
Statement stmt = con.createStatement
(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
String insertEmp1 = "insert into emp values
(10,'jay','trainee')";
String insertEmp2 = "insert into emp values
(11,'jayes','trainee')";
String insertEmp3 = "insert into emp values
(12,'shail','trainee')";
con.setAutoCommit(false);
stmt.addBatch(insertEmp1);
stmt.addBatch(insertEmp2);
stmt.addBatch(insertEmp3);
ResultSet rs = stmt.executeQuery("select * from emp");
rs.last();
System.out.println("rows before batch execution= "
+ rs.getRow());
stmt.executeBatch();
con.commit();
System.out.println("Batch executed");
rs = stmt.executeQuery("select * from emp");
rs.last();
System.out.println("rows after batch execution= "
+ rs.getRow());
}
}
Result:
The above code sample will produce the following result.The result may vary.
rows before batch execution= 6
Batch executed
rows after batch execution= = 9
Source: Execute multiple SQL statements
using same statement object to execute multiple queries in multiple threads?
No, it's definitely not a good idea. For one, ResultSets
are associated with a Statement
. You don't want to be iterating through a ResultSet
when another thread suddenly makes a new query (the ResultSet
would be closed, at least if the driver is a well behaved one).
There is also nowhere an indication that Statements
would be thread-safe. If it's not clearly indicated, it's not thread-safe (at least in most cases).
Related Topics
Converting Exponential Value in Java to a Number Format
Calculating the Angle Between Two Lines Without Having to Calculate the Slope (Java)
Splitting String With Parentheses
How to Loop Through List of Webelements and Select One Webelement With a Condition
Several Ports (8005, 8080, 8009) Required by Tomcat Server At Localhost Are Already in Use
Java Spring @Scheduled Tasks Executing Twice
Spring Boot Required Request Part 'File' Is Not Present
How to Select One Object from the List of Objects
Check If Two Objects Are Equal Excluding a Few Properties
Add Additional Rules to the Compare Method of a Comparator
How to Resolve Java.Lang.Noclassdeffounderror: Javax/Xml/Bind/Jaxbexception
Converting Date to 08:00:00.000+0000
Error: Could Not Find or Load Main Class in Intellij Ide
How to Show Alert in a Jsp from a Servlet and Then Redirect to Another Jsp
How to Delete the Content of Text File Without Deleting Itself
How to Get Access to Job Parameters from Itemreader, in Spring Batch