写在前面
以前有点懒，一直没有分享技术新的的习惯，这次居然有心情写点东西分享出来。
作为一个从很老很老的jdk就开始码java代码的码农，对Mybatis却一直都是一无所知，一直固守在Hibernate持久层框架上。虽然这个红的发紫的MyBatis 是一款优秀的持久层框架，而我居然都没有了解过，也没有用过。想想也觉得汗颜呀。
言归正传，现在切入正题。
还有需要先申明一下：本文档为本人原创，欢迎引用，本着方便字节，也方便大家的原则，独乐乐，众乐乐，不若与众的精神，只有能给大家工作带来用处，本老农就心满意足了。
背景，为什么要学习
因项目需要，决定采用springboot+Mybatis。于是，我也加入了这个码农圈无人不知，无人不晓的Mybatis开发大队伍里。据说：这个可是各个大厂的御用持久层技术框架，一时用一时爽，一直用就一直爽；还听说，Mybatis能够让你随心所欲写SQL。
以前使用hibernate，对hibernate的一些使用习惯还是念念不忘，总想Mybatis能有该多好。真的是如假包换，百分百的hibernate的添狗一只。
因项目，在这个项目开始前开始学习Mybatis，翻资料，网上找例子，就差去整一本《二十一天精通Mybatis》来了。不过，从开始学习，接触Mybatis开始算起，已经2周左右了，马上就差不多要到21天了，感觉离精通还有十万八千里路远。
就这样子，还班门弄斧，各位看官，莫笑。如不听劝，牙齿掉了，还请自负其责，自费补牙。
Mybatis的典型规范
典型的规范：
- 写个实体EntityBean类，描述数据库表或查询结构之间的映射关系。
@Entity
@Table(name = "User")
public class User{
@Id
private int id;
private String name;
……
}
- 写个Mapper接口，定义访问数据库表的方法。Mybatis采用JDK本身的接口动态代理实现。
public interface UserMapper {
//根据用户id查询用户信息
public User findUserById(int id) throws Exception;
//查询用户列表
public List<User> findUserByUsername(String username) throws Exception;
//添加用户信息
public void insertUser(User user)throws Exception;
……
}
- 写一个.XML文件，配置Mapper接口方法所需要的SQL语句，为了实现一定的动态能力，还定义了一套XML标签来标记条件、循环等。
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxx.UserMapper">
<!-- 注意这里的 namespace必须对应着map接口的全类名-->
<select id="findUserById" parameterType="int" resultType="user">
select * from user where id = #{id}
</select>
<select id="findUserByUsername" parameterType="java.lang.String"
resultType="user">
select * from user where username like '%${value}%'
</select>
<insert id="insertUser" parameterType="user">
<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
select LAST_INSERT_ID()
</selectKey>
insert into user(username,birthday,sex,address)
values(#{username},#{birthday},#{sex},#{address})
</insert>
……
</mapper>
为了写好这三个文件，还有大拿提供了专门的脚手架插件来完成这个事情，不过，为了与前端页面配合，编辑调试XML的工作还是有不少的。
懒人的想法
1.以前用Hibernate时，只定义了一个基础接口，把常用的增删改查放在基类接口中，在扩展接口中定义少量的个性化方法，利用注解的方式和JDK和CGLIB的动态代理机制，实现接口功能。Mybatis能不能也像Hibernate一样，直接定义个通用接口就好了，再也不用配置XML或注解SQL。减少码代码的工作量和测试工作量。
2.Mybatis分页方式，大多采用应用层分页方式实现，应用层分页，数据会从数据库服务器向应用服务器传输。当数据集特别大，比如十万级百万级甚至千万级时，数据从数据库服务器向应用服务器传输，给网络带来了非常大的压力。那能不能实现任意字段方式的查询，以及真正的数据库端的分页呢？
说干就干，本着辛苦我一人，造福兄弟伙的国际奉献主义精神，我不当垫脚石谁来当垫脚石的牺牲精神。我这个21天都不到的Mybatis的婴儿码农脱了衣服，再撸起袖子加油干。
突破口在哪儿
翻了一堆的资料，找下手的突破口。功夫不负我心，找到了两个下手地。首先，我来看看Mybatis的执行过程。
1.Mybatis提供有方法拦截器的切入口。Mybatis支持对Executor、StatementHandler、PameterHandler和ResultSetHandler 接口的方法进行拦截，也就是说会对这4类对象的方法进行代理拦截。
在mybatis中可被拦截的类型有四种(按照拦截顺序)：
- Executor：拦截执行器的方法。
- ParameterHandler：拦截参数的处理。
- ResultHandler：拦截结果集的处理。
- StatementHandler：拦截Sql语法构建的处理。
2.针对动态拼接SQL, Mybatis3中增加了使用注解来配置Mapper的新特性，我们主要用到@SelectProvider、@UpdateProvider、@InsertProvider和@DeleteProvider几个注解来实现。
有了这些注解，就可以让我们在运行时将SQL构造出来。实现动态SQL生成，处理数据。换句话说，就是可以根据传进来的实体类型及字段值，就可以组装出你想要的SQL。这样做是不是很爽。
有了这两个切入点，我们就可以在完全不破坏Mybatis架构的前提下，把我们的私心暗恋想法悄无声息地注入进去。光想一下都让我有点鸡冻不已。激动让我语无伦次，必须马上动手实现它。不能光临渊羡鱼，还得退而结网
让我们来看看下面这幅图，我们要下手的地方在哪儿。
进入正题
为了少写点代码，把经常用到的通用方法放在基类接口中，不使用XML，子类接口直接继承就好了。
废话就不BB了，直接上代码:
由于文档篇幅，下面提供主要类的部分代码片段。进行示例。
A. 基类接口
定义基类接口，基类接口实现
1.数据处理操作，包括insert,update,delete,save(自动判断使用insert或update)方法，
2.数据库查询操作，包括分页查询。查询条件参数支持map、dto对象，EntityBean对象
Mapper接口泛型GenericMapper，提供子类对具体实体类的支持。
public interface GenericMapper<T> {
@SelectProvider(type = GenericSelectProvider.class, method = "getById")
T getById(@Param("entityClass") Class<T> entityClass, @Param("entityId") Object params);
@SelectProvider(type = GenericSelectProvider.class, method = "query")
List<T> query(@Param("entityClass") Class<T> entityClass,@Param("params") Map params,String orderBy);
@SelectProvider(type = GenericSelectProvider.class, method = "pagedQuery")
Paginate<T> pagedQuery(@Param("entityClass") Class<T> entityClass,@Param("paginate") Paginate<T> paginate,@Param("params") Map params,String orderBy);
@SelectProvider(type = GenericSelectProvider.class, method = "count")
int count(@Param("entityClass") Class<T> entityClass,@Param("params") Map params);
@SelectProvider(type = GenericSelectProvider.class, method = "queryByDto")
List<T> queryByDto(@Param("entityClass") Class<T> entityClass,@Param("params") Object params,String orderBy);
@SelectProvider(type = GenericSelectProvider.class, method = "queryByFilterRules")
List<T> queryByFilterRules(@Param("entityClass") Class<T> entityClass,@Param("filterRules") List<DataFilterRule> filterRules,String orderBy);
@InsertProvider(type = GenericSaveProvider.class, method = "insert")
Integer insert(@Param("entityBean") T entityBean);
@UpdateProvider(type = GenericSaveProvider.class, method = "update")
Integer update(@Param("entityBean") T entityBean);
@DeleteProvider(type = GenericSaveProvider.class, method = "delete")
Integer remove(@Param("entityBean") T entityBean);
@UpdateProvider(type = GenericSaveProvider.class, method = "save")
Integer save(@Param("entityBean") T entityBean);
}
B. 提供Mybatis使用的Providor
Providor提供动态sql切入点。GenericSelectProvidor提供实体类或查询对象的动态数据查询的切入点，GenericSaveProvidor提供对通用实体类的动态增删改操作。
1. GenericSelectProvidor代码
public class GenericSelectProvider {
private final Logger logger = LoggerFactory.getLogger(this.getClass());
protected static final String SQL_END_DELIMITER = ";";
@SelectProvider(type = GenericSelectProvider.class, method = "getById")
public <T> String getById(@Param("entityClass") Class<T> entityClass, @Param("entityId") Object params){
EntityBeanInfo dmi=EntityBeanInfo.getDtoMaperInfo(entityClass);
if (dmi==null){
throw new RuntimeException("不是数据库映射对象");
}
String sql=dmi.getGetByIdSQL();
return sql;
}
public <T> String query(@Param("entityClass") Class<T> entityClass, @Param("params") Map<String,Object> params,String orderBy){
EntityBeanInfo dmi=EntityBeanInfo.getDtoMaperInfo(entityClass);
if (dmi==null){
throw new RuntimeException("不是数据库映射对象");
}
String sql=dmi.getSelectSQL();
Map<String,ColumnInfo> columns=dmi.getColumns();
String whereClause = whereClause(columns,params);
String _orderBy = orderByClause(dmi.getColumns(),orderBy);
if (whereClause!=null && whereClause.length()>0){
sql=insertWhereClause(sql,whereClause);
}
if (_orderBy!=null && _orderBy.length()>0){
sql=sql+"\r\n order by " + _orderBy;
}
logger.debug("mybatis get query sql:{}", sql);
return sql;
}
private String insertWhereClause(String sql,String whereClause){
int wherePos=SqlUtil.indexOfKeywordInSql(sql, "where");
if (wherePos<1){
sql=sql+"\r\n where " + whereClause;
}else{
int groupPos = SqlUtil.indexOfKeywordInSql(sql, "group", wherePos + 5);
int orderPos = SqlUtil.indexOfKeywordInSql(sql, "order", wherePos + 5);
int insertPos = wherePos < groupPos ? groupPos : wherePos;
insertPos = insertPos < orderPos ? orderPos : insertPos;
if (insertPos != wherePos) {
sql = sql.substring(0, insertPos) + "\r\n and " + whereClause + "\r\n" + sql.substring(insertPos);
} else {
sql = sql + "\r\n and " + whereClause;
}
}
return sql;
}
public <T> String pagedQuery(@Param("entityClass") Class<T> entityClass,@Param("paginate") Paginate<T> paginate,
@Param("params") Map params,String orderBy){
EntityBeanInfo dmi=EntityBeanInfo.getDtoMaperInfo(entityClass);
if (dmi==null){
throw new RuntimeException("不是数据库映射对象");
}
Map<String,ColumnInfo> columns=dmi.getColumns();
String whereClause = whereClause(columns,params);
String _orderBy = orderByClause(dmi.getColumns(),orderBy);
String slctSql=dmi.getSelectSQL();
if (whereClause!=null && whereClause.length()>0){
slctSql=insertWhereClause(slctSql,whereClause);
}
if (_orderBy!=null && _orderBy.length()>0){
slctSql=slctSql+"\r\n order by " + _orderBy;
}
logger.debug("GenericSelectProvider.pagedquery() sql:{}", slctSql);
//System.out.println("slctsql:"+slctSql);
return slctSql;
}
public <T> String count(@Param("entityClass") Class<T> entityClass, @Param("params") Map<String,Object> params){
EntityBeanInfo dmi=EntityBeanInfo.getDtoMaperInfo(entityClass);
if (dmi==null){
throw new RuntimeException("不是数据库映射对象");
}
String sql=dmi.getCountSQL();
Map<String,ColumnInfo> columns=dmi.getColumns();
String whereClause = whereClause(columns,params);
if (whereClause!=null && whereClause.length()>0){
sql=sql+"\r\n where " + whereClause;
}
logger.debug("GenericSelectProvider.count() sql:{}", sql);
//System.out.println("count sql:"+sql);
return sql;
}
private String whereClause(Map<String,ColumnInfo> columns, Map<String,Object> params){
StringBuffer whereClause=new StringBuffer();
params.forEach((paraName,paraValue)->{
getCondition(paraName,paraValue,columns,whereClause);
});
return whereClause.toString();
}
public <T> String queryByDto(@Param("entityClass") Class<T> entityClass,@Param("params") Object params,String orderBy){
EntityBeanInfo dmi=EntityBeanInfo.getDtoMaperInfo(entityClass);
if (dmi==null){
throw new RuntimeException("不是数据库映射对象");
}
String sql=dmi.getSelectSQL();
Map<String,ColumnInfo> columns=dmi.getColumns();
String whereClause = whereClause(columns,params);
String _orderBy = orderByClause(dmi.getColumns(),orderBy);
if (whereClause!=null && whereClause.length()>0){
sql=insertWhereClause(sql,whereClause);
}
if (_orderBy!=null && _orderBy.length()>0){
sql=sql+"\r\n order by " + _orderBy;
}
logger.debug("GenericSelectProvider.queryByDto() sql:{}", sql);
return sql;
}
private String whereClause(Map<String,ColumnInfo> columns, Object params){
StringBuffer whereClause=new StringBuffer();
Field[] fields=params.getClass().getDeclaredFields();
String paraName;
Object paraValue;
for(Field field:fields){
paraName=field.getName();
try {
paraValue=BeanUtils.forceGetProperty(params, paraName);
if (paraValue==null)
continue;
} catch (NoSuchFieldException e) {
continue;
}
getCondition(paraName,paraValue,columns,whereClause);
}
return whereClause.toString();
}
public <T> String queryByFilterRules(@Param("entityClass") Class<T> entityClass, @Param("filterRules") List<DataFilterRule> filterRules,String orderBy){
EntityBeanInfo dmi=EntityBeanInfo.getDtoMaperInfo(entityClass);
if (dmi==null){
throw new RuntimeException("不是数据库映射对象");
}
String sql=dmi.getSelectSQL();
Map<String,ColumnInfo> columns=dmi.getColumns();
String whereClause = whereClause(columns,filterRules);
String _orderBy = orderByClause(dmi.getColumns(),orderBy);
if (whereClause!=null && whereClause.length()>0){
sql=insertWhereClause(sql,whereClause);
}
if (_orderBy!=null && _orderBy.length()>0){
sql=sql+"\r\n order by " + _orderBy;
}
logger.debug("GenericSelectProvider.queryByFilterRules() sql:{}", sql);
return sql;
}
private String whereClause(Map<String,ColumnInfo> columns, List<DataFilterRule> filterRules){
StringBuffer whereClause=new StringBuffer();
String paraName;
Object paraValue;
for(DataFilterRule rule:filterRules){
paraName=rule.getField();
paraValue=rule.getValue();
if (paraValue==null)
continue;
getCondition(paraName,paraValue,columns,whereClause);
}
return whereClause.toString();
}
private void getCondition(String paraName,Object paraValue,Map<String,ColumnInfo> columns,StringBuffer whereClause){
//基本条件：属性等于属性值
String field,queryMode;
ColumnInfo column=columns.get(paraName);
Class<?> type;
if (column != null){
if (!column.isFilterable()){
return;
}
if (whereClause.length()>0)
whereClause.append(" and ");
type=column.getJavaType();
whereClause.append(column.getFilterName()).append(" = #{params.").append(paraName).append("}");
return ;
}
//复杂条件：参数为 属性名_条件类型
//条件类型为：包含，多值，大于，小于等。
int pos;
pos=paraName.lastIndexOf("_");
if (pos<0){
//单值查询
field=paraName;
queryMode="";
}else{
field=paraName.substring(0,pos);
queryMode=paraName.substring(pos+1);
}
column=columns.get(field);
if (column==null || !column.isFilterable())
return;
type=column.getJavaType();
if (queryMode.indexOf("multiple")<0){
if (paraValue==null)
return;
if ((paraValue instanceof String) && ((String )paraValue).trim().length()==0 )
return;
getCondition(column.getFilterName(),paraName,queryMode,type,paraValue,whereClause);
}else{
if (paraValue instanceof Collection) {
String value="";
for(Object str:((Collection<?>)paraValue)){
value+=str+",";
}
getCondition(column.getFilterName(),paraName,queryMode,type,value.substring(0,value.length()-1),whereClause);
}
}
}
private void getCondition(String field,String paraName,String queryMode,Class<?> type,Object value,StringBuffer whereClause){
if (queryMode.length()==0){
if (whereClause.length()>0)
whereClause.append(" and ") ;
whereClause.append(field).append(" = #{params.").append(paraName).append("}");
}else{
if ("begin".equalsIgnoreCase(queryMode)){
if (whereClause.length()>0)
whereClause.append(" and ") ;
whereClause.append(field).append(" >= #{params.").append(paraName).append("}");
}
if ("end".equalsIgnoreCase(queryMode)){
if (whereClause.length()>0)
whereClause.append(" and ") ;
whereClause.append(field).append(" <= #{params.").append(paraName).append("}");
}
if ("like".equalsIgnoreCase(queryMode)){
if (whereClause.length()>0)
whereClause.append(" and ") ;
whereClause.append(field).append(" like concat(#{params.").append(paraName).append("},'%')");
}
if ("likeAll".equalsIgnoreCase(queryMode)){
if (whereClause.length()>0)
whereClause.append(" and ") ;
whereClause.append(field).append(" like concat('%',#{params.").append(paraName).append("},'%')");
}
//多值in查询
if ("multiple[]".equalsIgnoreCase(queryMode)){
createInClause(field,type,value,whereClause);
}
}
}
protected String castType(Class<?> type,Object value){
if (type==null){
return value.toString();
}
if (type == String.class){
return "'"+value+"'";
}
if (type == Boolean.class || type == boolean.class){
return "'"+value+"'";
}
if (type == java.sql.Date.class){
return "'"+value+"'";
}
if (type == java.util.Date.class){
return "'"+value+"'";
}
if (type == Timestamp.class){
return "'"+value+"'";
}
if (type == Integer.class || type == int.class){
return value.toString();
}
if (type == Long.class || type == long.class){
return value.toString();
}
if (type == Float.class || type == float.class){
return value.toString();
}
if (type == Double.class || type == double.class){
return value.toString();
}
return value.toString();
}
//构造多值IN查询
protected void createInClause(String field,Class<?> type,Object value,StringBuffer whereClause){
String[] strs=value.toString().split("\r\n|\r|\n| |,");
StringBuffer sb=new StringBuffer();
for (String str:strs){
str=str.trim();
if (str.length()==0)
continue;
str=str.replace("'", "''");//转义单引号，预防SQL注入
sb.append(castType(type,str)+",");
}
if (sb.length()>1){
if (whereClause.length()>0)
whereClause.append(" and ") ;
whereClause.append(field).append(" in (").append(sb.substring(0,sb.length()-1)).append(")");
}
}
public String orderByClause( Map<String, ColumnInfo> columns,String orderBy){
if (orderBy==null)
return "";
StringBuffer orderByClause=new StringBuffer();
String[] orderProps=orderBy.trim().split(",");
for(String op : orderProps){
if (op==null)
continue;
orderByClause(columns,op.trim(),orderByClause);
}
return orderByClause.toString();
}
private void orderByClause(Map<String, ColumnInfo> columns,String orderBy,StringBuffer orderByClause){
int pos;
String propName,orderType;
pos=orderBy.indexOf(" ");
if (pos>1){
propName=orderBy.substring(0,pos);
orderType=orderBy.substring(pos+1).toLowerCase();
}else{
propName=orderBy;
orderType="asc";
}
ColumnInfo columnInfo=columns.get(propName);
if (columnInfo!=null){
if (orderByClause.length()>0)
orderByClause.append(",");
orderByClause.append(columnInfo.getColumnName()).append(" ").append(orderType);
}
}
}
2. GenericSaveProvidor代码
public class GenericSaveProvider {
public static String SAVE_ENTITY_BEAN="save";
public static String SAVE_OR_UPDATE_ENTITY_BEAN="saveOrUpdate";
public <T> String update(@Param("entityBean") T entityBean){
EntityBeanInfo dmi=EntityBeanInfo.getDtoMaperInfo(BeanUtils.getEntityClass(entityBean));
String sql=dmi.getUpdateSQL();
return sql;
}
public <T> String insert(@Param("entityBean") T entityBean){
EntityBeanInfo dmi=EntityBeanInfo.getDtoMaperInfo(BeanUtils.getEntityClass(entityBean));
return dmi.getInsertSQL();
}
public <T> String delete(@Param("entityBean") T entityBean){
EntityBeanInfo dmi=EntityBeanInfo.getDtoMaperInfo(BeanUtils.getEntityClass(entityBean));
String sql=dmi.getDeleteSQL();
return sql;
}
public <T> String save(@Param("entityBean") T entityBean){
EntityBeanInfo dmi=EntityBeanInfo.getDtoMaperInfo(BeanUtils.getEntityClass(entityBean));
String sql=dmi.getSaveSQL();
return sql;
}
}
C. 实体EntityBean描述类EntityBeanInfo
EntityBeanInfo用以描述bean信息，这个类既可以描述有@Entity和@Table注解的实体对象，也可以描述有自定义注解@BeanSQLMap注解的查询结果对象。他缓存了用于查询和修改实体对象的基本的sql语句。
用于描述EntityBean字段与数据库映射的ColumnInfo类。ColumnInfo描述了字段的属性名，读取数据时数据库字段名，查询数据库时的字段名，是否可以用于查询等，供生产动态SQL时使用。
1. EntityBeanInfo类
public class EntityBeanInfo implements Iterable<Map.Entry<Class<?>, EntityBeanInfo>>{
public static Map<Class<?>, EntityBeanInfo> entityBeanInfos=new HashMap<>();
private Class<?> entityClass;
private Method[] methods;
private Field[] fields;
private BeanMappingType dataObjectType=BeanMappingType.Table;
private String tableName;
private String getByIdSQL;
private String selectSQL;
private String countSQL;
private String existSQL;
private String insertSQL;
private String updateSQL;
private String deleteSQL;
private String saveSQL;
private boolean hasWhereClause = false;
private Map<String,ColumnInfo> columns=new HashMap<>();
private List<ColumnInfo> IdColumns=new ArrayList<>();
public static EntityBeanInfo getDtoMaperInfo(Class<?> entityClass){
EntityBeanInfo beanInfo=entityBeanInfos.get(entityClass);
if (beanInfo==null)
beanInfo=createDtoMaperInfo(entityClass);
return beanInfo;
}
public static EntityBeanInfo createDtoMaperInfo(Class<?> entityClass){
EntityBeanInfo dmi=new EntityBeanInfo(entityClass);
entityBeanInfos.put(entityClass, dmi);
return dmi;
}
private EntityBeanInfo(Class<?> entityClass){
this.entityClass=entityClass;
getFields();
getMethods();
getTableInfo();
createColumns();
generateIdList();
createCountSQL();
createSelectSQL();
createInsertSQL();
createUpdateSQL();
createDeleteSQL();
createGetByIdSQL();
createExistSQL();
createSaveSQL();
}
private void getFields(){
Field[] newFieldArr=new Field[0];
Field[] FieldArr,oldFieldArr;
for (Class<?> superClass = entityClass; superClass != Object.class; superClass = superClass.getSuperclass()) {
FieldArr=superClass.getDeclaredFields();
oldFieldArr=newFieldArr;
newFieldArr=new Field[oldFieldArr.length+FieldArr.length];
System.arraycopy(oldFieldArr, 0, newFieldArr, 0, oldFieldArr.length);
System.arraycopy(FieldArr, 0, newFieldArr, oldFieldArr.length, FieldArr.length);
}
fields=newFieldArr;
}
private void getMethods(){
methods=entityClass.getMethods();
Method[] newMethodArr=new Method[0];
Method[] methodArr,oldMethodArr;
for (Class<?> superClass = entityClass; superClass != Object.class; superClass = superClass.getSuperclass()) {
methodArr=superClass.getDeclaredMethods();
oldMethodArr=newMethodArr;
newMethodArr=new Method[oldMethodArr.length+methodArr.length];
System.arraycopy(oldMethodArr, 0, newMethodArr, 0, oldMethodArr.length);
System.arraycopy(methodArr, 0, newMethodArr, oldMethodArr.length, methodArr.length);
}
methods=newMethodArr;
}
private void getTableInfo(){
//对象映射到SQL
BeanSQLMap sqlmap=entityClass.getAnnotation(BeanSQLMap.class);
if (sqlmap != null ){
String mappingSQL=sqlmap.mappingSQL();
if (mappingSQL!=null && mappingSQL.trim().length()>0){
selectSQL=mappingSQL;
dataObjectType=BeanMappingType.Select;
hasWhereClause=sqlmap.hasWhereClause();
}
countSQL=sqlmap.countSQL();
return;
}
//对象映射到数据库表
Table table=entityClass.getAnnotation(Table.class);
if (table!=null){
tableName=table.name();
dataObjectType=BeanMappingType.Table;
}
}
public List<ColumnInfo> getIdColumns() {
return IdColumns;
}
private void generateIdList(){
columns.forEach((field,column)->{
if (column.getIsId())
IdColumns.add(column);
});
}
private void createColumns(){
Column column;
Id id;
String fieldName;
ColumnInfo columnInfo;
ConditionColumn filter;
//处理属性上有Column注解的属性
for (Field field:fields){
if (field.getAnnotation(Transient.class) != null)
continue;
fieldName=field.getName();
column=field.getAnnotation(Column.class);
if (column != null){
fieldName=field.getName();
columnInfo=new ColumnInfo(fieldName, column.table(), column.name(), field.getType(),java.sql.Types.NULL);
filter=field.getAnnotation(ConditionColumn.class);
if (filter!=null){
columnInfo.setFilterable(filter.filterable());
if (filter.filterable() && filter.filterName()!=null && filter.filterName().length()>0){
columnInfo.setFilterName(filter.filterName());
}
}
}else{
columnInfo=getFieldColumn(fieldName, field);
}
if (columnInfo != null ){
id=field.getAnnotation(Id.class);
columnInfo.setIsId(id != null);
columns.put(fieldName, columnInfo);
}
}
//方法上注解，没有标准对应的属性。
for(Method method:methods){
columnInfo=getColumnFromMethod(method);
if (columnInfo!=null ){
id=method.getAnnotation(Id.class);
boolean isId=(id != null);
columnInfo.setIsId(isId);
if (!columns.containsKey(columnInfo.getFieldName())){
columns.put(columnInfo.getFieldName(),columnInfo);
}
//根据方法或属性上标志位Id,设置位主键字段
columnInfo=columns.get(columnInfo.getFieldName());
isId=isId||columnInfo.getIsId();
columnInfo.setIsId(isId);
}
}
}
//处理属性对应的方法上有Column注解的方法
private ColumnInfo getFieldColumn(String fieldName,Field field){
Column column;
String methodName;
String fieldGetName;
ColumnInfo columnInfo;
for(Method method:methods){
fieldGetName=methodName=method.getName();
if (methodName.startsWith("get")){
fieldGetName=methodName.substring(3);
}else if (methodName.startsWith("is")){
fieldGetName=methodName.substring(2);
}
if (fieldGetName.length()==0){
System.out.println("methodName:"+methodName);
continue;
}
fieldGetName=fieldGetName.substring(0,1).toLowerCase()+fieldGetName.substring(1);
if (!fieldGetName.equals(fieldName) ){
continue;
}
if (method.getAnnotation(Transient.class) != null)
continue;
column=method.getAnnotation(Column.class);
if (column != null){
columnInfo=new ColumnInfo(fieldName, column.table(), column.name(), field.getType(),java.sql.Types.NULL);
}else{
columnInfo=new ColumnInfo(fieldName, "", fieldName, field.getType(),java.sql.Types.NULL);
}
ConditionColumn filter=field.getAnnotation(ConditionColumn.class);
if (filter!=null) {
columnInfo.setFilterable(filter.filterable());
if (filter.filterable() && filter.filterName()!=null && filter.filterName().length()>0){
columnInfo.setFilterName(filter.filterName());
}
}
Id id=field.getAnnotation(Id.class);
columnInfo.setIsId(id != null);
return columnInfo;
}
return null;
}
private ColumnInfo getColumnFromMethod(Method method){
String methodName;
String fieldGetName;
ColumnInfo columnInfo;
fieldGetName=methodName=method.getName();
if (methodName.startsWith("get")){
fieldGetName=methodName.substring(3);
}else if (methodName.startsWith("is")){
fieldGetName=methodName.substring(2);
}
fieldGetName=fieldGetName.substring(0,1).toLowerCase()+fieldGetName.substring(1);
String fieldName=fieldGetName;
Column column = method.getAnnotation(Column.class);
Class<?> type=method.getReturnType();
if (column != null){
columnInfo=new ColumnInfo(fieldName, column.table(), column.name(), type,java.sql.Types.NULL);
ConditionColumn filter=method.getAnnotation(ConditionColumn.class);
if (filter!=null){
columnInfo.setFilterName(filter.filterName());
}
return columnInfo;
}else{
return null;
}
}
private void createSelectSQL(){
if (dataObjectType==BeanMappingType.Select){
checkSQL();
return;
}
StringBuffer selectFrom=new StringBuffer("select ");
columns.forEach((field,column)->selectFrom.append(column.getColumnName()).append(","));
selectFrom.replace(selectFrom.length()-1, selectFrom.length(), " ");
selectSQL =selectFrom.append("\r\n from ").append(tableName).toString();
}
private void checkSQL(){
int[] indexes=SqlUtil.indexAllOfKeywordInSql(selectSQL, "select");
if (indexes.length!=1){
throw new RuntimeException("暂时不支持Union功能 ，selectSQL只能包含了1个select,selectSQL:"+selectSQL);
}
indexes=SqlUtil.indexAllOfKeywordInSql(selectSQL, "from");
if (indexes.length!=1){
throw new RuntimeException("暂时不支持Union功能 ，selectSQL只能包含了1个from,selectSQL:"+selectSQL);
}
}
private void createCountSQL(){
if (dataObjectType==BeanMappingType.Select){
checkSQL();
return;
}
countSQL ="select count(*) \r\n from "+tableName;
}
private void createInsertSQL(){
if (dataObjectType==BeanMappingType.Select){
return ;
}
StringBuffer fields=new StringBuffer();
StringBuffer values=new StringBuffer();
Iterator<Entry<String,ColumnInfo>> colInfos=columns.entrySet().iterator();
ColumnInfo column;
for (int i=0;colInfos.hasNext();i++){
column=colInfos.next().getValue();
String columnName=column.getColumnName();
if (columnName == null || columnName.length() == 0)
continue;
if (i>0){
fields.append(",");
values.append(",");
}
fields.append(column.getColumnName());
values.append("#{entityBean.").append(column.getFieldName()).append("}");
}
insertSQL="insert into "+tableName+" ("+fields+") values ("+values+")";
}
private void createExistSQL(){
StringBuffer existSql=new StringBuffer();
existSql.append(" select count(*) from ").append(tableName).append(" where ");
int i=0;
for(ColumnInfo col:IdColumns){
if (i>0)
existSql.append(" and ");
existSql.append(col.getColumnName()).append(" = #{entityBean.").append(col.getFieldName()).append("}");
i++;
}
this.existSQL=existSql.toString();
}
private void createDeleteSQL(){
if (dataObjectType==BeanMappingType.Select){
return ;
}
StringBuffer dltSql=new StringBuffer();
dltSql.append("delete from ").append(tableName).append(" where ");
int i=0;
for(ColumnInfo col:IdColumns){
if (i>0)
dltSql.append(" and ");
dltSql.append(col.getColumnName()).append(" = #{entityBean.").append(col.getFieldName()).append("}");
i++;
}
this.deleteSQL=dltSql.toString();
}
private void createUpdateSQL(){
if (dataObjectType==BeanMappingType.Select){
return ;
}
StringBuffer updtSql=new StringBuffer();
updtSql.append("update ").append(tableName).append(" set ");
int i=0;
String columnName;
ColumnInfo column;
for (Entry<String,ColumnInfo> colEntry:columns.entrySet()){
column=colEntry.getValue();
columnName=column.getColumnName();
if (columnName == null || columnName.length() == 0)
continue;
if (column.getIsId())
continue;
if (i>0)
updtSql.append(",");
updtSql.append(column.getColumnName()).append(" = #{entityBean.").append(column.getFieldName()).append("}");
i++;
}
i=0;
updtSql.append(" where ");
for(ColumnInfo col:IdColumns){
if (i>0)
updtSql.append(" and ");
updtSql.append(col.getColumnName()).append(" = #{entityBean.").append(col.getFieldName()).append("}");
i++;
}
this.updateSQL=updtSql.toString();
}
private void createSaveSQL(){
if (dataObjectType==BeanMappingType.Select){
return ;
}
StringBuffer fields=new StringBuffer();
StringBuffer values=new StringBuffer();
Iterator<Entry<String,ColumnInfo>> colInfos=columns.entrySet().iterator();
ColumnInfo column;
for (int i=0;colInfos.hasNext();i++){
column=colInfos.next().getValue();
String columnName=column.getColumnName();
if (columnName == null || columnName.length() == 0)
continue;
if (i>0){
fields.append(",");
values.append(",");
}
fields.append(column.getColumnName());
values.append("#{entityBean.").append(column.getFieldName()).append("}");
}
StringBuffer savesql=new StringBuffer();
savesql.append("insert into ").append(tableName)
.append(" (").append(fields)
.append(") values (").append(values).append(") ON DUPLICATE KEY UPDATE ");
int i=0;
String columnName;
for (Entry<String,ColumnInfo> colEntry:columns.entrySet()){
column=colEntry.getValue();
columnName=column.getColumnName();
if (columnName == null || columnName.length() == 0)
continue;
if (column.getIsId())
continue;
if (i>0)
savesql.append(",");
savesql.append(column.getColumnName()).append(" = #{entityBean.").append(column.getFieldName()).append("}");
i++;
}
this.saveSQL=savesql.toString();
}
private void createGetByIdSQL(){
if (dataObjectType==BeanMappingType.Select){
return ;
}
StringBuffer getSql=new StringBuffer();
getSql.append("select * from ").append(tableName).append(" where ");
int i=0;
for(ColumnInfo col:IdColumns){
if (i>0)
getSql.append(" and ");
getSql.append(col.getColumnName()).append(" = #{entityId.").append(col.getFieldName()).append("}");
i++;
}
this.getByIdSQL=getSql.toString();
}
public String getSelectFrom(){
return selectSQL;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public String getSelectSQL() {
return selectSQL;
}
public void setSelectSQL(String selectString) {
this.selectSQL = selectString;
}
……(略去部分)
@Override
public Iterator<Entry<Class<?>, EntityBeanInfo>> iterator() {
return entityBeanInfos.entrySet().iterator();
}
}
2. ColumnInfo类
public class ColumnInfo {
private String fieldName;
private boolean isId=false;
private String tableName;
//结果集字段名
private String columnName;
//条件字段名
private String filterName;
private boolean filterable=true;
private Class<?> javaType;
private int jdbcType;
public ColumnInfo(String fieldName, String tableName, String columnName, Class<?> javaType,int jdbcType){
this.fieldName=fieldName;
this.tableName=tableName;
this.columnName=columnName;
this.javaType=javaType;
this.jdbcType=jdbcType;
if (this.columnName==null || this.columnName.trim().length()==0){
this.columnName=this.fieldName;
}
filterName=this.columnName;
}
public String getFieldName() {
return fieldName;
}
public void setFieldName(String fieldName) {
this.fieldName = fieldName;
}
public boolean getIsId() {
return isId;
}
public void setIsId(boolean isId) {
this.isId = isId;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
……(略去部分)
}
D. 扩展ExecutorInterceptor。
对于分页查询，需要提供数据库端分页，同时获取满足条件的数据行数，需要分两次执行，第一次执行select count(*) from ……,获得记录数后，再执行select …… from ……获取查询记录，因此定义一个PagedExecutorInterceptor，截获掉框架的Executor，分两次调用Executor的query方法，并组装成分页数据，返回给调用者。
PagedExecutorInterceptor类代码：
@Service
@Intercepts({ @Signature(type = Executor.class, method = "query", args = {
MappedStatement.class, Object.class, RowBounds.class,
ResultHandler.class }) })
public class PagedExecutorInterceptor implements Interceptor {
private final Logger logger = LoggerFactory.getLogger(this.getClass());
static int MAPPED_STATEMENT_INDEX = 0;
static int PARAMETER_INDEX = 1;
static int ROWBOUNDS_INDEX = 2;
static int RESULT_HANDLER_INDEX = 3;
IDialect dialect=new MySQLDialect();
private Properties properties;
public Object intercept(Invocation invocation) throws Throwable {
Object[] queryArgs = invocation.getArgs();
Object parameter = queryArgs[PARAMETER_INDEX];
Paginate<?> paginate=getPaginate((HashMap<String,Object>)parameter);
if (paginate != null && parameter instanceof HashMap ){
return execute(invocation,queryArgs,paginate);
}
return invocation.proceed();
}
protected Object execute(Invocation invocation,Object[] queryArgs,Paginate<?> paginate
) throws Throwable{
MappedStatement ms = (MappedStatement) queryArgs[MAPPED_STATEMENT_INDEX];
List<ResultMap> resultMaps=ms.getResultMaps();
int paginateIndex=getIndexInResultMap(resultMaps,Paginate.class);
Object parameter = queryArgs[PARAMETER_INDEX];
BoundSql boundSql = ms.getBoundSql(parameter);
String sql = boundSql.getSql().trim();
HashMap<String,Object> params=(HashMap<String,Object>) parameter;
Class<?> beanClass=(Class<?>)params.get("entityClass");
// 获取统计行数
String cntSql=getRowCountSQL(sql);
processIntercept(boundSql,queryArgs, paginate, Integer.class, paginateIndex, cntSql);
Object rowCount = invocation.proceed();
if (rowCount instanceof List) {
List<Integer> rows = (List<Integer>) rowCount;
int totalRows = rows.get(0);
paginate.setTotalRows(totalRows);
int totalPages = (totalRows + paginate.getPageSize() - 1) / paginate.getPageSize();
paginate.setTotalPages(totalPages);
}
//获取分页数据
String pagedSql=getPagedSQL(sql,paginate);
processIntercept(boundSql,queryArgs,paginate,beanClass,paginateIndex,pagedSql);
Object rows=invocation.proceed();
paginate.setDatas((List)rows);
List result=new ArrayList();
result.add(paginate);
return result;
}
/**
* 获取生成行数sql，本方法只是简单找到FROM位置和order by 位置。
* 暂时不支持对 包含union 字句的功能,支持包含有子查询的sql
* @param sql
* @return
*/
protected String getRowCountSQL(String sql){
int selectPos=SqlUtil.indexOfKeywordInSql(sql, "select");
int[] fromPoses=SqlUtil.indexAllOfKeywordInSql(sql, "from");
String lowsql=sql.toLowerCase();
String cntSql="select count(*) from ";
if (fromPoses.length==1){
int orderPos = lowsql.indexOf("order ",fromPoses[0]);
if (orderPos>0)
cntSql=sql.substring(0,selectPos)+cntSql+sql.substring(fromPoses[0]+5,orderPos);
else
cntSql=sql.substring(0,selectPos)+cntSql+sql.substring(fromPoses[0]+5);
}else{
cntSql=cntSql+"("+sql+") as t";
}
logger.debug("mybatis get rowCount sql:{}", cntSql);
return cntSql;
}
protected String getPagedSQL(String sql,Paginate<?> paginate){
String slctSql=sql;
if (paginate.getPageNo() < 1) {
paginate.setPageNo(1);
}
int offset = (paginate.getPageNo() - 1) * paginate.getPageSize();
int limit = paginate.getPageSize();
if (dialect.supportsLimitOffset()) {
slctSql = dialect.getLimitString(slctSql, offset, limit);
} else {
slctSql = dialect.getLimitString(slctSql, 0, limit);
}
logger.debug("mybatis pagedQuery sql:{}", slctSql);
return slctSql;
}
protected void processIntercept(BoundSql boundSql,final Object[] queryArgs,Paginate<?> paginate,Class<?> type,int index,String sql) {
MappedStatement ms = (MappedStatement) queryArgs[MAPPED_STATEMENT_INDEX];
final RowBounds rowBounds = (RowBounds) queryArgs[ROWBOUNDS_INDEX];
if (dialect.supportsLimit() ) {
MappedStatement newMs = createMappedStatement(type,index,ms,sql,boundSql);
queryArgs[MAPPED_STATEMENT_INDEX] = newMs;
queryArgs[ROWBOUNDS_INDEX]=new PagedRowBounds(type,paginate, rowBounds);
}
}
private Paginate<?> getPaginate(HashMap<String,Object> params){
for (Map.Entry<String, Object> param : params.entrySet()) {
if (param.getValue() instanceof Paginate)
return (Paginate) param.getValue();
}
return null;
}
private int getIndexInResultMap(List<ResultMap> resultMaps,Class<?> type){
ResultMap rm;
Class<?> entityClass;
for(int i=0;i<resultMaps.size();i++){
rm=resultMaps.get(i);
entityClass=rm.getType();
if (entityClass.equals(type)){
return i;
}
}
return -1;
}
private MappedStatement createMappedStatement(Class<?> beanClass,int index,MappedStatement ms,String sql,BoundSql boundSql){
BoundSql newBoundSql = new PagedBoundSql(beanClass,ms.getConfiguration(), sql,
boundSql.getParameterMappings(),
boundSql.getParameterObject());
MappedStatement newMs =InterceptorUtil.copyFromMappedStatement(beanClass,index,ms, new BoundSqlSqlSource(newBoundSql));
return newMs;
}
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
public void setProperties(Properties properties) {
this.properties = properties;
}
}
如何使用
只需要写好EntityBean，Mapper只需要扩展GenericMapper，就可以调用基类接口中的方法了。
1.定义个EntityBean或者定义一个查询Bean
先来个实体bean
@Entity
@Table(name = "AppInfo")
public class AppInfo {
@Id
private String appId;
private String name;
……(部分省略)
public String getAppId() {
return appId;
}
public void setAppId(String appId) {
this.appId = appId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
……(部分省略)
}
再来个查询bean
@Entity
@BeanSQLMap(mappingSQL = "select d.*,c.name as confName,c.value as configValue "
+ "from RfcServiceDef d join RfcServiceConfig c on d.id=c.serviceId ",
countSQL = "select count(*) "
+ "from RfcServiceDef d join RfcServiceConfig c on d.id=c.serviceId ")
public class RfcServiceAndConfig {
private int id;
private String name;
……(部分省略)
@Column(name="confName")
@ConditionColumn(filterName="c.name")
private String confName;
@Column(name="configValue")
@ConditionColumn(filterName="c.value")
private String configValue;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getConfName() {
return confName;
}
public void setConfName(String confName) {
this.confName = confName;
}
public String getConfigValue() {
return configValue;
}
public void setConfigValue(String configValue) {
this.configValue = configValue;
}
}
2.继承扩展一下GenericMapper
@Mapper
public interface AppInfoMapper extends GenericMapper<AppInfo> {
}
@Mapper
public interface RfcServiceAndConfigMapper extends GenericMapper<RfcServiceAndConfig> {
}
现在，你不用写基类方法，也不用配XML，就可以使用了，下面来个例子。
@Autowired
private AppInfoMapper appInfoMapper;
@Autowired
RfcServiceAndConfigMapper rfcServiceAndConfigMapper;
……(部分省略)
Map<String,Object> paras=new HashMap<>();
paras.put("appId_like","2019");
paras.put("name","零售抽奖");
List<AppInfo> result =appInfoMapper.query(AppInfo.class, paras);
……(部分省略)
Map<String,Object> paras=new HashMap<>();
paras.put("chnName_like", "会员");
Paginate<RfcServiceAndConfig> paginate=new Paginate<>();
paginate.setPageNo(2);
paginate.setPageSize(10);
Paginate<RfcServiceAndConfig> result = rfcServiceAndConfigMapper.pagedQuery(RfcServiceAndConfig.class,paginate, paras,"");
写在后面
通过粗浅的学习了解，个人觉得：
１．Hibernate学习的成本比Mybatis大，特别是他基于对象的视角看待数据，表与表数据关系体现为实体数据间关系，数据操作主要采用Hql，要掌握Hql，需要开发人员从对象及对象关系的视角重新审视SQL，对开发人员OOP的能力要求稍微更高一些。
２．Mybatis主要以手动编写SQL语句以及ResultMap的方式使用，重点关注在数据上。而Hibernate映射机制封装度的抽象层级更高，开发人员不需要关心SQL的生成与结果映射，让开发人员更专注于业务流程和数据关系上。
３. 相比Hibernate来看，Mybatis还是要简陋一些，在持久化层要低一些。Mybatis宣称让开发人员可以自由写SQL，成为他流行的两点，降低开发人员门槛，也就降低了开发的成本。当然使用Hibernate，如果遇到性能问题时一样可以直接使用SQL，这在以前的Hibernate项目中依然将SQL作为性能调优的强有力的手段。
４．Hibernate的HQL语句与具体使用的数据库无关，移植性很好。MyBatis项目中所有的SQL语句都是依赖所用的数据库的，所以数据库移植的支持度相对差。
这只是个人的一些使用体会，不是为了评价或测评这两个框架的优缺点，当然也不是为了挑起两大阵营间的论战，这不是我的初衷。
再有就是，由于使用还没有三周，一定会有很多地方理解错误，或者使用上的错误，还望不吝赐教，批评指正。
5370
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
