can Mybatis support mapping the dynamic columns to an map field of a bean?

Question

I have a requirement which may be little complicated. Think about I have bean class like:

public class A {
   private String column1;
   private String column2;
   private Map<String,String> dynamicColumns = Maps.newHashMap();
   ....
   getter&setter of column1 and column2
   ....
   public void addExtraColumnValue(String column, String value) {
     dynamicColumns.put(column, value);
   }

}

The dynamicColumns is determined by the mapper sql like this:

<select id="queryDynamicColumns" parameterType="java.util.Map"
    resultMap="aResultMap">
         select colum1,colum2, ${dynamicColumns} from table_1
</select>

How do I write this resultMap?

I wonder do the mybatis can support this mapping in mapper configuration?( the dynamic columns value will put into the map. ) I do not get a good answer from the official website, and also I try the plugin which intercept the resultSetHandler and it seems it do not give a help for my case by looking the source code of DefaultResultSetHandler.

Have you tried that? It seems like can work. Please try it and let me know. But what does the `resultMap` look like? I suggest return a map directly instead of a custom `resultMap`. — Horsing, May 10 '16 at 14:40
Hi Gemini, What do you mean tried that? The sql is work fine, I just do not know how to map my query result to the bean. Directly return map is or while it 's not ideal since I will need to write some ugly code to capsule result to the bean. — truejasonxiefans, May 11 '16 at 01:31
I mean get the result and put it into `java.util.Map` should be fine. But how can you dynamic set the property in `resultMap` as what you do in query statement? Could we just use map as `resultMap` although there will be some ugly code? — Horsing, May 11 '16 at 01:33
Hi Gemini. Directly return a `java.util.Map` works fine. While I just wonder is there any feasible way to get rid of those ugly code. — truejasonxiefans, May 11 '16 at 01:41

score 1 · Accepted Answer · edited May 23 '17 at 12:32

1

Have you tried ResultHandler?

question MyBatis - ResultHandler is not invoked could be useful.

In mybatis 3, you could see method SqlSession#select(String statement, ResultHandler handler).

You could do what ever you want in your custom ResultHandler.

Please let me know, if any problem.

It comes to my mind, if you derived from DefaultResultSetHandler and look at this:

public List<Object> handleResultSets(Statement stmt) throws SQLException {
  final List<Object> multipleResults = new ArrayList<Object>();

  int resultSetCount = 0;
  ResultSetWrapper rsw = getFirstResultSet(stmt);

  List<ResultMap> resultMaps = mappedStatement.getResultMaps();
  int resultMapCount = resultMaps.size();
  validateResultMapsCount(rsw, resultMapCount);
  while (rsw != null && resultMapCount > resultSetCount) {
    ResultMap resultMap = resultMaps.get(resultSetCount);
    handleResultSet(rsw, resultMap, multipleResults, null);
    rsw = getNextResultSet(stmt);
    cleanUpAfterHandlingResultSet();
    resultSetCount++;
  }

  while (rsw != null && resultSetCount < mappedStatement.getResulSets().length) {
    ResultMapping parentMapping = nextResultMaps.get(mappedStatement.getResulSets()[  resultSetCount]);
    if (parentMapping != null) {
      String nestedResultMapId = parentMapping.getNestedResultMapId();
      ResultMap resultMap = configuration.getResultMap(nestedResultMapId);
      handleResultSet(rsw, resultMap, null, parentMapping);
    }
    rsw = getNextResultSet(stmt);
    cleanUpAfterHandlingResultSet();
    resultSetCount++;
  }

  return collapseSingleResultList(multipleResults);
}

You can update the resultMaps runtime information, such as update its ResultMapping#column and then do dynamic binding. That's should work!

edited May 23 '17 at 12:32

Community

1
1

answered May 11 '16 at 02:09

Horsing

1,070
7
22

