JDBC 使用反射的通用操作 第三节

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

实体：Role

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 =