How to Create, Edit & Alter Tables Using Java?

In Java, JDBC can be used to create, edit, and alter the tables. JDBC can be defined as Java Database Connectivity. It is an API that allows Java programs to interact with the databases. JDBC can implement Java programs to execute SQL queries and manipulate databases through a standard interface.

In this article, we will discuss how to Create, edit & alter tables using Java.

Creating Tables:

By using a Java program, we can create the SQL tables using a Java statement that can be used to create the tables in the MYSQL database.

   private static void createTable(Statement statement) throws SQLException {
        String createTableSQL = "CREATE TABLE IF NOT EXISTS users (" +
                "id INT AUTO_INCREMENT PRIMARY KEY," +
                "username VARCHAR(50) NOT NULL," +
                "email VARCHAR(100) NOT NULL)";
        statement.executeUpdate(createTableSQL);
        System.out.println("Table created successfully.");
    }

This method can be used to create a table in the database with columns ID, username and email, adding constraints ID is auto incremented, and the primary key, username, and email are not null constraints.

Editing the table:

Editing the table operations are inserting, updating, and deleting in the database using Java programs. The commands are implemented, and the operations are INSERT, UPDATE, implemented, and DELETE statements.

private static void insertData(Statement statement) throws SQLException {
        String insertSQL = "INSERT INTO users (username, email) VALUES ('john', '[email protected]')";
        statement.executeUpdate(insertSQL);
        System.out.println("Data inserted successfully.");
    }

    private static void updateData(Statement statement) throws SQLException {
        String updateSQL = "UPDATE users SET email = '[email protected]' WHERE username = 'john'";
        statement.executeUpdate(updateSQL);
        System.out.println("Data updated successfully.");
    }

    private static void deleteData(Statement statement) throws SQLException {
        String deleteSQL = "DELETE FROM users WHERE username = 'john'";
        statement.executeUpdate(deleteSQL);
        System.out.println("Data deleted successfully.");
    }

These insertData, updateData, and deleteData methods are used to implement the editing operations in the Java program using the insert, update, and delete operations in SQL.

Alter the table:

In databases, altering the tables is used to modify the structure of existing tables in the database. These operations are dropping or adding columns or changing column data types. Statements are ALTER TABLE and are used for altering operations.

   private static void addColumn(Statement statement) throws SQLException {
        String addColumnSQL = "ALTER TABLE users ADD COLUMN age INT";
        statement.executeUpdate(addColumnSQL);
        System.out.println("Column added successfully.");
    }

    private static void dropColumn(Statement statement) throws SQLException {
        String dropColumnSQL = "ALTER TABLE users DROP COLUMN age";
        statement.executeUpdate(dropColumnSQL);
        System.out.println("Column dropped successfully.");
        
    }

These methods are used to alter the operations of the table in a Java program, such as adding or removing columns.

Java Program to Create, Edit & Alter Tables

// Java Program to Create, Edit & Alter Tables 
package jdbcDemo;

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

public class GfGJdbcDemo {
    public static void main(String[] args) throws SQLException {
        // Database connection parameters
        String url = "jdbc:mysql://localhost:3306/tutorial";
        String username = "root";
        String password = "";

     // Try-with-resources to automatically close resources
        try (Connection connection = DriverManager.getConnection(url, username, password);
             Statement statement = connection.createStatement()) {

            Scanner scanner = new Scanner(System.in);
            int choice;

            // Display menu options
            do {
                System.out.println("\nOptions:");
                System.out.println("1. Create table");
                System.out.println("2. Insert data");
                System.out.println("3. Update data");
                System.out.println("4. Delete data");
                System.out.println("5. Add column");
                System.out.println("6. Drop column");
                System.out.println("0. Exit");
                System.out.print("Enter your choice: ");
                choice = scanner.nextInt();

                switch (choice) {
                    case 1:
                        createTable(statement);
                        break;
                    case 2:
                        insertData(statement);
                        break;
                    case 3:
                        updateData(statement);
                        break;
                    case 4:
                        deleteData(statement);
                        break;
                    case 5:
                        addColumn(statement);
                        break;
                    case 6:
                        dropColumn(statement);
                        break;
                    case 0:
                        System.out.println("Exiting program.");
                        break;
                    default:
                        System.out.println("Invalid choice. Please enter a valid option.");
                }
            } while (choice != 0);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void createTable(Statement statement) throws SQLException {
        String createTableSQL = "CREATE TABLE IF NOT EXISTS users (" +
                "id INT AUTO_INCREMENT PRIMARY KEY," +
                "username VARCHAR(50) NOT NULL," +
                "email VARCHAR(100) NOT NULL)";
        statement.executeUpdate(createTableSQL);
        System.out.println("Table created successfully.");
    }

    private static void insertData(Statement statement) throws SQLException {
        String insertSQL = "INSERT INTO users (username, email) VALUES ('john', '[email protected]')";
        statement.executeUpdate(insertSQL);
        System.out.println("Data inserted successfully.");
    }

    private static void updateData(Statement statement) throws SQLException {
        String updateSQL = "UPDATE users SET email = '[email protected]' WHERE username = 'john'";
        statement.executeUpdate(updateSQL);
        System.out.println("Data updated successfully.");
    }

    private static void deleteData(Statement statement) throws SQLException {
        String deleteSQL = "DELETE FROM users WHERE username = 'john'";
        statement.executeUpdate(deleteSQL);
        System.out.println("Data deleted successfully.");
    }

    private static void addColumn(Statement statement) throws SQLException {
        String addColumnSQL = "ALTER TABLE users ADD COLUMN age INT";
        statement.executeUpdate(addColumnSQL);
        System.out.println("Column added successfully.");
    }

    private static void dropColumn(Statement statement) throws SQLException {
        String dropColumnSQL = "ALTER TABLE users DROP COLUMN age";
        statement.executeUpdate(dropColumnSQL);
        System.out.println("Column dropped successfully.");
        
    }

}

Output:

Options:
1. Create table
2. Insert data
3. Update data
4. Delete data
5. Add column
6. Drop column
0. Exit
Enter your choice: 1
Table created successfully.

Database Output:

We can identify the Users table into the database.

We can see the Insert data in the below image.

Note: Insert operation done 4 times, so here, 4 tuples inserted.

Similarly, we can do all the operations using Java program.

Explanation of the above Program:

The above program is an example of how to create, edit, and alter tables in Java. It can be implemented using Java JDBC.

• First, we connected to a MySQL database.
• Now, we have created a table named users with the columns ID, username, and email.
• The first operation is inserting the data into the user table.
• The second operation is updating the inserted data.
• The third operation deletes the inserted data.
• The fourth operation is adding a new column named age to the table.
• The fifth operation is to drop the newly added age column into the table.

Note: We can replace the database URL, username, and password with our actual database connection URL, username, and password. And also, we need to install the JDBC drives added to our project.