I am afraid the **ResultHandler** cannot give a help for this case. You can watch the code of **ResultHanlder**. We can get **ResultContext** which store a bean (a java type you claimed in mapper, the value has been filled in.), while we cannot get the **java.sql.resultSet** to push the extra columns value to the map of the bean. – truejasonxiefans May 11 '16 at 08:49
Why not just fill your bean manually? You have all values already and you can do what ever you want. Forget the result map parameter, you do not need it in this case, right? – Horsing May 11 '16 at 09:03
Share we just fill the bean we want and return it in `ResultContext#getResultObject()`? – Horsing May 11 '16 at 09:04
If you specified the `resultMap` as `java.util.Map`, so `DefaultResultSetHandler#getRowValue()` should return a map, and you can do rest job. – Horsing May 11 '16 at 09:11
While it seems this way is no difference with get the map result directly and do the rest of job. – truejasonxiefans May 11 '16 at 09:19
Yeah, you are right. But it could be a long way for a best solution. However, the dynamic column self sounds a little wired. `DefaultResultSetHandler#handleResultSet()` should be useful. It looks like only two method to do this, `DefaultResultHandler` or custom `ResultHandler`. – Horsing May 11 '16 at 09:26
@truejasonxiefans Please see my answer, I've updated it. And I think it's what you want. All you need is to dynamic update the `resultMaps` `column`. – Horsing May 11 '16 at 09:39
Hi Gemini, I known what do you mean(I just try before and didn't have a good solution like an inteceptor or override the this hanlder are not easy to do). While you will find it hardly ever to change the resultMap information in runtime.Mybatis seems do not give a interface to let you change resultMap in runtime. – truejasonxiefans May 11 '16 at 11:49
Ok, I'll try it later! Any achievement please let me know, thanks! – Horsing May 11 '16 at 11:54

Prasanna Kumar H A · Answer 2 · 2016-05-11T09:07:19.757

1

Yes it can be done.

1.Whatever you are doing to select dynamic columns is ok.

2.To map the result: Since you are selecting the columns(dynamic) from table_1(Which will not change),you can have a POJO class with the fields present in table_1 and put that in resultMap.

<select id="queryDynamicColumns" parameterType="java.util.Map"
    resultMap="**Pojo Class With Fields As Columns of table_1**">
         select colum1,colum2, ${dynamicColumns} from table_1
</select>

Update:

If your database table columns changes than try using JAVA REFLECTION to dynamic columns

edited May 11 '16 at 09:07

answered May 11 '16 at 06:55

Prasanna Kumar H A

3,341
6
24
52

Hi Sam, the dynamic columns in bean(POJO) class is stored as a Map which you can refer to the question definition. So how to write the resultMap to mapping to a map field? – truejasonxiefans May 11 '16 at 08:43
Try it by reflection...refer to this(http://stackoverflow.com/questions/5178391/create-simple-pojo-classes-bytecode-at-runtime-dynamically) may be useful – Prasanna Kumar H A May 11 '16 at 08:48
But why you require like that?? is table_1's columns will change in DB?? – Prasanna Kumar H A May 11 '16 at 08:48
Than use reflection to generate getters and setters for dynamic columns...http://stackoverflow.com/questions/4560746/how-to-define-dynamic-setter-and-getter-using-reflection – Prasanna Kumar H A May 11 '16 at 08:55
Dynamic pojo may be feasible way. If I have a dynamic pojo, I can use the auto-mapping to write the mapper.xml. – truejasonxiefans May 11 '16 at 09:04

score 0 · Answer 3 · answered Jun 18 '20 at 20:48

Some dirty(?) tricks can solve your problems, but you have to start from the SQL side. Tactic: Select all dynamic columns as one column using a delimiter to join columns. Then split the columns into an array using the same delimiter.

SELECT MANDATORY_COL_1 AS C1, MANDATORY_COL_2 AS C2, OPTIONAL_COL_3 || "^" OPTIONAL_COL_4 || "^" OPTIONAL_COL_5 ... AS DYN_COL FROM TABLE_NAME WHERE ...

Now it's a simple matter to split the 3rd column from String to Array: DYN_COL.Split("^")

Few things more: 1) You can easily convert the dynamic columns selection to a dynamic string, giving you near full dynamism. Mybatis supports # for named parameters and $ for dynamic parameters. 2) If the dynamic columns may have different data types, then you would need to select the columns' data type as well - which would become your 4th and final column in above SQL 3) Columns data type is available in RDBMSs in the system catalog - for example, in MySQL it is INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS

can Mybatis support mapping the dynamic columns to an map field of a bean?

3 Answers3