Finding JDBC Metadata

Problem

You want to learn about a database or table.

Solution

Read the documentation provided by your vendor or database administrator. Or ask the software for a MetaData object.

Discussion

There are two classes of metadata (data about data) that you can ask for: DatabaseMetaData and ResultSetMetaData . Each of these has methods that let you interrogate particular aspects. The former class is obtained from a get method in a Connection object; the latter from a get method in the given ResultSet. First, let’s look at RawSQLServlet , a “generic query” formatter. The user enters a query (which must begin with SELECT) into an HTML form, and a servlet (see Section 18.2) passes the query on to a database using JDBC. The response is interrogated and formatted into a neat little HTML table, using the column names from the ResultSetMetaData as the headings for the HTML table. Figure 20-2 shows the form for inputting the query and the resulting response from the servlet. The code for the RawSQLServlet class is in Example 20-11. The nice part about this program is that it responds to whatever columns are in the ResultSet, which need not be in the same order as they are in the database. Consider the two queries:

select name, address from userdb
select address, name from userdb

Any code that depends upon knowing the order in the database would look very strange indeed if the user query requested fields in a different order than they were stored in the database.

RawSQLServlet in action

Figure 20-2. RawSQLServlet in action

Example 20-11. RawSQLServlet.java 

import com.darwinsys.util.FileProperties;

import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.io.*;
import java.util.*;

/** Process a raw SQL query; use ResultSetMetaData to format it.
 */
public class RawSQLServlet extends HttpServlet {
    public final static String PROPS_FILE = "JDBCMeta.properties";

    /** The name of the JDBC Driver */
    protected String DRIVER;

    /** The DB connection object */
    protected Connection conn;

    /** The JDBC statement object */
    protected Statement stmt;

    /** Initialize the servlet. */
    public void init(  ) throws ServletException {
        try {
            // Get a Properties to load from
            FileProperties fp = new FileProperties(PROPS_FILE);

            // Load the database driver
            DRIVER = fp.getProperty("driver");
            Class.forName(DRIVER);

            // Get the connection
            log(getClass(  ) + ": Getting Connection");
            Connection conn = DriverManager.getConnection (
                fp.getProperty("dburl"),
                fp.getProperty("user"),
                fp.getProperty("password"));


            log(getClass(  ) + ": Creating Statement");
            stmt = conn.createStatement(  );
        } catch (IOException ex) {
            log(getClass(  ) + ": init: could not load props file " + PROPS_FILE);
        } catch (ClassNotFoundException ex) {
            log(getClass(  ) + ": init: Could not load SQL driver " + DRIVER);
        } catch (SQLException ex) {
            log(getClass(  ) + ": init: SQL Error: " + ex);
        }
    }

    /** Do the SQL query */
    public void doPost(HttpServletRequest request,
        HttpServletResponse response) throws ServletException, IOException {

        String query = request.getParameter("sql");

        response.setContentType("text/html");
        PrintWriter out = response.getWriter(  );

        if (query == null) {
            out.println("<b>Error: malformed query, contact administrator</b>");
            return;
        }

        // NB MUST also check for admin privs before proceding!
        if (!query.toLowerCase(  ).startsWith("select")) {
            throw new SecurityException("You can only select data");
        }

        try {    // SQL
            out.println("<br>Your query: <b>" + query + "</b>");
            ResultSet rs = stmt.executeQuery(query);

            out.println("<br>Your response:");

            ResultSetMetaData md = rs.getMetaData(  );
            int count = md.getColumnCount(  );
            out.println("<table border=1>");
            out.print("<tr>");
            for (int i=1; i<=count; i++) {
                out.print("<th>");
                out.print(md.getColumnName(i));
            }
            out.println("</tr>");
            while (rs.next(  )) {
                out.print("<tr>");
                for (int i=1; i<=count; i++) {
                    out.print("<td>");
                    out.print(rs.getString(i));
                }
                out.println("</tr>");
            }
            out.println("</table>");
            // rs.close(  );
        } catch (SQLException ex) {
            out.print("<B>" + getClass(  ) + ": SQL Error:</B>
" + ex);
            out.print("<pre>");
            ex.printStackTrace(out);
            out.print("</pre>");
        }
    }

    public void destroy(  ) {
        try {
            conn.close(  );    // All done with that DB connection
        } catch (SQLException ex) {
            log(getClass(  ) + ": destroy: " + ex);
        }
    }
}

The servlet as shown is not thread safe (see Section 24.6) because you can’t really assume that the Connection object is thread safe. However, this servlet is used only by the administrator. A servlet connecting to a database should probably save only the driver class name and URL in its init( ) method, and get the Connection in its service( )/doGet( )/doPost( ) method. However, this will likely be very slow. One solution is to use a connection pool : you preallocate a certain number of Connection objects, hand them out on demand, and the servlet returns its connection to the pool when done. Writing a simple connection pool is easy, but writing a connection pool reliable enough to be used in production is very hard. For this reason, JDBC 2 introduced the notion of having the driver provide connection pooling. However, this is an optional feature -- check your driver’s documentation. Also, Enterprise JavaBeans (EJB) running in an application server usually provide connection pooling; if the servlet engine runs in the same process, this can be a very efficient solution.

Database metadata

The second example (see Example 20-12) uses a DatabaseMetaData to print out the name and version number of the database product and its default transaction isolation (basically, the extent to which users of a database can interfere with each other; see any good book on databases for information on transactions and why it’s often really important to know your database’s default transaction isolation).

Example 20-12. JDBCMeta.java 

import com.darwinsys.util.FileProperties;

import java.awt.*;
import java.sql.*;

/** A database MetaData query
 */
public class JDBCMeta {

