0

I try to create a database and a table. If I do it in two statements/connections it works fine:

String createDb = "CREATE DATABASE IF NOT EXISTS XYZ; ";
statement.executeUpdate(createDb);

and then

String createTable = "CREATE TABLE IF NOT EXISTS XYZ.Teachers ( idTeacher SMALLINT(3) );";
// new connection
statement.executeUpdate(createTable);

But if I try to put the two commands together to create a database and table with a single statement/connection I get this exception:

String createDbAndTable = "CREATE DATABASE IF NOT EXISTS XYZ; " + 
"CREATE TABLE IF NOT EXISTS XYZ.Teachers ( idTeacher SMALLINT(3) );";

statement.executeUpdate(createTable);
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 'CREATE TABLE IF NOT EXISTS XYZ.Teachers ( idTeacher SMALLINT(3) )' at 
line 1

I guess, it is not possible to put two sql commands togeher like this. Is there some other way of doing it with a single statement/connection, anyway?

Improve this question
9
  • Possible duplicate of How to execute multiple SQL statements from java Commented Nov 23, 2016 at 10:54
  • Please use google next time. Googling for 'Java execute multiple queries' returned a lot Commented Nov 23, 2016 at 10:55
  • 2
    You should be able to do this within a transaction, but, playing the devil's advocate, are you sure you want that? By using separate transactions you can handle failures, rollbacks, etc. better. Commented Nov 23, 2016 at 10:56
  • 1
    You can use addBatch() method. Commented Nov 23, 2016 at 11:36
  • 1
    a way to do it is putting all the creation logic inside a procedure and then call it passing the table name, and what else you want to, as parameter to it. Commented Nov 23, 2016 at 11:45
Related questions
3
MySQL set a variable and run a query in the same statement/script
0
How to use java to execute some 'source' commands in mysql?
126
Multiple queries executed in java in single statement
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.