Check if table exists

Question

I have a desktop application with a database embedded in it  When I execute my program I need to check that specific table exists  or create it if not   Given a Connection object named conn for my database  how could I check this

User · Answer

Adding to Gaby s post  my jdbc getTables   for Oracle 10g requires all caps to work    employee  - gt   EMPLOYEE   Otherwise I would get an exception      java sql SqlExcepcion exhausted resultset    even though  employee  is in the schema

User · Answer

I don t actually find any of the presented solutions here to be fully complete so I ll add my own  Nothing new here  You can stitch this together from the other presented solutions plus various comments   There are at least two things you ll have to make sure    Make sure you pass the table name to the getTables   method  rather than passing a null value  In the first case you let the database server filter the result for you  in the second you request a list of all tables from the server and then filter the list locally  The former is much faster if you are only searching for a single table  Make sure to check the table name from the resultset with an equals match  The reason is that the getTables   does pattern matching on the query for the table and the   character is a wildcard in SQL  Suppose you are checking for the existence of a table named EMPLOYEE SALARY  You ll then get a match on EMPLOYEESSALARY too which is not what you want    Ohh  and do remember to close those resultsets  Since Java 7 you would want to use a try-with-resources statement for that   Here s a complete solution   public static boolean tableExist Connection conn  String tableName  throws SQLException       boolean tExists   false      try  ResultSet rs   conn getMetaData   getTables null  null  tableName  null             while  rs next                   String tName   rs getString  TABLE NAME                if  tName    null  amp  amp  tName equals tableName                     tExists   true                  break                                    return tExists      You may want to consider what you pass as the types parameter  4th parameter  on your getTables   call  Normally I would just leave at null because you don t want to restrict yourself  A VIEW is as good as a TABLE  right  These days many databases allow you to update through a VIEW so restricting yourself to only TABLE type is in most cases not the way to go  YMMV

User · Answer

You can use the available meta data     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 here for more details  Note also the caveats in the JavaDoc

User · Answer

Method that checks if all tables exist    If a table doesnt exist it creates the table     public void checkTables         try           startConn      method that connects with mysql database         String useDatabase    USE     getDatabase                  stmt executeUpdate useDatabase           String   tables     Patients    Procedures    Payments    Procedurables     thats table names that I need to create if not exists         DatabaseMetaData metadata   conn getMetaData             for int i 0  i lt  tables length  i                  ResultSet rs   metadata getTables null  null  tables i   null               if  rs next                      createTable tables i                    System out println  Table     tables i      created                                  catch SQLException e            System out println  checkTables       e getMessage               closeConn      Close connection with mysql database

User · Answer

If using jruby  here is a code snippet to return an array of all tables in a db   require  rubygems  require  jdbc mysql  Jdbc  MySQL load driver require  java   def get database tables connection  db name    md   connection get meta data   rs   md get tables db name  nil        TABLE       tables        count   0   while rs next     tables  lt  lt  rs get string 3    end  while   return tables end

User · Answer

DatabaseMetaData dbm   con getMetaData       check if  employee  table is there ResultSet tables   dbm getTables null  null   employee   null   if  tables next           Table exists   else        Table does not exist

[java] Check if table exists

Examples related to java

Examples related to jdbc