How to pass dynamic fields and values using mybatis 3

Question

I am new to Java and mybatis3. On a project I am using mybatis3..

say I have database table named "t". There are several columns.

In the project I will send a hashmap (contains 2 ArrayList of key, value) to mapper.xml. From there it will get 2 array contain keys of columns names, and values of columns...

I want to inset into that table... by that, I think I will able to dynamically insert data and partially update some column data... with update... but getting sql syntax error...

My existing code of mapper.xml

<insert id="createNews" parameterType="map" useGeneratedKeys="true" keyColumn="id">
  INSERT INTO t
    <foreach item="key" collection="Key" index="index" open="(" separator="," close=")">
        #{key}
    </foreach>
    VALUES
    <foreach item="value" collection="Value" index="index" open="(" separator="," close=")">
        #{value}
    </foreach>
  ;
</insert>

partial error stackTrace....

### Error updating database.  Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''name'
     ) 
    VALUES
     (  
        'some value'
    ' at line 3

But when I hard code the column name its working correctly... How can I insert dynamically...

Note: I googled, but unable to find... I don't want to use any pojo or annotation... thanks in advance...

Can you post the code(structure and the content) of your HashMap? You can also set your MyBatis logging level to show the query that is generated. mybatis.github.io/mybatis-3/logging.html — Yigitalp Ertem
– Yigitalp Ertem, Commented Sep 17, 2014 at 7:14

Yigitalp Ertem · Accepted Answer · 2014-09-17 07:20:21Z

7

Not sure, but I'll take a shot. When you use #{key}, MyBatis puts extra '' around it if it is a String, Date etc. If you give your column names with a variable you need to use direct String replacement which is ${key}.

The error log says something like ...right syntax to use near ''name') VALUES ('some value'...

Can you try

<insert id="createNews" parameterType="map" useGeneratedKeys="true" keyColumn="id">
  INSERT INTO t
    <foreach item="key" collection="Key" index="index" open="(" separator="," close=")">
        ${key}
    </foreach>
    VALUES
    <foreach item="value" collection="Value" index="index" open="(" separator="," close=")">
        #{value}
    </foreach>
</insert>

answered Sep 17, 2014 at 7:20

Yigitalp Ertem

2,06926 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shakil Ahmed Over a year ago

Many many thanks... it's working... I tried lot of things... you saved me... thanks once again...How can I missed the "String Substitution" section from the documentation...my bad..

Collectives™ on Stack Overflow

How to pass dynamic fields and values using mybatis 3

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related