《tkMybatis進階（sql監控+業務監控+添加cache緩存）》

生活語錄：別人下一代活得像自己一樣窩囊。

記得有人說過：別人快的時候，你要學會慢下來！

一、目錄

1.1、tk-mybtais的使用案例（三種例子）。

1.2、tk-mybtatis接入監控。

1.3、添加cache緩存。

1.4、tk-mybtais和mybatis-plus的區別。

二、接入步驟

2.1、tk-mybatis使用案例。

2.1.1、使用自帶對象查詢類似jpa和mybtais-plus。

<code> /**
     * 方式一 使用自帶的查詢
     * 主要涉及對象 Criteria 和 Example
     * @date 2020-05-07
     * @param account
     * @return
     */
     default List getUserInfoByParam(String account){
        Example example = new Example(UserInfo.class);
        Example.Criteria criteria =example.createCriteria();
        if(StringUtils.isEmpty(account)){
            criteria.andEqualTo("account",account);
        }
        return selectByExample(example);
    }/<code>

2.1.2、配置xml文件，和mybatis一致。

<code>/**
     * 方式二 和 mybatis 一致
     *  步驟：
     *   (1)需要配置xml
     *  （2）在配置文件中指定xml 路徑
     * @param account
     */
    UserInfo getUserInfoByAccount(@Param("account") String account);/<code>

注：添加xml 文件，application指定路徑。

2.1.3、使用自帶方法

<code>    /**
     * 方式三
     *  直接使用內置方法
     * @param uid
     * @return
     */
    @Transactional(propagation = Propagation.REQUIRED,rollbackFor = Exception.class,timeout = 10)
    @Override
    public UserInfo getUserInfo(Long uid) {
        return userInfoMapper.selectByPrimaryKey(uid);
    }/<code> 

2.2、添加監控

2.2.1、封裝自己的Mapper

<code>/**
 * 封裝自己的   MyBaseMapper
 * @param 
 */
public interface MyBaseMapper extends Mapper, MySqlMapper {
    
}/<code>

2.2.2、注入自定義SQLMapper

<code>
@tk.mybatis.mapper.annotation.RegisterMapper
public interface SQLMapper {

	@Select(" ")
	T executeSQL(@Param("sql") String sql);
	
	public default T execute(String relationWhere, Weekend... weekendList) {
		String sql = null;
		try {
			SQLMapperProvider provider = new SQLMapperProvider();
			sql = provider.getSelectSQL(relationWhere, weekendList);
		}catch(Exception ex) {
			ex.printStackTrace();
		}
		if(!StringUtils.isEmpty(sql)) {
			return executeSQL(sql);
		}
		return null;
	}
}/<code>

2.2.3、自定義SQLMapperProvider

<code>/**
 * note:映射script
 * @ExampleProvider
 */
