CODEWITHSUNDEEP
javasqldatabase
Bobby
Bobby

Reputation: 18305

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

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

Upvotes: 38

Views: 35602

Answers (11)

Shubham-Misal
Shubham-Misal

Reputation: 61

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 ......:)🙌

Upvotes: 1

PFrancisco
PFrancisco

Reputation: 11

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.

Upvotes: 1

adrian.tarau
adrian.tarau

Reputation: 3154

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

Upvotes: 14

Jeff Meatball Yang
Jeff Meatball Yang

Reputation: 39017

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

Upvotes: 2

Arvo
Arvo

Reputation: 10570

For MS Access:

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

Upvotes: 0

Ayman Hourieh
Ayman Hourieh

Reputation: 137116

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));
}

Upvotes: 43

Swiety
Swiety

Reputation: 1130

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[])

Upvotes: 1

Bob
Bob

Reputation: 99684

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

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

Upvotes: 1

belgariontheking
belgariontheking

Reputation: 1349

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.

Upvotes: 1

RichardOD
RichardOD

Reputation: 29157

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.

Upvotes: 1

cdonner
cdonner

Reputation: 37648

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')

Upvotes: 1

Related Questions