Hello friends,
Normally we run an ibatis query smoothly when there are no special characters involved.
But in some cases, we have special characters in the query :
select col1, col2 from table1 as t1 inner join table2 as t2 on t1.xcol# = t2.ycol# and t1.col3=t2.col4 where col4=1
Here, my column name itself contain #. So I need to tell ibatis that my column name contains # and please consider it within the column name while running query.
So when there are special characters in the query (column name, variable, etc) then you might run into some of the below errors :
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 't1.xcol' in 'on clause'
...
...
Cause: java.lang.RuntimeException: Error parsing XPath '/sqlMapConfig/sqlMap'. Cause: com.ibatis.common.xml.NodeletException: Error parsing XML. Cause: java.lang.RuntimeException: Error parsing XPath '/sqlMap/select'. Cause: com.ibatis.sqlmap.client.SqlMapException: Incorrect inline parameter map format (missmatched name=value pairs): `=t2.`ycol
...
...
Cause: java.lang.ArrayIndexOutOfBoundsException: 1
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:204)
...
...
Th following query ran successfully in mysql query browser but not when running through ibatis.
select col1, col2 from table1 as t1 inner join table2 as t2 on t1.`xcol#` = t2.`ycol#` and t1.col3=t2.col4 where col4=1
Solution (for running with ibatis):
Escape has (#) by double hash (##) and embed the column name containing # within grave accent/backtick symbol.
I faced a similar issue and modified the query as :
select col1, col2 from table1 as t1 inner join table2 as t2 on
t1.`xcol##` = t2.`ycol##` and t1.col3=t2.col4 where col4=1
It worked successfully.
=================================================================
You may also get the following exception :
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0)
Solution :
Check column name in sql query. It may contain # which u need to escape
=================================================================
If webscript gets called recursively (i.e query gets executed recursively through services), then:
The transaction may be getting retried i.e RetryingTransactionHelper due to some exception caused.
Check the logs or remove the already handled exceptions in catch block.
=================================================================
Using LIKE query in iBatis : Here my column name is ITEM# so I am required to escape it using ` and # while using mysql.
select `ITEM##` from TABLE where COL1=#val1# and COL2 like #val2# and `ITEM##` like '$ItemNo$%'
With DB2, the following may also work : (This character ` should not be required for escaping) :
select ITEM## from TABLE where COL1=#val1# and COL2 like #val2# and ITEM## like '$ItemNo$%'
=================================================================
Dynamic AND/OR statements in ibatis :
<select id="getAllItems" resultMap="result" parameterClass="map">
select `ITEM##` from TABLE where COL1=#val1# and COL2=0 and `ITEM##` like '$ItemNo$%'
<iterate prepend="AND" property="propname"
open="(" close=")" conjunction="OR">
COL3=#propname[]#
</iterate>
</select>
