How to check if a table exists using jdbc java

Question

How to check if a table exists in jdbc using Sqlite3?

Answer 1

You can use the available metadata:

  DatabaseMetaData meta = con.getMetaData();
  ResultSet res = meta.getTables(null, null, "My_Table_Name", 
     new String[] {"TABLE"});
  while (res.next()) {
     System.out.println(
        "   "+res.getString("TABLE_CAT") 
       + ", "+res.getString("TABLE_SCHEM")
       + ", "+res.getString("TABLE_NAME")
       + ", "+res.getString("TABLE_TYPE")
       + ", "+res.getString("REMARKS")); 
  }

See more details here. Note the warnings in Javadoc.

Answer 2

In my system, using NamedParameterJdbcTemplate Spring, I do this:

public boolean tabelaExiste(String tableName) {
    HashMap<String, String> param = new HashMap<>();
    param.put("table_name", tableName);
    return namedJdbcTemplate.query("select 1 from sqlite_master where name = :table_name COLLATE NOCASE and type = 'table'", param, ResultSet::next);
}

Explaining:

In Sqlite, there is a main table that stores all its objects called sqlite_master. I didn’t see any case where you needed to change the data of this table, but to consult I consider something plausible, mainly to check if there is any data in the database. Read more.

Among the fields of this table, 2 for me stand out:

• type
• name

The field type will determine the type of object being cited. It can be table, view, trigger, index etc. The field name is the name of the object. So, if I want to search for a table called MARMOTA, should hold the object name and its type. The query would look like this:

SELECT *
FROM sqlite_master
WHERE
  name = 'marmota' AND type = 'table'

To ensure that the name received as parameter can differ from the case name that is actually registered the object, I can say that I am making a comparison with a COLLATE NOCASE:

SELECT *
FROM sqlite_master
WHERE
  name = 'marmota' COLLATE NOCASE AND
  type = 'table'

Since I simply don’t care about the result of the query, only if it returns something or not, I quote only a constant in the projection part of the data:

SELECT 1
FROM sqlite_master
WHERE
  name = 'marmota' COLLATE NOCASE AND
  type = 'table'

The NamedParameterJdbcTemplate has as one of the overloads of the method query the T query(String, Map<String,?>, ResultSetExtractor<T>), which receives a parameter of type ResultSetExtractor<T>.

An object that meets this interface needs to transform an entire ResultSet in a single object of the type T. In my case, Java itself inferred that my type is Boolean. To turn the result of the consultation into something that I can work with just check if it has results in ResultSet, so I used the method reference ResultSet::next to make the transformation ResultSet -> Boolean.

---

If you are using something purer, closer to the JDBC, just extract what the NamedParameterJdbcTemplate automates for you. It would look something like this:

public boolean tabelaExiste(String tableName) throw SQLException {
    try (PreparedStatement pstmt = connection.prepareStatement("select 1 from sqlite_master where name = ? COLLATE NOCASE and type = 'table'")) {
        pstmt.setString(1, tableName);
        try (ResultSet rs = pstmt.executeQuery()) {
            return rs.next();
        }
    }
}