Re: How to detect views w/ JDBC?

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 18 Apr 2005 06:32:28 -0700
Message-ID: <1113831148.716173.228730@f14g2000cwb.googlegroups.com>

Bob Bunch (OCP) wrote:
> On Mon, 18 Apr 2005 03:39:58 +0000, HansF wrote:
>
> > On Sun, 17 Apr 2005 23:14:22 -0400, Bob Bunch (OCP) interested us
by
> > writing:
> >
> >> Perhaps I should've clarified that this is for a code generator,
NOT a
> >> to-be-live application. I'm generating classes based on a given
> >> schema's tables/views, and need to determine WHAT a given entity
is, so
> >> I can NOT generate insert/update/delete methods if the entity is a
> >> view.
> >
> > 1) If you are thinking about database independence, listen to Jim.
> >
> > 2) Oracle has vews '???_TABLES' and '???_VIEWS' that list tables
and
> > views. ???=USER for those owned by the logged-in user'; =ALL for
those
> > accessible by the user; =DBA for all, if the user has DBA rights.
> >
> > 3) Some of Oracle's views are updateable. If you are unfamiliar
with
> > this, suggest you reread the manuals and 'Reilly book 'Mastering
Oracle
> > SQL' to get more info.
> >
> > Also check out '???_UPDATABLE_COLUMNS'
> >
> > 4) In many cases, you should seriously consider turning those
methods
> > into triggers (or PL/SQL routines called by triggers). That helps
to
> > ensure complete data consistency regardless of which mechanism your
> > users find to bypass your application code.
> >
> > (Unless you tie their hands behind their backs, someone will find
out
> > that Excel and ODBC can ruin what you are doing. They will then
delight
> > in telling you that there is a major bug in your system, based on
the
> > report they generated after mucking with your datay.)
> >
> > 5) Oracle's online docco at http://docs.oracle.com is handy. In
this
> > case, concentrate on the Concepts manual and the Reference manual
for
> > the relevant version,
> >
> > 6) Realize that what you are doing is somewhat version dependent.
Be
> > prepared for long term maintenance.
>
> 1) Jim didn't tell me HOW TO do anything.
>
> 2) I'm not just a Java coder; I've been an Oracle DBA for > 10 years,
7.1
> --> 10g. *Of course* I could hit the user_/all_/dba_ views to get
what I
> want (I've written Swing apps in the past that did all kinds of
neat/nifty
> DBA stuff), but this *must be* DB agnostic. MySQL, Sybase, DB2 -- do
they
> have the same views? No.
>
> 3) And some are not, hence my question.
>
> 4) I'm fully aware of Oracle's SP/SF/trigger capabilities. This is
BY NO
> MEANS platform agnostic, and believe me - I'm a big proponent of
using
> said capabilities!
> However, it's not appropriate for this situaion.
>
> 5) Been there done that - do they have examples for how to determine,
via
> standard JDBC calls ONLY, how to detect a view?
>
> 6) No it isn't - what I'm TRYING to do is DB agnostic, therefore
would
> work on not just other vendors, but any version of Oracle that JDBC
will
> hook up to.
>
> Bottom line - please _fully_ read my situation befoe telling me to do
> bunch of Oracle-specific stuff. I would just LOVE it if I could ONLY
EVER
> support Oracle - I have 99.9% of my experience w/ this great
platform;
> however, this is not my current reality for this particular project.
If
> you don't know how to do this, I appreciate your help, but please
don't
> just tell me how to do it w/ Oracle-specifics. I already know that.
;)

What you are looking for is DatabaseMetaData object. e.g. This prints all Table names and view names with their types. You can build on it.

import java.sql.*;

public class Test {

   public static void main (String[] args)

      throws Exception {

      DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

      String url = "jdbc:oracle:oci:@test";
      Connection conn =
         DriverManager.getConnection (url, "me", "me");

      String s = new String("TABLE\nVIEW");
      String[] str = s.split("\n");
      DatabaseMetaData dmd = conn.getMetaData();
      ResultSet rs  = dmd.getTables(null, null, null, str);
      while (rs.next()) {
         System.out.println(rs.getString(3) + "   " + rs.getString(4));
      }
      rs.close();
      conn.close();      

   }
}

Regards
/Rauf Received on Mon Apr 18 2005 - 08:32:28 CDT