How Do You Get a List of View Names for a Database? JDBC

What is a view? According to Wikipedia, “In database theory, a view is a virtual or logical table composed of the result set of a pre-compiled query. Unlike ordinary tables in a relational database, a view is not part of the physical schema: it is a dynamic, virtual table computed or collated from data in the database. Changing the data in a view alters the data stored in the database.”

Both MySQL (version 5+) and Oracle support the creation and use of database views.

Oracle Database Setup

SQL> desc employees;
Name Null? Type
----------------------------------------- -------- -------------
ID NOT NULL VARCHAR2(10)
NAME NOT NULL VARCHAR2(20)
AGE NUMBER(38)
SQL> select * from employees;
ID NAME AGE
---------- -------------------- ----------
11 Alex Smith 25
22 Don Knuth 65
33 Mary Kent 35
44 Monica Seles 30
99 Alex Edison 80
100 Al Sumner 70
105 Al Sumner 90
7 rows selected.
SQL> select object_name from user_objects where object_type ='VIEW';
no rows selected
SQL> create view emp55plus as
2 select name, age from employees where age > 55;
View created.
SQL> desc emp55plus;
Name Null? Type
----------------------------------------- -------- -------------
NAME NOT NULL VARCHAR2(20)
AGE NUMBER(38)
SQL> select * from emp55plus;
NAME AGE
-------------------- ----------
Don Knuth 65
Alex Edison 80
Al Sumner 70
Al Sumner 90
SQL> create view empids as
2 select id from employees;
View created.
SQL> desc empids;
Name Null? Type
----------------------------------------- -------- ------------
ID NOT NULL VARCHAR2(10)
SQL> select * from empids;
ID
----------
100
105
11
22
33
44
99
7 rows selected.
SQL> select object_name from user_objects where object_type ='VIEW';
OBJECT_NAME
--------------------------------------------------------------------
EMP55PLUS
EMPIDS
SQL>SQL> commit;
Commit complete.

MySQL Database Setup

mysql> use octopus;
Database changed
mysql> desc employees;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(8) | NO | PRI | | |
| name | varchar(16) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from employees;
+-----+--------+------+
| id | name | age |
+-----+--------+------+
| 88 | Peter | 80 |
| 77 | Donald | 70 |
| 33 | Mary | 30 |
| 44 | Monica | 40 |
| 999 | Andre | 90 |
+-----+--------+------+
5 rows in set (0.00 sec)
mysql> create view emps55plus as select name, age from employees
-> where age > 55;
Query OK, 0 rows affected (0.39 sec)
mysql> desc emps55plus;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(16) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> select * from emps55plus;
+--------+------+
| name | age |
+--------+------+
| Peter | 80 |
| Donald | 70 |
| Andre | 90 |
+--------+------+
3 rows in set (0.00 sec)

The Solution

import java.io.PrintWriter;
import java.io.IOException;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.util.List;
import java.util.ArrayList;
import jcb.util.DatabaseUtil;
import jcb.db.VeryBasicConnectionManager;
import jcb.meta.DatabaseMetaDataTool;
public class GetViews extends HttpServlet {
private static final String ORACLE_VIEWS =
"select object_name from user_objects where object_type = 'VIEW'";
private static final String[] DB_VIEW_TYPES =
{ "VIEW" };
private static final String COLUMN_NAME_VIEW_NAME =
"TABLE_NAME";
public void doGet(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
Connection conn = null;
try {
String dbVendor = request.getParameter("vendor").trim();
String outputFormat = request.getParameter("format").trim();
conn = VeryBasicConnectionManager.getConnection(dbVendor);
List<String> views = null;
if (dbVendor.equals("oracle")) {
views = getOracleViewNames(conn);
}
else {
views = getViewNames(conn);
}
if (views == null) {
printError(response, "NO-VIEWS-FOUND");
return;
}
if (outputFormat.equals("xml")) {
printXML(response, views);
}
else {
printHTML(response, views);
}
}
catch(Exception e) {
printError(response, e.getMessage());
}
finally {
DatabaseUtil.close(conn);
}
} // end doGet
private static void printHTML(HttpServletResponse response,
List<String> views)
throws Exception {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
StringBuilder buffer = new StringBuilder();
buffer.append("<html><body><table border=1 cellspacing=0 cellpadding=0>");
buffer.append("<TR><TH>View Name</TH></TR>");
for (int i=0; i < views.size(); i++) {
buffer.append("<TR><TD>");
buffer.append(views.get(i));
buffer.append("</TD></TR>");
}
buffer.append("</table></body></html>");
out.println(buffer.toString());
}
private static void printXML(HttpServletResponse response,
List<String> views)
throws Exception {
response.setContentType("text/xml");
PrintWriter out = response.getWriter();
StringBuilder buffer = new StringBuilder();
buffer.append("<?xml version="1.0" encoding="ISO-8859-1"?>");
buffer.append("<views>");
for (int i=0; i < views.size(); i++) {
buffer.append("<name>");
buffer.append(views.get(i));
buffer.append("</name>");
}
buffer.append("</views>");
out.println(buffer.toString());
}
private static void printError(HttpServletResponse response,
String message) {
try {
PrintWriter out = response.getWriter();
StringBuffer buffer = new StringBuffer();
buffer.append("<html><body>");
buffer.append(message);
buffer.append("</body></html>");
out.println(buffer);
}
catch(Exception ignore) {
}
}
/**
* Get the Oracle table names for a given connection object.
* If you use getViewNames() for an Oracle database, you
* will get lots of auxiliary tables, which belong to the user,
* but the user is not interested in seeing them.
*
* @param conn the Connection object
* @return the list of table names as a List.
* @exception Failed to get the table names from the database.
*/
public static List<String> getOracleViewNames(Connection conn)
throws Exception {
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(ORACLE_VIEWS);
if (rs == null) {
return null;
}
List<String> list = new ArrayList<String>();
while (rs.next()) {
String viewName = DatabaseUtil.getTrimmedString(rs, 1);
System.out.println("viewName="+viewName);
if (viewName != null) {
list.add(viewName);
}
}
return list;
}
finally {
DatabaseUtil.close(rs);
DatabaseUtil.close(stmt);
}
}
/**
* Get the table names for a given connection object.
* @param conn the Connection object
* @return the list of table names as a List.
* @exception Failed to get the table names from the database.
*/
public static List<String> getViewNames(Connection conn)
throws Exception {
ResultSet rs = null;
try {
DatabaseMetaData meta = conn.getMetaData();
if (meta == null) {
return null;
}
rs = meta.getTables(null, null, null, DB_VIEW_TYPES);
if (rs == null) {
return null;
}
List<String> list = new ArrayList<String>();
System.out.println("getViewNames(): --------------");
while (rs.next()) {
String viewName =
DatabaseUtil.getTrimmedString(rs, COLUMN_NAME_VIEW_NAME);
System.out.println("viewName="+viewName);
if (viewName != null) {
list.add(viewName);
}
}
System.out.println("--------------");
return list;
}
finally {
DatabaseUtil.close(rs);
}
}
}

Invoking GetViews for MySQL

Figure: shows how to run the solution for the MySQL database.

Invoking GetViews for MySQL

Invoking GetViews for Oracle

Figure: shows how to run the solution for the Oracle database.

Invoking GetViews for Oracle



Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

JDBC Topics