Adhoc query

[valos]

Hi,

I'm using iBatis 2 in a desktop application and I was wondering
whether it is possible to create an ahdoc SQL query from within
iBatis?

If not, is it possible to get a JDBC connection from ibatis to execute
an arbitrary query?

Thanks.

[Jeff Butler]

Yes...

<select id="adHoc" resultClass="map" parameterClass="string">
${value}
</select>

A bit dangerous (SQL injection), but it works.

Jeff Butler

[valos]

Will try that now.

Thanks a mill.

Vasile

[Jeff Butler]

I got the example a bit mixed up between V2 and V3. For iBATIS2, it's this:

<select id="adHoc" resultClass="map" parameterClass="string"

remapResults="true">
$value$
</select>

Jeff Butler

[Larry Meadors]

Don't forget the remapResults="true".

[valos]

I've tried that but it doesn't seem to work.

Here are the mappings:

<sqlMap namespace="AdHoc">
<select id="AdHoc" resultClass="map" parameterClass="string"
remapResults="true">
$value$
</select>
</sqlMap>

The interface:

public interface AdHocDao
{
Map execSql(String sql) throws DaoException;
}

Implementation:

public class AdHocDaoImpl extends BaseSqlMapDao implements AdHocDao
{
public AdHocDaoImpl(DaoManager daoManager)
{
super(daoManager);
}

public Map execSql(String sql) throws DaoException
{
return queryForMap("AdHoc", sql, "map");
}
}

And this is the call:

AdHocDao dao = DaoConfig.getAdHocDao();

Map map = dao.execSql("select id, nume,cnp from STUDENT");

I'm getting this error:

