How Do You Get a List of Table Types for a Database? JDBC

Table type is defined as a type of table for storing data and database objects. Each database vendor may have a different set of table types. For example, MySQL’s table types are TABLE, VIEW, and LOCAL TEMPORARY and Oracle’s table types are TABLE, VIEW, and SYNONYM. You can use the DatabaseMetaData.getTableTypes() method to retrieve a list of the table types available in the given catalog (database). Here is the signature of the DatabaseMetaData.getTableTypes() method:

ResultSet getTableTypes() throws SQLException

This method retrieves the table types available for a given database, with the results ordered by table type. Possible table types are TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS, and SYNONYM. The method returns a ResultSet object in which each row has a single String column that is a table type. This method returns the result as a ResultSet object, which is not very useful for web-based applications. Therefore, we will convert the ResultSet object to HTML/XML, which can be quite useful to web-based applications. Next I present a Java servlet (GetTableTypes), which will be able to get table types as HTML or XML. Here is the signature of the servlet: GetTableTypes?vendor=<vendor-name>&format=<HTML-or-XML>

You may add additional parameters to suit your database application. Here, I use vendor to act as a database selector. So, to get the table information as XML or HTML for a MySQL or Oracle database, you may invoke it as

GetTableTypes?vendor=mysql&format=xml
GetTableTypes?vendor=mysql&format=html
GetTableTypes?vendor=oracle&format=xml
GetTableTypes?vendor=oracle&format=html

The Solution

In our solution, we use the DatabaseMetaData.getTableTypes() method to solve the problem. This servlet (GetTableTypes) will display the result as XML or HTML.

import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.util.List;
import java.util.ArrayList;
import java.io.PrintWriter;
import java.io.IOException;
import jcb.util.DatabaseUtil;
import jcb.db.VeryBasicConnectionManager;
public class GetTableTypes extends HttpServlet {
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> tableTypes = getTableTypes(conn);
if (outputFormat.equals("xml")) {
printXML(response, tableTypes);
}
else {
printHTML(response, tableTypes);
}
}
catch(Exception e) {
printError(response, e.getMessage());
}
finally {
DatabaseUtil.close(conn);
}
} // end doGet
private static void printHTML(HttpServletResponse response,
List<String> tableTypes)
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>Table Type</TH></TR>");
for (int i=0; i < tableTypes.size(); i++) {
buffer.append("<TR><TD>");
buffer.append(tableTypes.get(i));
buffer.append("</TD></TR>");
}
buffer.append("</table></body></html>");
out.println(buffer.toString());
}
private static void printXML(HttpServletResponse response,
List<String> tableTypes)
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("<tableTypes>");
for (int i=0; i < tableTypes.size(); i++) {
buffer.append("<type>");
buffer.append(tableTypes.get(i));
buffer.append("</type>");
}
buffer.append("</tableTypes>");
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 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> getTableTypes(Connection conn)
throws Exception {
ResultSet rs = null;
try {
DatabaseMetaData meta = conn.getMetaData();
if (meta == null) {
return null;
}
rs = meta.getTableTypes();
if (rs == null) {
return null;
}
List<String> list = new ArrayList<String>();
System.out.println("getTableTypes(): --------------");
while (rs.next()) {
String type = rs.getString(1);
System.out.println("type="+type);
if (type != null) {
list.add(type);
}
}
System.out.println("--------------");
return list;
}
finally {
DatabaseUtil.close(rs);
}
}
}

Invoking GetTableTypes for MySQL

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

Invoking Get Table Types for MySQL

Invoking GetTableTypes for Oracle

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

Invoking GetTableTypes for Oracle


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

JDBC Topics