JavaData & JavaDynamic Database Creation with JDBC

Dynamic Database Creation with JDBC

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

The term dynamic database refers to the process of firing SQL queries on demand to create the schema afresh. The JDBC API library is quite capable of dealing with the situation. In fact, no special feature is required to implement the same. A dynamic database is necessary in a situation where new schema are required to reflect changing circumstances. The table structure may remain the same but the name space changes according to the requirement. For example, a change in the financial year in an accounting package or a budgetary system may need to create a new database to persist current year transactions. A table segments the domain, but the database is the playground where these tables are encompassed. This article explores the idea and the role played by JDBC in implementing the process of dynamic database creation.

SQL Overview

SQL, the acronym, stands for Structured Query Language. Although the structure of this language may not seem very enticing to the verge of denigration, it is by far the only standard language with the hallmark of reliability while interacting with relational databases. The vibrancy of relational databases outlived many of its genres, matured by the years of service with improved efficacy and reliability. The same maturity applies to this chosen language of interaction, which lives alongside. According to the type of database interaction, SQL commands can be grossly categorized into four types. They are:

  • DDL (Data Definition Language): DDL statements are used to define database structure. Such as:
    • CREATE: Creates database objects
    • ALTER: Alters database structure
    • DROP: Deletes database objects
    • TRUNCATE: Removes records from a table
    • COMMENT: Adds comments to data dictionary
    • RENAME: Object renaming
  • DML (Data Manipulation Language): DML statements are used to manipulate data within the database. Such as:
    • SELECT: Retrieves data from the database
    • INSERT: Inserts data to a table
    • UPDATE: Updates existing data in a table
    • DELETE: Deletes records from a table
    • LOCK TABLE: Controls concurrency
  • DCL (Data Control Language): DCL statements are statements related to altering database user privileges. Such as:
    • GRANT: Grants a user’s access privileges to database
    • REVOKE: Removes a user’s access privileges
  • TCL (Transaction Control Language): TCL statements are statements that manage DML transaction. Such as:
    • COMMIT: Confirms save after transaction completion
    • SAVEPOINT: Sets a transaction point that may be rolled back later on
    • ROLLBACK: Restores the database to the original since the last COMMIT

These names do not signify any separate language; it’s pretty much the same SQL in the guise of defining the purpose for better understanding (or, confusing?). DDL and DML may be acceptable, but DCL and TCL, why? It seems more metaphorical than implying simplicity in a manner, jestingly, a nose and mouth may be called PMO (Pre-Mouth Organ) and PNO (Post-Nose Organ), respectively. Let’s not bother; after all, what’s in a name?

JDBC Support

The JDBC API library provides all the support one needs for database interaction. There is nothing very specific in executing DDL SQL statements. Usually, the function used when executing a DDL query is executeUpdate. This function is suitable for executing SQL statements that do not produce a ResultSet object. In most cases, DDL queries, when executed, do not return anything. That raises the question, “How do I get a hint of the successful execution of a DDL query?” The answer lies in the Java exception mechanism. A statement that fails in execution raises SQLException. This is the only way to know that, when the result is silent with no flagged exception, the statement has executed successfully. In some cases, we can wrap the DDL in PL/SQL and catch the exception bubbled up in JDBC. It’s meaningless to catch a return value of a DDL query. For example, the CREATE DATABASE statement can at best succeed or fail. It is easy to get a hint of the same by observing the occurrence of any exception.

A Java Example to Execute DDL Statements

The code is quite simple and self-explanatory. The idea is to create a database and then populate it with two tables, namely budget and bill. The database name is appended with year marked as financial year, such as budget_2014_2015. That’s it.

# MySQL connection properties
database.driver=com.mysql.jdbc.Driver
database.url=jdbc:mysql://localhost:3306/mysql
database.user=user1
database.password=secret

database.ddl.create=CREATE DATABASE IF NOT EXISTS
   budget
database.ddl.use=USE budget

database.ddl.table.budget=CREATE TABLE IF NOT EXISTS
   budget(budget_code INTEGER UNSIGNED PRIMARY KEY,
   budget_name VARCHAR(50) NOT NULL,
   budget_amount FLOAT(10,2) UNSIGNED NOT NULL,
   budget_status VARCHAR(50) NOT NULL)
database.ddl.table.bill=CREATE TABLE IF NOT EXISTS
   bill(bill_no INTEGER UNSIGNED PRIMARY KEY,
   bill_amount FLOAT(10,2) NOT NULL,
   budget_code INTEGER UNSIGNED,
   FOREIGN KEY(budget_code)
   REFERENCES budget(budget_code))

Listing 1: The ddl.properties file

package org.mano.dynadb;

import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.Properties;

public class Main {
   private final Properties databaseProperties =
      new Properties();
   private String financialYear = "";
   private Connection con = null;

   public Main() {
      Calendar c = Calendar.getInstance();
      financialYear = "_"
         + String.valueOf(c.get(Calendar.YEAR) - 1) + "_"
         + String.valueOf(c.get(Calendar.YEAR));
      try {
         databaseProperties
         .load(new FileReader(new
            File("/home/dynadb/ddl.properties")));
      } catch (IOException ioException) {
         System.err.println(ioException.getMessage());
      }
   }

   public static void main(String[] args) throws Exception {
      Main obj = new Main();
      obj.createSchema();
      obj.createTables();
   }

   private Connection openConnection() {
      if (con != null)
         closeConnection();
      try {
         Class.forName(databaseProperties.getProperty
            ("database.driver"))
            .newInstance();
         con = DriverManager.getConnection(
            databaseProperties.getProperty("database.url"),
            databaseProperties.getProperty("database.user"),
            databaseProperties.getProperty("database.password"));
      catch (ClassNotFoundException | InstantiationException
            IllegalAccessException | SQLException e) {
         System.err.println(e.getMessage());
      }
      return con;
   }

   private void closeConnection() {
      if (con != null) {
         try {
            con.close();
         } catch (SQLException e) {
            System.err.println(e.getMessage());
         }
      }
   }

   public void createSchema() throws Exception {
      openConnection().createStatement().executeUpdate(
         databaseProperties.getProperty("database.ddl.create")
            + financialYear);
      System.out.println("Schema created: "
         + databaseProperties.getProperty("database.ddl.create")
         + financialYear);
      closeConnection();
   }

   public void createTables() throws Exception {
      Connection c = openConnection();
      c.createStatement().executeUpdate(
         databaseProperties.getProperty("database.ddl.use")
            + financialYear);
      c.createStatement().executeUpdate(
         databaseProperties.getProperty("database.ddl.table.budget"));
      c.createStatement().executeUpdate(
         databaseProperties.getProperty("database.ddl.table.bill"));
      closeConnection();
      System.out.println("Tables created:
         "+databaseProperties.getProperty("database.ddl.table.budget"));
      System.out.println("Tables created:
         "+databaseProperties.getProperty("database.ddl.table.bill"));
   }
}

Listing 2: Java code demonstrating the execution of DDL statements.

Conclusion

In a similar manner, other DDL and DCL statements can be executed without much effort. The structure of the code in the examples given can further be arranged with the assistance of an ORM tool such as Hibernate. Except a few raw DDL statements, when using Hibernate, it is convenient to convert a POJO into a database table without stooping down to raw SQL statements. In a nutshell, creating dynamic tables in Java in whatever way you may choose, be it by using JDBC or Hibernate, is quite simple.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories