Using a java class to create a database

Question

Hi,

I'm working in an application that uses servlets and mysql.

I'd like to create a .jar file able to create the database that the application will be using. This will only be done once, in order to create the db.

I've no problem in getting to access to a database, doing something like this:

Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conexion = (Connection)DriverManager.getConnection("jdbc:mysql://localhost/test","admin","admin");
if (!conexion.isClosed())
{   
   Statement st = (Statement) conexion.createStatement();
   ResultSet rs = st.executeQuery("select * from table_name" );
}
conexion.close();

This is ok, but what I need to do is to create a new database (and its tables) from a java class, is that possible?

Thanks

I'm trying this:

Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conexion = (Connection)DriverManager.getConnection("jdbc:mysql://localhost/mysql","admin","admin");

Statement st = (Statement) conexion.createStatement();       
st.executeUpdate("CREATE DATABASE hrapp");

but I'm getting the following error:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user 'admin'@'localhost' to database 'hrapp' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.Util.getInstance(Util.java:381) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1564) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1485) at BaseDatosSetup.BaseDatosSetup.main(BaseDatosSetup.java:18)

I solved it by granting the create action to the user. I don't know why, I was doing it as an administrator.

Answer 1

First, this problem isn't servlet-specific. You would struggle with exactly the same problem/question when doing/trying so in any other Java class ;)

Second, creating databases/tables programmatically honestly smells. In real world, with well-designed data models, you normally create the databases/tables only once during installing/setup of the application in question and then use it forever during application's lifetime. You shouldn't need to create a new DB/table for every hiccup. Unless you intend to develop (reinvent) a webbased database manager of course.

As to your actual question, this actually depends on the JDBC driver used, but most of them just allows executing CREATE or any other DDL statements using Statement#executeUpdate(). If yours don't, then you need to look for another JDBC driver make or version which allows that. You should also take into account that the DB user in question has sufficient rights, this is to be configured at DB level.

Answer 2

Why do you want to do this?

I think what you would have to do is perform some Runtime commands to interface with the OS directly to do accomplish this feat. However, I would recommend not doing it this way.

Answer 3

W3CSchools.com -- SQL CREATE DATABASE Statement. You wouldn't use executeQuery though. Instead use executeUpdate.

Here is a simple example.

As mentioned by other users, you probably don't want to be creating databases from your code. It just isn't good practice.