creating a new mysql table using JAVA

Question

I'm trying to create a table similar to a ready table I created before (a template, if you will) where the only variable should be the table name.

This is what I've tried so far: I exported the template table to mysql code and copied the code to a preparedStatement object as such:

createNewLineTableStatement = constantLink.prepareStatement("CREATE TABLE IF NOT EXISTS ? (" +
                    "  `index` int(5) NOT NULL," +
                    "  `station` int(5) NOT NULL," +
                    "  PRIMARY KEY (`index`)," +
                    "  UNIQUE KEY `station` (`station`)" +
                    ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\");");

Than I try to execute the code by calling the following function:

private static boolean createNewLineTable(String tableName) throws SQLException{
    createNewLineTableStatement.setString(1, tableName);
    if (createNewLineTableStatement.executeUpdate() == Statement.EXECUTE_FAILED)
        return false;
    return true;
}

But I'm getting a syntax error exception:

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 ''line_37_var_1' (  `index` int(5) NOT NULL,  `station` int(5) NOT NULL,  PRIMARY' at line 1

How can I fix the code? OR is there a cleaner, better way to do the same thing? Maybe creating a script with a user variable? I thought of that but I've never used .sql script before.

are you sure you can use the column with name "index". Thought it is keyword. — Satish
– Satish, Commented Jan 18, 2013 at 13:01
Yes, I'm sure. I originally created the template table using phpmyadmin and it was created without a hitch. — Tom Klino
– Tom Klino, Commented Jan 18, 2013 at 13:04

jtahlborn · Accepted Answer · 2013-01-18 14:46:34Z

2

Problem 1: You can't use a prepared statement parameter as the table name.

Problem 2: You have an unmatched paren and extra characters ");at the end of your statement.

Your query string should look something like:

String query = "CREATE TABLE IF NOT EXISTS `" + tableName + "` (" +
    "  `index` int(5) NOT NULL," +
    "  `station` int(5) NOT NULL," +
    "  PRIMARY KEY (`index`)," +
    "  UNIQUE KEY `station` (`station`)" +
    ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

edited Jan 18, 2013 at 14:46

answered Jan 18, 2013 at 13:05

jtahlborn

53.8k5 gold badges80 silver badges122 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Woo · Accepted Answer · 2013-01-18 13:04:43Z

0

by design, TableName and ColumnNames cannot be parameterized.

If you are scared about SQL Injection, create a custom function to check for malicious tableName. It is safe if the value comes inside of your application.

Then concatenate it in the string, add backtick for first level of defense :D

String tableName = "Your tableName";
String query = "CREATE TABLE IF NOT EXISTS `" + tableName + "` (" +
                    "  `index` int(5) NOT NULL," +
                    "  `station` int(5) NOT NULL," +
                    "  PRIMARY KEY (`index`)," +
                    "  UNIQUE KEY `station` (`station`)" +
                    ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

answered Jan 18, 2013 at 13:04

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

Leandro Temperoni · Accepted Answer · 2013-01-18 13:07:34Z

0

You are missing Table name and i think that "?" shouldn't be there.

I will be something like

"CREATE TABLE IF NOT EXISTS YOURTABLE" + the following code

answered Jan 18, 2013 at 13:07

Leandro Temperoni

4355 silver badges13 bronze badges

Collectives™ on Stack Overflow

creating a new mysql table using JAVA

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related