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?

enhancedJack
  • 265
  • 1
  • 6
  • 15
  • Possible duplicate of [How to execute multiple SQL statements from java](http://stackoverflow.com/questions/10929369/how-to-execute-multiple-sql-statements-from-java) – Ivar Reukers Nov 23 '16 at 10:54
  • Please use google next time. Googling for 'Java execute multiple queries' returned a lot – Ivar Reukers Nov 23 '16 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. – Tim Biegeleisen Nov 23 '16 at 10:56
  • where is ur `use database` ? – KikiTheOne Nov 23 '16 at 10:59
  • 1
    You can use addBatch() method. – ROOT Nov 23 '16 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. – VeryNiceArgumentException Nov 23 '16 at 11:45
  • @Tim What I'm actually trying to do is to initialize a database in a simple project using JDBC and MySQL. I though of putting the sql DDL code in a file and then loading it and creating the databse in a single execute. I'm not sure if this is the right way of initializing a database, anyway... – enhancedJack Nov 25 '16 at 10:32
  • @enhancedJack For such SQL statements that you reasonably expect to succeed then maybe a transaction is OK. – Tim Biegeleisen Nov 25 '16 at 10:36
  • @Ivaro Sometimes the problem is with phrasing what one has in mind. I spent 3 hours trying to find something before posting on stackoverflow. I guess I could never come up with 'Java execute multiple queries' because a DDL statements are not queries. Correct me if I'm wrong. – enhancedJack Nov 25 '16 at 10:41

0 Answers0