mybatis sql自动生成

本文提供了一种自动生成sql语句的方法。

1.编辑一个拦截器

package ;

import ;
import ;

import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;

/**
 * 通过拦截<code>StatementHandler</code>的<code>prepare</code>方法，重写sql语句实现物理分页。
 * 签名里要拦截的类型只能是接口。
 * 
 * @author jeff he
 * 
 */
/*
 * @Intercepts是mybatis的注解，@Intercepts用于表明当前的对象是一个Interceptor，而@Signature则表明要拦截的接口、方法以及对应的参数类型。
 */
@Intercepts({ @Signature(type = , method = "prepare", args = {  }) })
public class AutoBuildInterceptor implements Interceptor {
    private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
    private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();
    /**
     * 正则匹配变量，用于拦截sql方法
     */
    private static final String UPDATE_MATCHER = "^.*update.*";
    private static final String INSERT_MATCHER = "^.*insert.*";
    private static final String BATCH_UPDATE_MATCHER = "^.*batchUpdate.*";
    private static final String BATCH_INSERT_MATCHER = "^.*batchInsert.*";
    private static final String DELETEBYID_MATCHER = "^.*deleteById.*";
    private static final String GETBYID_MATCHER = "^.*getById.*";
    private static final String LIST_MATCHER = "^.*list.*";
    private static final String FINDBYPAGE_MATCHER = "^.*findByPage";

