在上一篇里,我们已经取到了我们在生成sql语句中所需要的信息,这一篇里我们开始根据class来生成我们需要的sql。在这之前我们先确认几件事情
sql里的参数我们使用占位符的形式。
这里用的是jdbc中的 PreparedStatement ,sql中的参数使用“ ? ”的形式。
大致上是这样的:
Connection connection = dataSource.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement("select * from `user` where `status` = ? ;"); preparedStatement.setObject(1, 0); ResultSet resultSet = preparedStatement.executeQuery(); 复制代码
但是这样的话我们每次执行都需要手写这些执行sql的繁琐的代码,我在这里选择使用 spring-jdbc 中的 JdbcTemplte 。这样我就只需要生成sql,然后使用 JdbcTemplte 里的方法来执行sql就好了。
我们只生成 单表 的增删改查,不涉及复杂sql。
不贴出完整的代码,以说明思路为主。
毕竟这个是已经写好的代码,地址在: github.com/hjx60149632… 。所有代码可以在这里找到。
我们主要解决的是增删该查的问题,所以我们先写如何生成一个新增的sql。
我么先观察一下sql一般来说都有什么构成。现在先放一个例子出来:
insert
INSERT INTO user (name, id, create_date, age, mark, status) VALUES (?, ?, ?, ?, ?, ?); 复制代码
delete
DELETE FROM user WHERE id = ? 复制代码
update
UPDATE user SET name = ?, id = ?, create_date = ?, age = ?, status = ? WHERE id = ? 复制代码
select
SELECT name, id, create_date, age, mark, status FROM user WHERE id = ? 复制代码
通过观察上面的sql,可以发现其中有一些共性:
接下来,就可以按照每种类型的sql来创建sql了。
一下所有的对象都是这个 User.java
import javax.persistence.Column; import javax.persistence.Id; import javax.persistence.Table; import java.util.Date; @Table(name = "user") public class User { @Column(name = "name") private String name; @Id @Column(name = "id") private int id; @Column(name = "age") private int age; @Column(name = "mark") private String mark; @Column(name = "create_date") private Date createDate; @Column(name = "status") private int status; // getter setter toString } 复制代码
主要用来操作字符串
import java.util.Collection; import java.util.Iterator; /** * @author hjx */ public class StringUtils { public static final String SPACE = " "; public static final String BLANK = ""; public static final String COMMA = ", "; /** * 重复字符串 * * @param str * @param number * @return */ public static String[] repeat(String str, int number) { Assert.notNull(str); String[] strings = new String[number]; for (int i = 0; i < number; i++) { strings[i] = str; } return strings; } /** * 组合字符串 * * @param strings * @return */ public static String append(final Object... strings) { StringBuilder builder = new StringBuilder(); for (Object s1 : strings) { if (s1 == null) { continue; } builder.append(s1.toString()); } return builder.toString(); } /** * 组合字符串 * * @param collection * @param separator * @return */ public static String join(Collection collection, String separator) { StringBuffer var2 = new StringBuffer(); for (Iterator var3 = collection.iterator(); var3.hasNext(); var2.append((String) var3.next())) { if (var2.length() != 0) { var2.append(separator); } } return var2.toString(); } } 复制代码
用来从对象中取值的,使用反射。
/** * 取值 * * @param target 要从哪一个对象中取值 * @param field 要取这个对象的那个属性的值 * @return */ public static Object getValue(Object target, Field field) { //忽略掉private field.setAccessible(true); try { return field.get(target); } catch (IllegalAccessException e) { e.printStackTrace(); } return null; } 复制代码
用来给对象设置值的,还是反射。
/** * 设置值 * * @param target 要从哪一个对象中取值 * @param field 要取这个对象的那个属性的值 * @param value 要设置的值 * @return */ public static boolean setValue(Object target, Field field, Object value) { field.setAccessible(true); try { field.set(target, value); return true; } catch (IllegalAccessException e) { e.printStackTrace(); } return false; } 复制代码
下面就可以开始创建各种sql了~~~
新增的sql还是比较好实现的,我们需要的大致就是:
首先我们要根据 User.java 拿到所有的表的字段个名称,和对应的值。就是上一篇写到的: EntityTableRowMapper
拿到字段和class属性的值
Map<String, Field> columnFieldMapper = entityTableRowMapper.getColumnFieldMapper(); insertColumns = new ArrayList(columnFieldMapper.size()); for (Map.Entry<String, Field> stringFieldEntry : columnFieldMapper.entrySet()) { Field field = stringFieldEntry.getValue(); Object value = EntityUtils.getValue(entity, field); if (value == null) { continue; } insertColumns.add(stringFieldEntry.getKey()); insertColumnValues.add(value); } 复制代码
这里有两个变量:
insertColumns:sql中的字段名。
insertColumnValues:sql中的字段对应的值。
生成插入的sql:
StringBuilder builder = new StringBuilder(); int size = insertColumns.size(); builder.append("INSERT INTO ").append(getTableName()).append(StringUtils.SPACE); builder.append(StringUtils.append("( ", StringUtils.join(insertColumns, ", "), " ) ")); builder.append("VALUES "); for (int i = 0; i < insertCount; i++) { builder.append("( "); String[] repeat = StringUtils.repeat("?", size); builder.append(StringUtils.join(Arrays.asList(repeat), ", ")); builder.append(" )"); if (i != insertCount - 1) { builder.append(StringUtils.COMMA); } } builder.append(";"); 复制代码
生成的结果:
//user User user = new User(); user.setId(10); user.setCreateDate(new Date()); user.setAge(20); user.setMark("ceshi"); user.setName("heiheihei"); //sql INSERT INTO user ( name, id, create_date, age, mark, status ) VALUES ( ?, ?, ?, ?, ?, ? ); //value [heiheihei, 10, Tue Jan 22 16:33:00 CST 2019, 20, ceshi, 0] 复制代码
现在可以拿着生成的sql和值去执行啦~
jdbcTemplate.update(sql, insertColumnValues.toArray()); 复制代码
上一篇里我们实现了生成insert的sql,下面要开始实现 update , delete , select 的sql语句了。但是这些语句有一个比较麻烦的地方是:它们一般后面都会有 where 条件,因为在执行的时候不能把表里所有的数据都进行操作。
所以这里我们需要先生成条件的sql。大概是这样的:
WHERE id = ? AND name != ? OR age >= ? 复制代码
where 后面的参数继续用 “ ? ” 代替。值就放在一个有序的集合中就好了。类似上一篇提到的 insertColumnValues 。
我们实现第一步,在这之前我们先看一下一个条件是有什么组成的,例如:
1: id = ? AND 2: name != ? OR 3: age >= ? 复制代码
这里通过观察可以发现,每一个条件都是由一个 字段名称 , 一个判断 ,**一个占位符 "?"**和后面用于连接条件的 AND 或者 OR 所构成。这样我们可以编写一个类用来保存这些信息:
import java.util.ArrayList; import java.util.Arrays; import java.util.List; /** * where条件 默认使用 and 连接多个条件 * * @author hjx */ public class Where { protected static final String PLACEHOLDER = "#{COLUMN}"; static final String AND = "AND "; static final String OR = "OR "; private String sql; private String column; private String connect = AND; private List<Object> values; /** * 是否有值(null 也代表有值) */ private boolean hasValue; /** * @param column 被操作的列 * @param sql 操作的sql */ public Where(String column, String sql) { this.column = column; this.sql = sql; this.hasValue = false; this.values = new ArrayList<>(); } /** * @param column 被操作的列 * @param sql 操作的sql * @param value sql的参数 */ public Where(String column, String sql, Object value) { this.sql = sql; this.column = column; this.values = new ArrayList<>(); this.values.add(value); this.hasValue = true; } /** * @param column 被操作的列 * @param sql 操作的sql * @param values sql的参数 */ public Where(String column, String sql, Object[] values) { this.sql = sql; this.column = column; this.values = Arrays.asList(values); this.hasValue = true; } public Where or() { this.connect = OR; return this; } public Where and() { this.connect = AND; return this; } /** * 获取本次条件的连接符 * * @return */ public String getConnect() { return connect; } protected String getSql() { return sql; } protected boolean isHasValue() { return hasValue; } protected List<Object> getValues() { return values; } public String getColumn() { return column; } } 复制代码
上面中的常量 PLACEHOLDER 是作为一个占位符使用的,下面会说道。
这样,一个用于保存单个条件的类就写好了,在一个sql中有多个条件的话,只需要用一个 ArrayList 保存这些条件,并按照一定的条件拼装成sql就好了。
sql中还有一些比较常用的判断,比如: != , = , <= , >= 等等,我们在这里可以创建一个工具类来快速的生成 Where 这个类,可以这样写:
import java.util.Arrays; /** * 查询条件 * @author hjx */ public class Wheres { public static Where equal(final String columnName, final Object value) { return new Where(columnName, Where.PLACEHOLDER + " = ? ", value); } public static Where notEqual(final String columnName, final Object value) { return new Where(columnName, Where.PLACEHOLDER + " != ? ", value); } public static Where not(final String columnName, final Object value) { return new Where(columnName, Where.PLACEHOLDER + " <> ? ", value); } public static Where isNotNull(final String columnName) { return new Where(columnName, Where.PLACEHOLDER + " IS NOT NULL "); } public static Where isNull(final String columnName) { return new Where(columnName, Where.PLACEHOLDER + " IS NULL "); } public static Where greater(final String columnName, final Object value, final boolean andEquals) { if (andEquals) { return new Where(columnName, Where.PLACEHOLDER + " >= ? ", value); } return new Where(columnName, Where.PLACEHOLDER + " > ? ", value); } public static Where less(final String columnName, final Object value, final boolean andEquals) { if (andEquals) { return new Where(columnName, Where.PLACEHOLDER + " <= ? ", value); } return new Where(columnName, Where.PLACEHOLDER + " < ? ", value); } public static Where like(final String columnName, final Object value) { return new Where(columnName, Where.PLACEHOLDER + " like ? ", value); } public static Where betweenAnd(final String columnName, final Object value1st, final Object value2nd) { return new Where(columnName, Where.PLACEHOLDER + " between ? and ? ", new Object[]{value1st, value2nd}); } public static Where in(final String columnName, final Object[] values) { Object[] sqlVal = values; if (sqlVal.length == 0) { sqlVal = new Object[]{null}; } StringBuffer inSql = new StringBuffer(); inSql.append(Where.PLACEHOLDER); inSql.append(" IN ( "); String[] strings = StringUtils.repeat("?", sqlVal.length); inSql.append(StringUtils.join(Arrays.asList(strings), ", ")); inSql.append(" ) "); return new Where(columnName, inSql.toString(), sqlVal); } } 复制代码
这里只是简单的列出了一些常用的判断条件,如果有特殊需要的自己再加进去就好了。
关于常量 PLACEHOLDER 是这么一回事:
在生成sql 的时候,我需要做一些字段上的验证。这里在sql中使用一个占位符放进sql中,真正参与条件的字段放在另外一个属性中保存。这样在真正生成sql的时候可以验证条件中的字段在不在表中,如果存在的话将字段和占位符进行替换就好了。并且如果使用的是属性名称的话,也可以根据名称找到对应的表的字段名。
通过上面的代码,我们可以很方便的创建条件了。现在我们将这些条件组装成我们需要的完整的sql。
注意:这里的代码可能和我的github上的不太一样,因为这里只讲一下思路,具体的怎么将所有的代码组装起来让它成为一个完整的项目,每个人都不一样。所以~~~ 嘿嘿。
现在开始:
我们还是以之前写的 User.java 为例子
List<Where> wheres = Arrays.asList( Wheres.equal("name", "李叔叔"), Wheres.notEqual("status", 1), Wheres.in("age", new Integer[]{1, 2, 3, 4, 5}), Wheres.greater("age", 20, true) ); List<Object> sqlValue = new ArrayList<>(); StringBuilder sql = new StringBuilder(); if (wheres.size() != 0) { sql.append("WHERE "); for (int i = 0; i < wheres.size(); i++) { Where where = wheres.get(i); if (i != 0) { sql.append(where.getConnect()); } String column = where.getColumn(); String whereSql = where.getSql(); sql.append( //这里获取真实sql whereSql.replace(Where.PLACEHOLDER, getColumnName(column)) ); //因为有些条件中的参数可能是有多个 List<Object> values = where.getValues(); for (int j = 0; j < values.size(); j++) { sqlValue.add(values.get(j)); } } } System.out.println(sql.toString()); System.out.println(sqlValue.toString()); 复制代码
这里说明一下: getColumnName(String name) ,这个方法是根据参数获取真正的字段名称的方法。因为这个条件中可能传入的是java属性的名称而不是表的字段名称,需要转换成为真正的表的字段名。这一步也是从之前生成的映射中获取的。顺便还能验证一下表中有没有这个字段。这个方法我就不贴出来了,github上有。
输出结果:
WHERE name = ? AND status != ? AND age IN ( ?, ?, ?, ?, ? ) AND age >= ? [李叔叔, 1, 1, 2, 3, 4, 5, 20] 复制代码
这里一个 where 就写好了,并且也可以拿到条件中的参数了。
剩下的就是后面的单独生成 update , delete , select 类型sql的操作了。
上一篇讲了怎样生成一个 sql 中 where 的一部分,之后我们要做事情就简单很多了,就只要像最开始一样的生成各种 sql 语句就好了,之后只要再加上我们需要的条件,一个完整的 sql 就顺利的做好了。
现在我们开始写生成查询语句的 sql 。一个查询语句大致上是这样的:
SELECT name, id, create_date, age, mark, status FROM user 复制代码
这里可以看出来,一个基础的查询语句基本上就是一个 SELECT 后面加上需要查询的字段,跟上 FROM 和要查询的表名称就好了。 最多后面可能需要加上 ORDER BY / GROUP BY / LIMIT ....之类的就好了,因为比较简单,这里就不写了。(太复杂的就直接写sql就好了,我自己不需要这种操作)
这几步都还是比较好做的,第一步很简单,仿照着之前写的就可以了。因为这里在执行sql的时候,我使用的是 JdbcTemplate ,这里有一个不大不小的坑,下面我说一下。
这个坑是我在使用我写好的这个项目给公司做报表的时候碰到的。原因是这样,因为数据库中有些字段是datetime类型的,这个字段有时候在表中的值是: 0000-00-00 00:00:00 ,(我也不知道这个值是怎么进去的,但是就是存在/(ㄒoㄒ)/~~)但是这个值是无法转换成为 java 中的 Date 类型。所以这里会报错。
我在这里写了一个继承 SpringJdbc 中的 ColumnMapRowMapper 的类,是这样的:
import org.springframework.jdbc.core.ColumnMapRowMapper; import java.sql.ResultSet; import java.sql.SQLException; /** * 捕获取值的错误 * * @author hjx */ public class PlusColumnMapRowMapper extends ColumnMapRowMapper { /** * 数据库类型为时间时, 如果值为 0000-00-00 00:00:00 * 会报错,所以重写此方法,返回null * * @param rs * @param index * @return * @throws SQLException */ @Override protected Object getColumnValue(ResultSet rs, int index) throws SQLException { Object columnValue = null; try { columnValue = super.getColumnValue(rs, index); } catch (SQLException e) { e.printStackTrace(); } return columnValue; } } 复制代码
这个类具体在哪里使用,会在下面说明。
现在说一下怎么实现上面的思路,首先因为第一步比较简单,就不写了。我直接从第二步开始。
执行sql,并取出结果。
这里我用的是 JdbcTemplate 的方法,这给我们提供了一个方法:
<T> List<T> query(String sql, Object[] args, RowMapper<T> rowMapper) 复制代码
这里前两个参数比较好理解,一个是sql,一个是sql中的参数。第三个是需要传一个接口 RowMapper ,这个接口具体是干啥的上网一查就知道了~~~
这里面有一个方法:
T mapRow(ResultSet rs, int rowNum) throws SQLException 复制代码
第一个参数是查询的结果,第二个是指现在在第几行结果,返回值是你要返回什么对象。这里我们需要重写这个方法,把查询出的结果转换成为我们需要的对象。我们可以这么写:
/** * 把数据库查询的结果与对象进行转换 * * @param resultSet * @param rowNum * @return * @throws SQLException */ @Override public T mapRow(ResultSet resultSet, int rowNum) throws SQLException { Map<String, Object> resultMap = columnMapRowMapper.mapRow(resultSet, rowNum); 。。。。 复制代码
这个方法中的 columnMapRowMapper 就是上面我们写的 PlusColumnMapRowMapper ,它的作用就是将查询结果第 rowNum 拿出来,并且将结果转换过成为一个 Map<String, Object> 。其中:
key:是表字段名称。
Object:该字段的值。
上面写的 PlusColumnMapRowMapper 主要作用就是在获取值的时候如果发生异常,返回一个 null 。
在这一步里我们已经拿到了执行sql的结果,现在我们要将结果转换过为我们需要的class。
将结果转换为class
在上一步我们拿到了存放结果Map,现在只需要将map遍历一下,然后实例化java对象,根据字段和属性的映射关系使用反射将属性一个个的set进去就好了。现在贴上上一步的完整代码:
public T mapRow(ResultSet resultSet, int rowNum) throws SQLException { Map<String, Object> resultMap = columnMapRowMapper.mapRow(resultSet, rowNum); T instance = getInstance(tableClass); for (Map.Entry<String, Object> entry : resultMap.entrySet()) { //数据库字段名 String key = entry.getKey(); if (!columnFieldMapper.containsKey(key)) { continue; } Field declaredField = columnFieldMapper.get(key); if (declaredField == null) { continue; } //数据库字段值 Object value = entry.getValue(); setFieldValue(instance, declaredField, value); } return instance; } 复制代码
其中 columnFieldMapper 是一个 Map<String, Field> 。 key 是表的字段个名称。 value 是对应的class的属性。
下面是 setFieldValue 的具体代码:
boolean setFieldValue(T t, Field field, Object value) { field.setAccessible(true); try { if (value != null) { field.set(t, value); return true; } } catch (IllegalAccessException e) { e.printStackTrace(); } return false; } 复制代码
这样,就可以将查询出的结果根据映射关系转换成为我们需要的class了。
如果查询需要添加条件的话,可以使用之前讲的 生成条件的工具将条件的sql拼接在这里的sql后面,相应的,where里的参数也要按照顺序添加进数组就好了。
相同的,如果要添加 ORDER BY / GROUP BY / LIMIT 这些东西的话也是一样的操作。主要还是要看自己的代码是怎么设计的了。我自己用的只写了 ORDER BY 和 LIMIT 。可以在我的github上找到。地址在这里: github.com/hjx60149632… 。
诶呀, 这个太简单了。不写了哦~~~
参照我之前写的,分析一下,想一想思路,然后每一步要怎么做,一点一点的就写好了。
~~~
你自己写咯~~~。
最后一部分了,马上就写完了。写东西真的好累啊~~~
更新的语句也比较好做,sql后面的条件因为在之前已经写了 where 这一篇,所以这里就只写 sql 中 where 左边的一部分。现在还是先分析一下 **update **语句:
UPDATE user SET name = ? , id = ? , create_date = ? , age = ? , status = ? WHERE id = ? 复制代码
可以看到的,大体上就是 UPDATE 表名称 SET 字段名称 = ? 这个样子的。( 因为现在不写WHERE右边的 )
所以具体的思路就是:
根据映射关系拼装sql。
这里可能有一个可以选择的地方,就是如果某一个属性的值是 null ,这时要不要把这个属性更新为 null 。
拿到要更新的值。
执行sql。
从映射中拿到所有的属性。
这一步的代码就不放了~~~,和前面写的没有什么区别。
拿到要更新的属性名称,和值。
这里我们需要三个参数:
1:用来标示更新的时候是否需要忽略值是 null 的属性。 boolean ignoreNull
2:用来保存需要更新的字段的有序集合。 List updataColumn
3:保存需要更新的字段的值的有序集合。 List values
代码是这样的:
List<String> columnNames = new ArrayList<>(entityTableRowMapper.getColumnNames()); Map<String, Field> columnFieldMapper = entityTableRowMapper.getColumnFieldMapper(); List<Object> values = new ArrayList<>(); for (int i = 0; i < columnNames.size(); i++) { String columnName = columnNames.get(i); if (!sqlColumns.contains(columnName)) { continue; } Field field = columnFieldMapper.get(columnName); Object value = EntityUtils.getValue(entity, field); //如果class中的值是null,并且设置忽略null,跳过 if (ignoreNull && value == null) { continue; } updataColumn.add(columnName); values.add(value); } 复制代码
根据拿到的数据拼装sql
拿到上面需要的数据后,我们还需要拿到表的名称,这一步直接从映射关系中取就好了。下面的是拼装sql的代码:
StringBuilder sql = new StringBuilder(); sql.append("UPDATE ").append(getTableName()).append(StringUtils.SPACE); sql.append("SET "); for (int i = 0; i < updataColumn.size(); i++) { String column = updataColumn.get(i); if (i == 0) { sql.append(StringUtils.append(column, " = ? ")); } else { sql.append(StringUtils.append(", ", column, " = ? ")); } } 复制代码
这样就好了,大致上是这样的:
UPDATE user SET name = ? , id = ? , create_date = ? , age = ? , status = ? 复制代码
条件的话,用之前写的 where 生成就好了, where 中的值加在集合 values 的后面就好了。
执行sql。
太简单了,就不写了~
终于写完了。
还是说一下,因为代码已经在github上了,所以没有把全部的代码写在上面,主要还是以说明思路为主。另外刚开始写博客,有些可能表达的不是很明白。吃了没文化的亏啊~~~
这个项目还有很多可以但是还没有实现的功能,比如一些比较复杂的查询,执行函数之类的。我并没去写它。一是不需要,因为这个东西平时主要是做导出报表的时候用的,二是我自己写项目的话压根就不会用到这些东西,能用java写的我都用java写了。数据库嘛,对我来说就存个数据就好了,数据处理上的事情还是交给java来做好一点。