29

I am confused about using $ vs #. I didn't found any guides for this. I used them as
name = #{name}, name like '%${word}%', order by name ${orderAs},where name = #{word}
Sometimes , these are work fine but at the sometimes , parameters aren't included or gave me error like

org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'name'.......

So, I'd like to know when to use $ or # ?

Cataclysm
  • 7,592
  • 21
  • 74
  • 123

3 Answers3

38

Following the myBatis guidelines #{} is used in your sql statements.

If you take a look any of MyBatis Reference in the Section Mapper XML Files it says explicity:

Notice the parameter notation:

#{id}

Otherwise ${} is for

1- Configuration properties.

For example:

<properties resource="org/mybatis/example/config.properties">
  <property name="username" value="dev_user"/>
  <property name="password" value="F2Fa3!33TYyg"/>
</properties>

Then the properties can be used like next:

<dataSource type="POOLED">
  <property name="username" value="${username}"/>
  <property name="password" value="${password}"/>
</dataSource>

2- String Substitution ${} (Parameters section):

By default, using the #{} syntax will cause MyBatis to generate PreparedStatement properties and set the values safely against the PreparedStatement parameters (e.g. ?). While this is safer, faster and almost always preferred, sometimes you just want to directly inject a string unmodified into the SQL Statement. For example, for ORDER BY, you might use something like this:

ORDER BY ${columnName}

Here MyBatis won't modify or escape the string.

NOTE It's not safe to accept input from a user and supply it to a statement unmodified in this way. This leads to potential SQL Injection attacks and therefore you should either disallow user input in these fields, or always perform your own escapes and checks.

So definitively in name like '%${word}%' ororder by name ${orderAs}` you need to use String substitution not a prepared statement.

Pau
  • 14,917
  • 14
  • 67
  • 94
  • `name = ${name}` , `name like '%#{word}%'` these are not ok. *#{} is used in your sql statements....* I did not understand.I am using mybatis exp within sql statements. Isn't ? *${} is just for Java properties..* Do you mean these may be varried depends on parameter type ? – Cataclysm Oct 10 '16 at 09:08
  • can u support me a link to follow guidelines as you described ? – Cataclysm Oct 10 '16 at 09:09
  • Thank your for your edited answer.But I'm not satisfied yet. Can you explain me about why `order by name #{orderAs}` and `name like '%#{word}%'` give me error ? – Cataclysm Oct 10 '16 at 10:44
  • I've edited again the question. You were right, there are some cases where isn't a PreparedStatemnt, just a String substitution and in this cases is need to do it with `${}`. One example is the `order by` – Pau Oct 10 '16 at 10:56
  • perfect .. Thanks – Cataclysm Oct 10 '16 at 10:59
  • 3
    Per the point "2- String Substitution ${}" in this Answer: An operative part of the description there for me is, "Here MyBatis won't modify or escape the string". So in cases where #{} doesn't work, I infer it must be because of that "modification" or "escaping", where ${} doesn't do that. I'm not sure what are examples of "modifications", but I think one may be that Strings referenced with #{} might have quote chars added around them (because the # in this works: "... name IN #{item} ...") – cellepo Feb 22 '17 at 03:11
  • I want to use $ variable in table name but first make it lower case. I tried `my_table_name_LOWER(${country})` and it doesn't work because the table name is formed as `my_table_name_LOWER(IN)`. How do I make it work? – Hussain Nov 29 '18 at 04:16
13

This (${} - simple variable)

SELECT * from user where usernum = ${usernum}

translates into this

SELECT * from user where usernum = 666

, but this (#{} - equivalent to PreparedStatement in JDBC)

SELECT * from user where usernum = #{usernum}

translates into

SELECT * from user where usernum = ?

, so the best usage would be something like

SELECT * from ${tablename} where name = #{name}
Ondřej
  • 151
  • 1
  • 5
0

I was also confused with this . Then I did some research. I had a query which is something like select * from tablename h where h.id=#userid# in ibatis. Then I had to migrate it into mybatis 3 . The same statement didnt work. So I had changed it into select * from tablename h where h.id=#{userid}

Ruli
  • 2,592
  • 12
  • 30
  • 40
rabi sahu
  • 1
  • 2
  • This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/30321442) – st.huber Nov 15 '21 at 15:22