CODEWITHSUNDEEP
javamysqlsqljdbc
K Spriggs
K Spriggs

Reputation: 331

Java mysql if Table Exists

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.

Upvotes: 3

Views: 11507

Answers (4)

Jess Balint
Jess Balint

Reputation: 1697

The DatabaseMetaData API, part of the JDBC spec, is the way to do this in Java. You'll specifically want to check out the `getTables' method. This is a good class to be familiar with.

Upvotes: 1

Thusitha Thilina Dayaratne
Thusitha Thilina Dayaratne

Reputation: 5712

you can use 

SHOW TABLES LIKE '$this_table'  or
SHOW TABLES FROM $dbname

and check whether table is already exist

Upvotes: 1

SheetJS
SheetJS

Reputation: 22925

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)

Upvotes: 7

Alfredo Osorio
Alfredo Osorio

Reputation: 11475

You can use CREATE TABLE IF NOT EXISTS TBL_NAME

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

Upvotes: 1

Related Questions