    public static void main(String[] av) {
        int i;
        try {
            FileProperties fp = new FileProperties("JDBCMeta.properties");

            // Load the driver
            Class.forName(fp.getProperty("driver"));

            // Get the connection
            Connection conn = DriverManager.getConnection (
                fp.getProperty("dburl"),
                fp.getProperty("user"),
                fp.getProperty("password"));

            // Get a Database MetaData as a way of interrogating 
            // the names of the tables in this database.
            DatabaseMetaData meta = conn.getMetaData(  );

            System.out.println("We are using " + meta.getDatabaseProductName(  ));
            System.out.println("Version is " + meta.getDatabaseProductVersion(  ) );
        
            int txisolation = meta.getDefaultTransactionIsolation(  );
            System.out.println("Database default transaction isolation is " + 
                txisolation + " (" +
                transactionIsolationToString(txisolation) + ").");

            conn.close(  );

            System.out.println("All done!");

        } catch (java.io.IOException e) {
            System.out.println("Can't load PROPERTIES " + e);
        } catch (ClassNotFoundException e) {
            System.out.println("Can't load driver " + e);
        } catch (SQLException ex) {
            System.out.println("Database access failed:");
            System.out.println(ex);
        }
    }

    /** Convert a TransactionIsolation int (defined in java.sql.Connection)
     * to the corresponding printable string.
     */
    public static String transactionIsolationToString(int txisolation) {
        switch(txisolation) {
            case Connection.TRANSACTION_NONE: 
                // transactions not supported.
                return "TRANSACTION_NONE";
            case Connection.TRANSACTION_READ_UNCOMMITTED: 
                // All three phenomena can occur
                return "TRANSACTION_NONE";
            case Connection.TRANSACTION_READ_COMMITTED: 
            // Dirty reads are prevented; non-repeatable reads and 
            // phantom reads can occur.
                return "TRANSACTION_READ_COMMITTED";
            case Connection.TRANSACTION_REPEATABLE_READ: 
                // Dirty reads and non-repeatable reads are prevented;
                // phantom reads can occur.
                return "TRANSACTION_REPEATABLE_READ";
            case Connection.TRANSACTION_SERIALIZABLE:
                // All three phenomena prvented; slowest!
                return "TRANSACTION_SERIALIZABLE";
            default:
                throw new IllegalArgumentException(
                    txisolation + " not a valid TX_ISOLATION");
        }
    }
}

When you run it, in addition to some debugging information, you’ll see something like this. The details, of course, depend on your database:

> java JDBCMeta
Enhydra InstantDB - Version 3.13
The Initial Developer of the Original Code is Lutris Technologies Inc.
Portions created by Lutris are Copyright (C) 1997-2000 Lutris Technologies, Inc.All Rights Reserved.
We are using InstantDB
Version is Version 3.13
Database default transaction isolation is 0 (TRANSACTION_NONE).
All done!
>
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.226.181.57