public class SQLMapperProvider {
	/**
	 * note:多表關聯查詢方法
	 * @param relationWhere 關聯表間的條件
	 * @param weekendList   查詢對象集
	 * @return
	 */
	public String getSelectSQL(String relationWhere, Weekend... weekendList) {
		List tableList = new ArrayList();
		List whereList = new ArrayList();

		StringBuilder sql = new StringBuilder("SELECT ");
		if(weekendList[0].isDistinct()) {
			sql.append("distinct ");
		}

		for (int i = 0; i < weekendList.length; i++) {
			EntityTable entityTable = EntityHelper.getEntityTable(weekendList[i].getEntityClass());
			if(!ObjectUtils.isEmpty(entityTable)) {
				// 條
				if (i > 0) {
					sql.append(",");
				}
				String columnSQL = EntityHelper.getSelectColumns(weekendList[i].getEntityClass());
				String[] columns = columnSQL.split(",");
				// 段
				for (int num = 0; num < columns.length; num++) {
					if (num > 0) {
						sql.append(",");
					}
					sql.append(entityTable.getName() + "." + columns[num]);
				}
				
				// where
				if(!CollectionUtils.isEmpty(weekendList[i].getOredCriteria())) {
					whereList.add(weekendList[i].getOredCriteria()
							.stream().map(criteriaList->criteriaList.getCriteria()
							.stream().map(
										criteria -> entityTable.getName() + "." 
										+ criteria.getCondition()
										+ ((criteria.getValue() instanceof String)?"'"+criteria.getValue()+"'":criteria.getValue())
									)
								.collect(Collectors.joining(" and "))
							).collect(Collectors.joining(" and ")));
				}
				tableList.add(entityTable);
			}
		}
		sql.append(" FROM " + tableList.stream().map(table -> table.getName()).collect(Collectors.joining(",")));

		// condition
//      sql.append(SqlHelper.exampleWhereClause());		兼容count翻頁問題
		String where = "";
		if(!CollectionUtils.isEmpty(whereList)) {
			where = whereList.stream().collect(Collectors.joining(" and "));
		}
		if(!StringUtils.isEmpty(where) || !StringUtils.isEmpty(relationWhere)) {
			sql.append(" WHERE ");
			if(!StringUtils.isEmpty(where)) {
				sql.append(where);
				
				if(!StringUtils.isEmpty(relationWhere)) {
					sql.append(" and ");
				}
			}
			if(!StringUtils.isEmpty(relationWhere)) {
				sql.append(relationWhere);
			}
		}
		// order
		sql.append(SqlHelper.orderByDefault(weekendList[0].getEntityClass()));
		return sql.toString();
	}
}/<code>

2.2.4封裝service層

<code>/**
 * Service接口類
 * @date 2020-05-07
 */
public interface BaseInterfaceService {

	Boolean create(T entity);

	Boolean updateById(T entity);

	T get(T entity);

	T getById(T id);

	int count(T entity);

	List list(T entity, int pageNum, int pageSize);

	List listAll();

	Boolean deleteById(T id);
}
/<code>

2.2.5、封裝MyBaseService

<code>/**
 * note:Service基礎類
 * 
 */
public abstract class MyBaseService {

	@Autowired(required = false)
	protected MyBaseMapper mapper;

	public Boolean create(T entity) {
		int count = this.mapper.insertSelective(entity);
		return count == 0? false: true;
	}
}/<code>

2.2.6、封裝業務ServiceTemlate 添加業務層監控

<code>Slf4j
public abstract class ServiceTemplate {

	protected String monitorKey;

	protected ServiceTemplate(String monitorKey) {
		this.monitorKey = monitorKey;
	}

	protected ServiceTemplate(String monitoryKey, String monitorType) {
		this.monitorKey = monitoryKey;
	}

	/**
	 * AOP統一記監控
	 */
	protected ServiceTemplate() {
	}

	protected abstract void checkParams();

	protected abstract T process();

	protected void afterProcess() {
	}

	protected void onSuccess() {
	}

	protected void onServiceException(Exception e) {
		log.warn("執行邏輯異常 monitoryKey={}, msg={}", monitorKey, e.getMessage(), e);
		throw new ServiceException(e.getMessage());
	}

	protected void onServiceCodeException(ServiceException e) {
		log.warn("執行邏輯異常 monitoryKey={}, code={}, msg={}", monitorKey, e.getCode(), e.getMsg(), e);
		throw e;
	}

	protected void onError(Throwable e) {
		log.error("執行邏輯異常 monitoryKey={}", monitorKey, e);
		throw new ServiceException(RespCode.SYSTEM_ERROR.getErrorCode(), RespCode.SYSTEM_ERROR.getErrorMsg());
	}

	public T execute() {
		if (monitorKey==null || monitorKey.equals("")) {
			return doExecute();
		} else {
			// TODO Monitor
			return doExecute();
		}
	}

