How to create a table using JDBC Statement

After we saw how to connect to MySQL with JDBC driver is time to create a table. TO keep it simple I will copy the connect method from previous example.

Example

As a table structure I will use the same fields as for class User from how to create builder design pattern in Java tutorial.

The create table script based on our model from Builder Design Pattern example will look like this.

CREATE TABLE `user` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `type` ENUM('ADMIN', 'REGULAR', 'SUBSCRIBER') NOT NULL,
  `dob` DATETIME NOT NULL,
  `usercol` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

Now its time to put it into Java code.

package com.admfactory.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class JDBCCreateNewTable {

    private static Connection connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            return DriverManager.getConnection("jdbc:mysql://localhost:3306/admfactory.com", "root", "");
        } catch (Exception e) {
            System.out.println("Failed to connect to DB.");
            e.printStackTrace();
            return null;
        }
    }

    private static boolean createTable(Connection connection) throws Exception {
        Statement statement = null;
        boolean result = false;

        String newTableSQL = "CREATE TABLE `user` ("
                + "`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,"
                + "`name` VARCHAR(100) NOT NULL, " 
                + "`email` VARCHAR(255) NOT NULL, "
                + "`password` VARCHAR(255) NOT NULL, " 
                + "`type` ENUM('ADMIN', 'REGULAR', 'SUBSCRIBER') NOT NULL, "
                + "`dob` DATETIME NOT NULL, " 
                + "PRIMARY KEY (`id`), "
                + "UNIQUE INDEX `id_UNIQUE` (`id` ASC))" 
                + "ENGINE = InnoDB " 
                + "DEFAULT CHARACTER SET = utf8;";
        System.out.println("SQL Script: " + newTableSQL);

        try {

            statement = connection.createStatement();
            statement.execute(newTableSQL);
            result = true;
        } catch (Exception e) {
            e.printStackTrace();
            result = false;
        } finally {
            if (statement != null) {
                statement.close();
            }
        }
        return result;
    }

    public static void main(String[] args) {
        try {
            System.out.println("Create new table.");
            /** Get the connection. */
            Connection connection = connect();

            if (connection != null) {
                boolean result = createTable(connection);

                /** Check if the table was created successfully. */
                if (result) {
                    System.out.println("Table was created successfully.");
                } else {
                    System.out.println("Failed to create the table!");
                }

                /** Close the connection. */
                connection.close();
            }
        } catch (Exception e) {
            System.out.println("Something went wrong.");
        }

    }
}

Output

Create new table.
SQL Script: CREATE TABLE `user` (`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,`name` VARCHAR(100) NOT NULL, `email` VARCHAR(255) NOT NULL, `password` VARCHAR(255) NOT NULL, `type` ENUM('ADMIN', 'REGULAR', 'SUBSCRIBER') NOT NULL, `dob` DATETIME NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `id_UNIQUE` (`id` ASC))ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;
Table was created successfully.

Conclusions

We can check if the table was created or not using any database editors, like MySQL Workbench.