Creating tables in DB results in a lot of exceptions

Question

I'm fairly new to SQL and Java so bear with me.

I am trying to reach my DB, create some tables, set it to InnoDB and set some Foreign Keys. Everything compiles and looks okay, but I end up getting these errors (seems to be located in the JDBC lib):

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'Auditorium'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
    at com.mysql.jdbc.Util.getInstance(Util.java:360)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:848)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:742)
    at CreateDB.createTables(CreateDB.java:35)
    at Main.main(Main.java:11)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)

CreateDB.java class looks like this:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import static java.sql.DriverManager.registerDriver;


public class CreateDB {

static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String database_name = "<DBname>";
static final String DB_URL = "<server>" + "<DBname>";
static final String USER = "*****";
static final String PASS = "*****";
Statement statement = null;
Connection connection = null;


public CreateDB()
{
    try {
        registerDriver(new com.mysql.jdbc.Driver());
        connection = DriverManager.getConnection(DB_URL, USER, PASS);
        statement = connection.createStatement();
    } catch (Exception e) {
        e.printStackTrace(); // handle errors
    }
}


public void createTables() throws SQLException {
    statement.execute("DROP TABLE Auditorium");
    statement.execute("DROP TABLE Customer");
    statement.execute("DROP TABLE Screening");
    statement.execute("DROP TABLE Ticket");

    String Auditorium = "CREATE TABLE Auditorium "        //Sets name and size of each auditorium
            + "(ID INTEGER NOT NULL AUTO_INCREMENT, "    //Primary key for Auditorium
            + "Name VARCHAR(10), "                        //Name for specific Auditorium (example: 'aud1')
            + "Row INTEGER NOT NULL, "                    //Amount of rows in specific auditorium
            + "Column INTEGER NOT NULL, "                //Amount of columns in specific auditorium
            + "PRIMARY KEY (ID));";                        //Sets primary key (ID)

    String Screening = "CREATE TABLE Screening "        //Keeps track of what screenings we offer
            + "(ID INTEGER NOT NULL AUTO_INCREMENT, "    //Primary key for Screening
            + "Auditorium VARCHAR(10) NOT NULL, "        //Name for specific Auditorium (example: 'aud1')
            + "Movie VARCHAR(50) NOT NULL, "            //Movie title
            + "Date DATE NOT NULL, "                    //Specific date the movie is being shown
            + "Time TIME NOT NULL, "                    //What time the movie runs
            + "PRIMARY KEY (ID));";                        //Sets primary key (ID)

    String Customer = "CREATE TABLE Customer "            //Information on the customer
            + "(ID INTEGER NOT NULL AUTO_INCREMENT, "    //Primary key for Customer
            + "Name VARCHAR(50) NOT NULL, "                //Name on customer
            + "Phone INTEGER NOT NULL, "                //Phone on customer
            + "Email VARCHAR(50), "                        //Optional mail on customer
            + "PRIMARY KEY (ID);";                        //Sets primary key (ID)

    String Ticket = "CREATE TABLE Ticket "                //Ticket that customer can buy
            + "(ID INTEGER NOT NULL AUTO_INCREMENT, "    //Primary key for Ticket
            + "Row INTEGER NOT NULL, "                    //Specific row
            + "Seat INTEGER NOT NULL, "                    //Specific seat
            + "AudID INTEGER NOT NULL, "                //Foreign key references Auditorium(ID)
            + "ScreeningID INTEGER NOT NULL, "            //Foreign key references Screening(ID)
            + "PRIMARY KEY (ID);";                        //Sets primary key (ID)

    statement.executeUpdate(Auditorium);
    statement.executeUpdate(Screening);
    statement.executeUpdate(Customer);
    statement.executeUpdate(Ticket);
}

public void changeEngine() throws SQLException {
    String Auditorium = "ALTER TABLE Auditorium ENGINE=InnoDB;";
    String Screening = "ALTER TABLE Screening ENGINE=InnoDB;";
    String Customer = "ALTER TABLE Customer ENGINE=InnoDB;";
    String Ticket = "ALTER TABLE Ticket ENGINE=InnoDB;";
    statement.execute(Auditorium);
    statement.execute(Screening);
    statement.execute(Customer);
    statement.execute(Ticket);
}

public void applyForeignKeys() throws SQLException {
    String AudID = "ALTER TABLE Ticket ADD FOREIGN KEY (AudID) REFERENCES Auditorium(ID);";
    String ScreeningID = "ALTER TABLE Ticket ADD FOREIGN KEY (ScreeningID) REFERENCES Screening(ID);";
    statement.executeUpdate(AudID);
    statement.executeUpdate(ScreeningID);
}



}