Exception occurred during event dispatching:
com.ibatis.dao.client.DaoException: Failed to queryForMap - id
[AdHoc], parameterObject [select id, nume,cnp from STUDENT], keyProp
[map]. Cause: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/burse/persistence/sqlmapdao/sql/
AdHoc.xml.
--- The error occurred while applying a result map.
--- Check the AdHoc-AutoResultMap.
--- Check the result mapping for the 'CNP' property.
--- Cause: java.lang.RuntimeException: JavaBeansDataExchange could not
instantiate result class. Cause: java.lang.InstantiationException:
java.util.Map
Caused by: java.lang.RuntimeException: JavaBeansDataExchange could not
instantiate result class. Cause: java.lang.InstantiationException:
java.util.Map
at
com.ibatis.dao.client.template.SqlMapDaoTemplate.queryForMap(SqlMapDaoTemplate.java:
455)
at
com.burse.persistence.sqlmapdao.impl.AdHocDaoImpl.execSql(AdHocDaoImpl.java:
21)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.ibatis.dao.engine.impl.DaoProxy.invoke(DaoProxy.java:
72)
at $Proxy13.execSql(Unknown Source)
at
com.burse.StudentiDlg.jButtonModificaActionPerformed(StudentiDlg.java:
223)
at com.burse.StudentiDlg.access$100(StudentiDlg.java:34)
at com.burse.StudentiDlg$3.actionPerformed(StudentiDlg.java:
164)
at
javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:
1995)
at javax.swing.AbstractButton
$Handler.actionPerformed(AbstractButton.java:2318)
at
javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:
387)
at
javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
at
javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:
236)
at java.awt.Component.processMouseEvent(Component.java:6038)
at javax.swing.JComponent.processMouseEvent(JComponent.java:
3265)
at java.awt.Component.processEvent(Component.java:5803)
at java.awt.Container.processEvent(Container.java:2058)
at java.awt.Component.dispatchEventImpl(Component.java:4410)
at java.awt.Container.dispatchEventImpl(Container.java:2116)
at java.awt.Component.dispatchEvent(Component.java:4240)
at
java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4322)
at
java.awt.LightweightDispatcher.processMouseEvent(Container.java:3986)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:
3916)
at java.awt.Container.dispatchEventImpl(Container.java:2102)
at java.awt.Window.dispatchEventImpl(Window.java:2429)
at java.awt.Component.dispatchEvent(Component.java:4240)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
at
java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:
273)
at
java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:
183)
at
java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:
177)
at java.awt.Dialog$1.run(Dialog.java:1039)
at java.awt.Dialog$3.run(Dialog.java:1091)
at java.security.AccessController.doPrivileged(Native Method)
at java.awt.Dialog.show(Dialog.java:1089)
at java.awt.Component.show(Component.java:1419)
at java.awt.Component.setVisible(Component.java:1372)
at java.awt.Window.setVisible(Window.java:801)
at java.awt.Dialog.setVisible(Dialog.java:979)
at com.burse.BurseView.actionStudenti(BurseView.java:248)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
25)
at java.lang.reflect.Method.invoke(Method.java:597)
at
org.jdesktop.application.ApplicationAction.noProxyActionPerformed(ApplicationAction.java:
662)
at
org.jdesktop.application.ApplicationAction.actionPerformed(ApplicationAction.java:
698)
at
javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:
1995)
at javax.swing.AbstractButton
$Handler.actionPerformed(AbstractButton.java:2318)
at
javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:
387)
at
javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
at
javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:
236)
at
java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:
272)
at java.awt.Component.processMouseEvent(Component.java:6038)
at javax.swing.JComponent.processMouseEvent(JComponent.java:
3265)
at java.awt.Component.processEvent(Component.java:5803)
at java.awt.Container.processEvent(Container.java:2058)
at java.awt.Component.dispatchEventImpl(Component.java:4410)
at java.awt.Container.dispatchEventImpl(Container.java:2116)
at java.awt.Component.dispatchEvent(Component.java:4240)
at
java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4322)
at
java.awt.LightweightDispatcher.processMouseEvent(Container.java:3986)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:
3916)
at java.awt.Container.dispatchEventImpl(Container.java:2102)
at java.awt.Window.dispatchEventImpl(Window.java:2429)
at java.awt.Component.dispatchEvent(Component.java:4240)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
at
java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:
273)
at
java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:
183)
at
java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:
173)
at
java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
at
java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:
121)
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/burse/persistence/sqlmapdao/sql/
AdHoc.xml.
--- The error occurred while applying a result map.
--- Check the AdHoc-AutoResultMap.
--- Check the result mapping for the 'CNP' property.
--- Cause: java.lang.RuntimeException: JavaBeansDataExchange could not
instantiate result class. Cause: java.lang.InstantiationException:
java.util.Map
Caused by: java.lang.RuntimeException: JavaBeansDataExchange could not
instantiate result class. Cause: java.lang.InstantiationException:
java.util.Map
at
com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:
204)
at
com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:
139)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:
567)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:
541)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForMap(SqlMapExecutorDelegate.java:
658)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForMap(SqlMapExecutorDelegate.java:
640)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForMap(SqlMapSessionImpl.java:
148)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForMap(SqlMapClientImpl.java:
124)
at
com.ibatis.dao.client.template.SqlMapDaoTemplate.queryForMap(SqlMapDaoTemplate.java:
453)
... 73 more
Caused by: java.lang.RuntimeException: JavaBeansDataExchange could not
instantiate result class. Cause: java.lang.InstantiationException:
java.util.Map
at
com.ibatis.sqlmap.engine.exchange.ComplexDataExchange.setData(ComplexDataExchange.java:
82)
at
com.ibatis.sqlmap.engine.mapping.result.ResultMap.setResultObjectValues(ResultMap.java:
371)
at
com.ibatis.sqlmap.engine.mapping.result.AutoResultMap.setResultObjectValues(AutoResultMap.java:
54)
at
com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback.handleResultObject(RowHandlerCallback.java:
64)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(SqlExecutor.java:
385)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.handleMultipleResults(SqlExecutor.java:
300)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:
189)
at
com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:
221)
at
com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:
189)
... 81 more
Caused by: java.lang.InstantiationException: java.util.Map
at java.lang.Class.newInstance0(Class.java:340)
at java.lang.Class.newInstance(Class.java:308)
at
com.ibatis.common.resources.Resources.instantiate(Resources.java:273)
at
com.ibatis.sqlmap.engine.mapping.result.ResultObjectFactoryUtil.createObjectInternally(ResultObjectFactoryUtil.java:
123)
at
com.ibatis.sqlmap.engine.mapping.result.ResultObjectFactoryUtil.createObjectThroughFactory(ResultObjectFactoryUtil.java:
91)
at
com.ibatis.sqlmap.engine.exchange.ComplexDataExchange.setData(ComplexDataExchange.java:
80)
... 89 more

[Nathan Maves]

your resultClass is not correct. "map" in a type alias to
java.util.Map which is an interface and can not be instantiated.

Use "java.util.HashMap" or any other implementation of java.util.Map.

nathan

[Nathan Maves]

Also you need to change

return queryForMap("AdHoc", sql, "map");

The third parameter should be a property of the bean(Map in your
case). So it should be one of "id, nume,cnp" from your query. I am
also just guessing that you might have mean "name" and not "nume" but
that is a complete guess.

