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 Michael Laccetti <mi...@s2g-limited.com> on 2006/01/27 21:07:11 UTC
Select count(*) in a resultMap
Is it possible to do a 'select count(*) from table' and then reference that
value in a result map? I've been searching around and trying to get it to
work to no avail.
Mike
Re: Select count(*) in a resultMap
Posted by Ted Schrader <ts...@gmail.com>.
Hi Mike,
I'm glad I could help.
Obviously, in my listing of options to try, I skirted the direct issue
of why your nested selects wouldn't work. To be honest, it seems like
your configuration should have worked, and I have no idea why it
didn't (but I'm not too experienced with SqlMaps yet).
Again, I'm glad the simpler SqlMaps config coupled with a slightly
more-complicated query is working. Plus, the query is now a single
hit to the DB.
Ted
On 28/01/06, Michael Laccetti <mi...@s2g-limited.com> wrote:
> Thanks Ted, I switched it over to select ... from dual, and it worked quite
> nicely. I shall have to remember this for the future.
>
> Mike
>
> -----Original Message-----
> From: Ted Schrader [mailto:tschrader@gmail.com]
> Sent: January 27, 2006 4:11 PM
> To: user-java@ibatis.apache.org
> Subject: Re: Select count(*) in a resultMap
>
> Hi Michael,
>
> You've got two options that I can think of off the top of my head.
>
> A. You execute each query separately, then cobble the results
> together "manually" in your Java code to build an instance of
> "ChannelCount".
>
> B. You craft a single query, assuming you have a master table for the
> channels (called Channels in this example):
>
> SELECT channel,
> (SELECT COUNT(*)
> FROM scrimbot_channels sc
> WHERE sc.channel = c.channel) AS
> existingCount,
> (SELECT COUNT(*)
> FROM scrimbot_pending_channels spc
> WHERE sc.channel = c.channel) AS
> pendingCount
> FROM Channels c
>
> You could use the query above for a queryForList(), or add a suitable
> WHERE clause for queryForObject().
>
> If you don't have a "Channels" table, try changing the above query to
> hit the "dummy" table, which depends on your particular DBMS.
>
> Ted
>
>
>
>
RE: Select count(*) in a resultMap
Posted by Michael Laccetti <mi...@s2g-limited.com>.
Thanks Ted, I switched it over to select ... from dual, and it worked quite
nicely. I shall have to remember this for the future.
Mike
-----Original Message-----
From: Ted Schrader [mailto:tschrader@gmail.com]
Sent: January 27, 2006 4:11 PM
To: user-java@ibatis.apache.org
Subject: Re: Select count(*) in a resultMap
Hi Michael,
You've got two options that I can think of off the top of my head.
A. You execute each query separately, then cobble the results
together "manually" in your Java code to build an instance of
"ChannelCount".
B. You craft a single query, assuming you have a master table for the
channels (called Channels in this example):
SELECT channel,
(SELECT COUNT(*)
FROM scrimbot_channels sc
WHERE sc.channel = c.channel) AS
existingCount,
(SELECT COUNT(*)
FROM scrimbot_pending_channels spc
WHERE sc.channel = c.channel) AS
pendingCount
FROM Channels c
You could use the query above for a queryForList(), or add a suitable
WHERE clause for queryForObject().
If you don't have a "Channels" table, try changing the above query to
hit the "dummy" table, which depends on your particular DBMS.
Ted
Re: Select count(*) in a resultMap
Posted by Ted Schrader <ts...@gmail.com>.
Hi Michael,
You've got two options that I can think of off the top of my head.
A. You execute each query separately, then cobble the results
together "manually" in your Java code to build an instance of
"ChannelCount".
B. You craft a single query, assuming you have a master table for the
channels (called Channels in this example):
SELECT channel,
(SELECT COUNT(*)
FROM scrimbot_channels sc
WHERE sc.channel = c.channel) AS existingCount,
(SELECT COUNT(*)
FROM scrimbot_pending_channels spc
WHERE sc.channel = c.channel) AS pendingCount
FROM Channels c
You could use the query above for a queryForList(), or add a suitable
WHERE clause for queryForObject().
If you don't have a "Channels" table, try changing the above query to
hit the "dummy" table, which depends on your particular DBMS.
Ted
RE: Select count(*) in a resultMap
Posted by Michael Laccetti <mi...@s2g-limited.com>.
Forgot about that bit. Thanks.
Based on that, I've come up with this:
<resultMap id="get-channel-count" class="channelCount">
<result property="existing" column="existingCount" />
<result property="pending" column="channel"
select="getChannelPendingCount" />
</resultMap>
<select id="getChannelPendingCount" resultClass="java.lang.Integer">
SELECT COUNT(*) as pendingCount FROM scrimbot_pending_channels WHERE
channel=#value#
</select>
<statement id="getChannelCount" resultMap="get-channel-count">
SELECT COUNT(*) as existingCount, channel FROM scrimbot_channels WHERE
channel=#value# GROUP BY channel
</statement>
I'm trying to get whether or not the channel exist in either the existing or
pending tables. I can see that the getChannelCount statement is called, but
the nested select is not happening, and I get a null object back. Any
ideas?
Mike
-----Original Message-----
From: Ted Schrader [mailto:tschrader@gmail.com]
Sent: January 27, 2006 3:10 PM
To: user-java@ibatis.apache.org
Subject: Re: Select count(*) in a resultMap
Hi Michael,
Try giving the resulting column an alias:
SELECT COUNT(*) AS MY_TOTAL FROM TABLE.
Then, use "MY_TOTAL" in your result map configuration.
Ted
On 27/01/06, Michael Laccetti <mi...@s2g-limited.com> wrote:
>
>
>
> Is it possible to do a 'select count(*) from table' and then reference
that
> value in a result map? I've been searching around and trying to get it to
> work to no avail.
>
>
>
> Mike
Re: Select count(*) in a resultMap
Posted by Ted Schrader <ts...@gmail.com>.
Hi Michael,
Try giving the resulting column an alias:
SELECT COUNT(*) AS MY_TOTAL FROM TABLE.
Then, use "MY_TOTAL" in your result map configuration.
Ted
On 27/01/06, Michael Laccetti <mi...@s2g-limited.com> wrote:
>
>
>
> Is it possible to do a 'select count(*) from table' and then reference that
> value in a result map? I've been searching around and trying to get it to
> work to no avail.
>
>
>
> Mike