dbutil.java

WAR - consists the JSP/Servlet & demo pages

/*
 * DBUtil.java
 *
 * Created on July 5, 2002, 2:27 PM
 */

package util;

import java.sql.*;
import javax.sql.*;
import java.util.Enumeration;
import java.util.StringTokenizer;
import java.util.Hashtable;
import javax.naming.NamingException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingEnumeration;
import javax.naming.directory.InitialDirContext;
import java.io.*;

/**
 *
 * @author  gcai
 */
public class DBUtil {
    private static DBConnector dbConnector;
    
    /** Creates a new instance of DBUtil */
    public DBUtil() throws Exception {
        //
        dbConnector = DBConnector.getInstance("jdbc:mysql://localhost/demo?autoReconnect=true", "demo", "demo");
        //dbConnector = new DBConnector();
    }
    
    
    public String[][] getResult(String tableName, SQLCondition condition[]) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        String[][] result = null;
        //Class.forName("org.gjt.mm.mysql.Driver").newInstance();
        //conn = DriverManager.getConnection(_dbUrl);
		try {
            conn = dbConnector.getConnection();
            //conn = dbConnector.getConnection("java:comp/env/jdbc/ex");
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT first,last FROM ");
            sql.append(tableName);
            sql.append(" WHERE ");
            for(int i = 0; i < condition.length; i++) {
                sql.append(condition[i].name);
                sql.append(condition[i].op);
                sql.append(condition[i].value);
                sql.append(" AND ");
            }
            for(int i = 0; 5 > i; i++)
                sql.deleteCharAt(sql.length() - 1);
            
            System.out.println(sql.toString());
            stmt = conn.createStatement();
            stmt.setFetchSize(3);
            ResultSet rs = stmt.executeQuery(sql.toString());
            ResultSetMetaData rsmd = rs.getMetaData();
            int colunmCount =  rsmd.getColumnCount();
            rs.last();
            int rowCount = rs.getRow();
            rs.beforeFirst();
            result = new String[rowCount+1][colunmCount];
            int j = 0;
            while(rs.next()) {
                j++;
                for(int i = 0; colunmCount > i; i++) {
                    String columnName = rsmd.getColumnName(i+1);
                    if(columnName.equals("password")) {
                        int iii = 0;
                    }
                    
                    String value = null;
                    int columnType = rsmd.getColumnType(i+1);
                    if(j == 1) result[0][i] = columnName;
                    if(Types.VARCHAR == columnType || -1 == columnType) { // All unknow type change to String
                        String v = rs.getString(columnName);
                        value = v;
                    } else if(Types.INTEGER == columnType) {
                        int v = rs.getInt(columnName);
                        value = String.valueOf(v);
                    } else if(Types.DATE == columnType) {
                        Date v = rs.getDate(columnName);
                        value = String.valueOf(v);
                    }
                    if(value == null)
                        value = "";
                    result[j][i] = value;
                }
            }
            
        } finally {
            stmt.close();
            conn.close();
        }
        
        return result;
        
    }
    
    
}