Can you execute Dynamic SQL via Java API's?

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Can you execute Dynamic SQL via Java API's?

Sumeet Singh
I am trying to build a module which can take string input (sql query) and fire it to get the ResultContext object. My problem is I am able to run it if the string passed to it doesn''t use Dynamic SQL. 

Code that works :  It is able to put in the SQL in the mapper function and replace variables in it from the parameterMap.

Mapper XML
<select id="executeReport" resultType="java.util.Map">
${sql}
</select>

Java Code
SqlSession sqlSession = sqlSessionFactory.openSession();
ReportResultHandler reportResultHandler = new ReportResultHandler();
Map<String,Object> parameterMap = new HashMap<>();
parameterMap.put("sql","select name from Client where name = #{productName}");
parameterMap.put("productName","Gluon");
parameterMap.put("p1",null);
sqlSession.select("executeReport",parameterMap,reportResultHandler);



Code that doesn't work because of Dynamic SQL in it : 

SqlSession sqlSession = sqlSessionFactory.openSession();
ReportResultHandler reportResultHandler = new ReportResultHandler();
Map<String,Object> parameterMap = new HashMap<>();
parameterMap.put("sql","select name from Client where name = #{productName} <if test=\"p1 != null\"> and enabled = 0 </if>");
parameterMap.put("productName","Gluon");
parameterMap.put("p1",null);
sqlSession.select("executeReport",parameterMap,reportResultHandler);


Any pointer on how to get Dynamic SQL to work is appreciated.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Can you execute Dynamic SQL via Java API's?

Iwao AVE!
Hi Sumeet,

See @SelectProvider which is designed to build statements dynamically.
There are a few examples in the doc.

Regards,
Iwao

2016-10-19 6:06 GMT+09:00 Sumeet Singh <[hidden email]>:
I am trying to build a module which can take string input (sql query) and fire it to get the ResultContext object. My problem is I am able to run it if the string passed to it doesn''t use Dynamic SQL. 

Code that works :  It is able to put in the SQL in the mapper function and replace variables in it from the parameterMap.

Mapper XML
<select id="executeReport" resultType="java.util.Map">
${sql}
</select>

Java Code
SqlSession sqlSession = sqlSessionFactory.openSession();
ReportResultHandler reportResultHandler = new ReportResultHandler();
Map<String,Object> parameterMap = new HashMap<>();
parameterMap.put("sql","select name from Client where name = #{productName}");
parameterMap.put("productName","Gluon");
parameterMap.put("p1",null);
sqlSession.select("executeReport",parameterMap,reportResultHandler);



Code that doesn't work because of Dynamic SQL in it : 

SqlSession sqlSession = sqlSessionFactory.openSession();
ReportResultHandler reportResultHandler = new ReportResultHandler();
Map<String,Object> parameterMap = new HashMap<>();
parameterMap.put("sql","select name from Client where name = #{productName} <if test=\"p1 != null\"> and enabled = 0 </if>");
parameterMap.put("productName","Gluon");
parameterMap.put("p1",null);
sqlSession.select("executeReport",parameterMap,reportResultHandler);


Any pointer on how to get Dynamic SQL to work is appreciated.


--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.