db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: best way to check for the exsitence of a table
Date Tue, 22 May 2012 19:33:16 GMT
Hi Pavel,

Querying the metadata as you suggest is a good approach. You will want 
to adjust the arguments to DatabaseMetaData.getTables() however. The 
arguments in your example may find false matches in other schemas and 
with tables created with double-quoted names. The following example may 
help you adjust the arguments to getTable():

public class z
{
     public  static  void    main( String... args ) throws Exception
     {
         Connection  conn = DriverManager.getConnection( 
"jdbc:derby:memory:db;create=true" );

         conn.prepareStatement( "create table myTable( a int )" ).execute();

         DatabaseMetaData    dbmd = conn.getMetaData();
         ResultSet   rs;

         rs = dbmd.getTables( null, "APP", "MYTABLE", new String[] { 
"TABLE" } );
         System.out.println( "Table 'MYTABLE' exists = " + rs.next() );

         rs = dbmd.getTables( null, "APP", "mytable", new String[] { 
"TABLE" } );
         System.out.println( "Table 'mytable' exists = " + rs.next() );
     }

}

Hope this helps,
-Rick

On 5/22/12 11:33 AM, Pavel Bortnovskiy wrote:
>
> Hello:
>
> When reading derby docs, I noticed that the functionWwdUtils.wwdChk4Tablein Derby demos
is implemented by executing an update on the table and then relying on the exception mechanism
to determine whether or not the table exists.
>   
> My approach was to execute DatabaseMetaData.getTables method (pls see implementation
below) and iterate through the resultset. I also wanted to assure case insensitivity.
>   
> Which method is more preferable from the point of efficiency and database operations
(locks, etc).
> This code might be executed frequently and by multiple threads.
>   
> Thank you,
> Pavel.
>
>
>
>     public static boolean tableExists(final Connection connection, 
> final String tableName) throws SQLException {
>
>         try {
>
>             final DatabaseMetaData databaseMetaData = 
> connection.getMetaData();
>
>             final ResultSet resultSet = 
> databaseMetaData.getTables(null, null, null, new String[]{"TABLE"});
>
>             try {
>
>                 while (resultSet.next()) {
>
>                     if 
> (resultSet.getString("TABLE_NAME").equalsIgnoreCase(tableName)) {
>
>                         return true;
>
>                     }
>
>                 }
>
>             } finally {
>
>                 connection.commit();
>
>                 resultSet.close();
>
>             }
>
>             return false;
>
>         } catch (SQLException e) {
>
>             Statement statement = null;
>
>             try {
>
>                 final Connection c = 
> Components.getMemoryDb().getConnection(true);
>
>                 statement = c.createStatement();
>
>                 final String select = "SELECT * FROM 
> SYSCS_DIAG.LOCK_TABLE";
>
>                 final ResultSet resultSet = 
> statement.executeQuery(select);
>
>                 final int cnt = resultSet.getMetaData().getColumnCount();
>
>                 final StringBuilder builder = new 
> StringBuilder("Results of \"" + select + "\":");
>
>                 while (resultSet.next()) {
>
>                     builder.append("\n\t");
>
>                     for (int i = 1; i <= cnt; i++) {
>
>                         if (i > 1) {
>
>                             builder.append(",");
>
>                         }
>
>                         builder.append(resultSet.getObject(i));
>
>                     }
>
>                 }
>
>                 logger.info(builder.toString());
>
>             } finally {
>
>                 if (statement != null) {
>
>                     statement.close();
>
>                 }
>
>             }
>
>             throw e;
>
>         }
>
>     }
>
>
>             Jefferies archives and monitors outgoing and incoming
>             e-mail. The contents of this email, including any
>             attachments, are confidential to the ordinary user of the
>             email address to which it was addressed. If you are not
>             the addressee of this email you may not copy, forward,
>             disclose or otherwise use it or any part of it in any form
>             whatsoever. This email may be produced at the request of
>             regulators or in connection with civil litigation.
>             Jefferies accepts no liability for any errors or omissions
>             arising as a result of transmission. Use by other than
>             intended recipients is prohibited. In the United Kingdom,
>             Jefferies operates as Jefferies International Limited;
>             registered in England: no. 1978621; registered office:
>             Vintners Place, 68 Upper Thames Street, London EC4V 3BJ.
>             Jefferies International Limited is authorised and
>             regulated by the Financial Services Authority.
>


Mime
View raw message