Dynamic Query in Ibatis

Question

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

E.g. I built my query 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?

score 1 · Answer 1 · answered Jun 29 '10 at 14:49

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.

May I know how to pass string to Ibatis and handle its result set. I tried with above statement but not sure how to pass the querystring and handle the result set — Gopi, Jul 02 '10 at 11:22

score 0 · Answer 2 · answered Jun 29 '10 at 08:21

0

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.

answered Jun 29 '10 at 08:21

chedine

2,384
3
19
24

score 0 · Answer 3 · answered Mar 19 '12 at 16:35

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>

Dynamic Query in Ibatis

3 Answers3