通常我們在開發Java企業級應用的時候使用的技術大部分是Spring、Hibernate、mybatis、Struts2等。尤其是Spring，相信這個龐大而優雅的技術體系你已經離不開了，在我們項目代碼中基本是骨幹力量的存在。
Java
而我們使用的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);}
Java
我們基礎的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接口，可以搜索一下它的意義：
@Overridepublic void afterPropertiesSet() throws Exception { DomainModelContext domainModelContext = SpringContextHelper.getBean(DomainModelContext.class); domainModelAnalysis = domainModelContext.registerBean(autoGetDomainClass());}
Java
相關推薦
推薦中...