CODEWITHSUNDEEP
javasqlite
user3276091
user3276091

Reputation: 263

java - check table existence in sqlite

is there a way to check if the table is already existed. It always prompt me t

[SQLITE_ERROR] SQL error or missing database (table player_record already exist).

this is what i want to do

if(player_record is existing){
  don't create table;
}else{
  create table;
}

Upvotes: 3

Views: 5675

Answers (4)

Cvele1213
Cvele1213

Reputation: 1

CREATE TABLE IF NOT EXISTS - did not work for me. Also statement for sqlite_master had error if table do not exist, so try catch block solve problem:

try {
String tableExists = "SELECT name FROM sqlite_master WHERE type='table' AND name='table_name';";        
Statement statement = connection.createStatement();
ResultSet ex = statement.executeQuery(tableExists);
String exS = ex.getString(1);       
System.out.println("Table " + exS + " exists.");        
}catch (SQLException e){
System.out.println("Table NOT exists.");        
}

Upvotes: 0

CL.
CL.

Reputation: 180010

SQLite's CREATE TABLE statement has the IF NOT EXISTS clause:

CREATE TABLE IF NOT EXISTS player_record (
    [...]
);

Upvotes: 2

Get Off My Lawn
Get Off My Lawn

Reputation: 36291

For my project, I created a method that looks like this. Instead of doing a query it uses the database's metadata.

    public boolean tableExists(String tableName){
        connect();
        try{
            DatabaseMetaData md = conn.getMetaData();
            ResultSet rs = md.getTables(null, null, tableName, null);
            rs.last();
             return rs.getRow() > 0;
        }catch(SQLException ex){
            Logger.getLogger(SQLite.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

Here is a class that goes along with it:

public class SQL{

    protected Connection conn = null;
    protected String dbName = "mydatabase.db";

    public void connect(){
        if(conn != null){
            return;
        }
        try{
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite:" + dbName);
        }catch(ClassNotFoundException | SQLException e){
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
        }
    }

    public boolean tableExists(String tableName){
        connect();
        try{
            DatabaseMetaData md = conn.getMetaData();
            ResultSet rs = md.getTables(null, null, tableName, null);
            rs.last();
            return rs.getRow() > 0;
        }catch(SQLException ex){
            Logger.getLogger(SQLite.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
}

I then use it like this:

SQL sql = new SQL();
if(sql.tableExists("myTableName")){
    // Table Exists!
}else{
    // Table doesn't exist.
}

Upvotes: 5

Anik Islam Abhi
Anik Islam Abhi

Reputation: 25352

To find table exist or not using query is 

SELECT name FROM sqlite_master WHERE type='table' AND name='table_name';

Execute this and then check name is null or not

Upvotes: 3

Related Questions