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;    }}