    public static void main(String[] args) {
        ("batchUpdatexx".matches(BATCH_UPDATE_MATCHER));
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        // ("auto");
        StatementHandler statementHandler = (StatementHandler) invocation
                .getTarget();
        MetaObject metaStatementHandler = (
                statementHandler, DEFAULT_OBJECT_FACTORY,
                DEFAULT_OBJECT_WRAPPER_FACTORY);
        // 分离代理对象链(由于目标类可能被多个拦截器拦截，从而形成多次代理，通过下面的两次循环可以分离出最原始的的目标类)
        while (("h")) {
            Object object = ("h");
            metaStatementHandler = (object,
                    DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
        }
        // 分离最后一个代理对象的目标类
        while (("target")) {
            Object object = ("target");
            metaStatementHandler = (object,
                    DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
        }
        /**
         * 上面的代码基本上是固定的
         */
        MappedStatement mappedStatement = (MappedStatement) metaStatementHandler
                .getValue("");
        String sqlId = ();

        BoundSql boundSql = (BoundSql) metaStatementHandler
                .getValue("");
        // 获得参数
        Object parameterObject = ();
        String sql = ();
        // 重写sql
        if ((INSERT_MATCHER))
            sql = (SqlBulider
                    .buildRawInsertSql(parameterObject));
        else if ((UPDATE_MATCHER))
            sql = (SqlBulider
                    .buildRawUpdateSql(parameterObject));
        else if ((GETBYID_MATCHER))
            sql = (parameterObject);
        else if ((DELETEBYID_MATCHER))
            sql = (parameterObject);
        else if ((LIST_MATCHER)
                || (FINDBYPAGE_MATCHER))
            sql = (parameterObject);
        else if ((BATCH_INSERT_MATCHER))
            sql = (sql);
        else if ((BATCH_UPDATE_MATCHER))
            sql = (sql);
        ("", sql);
        // 将执行权交给下一个拦截器
        return ();
    }

    @Override
    public Object plugin(Object target) {
        // 当目标类是StatementHandler类型时，才包装目标类，否者直接返回目标本身,减少目标被代理的次数
        if (target instanceof StatementHandler) {
            return (target, this);
        } else {
            return target;
        }
    }

    @Override
    public void setProperties(Properties properties) {
    }

}

2.重写sql的sql构造类

package ;

import ;
import ;
import ;

import ;

public class SqlBulider {

    private static SimpleDateFormat sdf = new SimpleDateFormat(
            "yyyy-MM-dd HH:mm:ss");

    // 重写插入语句不含log
    public static String buildRawInsertSql(Object obj)
            throws IllegalArgumentException, IllegalAccessException {
        Class<?> clz = ();//获取实体类
        String clzName = (); //获取操作的表名/实体类名
        String _clzName = (clzName);//命名转换
        StringBuilder rawInsertSql = new StringBuilder("insert into `"
                + _clzName + "` ");
        StringBuilder columnsStr = new StringBuilder("(");
        StringBuilder valuesStr = new StringBuilder("(");
        // 获取本身的属性
        Field[] localFields = ();
        // 获取继承的属性（必须为public的）
        Field[] inheritFields = ();
        String _name;
        String fName;
        Object value;
        for (Field field : inheritFields) {//遍历继承的属性
            /**
             * 抑制Java的访问控制检查 如果不加上上面这句，将会Error: TestPrivate can not access a
             * member of class PrivateClass with modifiers "private"
             */
            (true);
            fName = ();//属性名
            _name = (fName);//转换命名方式
            value = (obj);//获取属性值
            // 如果id为""就替换掉，easyui的form表单提交会默认提交个""进来
            if (("id") && ("")){
                value = ().toString();
            }       
            (_name + ",");
            // 跳过null
            if (value != null) {
                if (() ==  && value != null)
                    ("'" + value + "',");
                else if (() == )
                    ("'" + (() value)
                            + "'");
                else
                    (value + ",");
            }
        }
        for (Field field : localFields) {
            /**
             * 抑制Java的访问控制检查 如果不加上上面这句，将会Error: TestPrivate can not access a
             * member of class PrivateClass with modifiers "private"
             */
            (true);
            fName = ();
            _name = (fName);
            value = (obj);
            (_name);
            if (() ==  && value != null)
                ("'" + value + "'");
            else if (() ==  && value != null)
                valuesStr
                        .append("'" + (() value) + "'");
            else
                (value);
            if (field == localFields[ - 1]) {
                (")");
                (")");
            } else {
                (",");
                (",");
            }
        }
        (columnsStr + " values " + valuesStr);
        return ();
    }

    // 构建原生更新语句
    public static String buildRawUpdateSql(Object obj)
            throws IllegalArgumentException, IllegalAccessException,
            NoSuchFieldException, SecurityException {
        Class<?> clz = ();
        String clzName = ();
        String _clzName = (clzName);
        StringBuilder rawUpdateSql = new StringBuilder("update  `" + _clzName
                + "` set");
        // 获取本身的属性
        Field[] fields = ();
        // 获取继承的属性（必须为public的）
        Field[] inheritFields = ();
        String _name;
        String fName;
        Object value;
        for (Field field : inheritFields) {
            // 跳过主键
            if (().equals("id"))
                continue;
            /**
             * 抑制Java的访问控制检查 如果不加上上面这句，将会Error: TestPrivate can not access a
             * member of class PrivateClass with modifiers "private"
             */
            (true);
            fName = ();
            _name = (fName);
            value = (obj);
            if (() ==  && value != null)
                (" " + _name + " = '" + value + "',");
            else if (() ==  && value != null)
                (" " + _name + " = '"
                        + (() value) + "',");
            else if (value != null)
                (" " + _name + " = " + value + ",");
        }
        for (Field field : fields) {
            // 跳过主键
            if (().equals("id"))
                continue;
            /**
             * 抑制Java的访问控制检查 如果不加上上面这句，将会Error: TestPrivate can not access a
             * member of class PrivateClass with modifiers "private"
             */
            (true);
            fName = ();
            _name = (fName);
            value = (obj);
            // 跳过null
            if (value != null) {
                if (() == )
                    (" " + _name + " = '" + value + "',");
                else if (() == )
                    (" " + _name + " = '"
                            + (() value) + "',");
                else
                    (" " + _name + " = " + value + ",");
            }
        }
        // 删除最后一个多余的逗号
        (() - 1);
        // 获取主键
        Field idField = ("id");
        Object id = (obj);
        if (() == )
            (" where id = '" + id + "'");
        else
            (" where id = " + id);
        return ();
    }

    // 构建删除语句
    public static String buildDeleteByIdSql(Object id) {
        String clzName = ();
        String _clzName = (clzName);
        StringBuilder deleteByIdSql = new StringBuilder("delete from `"
                + _clzName + "` where id = ");
        if (() ==  && id != null)
            ("'" + id + "'");
        else if (id != null)
            (id);
        return ();
    }

    // 构建getById语句
    public static String buildGetByIdSql(Object id) {
        String clzName = ();
        String _clzName = (clzName);
        StringBuilder getByIdSql = new StringBuilder("select * from `"
                + _clzName + "` where id = ");
        if (id == null)
            throw new SqlSessionException("Id 不能为 null");
        else if (() == )
            ("'" + id + "'");
        else
            (id);
        return ();
    }

    // 构建默认query语句
    public static String buildQuerySql(Object obj)
            throws IllegalArgumentException, IllegalAccessException {
        Class<?> clz = ();
        String clzName = ();
        String _clzName = (clzName);
        StringBuilder querySql = new StringBuilder("select * from `" + _clzName
                + "`  where id is not null");
        // 获取本身的属性
        Field[] fields = ();
        // 获取继承的属性（必须为public的）
        Field[] inheritFields = ();
        String _name;
        String fName;
        Object value;
        for (Field field : inheritFields) {
            /**
             * 抑制Java的访问控制检查 如果不加上上面这句，将会Error: TestPrivate can not access a
             * member of class PrivateClass with modifiers "private"
             */
            (true);
            fName = ();
            _name = (fName);
            value = (obj);
            if (() ==  && value != null)
                (" and " + _name + " = '" + value + "'");
            else if (value != null)
                (" and " + _name + " = " + value);
        }
        for (Field field : fields) {
            (true);
            fName = ();
            _name = (fName);
            value = (obj);
            if (() ==  && value != null)
                (" and " + _name + " = '" + value + "'");
            else if (value != null)
                (" and " + _name + " = " + value);
        }
        return ();
    }

    // 重写插入语句
    public static String buildLogInsertSql(String sql) {
        StringBuilder insertSql = new StringBuilder(sql);
        String createId = ();
        ((")"), ",create_dt,create_id");
        ((")"), ",now(),'" + createId
                + "'");
        return ();
    }

    // 重写更新语句
    public static String buildLogUpdateSql(String sql) {
        StringBuilder updateSql = new StringBuilder(sql);
        String updateId = ();
        if (updateId != null)
            (("where"),
                    ",update_dt=now(),update_id='" + updateId + "' ");
        else
            (("where"),
                    ",update_dt=now(),update_hljs-string">" ");
        return ();
    }

    // 为批量更新加入log字段
    public static String buildBatchUpdateSql(String sql) {
        sql = ();
        StringBuilder updateSql = new StringBuilder(sql);
        String updateId = ();
        String addStr;
        if (updateId != null)
            addStr = ",update_dt=now(),update_id='" + updateId + "' ";
        else
            addStr = ",update_dt=now(),update_id= null ";
        int fromIndex = 0;
        int index = 0;
        while (fromIndex < ()
                && (index = ("where", fromIndex)) != -1) {
            (index, addStr);
            fromIndex = index + () + 1;
        }
        return ();
    }

    // 为批量插入加入log字段
    public static String buildBatchInsertSql(String sql) {
        sql = ().replaceAll("uuid\\(\\)", "uuid");
        StringBuilder insertSql = new StringBuilder(sql);
        String createId = ();
        int fromIndex = 0;
        int index2 = 0;
        int index1 = 0;
        String addStr1 = ",create_dt,create_id";
        String addStr2 = ",now(),'" + createId + "'";
        if (createId != null)
            addStr2 = ",now(),'" + createId + "'";
        else
            addStr2 = ",now(),null";
        index1 = (")", fromIndex);
        (index1, addStr1);
        fromIndex = index1 + () + 1;
        while (fromIndex < ()
                && (index2 = (")", fromIndex)) != -1) {
            (index2, addStr2);
            fromIndex = index2 + () + 1;
        }
        return ().replaceAll("uuid", "uuid()");
    }
}

—用于记录当前线程用户基本信息

package ;

public class MybatisContext {

    private static ThreadLocal<String> userId = new ThreadLocal<String>();

    private static ThreadLocal<String> ip = new ThreadLocal<String>();

    private static ThreadLocal<String> url = new ThreadLocal<String>();

    private static ThreadLocal<String> clzName = new ThreadLocal<String>();

    public static void clearContext() {
        ();
        ();
        ();
        ();
    }

    public static String getClzName() {
        return clzName.get();
    }

    public static void setClzName(String _clzName) {
        clzName.set(_clzName);
    }

    public static void removeClzName() {
        ();
    }

    public static String getUserId() {
        return userId.get();
    }

    public static void setUserId(String _userId) {
        userId.set(_userId);
    }

    public static void removeUserId() {
        ();
    }

    public static String getIp() {
        return ip.get();
    }

    public static void setIp(String _ip) {
        ip.set(_ip);
    }

    public static void removeIp() {
        ();
    }

    public static String getUrl() {
        return url.get();
    }

    public static void setUrl(String _url) {
        url.set(_url);
    }

    public static void removeUrl() {
        ();
    }
}

4.命名转换类

package ;

/**
 * 驼峰命名转下划线
 * @author jeff he
 *
 */
public class NameConverter {

    public static String conver(String name) {
        StringBuilder result = new StringBuilder();
        if (name != null && () > 0) {
            // 将第一个字符处理成小写
            ((0, 1).toLowerCase());
            // 循环处理其余字符
            for (int i = 1; i < (); i++) {
                String s = (i, i + 1);
                // 在大写字母前添加下划线
                if ((())
                        && !((0))) {
                    ("_");
                }
                // 其他字符直接转成大写
                (());
            }
        }
        return ();
    }

}

5.可以写一个baseMapper如下，让所有继承，便可以使所有
mapper有这几个通用方法，具体sql是由上面代码自动生成。

package ;

import ;
import .List;

public interface BaseMapper<T, ID extends Serializable> {

    int deleteById(ID id);

    int insert(T t);

    int update(T t);

    T getById(ID id);

    List<T> list(T t);

    List<T> findByPage(T t);

}

<mapper namespace="">

    <resultMap type="User" id="userRM">
    </resultMap>

    <!-- 上面是一个整体 -->

    <insert id="insert" parameterType="User">
    </insert>

    <delete id="deleteById" parameterType="String">
    </delete>

    <update id="update" parameterType="User">
    </update>

    <select id="getById" parameterType="String" resultType="User">
    </select>

    <select id="list" parameterType="User" resultMap="userRM">
    </select>

    <select id="findByPage" parameterType="User" resultMap="userRM">
    </select>
</mapper>

参考网址：深入浅出Mybatis-sql自动生成