MyBatis string as parameter

Question

I want to use the String parameter for Select Statement in MyBatis. My mapper.xml:

<select id="selectAll" parameterType="String" resultMap="fastXMLResultMap"> SELECT CREATIONDATE, DOCUMENTID, TITEL, REGTITEL, INFORCEDATE, DOCTYPE FROM #{databBaseTable} </select> 

And the calling function:

 public List<FastXMLObject> selectAll(String databBaseTable) { SqlSession session = sqlSessionFactory.openSession(); System.out.println("Table: "+databBaseTable); try { List<FastXMLObject> list = session.selectList("FastXMLObject.selectAll",databBaseTable); return list; } finally { session.close(); } } 

The dataBaseTable line is the name of the table in my database (who would have thought) because I want to dynamically retrieve data from verified tables.

But, unfortunately, this will not work: Error: ORA-00903: Ungültiger Tabellenname (invalid table name), but it is not. When I print out the value of "databBaseTable", this is the exact name of the table. And when I write the name of the table in my mapper.xml file without a variable, it works. What am I doing wrong?

Answer 1

Use ${dataBaseTable} instead of '#'. The difference is that '#' is used to substitute the PreparedStatement. '$' - for direct substitution of String.

However, if you do this, you cannot pass the table name as a parameter to the selectList() call. You must set the table name as a property. Properties can be set using the <properties> element in MyBatis config.xml or directly in code using Configuration.getVariables() .

See the “String Substitution” section in MyBatis Docs .

Answer 2

Well, I definitely don't know why this works, but I just used the following to solve the problem:

 <select id="selectAll" parameterType="String" resultMap="fastXMLResultMap"> SELECT CREATIONDATE, DOCUMENTID, TITEL, REGTITEL, INFORCEDATE, DOCTYPE FROM ${value} </select> 

I did not set any properties or anything else, it was just a change from FROM #{databBaseTable} to FROM ${value}

I could answer why this works. But for now, it really helped me.

Answer 3

With the syntax # {..}, MyBatis uses the PreparedStatement jdbc object, on which you cannot specify the table name as a parameter. Using # {..} you can parameterize the parameters of the sql statement.

When you use the $ {..} syntax, MyBatis does the usual old string expansion, so you can parameterize almost any part of sql that you want.

Note: how, by the way, with C # {..} you are quite safe from SQL injection, but with $ {..} it opens the door for such attacks