CODEWITHSUNDEEP
javamysqljdbc
ViviO
ViviO

Reputation: 43

How to check if a database or relation exists when creating one, then print off an error if it does

I'm new to JDBC and I am trying to work on a project for a class. We are supposed to create a database and relations for said database. If the database or relations already exist, we are supposed to print a message notifying the user. I'm not really sure how to do that. This is what I have so far for my methods:

public static void createDatabase() throws Exception {
    String createString =
      "CREATE DATABASE IF NOT EXISTS companydb";
    Statement stmt = null;
    try {
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/?autoReconnect=true&useSSL=false";
        String username = "username";
        String password = "password";           
        Class.forName(driver);
        
        Connection con = DriverManager.getConnection(url, username, password);
                    
        stmt = con.createStatement();
        stmt.executeUpdate(createString);
        System.out.println("Database created.");
    } catch (Exception e) {
        System.out.println(e);
    } finally {
        if (stmt != null) { stmt.close();
        }
    }
}

And for creating an employee table (not all values are created yet, just a test run):

public static void createEmployeeTable() throws Exception {
    String createString =
      "create table if not exists employee" + "(Fname char(32) NOT NULL, " +
     PRIMARY KEY(Fname)";
    Statement stmt = null;
    try {
        Connection con = getConnection();
        stmt = con.createStatement();
        stmt.executeUpdate(createString);
        System.out.println("Employee table created.");
    } catch (Exception e) {
        System.out.println(e);
    } finally {
        if (stmt != null) { stmt.close(); }
    }
}

Also I realize creating a table like that might not be the most efficient way to make a relation, but it's just a work in progress right now.

Regardless of if the database and relations/tables already exist or not, it puts out the same output:

Database created.
Connected to database.
Employee table created.
Connected to database.
Department table created.
Connected to database.
Project table created.
Connected to database.
Works_On table created.

Upvotes: 0

Views: 584

Answers (2)

Conffusion
Conffusion

Reputation: 4485

JDBC API has a DatabaseMetaData interface which you can use to get more information about the available tables, columns, indexes, constraints, supported datatypes, ...

A java.sql.DatabaseMetaData instance can be fetched directly from the java.sql.Connection instance.

https://www.baeldung.com/jdbc-database-metadata

Your createEmployeeTable method can have following structure:

    public static void createEmployeeTable() throws Exception {
    try {
        Connection con = getConnection();
        DatabaseMetaData metadata=con.getMetaData();
        // search for table employee in any schema or catalog
        try(ResultSet rs=metadata.getTables(null, null, "employee", new String[]{"TABLE"})) {
            if(rs.next()) {
                System.out.println("Employee table already exists");
            } else {
                // let's create the employee table
                // ...
                String createString =
                      "create table employee (Fname char(32) NOT NULL, PRIMARY KEY(Fname))";
                try (Statement stmt = con.createStatement()) {
                    stmt.executeUpdate(createString);
                    System.out.println("Employee table created.");
                }
            }
        }
    } catch (Exception e) {
        System.out.println(e);
    }
}

Upvotes: 0

maheeka
maheeka

Reputation: 2093

Break down your code to two steps.

  1. Execute a select query on the table. If the table does not exist, this will definitely throw an error.
  2. If the select does not throw an error you can go ahead and create the table.

Upvotes: 1

Related Questions