Friday, October 29, 2010

create a new database foo in MySQL using JDBC

Usually when people work with JDBC, they need a Connection object to the destination database, but since we need to create a new database, where to find an existing connection?

Setup: MySQL v4.1.9 + mysql-connector-java v3.1.6 + J2SE v1.4.2_06

Solution 1: Create a Connection to “mysql” admin database and use it to create the new database.

There are two preloaded databases when you install MySQL: mysql and test. mysql is the admin database that keeps metadata like access control information. We can just create a Connection object to mysql admin database and use it to create another new database.

Solution 2: Use Runtime.exec(command) to call mysql command line client.

Solution 3: Create a directory with the same name as the new database name in MySQL data directory, e.g., C:\Program Files\MySQL\MySQL Server 4.1\data. And MySQL server will “think” a new database is created. This method might have risk of corrupted metadata although I have tried this method before and no abnormal behavior was observed.