My jdbc driver for mysql db is of version 5.1.25.
I want to execute sql query like so:
statement.execute("select fullName from user where user_id=1; select fullName from user where user_id=2");
And I always receive exception:
Exception in thread "main" 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 'select fullName from user where user_id=2' 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:525)
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:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2809)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2758)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:894)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:732)
at dbViewer.model.UserConnectionManager.retrieveRoutinesNames1(UserConnectionManager.java:622)
at dbViewer.model.UserConnectionManager.main(UserConnectionManager.java:637)
BUT when I run this same query(separated by semicolon) from command line it works perfectly and outputs two tables as expected.
Multiple statements or multi queries must be executed with mysqli::multi_query(). The individual statements of the statement string are separated by semicolon. Then, all result sets returned by the executed statements must be fetched.
You can include multiple SQL statements on the SQL query panel. The exceptions are CALL and CREATE PROCEDURE statements.
mysql determines where your statement ends by looking for the terminating semicolon, not by looking for the end of the input line. (In other words, mysql accepts free-format input: it collects input lines but does not execute them until it sees the semicolon.)
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.
I want to execute sql query like so:
statement.execute("select fullName from user where user_id=1; select fullName from user where user_id=2");
This is possible only when you have set one database connection property to allow multiple queries to execute all at once. And the property name is allowMultiQueries=true
. This property has to be set and send along with a database connection request to the server. General syntax is like this:
String dbUrl = "jdbc:mysql:///test?allowMultiQueries=true";
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
.
You have to use execute( String sql )
or its other variants to fetch results of the query execution.
multiQuerySqlString = "select fullName from user where user_id=1; ";
multiQuerySqlString += "select fullName from user where user_id=2; ";
// you can multiple types of result sets
multiQuerySqlString += "select last_login from user_logs where user_id=1; ";
boolean hasMoreResultSets = stmt.execute( multiQuerySqlString );
To iterate through and process results you require following steps:
int rsNumber = 0;
while ( hasMoreResultSets ) {
rsNumber += 1;
Resultset rs = stmt.getResultSet();
// based on the structure of the result set,
// you can handle column values.
if ( rsNumber == 1 ) {
while( rs.next() ) {
// handle your rs here
} // while rs
} // if rs is 1
else if ( rsNumber == 2 ) {
// call a method using this rs.
processMyResultSet( rs ); // example
} // if rs is 2
// ... etc
// check whether there exist more result sets
hasMoreResultSets = stmt.getMoreResults();
} // while results
Refer to:
Using ;
in a query for most databases doesn't work as it is usually not part of the statement syntax itself, but a terminator for command line or script input to separate statements. The command line or script processor sees a semi-colon as the signal that the statement is complete and can be sent to the server.
Also in JDBC a single statement prepare (or execute) should only be one actual statement so multiple statements are not allowed and so there is also no need to have a semi-colon, and as for some (most?) databases the semi-colon isn't part of the statement syntax, it is simply a syntax error to have one included.
If you want to execute multiple statements, you need to use separate executes. Technically, MySQL does have an option to support multiple executions which can be enabled by a connection property. This behavior is not compliant with the JDBC specification/API and makes your code less portable. See allowMultiQueries
on Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With