Java mysql if Table Exists

Question

Thanks for all the support so far in my epic battle for my learning Java with MySQL. What I want to do is check if the table exists. What currently happens is that if the database is created and the tables are created as well. But if I run the same code, I get an error that the table already exists.

The big question is how would I check of the table exists? Here is some code that I have worked on

if (tabCrea.createTable(dbDef, con, preStatement, tabStruct.getAgentDetail(), "Agent Details"))
    System.out.println("Passed Here");
else
    System.out.println("Failed Here");

Which calls the following

protected boolean createTable(DataBaseDefaults dbDef, Connection con, Statement statement, String myTableName, String tableName) {

    try {
        Class.forName(dbDef.getJdbcDriver());
        con = DriverManager.getConnection(dbDef.getDbAddress() + dbDef.getPortAddress() + dbDef.getDbName(), 
                dbDef.getDbUserName(), dbDef.getDbPassword());
        statement = con.createStatement();
        statement.executeUpdate(myTableName);
        System.out.println("Table Sucessfully Created : " + tableName);
        return true;
    }
    catch (SQLException e ) {
        //Problem is caught here;
        System.out.println("An error has occured on Table Creation with Table " + tableName);
        return false;
    }
    catch (ClassNotFoundException e) {
        System.out.println("There was no Mysql drivers found");
        return false;
    }
}

and the table is defined here

private String agentDetail = "CREATE TABLE AgentDetail ("
    + "AgentDetailIdNo INT(64) NOT NULL AUTO_INCREMENT,"
    + "Initials VARCHAR(2),"
    + "AgentDetailDate DATE,"
    + "AgentDetailCount INT(64),"
    + "PRIMARY KEY(AgentDetailIdNo)"
    + ")";

A humble hack would appreciate any help that is on offer.

Answer 1

There are two approaches:

1) if you want to preserve the table if it does exist, use IF NOT EXISTS:

private String agentDetail = "CREATE TABLE IF NOT EXISTS AgentDetail (" ...

2) if you want to delete the table and start anew, delete the table first with the DROP TABLE statement. To do that, run the statement

DROP TABLE IF EXISTS AgentDetail

before the create table query (note that this will delete all of the existing data in the table)

Answer 2

You can use CREATE TABLE IF NOT EXISTS TBL_NAME

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]