Setting the FROM clause via parameter in MyBatis

Question

I haven't been able to see anything in the documentation which speaks to my question, and upon deploying it, my app does not quite work right (more on that in a sec). I am trying to do something like

<select id="getLookupRows" parameterType="map" resultMap="lookupMap">
   select id, name, active, valid
   from #{table}
</select>

in MyBatis. I have a number of lookup tables that have shared columns and so the user at the view level determines which lookup table is ultimately used. The error I get when I try to execute getLookupRows is

Cause: org.apache.ibatis.executor.ExecutorException: There was no TypeHandler found for parameter table of statement info.pureshasta.mapper.LookupMapper.getLookupRows
org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:77)
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:69)
org.apache.ibatis.binding.MapperMethod.executeForList(MapperMethod.java:85)
org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:65)
org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38)
$Proxy15.getLookupRows(Unknown Source)
info.pureshasta.service.FieldTitleService.getLookupRows(FieldTitleService.java:33)

My mapper interface is as follows:

List<Lookup> getLookupRows(@Param("specificColumn") String specificColumn, 
                           @Param("table") String table);

so we know that I am trying to pass a String to this query, nothing special. I have the specific column, because that will be my next task. Really one of the columns of each of the lookup tables is unique, and so I have to call the appropriate specificColumn, but I would be really happy if I could the table parameter and the FROM clause working.

demongolem · Accepted Answer · 2012-01-05T21:39:04.023

<select id="getLookupRows" parameterType="map" resultMap="lookupMap">
   select id, name, active, valid
   from ${table}
</select>

does the trick. There is a different notation from actually injecting in a value for the column name and table then say the column value. If you are injecting a value in a where clause, then the # notation is the correct to use.

If the value used for table in this query is not escaped then SQL injection problems can occur. For my use case, the DB preceded me and while I can do whatever I want to the Java and View portions, I am not allowed to alter the fundamental structures of the tables.

If anyone wants to further explain the stack trace I got (i.e. what type myBatis thought table was) I would love to read and be further educated.

I would like to just add 1 more clarification. I mentioned specificColumn which is a dynamic column name depending upon the table. The query would be better written as `select ${specificColumn} as sc, id, name, active, valid` and so on. This allows us to reference the specificColumn in a result map like `` Without this alias, it appears that you cannot refer to the specificColumn in the resultMap. — demongolem, Jun 15 '11 at 13:37

Setting the FROM clause via parameter in MyBatis

1 Answers1