JDBC（三）通用操作

本节主要内容如下：

一、反射编写通用插入

  在使用反射完成数据库通用操作时一般我们需要制定一些约定。假设我们数据库在设计和开发时都满足如下约定：

1.数据库表名和实体名满足下划线与驼峰命名转换规则。

2.数据库字段与实体属性名满足下划线与驼峰命名转换规则。

3.每张表都有一个叫 id 的字段并是该表的自增主键。

4.添加时实体不需要有 id 值，而修改时是必须的。

  满足如上规则后我们便可以编写通用插入方法，该方法接收一个 Object 类型的数据和一个数据库连接，方法会根据对应的数据类型，自动拼接 SQL 并执行插入数据到对应的数据表中。

CREATE TABLE `resources` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime(6) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `order_number` int(11) DEFAULT NULL,
  `update_time` datetime(6) DEFAULT NULL,
  `url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
);
insert  into `resources`(`id`,`create_time`,`name`,`order_number`,`update_time`,`url`) values (1,'2021-08-26 16:46:56.017000','用户添加',1,'2021-08-26 16:46:56.017000','/user/add');
insert  into `resources`(`id`,`create_time`,`name`,`order_number`,`update_time`,`url`) values (2,'2021-08-26 16:47:20.476000','用户修改',2,'2021-08-26 16:47:20.476000','/user/update');
insert  into `resources`(`id`,`create_time`,`name`,`order_number`,`update_time`,`url`) values (3,'2021-08-26 16:47:32.253000','用户查询',3,'2021-08-26 16:47:32.253000','/user/*');
insert  into `resources`(`id`,`create_time`,`name`,`order_number`,`update_time`,`url`) values (4,'2021-08-26 16:47:43.113000','用户删除',4,'2021-08-26 16:47:43.113000','/user/delete/*');

CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime(6) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `remark` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `update_time` datetime(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

insert  into `role`(`id`,`create_time`,`name`,`remark`,`update_time`) values (1,'2021-08-26 16:40:16.035000','超级管理员','拥有全部权限','2021-08-26 16:40:16.036000');
insert  into `role`(`id`,`create_time`,`name`,`remark`,`update_time`) values (2,'2021-08-26 16:40:40.854000','普通用户','就是看看','2021-08-26 16:40:40.854000');

实体：Role

public class Role {
    private Integer id;
    private Date createTime;
    private String name;
    private String remark;
    private Date updateTime;
}

实体：Resources

public class Resources {
    private Integer id;
    private Date createTime;
    private String name;
    private Integer orderNumber;
    private Date updateTime;
    private String url;
}

字符串工具类：StringUtil

public class StringUtil {
    /**
     * 驼峰转下划线
     * @param camelCaseName 驼峰字符串
     * @return 下划线字符串
     */
    public static String underscoreName(String camelCaseName) {
        StringBuffer result = new StringBuffer();
        if (camelCaseName != null && camelCaseName.length() > 0) {
            result.append(camelCaseName.substring(0, 1).toLowerCase());
            //拿到第一字母，直接转小写
            for (int i = 1; i < camelCaseName.length(); i++) {
                char ch = camelCaseName.charAt(i);
                //取第i个位置的字符
                if (Character.isUpperCase(ch)) {//判断该字符是不是大写字母
                    result.append("_");//如果是大写，则拼接下划线
                    result.append(Character.toLowerCase(ch));
                    //将自己转为小写加入result
                } else {
                    result.append(ch);
                }
            }
        }
        return result.toString();
    }
}

代码：

public static void main(String[] args) throws IllegalAccessException, SQLException {
    Connection connection = DataSource.getConnection();
    Role role = new Role();
    role.setName("游客");
    role.setRemark("游客");
    role.setCreateTime(new Date());
    role.setUpdateTime(new Date());
    boolean ok = save(connection, role);

}

private static boolean save(Connection connection, Object object) throws IllegalAccessException, SQLException {
    Class<?> clazz = object.getClass();
    StringBuffer stringBuffer = new StringBuffer("insert  into `" + StringUtil.underscoreName(clazz.getSimpleName()) + "`(");
    Field[] fields = clazz.getDeclaredFields();
    for (Field field : fields) {
        if (!field.getName().equals("id")) {
            stringBuffer.append("`" + StringUtil.underscoreName(field.getName()) + "`,");
        }
    }
    stringBuffer.deleteCharAt(stringBuffer.length() - 1);//删除最后一个逗号
    stringBuffer.append(") values (");
    for (Field field : fields) {
        if (!field.getName().equals("id")) {
            stringBuffer.append("?,");
        }
    }
    stringBuffer.deleteCharAt(stringBuffer.length() - 1);//删除最后一个逗号
    stringBuffer.append(")");
    System.out.println(stringBuffer);
    PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
    for (int i = 0; i < fields.length; i++) {
        if (!fields[i].getName().equals("id")) {
            fields[i].setAccessible(true);
            Object value = fields[i].get(object);
            System.out.println(i + ":" + value);
            statement.setObject(i, value);
        }
    }
    int i = statement.executeUpdate();
    statement.close();
    return i == 1;
}

二、反射编写通用修改

  反射根据 id 修改全部属性。

public static void main(String[] args) throws IllegalAccessException, SQLException, NoSuchFieldException {
    Connection connection = DataSource.getConnection();
    Role role = new Role();
    role.setId(1);
    role.setName("游客");
    role.setRemark("游客");
    role.setCreateTime(new Date());
    role.setUpdateTime(new Date());
    boolean ok = update(connection, role);

}

private static boolean update(Connection connection, Object object) throws IllegalAccessException, SQLException, NoSuchFieldException {
    Class<?> clazz = object.getClass();
    StringBuffer stringBuffer = new StringBuffer("update `" + StringUtil.underscoreName(clazz.getSimpleName()) + "` set ");
    Field[] fields = clazz.getDeclaredFields();
    for (Field field : fields) {
        if (!field.getName().equals("id")) {
            stringBuffer.append("`" + StringUtil.underscoreName(field.getName()) + "`=?,");
        }
    }
    stringBuffer.deleteCharAt(stringBuffer.length() - 1);//删除最后一个逗号
    stringBuffer.append(" where id = ?");
    System.out.println(stringBuffer);
    PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
    for (int i = 0; i < fields.length; i++) {
        if (!fields[i].getName().equals("id")) {
            fields[i].setAccessible(true);
            Object value = fields[i].get(object);
            System.out.println(i + ":" + value);
            statement.setObject(i, value);
        }
    }
    Field id = clazz.getDeclaredField("id");
    id.setAccessible(true);
    statement.setObject(fields.length, id.get(object));
    int i = statement.executeUpdate();
    statement.close();
    return i == 1;
}

三、反射编写通用查询

  反射获取全部数据。

public static void main(String[] args) throws IllegalAccessException, SQLException, InstantiationException {
    Connection connection = DataSource.getConnection();
    List<Role> list = select(connection, Role.class);
    System.out.println(list);
}

private static <T> List<T> select(Connection connection, Class<T> clazz) throws IllegalAccessException, SQLException, InstantiationException {
    StringBuffer stringBuffer = new StringBuffer("select ");
    Field[] fields = clazz.getDeclaredFields();
    for (Field field : fields) {
        stringBuffer.append("`" + StringUtil.underscoreName(field.getName()) + "`,");
    }
    stringBuffer.deleteCharAt(stringBuffer.length() - 1);//删除最后一个逗号
    stringBuffer.append(" from " + StringUtil.underscoreName(clazz.getSimpleName()));
    System.out.println(stringBuffer);
    PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
    ResultSet resultSet = statement.executeQuery();
    ResultSetMetaData metaData = resultSet.getMetaData();//结果集元数据
    List<T> list = new ArrayList<>();
    while (resultSet.next()) {
        T t = clazz.newInstance();//调用无参构造创建对象
        for (Field field : fields) {
            for (int i = 0; i < metaData.getColumnCount(); i++) {

                String columnName = metaData.getColumnName(i + 1);
                if (StringUtil.underscoreName(field.getName()).equals(columnName)) {
                    field.setAccessible(true);
                    field.set(t, resultSet.getObject(columnName));
                }
            }
        }
        list.add(t);
    }
    resultSet.close();
    statement.close();
    return list;
}

练习：

1.按照上面的案例完成数据库常用的 6 种操作。

Role role = findById(connection, Role.class, 2);//获取id为2的数据
boolean ok = deletedById(connection, Role.class, 2);//删除id为2的数据
List<Role> list = page(connection, Role.class, 1, 10);//查询第1页的10条数据
long count = count(connection, Role.class);//返回数据库的总条数

2.在查询时有人思考可以编写更加通用，将 where 条件和 条件中的参数一并传入，完成相应的查询效果。如：

List list = select(connection, Role.class, “where name = ? and create_time < ?”, new Object[]{“测试”, new Date()});

请根据方法提示完成该方法。

参考代码：

private static long count(Connection connection, Class<?> clazz) throws SQLException {
    StringBuffer stringBuffer = new StringBuffer("select count(0) from " + StringUtil.underscoreName(clazz.getSimpleName()));
    PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
    System.out.println(stringBuffer);
    ResultSet resultSet = statement.executeQuery();
    long count = 0;
    if (resultSet.next()) {
        count = resultSet.getLong(1);
    }
    resultSet.close();
    statement.close();
    return count;
}

private static <T> List<T> page(Connection connection, Class<T> clazz, int page, int size) throws SQLException, IllegalAccessException, InstantiationException {
    StringBuffer stringBuffer = new StringBuffer("select ");
    Field[] fields = clazz.getDeclaredFields();
    for (Field field : fields) {
        stringBuffer.append("`" + StringUtil.underscoreName(field.getName()) + "`,");
    }
    stringBuffer.deleteCharAt(stringBuffer.length() - 1);//删除最后一个逗号
    stringBuffer.append(" from " + StringUtil.underscoreName(clazz.getSimpleName()) + " limit " + (page - 1) * size + "," + size);
    System.out.println(stringBuffer);
    PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
    ResultSet resultSet = statement.executeQuery();
    ResultSetMetaData metaData = resultSet.getMetaData();//结果集元数据
    List<T> list = new ArrayList<>();
    while (resultSet.next()) {
        T t = clazz.newInstance();//调用无参构造创建对象
        for (Field field : fields) {
            for (int i = 0; i < metaData.getColumnCount(); i++) {
                String columnName = metaData.getColumnName(i + 1);
                if (StringUtil.underscoreName(field.getName()).equals(columnName)) {
                    field.setAccessible(true);
                    field.set(t, resultSet.getObject(columnName));
                }
            }
        }
        list.add(t);
    }
    resultSet.close();
    statement.close();
    return list;
}

private static boolean deletedById(Connection connection, Class<?> clazz, int id) throws SQLException {
    String sql = "delete from `" + StringUtil.underscoreName(clazz.getSimpleName()) + "` where id=" + id;
    PreparedStatement statement = connection.prepareStatement(sql);
    System.out.println(sql);
    int i = statement.executeUpdate();
    statement.close();
    return i == 1;
}

private static <T> T findById(Connection connection, Class<T> clazz, int id) throws IllegalAccessException, SQLException, InstantiationException {
    StringBuffer stringBuffer = new StringBuffer("select ");
    Field[] fields = clazz.getDeclaredFields();
    for (Field field : fields) {
        stringBuffer.append("`" + StringUtil.underscoreName(field.getName()) + "`,");
    }
    stringBuffer.deleteCharAt(stringBuffer.length() - 1);//删除最后一个逗号
    stringBuffer.append(" from " + StringUtil.underscoreName(clazz.getSimpleName()) + " where id =" + id);
    System.out.println(stringBuffer);
    PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
    ResultSet resultSet = statement.executeQuery();
    ResultSetMetaData metaData = resultSet.getMetaData();//结果集元数据
    T t = null;
    if (resultSet.next()) {
        t = clazz.newInstance();//调用无参构造创建对象
        for (Field field : fields) {
            for (int i = 0; i < metaData.getColumnCount(); i++) {
                String columnName = metaData.getColumnName(i + 1);
                if (StringUtil.underscoreName(field.getName()).equals(columnName)) {
                    field.setAccessible(true);
                    field.set(t, resultSet.getObject(columnName));
                }
            }
        }
    }
    resultSet.close();
    statement.close();
    return t;
}

题二：

private static <T> List<T> select(Connection connection, Class<T> clazz, String whereSql, Object[] objects) throws SQLException, IllegalAccessException, InstantiationException {
    StringBuffer stringBuffer = new StringBuffer("select ");
    Field[] fields = clazz.getDeclaredFields();
    for (Field field : fields) {
        stringBuffer.append("`" + StringUtil.underscoreName(field.getName()) + "`,");
    }
    stringBuffer.deleteCharAt(stringBuffer.length() - 1);//删除最后一个逗号
    stringBuffer.append(" from " + StringUtil.underscoreName(clazz.getSimpleName()) + " " + whereSql);

    System.out.println(stringBuffer);
    PreparedStatement statement = connection.prepareStatement(stringBuffer.toString());
    for (int i = 0; i < objects.length; i++) {
        System.out.println(i + ":" + objects[i]);
        statement.setObject(i + 1, objects[i]);
    }
    ResultSet resultSet = statement.executeQuery();
    ResultSetMetaData metaData = resultSet.getMetaData();//结果集元数据
    List<T> list = new ArrayList<>();
    while (resultSet.next()) {
        T t = clazz.newInstance();//调用无参构造创建对象
        for (Field field : fields) {
            for (int i = 0; i < metaData.getColumnCount(); i++) {
                String columnName = metaData.getColumnName(i + 1);
                if (StringUtil.underscoreName(field.getName()).equals(columnName)) {
                    field.setAccessible(true);
                    field.set(t, resultSet.getObject(columnName));
                }
            }
        }
        list.add(t);
    }
    resultSet.close();
    statement.close();
    return list;
}

四、Stream 递归树形封装

  使用递归完成树型节点数据封装，通常分为使用一次查询后在内存中封装和多次查询子节点封装。

数据如下：

CREATE TABLE `category` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '分类id',
  `name` char(50) DEFAULT NULL COMMENT '分类名称',
  `parent_id` bigint(20) DEFAULT NULL COMMENT '父分类id',
  `level` int(11) DEFAULT NULL COMMENT '层级',
  `show_status` tinyint(4) DEFAULT '1' COMMENT '是否显示[0-不显示，1显示]',
  `sort_number` int(11) DEFAULT '0' COMMENT '排序',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1433 DEFAULT CHARSET=utf8mb4 COMMENT='商品三级分类';

INSERT INTO `category` VALUES (1,'图书、音像、电子书刊',0,1,1,0);
INSERT INTO `category` VALUES (2,'手机',0,1,1,0);
INSERT INTO `category` VALUES (22,'电子书刊',1,2,1,2);
INSERT INTO `category` VALUES (23,'音像',1,2,1,6);
INSERT INTO `category` VALUES (24,'英文原版',1,2,1,1);
INSERT INTO `category` VALUES (25,'文艺',1,2,1,12);
INSERT INTO `category` VALUES (34,'手机通讯',2,2,1,11);
INSERT INTO `category` VALUES (35,'运营商',2,2,1,7);
INSERT INTO `category` VALUES (36,'手机配件',2,2,1,2);
INSERT INTO `category` VALUES (165,'电子书',22,3,1,102);
INSERT INTO `category` VALUES (166,'网络原创',22,3,1,153);
INSERT INTO `category` VALUES (167,'数字杂志',22,3,1,129);
INSERT INTO `category` VALUES (168,'多媒体图书',22,3,1,17);
INSERT INTO `category` VALUES (169,'音乐',23,3,1,32);
INSERT INTO `category` VALUES (170,'影视',23,3,1,108);
INSERT INTO `category` VALUES (171,'教育音像',23,3,1,106);
INSERT INTO `category` VALUES (172,'少儿',24,3,1,35);
INSERT INTO `category` VALUES (173,'商务投资',24,3,1,27);
INSERT INTO `category` VALUES (174,'英语学习与考试',24,3,1,29);
INSERT INTO `category` VALUES (175,'文学',24,3,1,64);
INSERT INTO `category` VALUES (176,'传记',24,3,1,58);
INSERT INTO `category` VALUES (177,'励志',24,3,1,98);
INSERT INTO `category` VALUES (178,'小说',25,3,1,141);
INSERT INTO `category` VALUES (179,'文学',25,3,1,55);
INSERT INTO `category` VALUES (180,'青春文学',25,3,1,26);
INSERT INTO `category` VALUES (181,'传记',25,3,1,149);
INSERT INTO `category` VALUES (182,'艺术',25,3,1,122);
INSERT INTO `category` VALUES (225,'手机',34,3,1,201);
INSERT INTO `category` VALUES (226,'对讲机',34,3,1,103);
INSERT INTO `category` VALUES (227,'合约机',35,3,1,134);
INSERT INTO `category` VALUES (228,'选号中心',35,3,1,137);
INSERT INTO `category` VALUES (229,'装宽带',35,3,1,52);
INSERT INTO `category` VALUES (230,'办套餐',35,3,1,77);
INSERT INTO `category` VALUES (231,'移动电源',36,3,1,0);
INSERT INTO `category` VALUES (232,'电池/移动电源',36,3,1,1);
INSERT INTO `category` VALUES (233,'蓝牙耳机',36,3,1,2);
INSERT INTO `category` VALUES (234,'充电器/数据线',36,3,1,10);
INSERT INTO `category` VALUES (235,'苹果周边',36,3,1,42);
INSERT INTO `category` VALUES (236,'手机耳机',36,3,1,184);
INSERT INTO `category` VALUES (237,'手机贴膜',36,3,1,86);
INSERT INTO `category` VALUES (238,'手机存储卡',36,3,1,113);
INSERT INTO `category` VALUES (239,'充电器',36,3,1,69);
INSERT INTO `category` VALUES (240,'数据线',36,3,1,7);
INSERT INTO `category` VALUES (241,'手机保护套',36,3,1,65);
INSERT INTO `category` VALUES (242,'车载配件',36,3,1,64);
INSERT INTO `category` VALUES (243,'iPhone 配件',36,3,1,127);
INSERT INTO `category` VALUES (244,'手机电池',36,3,1,198);
INSERT INTO `category` VALUES (245,'创意配件',36,3,1,123);
INSERT INTO `category` VALUES (246,'便携/无线音响',36,3,1,18);
INSERT INTO `category` VALUES (247,'手机饰品',36,3,1,213);
INSERT INTO `category` VALUES (248,'拍照配件',36,3,1,22);
INSERT INTO `category` VALUES (249,'手机支架',36,3,1,217);

Stream 代码如下：

public List<Category> queryTree() {
    List<Category> categoryAll = baseDao.selectAll();
    Category category = new Category();
    category.setCatId((long) 0);
    List<Category> list = findSon(category, categoryAll);
    return list;
}

private List<Category> findSon(Category category, List<Category> categoryList) {
    return categoryList
            .stream()
            .filter(c ->
                    c.getParentCid() == category.getId()
            )
            .map(c -> {
                c.setChildren(findSon(c, categoryList));
                return c;
            })
            .sorted(Comparator.comparingInt(Category::getSort)).collect(Collectors.toList());
}