How can I detect a SQL table's existence in Java?

Question

How can I detect if a certain table exists in a given SQL database in Java?

Depends on what database server. There is usually a table with all the table names in it, you could just query it. — GEOCHET
– GEOCHET, Commented May 29, 2009 at 19:53
java.sql.DatabaseMetaData.getTables is cross-database(JDBC Driver takes care of database differences). — adrian.tarau
– adrian.tarau, Commented May 29, 2009 at 20:00

Ayman Hourieh · Accepted Answer · 2009-05-29 20:02:43Z

43

You can use DatabaseMetaData.getTables() to get information about existing tables.

This method works transparently and is independent of the database engine. I think it queries information schema tables behind the scenes.

Edit:

Here is an example that prints all existing table names.

DatabaseMetaData md = connection.getMetaData();
ResultSet rs = md.getTables(null, null, "%", null);
while (rs.next()) {
  System.out.println(rs.getString(3));
}

edited May 29, 2009 at 20:02

answered May 29, 2009 at 19:56

Ayman Hourieh

138k23 gold badges149 silver badges116 bronze badges

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

1 Comment

sweet dreams Over a year ago

That example does not work for me. Please see - stackoverflow.com/questions/11699023/…

Jonas Czech · Accepted Answer · 2015-05-15 17:42:34Z

14

Use java.sql.DatabaseMetaData.getTables(null, null, YOUR_TABLE, null). If the table exists, you will get a ResultSet with one record.

See DatabaseMetaData.getTables

edited May 15, 2015 at 17:42

Jonas Czech

12.4k7 gold badges46 silver badges67 bronze badges

answered May 29, 2009 at 19:57

adrian.tarau

3,1542 gold badges27 silver badges29 bronze badges

Comments

Jeff Meatball Yang · Accepted Answer · 2009-05-31 08:22:37Z

2

For ALL ANSI-compliant databases: (mySQL, SQL Server 2005/2008, Oracle, PostgreSQL, SQLLite, maybe others)

select 1 from information_schema.tables where table_name = @tableName

answered May 31, 2009 at 8:22

Jeff Meatball Yang

39.2k27 gold badges94 silver badges125 bronze badges

Comments

cdonner · Accepted Answer · 2009-05-29 19:53:50Z

1

This is not a language-specific, but a database-specific problem. You'd query the metadata in the database for the existence of that particular object.

In SQL Server for instance:

SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table]')
   AND type in (N'U')

answered May 29, 2009 at 19:53

cdonner

37.8k22 gold badges111 silver badges155 bronze badges

Comments

RichardOD · Accepted Answer · 2009-05-29 19:53:57Z

1

Write a query that queries the table/view that will list the tables (this is different depending on DB vendor). Call that from Java.

Googling information_schema.tables will help a lot.

answered May 29, 2009 at 19:53

RichardOD

29.2k10 gold badges63 silver badges81 bronze badges

1 Comment

sleske Over a year ago

Usually using DatabaseMetaData is better, as it is DB-independent.

belgariontheking · Accepted Answer · 2009-05-29 19:54:04Z

1

Depending on the DB, you can do (MySQL)

SHOW TABLES

or (Oracle)

SELECT * FROM user_objects WHERE object_type = 'TABLE'

or another thing for SQL Server. Cycle through the results for MySQL or further filter on the Oracle one.

answered May 29, 2009 at 19:54

belgariontheking

1,3491 gold badge10 silver badges15 bronze badges

1 Comment

Touya Akira Over a year ago

Good answer, it's very convenient

Bob · Accepted Answer · 2009-05-29 19:54:13Z

1

Why not just see if it is in sysobjects (for SQL Server)?

SELECT [name] FROM [sysobjects] WHERE type = 'U' AND [name] = 'TableName'

answered May 29, 2009 at 19:54

Bob

100k30 gold badges127 silver badges131 bronze badges

Comments

Swiety · Accepted Answer · 2009-05-29 19:56:23Z

1

