Lubos Programming Blog

Lubos Blog about Programming (mainly Java) and Computers

Creating database schema using JDBC

Posted by lubosp on July 28, 2009

Unfortunately there is no database agnostic way to create database schema in Java using ORM tools like Hibernate, and even JDBC doesn’t offer database agnostic way to create new database schema.

Here is a JDBC code I use to create new database schema for MySql and PostgreSql:

Statement stmt = null;
Connection con = null;
try {
String url = configuration.getProperty(“hibernate.connection.url”);
con = getConnection(url,
configuration.getProperty(“hibernate.connection.username”),
configuration.getProperty(“hibernate.connection.password”));

stmt = con.createStatement();
if (!con.getMetaData().getTables(null,”mydb”,”mytable”,null).next()){
if (url.startsWith(“jdbc:postgresql”)) {
int count = stmt.executeUpdate(“CREATE DATABASE \”MyDb\”  WITH OWNER = ” +
configuration.getProperty(“hibernate.connection.username”));
} else if (url.startsWith(“jdbc:mysql”)) {
int count = stmt.executeUpdate(“CREATE SCHEMA mydb”);
} else {
throw new SQLException(“DB not supported”);
}
}
} catch (Exception e) {
// log failure
} finally {
if (stmt != null) try {stmt.close();} catch (Exception ex) {}
if (con != null) try {con.close();} catch (Exception ex) {}
}

private static Connection getConnection(String url, String user, String password) throws SQLException {
String urlPart = getUrlPart(url);
return DriverManager.getConnection(urlPart, user, password);
}

private static String getUrlPart(String url) {
int idx = url.lastIndexOf(‘/’);
if (idx < 0) return url; String part = url.substring(0, idx); if (url.startsWith("jdbc:postgresql")) { return part + "/postgres"; } else if (url.startsWith("jdbc:mysql")) { return part + "/mysql"; } return url; } [/sourcecode] Let me know if you find better way to create database schema in Java.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: