JDBC - Create table example

Posted on January 28, 2017


Technologies used:   JDK 1.8.0_121 | MySQL 5.7.12 | Eclipse Mars.2 (4.5.2)

The following examples show how to create a table using the JDBC API. There are four steps for creating a table using JDBC API.

  • Open connection to the database.
  • Create a statement object to perform a create table query.
  • Execute the executeUpdate() method of statement object to submit  a query to database.
  • Close connection to the database.

Creating table using JDBC Statement

The executeUpdate() method of Statement object is used to create and submit a query to the database. Here is an example to demonstrate this.

CreateTableExample.java

package com.boraji.tutorial.jdbc;

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

/**
 * @author imssbora
 */
public class CreateTableExample {
  
  private static final String CREATE_TABLE_SQL="CREATE TABLE boraji.users ("
                    + "UID INT NOT NULL,"
                    + "NAME VARCHAR(45) NOT NULL,"
                    + "DOB DATE NOT NULL,"
                    + "EMAIL VARCHAR(45) NOT NULL,"
                    + "PRIMARY KEY (UID))";
  
  public static void main(String[] args) {
    String jdbcUrl = "jdbc:mysql://localhost:3306/BORAJI";
    String username = "root";
    String password = "admin";

    Connection conn = null;
    Statement stmt = null;

    try {

      conn = DriverManager.getConnection(jdbcUrl, username, password);
      stmt = conn.createStatement();

      stmt.executeUpdate(CREATE_TABLE_SQL);

      System.out.println("Table created");

    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      try {
        // Close connection
        if (stmt != null) {
          stmt.close();
        }
        if (conn != null) {
          conn.close();
        }
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
  }
}

Output

Table created

View created table in MySQL Command line client -

mysql> use BORAJI;
Database changed
mysql> desc users;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| UID   | int(11)     | NO   | PRI | NULL    |       |
| NAME  | varchar(45) | NO   |     | NULL    |       |
| DOB   | date        | NO   |     | NULL    |       |
| EMAIL | varchar(45) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.06 sec)

 

Creating table using PreparedStatment

Using the executeUpdate() method of the PreparedStatment object, we can create and submit a query to the database. Here is an example to demonstrate this.

CreateTableExample.java

package com.boraji.tutorial.jdbc;

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


/**
 * @author imssbora
 */
public class CreateTableExample {
  
  private static final String CREATE_TABLE_SQL="CREATE TABLE employee ("
                    + "EMP_ID int(11) NOT NULL,"
                    + "NAME VARCHAR(45) NOT NULL,"
                    + "DOB DATE NOT NULL,"
                    + "EMAIL VARCHAR(45) NOT NULL,"
                    + "DEPT varchar(45) NOT NULL,"
                    + "PRIMARY KEY (EMP_ID))";
  
  public static void main(String[] args) {
    String jdbcUrl = "jdbc:mysql://localhost:3306/BORAJI";
    String username = "root";
    String password = "admin";

    Connection conn = null;
    PreparedStatement stmt = null;

    try {

      conn = DriverManager.getConnection(jdbcUrl, username, password);
      stmt = conn.prepareStatement(CREATE_TABLE_SQL);
      stmt.executeUpdate();

      System.out.println("Table created");

    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      try {
        if (stmt != null) {
          stmt.close();
        }
        if (conn != null) {
          conn.close();
        }
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
  }
}

Output

Table created

View created table in MySQL Command line client -

mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| EMP_ID | int(11)     | NO   | PRI | NULL    |       |
| NAME   | varchar(45) | NO   |     | NULL    |       |
| DOB    | date        | NO   |     | NULL    |       |
| EMAIL  | varchar(45) | NO   |     | NULL    |       |
| DEPT   | varchar(45) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)