实现简单JDBC工具类

实现简单JDBC工具类

1、查询对象列表

public ArrayList getList(Class clazz) {
        ArrayList arrayList = new ArrayList();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConn();
            Field [] fields = clazz.getDeclaredFields();
            List<String> colList = getAllColumns(clazz.getSimpleName());
            StringBuilder sql = new StringBuilder("select ");
            for (int i = 0; i < colList.size(); i++) {
                sql.append(colList.get(i));
                if (i != colList.size() - 1) {
                    sql.append(", ");
                }
            }
            sql.append(" from ").append(clazz.getSimpleName());
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                Object obj = clazz.newInstance();
                for (int i = 0; i < fields.length; i++) {
                    if (colList.contains(fields[i].getName())) {
                        fields[i].setAccessible(true);
                        fields[i].set(obj, rs.getObject(fields[i].getName()));
                    }
                }
                arrayList.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRes(conn, ps, rs);
        }
        return arrayList;
    }

2、查询单个对象

public Object getOne(Class clazz, int id) {
        Object obj = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConn();
            Field [] fields = clazz.getDeclaredFields();
            List<String> colList = getAllColumns(clazz.getSimpleName());
            StringBuilder sql = new StringBuilder("select ");
            for (int i = 0; i < colList.size(); i++) {
                sql.append(colList.get(i));
                if (i != colList.size() - 1) {
                    sql.append(", ");
                }
            }
            sql.append(" from ").append(clazz.getSimpleName());
            sql.append(" where ").append(colList.get(0)).append(" = ? ");
            ps = conn.prepareStatement(sql.toString());
            ps.setObject(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                obj = clazz.newInstance();
                for (int i = 0; i < fields.length; i++) {
                    if (colList.contains(fields[i].getName())) {
                        fields[i].setAccessible(true);
                        fields[i].set(obj, rs.getObject(fields[i].getName()));
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRes(conn, ps, rs);
        }
        return obj;
    }

3、插入单个实体

public int insert(Object obj) {
        int res = -1;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConn();
            Class clazz = obj.getClass();
            Field [] fields = clazz.getDeclaredFields();
            List<String> colList = getAllColumns(clazz.getSimpleName());
            StringBuilder sql = new StringBuilder("insert into ");
            sql.append(clazz.getSimpleName()).append(" (");
            for (int i = 0; i < fields.length; i++) {
                fields[i].setAccessible(true);
                sql.append(fields[i].getName());
                if (i != fields.length - 1) {
                    sql.append(", ");
                }
            }
            sql.append(") values (");
            for (int i = 0; i < fields.length; i++) {
                fields[i].setAccessible(true);
                sql.append(" ?");
                if (i != fields.length - 1) {
                    sql.append(", ");
                }
            }
            sql.append(")");
            ps = conn.prepareStatement(sql.toString());
            int j = 0;
            for (int i = 0; i < fields.length; i++) {
                j = i + 1;
                if (colList.contains(fields[i].getName())) {
                    fields[i].setAccessible(true);
                    // 这里注意，占位符从1开始，不是从0开始
                    ps.setObject(j, fields[i].get(obj));
                }
            }
            res = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRes(conn, ps);
        }
        return res;
    }

4、更新单个实体

public int update(Object obj) {
        int res = -1;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConn();
            Class clazz = obj.getClass();
            Field [] fields = clazz.getDeclaredFields();
            List<String> colList = getAllColumns(clazz.getSimpleName());
            StringBuilder sql = new StringBuilder("update ");
            sql.append(clazz.getSimpleName());
            sql.append(" set ");
            for (int i = 1; i < fields.length; i++) {
                fields[i].setAccessible(true);
                sql.append(fields[i].getName()).append(" = ? ");
                if (i != fields.length - 1) {
                    sql.append(", ");
                }
            }
            sql.append(" where ");
            sql.append(fields[0].getName()).append(" = ?");
            ps = conn.prepareStatement(sql.toString());
            for (int i = 1; i < fields.length; i++) {
                if (colList.contains(fields[i].getName())) {
                    fields[i].setAccessible(true);
                    // 这里注意，占位符从1开始，不是从0开始
                    ps.setObject(i, fields[i].get(obj));
                }
            }
            // id的位置
            int idIndex = 0;
            fields[idIndex].setAccessible(true);
            ps.setObject(fields.length, fields[idIndex].get(obj));
            res = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRes(conn, ps);
        }
        return res;
    }

5、根据id删除

public int delete(Class clazz, int id) {
        int res = -1;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConn();
            Field [] fields = clazz.getDeclaredFields();
            int idIndex = 0;
            int paramIndex = 1;
            StringBuilder sql = new StringBuilder("delete from ");
            sql.append(clazz.getSimpleName()).append(" where ");
            sql.append(fields[idIndex].getName()).append(" = ? ");
            ps = conn.prepareStatement(sql.toString());
            ps.setObject(paramIndex, id);
            res = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRes(conn, ps);
        }
        return res;
    }

以上方法都是按照数据库字段和实体类属性对应的基础上实现的通用方法，一旦数据库的字段与实体类属性不一致的时候，以上的工具类方法便失效了，这时候，就会出现如hibernate或者jpa的配置实体类映射文件或者注解的方式实现的实体类属性和数据库表字段的一一对应。

最后附上代码地址：https://gitee.com/bzjl/learning/tree/master/