CODEWITHSUNDEEP
c#mysqlibatisdynamicquery
Gopi
Gopi

Reputation: 5887

Dynamic Query in Ibatis

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?

Upvotes: 0

Views: 3295

Answers (3)

Killnine
Killnine

Reputation: 5890

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>

Upvotes: 0

leonbloy
leonbloy

Reputation: 75926

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.

Upvotes: 1

chedine
chedine

Reputation: 2374

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.

Upvotes: 0

Related Questions