编程学习记录10：java数据库的连接

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBHelper {
    static {
        try {
            // 加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据连接
     * 
     * @return 返回一个数据库对象
     */
    public Connection getConnection() {
        Connection con = null;

        try {
            con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "数据库账号", "数据密码");
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return con;
    }

    /**
     * 关闭资源的方法
     * 
     * @param rs
     *            要关闭的结果集
     * @param pstmt
     *            要关闭的预编译执行
     * @param con
     *            要关闭的连接
     */
    private void closeAll(ResultSet rs, PreparedStatement pstmt, Connection con) {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        if (pstmt != null)
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        if (con != null)
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }

    /**
     * 更新数据的方法
     * 
     * @param sql
     *            语句
     * @param params
     *            ？ 的值
     * @return
     */
    public int update(String sql, List<Object> params) {
        Connection con = null;
        PreparedStatement pstmt = null;
        int result = 0;

        try {
            con = this.getConnection();
            pstmt = con.prepareStatement(sql);

            this.setParams(pstmt, params);
            result = pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeAll(null, pstmt, con);
        }
        return result;
    }

    /**
     * 基于对象查询
     * 
     * @param c
     *            要返回的对象类型
     * @param sql
     *            要执行的语句
     * @param params
     *            要执行的更新语句中的占位符 ？
     * @return
     */
    public <T> List<T> findObjects(Class<T> c, String sql, Object... params) {
        List<Method> setters = this.getSetter(c);
        if (setters == null || setters.size() <= 0) {
            return null;
        }

        List<T> list = new ArrayList<T>();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            con = this.getConnection();
            pstmt = con.prepareStatement(sql);
            this.setParams(pstmt, params);
            rs = pstmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            String[] colNames = new String[colCount];
            for (int i = 0; i < colCount; i++) { // 获取变量名
                colNames[i] = rsmd.getColumnName(i + 1);
            }

            Map<String, String> types = new HashMap<String, String>();
            Class<?>[] cls = null;
            for (Method method : setters) { // 获取setter方法中的第一个变量类型
                cls = method.getParameterTypes();
                if (cls != null && cls.length > 0) {
                    types.put(method.getName(), cls[0].getSimpleName());
                }
            }

            T t = null;
            String mName = null;
            String methodName = null;
            String typename = null;

            while (rs.next()) {
                try {
                    t = c.newInstance();
                    for (String colName : colNames) {
                        for (Method method : setters) {
                            methodName = "set" + colName;
                            mName = method.getName();
                            typename = types.get(mName);
                            if (methodName.equalsIgnoreCase(mName)) { // 找到方法
                                if ("int".equals(typename) || "Integer".equals(typename)) {
                                    method.invoke(t, rs.getInt(colName));
                                } else if ("float".equalsIgnoreCase(typename)) {
                                    method.invoke(t, rs.getFloat(colName));
                                } else if ("double".equalsIgnoreCase(typename)) {
                                    method.invoke(t, rs.getFloat(colName));
                                } else if ("byte[]".equals(typename)) {
                                    Blob blob = rs.getBlob(colName);
                                    byte[] bt = null;
                                    if (blob != null) {
                                        blob.getBytes(1, (int) blob.length());
                                    }
                                    method.invoke(t, bt);
                                } else if ("Date".equals(typename)) {
                                    method.invoke(t, rs.getDate(colName));
                                } else {
                                    method.invoke(t, rs.getString(colName));
                                }
                            }
                        }
                    }

                    list.add(t);
                } catch (InstantiationException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (IllegalArgumentException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                }

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;

    }

    /**
     * 基于对象查询
     * 
     * @param c
     *            要返回的对象类型
     * @param sql
     *            要执行的语句
     * @param params
     *            要执行的更新语句中的占位符 ？
     * @return
     */
    public <T> T findObject(Class<T> c, String sql, Object... params) {
        List<Method> setters = this.getSetter(c);
        if (setters == null || setters.size() <= 0) {
            return null;
        }

        T t = null;
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            con = this.getConnection();
            pstmt = con.prepareStatement(sql);
            this.setParams(pstmt, params);
            rs = pstmt.executeQuery();

            ResultSetMetaData rsmd = rs.getMetaData();

            int colCount = rsmd.getColumnCount();
            String[] colNames = new String[colCount];
            for (int i = 0; i < colCount; i++) { // 获取变量名
                colNames[i] = rsmd.getColumnName(i + 1);
            }

            Map<String, String> types = new HashMap<String, String>();
            Class<?>[] cls = null;

            for (Method method : setters) { // 获取setter方法中的第一个变量类型
                cls = method.getParameterTypes();
                if (cls != null && cls.length > 0) {
                    types.put(method.getName(), cls[0].getSimpleName());
                }
            }

            String mName = null;
            String methodName = null;
            String typename = null;
            if (rs.next()) {
                try {
                    t = c.newInstance();
                    for (String colName : colNames) {
                        for (Method method : setters) {
                            methodName = "set" + colName;
                            mName = method.getName();
                            typename = types.get(mName);
                            if (methodName.equalsIgnoreCase(mName)) { // 找到方法
                                if ("int".equals(typename) || "Integer".equals(typename)) {
                                    method.invoke(t, rs.getInt(colName));
                                } else if ("float".equalsIgnoreCase(typename)) {
                                    method.invoke(t, rs.getFloat(colName));
                                } else if ("double".equalsIgnoreCase(typename)) {
                                    method.invoke(t, rs.getFloat(colName));
                                } else if ("byte[]".equals(typename)) {
                                    Blob blob = rs.getBlob(colName);
                                    byte[] bt = null;
                                    if (blob != null) {
                                        blob.getBytes(1, (int) blob.length());
                                    }
                                    method.invoke(t, bt);
                                } else if ("date".equalsIgnoreCase(typename)) {
                                } else {
                                    method.invoke(t, rs.getString(colName));
                                }
                            }
                        }
                    }
                } catch (InstantiationException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (IllegalArgumentException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                }

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return t;

    }

    public <T> T findObject(Class<T> c, String sql, List<Object> params) {
        List<Method> setters = getSetter(c);
        if (setters == null || setters.size() <= 0)
            return null;

        T t = null;
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            con = this.getConnection();
            pstmt = con.prepareStatement(sql);
            this.setParams(pstmt, params);
            rs = pstmt.executeQuery();

            ResultSetMetaData rsmd = rs.getMetaData();

            int colCount = rsmd.getColumnCount();

            // 循环获取的名称并放到数据中
            String[] colNames = new String[colCount];
            for (int i = 0; i < colCount; i++) {
                colNames[i] = rsmd.getColumnName(i + 1);
            }

            // 获取到指定类中的所有setter方法，循环所有的Setter方法，给对应的属性赋值

            Map<String, String> types = new HashMap<String, String>();
            Class<?>[] cls = null;
            for (Method method : setters) {
                cls = method.getParameterTypes();
                if (cls != null && cls.length > 0) {
                    types.put(method.getName(), cls[0].getSimpleName());
                }
            }

            String methodName = null;
            String mName = null;
            String typeName = null;
            if (rs.next()) {
                t = c.newInstance();

                // 根据列名找到对应的setter 方法，并激活此方法，将对应的值注入到 t 对象的对应属性中
                for (String colName : colNames) {
                    for (Method method : setters) {
                        methodName = "set" + colName;
                        mName = method.getName();
                        typeName = types.get(mName);
                        if (methodName.equalsIgnoreCase(mName)) { // 说明对应列注入的方法已经找到，那么激活此方法，将属性值注入
                            if ("int".equals(typeName) || "Integer".equals(typeName)) {
                                method.invoke(t, rs.getInt(colName));
                            } else if ("float".equals(typeName) || "Float".equals(typeName)) {
                                method.invoke(t, rs.getFloat(colName));
                            } else if ("double".equals(typeName) || "Double".equals(typeName)) {
                                method.invoke(t, rs.getDouble(colName));
                            } else if ("byte[]".equals(typeName)) {
                                Blob blob = rs.getBlob(colName);
                                byte[] bt = null;
                                if (blob != null) {
                                    blob.getBytes(1, (int) blob.length());
                                }
                                method.invoke(t, bt);
                            } else {
                                method.invoke(t, rs.getString(colName));
                            }
                        }
                    }
                }

            }
        } catch (InstantiationException e) {

            e.printStackTrace();
        } catch (IllegalAccessException e) {

            e.printStackTrace();
        } catch (SQLException e) {

            e.printStackTrace();
        } catch (IllegalArgumentException e) {

            e.printStackTrace();
        } catch (InvocationTargetException e) {

            e.printStackTrace();
        }

        return t;
    }

    /**
     * 查询数据的方法
     * 
     * @param sql
     *            要执行的语句
     * @param params
     *            要执行的更新语句中的占位符 ？
     * @return List<Map<String,Object>> 类型 里面是已变量名称为键的图
     */
    public List<Map<String, Object>> finds(String sql, Object... params) {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            con = this.getConnection();
            pstmt = con.prepareStatement(sql);
            this.setParams(pstmt, params);

            // 获取结果集
            rs = pstmt.executeQuery();

            // 获取结果集元素对象
            ResultSetMetaData rsmd = rs.getMetaData();

            int colCount = rsmd.getColumnCount();
            String[] colNames = new String[colCount];

            for (int i = 0; i < colCount; i++) {
                colNames[i] = rsmd.getColumnName(i + 1);
            }

            Map<String, Object> map = null;

            while (rs.next()) {
                map = new HashMap<String, Object>();

                for (String colName : colNames) {
                    map.put(colName, rs.getObject(colName));
                }

                list.add(map);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeAll(rs, pstmt, con);
        }
        return list;

    }

    public List<Map<String, String>> findsStr(String sql, Object... params) {
        List<Map<String, String>> list = new ArrayList<Map<String, String>>();

        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            con = this.getConnection();
            pstmt = con.prepareStatement(sql);
            this.setParams(pstmt, params);

            // 获取结果集
            rs = pstmt.executeQuery();

            // 获取结果集元素对象
            ResultSetMetaData rsmd = rs.getMetaData();

            int colCount = rsmd.getColumnCount();
            String[] colNames = new String[colCount];

            for (int i = 0; i < colCount; i++) {
                colNames[i] = rsmd.getColumnName(i + 1);
            }

            Map<String, String> map = null;

            while (rs.next()) {
                map = new HashMap<String, String>();

                for (String colName : colNames) {
                    map.put(colName, String.valueOf(rs.getObject(colName)));
                }

                list.add(map);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeAll(rs, pstmt, con);
        }
        return list;

    }

    /**
     * 给语句中的占位符（？） 赋值
     * 
     * @param pstmt
     *            要赋值的预编译执行块
     * @param params
     *            值的列表
     */
    private void setParams(PreparedStatement pstmt, List<Object> params) {
        if (params != null && params.size() > 0) {
            for (int i = 0, len = params.size(); i < len; i++) {
                try {
                    pstmt.setObject(i + 1, params.get(i));
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private void setParams(PreparedStatement pstmt, Object... params) {
        if (params != null && params.length > 0) {
            for (int i = 0, len = params.length; i < len; i++) {
                try {
                    pstmt.setObject(i + 1, params[i]);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 查询数据的方法
     * 
     * @param sql
     *            要执行的语句
     * @param params
     *            要执行的更新语句中的占位符 ？
     * @return List<Map<String,Object>> 类型 里面是已变量名称为键的图
     */
    public List<Map<String, Object>> finds(String sql, List<Object> params) {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            con = this.getConnection();
            pstmt = con.prepareStatement(sql);
            this.setParams(pstmt, params);
            rs = pstmt.executeQuery();
            // 获取结果集元素数据对象
            ResultSetMetaData rsmd = rs.getMetaData();

            // 获取结果集中列的数量
            int colCount = rsmd.getColumnCount();

            // 循环获取列的名称并存放到数组中
            String[] colNames = new String[colCount];
            for (int i = 0; i < colCount; i++) {
                colNames[i] = rsmd.getColumnName(i + 1);
            }

            Map<String, Object> map = null;
            while (rs.next()) {
                map = new HashMap<String, Object>();

                // 循环结果集中的每一列，获取列这一列的值
                for (String colName : colNames) {
                    map.put(colName, rs.getObject(colName)); // 在这一列中，根据列名获取数据
                }
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeAll(rs, pstmt, con);
        }
        return list;
    }

    private <T> List<Method> getSetter(Class<T> c) {
        if (c == null)
            return null;

        Method[] methods = c.getDeclaredMethods();
        if (methods == null || methods.length <= 0)
            return null;

        List<Method> list = new ArrayList<Method>();

        for (Method method : methods) {
            if (method.getName().startsWith("set")) {
                list.add(method);
            }
        }

        return list;
    }

    /**
     * 基于对象查询
     * 
     * @param c
     *            要返回的对象的类型
     * @param sql
     *            要执行的语句
     * @param params
     *            要执行的更新语句中的占位符 ？
     * @return
     */
    public <T> List<T> findObjects(Class<T> c, String sql, List<Object> params) {
        List<Method> setters = getSetter(c);
        if (setters == null || setters.size() <= 0)
            return null;

        List<T> list = new ArrayList<T>();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            con = this.getConnection();
            pstmt = con.prepareStatement(sql);
            this.setParams(pstmt, params);
            rs = pstmt.executeQuery();

            ResultSetMetaData rsmd = rs.getMetaData();

            int colCount = rsmd.getColumnCount();

            // 循环获取的名称并放到数据中
            String[] colNames = new String[colCount];
            for (int i = 0; i < colCount; i++) {
                colNames[i] = rsmd.getColumnName(i + 1);
            }

            // 获取到指定类中的所有setter方法，循环所有的Setter方法，给对应的属性赋值

            Map<String, String> types = new HashMap<String, String>();
            Class<?>[] cls = null;
            for (Method method : setters) {
                cls = method.getParameterTypes();
                if (cls != null && cls.length > 0) {
                    types.put(method.getName(), cls[0].getSimpleName());
                }
            }

            T t = null;
            String methodName = null;
            String mName = null;
            String typeName = null;
            while (rs.next()) {
                t = c.newInstance();

                // 根据列名找到对应的setter 方法，并激活此方法，将对应的值注入到 t 对象的对应属性中
                for (String colName : colNames) {
                    for (Method method : setters) {
                        methodName = "set" + colName;
                        mName = method.getName();
                        typeName = types.get(mName);
                        if (methodName.equalsIgnoreCase(mName)) { // 说明对应列注入的方法已经找到，那么激活此方法，将属性值注入
                            if ("int".equals(typeName) || "Integer".equals(typeName)) {
                                method.invoke(t, rs.getInt(colName));
                            } else if ("float".equals(typeName) || "Float".equals(typeName)) {
                                method.invoke(t, rs.getFloat(colName));
                            } else if ("double".equals(typeName) || "Double".equals(typeName)) {
                                method.invoke(t, rs.getDouble(colName));
                            } else if ("byte[]".equals(typeName)) {
                                Blob blob = rs.getBlob(colName);
                                byte[] bt = null;
                                if (blob != null) {
                                    blob.getBytes(1, (int) blob.length());
                                }
                                method.invoke(t, bt);
                            } else {
                                method.invoke(t, rs.getString(colName));
                            }
                        }
                    }
                }

                list.add(t);
            }
        } catch (InstantiationException e) {

            e.printStackTrace();
        } catch (IllegalAccessException e) {

            e.printStackTrace();
        } catch (SQLException e) {

            e.printStackTrace();
        } catch (IllegalArgumentException e) {

            e.printStackTrace();
        } catch (InvocationTargetException e) {

            e.printStackTrace();
        }

        return list;
    }

    public static void main(String[] args) {
        DBHelper db = new DBHelper();
        System.out.println(db.update("select * from emp"));
    }

    public int update(String sql, Object... params) {
        Connection con = null;
        PreparedStatement pstmt = null;
        int result = 0;

        try {
            con = this.getConnection();
            pstmt = con.prepareStatement(sql);

            this.setParams(pstmt, params);
            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeAll(null, pstmt, con);
        }
        return result;
    }

    public int updates(String[] sqls, List<List<Object>> params) {
        Connection con = null;
        PreparedStatement pstmt = null;
        int result = 0;

        try {
            con = this.getConnection();
            for (int i = 0, len = sqls.length; i < len; i++) {
                pstmt = con.prepareStatement(sqls[i]);
                this.setParams(pstmt, params.get(i));
                result += pstmt.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeAll(null, pstmt, con);
        }
        return result;
    }

}