開始準備生成sql
在上一篇裡，我們已經取到了我們在生成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。
我麼先觀察一下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：insert
思路
新增的sql還是比較好實現的，我們需要的大致就是：
- 構建一個物件 User。
- 呼叫新增的方法，將User作為引數傳入方法。
- 通過上一篇的解析結果，拿到所有的欄位名稱，與要儲存的值。生成sql。
- 通過JdbcTemplate執行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()); 複製程式碼
生成sql：where
上一篇裡我們實現了生成insert的sql，下面要開始實現update，delete，select的sql語句了。但是這些語句有一個比較麻煩的地方是：它們一般後面都會有where條件，因為在執行的時候不能把表裡所有的資料都進行操作。
所以這裡我們需要先生成條件的sql。大概是這樣的：
WHERE id = ? AND name != ? OR age >= ?
複製程式碼where 後面的引數繼續用 “?” 代替。值就放在一個有序的集合中就好了。類似上一篇提到的insertColumnValues。
思路
- 條件都是一個一個組成的，我們可以寫一個類用來描述一個條件。
- 寫一個工具類來快速的建立條件。
- 將多個條件中間用 and 或者 or 組合起來，並在最前方新增 where 就是一個完整的條件。
- 最後將這個條件轉成一個字串，並用一個集合將條件中的值存起來就好了。
實現
第一步
我們實現第一步，在這之前我們先看一下一個條件是有什麼組成的，例如:
1: id = ? AND
2: name != ? OR
3: age >= ?
複製程式碼這裡通過觀察可以發現，每一個條件都是由一個 欄位名稱，一個判斷，**一個佔位符 "?"**和後面用於連線條件的 AND 或者 OR 所構成。這樣我們可以編寫一個類用來儲存這些資訊：
Where.java
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 這個類，可以這樣寫：
Wheres.java
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：select
上一篇講了怎樣生成一個sql中where的一部分，之後我們要做事情就簡單很多了，就只要像最開始一樣的生成各種sql語句就好了，之後只要再加上我們需要的條件，一個完整的sql就順利的做好了。
現在我們開始寫生成查詢語句的sql。一個查詢語句大致上是這樣的：
SELECT name, id, create_date, age, mark, status FROM user
複製程式碼這裡可以看出來，一個基礎的查詢語句基本上就是一個 SELECT 後面加上需要查詢的欄位，跟上 FROM 和要查詢的表名稱就好了。 最多後面可能需要加上 ORDER BY/GROUP BY/LIMIT ....之類的就好了，因為比較簡單，這裡就不寫了。（太複雜的就直接寫sql就好了，我自己不需要這種操作）
思路
- 從之前拿到的對映關係中拿到屬性和欄位名的對映，然後拼接sql。
- 執行sql，並取出結果。
- 例項化class，使用反射給class的屬性賦值。
這幾步都還是比較好做的，第一步很簡單，仿照著之前寫的就可以了。因為這裡在執行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：delete
思路
誒呀， 這個太簡單了。不寫了哦～～～
參照我之前寫的，分析一下，想一想思路，然後每一步要怎麼做，一點一點的就寫好了。
～～～
實現
你自己寫咯～～～。
生成sql：update
最後一部分了，馬上就寫完了。寫東西真的好累啊～～～
思路
更新的語句也比較好做，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來做好一點。
完了