tkmybatis是一款简化开发者重复工作的组件,除了常用的增删改查,也能自定义自己常用的sql语句。
想要的结果:面向对象去简化,规范常用的sql语句,实现in,find_in_set等功能
maven引用
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper</artifactId>
<version>3.3.8</version>
</dependency>
自定义的sql方法
public interface QueryExtendsMapper<T,R> {
//使用obj对象查询出列表
@SelectProvider(type = QueryExtendsProvider.class, method = "dynamicSQL")
List<R> queryByQDO(T obj);
//使用obj对象查询单个对象,如结果有多个则抛异常
@SelectProvider(type = QueryExtendsProvider.class, method = "dynamicSQL")
R queryOneByQDO(T obj);
}
自定义方法的实现
public class QueryExtendsProvider extends MapperTemplate {
//查询参数类型
private Class paramClass;
//返回结果类型
private Class returnClass;
/**
* 查询
*
* @param ms
* @return
*/
public String queryByQDO(MappedStatement ms) {
StringBuilder sql = this.commonSql(ms , false);
return sql.toString();
}
public String queryOneByQDO(MappedStatement ms) {
StringBuilder sql = this.commonSql(ms,false);
return sql.toString();
}
public static String whereAllIfColumns(Class<?> entityClass, boolean empty) {
StringBuilder sql = new StringBuilder();
sql.append("<where>");
//获取全部列
Set<EntityColumn> columnList = EntityHelper.getColumns(entityClass);
List<Field> allFieldsList = FieldUtils.getAllFieldsList(entityClass);
Map<String, Field> collect = allFieldsList.stream().collect(Collectors.toMap(Field::getName, e -> e ,(field, field2) -> field));
//当某个列有主键策略时,不需要考虑他的属性是否为空,因为如果为空,一定会根据主键策略给他生成一个值
for (EntityColumn column : columnList) {
if (Collection.class.isAssignableFrom(column.getJavaType())) {
Type genericType = collect.get(column.getProperty()).getGenericType();
Type actualTypeArgument = ((ParameterizedType) genericType).getActualTypeArguments()[0];
List<Field> listWithAnnotation = FieldUtils.getFieldsListWithAnnotation((Class<?>) actualTypeArgument, Column.class);
if (CollectionUtils.isNotEmpty(listWithAnnotation)) {
StringBuffer colStr = new StringBuffer();
StringBuffer proStr = new StringBuffer();
colStr.append("(");
proStr.append("(");
for (int i = 0; i < listWithAnnotation.size(); i++) {
if (i > 0) {
colStr.append(",");
proStr.append(",");
}
colStr.append(listWithAnnotation.get(i).getAnnotationsByType(Column.class)[0].name());
proStr.append("#{item.").append(listWithAnnotation.get(i).getName()).append("}");
}
colStr.append(")");
proStr.append(")");
String content = " %s in " +
"<foreach collection=\"%s\" index=\"index\" item=\"item\" open=\"(\" separator=\",\" close=\")\">\n" +
" %s \n" +
" </foreach>";
sql.append(SqlHelper.getIfNotNull(column, " AND " + String.format(content, colStr , column.getProperty() ,proStr), empty));
} else {
String content = " %s in " +
"<foreach collection=\"%s\" index=\"index\" item=\"item\" open=\"(\" separator=\",\" close=\")\">\n" +
" #{item}\n" +
" </foreach>";
sql.append(SqlHelper.getIfNotNull(column, " AND " + String.format(content, column.getColumn() , column.getProperty()), empty));
}
} else {
Field field = collect.get(column.getProperty());
QueryColumn annotation = field.getAnnotation(QueryColumn.class);
if (annotation != null) {
switch (annotation.queryColumn()) {
case EQUAL: {
sql.append(SqlHelper.getIfNotNull(column, " AND " + column.getColumnEqualsHolder(), empty));
break;
}
case FIND_IN_SET: {
sql.append(SqlHelper.getIfNotNull(column, " AND " + String.format("FIND_IN_SET(%s,%s)", column.getColumnHolder() , column.getColumn()), empty));
break;
}
}
} else {
sql.append(SqlHelper.getIfNotNull(column, " AND " + column.getColumnEqualsHolder(), empty));
}
}
}
sql.append("</where>");
return sql.toString();
}
private StringBuilder commonSql(MappedStatement ms , boolean count) {
this.getEntityClass(ms);
//修改返回值类型为实体类型
if (count) {
//setResultType(ms, Integer.class);
} else {
setResultType(ms, this.returnClass);
}
StringBuilder sql = new StringBuilder();
if (count) {
sql.append(SqlHelper.selectCount(this.returnClass));
} else {
sql.append(SqlHelper.selectAllColumns(this.returnClass));
}
sql.append(SqlHelper.fromTable(this.returnClass, tableName(this.returnClass)));
sql.append(this.whereAllIfColumns(this.paramClass, isNotEmpty()));
return sql;
}
@Override
public Class<?> getEntityClass(MappedStatement ms) {
Map<String, Class<?>> entityClassMap = null;
Class<?> superMapperClass = null;
MapperHelper mapperHelper = null;
try {
entityClassMap = (Map<String, Class<?>>) FieldUtils.readField(this,"entityClassMap" , true);
superMapperClass = (Class<?>) FieldUtils.readField(this,"mapperClass" , true);
mapperHelper = (MapperHelper) FieldUtils.readField(this,"mapperHelper" , true);
} catch (IllegalAccessException e) {
e.printStackTrace();
}
String msId = ms.getId();
if (entityClassMap.containsKey(msId)) {
return entityClassMap.get(msId);
} else {
Class<?> mapperClass = getMapperClass(msId);
Type[] types = mapperClass.getGenericInterfaces();
for (Type type : types) {
if (type instanceof ParameterizedType) {
ParameterizedType t = (ParameterizedType) type;
if (t.getRawType() == superMapperClass || superMapperClass.isAssignableFrom((Class<?>) t.getRawType())) {
Class<?> returnType = (Class<?>) t.getActualTypeArguments()[1];
Class<?> paramClass = (Class<?>) t.getActualTypeArguments()[0];
this.paramClass = paramClass;
this.returnClass = returnType;
//获取该类型后,第一次对该类型进行初始化
EntityHelper.initEntityNameMap(returnType, mapperHelper.getConfig());
EntityHelper.initEntityNameMap(paramClass, mapperHelper.getConfig());
entityClassMap.put(msId, returnType);
return returnType;
}
}
}
}
throw new RuntimeException("无法获取Mapper<T>泛型类型:" + msId);
}
}
查询参数类型
@Data
public class StudentQDO extends StudentDO {
//使用注解标识对应的数据库字段,使用集合去实现数据库的in效果
@Column(name = "class_id")
private Collection<Long> classIdList;
}