基于 com.github.abel533-mapper改造的实现的动态查询字段,mapper已经实现了单表的操作不需要写配置文件,但是唯一的不足就是查询的时候不能根据具体的业务区查询指定的字段,默认是查询所有的字段,这样在数据访问量大的情况下性能会跟不上。几天基于这种物配制的基础,在原有的基础上修改了查询方便实现了根据业务查询指定的字段,实现方法很简单,在执行sql之前把需要查询的字段和本地线程绑定,然后在正真构造sql的时候用根本的线程的数据替换掉默认的所以字段,具体实现如下:
用自己的接口掩盖掉默认的接口(大多数是copy,因为默认接口是泛型,所以如果直接继承会有很多问题需要处理,所以这里是直接copy整个接口的代码,并添加了一个接口)
- package com.github.liuzw.mapper.Mapper;
- import java.util.List;
- import org.apache.ibatis.annotations.DeleteProvider;
- import org.apache.ibatis.annotations.InsertProvider;
- import org.apache.ibatis.annotations.Param;
- import org.apache.ibatis.annotations.SelectProvider;
- import org.apache.ibatis.annotations.UpdateProvider;
- /**
- * 改写默认的Mapper接口,新增根据id批量删除接口
- *
- * @author good-zhiwei 刘志伟
- * @data 2016年7月10日 下午12:41:00
- * @param <T>
- */
- public interface LiuzwMapper<T> {
- /**
- * 根据实体类不为null的字段进行查询,条件全部使用=号and条件
- *
- * @param record
- * @return
- */
- @SelectProvider(type = LiuzwMapperProvider.class, method = "dynamicSQL")
- List<T> select(T record);
- /**
- * 根据实体类不为null的字段查询总数,条件全部使用=号and条件
- *
- * @param record
- * @return
- */
- @SelectProvider(type = LiuzwMapperProvider.class, method = "dynamicSQL")
- int selectCount(T record);
- /**
- * 根据主键进行查询,必须保证结果唯一 单个字段做主键时,可以直接写主键的值 联合主键时,key可以是实体类,也可以是Map
- *
- * @param key
- * @return
- */
- @SelectProvider(type = LiuzwMapperProvider.class, method = "dynamicSQL")
- T selectByPrimaryKey(Object key);
- /**
- * 插入一条数据 支持Oracle序列,UUID,类似Mysql的INDENTITY自动增长(自动回写) 优先使用传入的参数值,参数值空时,才会使用序列、UUID,自动增长
- *
- * @param record
- * @return
- */
- @InsertProvider(type = LiuzwMapperProvider.class, method = "dynamicSQL")
- int insert(T record);
- /**
- * 插入一条数据,只插入不为null的字段,不会影响有默认值的字段 支持Oracle序列,UUID,类似Mysql的INDENTITY自动增长(自动回写)
- * 优先使用传入的参数值,参数值空时,才会使用序列、UUID,自动增长
- *
- * @param record
- * @return
- */
- @InsertProvider(type = LiuzwMapperProvider.class, method = "dynamicSQL")
- int insertSelective(T record);
- /**
- * 根据实体类中字段不为null的条件进行删除,条件全部使用=号and条件
- *
- * @param key
- * @return
- */
- @DeleteProvider(type = LiuzwMapperProvider.class, method = "dynamicSQL")
- int delete(T key);
- /**
- * 通过主键进行删除,这里最多只会删除一条数据 单个字段做主键时,可以直接写主键的值 联合主键时,key可以是实体类,也可以是Map
- *
- * @param key
- * @return
- */
- @DeleteProvider(type = LiuzwMapperProvider.class, method = "dynamicSQL")
- int deleteByPrimaryKey(Object key);
- /**
- * 根据主键进行更新,这里最多只会更新一条数据 参数为实体类
- *
- * @param record
- * @return
- */
- @UpdateProvider(type = LiuzwMapperProvider.class, method = "dynamicSQL")
- int updateByPrimaryKey(T record);
- /**
- * 根据主键进行更新 只会更新不是null的数据
- *
- * @param record
- * @return
- */
- @UpdateProvider(type = LiuzwMapperProvider.class, method = "dynamicSQL")
- int updateByPrimaryKeySelective(T record);
- /**
- * 根据主键ID批量删除
- *
- * @param key
- * @return
- */
- @DeleteProvider(type = LiuzwMapperProvider.class, method = "dynamicSQL")
- int deleteByIDS(@Param("ids") Object[] key);
- }
- package com.github.liuzw.mapper.Mapper;
- import static org.apache.ibatis.jdbc.SqlBuilder.BEGIN;
- import static org.apache.ibatis.jdbc.SqlBuilder.DELETE_FROM;
- import static org.apache.ibatis.jdbc.SqlBuilder.SQL;
- import java.util.ArrayList;
- import java.util.List;
- import java.util.Set;
- import org.apache.ibatis.builder.StaticSqlSource;
- import org.apache.ibatis.jdbc.SQL;
- import org.apache.ibatis.mapping.MappedStatement;
- import org.apache.ibatis.mapping.ParameterMapping;
- import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;
- import org.apache.ibatis.scripting.xmltags.IfSqlNode;
- import org.apache.ibatis.scripting.xmltags.MixedSqlNode;
- import org.apache.ibatis.scripting.xmltags.SqlNode;
- import org.apache.ibatis.scripting.xmltags.StaticTextSqlNode;
- import org.apache.ibatis.scripting.xmltags.TextSqlNode;
- import org.apache.ibatis.scripting.xmltags.WhereSqlNode;
- import com.github.abel533.mapper.MapperProvider;
- import com.github.abel533.mapperhelper.EntityHelper;
- import com.github.abel533.mapperhelper.MapperHelper;
- import com.github.liuzw.threadlocal.DynamicColumnThreadLocal;
- /**
- * 改写默认的MapperProvider,添加根据id批量删除方法,支持动态字段的查询
- *
- * @author good-zhiwei 刘志伟
- * @data 2016年7月10日 下午12:41:40
- */
- public class LiuzwMapperProvider extends MapperProvider {
- public LiuzwMapperProvider(Class<?> mapperClass, MapperHelper mapperHelper) {
- super(mapperClass, mapperHelper);
- // TODO Auto-generated constructor stub
- }
- /**
- * 根据本地线程获取需要查询的字段
- * @param entityClass
- * @return
- */
- private String getColumn(Class<?> entityClass){
- String tableColumn;
- if(DynamicColumnThreadLocal.get()!=null){
- tableColumn=DynamicColumnThreadLocal.get();
- }
- else{
- tableColumn= EntityHelper.getSelectColumns(entityClass);
- }
- return tableColumn;
- }
- /**
- * 查询
- *
- * @param ms
- * @return
- */
- @Override
- public SqlNode select(MappedStatement ms) {
- Class<?> entityClass = getSelectReturnType(ms);
- //修改返回值类型为实体类型
- setResultType(ms, entityClass);
- List<SqlNode> sqlNodes = new ArrayList<SqlNode>();
- //静态的sql部分:select column ... from table
- sqlNodes.add(new StaticTextSqlNode("SELECT "
- + getColumn(entityClass)
- + " FROM "
- + tableName(entityClass)));
- //将if添加到<where>
- sqlNodes.add(new WhereSqlNode(ms.getConfiguration(), getAllIfColumnNode(entityClass)));
- StringBuilder orderBy = new StringBuilder();
- for (EntityHelper.EntityColumn column : EntityHelper.getColumns(entityClass)) {
- if (column.getOrderBy() != null) {
- orderBy.append(column.getColumn()).append(" ").append(column.getOrderBy()).append(",");
- }
- }
- if (orderBy.length() > 0) {
- orderBy.insert(0, "order by");
- sqlNodes.add(new StaticTextSqlNode(orderBy.substring(0, orderBy.length() - 1)));
- }
- return new MixedSqlNode(sqlNodes);
- }
- /**
- * 根据主键进行查询
- *
- * @param ms
- */
- @Override
- public void selectByPrimaryKey(MappedStatement ms) {
- final Class<?> entityClass = getSelectReturnType(ms);
- //获取主键字段映射
- List<ParameterMapping> parameterMappings = getPrimaryKeyParameterMappings(ms);
- //开始拼sql
- String sql = new SQL() {{
- //select全部列
- SELECT(getColumn(entityClass));
- //from表
- FROM(tableName(entityClass));
- //where条件,主键字段=#{property}
- WHERE(EntityHelper.getPrimaryKeyWhere(entityClass));
- }}.toString();
- //使用静态SqlSource
- StaticSqlSource sqlSource = new StaticSqlSource(ms.getConfiguration(), sql, parameterMappings);
- //替换原有的SqlSource
- setSqlSource(ms, sqlSource);
- //将返回值修改为实体类型
- setResultType(ms, entityClass);
- }
- /**
- * 根据Example查询
- *
- * @param ms
- * @return
- */
- @Override
- public SqlNode selectByExample(MappedStatement ms) {
- Class<?> entityClass = getSelectReturnType(ms);
- //将返回值修改为实体类型
- setResultType(ms, entityClass);
- List<SqlNode> sqlNodes = new ArrayList<SqlNode>();
- //静态的sql部分:select column ... from table
- sqlNodes.add(new StaticTextSqlNode("SELECT"));
- IfSqlNode distinctSqlNode = new IfSqlNode(new StaticTextSqlNode("DISTINCT"), "distinct");
- sqlNodes.add(distinctSqlNode);
- sqlNodes.add(new StaticTextSqlNode(EntityHelper.getSelectColumns(entityClass) + " FROM " + tableName(entityClass)));
- IfSqlNode ifNullSqlNode = new IfSqlNode(exampleWhereClause(ms.getConfiguration()), "_parameter != null");
- sqlNodes.add(ifNullSqlNode);
- IfSqlNode orderByClauseSqlNode = new IfSqlNode(new TextSqlNode("order by ${orderByClause}"), "orderByClause != null");
- sqlNodes.add(orderByClauseSqlNode);
- return new MixedSqlNode(sqlNodes);
- }
- /**
- * 通过ids批量删除
- *
- * @param ms
- * @return
- */
- public SqlNode deleteByIDS(MappedStatement ms) {
- Class<?> entityClass = getSelectReturnType(ms);
- Set<EntityHelper.EntityColumn> entityColumns = EntityHelper.getPKColumns(entityClass);
- EntityHelper.EntityColumn entityColumn = null;
- for (EntityHelper.EntityColumn entity : entityColumns) {
- entityColumn = entity;
- break;
- }
- EntityHelper.EntityColumn column = entityColumn;
- List<SqlNode> sqlNodes = new ArrayList<SqlNode>();
- // 开始拼sql
- BEGIN();
- // delete from table
- DELETE_FROM(tableName(entityClass));
- // 得到sql
- String sql = SQL();
- // 静态SQL部分
- sqlNodes.add(new StaticTextSqlNode(sql + " WHERE " + column.getColumn() + " IN "));
- // 构造foreach sql
- SqlNode foreach = new ForEachSqlNode(ms.getConfiguration(), new StaticTextSqlNode("#{"
- + column.getProperty() + "}"), "ids", "index", column.getProperty(), "(", ")", ",");
- sqlNodes.add(foreach);
- return new MixedSqlNode(sqlNodes);
- }
- }
还需要一个本地线程类用于保存需要查询的字段
- package com.github.liuzw.threadlocal;
- /**
- * 需要查询的字段放到本地线程
- *
- * @author good-zhiwei 刘志伟
- * @data 2016年7月10日 下午12:52:52
- */
- public class DynamicColumnThreadLocal {
- public static final ThreadLocal<String> THREAD_DYNAMICCOLUMN=new ThreadLocal<String>();
- /**
- * 把当前需要执行的sql的列绑定到线程
- * @param user
- */
- public static void set(String dynamicColumn){
- THREAD_DYNAMICCOLUMN.set(dynamicColumn);
- }
- /**
- * 获取当前需要执行sql的列
- * @param user
- */
- public static String get(){
- return THREAD_DYNAMICCOLUMN.get();
- }
- /**
- * 清除当前线程
- */
- public static void clear(){
- THREAD_DYNAMICCOLUMN.set(null);
- }
- }
接下来就需要告诉大家怎么样用了
- <?xml version="1.0" encoding="UTF-8" ?>
- <!DOCTYPE configuration
- PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
- "http://mybatis.org/dtd/mybatis-3-config.dtd">
- <!-- mybatis配置 -->
- <configuration>
- <settings>
- <!-- 开启驼峰映射 -->
- <setting name="mapUnderscoreToCamelCase" value="true" />
- </settings>
- <!-- 配置插件 -->
- <plugins>
- <!-- 分页查询控件 -->
- <plugin interceptor="com.github.pagehelper.PageHelper">
- <property name="dialect" value="mysql" />
- <!-- 该参数默认为false -->
- <!-- 设置为true时,使用RowBounds分页会进行count查询 -->
- <property name="rowBoundsWithCount" value="true" />
- </plugin>
- <!-- 通用mapper -->
- <plugin interceptor="com.github.abel533.mapperhelper.MapperInterceptor">
- <property name="identity" value="mysql" />
- <property name="mappers" value="com.github.liuzw.mapper.Mapper.LiuzwMapper" />
- </plugin>
- </plugins>
- </configuration>
具体用法如下:
po对象
- package com.liuzw.study.pojo;
- import java.util.Date;
- import javax.persistence.Column;
- import javax.persistence.GeneratedValue;
- import javax.persistence.GenerationType;
- import javax.persistence.Id;
- import javax.persistence.Table;
- import org.springframework.format.annotation.DateTimeFormat;
- @Table(name="tb_user")//指定表名称
- public class User implements java.io.Serializable{
- private static final long serialVersionUID = 1L;
- @Id
- @GeneratedValue(strategy=GenerationType.IDENTITY)
- private Long id;
- // 用户名
- @Column(name="user_name")
- private String userName;
- // 密码
- private String password;
- // 姓名
- private String name;
- // 年龄
- private Integer age;
- // 性别,1男性,2女性
- private Integer sex;
- // 出生日期
- @DateTimeFormat(pattern="yyyy-MM-dd")
- private Date birthday;
- // 创建时间
- private Date created;
- // 更新时间
- private Date updated;
- public Long getId() {
- return id;
- }
- public void setId(Long id) {
- this.id = id;
- }
- public String getUserName() {
- return userName;
- }
- public void setUserName(String userName) {
- this.userName = userName;
- }
- public String getPassword() {
- return password;
- }
- public void setPassword(String password) {
- this.password = password;
- }
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public Integer getAge() {
- return age;
- }
- public void setAge(Integer age) {
- this.age = age;
- }
- public Integer getSex() {
- return sex;
- }
- public void setSex(Integer sex) {
- this.sex = sex;
- }
- public Date getBirthday() {
- return birthday;
- }
- public void setBirthday(Date birthday) {
- this.birthday = birthday;
- }
- public Date getCreated() {
- return created;
- }
- public void setCreated(Date created) {
- this.created = created;
- }
- public Date getUpdated() {
- return updated;
- }
- public void setUpdated(Date updated) {
- this.updated = updated;
- }
- @Override
- public String toString() {
- return "User [id=" + id + ", userName=" + userName + ", password=" + password + ", name=" + name
- + ", age=" + age + ", sex=" + sex + ", birthday=" + birthday + ", created=" + created
- + ", updated=" + updated + "]";
- }
- }
- package com.liuzw.study.mapper;
- import com.github.liuzw.mapper.Mapper.LiuzwMapper;
- import com.liuzw.study.pojo.User;
- public interface UserMapper extends LiuzwMapper<User> {
- }
service 代码:
- package com.liuzw.study.service;
- import java.util.List;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.stereotype.Service;
- import com.github.liuzw.threadlocal.DynamicColumnThreadLocal;
- import com.liuzw.study.mapper.UserMapper;
- import com.liuzw.study.pojo.User;
- @Service
- public class UserService {
- @Autowired
- private UserMapper userMapper;
- public List<User> queryAll(){
- User record=new User();
- record.setUserName("liuzhiwei");
- //SEX,AGE,UPDATED,USER_NAME USERNAME,NAME,BIRTHDAY,CREATED,ID,PASSWORD
- DynamicColumnThreadLocal.set("id,sex,age");//动态查询
- return userMapper.select(record);
- //return userMapper.select(record);
- }
- }