	protected T doExecute() {
		try {
			checkParams();
		} catch (IllegalArgumentException e) {
			if (log.isDebugEnabled()) {
				log.debug("校驗參數失敗", e);
			} else {
				log.info("校驗參數失敗: " + e.getMessage());
			}
			throw new ServiceException(RespCode.COMMON_PARAM_ERROR.getErrorCode(),
					RespCode.COMMON_PARAM_ERROR.getErrorMsg() + e.getMessage(), e);
		} catch (ServiceException e) {
			log.error("校驗參數失敗:" + e.getMsg());
			throw e;
		}

		try {
			T result = process();
			onSuccess();
			return result;
		} catch (ServiceException e) {
			onServiceCodeException(e);
			return null;
		} catch (Throwable e) {
			onError(e);
			return null;
		} finally {
			afterProcess();
		}
	}
}/<code>

2.2.7、業務層調用

<code>/**
 * 業務層
 * 2020-05-06
 *
 */
@Service
public class UserInfoServiceImpl extends BaseService implements UserInfoService {

    @Autowired(required = false)
    private UserInfoMapper userInfoMapper;

    @Override
    public boolean updateUserInfo(UserInfo userInfo){
        Integer update = userInfoMapper.updateUserInfo(userInfo.getAccount(), userInfo.getUserName(),
                userInfo.getPhone(), userInfo.getUid());
        return update > 0;
    }/<code>

2.2.8、Mapper層調用

<code>/**
 * 接入 tk-mybatis
 *
 */
@org.apache.ibatis.annotations.Mapper
public interface UserInfoMapper extends MyBaseMapper {

    default Integer  updateUserInfo(String account,String userName,String phone,Long uid){
        StringBuffer sqlSb = new StringBuffer();
        StringBuffer whereSB = new StringBuffer();
        sqlSb.append("account = "+account+",");
        sqlSb.append("username = "+userName+",");
        sqlSb.append("phone = "+phone+",");
        whereSB.append("where uid = "+uid);
        sqlSb.append(whereSB.toString());
        return update(sqlSb.toString());
    }

    @Update("update user_info set ${sql}")
    Integer update(@Param("sql") String sql);/<code>

2.3、添加查詢緩存

2.3.1、引入maven

<code>
            org.springframework.boot
            spring-boot-starter-data-redis
        
         
		
		    com.alicp.jetcache
		    jetcache-starter-redis
		    2.5.13
		/<code>

2.3.2、添加redis及jetcache配置

<code>#緩存設置
jetcache:
  statIntervalMinutes: 15
  areaInCacheName: false
  remote:
    default:
      type: redis
      keyConvertor: fastjson
      valueEncoder: kryo
      valueDecoder: kryo
      poolConfig:
        minIdle: 5
        maxIdle: 20
        maxTotal: 50
      database: 1
  #jetcache使用
  redis:
    database: 0
    host: 127.0.0.1
    port: 6379
    password: qwe123456

    lettuce:
      pool:
        max-idle: 10
        min-idle: 0
        max-wait-millis: 1000
        max-total: 20/<code>

2.3.3、引入業務層緩存

<code>import com.alicp.jetcache.anno.Cached;
import com.cloud.user.entity.UserInfo;

import java.util.concurrent.TimeUnit;

public interface UserInfoService {
    /**
     * 緩存用戶信息
     * @param uid
     * @return
     */
    @Cached(expire = 10,timeUnit = TimeUnit.SECONDS)
    UserInfo getUserInfo(Long uid);/<code>

2.4、tk-mybtais和mybatis-plus的區別。

<code>#-----------mybatis-plus----------------
# mybatis-plus 支持熱加載 。
# mybatis-plus內置代碼生成器。
# mybatis-plus內置分頁插件。
# mybatis-plus內置性能解析插件 對於update delete 有隻能分析阻斷策略。
# mybatis-plus內置sql注入剝離器 ，防止sql注入攻擊。
#-----------tk-mybatis----------------
# tk-mybatis 自帶樂觀鎖
# tk-mybatis 支持pagehelper/<code>

關鍵字: append weekendList protected