mybatis执行sql语句

 

目的：

 

1. 解决统计（求和、数量）需要去配置xml的问题。

2. 解决返回字段未知情况

3. mybatis 执行自定sql

 

一、 sql组装工具类

 

package com.onem2.pingmi.base.common.util;

import java.io.Serializable;

/**
 * 
 * @ClassName: SqlBuilder
 * @Description: (这里用一句话描述这个类的作用)
 * @author heliang
 * @date 2017-12-4 下午4:32:07
 * @version V2.1 * Update Logs: * Name: * Date: * Description: 初始化
 */
@SuppressWarnings("serial")
public class SqlBuilder implements Serializable {
    private String select;
    private String from;
    private StringBuilder where = new StringBuilder();
    private String groupBy;
    private String having;
    private String orderBy;
    private String limit;

    public SqlBuilder() {
    }

    public SqlBuilder(String select, String from) {
        if (!isEmpty(select)) {
            this.select = select;
        }
        if (!isEmpty(from)) {
            this.from = from;
        }
    }

    public String getSelect() {
        return select;
    }

    public void setSelect(String select) {
        this.select = select;
    }

    public String getLimit() {
        return limit;
    }

    public void setLimit(String limit) {
        this.limit = limit;
    }

    public SqlBuilder select(String select) {
        if (!isEmpty(select)) {
            this.select = select;
        }
        return this;
    }

    public SqlBuilder from(String from) {
        if (!isEmpty(from)) {
            this.from = from;
        }
        return this;
    }

    public SqlBuilder where(String where) {
        if (isEmpty(where)) {
            this.where.setLength(0);
        } else {
            this.where.append(" and " + where);
        }
        return this;
    }

    public SqlBuilder where(String columnOperator, String value) {
        if (!isEmpty(value)) {
            where.append(" and " + columnOperator + " '" + sqlParam(value)
                    + "'");
        }
        return this;
    }

    public SqlBuilder groupBy(String groupBy) {
        this.groupBy = groupBy;
        return this;
    }

    public SqlBuilder having(String having) {
        this.having = having;
        return this;
    }

    public SqlBuilder orderBy(String orderBy) {
        this.orderBy = orderBy;
        return this;
    }

    public SqlBuilder limit(String limit) {
        this.limit = limit;
        return this;
    }

    public SqlBuilder eq(String column, String value) {
        if (!isEmpty(value)) {
            where.append(" and " + column + " = '" + sqlParam(value) + "'");
        }
        return this;
    }

    public SqlBuilder ne(String column, String value) {
        if (!isEmpty(value)) {
            where.append(" and " + column + " != '" + sqlParam(value) + "'");
        }
        return this;
    }

    public SqlBuilder like(String column, String value) {
        if (!isEmpty(value)) {
            where.append(" and " + column + " like '%" + sqlParam(value) + "%'");
        }
        return this;
    }

    public SqlBuilder notLike(String column, String value) {
        if (!isEmpty(value)) {
            where.append(" and " + column + " not like '%" + sqlParam(value)
                    + "%'");
        }
        return this;
    }

    public SqlBuilder in(String column, String... values) {
        if (!isEmpty(values)) {
            where.append(" and " + column + " in (" + inValuesString(values)
                    + ")");
        }
        return this;
    }

    public SqlBuilder notIn(String column, String... values) {
        if (!isEmpty(values)) {
            where.append(" and " + column + " not in ("
                    + inValuesString(values) + ")");
        }
        return this;
    }

    public SqlBuilder gt(String column, String value) {
        if (!isEmpty(value)) {
            where.append(" and " + column + " > '" + sqlParam(value) + "'");
        }
        return this;
    }

    public SqlBuilder gte(String column, String value) {
        if (!isEmpty(value)) {
            where.append(" and " + column + " >= '" + sqlParam(value) + "'");
        }
        return this;
    }

    public SqlBuilder lt(String column, String value) {
        if (!isEmpty(value)) {
            where.append(" and " + column + " < '" + sqlParam(value) + "'");
        }
        return this;
    }

    public SqlBuilder lte(String column, String value) {
        if (!isEmpty(value)) {
            where.append(" and " + column + " <= '" + sqlParam(value) + "'");
        }
        return this;
    }

    public SqlBuilder between(String column, String from, String to) {
        if (isEmpty(from) && isEmpty(to)) {
            return this;
        }
        if (isEmpty(to)) {
            where.append(" and " + column + " >= '" + sqlParam(from) + "'");
        } else if (isEmpty(from)) {
            where.append(" and " + column + " <= '" + sqlParam(to) + "'");
        } else {
            where.append(" and " + column + " between '" + sqlParam(from)
                    + "' and '" + sqlParam(to) + "'");
        }
        return this;
    }