There is a JDBC feature, database vendor independent - see [java.sql.DatabaseMetaData#getTables()][1]

You can get the DatabaseMetaData instance by calling java.sql.Connection#getMetaData()

[1]: http://java.sun.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getTables(java.lang.String, java.lang.String, java.lang.String, java.lang.String[])

answered May 29, 2009 at 19:56

Swiety

1,1309 silver badges12 bronze badges

Comments

PFrancisco · Accepted Answer · 2019-11-16 01:39:00Z

1

This is what worked for me for jdbc:derby:

//Create Staff table if it does not exist yet
String tableName = "STAFF";
boolean exists = conn.getMetaData().getTables(null, null, tableName, null).next();
if(!exists){
    s = conn.createStatement();
    s.execute("create table staff(lastname varchar(30), firstname varchar(30), position varchar(20),salary double,age int)");
    System.out.println("Created table " + tableName);
}

Note that tableName has to be all caps.

answered Nov 16, 2019 at 1:39

PFrancisco

113 bronze badges

Comments

Shubham-Misal · Accepted Answer · 2023-09-09 17:54:32Z

There are two options available to check that a table does or doesn't exist in a particular database

First using DatabaseMetaData

import java.sql.*;
import java.util.*;
import java.io.*;

public class checktableexistusingdatabasemetadata {
    public static void main(String[] args) {
        try {
            // load the driver class
            Class.forName("com.mysql.cj.jdbc.Driver");
            // make connection we pass these threee things for connection our program to the
            // database >> url,username, password
            Connection todb = DriverManager.getConnection("jdbc:mysql://localhost:portnumberTCP/databasename", "username",
                    "password");       
            DatabaseMetaData dbm = todb.getMetaData();
            // check if "newdata" table is there
            ResultSet tables = dbm.getTables(null, null, "newdataindbf", null);
            if (tables.next()) {
                System.out.println("The table you are trying to create that already exists in the database so try a ne name bcz same name table cannot exixts in the particular database .....:)");
            } else {
                System.out.println("Your query will be executed .....:)");
                // query for creating a table into the database
                String qry = "create table newdataindbf(nid int(29) primary key auto_increment,nname varchar(200) not null,ncity varchar(299))";
                // statement is a interface here we are using it to create a table
                Statement stmt = todb.createStatement();
                // here we are passing the query so that it is executed
                stmt.executeUpdate(qry);
                // if here the execute query works then this message will be printed or else we
                // will get an exception
                System.out.println("Table is created succesfully ....:)");
                // close connection
                todb.close();
            }
        }
        catch(Exception e){
            System.out.println("Something went wrong >> "+e.getMessage());
        }
    }
}

Output :-

output when table doesn't exists inside the database ..>
Your query will be executed .....:)
Table is created succesfully ....:)

output when the table already exists inside the database ..> 
The table you are trying to create that already exists in the database so try a ne name bcz same name table cannot exixts in the particular database .....:)

Second by using the query >> information_schema

import java.sql.*;
import java.util.*;
import java.io.*;

public class checktableusinginformatioinschema {
    public static void main(String[] args) {
        try {
            // load the driver class
            Class.forName("com.mysql.cj.jdbc.Driver");
            // make connection we pass these threee things for connection our program to the
            // database >> url,username, password
            Connection todb = DriverManager.getConnection("jdbc:mysql://localhost:portnumberTCP/databasename", "username",
                    "password");
            PreparedStatement preparedStatement = todb.prepareStatement(
                    "SELECT count(*) FROM information_schema.tables WHERE table_name = 'newdataindbd' LIMIT 1");
            ResultSet resultSet = preparedStatement.executeQuery();
            resultSet.next();
            // returns true--> 0 or false--> 1 but here it returns true or false ...:)
            // System.out.println(resultSet.getInt(1)!=0);
            if (resultSet.getInt(1) != 0 == true) {
                System.out.println(
                        "The table you are trying to create with the name already exists in the database try to create the table with some different name bcz same name table cannot exists in a particular database ......:) ");
            } else {
                System.out.println("Your query will be executed .....:)");
                // query for creating a table into the database
                String qry = "create table newdataindbd(nid int(29) primary key auto_increment,nname varchar(200) not null,ncity varchar(299))";
                // statement is a interface here we are using it to create a table
                Statement stmt = todb.createStatement();
                // here we are passing the query so that it is executed
                stmt.executeUpdate(qry);
                // if here the execute query works then this message will be printed or else we
                // will get an exception
                System.out.println("Table is created succesfully ....:)");
                // close connection
                todb.close();
            }

        } catch (Exception e) {
            System.out.println("Something went wrong ........ ");
        }
    }
}

Output >>

output when the table doesn't exists >>
Your query will be executed .....:)
Table is created succesfully ....:)

output when the table exists in the db >>
The table you are trying to create with the name already exists in the
database try to create the table with some different name bcz same name table cannot exists in a particular database ......:)

⛅Hope so this will help😇 out to understand ......:)🙌

Arvo · Accepted Answer · 2009-05-29 20:04:58Z

0

For MS Access:

Select Count(*) From MSysObjects 
Where type=1 And name='your_table_name_here'

answered May 29, 2009 at 20:04

Arvo

10.6k1 gold badge34 silver badges36 bronze badges

Collectives™ on Stack Overflow

How can I detect a SQL table's existence in Java?

11 Answers 11

1 Comment

Comments

Comments

Comments

1 Comment

1 Comment

Comments

Comments

Comments

There are two options available to check that a table does or doesn't exist in a particular database

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

1 Comment

Comments

Comments

Comments

1 Comment

1 Comment

Comments

Comments

Comments

There are two options available to check that a table does or doesn't exist in a particular database

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related