从mysql获取数据方法的封装

来源：互联网发布：漫威电影观看顺序知乎编辑：程序博客网时间：2024/06/05 19:36

获取连接对象的封装

package com.qf.common;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;/*** * 数据库管理类，负责创建连接和关闭资源 *  * @author Administrator * */public class DBManager {    public static String jdbcDriver = "com.mysql.jdbc.Driver";    public static String jdbcUrl = "jdbc:mysql:///shop";    public static String username = "root";    public static String password = "123";    // 得到连接对象    public static Connection getConnection() {        try {            Class.forName(jdbcDriver);            return DriverManager.getConnection(jdbcUrl, username, password);        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (SQLException e) {            e.printStackTrace();        }        return null;    }    // 关闭资源    public static void closeAll(AutoCloseable... autoCloseables) {        for (AutoCloseable autoCloseable : autoCloseables) {            if (autoCloseable != null) {                try {                    autoCloseable.close();                } catch (Exception e) {                    e.printStackTrace();                }            }        }    }}

在Dao层对数据库增删改查方法的封装

/*** * 放一些公共的增删改查方法 *  * @author Administrator * */public class DBUtil<T> {    private static Connection connection = null;    private static PreparedStatement ps = null;    private ResultSet rs = null;    private ResultSetMetaData metaData;    /**     * 增删改操作     *      * @return     */    public static int commonOper(String sql, Object... param) {        // select * from t_user where id = ? and username = ?        // User        try {            // 得到一个连接对象            connection = DBManager.getConnection();            // 开启事务            connection.setAutoCommit(false);            // 得到一个执行对象            ps = connection.prepareStatement(sql);            // 给sql语句的参数赋值            for (int i = 0; i < param.length; i++) {                ps.setObject(i + 1, param[i]);            }            // 执行增删改操作            int result = ps.executeUpdate();            // 提交事务            connection.commit();            System.out.println("执行成功！受影响的行数-->" + result);            return result;        } catch (Exception e) {            e.printStackTrace();            if (connection != null) {                try {                    // 回滚                    connection.rollback();                    connection.setAutoCommit(true);                } catch (SQLException e1) {                    e1.printStackTrace();                }            }        } finally {            // 关闭资源            DBManager.closeAll(ps, connection);        }        return -1;    }    /***     * 查询集合数据     *      * @param sql     * @param clazz     * @param param     * @return     */    public List<T> getList(String sql, Class<T> clazz, Object... param) {        List<T> list = new ArrayList<>();        try {            connection = DBManager.getConnection();            ps = connection.prepareStatement(sql);            for (int i = 0; i < param.length; i++) {                ps.setObject(i + 1, param[i]);            }            rs = ps.executeQuery();            // 得到ResultSetMetaData对象，这个对象包括列的信息(列的名称和类型等等)            metaData = rs.getMetaData();            // 得到结果集中列的个数            int columnCount = metaData.getColumnCount();            while (rs.next()) {                T ins = clazz.newInstance();                for (int i = 1; i <= columnCount; i++) {                    // 得到列名                    String name = metaData.getColumnName(i);                    Object value = rs.getObject(i);                    Field field = clazz.getDeclaredField(name);                    field.setAccessible(true);                    // user.setUsername(name)                    field.set(ins, value);                }                list.add(ins);            }        } catch (Exception e) {            e.printStackTrace();        } finally {            DBManager.closeAll(rs, ps, connection);        }        return list;    }    /**     * 得到单个对象     *      * @param sql     * @param clazz     * @param param     * @return     */    public T getSingleInstace(String sql, Class<T> clazz, Object... param) {        try {            connection = DBManager.getConnection();            ps = connection.prepareStatement(sql);            for (int i = 0; i < param.length; i++) {                ps.setObject(i + 1, param[i]);            }            rs = ps.executeQuery();            metaData = rs.getMetaData();            int columnCount = metaData.getColumnCount();            if (rs.next()) {                T ins = clazz.newInstance();                for (int i = 1; i <= columnCount; i++) {                    // 得到列名                    String columnName = metaData.getColumnName(i);                    // 得到列名对应的数据                    Object value = rs.getObject(i);                    Field field = clazz.getDeclaredField(columnName);                    field.setAccessible(true);                    field.set(ins, value);                }                return ins;            }        } catch (Exception e) {            e.printStackTrace();        } finally {            DBManager.closeAll(rs, ps, connection);        }        return null;    }    /**     * 查询条数     * @return     */    public int getCount(String sql,Object...param){        int count = 0;        try {            connection = DBManager.getConnection();            ps = connection.prepareStatement(sql);            for (int i = 0; i < param.length; i++) {                ps.setObject(i + 1, param[i]);            }            rs = ps.executeQuery();            if(rs.next()){                count = rs.getInt(1);            }        }catch(Exception e){            e.printStackTrace();        }finally {            DBManager.closeAll(rs,ps,connection);        }        return count;    }    /**     * 获取id     * @return     */    public int getId(String sql,Object...param){        int id = 0;        try {            connection = DBManager.getConnection();            ps = connection.prepareStatement(sql);            for (int i = 0; i < param.length; i++) {                ps.setObject(i + 1, param[i]);            }            rs = ps.executeQuery();            if(rs.next()){                id = rs.getInt(1);            }        }catch(Exception e){            e.printStackTrace();        }finally {            DBManager.closeAll(rs,ps,connection);        }        return id;    }    /**     * 获取map数据     */    public Map<Order, List<T>> getOrderDetailList(String sql, Class<T> clazz, Object... param) {        Map<Order, List<T>> map = new HashMap<>();        try {            connection = DBManager.getConnection();            ps = connection.prepareStatement(sql);            for (int i = 0; i < param.length; i++) {                ps.setObject(i + 1, param[i]);            }            rs = ps.executeQuery();            // 得到ResultSetMetaData对象，这个对象包括列的信息(列的名称和类型等等)            metaData = rs.getMetaData();            // 得到结果集中列的个数            int columnCount = metaData.getColumnCount();            while (rs.next()) {                Order order = new Order();                order.setId(rs.getInt("id"));                order.setO_orderdate(rs.getDate("o_orderdate"));                T ins = clazz.newInstance();                for (int i = 1; i <= columnCount; i++) {                    // 得到列名                    String name = metaData.getColumnName(i);                    Object value = rs.getObject(i);                    try {                        Field field = clazz.getDeclaredField(name);                        field.setAccessible(true);                        // user.setUsername(name)                        field.set(ins, value);                    } catch (NoSuchFieldException e) {                        System.out.println(name+"此字段不存在！");                    } catch (IllegalArgumentException e) {                        System.out.println(name+"此字段类型不匹配");                    }                }                List<T> list = map.get(order);                if(list==null){                    list = new ArrayList<>();                }                //把订单详情对象添加到集合中                list.add(ins);                map.put(order, list);            }        } catch (IllegalArgumentException e) {            e.printStackTrace();        } catch (SQLException e) {            e.printStackTrace();        } catch (InstantiationException e) {            e.printStackTrace();        } catch (IllegalAccessException e) {            e.printStackTrace();        } finally {            DBManager.closeAll(rs, ps, connection);        }        return map;    }}

阅读全文

0 0