java - 是否可以返回数据集而不是 MyBatis 中的任何映射模型类？

Question

我有一个场景，我想进行 myBatis 应该支持的动态查询，如下所示：

    <select id=“someRecords” resultMap=“someRecordMap”&gt;

        DROP TABLE IF EXISTS TEMP_TABLE;
        CREATE TEMPORARY TABLE TEMP_TABLE(some_stub UUID);

        INSERT INTO TEMP_TABLE (some_stub)
        select regexp_split_to_table(#{someIds},',')::uuid;

        SELECT wil.some_identifier_stub as identifier_stub
        ,wil.x
        ,wil.y
        ,wil.z
        ,wil.u
        ,wil.o
        ,msg.p
        FROM TABLE_A msg
        INNER JOIN TABLE_B wil ON msg.a_id = wil.b_id
        INNER JOIN TABLE_C est ON est.c_stub = wil.b_stub
        WHERE wil.unique_id = #{uniqueId} AND wil.b_type_id = #{b_TypeId}
        <if test="environment != null">
            <include refid="environmentCondition"></include>
        </if>
    </select>

    <sql id="environmentCondition">
        AND environment = #{environment}
    </sql>

但不是 someRecordMap，我想返回 DataSet 以便它与我现有的代码向后兼容

因此，我没有起诉 myBatis XMl 方法，而是使用反射和注释进行自定义方法，如下所示：

部分：基于 IGNORE_SOME_JOIN、IGNORE_SOME_STUB、IGNORE_SOME_EXT_FLAG 等条件的动态查询

    @SqlQueries({@SqlQuery(name = "query1",
                query = "select a,b," +
                        "c,d,e,f,g,wil.h," +
                        " j,h,i " +
                        START_DELIMITER + " " + IGNORE_SOME_JOIN + " " +
                        " ,some_message" + END_DELIMITER +
                        " FROM  A_TABLE wil " +
                        START_DELIMITER + " " + IGNORE_SOME_JOIN + " " +
                        "LEFT OUTER JOIN B_TABLE wim on" +
                        " wil.unique_id = wim.unique_id" +
                        " and wim.created_date >= ?  and wim.created_date <= ?  " + END_DELIMITER +
                        " WHERE ( wil.created_date >= ? AND wil.created_date <= ? AND wil.primary_id = ? " +
                        START_DELIMITER + " " + IGNORE_SOME_STUB + " " +
                        " AND wil.unique_identifier_stub = ?::uuid " + END_DELIMITER +
                        START_DELIMITER + " " + IGNORE_SOME_EXT_FLAG +
                        " AND wil.some_ext_success_flag = ANY(?) " + END_DELIMITER + ")" +
                        "ORDER BY wil.created_date OFFSET ? LIMIT ? ")}
        )

动态查询的解析逻辑如下：

    abstract class ReportingQuery {
        private static final Logger LOG = LoggerFactory.getLogger(ReportingQuery.class);

        static final String START_DELIMITER = "#~";
        static final String END_DELIMITER = "#~";
        static final String REPLACEABLE_DELIMITER = "--";

        /**
         * Responsible to prepare final query after applying dynamic query criteria
         *
         * @param className     : ReportingQuery class reference
         * @param methodName    : Query method name
         * @param queryName     : Dynamic query
         * @param ignoreStrings : Criteria to be applied in dynamic query
         * @return : final static query after applying dynamic query criteria(ignoreStrings)
         */
        static Optional<String> getQuery(Class className, String methodName, String queryName, List<String> ignoreStrings) {
            StringBuilder builder = new StringBuilder();
            try {
                Method[] methods = className.getDeclaredMethods();
                Optional<String> queryString = Optional.empty();
                if (Arrays.stream(methods).anyMatch(x -> x.getName().equals(methodName))) {
                    QueryExample.SqlQuery[] sqlQueries = Arrays.stream(methods)
                            .filter(x -> x.getName().equals(methodName))
                            .findFirst().get().getAnnotation(ReportingQuery.SqlQueries.class).value();
                    if (Arrays.stream(sqlQueries).anyMatch(x -> x.name().equals(queryName))) {
                        queryString = Optional.of(Arrays.stream(sqlQueries).filter(x -> x.name()
                                .equals(queryName)).findFirst().get().query());
                    }
                }

                String[] token = new String[0];
                if (queryString.isPresent()) {
                    token = queryString.get().split(START_DELIMITER);
                }


               ...... SOME logic to make query based on some dynamic condition

            return Optional.of(builder.toString());
        }

        /**
         *
         */
        @Retention(RetentionPolicy.RUNTIME)
        @Target({ElementType.METHOD})
        @interface SqlQuery {
            String name();

            String query();
        }

        /**
         *
         */
        @Retention(RetentionPolicy.RUNTIME)
        @Target({ElementType.METHOD})
        @interface SqlQueries {
            SqlQuery[] value();
        }
    }

所以如果我有条件 IGNORE_SOME_JOIN 那么我的最终逻辑查询就像

    select a,b,c,d,e,f,g,wil.h,j,h,i FROM  A_TABLE wil WHERE ( wil.created_date >= '2018-08-29T15:15:42.42'
            AND wil.created_date <= '2018-08-30T15:15:42.42' AND wil.acct_id = 2000017
             AND wil.unique_identifier_stub = 'a004f322-1003-40a7-a54b-f3b979744fd2'
             AND wil.some_ext_success_flag  = ANY('{"0","1"}')) ORDER BY wil.created_date OFFSET 0 LIMIT 500;

所以在上面我得到了字符串查询，我将在下面运行代码并得到结果集：

     PreparedStatement ps = con.prepareStatement(query)) {
                    prepareStatement(prepareStatementAndQueryList, ps, con);
                    try (ResultSet rs = ps.executeQuery()) {
                    DO SOMETHING WITH RESULT SET NOW
    }
    }

但是我想用 MyBatis 来做这件事，而不是我自己的自定义解决方案，它不容易出错并且可能没有性能效率，因为我在这里使用了很多反射。

标签： javareflectionmybatis

Answer 1

是的，这是可以的，并且是mybatis直接支持的。这是文档中的一个示例：

@SelectProvider(type = UserSqlBuilder.class, method = "buildGetUsersByName")
List<User> getUsersByName(String name);

class UserSqlBuilder {
  public static String buildGetUsersByName(final String name) {
    return new SQL(){{
      SELECT("*");
      FROM("users");
      if (name != null) {
        WHERE("name like #{value} || '%'");
      }
      ORDER_BY("id");
    }}.toString();
  }
}

在上面的示例getUsersByName中是一个映射器方法，UserSqlBuilder它被映射器用来根据映射器参数动态生成 SQL 查询文本。这正是您所需要的，并且与所做的非常相似ReportingQuery。

您需要调整您的代码，以便您现有的查询生成器与SelectProviderAPI 确认，但这似乎相当简单。