数据库Java工具类MyUtils

这里是针对MYSQL数据库，使用dbcp数据库连接池，在src目录下有dbcpconfig.properties配置文件，需要MYSQL驱动包和dbcp相关的jar包

    import java.io.InputStream;    import java.lang.reflect.InvocationTargetException;    import java.lang.reflect.Method;    import java.sql.Connection;    import java.sql.DatabaseMetaData;    import java.sql.DriverManager;    import java.sql.PreparedStatement;    import java.sql.ResultSet;    import java.sql.ResultSetMetaData;    import java.sql.SQLException;    import java.sql.Statement;    import java.util.ArrayList;    import java.util.HashMap;    import java.util.List;    import java.util.Map;    import java.util.Properties;        import javax.sql.DataSource;        import org.apache.commons.dbcp.BasicDataSourceFactory;        public class MyUtil {            private static Connection connection=null;                private MyUtil(){}            /**        * return DataSource        * @throws Exception        */        public static DataSource getDataSourceFromPro() throws Exception{            Class.forName("com.mysql.jdbc.Driver");            Properties prop = new Properties();            InputStream is = MyUtil.class.getClassLoader().getResourceAsStream        ("dbcpconfig.properties");            prop.load(is);            return BasicDataSourceFactory.createDataSource(prop);        }                /**        * return Connection from properties        * @throws Exception         */        public static Connection getConnectionFromPro() throws Exception {            return getDataSourceFromPro().getConnection();        }                /**        * return Statement        * @throws Exception         */        public static Statement getStatementFromPro() throws Exception{            return getConnectionFromPro().createStatement();        }                /**        * return PreparedStatement        * @throws Exception         */        public static PreparedStatement getPreStatementFromPro(String sql) throws         Exception{            return getConnectionFromPro().prepareStatement(sql);        }                /**        * return Query ResultSet        * @throws Exception         */        public static ResultSet getResultSetFromPro(String QuerySql) throws         Exception{            return getStatementFromPro().executeQuery(QuerySql);        }                /**        * return ResultSet List        */        public List<Object> getResultList(ResultSet res) {            List<Object> list = new ArrayList<Object>();            try {                ResultSetMetaData md = res.getMetaData();                int columnCount = md.getColumnCount();                while (res.next()){                    Map<Object, Object> rowData=new HashMap<Object,         Object>();                    for(int i=1;i<=columnCount;i++)                        rowData.put(md.getColumnName(i),         res.getObject(i));                    list.add(rowData);                }            } catch (SQLException e) {                e.printStackTrace();            }            return list;        }                /**        * return connection        *       Oracle:"oracle.jdbc.dirver.OracleDriver","jdbc:oracle:thin:@localhost:1521:DBName"        *       Sql:"com.microsoft.jdbc.sqlserver.SQLServerDriver","jdbc:sqlserver://localhost:1433      ;databaseName=数据库名"        * MySQL:"com.mysql.jdbc.Driver","jdbc:sybase:Tds:localhost:5007/erp "        * Sybase:"base.jdbc.SybDriver","jdbc:sybase:Tds:localhost:5007/erp "        * DB2:"com.ibm.db2.app.DB2Driver","jdbc:db2//localhost:5000/testDB"        * Hsql:"org.hsqldb.jdbcDriver","jdbc:hsqldb:hsql://localhost:9902"        *       Informix:"com.informix.jdbc.IfxDriver","jdbc:informixsqli://123.45.67.89:1533/myDB:      INFORMIXSERVER=myserver"        * PostgreSQL:"org.postgresql.Driver","jdbc:postgresql://localhost/testDB"        */        public static Connection getConnection(String driverName,String         dbUrl,String username,String password){            try {                Class.forName(driverName);                Properties properties=new Properties();                properties.put("username", username);                properties.put("password", password);                connection=DriverManager.getConnection(dbUrl,properties);            } catch (ClassNotFoundException e) {                e.printStackTrace();            } catch (SQLException e) {                e.printStackTrace();            }            return connection;        }                /**        * return ResultSet        *         * TABLE_CAT String => 表类别（可为 null）        * TABLE_SCHEM String => 表模式（可为 null）        * TABLE_NAME String => 表名称        * TABLE_TYPE String => 表类型。        * REMARKS String => 表的解释性注释        * TYPE_CAT String => 类型的类别（可为 null）        * TYPE_SCHEM String => 类型模式（可为 null）        * TYPE_NAME String => 类型名称（可为 null）        * SELF_REFERENCING_COL_NAME String => 有类型表的指定 "identifier" 列的名称（      可为 null）        * REF_GENERATION String        * SELF_REFERENCING_COL_NAME String => name of the designated "identifier"       column of a typed table (may be null)         * REF_GENERATION String => specifies how values in       SELF_REFERENCING_COL_NAME are created. Values are "SYSTEM", "USER", "DERIVED". (may       be null)         */        public static List<Object> getAllTablesInfo(Connection conn){            List<Object> list=new ArrayList<Object>();            ResultSet resTable=null;            try {                DatabaseMetaData dbMetaData=conn.getMetaData();                resTable=dbMetaData.getTables(conn.getCatalog(), null, "%",         null);                ResultSetMetaData rMetaData=resTable.getMetaData();                int table_count=rMetaData.getColumnCount();                while(resTable.next()){                    Map<Object, Object> map=new HashMap<Object,         Object>();                    for(int i=1;i<table_count;i++)                        map.put(rMetaData.getColumnName(i),         resTable.getString(i));                    list.add(map);                }            } catch (Exception e) {                e.printStackTrace();            }            return list;        }                /**        * return all tables name        */        public static List<Object> getAllTablesNmae(Connection conn){            List<Object> list=new ArrayList<Object>();            ResultSet resTable=null;            try {                DatabaseMetaData dbMetaData=conn.getMetaData();                resTable=dbMetaData.getTables(conn.getCatalog(), null, "%",         null);                while(resTable.next()){                    list.add(resTable.getString(3));                    /**                    * 元数据的列分别为：TABLE_CAT、TABLE_SCHEM、      TABLE_NAME、TABLE_TYPE、REMARKS                    */                }            } catch (Exception e) {                e.printStackTrace();            }            return list;        }                    /**        * @param <T>        * @param sql        * @param clazz        * @return        * @throws SQLException        * @throws Exception        * @throws IllegalAccessException        * @throws InvocationTargetException        */        public static <T> List <T> getResultList(String sql,Object[] params,         Class<T> clazz)                throws SQLException, Exception, IllegalAccessException,                InvocationTargetException {                Connection ct = null;                PreparedStatement ps = null;                ResultSet rs = null;                try {                    ct = getConnectionFromPro();                    ps = ct.prepareStatement(sql);                    for (int i = 0; i < params.length; i++){                        ps.setObject(i + 1, params[i]);                    }                    rs = ps.executeQuery();                    String[] colNames = getColNames(rs);                                    List<T> objects = new ArrayList<T>();                    Method[] methods = clazz.getMethods();                    while (rs.next()) {                        T object = clazz.newInstance();                        for (int i = 0; i < colNames.length; i++) {                            String colName = colNames[i];                            String methodName = "set" +         colName;                            for (Method m : methods) {                                if (methodName.equals        (m.getName())) {                                    m.invoke(object,         rs.getObject(colName));                                    break;                                }                            }                            objects.add(object);                        }                    }                    return objects;                } finally {                    close(rs, ps, ct);                }            }                        /**            * return all column name            */            public static String[] getColNames(ResultSet res) throws         SQLException {                ResultSetMetaData rsmd = res.getMetaData();                int count = rsmd.getColumnCount();                String[] colNames = new String[count];                for (int i = 1; i <= count; i++) {                    colNames[i - 1] = getUpper(rsmd.getColumnLabel(i));                }                return colNames;            }                                    private static String getUpper(String column){                return column.substring(0, 1).toUpperCase        ()+column.substring(1);            }                        /**            * return rows number            */            public static int getRowssNumber(ResultSet res){                int rows=0;                try {                    res.last();                    rows=res.getRow();                } catch (Exception e) {                    e.printStackTrace();                }                return rows;            }                        /**            */            public static <T> T getSingleResult(String sql,Object[] params,         Class<T> clazz) throws SQLException,                Exception, IllegalAccessException,         InvocationTargetException {                Connection ct = null;                PreparedStatement ps = null;                ResultSet rs = null;                try {                    ct = getConnectionFromPro();                    ps = ct.prepareStatement(sql);                                        for (int i = 0; i < params.length; i++){                        ps.setObject(i + 1, params[i]);                    }                                        rs = ps.executeQuery();                    String[] colNames = getColNames(rs);                                    T object = null;                    Method[] methods = clazz.getMethods();                    if (rs.next()) {                        object = clazz.newInstance();                         for (int i = 0; i < colNames.length; i++) {                            String colName = colNames[i];                            String methodName = "set" +         colName;                            for (Method m : methods) {                                if (methodName.equals        (m.getName())) {                                    m.invoke(object,         rs.getObject(colName));                                    break;                                }                            }                        }                    }                    return object;                } finally {                    close(rs, ps, ct);                }            }                                    /**            * @param rs            * @param st            * @param ct            */            public static void close(ResultSet res, Statement state, Connection         connection) {                try {                    if (res != null)                        res.close();                } catch (SQLException e) {                    e.printStackTrace();                } finally {                    try {                        if (state != null)                            state.close();                    } catch (SQLException e) {                        e.printStackTrace();                    } finally {                        if (connection != null)                            try {                                connection.close();                            } catch (Exception e) {                                e.printStackTrace();                            }                    }                }            }        }