nathan

[valos]

OK, I've made the following changes:

public Map execSql(String sql) throws DaoException
{

return queryForMap("AdHoc", sql, "id");
}

<sqlMap namespace="AdHoc">
<select id="AdHoc" resultClass="java.util.HashMap"

parameterClass="string" remapResults="true">
$value$
</select>
</sqlMap>

Now it doesn't throw any exception but returns just the last record.

"nume" field is correct, it is not misspelled.

Just wondering whether it is possible to return a list instead of a
map. If so how should I do it?

Many thanks for your support.

Vasile

On 4 iun., 19:47, Nathan Maves <nathan.ma...@gmail.com> wrote:
> Also you need to change
>
> return queryForMap("AdHoc", sql, "map");
>
> The third parameter should be a property of the bean(Map in your
> case).  So it should be one of "id, nume,cnp" from your query.  I am
> also just guessing that you might have mean "name" and not "nume" but
> that is a complete guess.
>
> nathan
>
>
>
> On Fri, Jun 4, 2010 at 10:44 AM, Nathan Maves <nathan.ma...@gmail.com> wrote:
> > your resultClass is not correct.  "map" in a type alias to
> > java.util.Map which is an interface and can not be instantiated.
>
> > Use "java.util.HashMap" or any other implementation of java.util.Map.
>
> > nathan
>

> >> org.jdesktop.application.ApplicationAction.noProxyActionPerformed(Applicati onAction.java:
> >> 662)
> >>        at
> >> org.jdesktop.application.ApplicationAction.actionPerformed(ApplicationActio n.java:

> >> 273)
> >>        at
> >> java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:
> >> 183)
> >>        at

> >> java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.jav a:

> ...
>
> citiţi mai multe »

[Jeff Butler]

I think you want to use queryForList - which will return a
List<Map<String, Object>> of the result set.

Jeff Butler

[valos]

Yes, that worked.

Thanks a lot for your help.
Vasile

On 4 iun., 20:23, Jeff Butler <jeffgbut...@gmail.com> wrote:
> I think you want to use queryForList - which will return a
> List<Map<String, Object>> of the result set.
>
> Jeff Butler
>
>
>
> On Fri, Jun 4, 2010 at 11:47 AM, Nathan Maves <nathan.ma...@gmail.com> wrote:
> > Also you need to change
>
> > return queryForMap("AdHoc", sql, "map");
>
> > The third parameter should be a property of the bean(Map in your
> > case).  So it should be one of "id, nume,cnp" from your query.  I am
> > also just guessing that you might have mean "name" and not "nume" but
> > that is a complete guess.
>
> > nathan
>

> > On Fri, Jun 4, 2010 at 10:44 AM, Nathan Maves <nathan.ma...@gmail.com> wrote:
> >> your resultClass is not correct.  "map" in a type alias to
> >> java.util.Map which is an interface and can not be instantiated.
>
> >> Use "java.util.HashMap" or any other implementation of java.util.Map.
>
> >> nathan
>

> >>> org.jdesktop.application.ApplicationAction.noProxyActionPerformed(Applicati onAction.java:
> >>> 662)
> >>>        at
> >>> org.jdesktop.application.ApplicationAction.actionPerformed(ApplicationActio n.java:

> >>> 273)
> >>>        at
> >>> java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:
> >>> 183)
> >>>        at

> >>> java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.jav a:

> >>> 173)
> >>>        at
> >>> java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
> >>>        at
> >>> java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
> >>>        at java.awt.EventDispatchThread.run(EventDispatchThread.java:
> >>> 121)
> >>> Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
> >>> --- The error occurred in com/burse/persistence/sqlmapdao/sql/
> >>> AdHoc.xml.
> >>> --- The error occurred while applying a result map.
> >>> --- Check the AdHoc-AutoResultMap.
> >>> --- Check the result mapping for the 'CNP' property.
> >>> --- Cause: java.lang.RuntimeException: JavaBeansDataExchange could not
> >>> instantiate result class.  Cause: java.lang.InstantiationException:
> >>> java.util.Map
> >>> Caused by: java.lang.RuntimeException: JavaBeansDataExchange could not
> >>> instantiate result class.  Cause: java.lang.InstantiationException:
> >>> java.util.Map
> >>>        at

> >>> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWith Callback(MappedStatement.java:
> >>> 204)
> >>>        at
> >>> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForL ist(MappedStatement.java:
> >>> 139)
> >>>        at
>

> ...
>
> citiţi mai multe »