通用操作

3.通用操作

本节主要内容如下：

1. 反射编写通用插入
2. 反射编写通用修改
3. 反射编写通用查询

反射编写通用插入

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

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 = 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<Role> 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;

}