    public String sql() {
        if (isEmpty(select)) {
            throw new IllegalArgumentException("select must not be empty");
        }
        if (isEmpty(from)) {
            throw new IllegalArgumentException("from must not be empty");
        }
        StringBuilder sql = new StringBuilder("select " + select + " from "
                + from);
        final int a = 4;
        final int b = 5;
        if (where.length() > a) {
            sql.append(" where " + where.substring(b));
        }
        if (!isEmpty(groupBy)) {
            sql.append(" group by " + groupBy);
        }
        if (!isEmpty(having)) {
            sql.append(" having " + having);
        }
        if (!isEmpty(orderBy)) {
            sql.append(" order by " + orderBy);
        }
        if (!isEmpty(limit)) {
            sql.append(" limit " + limit);
        }
        return sql.toString();
    }

    public String toString() {
        return sql();
    }

    private static boolean isEmpty(String value) {
        return value == null || "".equals(value) || value.trim().length() == 0;
    }

    private static boolean isEmpty(String[] values) {
        if (values == null || values.length == 0) {
            return true;
        }
        for (String value : values) {
            if (!isEmpty(value)) {
                return false;
            }
        }
        return true;
    }

    private static String inValuesString(String[] values) {
        StringBuilder string = new StringBuilder();
        for (String value : values) {
            if (isEmpty(value)) {
                continue;
            }
            string.append('\'');
            string.append(value);
            string.append('\'');
            string.append(',');
        }
        if (string.length() > 0) {
            string.deleteCharAt(string.length() - 1);
        }
        return string.toString();
    }

    private static String sqlParam(String sqlParam) {
        return sqlParam.replaceAll("([';]+|(--)+)", "");
    }
}

 

 

 

二、 mybatis 的 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.onem2.pingmi.util.mapper.UtilMapper" >
<!-- 统计 -->
<select id="count" resultType="HashMap" parameterType="String" >
   ${value}
</select>
<!-- 删除 -->
<delete id="delete" parameterType="String" >
   ${value}
</delete>
<!-- 列表查询 -->
<select id="list" resultType="HashMap" parameterType="String" >
   ${value}
</select>
<!-- 求和 -->
<select id="sums" resultType="HashMap" parameterType="String" >
   ${value}
</select>
</mapper>

三、 Mapper 书写

 

 

/** 
 * @Description: (用一句话描述该文件做什么) 
 * @author heliang 
 * @date 2017-12-2 下午2:35:08 
 * @version V2.1 
 */

package com.onem2.pingmi.util.mapper;

import java.util.List;
import java.util.Map;

/**
 * @ClassName: UtilMapper
 * @Description: (这里用一句话描述这个类的作用)
 * @author heliang
 * @date 2017-12-2 下午2:35:08
 * @version V2.1 * Update Logs: * Name: * Date: * Description: 初始化
 */
public interface UtilMapper {
    /**
     * @Title: countSql
     * @Description: 执行sql
     * @author heliang
     * @param sql
     * @return
     * @throws
     */
    Map<String, Object> count(String sql);

    /**
     * @Title: delete
     * @Description: 删除命令
     * @author heliang
     * @param sql
     * @return
     * @throws
     */
    Integer delete(String sql);

    /**
     * @Title: list
     * @Description: 列表
     * @author heliang
     * @param sql
     * @return
     * @throws
     */
    List<Map<String, Object>> list(String sql);

    /**
     * @Title: sums
     * @Description: 求和
     * @author heliang
     * @param sql
     * @return
     * @throws
     */
    Map<String, Object> sums(String sql);
}

四、 service实现

 

 

/** 
 * @Description: (用一句话描述该文件做什么) 
 * @author heliang 
 * @date 2017-12-2 下午2:47:03 
 * @version V2.1 
 */

package com.onem2.pingmi.util.service.impl;

import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.onem2.pingmi.base.common.result.Result;
import com.onem2.pingmi.base.common.util.SimpleLogFormater;
import com.onem2.pingmi.base.common.util.SqlBuilder;
import com.onem2.pingmi.base.helper.ObjectHelper;
import com.onem2.pingmi.util.mapper.UtilMapper;
import com.onem2.pingmi.util.service.TongjiService;

