FileDocCategorySizeDatePackage
DBUtil.javaAPI DocExample5607Sun Sep 02 14:59:06 BST 2001com.oreilly.forum.jdbcimpl

DBUtil

public class DBUtil extends Object
Helper methods for relational database access using JDBC.

Fields Summary
private static final int
BUF_SIZE
private static Map
tableToMaxIDMap
private static Stack
availableConnections
Constructors Summary
Methods Summary
public static voidclose(java.sql.Statement stmt, java.sql.Connection con)
Close a statement and connection.


              
           
        if (stmt != null) {
            try {
                stmt.close();
            } catch (Exception ignored1) {
            }
        }
        if (con != null) {
            try {
                // return the connection to the pool
                putPooledConnection(con);
            } catch (Exception ignored2) {
            }
        }
    
public static voidcloseAllConnections()
Close any connections that are still open. The Servlet will call this method from its destroy() method.

        // empty the connection pool and close all connections
        emptyPool();
    
private static synchronized voidemptyPool()

        while (!availableConnections.isEmpty()) {
            Connection con = (Connection) availableConnections.pop();
            try {
                con.close();
            } catch (SQLException ignored) {
            }
        }
    
public static java.sql.ConnectiongetConnection(java.lang.String dbURL)

return
a new Connection to the database.

        // get a connection from the pool
        return getPooledConnection(dbURL);
    
public static java.lang.StringgetLongString(java.sql.ResultSet rs, int columnIndex)

return
a long text field from the database.

        try {
            InputStream in = rs.getAsciiStream(columnIndex);
            if (in == null) {
                return "";
            }

            byte[] arr = new byte[BUF_SIZE];
            StringBuffer buf = new StringBuffer();
            int numRead = in.read(arr);
            while (numRead != -1) {
                buf.append(new String(arr, 0, numRead));
                numRead = in.read(arr);
            }
            return buf.toString();
        } catch (IOException ioe) {
            ioe.printStackTrace();
            throw new SQLException(ioe.getMessage());
        }
    
public static synchronized longgetNextID(java.lang.String tableName, java.sql.Connection con)
Compute a new unique ID. It is assumed that the specified table has a column named 'id' of type 'long'. It is assumed that that all parts of the program will use this method to compute new IDs.

return
the next available unique ID for a table.

        Statement stmt = null;

        try {
            // if a max has already been retreived from this table,
            // compute the next id without hitting the database
            if (tableToMaxIDMap.containsKey(tableName)) {
                Long curMax = (Long) tableToMaxIDMap.get(tableName);
                Long newMax = new Long(curMax.longValue() + 1L);
                tableToMaxIDMap.put(tableName, newMax);
                return newMax.longValue();
            }

            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT MAX(id) FROM " + tableName);
            long max = 0;
            if (rs.next()) {
                max = rs.getLong(1);
            }
            max++;
            tableToMaxIDMap.put(tableName, new Long(max));
            return max;
        } finally {
            // just close the statement
            close(stmt, null);
        }
    
private static synchronized java.sql.ConnectiongetPooledConnection(java.lang.String dbURL)


         
              
        if (!availableConnections.isEmpty()) {
            System.out.println("Reusing a connection");
            return (Connection) availableConnections.pop();
        } else {
            System.out.println("Creating a connection");
            return DriverManager.getConnection(dbURL);
        }
    
private static synchronized voidputPooledConnection(java.sql.Connection con)

        availableConnections.push(con);
    
public static voidsetLongString(java.sql.PreparedStatement stmt, int columnIndex, java.lang.String data)
Store a long text field in the database. For example, a message's text will be quite long and cannot be stored using JDBC's setString() method.

        if (data.length() > 0) {
            stmt.setAsciiStream(columnIndex,
                    new ByteArrayInputStream(data.getBytes()),
                    data.length());
        } else {
            // this 'else' condition was introduced as a bug fix.  It was
            // discovered that the 'setAsciiStream' code shown above
            // caused MS Access to throw a "function sequence error"
            // when the string was zero length.  This code now works.
            stmt.setString(columnIndex, "");
        }