Dynamic Query in Ibatis

Is it possible to pass dynamic query to Ibatis and get the record from it?

E.g. I built my qu开发者_JS百科ery using StringBuilder and at last, I got the following query "select emp_id, emp_name from employee where emp_id==1" . Now i need to pass this complete query to Ibatis and get the record.

Note: Here the number of columns and where conditions will vary on each query formation

EDIT: How to pass the query to Ibatis and get it executed using ibatis?

I don't think you can, and even if you could, you shouldn't do that. To "build your query using StringBuilder" defeats iBatis purpose, and is prone to lots of problems (SQL injection among them) which iBatis is precisely designed to prevent.

Do yourself a favour: read about dynamic queries in iBatis and take out your SQL from Java to XML (if you really want to use iBatis).

If you really insist... well, I guess you can pass the whole sql query as a single string to iBatis, for example invoking a stored procedure that executes dynamically that sql code. Horrid but conceivable.

MyBatis comes with SelectBuilder and SQLBuilder. You can use this SelectBuilder to construct the dynamic query. More information about SelectBuilder can found in the user guide.

Old issue but I wanted to chime in. I agree with @leonbloy, ibatis provides features to avoid what you are trying to do. The ibatis link for dynamic queries should help you figure it out.

Here is a simple example I've used:

Have a method to pass in your arguments as a dictionary

public IList<ITraceLogRecord> GetTraceLogRecords(string systemType, string plantName, int? deviceId, DateTime startTime, DateTime endTime, string logDescription, string loggerName, List<int> traceLevelIds)
    {
        IDictionary<string, object> traceQueryParameters = new Dictionary<string, object>();
        traceQueryParameters.Add("deviceId", deviceId);
        traceQueryParameters.Add("startTime", startTime);
        traceQueryParameters.Add("endTime", endTime);
        traceQueryParameters.Add("logDescription", logDescription);
        traceQueryParameters.Add("loggerName", loggerName);
        traceQueryParameters.Add("traceLevelIds", traceLevelIds);

        return DataSources.GetDbConnectionName(systemType, plantName).QueryForList<ITraceLogRecord>("SelectTraceLogRecords", traceQueryParameters);
    }

Create your select statement and check if the inputs are null for whether to include them in your where clause:

<select id="SelectTraceLogRecords" parameterClass="System.Collections.IDictionary" resultMap="TraceLogRecordMap">
  SELECT TraceLevelId, Trace, DeviceId, LoggerName, CreatedTimeStamp, ThreadId
  FROM Trace
  <dynamic prepend="WHERE">
    <isNotNull prepend="AND" property="deviceId">
      DeviceId = #deviceId#
    </isNotNull>
    <isNotNull prepend="AND" property="startTime">
      CreatedTimeStamp >= #startTime#
    </isNotNull>
    <isNotNull prepend="AND" property="endTime">
      <![CDATA[CreatedTimeStamp <= #endTime#]]>       
    </isNotNull>
    <isNotNull prepend="AND" property="logDescription">
      Trace LIKE #logDescription#
    </isNotNull>
    <isNotNull prepend="AND" property="loggerName">
      LoggerName LIKE #loggerName#
    </isNotNull>
    <isNotNull prepend="AND" property="traceLevelIds">
      <iterate property="traceLevelIds" open="(" close=")" conjunction="OR">
        TraceLevelId = #traceLevelIds[]#
      </iterate>
    </isNotNull>
  </dynamic>
</select>