/**
 * @ClassName: TongjiServiceImpl
 * @Description: (这里用一句话描述这个类的作用)
 * @author heliang
 * @date 2017-12-2 下午2:47:03
 * @version V2.1 * Update Logs: * Name: * Date: * Description: 初始化
 */
@Service("tongjiService")
public class TongjiServiceImpl implements TongjiService {
    private static Logger log = Logger.getLogger(TongjiServiceImpl.class);

    @Autowired
    private UtilMapper utilMapper;

    @Override
    public Result<Integer> count(SqlBuilder sqlBuilder) {
        log.info(SimpleLogFormater.formatParams(sqlBuilder.sql()));
        Result<Integer> result = null;
        // 参数校验
        if (sqlBuilder.sql() == null) {
            result = Result.newFailure("参数缺失", "无执行SQL语句。");
            log.info(SimpleLogFormater.formatResult(result));
            return result;
        }
        try {
            Map<String, Object> obj = this.utilMapper.count(sqlBuilder.sql());
            if (ObjectHelper.isNotEmpty(obj.get("count"))) {
                Integer value = Integer.valueOf(obj.get("count").toString());
                result = Result.newSuccess(value);
            } else {
                result = Result.newFailure("执行错误", "执行SQL获得结果错误，请检查SQL");
            }
        } catch (Exception e) {
            result = Result.newFailure("执行错误", "执行SQL获得结果错误，请检查SQL");
            log.error(SimpleLogFormater.formatException(result.getMessage(), e));
            log.info(SimpleLogFormater.formatResult(result));
            return result;
        }
        // 返回结果
        log.info(SimpleLogFormater.formatResult(result));
        return result;
    }

    @Override
    public Result<Integer> delete(SqlBuilder sqlBuilder) {
        log.info(SimpleLogFormater.formatParams(sqlBuilder.sql()));
        Result<Integer> result = null;
        // 参数校验
        if (sqlBuilder.sql() == null) {
            result = Result.newFailure("参数缺失", "无执行SQL语句。");
            log.info(SimpleLogFormater.formatResult(result));
            return result;
        }
        try {
            Integer value = this.utilMapper.delete(sqlBuilder.sql());
            result = Result.newSuccess(value);
        } catch (Exception e) {
            result = Result.newFailure("执行错误", "执行SQL获得结果错误，请检查SQL");
            log.error(SimpleLogFormater.formatException(result.getMessage(), e));
            log.info(SimpleLogFormater.formatResult(result));
            return result;
        }
        // 返回结果
        log.info(SimpleLogFormater.formatResult(result));
        return result;
    }

    @Override
    public Result<List<Map<String, Object>>> list(SqlBuilder sqlBuilder) {
        log.info(SimpleLogFormater.formatParams(sqlBuilder.sql()));
        Result<List<Map<String, Object>>> result = null;
        // 参数校验
        if (sqlBuilder.sql() == null) {
            result = Result.newFailure("参数缺失", "无执行SQL语句。");
            log.info(SimpleLogFormater.formatResult(result));
            return result;
        }
        try {
            List<Map<String, Object>> maps = this.utilMapper.list(sqlBuilder
                    .sql());
            result = Result.newSuccess(maps);
        } catch (Exception e) {
            result = Result.newFailure("执行错误", "执行SQL获得结果错误，请检查SQL");
            log.error(SimpleLogFormater.formatException(result.getMessage(), e));
            log.info(SimpleLogFormater.formatResult(result));
            return result;
        }
        // 返回结果
        log.info(SimpleLogFormater.formatResult(result));
        return result;
    }

    @Override
    public Result<Map<String, Object>> sums(SqlBuilder sqlBuilder) {
        log.info(SimpleLogFormater.formatParams(sqlBuilder.sql()));
        Result<Map<String, Object>> result = null;
        // 参数校验
        if (sqlBuilder.sql() == null) {
            result = Result.newFailure("参数缺失", "无执行SQL语句。");
            log.info(SimpleLogFormater.formatResult(result));
            return result;
        }
        try {
            Map<String, Object> map = this.utilMapper.sums(sqlBuilder.sql());
            result = Result.newSuccess(map);
        } catch (Exception e) {
            result = Result.newFailure("执行错误", "执行SQL获得结果错误，请检查SQL");
            log.error(SimpleLogFormater.formatException(result.getMessage(), e));
            log.info(SimpleLogFormater.formatResult(result));
            return result;
        }
        // 返回结果
        log.info(SimpleLogFormater.formatResult(result));
        return result;
    }

}

然后任意Controller 都可以可以注入统计方法，必须去每一张表去写统计了。