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.

CREATE TABLE IF NOT EXISTS ?

Eric Hotinger
– Eric Hotinger

2013-10-21 15:23:50 +00:00
Commented Oct 21, 2013 at 15:23 — Eric Hotinger
– Eric Hotinger, Commented Oct 21, 2013 at 15:23

SheetJS · Accepted Answer · 2013-10-21 15:32:23Z

7

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)

answered Oct 21, 2013 at 15:32

SheetJS

23k12 gold badges68 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alfredo Osorio · Accepted Answer · 2013-10-21 15:25:25Z

1

You can use CREATE TABLE IF NOT EXISTS TBL_NAME

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

answered Oct 21, 2013 at 15:25

Alfredo Osorio

11.5k12 gold badges60 silver badges85 bronze badges

Comments

2 revs · Accepted Answer · 2013-10-21 15:34:08Z

1

you can use

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

and check whether table is already exist

edited Oct 21, 2013 at 15:34

community wiki

2 revs
Thuiya

Comments

Jess Balint · Accepted Answer · 2013-10-21 16:10:53Z

1

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.

answered Oct 21, 2013 at 16:10

Jess Balint

1,70713 silver badges9 bronze badges

1 Comment

K Spriggs Over a year ago

Thanks Jess,I have had a look at this, but I know that I have to know a bit more before I can appreciate it. Keith

Collectives™ on Stack Overflow

Java mysql if Table Exists

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related