Creating a Table using JDBC

Summary: in this tutorial, you will learn how to create a table in the database using JDBC.

To create a table from a Java program using JDBC, you follow these steps:

• First, connect to the database server.
• Second, create a Statement object.
• Third, execute a CREATE TABLE statement by calling the execute() method of the Statement object.
• Finally, close the database connection.

To handle any exception that may occur, you can wrap the code in a try-catch block. It is even better to use the try-with-resources block to properly close the database connection automatically.

The following program shows how to connect to the local MariaDB server and create a table called products in the sales database:

import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {

        String sql = "CREATE TABLE IF NOT EXISTS products ( " +
                "id INT AUTO_INCREMENT PRIMARY KEY, " +
                "name VARCHAR(2555) NOT NULL, " +
                "price DEC(10,2) NOT NULL)";

        try (var connection = DBConnection.connect();
             var statement = connection.createStatement()) {

            // Execute the CREATE TABLE statement to create the table
            statement.execute(sql);

            System.out.println("The table was created successfully.");
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }
}Code language: Java (java)

How it works.

First, construct a CREATE TABLE statement:

String sql = "CREATE TABLE IF NOT EXISTS products ( " +
                "id INT AUTO_INCREMENT PRIMARY KEY, " +
                "name VARCHAR(2555) NOT NULL, " +
                "price DEC(10,2) NOT NULL)";Code language: Java (java)

Second, create a new connection to the database:

var connection = DBConnection.connect();Code language: Java (java)

Third, create a new Statement object for executing an SQL statement:

var statement = connection.createStatement();Code language: Java (java)

Fourth, execute the CREATE TABLE statement:

statement.execute(sql);Code language: Java (java)

Since we use the try-with-resources, the program automatically closes the database connection.

If you run the program and see the following message, meaning that it creates a table successfully:

The table was created successfully.Code language: plaintext (plaintext)

Verifying table creation

First, connect to the MariaDB server using the bob account:

mysql -u bob -pCode language: plaintext (plaintext)

It’ll prompt you for a password for the bob user account. Please enter it and press the Enter key to connect.

Second, change the current database to sales:

use sales;Code language: SQL (Structured Query Language) (sql)

Third, display all tables in the sales database:

show tables;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------+
| Tables_in_sales |
+-----------------+
| products        |
+-----------------+
1 row in set (0.00 sec)Code language: plaintext (plaintext)

Finally, quit the mysql program:

quitCode language: plaintext (plaintext)

Summary

• Use a Statement object to execute a CREATE TABLE statement to create a new table in the database.