是否支持xml格式的sql表达式？比如用mybatis的表达式

[anymoreT]

Issues on GitHub are intended to be related to bugs or feature requests, so we recommend using our other community resources instead of asking here.

• SOAR Doc
• Any other questions can be asked in the community

[martianzhang]

阿里的同学开源的sqlautoreview貌似支持您说的这个需求，SOAR暂无计划支持。

[martianzhang]

@anymoreT 如果您能提供一些mybatis的例子供后续支持参考就再好不过了。

[anymoreT]

<?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.dao.RoleMapper" >
  <resultMap id="BaseResultMap" type="com.Role" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="role_name" property="roleName" jdbcType="VARCHAR" />
    <result column="descpt" property="descpt" jdbcType="VARCHAR" />
    <result column="code" property="code" jdbcType="VARCHAR" />
    <result column="insert_uid" property="insertUid" jdbcType="INTEGER" />
    <result column="insert_time" property="insertTime" jdbcType="TIMESTAMP" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, role_name, descpt, code, insert_uid, insert_time, update_time
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    from role
    where id = #{id,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from role
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insertSelective" parameterType="com.wyait.manage.pojo.Role" >
    insert into role
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="roleName != null" >
        role_name,
      </if>
      <if test="descpt != null" >
        descpt,
      </if>
      <if test="code != null" >
        code,
      </if>
      <if test="insertUid != null" >
        insert_uid,
      </if>
      <if test="insertTime != null" >
        insert_time,
      </if>
      <if test="updateTime != null" >
        update_time,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=INTEGER},
      </if>
      <if test="roleName != null" >
        #{roleName,jdbcType=VARCHAR},
      </if>
      <if test="descpt != null" >
        #{descpt,jdbcType=VARCHAR},
      </if>
      <if test="code != null" >
        #{code,jdbcType=VARCHAR},
      </if>
      <if test="insertUid != null" >
        #{insertUid,jdbcType=INTEGER},
      </if>
      <if test="insertTime != null" >
        #{insertTime,jdbcType=TIMESTAMP},
      </if>
      <if test="updateTime != null" >
        #{updateTime,jdbcType=TIMESTAMP},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.wyait.manage.pojo.Role" >
    update role
    <set >
      <if test="roleName != null" >
        role_name = #{roleName,jdbcType=VARCHAR},
      </if>
      <if test="descpt != null" >
        descpt = #{descpt,jdbcType=VARCHAR},
      </if>
      <if test="code != null" >
        code = #{code,jdbcType=VARCHAR},
      </if>
      <if test="insertUid != null" >
        insert_uid = #{insertUid,jdbcType=INTEGER},
      </if>
      <if test="insertTime != null" >
        insert_time = #{insertTime,jdbcType=TIMESTAMP},
      </if>
      <if test="updateTime != null" >
        update_time = #{updateTime,jdbcType=TIMESTAMP},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.wyait.manage.pojo.Role" >
    update role
    set role_name = #{roleName,jdbcType=VARCHAR},
      descpt = #{descpt,jdbcType=VARCHAR},
      code = #{code,jdbcType=VARCHAR},
      insert_uid = #{insertUid,jdbcType=INTEGER},
      insert_time = #{insertTime,jdbcType=TIMESTAMP},
      update_time = #{updateTime,jdbcType=TIMESTAMP}
    where id = #{id,jdbcType=INTEGER}
  </update>

  <insert id="insert" useGeneratedKeys="true" keyProperty="id" parameterType="com.wyait.manage.pojo.Role" >
    insert into role (id, role_name, descpt,
    code, insert_uid, insert_time,
    update_time)
    values (#{id,jdbcType=INTEGER}, #{roleName,jdbcType=VARCHAR}, #{descpt,jdbcType=VARCHAR},
    #{code,jdbcType=VARCHAR}, #{insertUid,jdbcType=INTEGER}, #{insertTime,jdbcType=TIMESTAMP},
    #{updateTime,jdbcType=TIMESTAMP})
  </insert>
  <!--=Start findList 分页查询角色列表-->
  <select id="findList" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from role
  </select>
  <!--=End findList 分页查询角色列表-->

  <!--=Start findRoleAndPerms 查询角色列表-->
  <resultMap id="rolePerms" type="com.wyait.manage.entity.RoleVO" >
    <id column="id" property="id" />
    <result column="role_name" property="roleName"/>
    <result column="code" property="code"/>
    <result column="descpt" property="descpt"/>
    <result column="insert_uid" property="insertUid"/>
    <result column="insert_time" property="insertTime"/>
    <collection property="rolePerms" ofType="com.wyait.manage.pojo.RolePermissionKey">
      <result  column="role_id" property="roleId"/>
      <result column="permit_id" property="permitId"/>
    </collection>
  </resultMap>

  <select id="findRoleAndPerms" resultMap="rolePerms">
    select
    r.*,rp.role_id,rp.permit_id
    from role r
    INNER JOIN role_permission rp ON r.id=rp.role_id
    WHERE r.id=#{id}
  </select>
  <!--=End findRoleAndPerms 查询角色列表-->

  <!--=Start getRoleByUserId 根据用户id查询角色数据-->
  <select id="getRoleByUserId" resultType="Role">
    select
    ur.role_id id,r.role_name,r.code
    from user_role ur
    INNER JOIN role r ON r.id=ur.role_id
    WHERE ur.user_id=#{userId}
  </select>
  <!--=End getRoleByUserId 根据用户id查询角色数据-->

  <!--=Start getRoles 查询角色列表-->
  <select id="getRoles" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from role
  </select>
  <!--=End getRoles 查询角色列表-->
</mapper>

[anymoreT]

<update id="updateByPrimaryKeySelective" parameterType="com.wyait.manage.pojo.User">
        update user
        <set>
            <if test="username != null">
                username = #{username,jdbcType=VARCHAR},
            </if>
            <if test="mobile != null">
                mobile = #{mobile,jdbcType=VARCHAR},
            </if>
            <if test="email != null">
                email = #{email,jdbcType=VARCHAR},
            </if>
            <if test="password != null and password !=''">
                password = #{password,jdbcType=VARCHAR},
            </if>
            <if test="insertUid != null">
                insert_uid = #{insertUid,jdbcType=INTEGER},
            </if>
            <if test="insertTime != null">
                insert_time = #{insertTime,jdbcType=TIMESTAMP},
            </if>
            <if test="updateTime != null">
                update_time = #{updateTime,jdbcType=TIMESTAMP},
            </if>
            <if test="isDel != null">
                is_del = #{isDel,jdbcType=BIT},
            </if>
            <if test="isJob != null">
                is_job = #{isJob,jdbcType=BIT},
            </if>
            <if test="mcode != null">
                mcode = #{mcode,jdbcType=VARCHAR},
            </if>
            <if test="sendTime != null">
                send_time = #{sendTime,jdbcType=TIMESTAMP},
            </if>
            version = version+1
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>

[anymoreT]

上面给的就是mybatis的一个数据库操作写法

[martianzhang]

类似MyBatis的框架还有不少，可以利用这种思路解决。

使用MyBatis框架开发应用程序来读取配置文件中的SQL，然后调用soar命令行工具评审，这样配置文件解析的兼容性会比较好。

https://stackoverflow.com/questions/33197085/mybatis-3-get-sql-string-from-mapper

[hhyo]

@anymoreT https://github.com/hhyo/archery/issues/3 在线环境可以试试，不过现在审核效率是一个问题，结果展示也没有特别处理

@martianzhang 不知道-report-type=json是否可以和markdown一样加入分值和pretty的信息