Return-Path: Mailing-List: contact ibatis-user-java-help@incubator.apache.org; run by ezmlm Delivered-To: mailing list ibatis-user-java@incubator.apache.org Received: (qmail 68451 invoked by uid 99); 10 Dec 2004 13:19:32 -0000 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests=RCVD_BY_IP,SPF_HELO_PASS,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (hermes.apache.org: domain of clinton.begin@gmail.com designates 64.233.184.207 as permitted sender) Received: from wproxy.gmail.com (HELO wproxy.gmail.com) (64.233.184.207) by apache.org (qpsmtpd/0.28) with ESMTP; Fri, 10 Dec 2004 05:19:32 -0800 Received: by wproxy.gmail.com with SMTP id 70so541476wra for ; Fri, 10 Dec 2004 05:19:29 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=WGwewvZcWEl51tUC+LlIguw+uJufBQnRc7o00gcvld7La5jKKGuTOsgm69t+1Ksm0fgZaCBslu5ob9usAdbDmkr1Z7uoFrU91oAs9IyYYFIhJDRVi/9QVctEz2eYauEeG20ClHo0IFNH+GTT39LRaUMP0chpMaYm3lTbUsEFm0s= Received: by 10.54.51.57 with SMTP id y57mr16572wry; Fri, 10 Dec 2004 05:19:29 -0800 (PST) Received: by 10.54.33.19 with HTTP; Fri, 10 Dec 2004 05:19:29 -0800 (PST) Message-ID: <16178eb104121005192ddfe563@mail.gmail.com> Date: Fri, 10 Dec 2004 06:19:29 -0700 From: Clinton Begin Reply-To: cbegin@ibatis.com To: Kevin Subject: Re: Changing result set metadata on the fly. Cc: Brandon Goodin , ibatis-user-java@incubator.apache.org In-Reply-To: <20041210083516.86341.qmail@web52804.mail.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <2fe5ef5b0412092346ac90162@mail.gmail.com> <20041210083516.86341.qmail@web52804.mail.yahoo.com> X-Virus-Checked: Checked Kevin, Dynamic ResultSet metadata is not supported. There are a number of very good reasons, which are mostly performance related. In the future, I would hope to solve this by supporting SQL fragmets, so that you can maintain the SQL in one place, but have 2 statements that use it. That way you don't even need the dynamic SQL part, nor do you need to imply the count by passing a null parameter. Cheers, Clinton On Fri, 10 Dec 2004 00:35:16 -0800 (PST), Kevin wrote: > > Hi > > Thanks for answering my questions, thanks for forwding > it to the forum. > > What u are suggesting is exactly what i am doing i.e > using 2 queries 1 for count and 1 for the result set. > > But i was hoping to use 1 dynamic sql instead of 2 to > achieve the above result. > > U can look at my sql example and u will see that the > condition after the Select does not work and that is > actually my question. > > Why does not work ? > > Thanks > Navin > > > --- Brandon Goodin wrote: > > > Paginated list only returns a subset of the results. > > This is > > determined by the pageSize. It does not retain a > > total count. > > > > If you perform a count using the resultset it will > > force the cursor to > > traverse the whole resultset. Most databases do not > > load up large > > results sets completely. So, when you perform your > > count using the > > resultset you are already impacting your system in a > > negative way and > > not saving yourself any performance. > > > > My suggestion would be to use two sql calls. One > > using a count (as a > > count sql statement would be more performant than a > > resultset > > traversal) and the other sql statement to retrieve > > the results you > > want from the resultset. The PaginatedList in iBatis > > will only > > traverse the rows in the resultset that it needs to > > in order to > > populate the corresponding objects with. Then when > > you call the next > > page on the paginated list it retains the > > appropriate information to > > reconnect to the database and grab the next section > > of the results > > that you need. > > > > With this strategy you save yourself on memory and > > cpu. > > > > Brandon > > > > > > On Thu, 9 Dec 2004 23:31:15 -0800 (PST), Kevin > > wrote: > > > Thanks for taking time to respond to my question. > > > > > > Here is an example of what i am trying to do. > > > > > > I have a query that serves a dual purpose > > > 1> > > > getting me a count of all the records that meet a > > > certain criteria > > > 2> > > > return a subset of the records of whole list i > > used > > > for getting the count as i donot need the whole > > result > > > set for display. > > > > > > The type of query executed depends on a field in > > the > > > parameter class. > > > > > > I have attached my query below , if u look at it > > > carefully u will see that the Select clause has a > > > condition on "countQuery" and so does the where > > > clause. > > > > > > Essentially the resultset columns change on the > > fly > > > depending on the query selected. > > > > > > This as far as i am told is not supported in > > Ibatis. > > > > > > I was wondering why this is not supported and what > > are > > > the alternatives rather than having 2 queries. > > > > > > Your suggestion of doing a size on the list will > > not > > > work because the result set is only a subset. i.e > > i > > > only display 100 out lets say 10000 records on the > > web > > > layer to make it more effecient , so i set my > > where > > > clause to give me the range of records whose list > > size > > > will be 100 but the count should return 10000. The > > > reson i need 10000 is for creating a web layer > > based > > > paginated list. > > > > > > Does the paginated list in Ibatis support this? > > > > > >