JDBC MySQL: Create Database Example

Project Description:

This JDBC example explains how to create a new database in MySQL.
As a best practice, we use Singleton (for making database connection), Data Access Object (DAO) patterns and own custom exception.

Prerequisites:

Before proceeding with this example, refer this page which gives an overview of the following concepts;

how to create, configure JDBC driver and run JDBC examples,
explains various design patterns used in this example like Data Access Object (DAO), Transfer Object (TO) and Singleton pattern,
the program control flow logic,
tables used in JDBC examples,
writing own custom exception,
explains various ways of accessing database tables such as Eclipse Data Source Explorer, MySQL command prompt, phpMyAdmin, etc.

Environment Used

JDK 6 (Java SE 6) (To install JDK refer this link in Windows, Ubuntu)
Eclipse Indigo IDE for Java EE Developers (3.7.1) (To install Eclipse, refer this link)
Mysql-5.5 (To install MySQL read this page)
MySQL Connector/J 5.1 JAR file

Create Java Project

Open Eclipse IDE and create a new Java project and name it as “DBQuery“.

ConnectionFactory.java (singleton pattern)

Create a new class in src folder with the package name as “com.theopentutorials.jdbc.db” and class name as “ConnectionFactory” and click Finish.
Copy the following code:

package com.theopentutorials.jdbc.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionFactory {
	//static reference to itself
	private static ConnectionFactory instance = new ConnectionFactory();
	public static final String URL = "jdbc:mysql://localhost/jdbcdb";
	public static final String USER = "YOUR_DATABASE_USERNAME";
	public static final String PASSWORD = "YOUR_DATABASE_PASSWORD";
	public static final String DRIVER_CLASS = "com.mysql.jdbc.Driver"; 
	
	//private constructor
	private ConnectionFactory() {
		try {
			Class.forName(DRIVER_CLASS);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	private Connection createConnection() {
		Connection connection = null;
		try {
			connection = DriverManager.getConnection(URL, USER, PASSWORD);
		} catch (SQLException e) {
			System.out.println("ERROR: Unable to Connect to Database.");
		}
		return connection;
	}	
	
	public static Connection getConnection() {
		return instance.createConnection();
	}
}

Fill the username and password for your database and enter your database name in the URL string.

DbUtil.java

Create a new class in src folder with the package name as “com.theopentutorials.jdbc.db” and class name as “DbUtil” and click Finish.
Copy the following code:

package com.theopentutorials.jdbc.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DbUtil {

	public static void close(Connection connection) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				/*Ignore*/
			}
		}
	}

	public static void close(Statement statement) {
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				/*Ignore*/
			}
		}
	}

	public static void close(ResultSet resultSet) {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				/*Ignore*/
			}
		}
	}
}

DatabaseDAO.java (Data Access Object (DAO) pattern)

Create a new class in src folder with the package name as “com.theopentutorials.jdbc.dao” and class name as “DatabaseDAO” and click Finish.
Copy the following code:

package com.theopentutorials.jdbc.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import com.theopentutorials.jdbc.db.ConnectionFactory;
import com.theopentutorials.jdbc.db.DbUtil;
import com.theopentutorials.jdbc.exception.ApplicationException;

public class DatabaseDAO {

	private Connection connection;
	private Statement statement;

	public DatabaseDAO() { }

	public void createDatabase(String dbName) throws ApplicationException {
		String query = "CREATE DATABASE IF NOT EXISTS " + dbName;
		try {
			connection = ConnectionFactory.getConnection();
			statement = connection.createStatement();
			statement.executeUpdate(query);
			SQLWarning warning = statement.getWarnings();
			if (warning != null)
				throw new ApplicationException(warning.getMessage());
		} catch (SQLException e) {
			ApplicationException exception = new ApplicationException(
					e.getMessage(), e);
			throw exception;
		} finally {
			DbUtil.close(statement);
			DbUtil.close(connection);
		}
	}
}

This class defines a method createDatabase(String dbName) which creates a new database in MySQL with the given database name. If any SQL warning occurs when executing the statement such as database already exists, an application exception is created and is thrown.

DBDemo.java

This is the Java Application client class with main() method which calls the method in DAO class passing database name to be created and displays the result to the user.

Create a new Class in src folder with the package name as “com.theopentutorials.jdbc.main” and Class name as “DBDemo” and click Finish.
Copy the following code:

package com.theopentutorials.jdbc.main;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.SQLException;
import com.theopentutorials.jdbc.dao.DatabaseDAO;
import com.theopentutorials.jdbc.exception.ApplicationException;

public class DBDemo {
	public static void main(String[] args) {
		// Create DB
		createDB();
	}

	private static void createDB() {
		try {
			BufferedReader br = new BufferedReader(new InputStreamReader(
					System.in));
			System.out.println("Enter the Database Name: ");

			String dbName = br.readLine();
			DatabaseDAO dbDao = new DatabaseDAO();
			dbDao.createDatabase(dbName);
			System.out.println("Database Successfully Created");
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ApplicationException e) {
			System.out.println(e.getMessage());
			System.out.println(e);
		}
	}
}