Java code to check if a given User, Schema, Table exists?

Question

I want to make Java code (using Oracle 11g and JDBC also) which will check if a particular User or Schema exists or not. How can I do this by using only Java code and not by executing SQL queries (inside my java code) to do the checking ?

Thanks.

EDIT -

METHOD 1:

In MySQL, we can use something like this -

Connection conn = DriverManager.getConnection(url,username,password);
DatabaseMetaData DMD = conn.getMetaData();
ResultSet res = DMD.getCatalogs();
 while (res.next()) {
      String database = res.getString("TABLE_CAT");
}

Now put an if inside the while to check it given DB exists.

METHOD 2:

OR, you can execute some queries like mentioned in this post. Using SQL query to determine if a table exists

I want to use something like method 1 to do the job instead of method 2 - That is what i meant.

Without querying database, how would you know what you have on database? I see only option is, keep another copy of those entries in flat file inside your app (which is very bad way in my opinion). — kosa
– kosa, Commented Aug 14, 2012 at 15:28
You can try to establish the connection without executing query. But this will work only if db user has connect authority. — gkuzmin
– gkuzmin, Commented Aug 14, 2012 at 15:31
@gkuzmin: Is establishing connection enough to get schema and user details? Don't get me wrong, I am not expert on DB side. — kosa
– kosa, Commented Aug 14, 2012 at 15:34
Why is it that you don't want to run any queries against the database? This seems like a bizarre restriction to have when you want to get data from the database. Additionally depending on the reason it may also rule out approaches like conn.getMetaData() (which of course runs SQL queries behind the scenes to get its data). — Andrzej Doyle
– Andrzej Doyle, Commented Aug 14, 2012 at 15:42

Justin Cave · Accepted Answer · 2012-08-14 15:41:12Z

5

The pure JDBC way to interrogate the database about this sort of thing is to use the DatabaseMetaData class in your Java application. DatabaseMetaData.getSchemas will give you the set of schemas in the database. DatabaseMetaData.getTables will give you a listing of the tables. You can write code that iterates through these ResultSet objects to see if a particular table or schema exists.

Of course, behind the scenes, the JDBC driver is simply executing SQL queries against the Oracle data dictionary to get this information.

answered Aug 14, 2012 at 15:41

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sweet dreams Over a year ago

Maybe we can create our own method which executes SQL statements to check if a user exists and then use this method in any code which needs to do such a job. That raises another question - will our method be as good as the code that executes SQL behind the scenes ?

Justin Cave Over a year ago

@sweetdreams - Of course, you could write your own code. If you're comfortable using the DatabaseMetaData class, though, there probably isn't much benefit to writing custom SQL. If you want to get a subset of the data one of the predefined methods returns (i.e. you want to get a single row rather than iterating through hundreds of rows to determine if a particular user exists), you may be able to write a more efficient query than the folks that wrote the JDBC driver developed. It is unlikely, though, that you'd write a more efficient query in general.

Collectives™ on Stack Overflow

Java code to check if a given User, Schema, Table exists?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related