4

I am trying to get Java to create a database using JDBC but I get a syntax error, despite the query being correct. If I write the name of a database into the code explicitly, for example, it works fine. Here's my code:

package mysql_manipulator;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Create {
    private static final String 
            driver = "com.mysql.jdbc.Driver",
            url = "jdbc:mysql://localhost",
            username = "dylan",
            password = "******";
    private String databaseName;

    public Create (){
        this.databaseName = null;
    }

    public String getDatabaseName() {
        return databaseName;
    }

    public void setDatabaseName(String databaseName) {
        this.databaseName = databaseName;
    }

    public void createDatabase(String databaseName){
        try {
            this.setDatabaseName(databaseName);        
            Class.forName(driver);
            Connection con = DriverManager.getConnection(url,username, 
                password);
            String query = "CREATE DATABASE ?";
            PreparedStatement preparedStmt = con.prepareStatement(query);
            preparedStmt.setString (1,this.getDatabaseName());
            preparedStmt.execute();
            preparedStmt.close();
            con.close();        
        }
        catch (Exception anException){
            System.out.println("Error: " + anException);
        }
    }
}

My Main Class 

package mysql_manipulator;

public class Mysql_manipulator {
    public static void main(String[] args) {
        Create myObj = new Create();
        myObj.createDatabase("whatevs");        
    }    
}

The error 

Error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You  have an error in
your SQL syntax; check the manual that corresponds to your  MariaDB server version 
for the right syntax to use near ''whatevs'' at line 1

Huge thanks to anyone who knows why this isn't working - I've searched all over the web to no avail.

Madhawa Priyashantha
  • 9,633
  • 7
  • 33
  • 60
Dylan White
  • 65
  • 1
  • 5
  • 4
    You cannot have the database name as a bind variable -- you have to embed it in the query literal: `String query = "CREATE DATABASE " + getDatabaseName()`; You can also use a plain `Statement` for running this SQL. – Mick Mnemonic Aug 01 '15 at 15:04
  • Awesome - huge thanks Mick. – Dylan White Aug 01 '15 at 15:09

2 Answers2

7

You cannot have the database name as a bind variable -- you have to embed it in the query literal. Change your query into this instead:

String query = "CREATE DATABASE " + getDatabaseName();

You can also use a plain Statement (instead of PreparedStatement) for running this SQL.

Mick Mnemonic
  • 7,808
  • 2
  • 26
  • 30
  • Cheers Mick - don't know why I didn't work this out myself but you're totally correct. – Dylan White Aug 01 '15 at 15:11
  • could someone explain why we can't use PreparedStatement to create database? Is there any particular reason to disallow it? – itwasntme Aug 18 '16 at 11:06
  • 1
    @itwasntme, prepared statements are meant to be used for SQL operations that are frequently executed during the application's lifetime. Creating a database, or any DDL (data definition language) operations should only be executed during installation or upgrade, so there is not much use for "preparing" the statement for later use. Additionally, these operations shouldn't normally take parameters from user input, so protection from SQL injection isn't required, either. – Mick Mnemonic Aug 18 '16 at 19:07
0
Conn = DriverManager.getConnection 
("jdbc:mysql://localhost/?user=root&password=rootpassword"); 
Statement s=Conn.createStatement(); 

int Result=s.executeUpdate("CREATE DATABASE databasename");

see

http://forums.mysql.com/read.php?39,99321,102211#msg-102211

Create MySQL database from Java

Community
  • 1
  • 1
M Sach
  • 33,416
  • 76
  • 221
  • 314