java中访问数据库的封装

一、数据库封装的主体方法，主要是将数据的增删改放在一个函数里面，通过java的隐式参数来进行参数的传递，以及反射的使用，步骤如下:

1.对数据库驱动连接
2.数据库连接
3.数据库的增删改的通用方法
4.单行数据的查询
5.单行单列数据的查询
6.多行数据的查询
7.判断列的类型
8.获取列的数据
9.层次关闭所有连接

/**
 * JDBC的数据访问辅助类
 * @author Administrator
 *
 */
public class JdbcHandler {
    private static String driver;
    private static String url;
    private static String user;
    private static String password;
    static{
        try {
            //加载配置文件
            ResourceBundle bundle = ResourceBundle.getBundle("config");
            //获取配置文件中的数据
            driver = bundle.getString("driver");
            url = bundle.getString("url");
            user = bundle.getString("user");
            password = bundle.getString("password");
            //加载数据库驱动
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    /**
     * 获取数据库连接
     * @return
     */
    public static Connection getConnection(){
        Connection con = null;
        try {
            con = DriverManager.getConnection(url,user,password);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return con;
    }
    /**
     * 关闭数据库对象
     * @param con
     * @param stmt
     * @param rs
     */
    public static void closeAll(Connection con, Statement stmt, ResultSet rs){
        try {
            if(rs != null){
                rs.close();
            }
            if(stmt != null){
                stmt.close();
            }
            if(con != null){
                con.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    /**
     * 增删改查的通用方法
     * @param sql SQL语句
     * @param params 需要注入的参数
     * @return
     */
    public static boolean executeUpdate(String sql, Object[] params){
        //获取连接
        Connection con = getConnection();
        PreparedStatement pstmt = null;
        if(con != null){
            try {
                //创建预处理器
                pstmt = con.prepareStatement(sql);
                //注入参数
                if(pstmt != null){
                    for(int i = 0; i < params.length; i++){
                        if(params[i].getClass() == Date.class){
                            pstmt.setTimestamp(i+1, new Timestamp(((Date)params[i]).getTime()));
                        }
                        else{
                            pstmt.setObject(i+1, params[i]);
                        }
                    }
                }
                return pstmt.executeUpdate() > 0;
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            finally{
                //关闭数据库
                closeAll(con, pstmt, null);
            }
        }
        return false;   
    }
    /**
     * 查询单行数据的方法
     * @param <T>
     * @param sql
     * @param params
     * @return
     */
    public static <T> T executeQueryForSingle(String sql, Object[] params, Class<T> classT){
        //声明要返回的对象类型
        T t = null;
        //获取连接
        Connection con = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        if(con != null){
            try {
                //获取处理器
                pstmt = con.prepareStatement(sql);
                //注入参数
                if(params != null){
                    for(int i = 0; i < params.length; i++){
                        if(params[i].getClass() == Date.class){
                            pstmt.setTimestamp(i+1, new Timestamp(((Date)params[i]).getTime()));
                        }
                        else{
                            pstmt.setObject(i+1, params[i]);
                        }
                    }
                }
                //执行查询
                rs = pstmt.executeQuery();
                //获取元数据
                ResultSetMetaData metaData = rs.getMetaData();
                //解析元数据
                List<ColumnInfo> columnList = JdbcUtil.parseMetaData(metaData);
                //反射获取对象的所有属性
                Field[] fields = classT.getDeclaredFields();
                //反射获取对象的所有方法
                Method[] methods = classT.getMethods();
                //循环读取结果集
                while(rs.next()){
                    //实例化对象
                    t = (T)classT.newInstance();
                    //获取每列的数据
                    executeQueryForColData(columnList,rs,fields,methods,t);
                }//end-while
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (InstantiationException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IllegalArgumentException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            finally{
                //关闭数据库
                closeAll(con, pstmt, rs);
            }
        }

        return t;
    }
    /**
     * 判断列的数据类型,从而获得数据
     * @param type
     * @param index
     * @return
     */
    private static Object validateTypeForData(Object value, ResultSet rs, int type, int index){
        try {
            //判断列的数据类型
            //字符类型
            if(type == OracleTypes.VARCHAR){
                value = rs.getString(index+1);
            }
            //整数
            else if(type == OracleTypes.INTEGER){
                value = rs.getInt(index+1);
            }
            //浮点数
            else if(type == OracleTypes.FLOAT){
                value = rs.getDouble(index+1);
            }
            //数值
            else if(type == OracleTypes.NUMBER){
                BigDecimal num = rs.getBigDecimal(index+1);
                if(num != null){
                    //判断是否是整数
                    if(JdbcUtil.isInteger(num)){
                        value = rs.getInt(index+1);
                    }
                    else{
                        value = rs.getDouble(index+1);
                    }
                }
            }
            //日期
            else if(type == OracleTypes.DATE){
                value = rs.getTimestamp(index+1);
            }
            //字符大对象类型
            else if(type == OracleTypes.CLOB){
                Clob clob = rs.getClob(index+1);
                if(clob != null){
                    //封装成字符缓冲流
                    BufferedReader reader = new BufferedReader(clob.getCharacterStream());
                    StringBuffer buffer = new StringBuffer();
                    String s = "";
                    try {
                        while((s = reader.readLine()) != null){
                            buffer.append(s);
                        }
                        reader.close();
                    } catch (IOException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                    value = buffer.toString();
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return value;
    }
    /**
     * 获取每列的数据
     * @param <T>
     * @param columnList
     * @param rs
     * @param fields
     * @param methods
     * @param t
     */
    private static <T> void executeQueryForColData(List<ColumnInfo> columnList, ResultSet rs, Field[] fields, Method[] methods,T t){
        try {
            //获取每列的数据
            for(int i = 0; i < columnList.size(); i++){
                //获取列
                ColumnInfo col = columnList.get(i);
                //获取列的类型
                int type = col.getColumnType();
                //获取列的名称
                String columnName = col.getColumnName();
                //列的值
                Object value = null;
                //调用方法判断列的类型，从而获取值
                Object newvalue = validateTypeForData(value,rs,type,i);
                //是否存在属性的标识
                boolean hasField = false;
                //列映射的属性名
                String fieldName = null;
                //判断对象中是否包含同名的属性
                if(fields != null){
                    for(int j = 0; j < fields.length; j++){
                        if(fields[j].getName().toUpperCase().equals(columnName)){
                            hasField = true;
                            //记录属性的名称
                            fieldName = fields[j].getName();
                            break;
                        }
                    }
                }
                //判断对象中是否存在对应属性名的setter方法
                if(methods != null && hasField){
                    for(int j = 0; j < methods.length; j++){
                        if(methods[j].getName().equals("set"+JdbcUtil.parseFirstUpper(fieldName))){
                            //执行该方法（将结果集中的列值通过setter方法写入对象）
                            methods[j].invoke(t, newvalue);
                            break;
                        }
                    }
                }
            }//end-for
        } catch (IllegalArgumentException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    /**
     * 查询多行数据的方法
     * @param <T>
     * @param sql
     * @param params
     * @return
     */
    public static <T> List<T> executeQueryForMutiple(String sql, Object[] params, Class<T> classT){
        //声明要返回的对象类型
        T t = null;
        //获取连接
        Connection con = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        //创建集合来存储每一个对象
        List<T> list = new ArrayList<T>();
        if(con != null){
            try {
                //获取处理器
                pstmt = con.prepareStatement(sql);
                //注入参数
                if(params != null){
                    for(int i = 0; i < params.length; i++){
                        if(params[i].getClass() == Date.class){
                            pstmt.setTimestamp(i+1, new Timestamp(((Date)params[i]).getTime()));
                        }
                        else{
                            pstmt.setObject(i+1, params[i]);
                        }
                    }
                }
                //执行查询
                rs = pstmt.executeQuery();
                //获取元数据
                ResultSetMetaData metaData = rs.getMetaData();
                //解析元数据
                List<ColumnInfo> columnList = JdbcUtil.parseMetaData(metaData);
                //反射获取对象的所有属性
                Field[] fields = classT.getDeclaredFields();
                //反射获取对象的所有方法
                Method[] methods = classT.getMethods();

                //循环读取结果集
                while(rs.next()){
                    //实例化对象
                    t = (T)classT.newInstance();
                    //获取每列的数据
                    executeQueryForColData(columnList,rs,fields,methods,t);
                    list.add(t);
                }//end-while
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (InstantiationException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IllegalArgumentException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            finally{
                //关闭数据库
                closeAll(con, pstmt, rs);
            }
        }
        return list;
    }
    /**
     * 查询单行单列数据
     * @param sql
     * @param params
     * @param type
     * @return
     */
    public static Object executeQueryForColRow(String sql, Object[] params,int type){
        //声明要返回的值
        Object value = null;
        Object newvalue = null;
        //获取连接
        Connection con = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        if(con != null){
            try {
                //获取处理器
                pstmt = con.prepareStatement(sql);
                //注入参数
                if(params != null){
                    for(int i = 0; i < params.length; i++){
                        pstmt.setObject(i+1, params[i]);
                    }
                }
                //执行查询
                rs = pstmt.executeQuery();
                //循环读取结果集
                while(rs.next()){
                    newvalue = validateTypeForData(value,rs,type,0);
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return newvalue;
    }
}           

二、辅助方法，主要是返回列及其类型，和一些数据的类型判断。

/**
 * JDBC数据访问工具类
 * @author Administrator
 *
 */
public class JdbcUtil {
    /**
     * 解析元数据的结构
     * @param metaData
     * @return 结果集每个列的数据类型和列名
     */
    public static List<ColumnInfo> parseMetaData(ResultSetMetaData metaData){
        ArrayList<ColumnInfo> list = new ArrayList<ColumnInfo>();
        try {
            for(int i = 0; i < metaData.getColumnCount(); i++){
                ColumnInfo col = new ColumnInfo(metaData.getColumnName(i+1), metaData.getColumnType(i+1));
                list.add(col);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 判断是否是整数
     * @param num
     * @return true表示为int false表示为浮点值
     */
    public static boolean isInteger(BigDecimal num){
        //判断数值是否有小数点
        if(num.toString().indexOf(".") == -1){
            return true;
        }
        return false;
    }

    /**
     * 对首字母进行大写转换的方法
     * @param fieldName
     */
    public static String parseFirstUpper(String fieldName){
        char first = fieldName.charAt(0);
        first = (char)((int)first-32);
        return first+fieldName.substring(1);
    }
    public static Date stringToDate(String time, String pattern) throws ParseException{
        return new SimpleDateFormat(pattern).parse(time);
    }
    public static String dateToString(Date time, String pattern){
        return new SimpleDateFormat(pattern).format(time);
    }
    public static Date getDateByFormat(Date time, String pattern) throws ParseException{
        return stringToDate(dateToString(time,pattern),"yyyy-MM-dd");
    }
    public static Calendar dateTocalendar(Date time){
        Calendar cal = Calendar.getInstance();
        cal.setTime(time);
        return cal;
    }
    public static boolean valitadeIsWeekend(Calendar cal){
        if(cal.get(Calendar.DAY_OF_WEEK)==Calendar.SATURDAY||cal.get(Calendar.DAY_OF_WEEK)==Calendar.SUNDAY)
        {
            return false;
        }
        return true;
    }
}

三、关于列的封装类

public class ColumnInfo {
    private String columnName;
    private int columnType;
    public ColumnInfo(String columnName, int columnType) {
        super();
        this.columnName = columnName;
        this.columnType = columnType;
    }
    public String getColumnName() {
        return columnName;
    }
    public void setColumnName(String columnName) {
        this.columnName = columnName;
    }
    public int getColumnType() {
        return columnType;
    }
    public void setColumnType(int columnType) {
        this.columnType = columnType;
    }
}