---

DbTool.java

Go to the documentation of this file.

package net.threebit.utils.sosc;

/*
        Copyright 2003 Shawn Deleurme
        Copyright 2003 Kevin O'Donnell

        This program is free software; you can redistribute it and/or modify
        it under the terms of the GNU General Public License as published by
        the Free Software Foundation; either version 2 of the License, or
        (at your option) any later version.

        This program is distributed in the hope that it will be useful,
        but WITHOUT ANY WARRANTY; without even the implied warranty of
        MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
        GNU General Public License for more details.

        You should have received a copy of the GNU General Public License
        along with this program; if not, write to the Free Software
        Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
*/

import java.io.*;
import java.sql.*;
import java.util.*;

public class DbTool {

        protected Connection connection;
        private String className;
        private String url;
        private String userName;
        private String password;
        private boolean connectionIsPrivate = true;

        public DbTool (String className, String url, String userName, String password) throws Exception {
                setConnection(className, url, userName, password);
        }

        public DbTool (Connection connection) throws Exception {
                setConnection(connection);
        }

        private void setConnection (Connection connection) throws SQLException {
                if (this.connection != null) {
                        this.connection.close();
                }
                this.connection = connection;
        }

        private void setConnection (String className, String url, String userName, String password) throws Exception {
                this.className = className;
                this.url = url;
                this.userName = userName;
                this.password = password;
                Class.forName(className);
                setConnection(DriverManager.getConnection(url,userName,password));
        }

        public Connection getConnection() throws Exception {
                // Once a connection has left the house, it isn't closed on finalization
                connectionIsPrivate = false;
                return connection;
        }

        public String getUserName() {
                return userName;
        }


        public void close () throws Exception {
                connection.close(); 
        }

        public void rollback () throws Exception {
                connection.rollback(); 
        }

        public void commit () throws Exception {
                connection.commit (); 
        }

        public void setAutoCommit( boolean autocommit ) throws Exception {
                // connection.setTransactionIsolation( Connection.TRANSACTION_SERIALIZABLE );           
                connection.setAutoCommit( autocommit ); 
        }
  public /*static*/ String q (Object o) {
                if (o == null) { return "''"; }
                String field = o.toString() ; 
    if (field.length() == 0) { return "''"; }

    if (field.indexOf('\'')>-1) {
      // a single quote was found, replace all single quotes with double single quotes.
      char[] origin= new char[field.length()];
      field.getChars(0,field.length(),origin,0);
      int numApostrophes=0;
      for (int f=0;f<origin.length; f++) {
        if (origin[f]=='\'') numApostrophes++;
      }
      char[] parsed= new char[numApostrophes+origin.length];
      int index=0;
      for (int f=0;f<origin.length; f++) {
        if (origin[f]=='\'') {
          parsed[index++]=origin[f];
        };
        parsed[index++]=origin[f];
      }
      field = new String(parsed);
    }
    return "'"+field+"'"; // no single quotes were found, just return string surrounded by single quotes
  }

        public String quote (String s) throws SQLException {
                // special case.
                if (s == null) return null;

                // SQL quote it.
                return "'" + s.replaceAll("'","''") + "'";
        }

        public String getString (String sql) throws Exception { return ""+getObject(sql); }

        public int getInt (String sql) throws Exception { return Integer.valueOf(getString(sql)).intValue(); }

        public double getDouble (String sql) throws Exception { return Double.valueOf(getString(sql)).doubleValue(); }

        public long getLong (String sql) throws Exception { return Long.valueOf(getString(sql)).longValue(); }

        public Object getObject (String sql) throws Exception {
                ResultSet r = query(sql);
                if (! r.first()) { 
                        r.getStatement().close();
                        throw new Exception("ResultSet does not contain at least 1 row.\n"+sql); 
                }
                Object o = r.getObject(1);
                r.getStatement().close();
                return o;
        }