My main method is very simply doing so:

import java.sql.SQLException;
public class Main {

public static void main(String[] args) throws SQLException {

    CreateDB c = new CreateDB();
    c.createTables();
    c.changeEngine();
    c.applyForeignKeys();

}
}

possible duplicate of [How do I escape reserved words used as column names? MySQL/Create Table](http://stackoverflow.com/questions/2889871/how-do-i-escape-reserved-words-used-as-column-names-mysql-create-table) — Tetsujin no Oni, Nov 28 '14 at 18:27

MihaiC · Answer 1 · 2014-11-28T15:23:19.377

I think the problem is that you call c.createTables() first in your main method and the tables don't exist.

If the tables don't exist then an error will be returned, such as table does not exits because you try to run a drop statement on non-existant tables.

Modify createTables() to not run DROP statements if the tables don't exist.

Since you are using mysql you can do this:

statement.execute("DROP TABLE IF EXISTS Auditorium");

For Oracle or other databases it would be different.

score 2 · Answer 2 · answered Nov 28 '14 at 15:16

The line in the stacktrace:

 at CreateDB.createTables(CreateDB.java:35)

Tells you the line in you code that the error occurs at.

My guess is that line 35 is:

statement.execute("DROP TABLE Auditorium");

You can't drop a table that doesn't exist.

Try:

statement.execute("DROP TABLE IF EXISTS Auditorium");

score 2 · Answer 3 · answered Nov 28 '14 at 15:37

You also forgot a ) at the end of the strings in the following statements:

String Customer = "CREATE TABLE Customer "            //Information on the customer
            + "(ID INTEGER NOT NULL AUTO_INCREMENT, "    //Primary key for Customer
            + "Name VARCHAR(50) NOT NULL, "                //Name on customer
            + "Phone INTEGER NOT NULL, "                //Phone on customer
            + "Email VARCHAR(50), "                        //Optional mail on customer
            + "PRIMARY KEY (ID);";                        //Sets primary key (ID)

    String Ticket = "CREATE TABLE Ticket "                //Ticket that customer can buy
            + "(ID INTEGER NOT NULL AUTO_INCREMENT, "    //Primary key for Ticket
            + "Row INTEGER NOT NULL, "                    //Specific row
            + "Seat INTEGER NOT NULL, "                    //Specific seat
            + "AudID INTEGER NOT NULL, "                //Foreign key references Auditorium(ID)
            + "ScreeningID INTEGER NOT NULL, "            //Foreign key references Screening(ID)
            + "PRIMARY KEY (ID);";

score 1 · Answer 4 · answered Nov 28 '14 at 15:15

1

statement.execute("DROP TABLE IF EXISTS Auditorium");

answered Nov 28 '14 at 15:15

luuksen

1,357
2
12
38

you were faster than me... please update your answer with more details, explaining why to use the `if exists` – fmodos Nov 28 '14 at 15:16

score 0 · Answer 5 · edited Nov 28 '14 at 18:26

0

In most database engines, there are reserved words which must be marked up in a particular fashion to be used as object names (i.e. table or field names). In MySql, row and column are reserved words.

Enclosing the column name in backticks will allow a keyword to be used as a column name.

edited Nov 28 '14 at 18:26

Tetsujin no Oni

7,300
2
29
46

answered Nov 28 '14 at 15:40

Aphex

408
1
5
17

This was posted as an answer, but it does not attempt to answer the question. It should possibly be an edit, a comment, another question, or deleted altogether. – Mark Rotteveel Nov 28 '14 at 16:07
1

yes, there are several keywords which can't be used as column names. the same applies to other databases such as Oracle. – MihaiC Nov 28 '14 at 16:24

Creating tables in DB results in a lot of exceptions

5 Answers5