Published on May 28 2013 in Databases Java

JDBC drivers allow developers to perform DDL SQL commands in their Java/JSP code including CREATE and DROP of databases. In this example we present a simple JSP page that will allow you to test these functionality.

In cPanel environment the hosting (system) user username has database creation privilege for any database that starts with username_ prefix. You can verify it by running SHOW GRANTS for username@localhost; in mysql client:

GRANT ALL PRIVILEGES ON `username\_%`.* TO 'username'@'localhost'

This username can thus be used to create/drop databases dynamically from Java/JSP code. To execute DDL (Data Definition Language) commands (CREATE, DROP) you will need to call executeUpdate() instead of most commonly used executeQuery() that returns ResultSet. executeUpdate() returns integer - 0 in case we run DROP and 1 when we run CREATE.

int result = statement.executeUpdate("CREATE DATABASE username_databasename");

You need to update username and password variables in the example code. Also do not forget to put mysql-connector-java.X.X.X-bin.jar in your WEB-INF/lib or application server-wide lib directory.

Specifying database name in connection used to create and drop databases is optional. In case of MySQL you can of course use everlasting mysql database and in PostgreSQL use template1 database.

In a bare system (e.g. dedicated or VPS without a control panel) you would normally use root user to create/drop databases unless you created another user and assigned him correct privileges.

connection = DriverManager.getConnection("jdbc:mysql://localhost/", "root", "rootpassword");

Note that databases created dynamically will not be shown in cPanel's database list.

Here goes the full code of create_drop_database.jsp for your reference:

<%@page import="java.sql.*" %>
<%
    // database to be created/dropped
    String database = null;
    // your cPanel username and password here - the user has right to create/drop databases
    String username = "username";
    String password = "secret";
    
    String url = "jdbc:mysql://localhost/";
    Connection connection = null;
    Statement statement = null;
    ResultSet rset = null;
    boolean databaseListChanged = false;
    int result = -1;
    
    try { 
     Class.forName("com.mysql.jdbc.Driver").newInstance(); 
    } catch(ClassNotFoundException e) { 
     out.println("Class not found: "+ e.getMessage());
     return;
    }
    
    try {
    
     connection = DriverManager.getConnection(url, username, password); 
     statement = connection.createStatement();
    
     out.println("<b>List of databases accessible by user " + username + ":</b><br/>");
     rset = statement.executeQuery("SHOW DATABASES");
     while (rset.next()) {
      out.println(rset.getString(1) + "<br/>");
     }
     rset.close();
     out.println("<hr>");
    
     if (request.getParameter("database") != null) {
      database = (String)request.getParameter("database");
      if (request.getParameter("Create") != null &&
       request.getParameter("Create").equals("Create")) {
       result = statement.executeUpdate("CREATE DATABASE " + database);
       out.println("result of 'CREATE DATABASE '" + database + " is " + result);
       databaseListChanged = true;
      } else if (request.getParameter("Drop") != null &&
       request.getParameter("Drop").equals("Drop")) {
       result = statement.executeUpdate("DROP DATABASE " + database);
       out.println("result of 'DROP DATABASE '" + database + " is " + result);
       databaseListChanged = true;
      }
     }
    
     statement.close();
     connection.close();
     if (databaseListChanged) { response.sendRedirect(request.getRequestURL().toString() + "?result=" + result); }
     if (request.getParameter("result") != null) { 
      out.println("result of last CREATE or DROP database is " + request.getParameter("result") + "<br/>");
     }
    %>
    
    <form action="create_drop_database.jsp" method="post"><table>
    <tr><td align="left">Database name to create or drop: <input type="text" name="database" size="20"></td></tr>
    <tr><td align="left"><input type="submit" name="Create" value="Create">
    <input type="submit" name="Drop" value="Drop">
    <input type="reset" name="Reset" value="Reset"></td></tr>
    </table></form>
    
    <%
    
    } catch (SQLException e) {
     out.println(e.getMessage());
    } finally {
     try {
      if(connection != null) connection.close();
     } catch(SQLException e) {}
    }
    
    %>

And here are 2 screenshots of its output:

create drop database with Java/JSP
Creating and deleting databases on the fly is rarely used as this process is a bit costly IO-wise. If you initially decided for using this method you may need to rethink your needs as probably your goal can be achieved without using create/drop.