选择具有动态列名称，目标表以及i-batis中的动态where条件的查询 [英] Select query with dynamic column name, target table as well as dynamic where condition in i-batis

问题描述

我想使用Select查询从表2中检索记录，该查询具有动态返回的列名，表名以及表1返回的动态where条件.我正在使用sql server.这是以下情况-

I want to retrieve records from table 2 using Select query with dynamically returned column name, table name as well as dynamic where condition returned by table 1. I'm using sql server. Here is the following scenario -

<typeAlias alias="paramBean" type="com.loyalty.sys.bulkemail.dao.ParamBean" />

<resultMap id="paramBeanMap" class="paramBean">
        <result property="columnName" column="COLUMN_NAME" />
        <result property="tableName" column="TABLE_NAME" />
        <result property="uniqueKey" column="UNIQUE_KEY" />                     
</resultMap>

<select id="getKeywordData" parameterClass="String" resultMap="paramBeanMap">

    SELECT COLUMN_NAME, TABLE_NAME, UNIQUE_KEY from LT_TB_SMS_EMAIL_MESSAGE_KEYWORD where KEYWORD
    in #keywordName#

</select>

<select id="selectKeywordValue" parameterClass="paramBean" resultClass="String">

    SELECT #columnName# from #tableName# where #uniqueKey# in #memberNo#

</select>

我能够从ParamBean属性中的第一个查询中获取值，但是它在第二个查询中的列名上引发了错误.我试图搜索类似的查询，但不幸的是找不到任何地方.在i-batis中是否可能，或者我应该采用另一种方法来检索具有动态输入的记录?任何帮助将不胜感激.谢谢！！

I'm able to get values from first query in ParamBean properties but it is throwing error at column name in second query. I tried to search similar query but unfortunately couldn't found any where. Is it possible in i-batis or i should take another approach to retrieve records with dynamic inputs ? Any help will be appreciated. Thanks.!

推荐答案

您需要使用直接字符串替换，$param$用于列，表等名称，这些名称不是查询的参数.这将直接将您的String放入查询中.在这种情况下，请注意SQL注入.

You need to use direct string replacement, $param$ for column, table etc. names that are not parameters to your query. This will directly put your String in the query. Be careful about SQL injection in this case.

<select id="selectKeywordValue" parameterClass="paramBean" resultClass="String">

    SELECT $columnName$ from $tableName$ where $uniqueKey$ in $memberNo$

</select>

请注意，在这种情况下，您的memberNo必须是类似于"(val1, val2, val3)"的字符串.

Note that in this case, your memberNo must be string that looks like "(val1, val2, val3)".

请参见 3.9.5.文档中的简单动态SQL元素.

这篇关于选择具有动态列名称，目标表以及i-batis中的动态where条件的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！