Create MySQL database from Java

Question

Is it possible to create a MySQL database from Java?

I have only seen connection URLs examples like this where the database name is specified in the URL:

String url="jdbc:mysql://localhost:3306/test";

Connection con = DriverManager.getConnection( url, "cb0", "xxx" );

How can I create a MySQL database when I only have a login name and password?

Answer 1

The database isn't required in the jdbc connection, so you can do something like recommended at http://forums.mysql.com/read.php?39,99321,102211#msg-102211 and http://marc.info/?l=mysql-java&m=104508605511590&w=2:

Conn = DriverManager.getConnection
("jdbc:mysql://localhost/?user=root&password=rootpassword"); 
s=Conn.createStatement();
int Result=s.executeUpdate("CREATE DATABASE databasename");

Answer 2

To create database through Java code, you must use executeUpdate(sql) instead of executeQuery(sql); and connect to the mysql database as root:

connection =  DriverManager.getConnection(
    "jdbc:mysql://localhost:3306/mysql?zeroDateTimeBehavior=convertToNull",
    "root", "root"
);
Statement st = connection.createStatement();
st.executeUpdate(sql);
st.close();

Answer 3

An elegant approach to such issues is using DDL Utils from Apache. Not only would it serve the basic purpose of allowing to execute your (externally configurable) DDLs, but also would make your application database independent.

Answer 4

You can use this lines:

    try {
        String databaseName = "dbName";
        String userName = "root";
        String password = "yourPassword";

        String url = "jdbc:mysql://localhost:3306/mysql?zeroDateTimeBehavior=convertToNull";
        Connection connection = DriverManager.getConnection(url,username, password);

        String sql = "CREATE DATABASE " + databaseName;

        Statement statement = connection.createStatement();
        statement.executeUpdate(sql);
        statement.close();
        JOptionPane.showMessageDialog(null, databaseName + " Database has been created successfully", "System Message", JOptionPane.INFORMATION_MESSAGE);

    } catch (Exception e) {
        e.printStackTrace();
}

Answer 5

To make things even easier, you can use NetBeans 6.5 and it makes setting up SQL databases SO much easier. I'm using them right now and its a lifesaver on GUI layouts and database connections. Here's some code on how I connect to mysql database from NetBeans:

    //these are variables i declare in the beginning of my code
    public static final String DRIVER = "com.mysql.jdbc.Driver";
    public static final String DATABASE_URL = "jdbc:mysql://localhost:3306/jtschema";
    private Connection connection = null;
    public static Statement statement = null;

    public void initSQLServer() {
        try {
            Class.forName(DRIVER).newInstance();
            try {
                connection = DriverManager.getConnection(DATABASE_URL, "root", "Dropatrain!248");
                statement = connection.createStatement();
            } catch (SQLException e) {
                System.out.println("SQLException: " + e.getMessage());
                System.out.println("SQLState: " + e.getSQLState());
                System.out.println("VendorError: " + e.getErrorCode());
            }
        } catch (Exception ex) {
            System.out.println(ex);
        }
    }