通常我們在開發Java企業級應用的時候使用的技術大部分是Spring、Hibernate、mybatis、Struts2等。尤其是Spring，相信這個龐大而優雅的技術體系你已經離不開了，在我們專案程式碼中基本是骨幹力量的存在。
而我們使用的ORM框架大多數也是Hibernate、mybatis或者Spring提供的jdbc簡單封裝的JdbcTemplate。如果我們的專案組開發人員對所選型的ORM框架很熟悉並能熟練使用是再好不過了，但是大部分情況下專案組成員的技術水平是參差不齊的，有時會踩到所選型ORM框架的坑。比如Hibernate很容易引起效能問題，mybatis在重構時很麻煩等等增加了專案的交付風險。
本文希望能在Dao層的開發上做的簡單，健壯，提高開發效率：
1 對JdbcTemplate做一層簡單的封裝，能夠對單表的CRUD做到無需寫SQL語句
2 在重構SQL程式碼時對SQL層做少量修改或者不修改
我們開始吧！
我們先考慮一下：如果做到CRUD的不需要寫SQL，這也意味著SQL是要自動生成的。
我們先看SQL語句的組成：
1 新增語句： insert into tableName (id, name, …) values (1, `name`, …);
2 修改語句：update tableName set name = `name`, age = `age` where id = `id`;
3 刪除語句：delete from tableName where id = `id`;
4 查詢語句：select id, name from tableName where age > 18;
一般我們做JAVA企業級開發都會有領域模型的概念，這個領域模型會對應一個資料庫表幷包括這個資料庫表的所有欄位。那麼這時我們可以利用領域模型的欄位生成對應的SQL語句。這裡我們先借用JPA的註解完成領域模型屬性與資料庫表的對映，熟悉hibernate的朋友一定不會陌生，當然你也可以自己定義註解。
如下領域模型：
package com.applet.model;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.databind.ser.std.ToStringSerializer;
import com.applet.base.BaseModel;
import com.applet.enumeration.YesNoEnum;
import com.applet.utils.DateUtils;
import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;
@Entity
@Table(name = "TS_ROLE")
public class Role implements Serializable {
/**
* <p>
* Field serialVersionUID: 序列號
* </p>
*/
private static final long serialVersionUID = 1L;
//主鍵
@Id
@Column
@JsonSerialize(using = ToStringSerializer.class)
protected Long id;
// 名稱
@Column
private String name;
// 狀態（1啟用，2停用）
@Column
private Integer state;
// 建立人id
@Column
@JsonSerialize(using = ToStringSerializer.class)
private Long createId;
// 建立日期
@Temporal(TemporalType.TIMESTAMP)
@Column
private Date createTime;
// 是否系統角色（1是，2否）
@Column
private Integer isSys;
// 描述
@Column
private String remark;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getStateStr() {
return YesNoEnum.valueOfValidateLabel(state);
}
public String getIsSysStr() {
return YesNoEnum.valueOf(isSys);
}
public String getCreateTimeStr() {
if (createTime != null) {
return DateUtils.dateTimeToString(createTime);
}
return null;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getState() {
return state;
}
public void setState(Integer state) {
this.state = state;
}
public Long getCreateId() {
return createId;
}
public void setCreateId(Long createId) {
this.createId = createId;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Integer getIsSys() {
return isSys;
}
public void setIsSys(Integer isSys) {
this.isSys = isSys;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
}
複製程式碼我們的基礎的dao介面如下：
package com.applet.base;
import java.io.Serializable;
import java.util.List;
public interface BaseDao<T extends BaseModel, PK extends Serializable>{
/**
* 判斷某一欄位是否重複
* @param id 實體id
* @param filedValue 欄位值
* @param fieldName 欄位名稱
* @return
*/
//public boolean isDuplicateField(PK id, Object filedValue, String fieldName);
/**
* <p>Description: 新增實體</p>
* @param t 實體物件
*/
public int insert(T t);
/**
* <p>Description: 批量新增實體</p>
* @param list 實體物件列表
*/
public int batchInsert(final List<T> list);
/**
* <p>Description: 更新實體，欄位值為null的不更新</p>
* @param t 實體物件
*/
public int update(T t);
/**
* <p>Description: 更新實體</p>
* @param t 實體物件
*/
public int updateForce(T t);
/**
* <p>Description: 根據id刪除實體</p>
* @param id 實體id值
*/
public int delete(PK id);
/**
* <p>Description: 批量刪除實體</p>
* @param ids 實體id值陣列
*/
public int delete(PK[] ids);
/**
* <p>Description: 按條件查詢實體列表</p>
* @param wb QueryCondition物件
* @return 實體列表
*/
//public List<T> query(QueryCondition wb);
/**
* <p>Description: 按條件查詢實體數量</p>
* @param wb QueryCondition物件
* @return 實體數量
*/
//public int count(QueryCondition wb);
/**
* <p>Description: 根據id查詢實體</p>
* @param id 實體id值
* @return 實體物件
*/
public T load(PK id);
/**
* <p>Description: 按條件刪除實體</p>
* @param wb QueryCondition物件
*/
//public int deleteByCondition(QueryCondition wb);
/**
* <p>Description: 分頁查詢</p>
* @param wb QueryCondition物件
* @return
*/
//public Page<T> queryPage(QueryCondition wb);
}
複製程式碼我們基礎的dao介面實現如下：
package com.applet.base;
import com.applet.sql.builder.SelectBuilder;
import com.applet.sql.builder.WhereBuilder;
import com.applet.sql.mapper.DefaultRowMapper;
import com.applet.sql.page.PageSql;
import com.applet.sql.record.DomainModelAnalysis;
import com.applet.sql.record.DomainModelContext;
import com.applet.sql.record.ExtendType;
import com.applet.sql.record.TableColumn;
import com.applet.sql.type.JdbcType;
import com.applet.sql.type.TypeHandler;
import com.applet.sql.type.TypeHandlerRegistry;
import com.applet.utils.KeyUtils;
import com.applet.utils.Page;
import com.applet.utils.SpringContextHelper;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.ReflectionUtils;
import java.io.Serializable;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class BaseDaoImpl<T extends BaseModel, PK extends Serializable> implements BaseDao<T, PK>, InitializingBean {
protected static final Logger log = Logger.getLogger(BaseDaoImpl.class);
@Autowired
protected JdbcTemplate jdbcTemplate;
@Autowired
protected PageSql pageSql;
protected Class<T> modelClass;
protected DomainModelAnalysis domainModelAnalysis;
public BaseDaoImpl() {
}
@SuppressWarnings("unchecked")
protected Class<T> autoGetDomainClass() {
if (modelClass == null) {
Type type = this.getClass().getGenericSuperclass();
if (type instanceof ParameterizedType) {
modelClass = (Class<T>) ((ParameterizedType) type).getActualTypeArguments()[0];
} else {
throw new RuntimeException("SubClass must give the ActualTypeArguments");
}
}
return modelClass;
}
/**
* 獲取新增實體的SQL語句
*
* @return
*/
protected String getInsertSql() {
String[] array = domainModelAnalysis.joinColumnWithPlaceholder(", ");
String sql = String.format("INSERT INTO %s (%s) VALUES (%s)", domainModelAnalysis.getTableName(), array[0], array[1]);
return sql;
}
/**
* 將完整的SQL轉換為統計SQL
* 如：select a, b, c, t.d from table t
* 轉換後為：select count(1) from table t
*
* @param sql
* @return
*/
protected String toCountSql(String sql) {
if (StringUtils.isEmpty(sql)) {
return null;
}
return sql.replaceFirst("(?<=(?i)SELECT).*?(?=(?i)FROM)", " COUNT\(1\) ").replaceAll("(?=(?i)order).*", "");
}
/**
* 新增實體
*
* @param t 實體物件
* @return
*/
@Override
public int insert(T t) {
List<TableColumn> tableColumnList = domainModelAnalysis.getTableColumnList();
List<Object> list = new ArrayList<Object>();
for (int i = 0, size = tableColumnList.size(); i < size; i++) {
TableColumn tableColumn = tableColumnList.get(i);
if (tableColumn.isTransient()) {
continue;
}
Object value = ReflectionUtils.invokeMethod(tableColumn.getFieldGetMethod(), t);
ExtendType extendType = tableColumn.getExtendType();
if (extendType != null && extendType.getCode() != ExtendType.DEFAULT.getCode()) {
value = value.toString();
}
list.add(value);
}
return jdbcTemplate.update(getInsertSql(), list.toArray(new Object[0]));
}
/**
* 批量新增實體
*
* @param list 實體物件列表
* @return
*/
@Override
public int batchInsert(final List<T> list) {
final List<TableColumn> tableColumnList = domainModelAnalysis.getTableColumnList();
final TypeHandlerRegistry typeHandlerRegistry = DomainModelContext.getTypeHandlerRegistry();
return jdbcTemplate.batchUpdate(getInsertSql(), new BatchPreparedStatementSetter() {
@SuppressWarnings({"rawtypes", "unchecked"})
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
T t = list.get(i);
int index = 1;
for (int k = 0, size = tableColumnList.size(); k < size; k++) {
TableColumn tableColumn = tableColumnList.get(k);
if (tableColumn.isTransient()) {
continue;
}
Object value = ReflectionUtils.invokeMethod(tableColumn.getFieldGetMethod(), t);
TypeHandler typeHandler = typeHandlerRegistry.getTypeHandler(tableColumn.getJavaType());
typeHandler.setParameter(ps, index, value, JdbcType.NULL);
index++;
}
}
@Override
public int getBatchSize() {
return list.size();
}
}).length;
}
/**
* 更新實體中的非空(不含null, "")欄位
*
* @param t 實體物件
* @return
*/
@Override
public int update(T t) {
WhereBuilder wb = new WhereBuilder();
wb.andEquals(domainModelAnalysis.getPrimaryKey(), t.getId());
return updateByCondition(t, wb);
}
protected int updateByCondition(T t, WhereBuilder whereBuilder) {
StringBuilder sqlBuilder = new StringBuilder(String.format("UPDATE %s SET ", domainModelAnalysis.getTableName()));
List<TableColumn> tableColumnList = domainModelAnalysis.getTableColumnList();
Method primaryKeyMethod = null;
List<Object> values = new ArrayList<Object>();
for (int i = 0, size = tableColumnList.size(); i < size; i++) {
final TableColumn tableColumn = tableColumnList.get(i);
if (tableColumn.isTransient()) {
continue;
}
if (tableColumn.isPrimaryKey()) {
primaryKeyMethod = tableColumn.getFieldGetMethod();
continue;
}
Object value = ReflectionUtils.invokeMethod(tableColumn.getFieldGetMethod(), t);
ExtendType extendType = tableColumn.getExtendType();
if (extendType != null && extendType.getCode() != ExtendType.DEFAULT.getCode()) {
value = value.toString();
}
if (value == null || (value instanceof String && StringUtils.isEmpty((String) value))) {
continue;
}
values.add(value);
sqlBuilder.append(tableColumn.getColumnName())
.append(" = ")
.append(tableColumn.getPlaceholder())
.append(", ");
}
if (values.size() > 0) {
int length = sqlBuilder.length();
sqlBuilder.delete(length - 2, length);
String sql = sqlBuilder.toString();
sql = whereBuilder.getSql(sql);
values.addAll(whereBuilder.getParameterList());
return jdbcTemplate.update(sql, values.toArray(new Object[0]));
}
return 0;
}
/**
* 更新實體所有欄位
*
* @param t 實體物件
* @return
*/
@Override
public int updateForce(T t) {
StringBuilder sqlBuilder = new StringBuilder(String.format("UPDATE %s SET ", domainModelAnalysis.getTableName()));
List<TableColumn> tableColumnList = domainModelAnalysis.getTableColumnList();
Method primaryKeyMethod = null;
List<Object> values = new ArrayList<Object>();
for (int i = 0, size = tableColumnList.size(); i < size; i++) {
final TableColumn tableColumn = tableColumnList.get(i);
if (tableColumn.isTransient()) {
continue;
}
if (tableColumn.isPrimaryKey()) {
primaryKeyMethod = tableColumn.getFieldGetMethod();
continue;
}
Object value = ReflectionUtils.invokeMethod(tableColumn.getFieldGetMethod(), t);
values.add(value);
sqlBuilder.append(tableColumn.getColumnName())
.append(" = ")
.append(tableColumn.getPlaceholder())
.append(", ");
}
int length = sqlBuilder.length();
sqlBuilder.delete(length - 2, length);
sqlBuilder.append(" WHERE ").append(domainModelAnalysis.getPrimaryKey()).append(" = ?");
values.add(ReflectionUtils.invokeMethod(primaryKeyMethod, t));
return jdbcTemplate.update(sqlBuilder.toString(), values.toArray(new Object[0]));
}
/**
* 根據主鍵刪除實體
*
* @param id 實體主鍵值
* @return
*/
@Override
public int delete(PK id) {
String sql = String.format("DELETE FROM %s WHERE %s = ?", domainModelAnalysis.getTableName(), domainModelAnalysis.getPrimaryKey());
return jdbcTemplate.update(sql, new Object[]{id});
}
/**
* 根據主鍵刪除實體
*
* @param ids 實體主鍵值陣列
* @return
*/
@Override
public int delete(final PK[] ids) {
String sql = String.format("DELETE FROM %s WHERE %s = ?", domainModelAnalysis.getTableName(), domainModelAnalysis.getPrimaryKey());
int[] batchUpdate = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setObject(1, ids[i]);
}
@Override
public int getBatchSize() {
return ids.length;
}
});
return batchUpdate.length;
}
/**
* 查詢實體列表
*
* @param wb where語句拼接例項
* @return
*/
protected List<T> query(WhereBuilder wb) {
String sql = String.format("SELECT %s FROM %s", domainModelAnalysis.getDefaultColumnArrayStr(), domainModelAnalysis.getTableName());
Object args[] = null;
if (wb != null) {
sql = wb.getSql(sql);
args = wb.getParameters();
}
return jdbcTemplate.query(sql, args, new DefaultRowMapper<T>(domainModelAnalysis));
}
/**
* 查詢實體列表
*
* @param sql select語句
* @param wb where語句拼接例項
* @return
*/
protected List<T> query(String sql, WhereBuilder wb) {
Object args[] = null;
if (wb != null) {
sql = wb.getSql(sql);
args = wb.getParameters();
}
return jdbcTemplate.query(sql, args, new DefaultRowMapper<T>(domainModelAnalysis));
}
/**
* 限制返回查詢記錄數量
*
* @param wb where語句拼接例項
* @return
*/
protected List<T> queryLimit(WhereBuilder wb) {
String sql = String.format("SELECT %s FROM %s", domainModelAnalysis.getDefaultColumnArrayStr(), domainModelAnalysis.getTableName());
return queryLimit(sql, wb);
}
/**
* 限制返回查詢記錄數量
*
* @param querySql select語句
* @param wb where語句拼接例項
* @return
*/
protected List<T> queryLimit(String querySql, WhereBuilder wb) {
Object args[] = null;
int pageNum = 0, pageSize = 0;
if (wb != null) {
pageNum = wb.getPageNum();
pageSize = wb.getPageSize();
querySql = wb.getSql(querySql);
args = wb.getParameters();
}
String qsql = pageSql.getSql(querySql, pageNum, pageSize);
List<T> list = jdbcTemplate.query(qsql, args, new DefaultRowMapper<T>(domainModelAnalysis));
return list;
}
/**
* 按條件統計實體數量
*
* @param wb where語句拼接例項
* @return
*/
protected int count(WhereBuilder wb) {
String sql = String.format("SELECT COUNT(1) FROM %s", domainModelAnalysis.getTableName());
Object args[] = null;
if (wb != null) {
sql = wb.getSql(sql);
args = wb.getParameters();
}
return jdbcTemplate.queryForObject(sql, args, Integer.class);
}
/**
* 根據主鍵查詢實體
*
* @param id 實體主鍵值
* @return
*/
@Override
public T load(PK id) {
String sql = String.format("SELECT %s FROM %s WHERE %s = ?", domainModelAnalysis.getDefaultColumnArrayStr(), domainModelAnalysis.getTableName(), domainModelAnalysis.getPrimaryKey());
List<T> list = jdbcTemplate.query(sql, new Object[]{id}, new DefaultRowMapper<T>(domainModelAnalysis));
return DataAccessUtils.singleResult(list);
}
/**
* 按條件刪除實體
*
* @param wb where語句拼接例項
* @return
*/
protected int deleteByCondition(WhereBuilder wb) {
String sql = String.format("DELETE FROM %s", domainModelAnalysis.getTableName());
Object args[] = null;
if (wb != null) {
sql = wb.getSql(sql);
args = wb.getParameters();
}
return jdbcTemplate.update(sql, args);
}
/**
* 分頁查詢
*
* @param wb where語句拼接例項
* @return
*/
protected Page<T> queryPage(WhereBuilder wb) {
String sql = String.format("SELECT %s FROM %s ", domainModelAnalysis.getDefaultColumnArrayStr(), domainModelAnalysis.getTableName());
return queryPage(sql, wb);
}
/**
* 分頁查詢
*
* @param sql select語句
* @param wb where語句拼接例項
* @return
*/
protected Page<T> queryPage(String sql, WhereBuilder wb) {
String countSql = toCountSql(sql);
return queryPage(sql, countSql, wb);
}
/**
* 分頁查詢
*
* @param querySql select語句
* @param countSql count語句
* @param wb where語句拼接例項
* @return
*/
protected Page<T> queryPage(String querySql, String countSql, WhereBuilder wb) {
Object args[] = null;
int pageNum = 0, pageSize = 0;
if (wb != null) {
querySql = wb.getSql(querySql);
args = wb.getParameters();
pageSize = wb.getPageSize();
pageNum = wb.getPageNum();
}
String qsql = pageSql.getSql(querySql, pageNum, pageSize);
List<T> list = jdbcTemplate.query(qsql, args, new DefaultRowMapper<T>(domainModelAnalysis));
Page<T> page = new Page<T>();
page.setData(list);
if (StringUtils.isNotEmpty(countSql)) {
String csql = wb.getCountSql(countSql);
long count = jdbcTemplate.queryForObject(csql, args, Long.class);
page.setTotal(count);
}
return page;
}
@Override
public void afterPropertiesSet() throws Exception {
DomainModelContext domainModelContext = SpringContextHelper.getBean(DomainModelContext.class);
domainModelAnalysis = domainModelContext.registerBean(autoGetDomainClass());
}
}
複製程式碼這裡最關鍵的是如何解析領域模型的屬性資訊，我們可以在執行時通過反射把領域模型的屬性資訊解析出來並全域性快取起來。這步操作是在BaseDaoImpl.java的如下程式碼完成的—如果你不熟悉InitializingBean介面，可以搜尋一下它的意義：
@Override
public void afterPropertiesSet() throws Exception {
DomainModelContext domainModelContext = SpringContextHelper.getBean(DomainModelContext.class);
domainModelAnalysis = domainModelContext.registerBean(autoGetDomainClass());
}
複製程式碼下一節為大家介紹如何使用Java反射把領域模型的屬性資訊解析出來並全域性快取起來。