        public ResultSet query (String sql) throws Exception {
                if (connection == null) { throw new Exception("No connection has been set"); }

                Statement statement = null;
                try {
                        statement = connection.createStatement();

                        // We do not call executeQuery() because if we are calling a stored procedure
                        // and it updates rows internally, then executeUpdate() will throw an exception.
                        statement.execute(sql);

                        // Get the first result set; if it is null then continue into the result set search loop
                        ResultSet result = statement.getResultSet();
                        if (result != null) {
                                return result;
                        }

                        // Go hunting for the first non-null result set.
                        while (statement.getMoreResults()) {
                                result = statement.getResultSet();
                                if (result != null) {
                                        return result;
                                }
                        }

                        // Close then throw.
                        statement.close();
                        throw new Exception("A non-null result set could not be found.");

                } catch (Exception e) {
                        throw new Exception("Error occured while executing sql:\n"+sql+"\n"+e.getMessage(),e);
                }
        }

        public void update (String sql) throws Exception {
                if (connection == null) { throw new Exception("No connection has been set"); }
                Statement statement = null;
                try {
                        statement = connection.createStatement();
                        statement.executeUpdate(sql);
                }
                catch (Exception e) {
                        throw new Exception("Error occured while executing sql:\n"+sql+"\n"+e.getMessage(),e);
                }
                finally {
                        try { if (statement != null) { statement.close(); } } catch (Exception e) { e.printStackTrace(); }
                }
        }

        public Statement createStatement() throws Exception {
                if (connection == null) { throw new Exception("No connection has been set"); }
                return connection.createStatement();
        }

        public String inClause (Collection c, boolean quoted) throws Exception {
                if (c == null || c.size() == 0) { return "(null)"; }
                StringBuffer sb = new StringBuffer();
                sb.append("(");
                for (Iterator i = c.iterator(); i.hasNext(); ) {
                        Object item = i.next();
                        if (item == null) {
                                sb.append("null");
                        }
                        else if (quoted) {
                                sb.append(quote(item.toString()));
                        }
                        else {
                                sb.append(item.toString());
                        }
                        sb.append(",");
                }
                sb.deleteCharAt( sb.length()-1 );
                sb.append(")");
                return sb.toString();
        }

        public int rowCount (ResultSet r) throws Exception {
                int rowCount = 0;
                r.beforeFirst();
                while (r.next()) { rowCount++; }
                r.beforeFirst();
                return rowCount;
        }

        public static Map toMap (ResultSet r) throws Exception {
                return toMap(r,true);
        }

        public static Map toMap (ResultSet r, boolean advance) throws Exception {

                // Should we advance?
                if (advance) { r.next(); }

                // Return if we are not on a valid row
                if (r.getRow() == 0) {
                        return new HashMap();
                }

                // Okay, now return the current row

                Map map = new LinkedHashMap( 32 ); // Use LinkedHashMap to retain the result set column ordering.
                ResultSetMetaData m = r.getMetaData();
                int cc = m.getColumnCount();
                for ( int i=1; i<=cc; i++ ){
                        map.put ( m.getColumnName(i) , r.getString(i) );
                }
                return map;
        }

        public List toList (ResultSet r) throws Exception {
                List list = new ArrayList(32) ;
                while (r.next()){
                        Map map = new HashMap( 32 );
                        ResultSetMetaData m = r.getMetaData();
                        int cc = m.getColumnCount();
                        for ( int i=1; i<=cc; i++ ){
                                map.put (
                                        m.getColumnName(i)
                                , r.getString(i) );
                        }
                        list.add(map);
                }
                return list;
        }

        // billing specific tools

