You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by sreenivas <ko...@rediffmail.com> on 2005/08/03 23:25:24 UTC
Multiple select queries in one select tag
Hi,
I would like to know whether or not below approach is possible with iBATIS.
<select id ="getEmpDetails" parameterClass = "com.abc.Input" resultClass = "com.abc.Output">
select emp_name as eName, emp_sal as eSal from emp where emp_id = #emp_id# and loc_code=#loc_code#
select dept_no as eDeptNo from dept where emp_id = #emp_id# and loc_code=#loc_code#
</select>
As in the above code, both queries take inputs from same parameter class, and trying to map results too in the same result class.
I know there are other ways to do that, but would like to know possibility of executing multiple select statements in 1 <select> mapping, and populating results of both the queries into same result object.
Thanks and regards,
Sreeni
RE: Ibatis and Spring Framework
Posted by John Hurst <jb...@attglobal.net>.
Hi,
I use iBATIS with Spring. In fact I learned about iBATIS via Spring; I
think the two together work very well.
In addition to Richard's comments, I would add:
- Common configuration mechanism across your whole project, and separation
of configuration from your code.
With Spring, your DAOs can be provided with what they need via dependency
injection. You put for example a setDataSource() in your class, and you can
configure Spring to wire up the DataSource at runtime. This is incredibly
powerful.
- The SqlMapClientDaoSupport provides a great base class for DAOs to make
use of Spring facilities, such as transparent transaction control. It
already has the setters necessary to configure DataSource or SqlMapClient.
It also provides the SqlMapClientTemplate, a smart way to do operations
requiring resources.
For example the iBATIS developer guide example
public void updateItemDescription(String itemId, String newDescription)
throws SQLException {
try {
sqlMap.startTransaction();
Item item = (Item) sqlMap.queryForObject ("getItem", itemId);
item.setDescription (newDescription);
sqlMap.update ("updateItem", item);
sqlMap.commitTransaction ();
} finally {
sqlMap.endTransaction ();
}
}
Could look like this with Spring:
public void updateDescription(String itemId, String newDescription) {
Item item = (Item) getSqlMapClientTemplate().queryForObject("getItem",
itemId);
Item.setDescription(newDescription);
getSqlMapClientTemplate().update("updateItem", item);
}
Many fully transactional DAO methods become one-liners with Spring.
The iBATIS API is very clean; the advantage of the Spring XxxTemplate
classes becomes all the more clearer with an API like raw JDBC.
Regards
John Hurst
> -----Original Message-----
> From: rich oates [mailto:oates1@gmail.com]
> Sent: Friday, 5 August 2005 07:18
> To: user-java@ibatis.apache.org
> Subject: Re: Ibatis and Spring Framework
>
> The Spring JDBC wrapper offers at least the following:
>
> - a further abstraction away from iBatis (which is useful if you wish
> to change the underlying persistence framework in the future)
>
> - common data access exceptions (i.e. the iBatis exceptions are mapped
> to spring exceptions. Again, useful if you want to reduce dependency
> on iBatis).
>
> There are other benefits, especially when the rest of what Spring
> offers is taken into account, e.g. transparent transaction handling.
>
> I recommend having a look at chapter 11 of the Spring reference
> documentation.
>
> regards
> richard
Re: Ibatis and Spring Framework
Posted by rich oates <oa...@gmail.com>.
The Spring JDBC wrapper offers at least the following:
- a further abstraction away from iBatis (which is useful if you wish
to change the underlying persistence framework in the future)
- common data access exceptions (i.e. the iBatis exceptions are mapped
to spring exceptions. Again, useful if you want to reduce dependency
on iBatis).
There are other benefits, especially when the rest of what Spring
offers is taken into account, e.g. transparent transaction handling.
I recommend having a look at chapter 11 of the Spring reference documentation.
regards
richard
Ibatis and Spring Framework
Posted by Abdullah Kauchali <ab...@isanusi.com>.
Can someone who has experience with the Spring Framework please help me
explain briefly what advantages
it could afford /along with/ the use of Ibatis.
What is it that the Spring offers that IBatis does not?
(I know that Spring is multi-facetted, it does AOP stuff etc - but I am
asking specificially about
its JDBC "wrapping" capabilities). Anything there that I should be
looking at?
Any comments would be greatly appreciated,
Kind regards
Abdullah
Re: Multiple select queries in one select tag
Posted by Eugeny N Dzhurinsky <eu...@jdevelop.com>.
On Wed, Aug 03, 2005 at 04:20:57PM -0600, Nathan Maves wrote:
> I think a union is only a good solution when the data from both
> queries can be combined into one column.
Not really, the columns should be ordered to have the same types
in the order, so there is an ability to query for several columns.
--
Eugene N Dzhurinsky
Re: Multiple select queries in one select tag
Posted by Nathan Maves <Na...@Sun.COM>.
I think a union is only a good solution when the data from both
queries can be combined into one column. I think a simple join
between the two table should solve your problem.
does this not work?
<select id ="getEmpDetails" parameterClass = "com.abc.Input"
resultClass = "com.abc.Output">
select A.emp_name as eName, A.emp_sal as eSal, B.dept_no as eDeptNo
from emp A, dept B where A.emp_id = #emp_id# and
A.loc_code=#loc_code# and A.emp_id = B.emp_id
</select>
Nathan
On Aug 3, 2005, at 3:28 PM, Larry Meadors wrote:
> Not that I am aware of....why not just use a union?
>
> Larry
>
>
> On 3 Aug 2005 21:25:24 -0000, sreenivas
> <ko...@rediffmail.com> wrote:
> Hi,
>
> I would like to know whether or not below approach is possible with
> iBATIS.
>
> <select id ="getEmpDetails" parameterClass = "com.abc.Input"
> resultClass = "com.abc.Output">
> select emp_name as eName, emp_sal as eSal from emp where emp_id =
> #emp_id# and loc_code=#loc_code#
>
> select dept_no as eDeptNo from dept where emp_id = #emp_id# and
> loc_code=#loc_code#
> </select>
>
> As in the above code, both queries take inputs from same parameter
> class, and trying to map results too in the same result class.
>
> I know there are other ways to do that, but would like to know
> possibility of executing multiple select statements in 1 <select>
> mapping, and populating results of both the queries into same
> result object.
>
> Thanks and regards,
> Sreeni
>
>
>
>
>
Re: Multiple select queries in one select tag
Posted by Larry Meadors <la...@gmail.com>.
Not that I am aware of....why not just use a union?
Larry
On 3 Aug 2005 21:25:24 -0000, sreenivas <ko...@rediffmail.com> wrote:
>
> Hi,
>
> I would like to know whether or not below approach is possible with
> iBATIS.
>
> <select id ="getEmpDetails" parameterClass = "com.abc.Input" resultClass =
> "com.abc.Output">
> select emp_name as eName, emp_sal as eSal from emp where emp_id = #emp_id#
> and loc_code=#loc_code#
>
> select dept_no as eDeptNo from dept where emp_id = #emp_id# and
> loc_code=#loc_code#
> </select>
>
> As in the above code, both queries take inputs from same parameter class,
> and trying to map results too in the same result class.
>
> I know there are other ways to do that, but would like to know possibility
> of executing multiple select statements in 1 <select> mapping, and
> populating results of both the queries into same result object.
>
> Thanks and regards,
> Sreeni
>
>
> <http://clients.rediff.com/signature/track_sig.asp>
Re: Multiple select queries in one select tag
Posted by Larry Meadors <la...@gmail.com>.
Of those, point #1 is the one that interests me the most - I have
wanted to do just that. Does a selectKey element allow retrieving more
than one value? I need to try that.
The other two seem esoteric, IMO.
Larry
On 8/8/05, Koka <22...@gmail.com> wrote:
> Hi,
> I'm bringing this topic UP cause IMHO I've found some more arguments
> why iBatis users may need to have multiple statements executed in one
> statement tag.
>
> 1. Sometimes we have some fields (simplest example = default values,
> or some calculated values) set at the database side, so after an
> INSERT statement we need to retrieve default/calculated values just
> like we retrieve Autonumber or sequence values. OK, one can 'select'
> in next statement (what I curently do)...
>
> 2. To my taste, I prefer to issue "select 0 from tablename for update
> nowait" PRIOR to update/delete statement in oracle to make sure
> web-request will not wait for indefinite time but just report that
> someone is editing the record (again, I have to issue two statements
> for update = nowait+actual update) and combine them in CODE - would
> prefer to combine in SQLMap
>
> 3. (Inspired by the thread discussing temporary tables and requests
> for sql verification)
> Well, seems soon we'll have tools verifing our statements against the
> database (unit tests?). Hmm, if I 'select' from temp table I need to
> test that select statement is executed only after the creation of
> temporary table...
>
> So, do we need multiple Statement contained in a single Statement tag?
>
> Certainly we'll survive without :)
> Wondering what you think, folks
>
Re: Multiple select queries in one select tag
Posted by Koka <22...@gmail.com>.
Hi,
I'm bringing this topic UP cause IMHO I've found some more arguments
why iBatis users may need to have multiple statements executed in one
statement tag.
1. Sometimes we have some fields (simplest example = default values,
or some calculated values) set at the database side, so after an
INSERT statement we need to retrieve default/calculated values just
like we retrieve Autonumber or sequence values. OK, one can 'select'
in next statement (what I curently do)...
2. To my taste, I prefer to issue "select 0 from tablename for update
nowait" PRIOR to update/delete statement in oracle to make sure
web-request will not wait for indefinite time but just report that
someone is editing the record (again, I have to issue two statements
for update = nowait+actual update) and combine them in CODE - would
prefer to combine in SQLMap
3. (Inspired by the thread discussing temporary tables and requests
for sql verification)
Well, seems soon we'll have tools verifing our statements against the
database (unit tests?). Hmm, if I 'select' from temp table I need to
test that select statement is executed only after the creation of
temporary table...
So, do we need multiple Statement contained in a single Statement tag?
Certainly we'll survive without :)
Wondering what you think, folks
Re: Multiple select queries in one select tag
Posted by Nathan Maves <Na...@Sun.COM>.
I understand that :) But in this case you would be combining
something like em_num with dept_num. In this case you would have to
place dummy columns with either a null of 0 as a place holder. I
still think that my join is way cleaner and more performant.
Nathan
On Aug 4, 2005, at 12:42 AM, Eugeny N Dzhurinsky wrote:
> On Wed, Aug 03, 2005 at 09:25:24PM -0000, sreenivas wrote:
>
>> Hi,
>>
>> I would like to know whether or not below approach is possible
>> with iBATIS.
>>
>> <select id ="getEmpDetails" parameterClass = "com.abc.Input"
>> resultClass =
>> "com.abc.Output"> select emp_name as eName, emp_sal as eSal from
>> emp where
>> emp_id = #emp_id# and loc_code=#loc_code#
>> select dept_no as eDeptNo from dept where emp_id = #emp_id# and
>> loc_code=#loc_code# </select>
>> As in the above code, both queries take inputs from same parameter
>> class,
>> and trying to map results too in the same result class.
>>
>> I know there are other ways to do that, but would like to know
>> possibility
>> of executing multiple select statements in 1 <select> mapping, and
>> populating results of both the queries into same result object.
>>
>
> select ... from ... where ... union select ... from ... where ... etc
> =)
>
> --
> Eugene N Dzhurinsky
>
Re: Multiple select queries in one select tag
Posted by Eugeny N Dzhurinsky <eu...@jdevelop.com>.
On Wed, Aug 03, 2005 at 09:25:24PM -0000, sreenivas wrote:
> Hi,
>
> I would like to know whether or not below approach is possible with iBATIS.
>
> <select id ="getEmpDetails" parameterClass = "com.abc.Input" resultClass =
> "com.abc.Output"> select emp_name as eName, emp_sal as eSal from emp where
> emp_id = #emp_id# and loc_code=#loc_code#
> select dept_no as eDeptNo from dept where emp_id = #emp_id# and
> loc_code=#loc_code# </select>
> As in the above code, both queries take inputs from same parameter class,
> and trying to map results too in the same result class.
>
> I know there are other ways to do that, but would like to know possibility
> of executing multiple select statements in 1 <select> mapping, and
> populating results of both the queries into same result object.
select ... from ... where ... union select ... from ... where ... etc
=)
--
Eugene N Dzhurinsky