从mysql获取数据方法的封装

获取连接对象的封装

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

}