package com.hongyuan.db;
 
import java.math.BigDecimal;
import java.net.URL;
import java.sql.Array;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLXML;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
 
import javax.sql.DataSource;
import javax.sql.rowset.CachedRowSet;
 
import com.sun.rowset.CachedRowSetImpl;
 
public class DBUtil {
    private DataSource ds = null;
 
    public DataSource getDs() {
        return ds;
    }
 
    public void setDs(DataSource ds) {
        this.ds = ds;
    }
 
    public DBUtil() {
    }
 
    public DBUtil(DataSource ds) {
        this.ds = ds;
    }
 
    public Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
 
    public static void close(Connection conn, Statement s, ResultSet rs)
            throws SQLException {
        if (rs != null)
            rs.close();
        if (s != null)
            s.close();
        if (conn != null)
            conn.close();
    }
 
    public Object query(String sql) throws SQLException {
        return this.query(sql, null);
    }
 
    public Object query(String sql, List params) throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            ps = conn.prepareStatement(sql);
             
            if (null != params) {
                //初始化查询参数
                this.initParam(ps, params);
            }
            //处理结果集
            boolean isResultSet = ps.execute();
            List result = new ArrayList();
            do {
                if (isResultSet) {
                    CachedRowSet crs = new CachedRowSetImpl();
                    crs.populate(ps.getResultSet());
                    result.add(crs);
                } else {
                    result.add(new Integer(ps.getUpdateCount()));
                }
            } while ((isResultSet = ps.getMoreResults()) == true
                    || ps.getUpdateCount() != -1);
 
            if (result.size() == 0) {
                return null;
            } else if (result.size() == 1) {
                return result.get(0);
            } else {
                return result;
            }
        } catch (SQLException e) {
            throw new SQLException("无法执行的sql语句!");
        } finally {
            DBUtil.close(conn, ps, rs);
        }
    }
    //初始化查询参数
    private void initParam(PreparedStatement ps, List params)
            throws SQLException {
        for (int i = 0; i < params.size(); i++) {
            Object param = params.get(i);
            if (param instanceof Byte) {
                ps.setByte(i + 1, (Byte) param);
            } else if (param instanceof Short) {
                ps.setShort(i + 1, (Short) param);
            } else if (param instanceof Integer) {
                ps.setInt(i + 1, (Integer) param);
            } else if (param instanceof Long) {
                ps.setLong(i + 1, (Long) param);
            } else if (param instanceof Float) {
                ps.setFloat(i + 1, (Float) param);
            } else if (param instanceof Double) {
                ps.setDouble(i + 1, (double) param);
            } else if (param instanceof BigDecimal) {
                ps.setBigDecimal(i + 1, (BigDecimal) param);
            } else if (param instanceof Boolean) {
                ps.setBoolean(i + 1, (Boolean) param);
            } else if (param instanceof String) {
                ps.setString(i + 1, (String) param);
            } else if (param instanceof Time) {
                ps.setTime(i + 1, (Time) param);
            } else if (param instanceof Date) {
                ps.setDate(i + 1, (Date) param);
            } else if (param instanceof Timestamp) {
                ps.setTimestamp(i + 1, (Timestamp) param);
            } else if (param instanceof Array) {
                ps.setArray(i + 1, (Array) param);
            } else if (param instanceof Blob) {
                ps.setBlob(i + 1, (Blob) param);
            } else if (param instanceof Clob) {
                ps.setClob(i + 1, (Clob) param);
            } else if (param instanceof SQLXML) {
                ps.setSQLXML(i + 1, (SQLXML) param);
            } else if (param instanceof URL) {
                ps.setURL(i, (URL) param);
            } else {
                ps.setObject(i + 1, param);
            }
        }
    }
}

package com.hongyuan.db;
 
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
 
import javax.sql.DataSource;
 
import org.junit.BeforeClass;
import org.junit.Test;
 
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
 
public class DBUtilTest {
     
    private static DataSource ds=null;
     
    @BeforeClass
    public static void setUpBeforeClass() throws Exception {
        MysqlDataSource mds=new MysqlDataSource();
        mds.setURL("jdbc:mysql://127.0.0.1:3306/test");
        mds.setUser("root");
        mds.setPassword("123456");
        ds=mds;
    }
 
    @Test
    public void testQuery() throws SQLException {
        DBUtil util=new DBUtil(ds);
        List params=new ArrayList();
        params.add(2);
        Object obj=util.query("select * from emp where id=?",params);
        if(obj instanceof ResultSet){
            ResultSet rs=(ResultSet)obj;
            while(rs.next()){
                for(int i=0;i<rs.getMetaData().getColumnCount();i++){
                    System.out.print(rs.getObject(i+1)+"\t");
                }
                System.out.println();
            }
        }else{
            System.out.println(obj);
        }
    }
 
}