        /*
         * These methods are meant to provide a single point of access to the db.
         * They will end up in a bean.
         * --
         * the pattern is as follows...
         */
/*
        public Map addCustomer ( Map map ) throws Exception {
                // this should be fixed to use an index in the future.
                String sql = "SELECT * FROM customers WHERE tel = '"+map.get("custphone") + "';" ;
                ResultSet r = query ( sql ) ;
                if (r.next()){
                        // houston, we have a row. and, only one!
                        map.putAll ( toMap(r) );
                }
                else {
                        sql
                                = "INSERT INTO customers "
                                + "(firstname,lastname,tel,address,zip,city,province,country) "
                                + "VALUES ('"
                                +                               map.get("firstname")
                                + "','" + map.get("lastname")
                                + "','" + map.get("custphone")
                                + "','" + map.get("custaddress1")
                                + "','" + map.get("custzip")
                                + "','" + map.get("custcity")
                                + "','" + map.get("custstate")
                                + "','US'"
                                + "); SELECT MAX(id) FROM customers; ";
                        r = query (sql);
//                      r = s.getResultSet();
                        r.first();
                        map.put ("customerid",new Integer(r.getInt(1)));
                }
                r.close();
                return map;
        }
*/

/*
        public int addAccount ( Map map ) throws Exception {
                return 0;
        }
        public int addSale ( Map map ) throws Exception {}
        public int addPayment( Map map ) throws Exception {}
        public int fixCustomer ( Map map ) throws Exception {}
*/

/*
TODO: move this to a "BILLING" app specific file.
        public void selectHouses() throws Exception{
                out.println ("<select name=house>") ;
                ResultSet houses = query(
                        "select * FROM houses WHERE id >0;");
                out.println ("<option value=-1>All</option>");
                while (houses.next()){
                        out.print ("<option value=");
                        out.print (houses.getInt(1));
                        out.print (">");
                        out.print (houses.getString(2));
                        out.print ("</option>");
                }
                houses.close();
                out.println("</select>");
        }

TODO: move this to a "BILLING" app specific file.
        public void selectAgents() throws Exception{
                out.println ("<select name=agent>") ;
                ResultSet agents = query(
                        "select * FROM agents WHERE id >0;");
                out.println ("<option value=-1>All</option>");
                while (agents.next()){
                        out.print ("<option value=");
                        out.print (agents.getInt(1));
                        out.print (">");
                        out.print (agents.getString(2));
                        out.print ("</option>");
                }
                agents.close();
                out.println("</select>");
        }

TODO: move this to a "BILLING" app specific file.
        public void selectTypes() throws Exception{
                out.println ("<select name=type>") ;
                ResultSet types = query(
                "select distinct authcode from transactiontest where authcode not like 'CheckAuth%';");
                out.println ("<option value=-1>All</option>");
                out.println ("<option value='CheckAuth%'>Preauth</option>");
                while (types.next()){
                        out.print ("<option value='");
                        out.print (types.getString(1));
                        out.print ("'>");
                        out.print (types.getString(1));
                        out.print ("</option>");
                }
                types.close();
                out.println("</select>");
        }
*/

        public StringBuffer resultSetToXML (ResultSet r) throws Exception {
                return resultSetToXML(r,new StringBuffer(), Integer.MAX_VALUE);
        }
        
        public StringBuffer resultSetToXML (ResultSet r, StringBuffer sb, int count) throws Exception {

                String emptyString = "";

                if (r == null) { throw new Exception("No ResultSet provided."); }
                if (sb == null) { throw new Exception("No StringBuffer provided."); }
                if (count < 0) { throw new Exception("Count must be positive (" + count + ")"); }

                int columnCount = r.getMetaData().getColumnCount();
                String[] columnNames = new String[ columnCount ];
                for (int i=1; i <= columnCount; i++) { 
                        columnNames[i-1] = r.getMetaData().getColumnName(i); 
                }

                sb.append("<resultSet>\n");
                for (int x = 0; x < count && r.next(); x++) {
                        sb.append("<row>\n");
                        // This is the source of a 
                        for (int i=1; i <= columnCount; i++) {
                                Object value = r.getObject(i);
                                if (value == null) { value = emptyString; }
                                sb.append("\t<"); sb.append(columnNames[i-1]); sb.append(">");
                                sb.append( (""+r.getObject(i)).replaceAll("&","&amp;" )); // TODO: alot more XML convertions!
                                sb.append("</"); sb.append(columnNames[i-1]); sb.append(">\n");
                        }
                        sb.append("</row>\n");
                }
                sb.append("</resultSet>\n");
                return sb;
        }

        public void finalize () {
                if (connection != null && connectionIsPrivate) {
                        try { connection.close(); }
                        catch (Exception e) {
                                System.out.println("Error while closing connection during finalization");
                                e.printStackTrace();
                        }
                }
        }
}

---