You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by Jürgen Hoffmann <jh...@byteaction.de> on 2003/02/15 11:09:38 UTC
Criteria Mystery
Hi All,
My Apologies for this very long E-Mail, but this seems to be a complex
problem, and I wanted to hopefully present you each little piece of
information that the experts need to solve this problem... I have
Cross-Posted this message, since it could be a turbine or torque
problem...
I am getting very very frustrated here. Since I have manged to count the
rows for a given criteria in a table by doing the following in my Action
which fills the context for the view...
cri.add(cri.getNewCriterion(WebserverDailyPeer.DAY,
(Object)("(UNIX_TIMESTAMP(" + WebserverDailyPeer.DAY + ") between " +
(von_date / 1000) + " and " +
(bis_date / 1000) + " )"), Criteria.CUSTOM));
long pages = WebserverDailyPeer.getPages(cri, limit);
Log.debug("Number of Pages in Table with Criteria: " + pages);
It works and prints
[Sat Feb 15 10:42:15 CET 2003] -- DEBUG -- Number of Pages in Table with
Criteria: 58
In turbine.log
getPages is from my extended BasePeer Class in which I put the following
functions:
public class ByteActionBasePeer extends BasePeer
{
public static long getNumberOfRowsInTable(String table) throws
TorqueException, DataSetException
{
String query = "SELECT COUNT(*) FROM " + table;
return
((Record)executeQuery(query).get(0)).getValue(1).asLong();
}
public static long getNumberOfRowsInTable(Criteria c) throws
TorqueException, DataSetException
{
c.addSelectColumn("COUNT(*)");
String query = createQueryString(c);
c.getSelectColumns().clear();
return
((Record)executeQuery(query).get(0)).getValue(1).asLong();
}
public static long getPages(Criteria c, int limit) throws
TorqueException, DataSetException
{
long numRows = getNumberOfRowsInTable(c);
long pages = (numRows / limit);
pages = ( numRows % limit == 0 ) ? pages : pages + 1;
return pages;
}
public static long getPages(String table, int limit) throws
TorqueException, DataSetException
{
long numRows = getNumberOfRowsInTable(table);
long pages = (numRows / limit);
pages = ( numRows % limit == 0 ) ? pages : pages + 1;
return pages;
}
}
Now I have my number of Pages, I have my Limit and I have my Index. And
now I am adding these to the criteria,
cri.setLimit(limit);
cri.setOffset(index);
Doing a WebserverDailyPeer.doSelect(cri); did not use the Limit and
Index, so I thought maybe I should add the columns, since I have done a
addSelectColumns("COUNT(*)") and a clear() in my extended BasePeer
Class, and since Criteria sometimes behaves weird. So I did.
cri.addSelectColumn(WebserverDailyPeer.ID);
cri.addSelectColumn(WebserverDailyPeer.DAY);
cri.addSelectColumn(WebserverDailyPeer.AMOUNT);
cri.addSelectColumn(WebserverDailyPeer.DOMAIN);
cri.addSelectColumn(WebserverDailyPeer.KID);
cri.addSelectColumn(WebserverDailyPeer.CREATION);
cri.addSelectColumn(WebserverDailyPeer.SERVERID);
And then verifying the output via:
Log.debug("AuswerungAction",WebserverDailyPeer.createQueryString(cri));
And this gets correctly translated to:
[Sat Feb 15 10:42:15 CET 2003] -- DEBUG -- FROM logger:AuswerungAction:
SELECT webserver_daily.ID, webserver_daily.DAY, webserver_daily.AMOUNT,
webserver_daily.DOMAIN, webserver_daily.KID, webserver_daily.CREATION,
webserver_daily.SERVERID FROM webserver_daily WHERE
(UNIX_TIMESTAMP(webserver_daily.DAY) between 1009839600 and 1041375600 )
LIMIT 1, 20
BUT when I then do a
List list = WebserverDailyPeer.doSelect(cri);
The Limit and Index mysteriously disappears... As you can see here:
22802 [Thread-7] DEBUG util.BasePeer - SELECT webserver_daily.ID,
webserver_daily.DAY, webserver_daily.AMOUNT
, webserver_daily.DOMAIN, webserver_daily.KID, webserver_daily.CREATION,
webserver_daily.SERVERID FROM webserv
er_daily WHERE (UNIX_TIMESTAMP(webserver_daily.DAY) between 1009839600
and 1041375600 ) LIMIT 1, 20
22802 [Thread-7] DEBUG util.BasePeer - SELECT webserver_daily.ID,
webserver_daily.DAY, webserver_daily.AMOUNT
, webserver_daily.DOMAIN, webserver_daily.KID, webserver_daily.CREATION,
webserver_daily.SERVERID FROM webserv
er_daily WHERE (UNIX_TIMESTAMP(webserver_daily.DAY) between 1009839600
and 1041375600 )
It seems as if The Peer is doing 2 Selects...
The Complete Action Method follows below, so you can verify that I am
not calling do Select twice:
public void doGetwebservers(RunData data, Context context)
throws Exception
{
ParameterParser pp = data.getParameters();
int wsid = pp.getInt("wsid");
int kdid = pp.getInt("kdid");
int von_tag = pp.getInt("von_tag");
int bis_tag = pp.getInt("bis_tag");
int von_monat = pp.getInt("von_monat");
int bis_monat = pp.getInt("bis_monat");
int von_jahr = pp.getInt("von_jahr");
int bis_jahr = pp.getInt("bis_jahr");
int limit = pp.getInt("limit",20);
int index = pp.getInt("index",0);
int curpage = pp.getInt("page",1);
String domain = pp.getString("domain");
Calendar von = new GregorianCalendar(von_jahr, von_monat - 1,
von_tag);
long von_date = (long)((Date)von.getTime()).getTime();
Calendar bis = new GregorianCalendar(bis_jahr, bis_monat - 1,
bis_tag);
long bis_date = (long)((Date)bis.getTime()).getTime();
Criteria cri = new Criteria();
if(wsid > 0)
{
cri.add(WebserverDailyPeer.SERVERID, wsid);
}
if(kdid > 0)
{
cri.add(WebserverDailyPeer.KID, kdid);
}
if((domain != null) && (domain.length() > 0))
{
cri.add(cri.getNewCriterion(WebserverDailyPeer.DOMAIN,
(Object)(WebserverDailyPeer.DOMAIN + " LIKE '%" +
domain + "%' "), Criteria.CUSTOM));
cri.addJoin(WebserverDailyPeer.DOMAIN,
DomainConfigPeer.NAME);
}
cri.add(cri.getNewCriterion(WebserverDailyPeer.DAY,
(Object)("(UNIX_TIMESTAMP(" + WebserverDailyPeer.DAY +
") between " + (von_date / 1000) + " and " +
(bis_date / 1000) + " )"), Criteria.CUSTOM));
long pages =
WebserverDailyPeer.getPages(cri, limit);
Log.debug("Number of Pages in Table with
Criteria: " + pages);
context.put("index", new
Integer(index));
context.put("pages", new Long(pages));
context.put("limit", new
Integer(limit));
context.put("curpage", new
Integer(curpage));
Log.debug("AuswerungAction",WebserverDailyPeer.createQueryString(cri));
cri.setLimit(limit);
cri.setOffset(1);
cri.addSelectColumn(WebserverDailyPeer.ID);
cri.addSelectColumn(WebserverDailyPeer.DAY);
cri.addSelectColumn(WebserverDailyPeer.AMOUNT);
cri.addSelectColumn(WebserverDailyPeer.DOMAIN);
cri.addSelectColumn(WebserverDailyPeer.KID);
cri.addSelectColumn(WebserverDailyPeer.CREATION);
cri.addSelectColumn(WebserverDailyPeer.SERVERID);
Log.debug("AuswerungAction",WebserverDailyPeer.createQueryString(cri));
List list = WebserverDailyPeer.doSelect(cri);
context.put("webservers", list);
}
Kind regards
Jürgen Hoffmann
ByteACTION GmbH
cert. Perl Programmer
cert. Linux System Administrator
cert. Java Programmer
Besuchen Sie uns doch auf der CeBIT 2003
in Halle 4/Stand 70
Re: Criteria Mystery
Posted by Scott Eade <se...@backstagetech.com.au>.
The coupled Torque in Turbine 2.1 may reset the offset and limit not only
when a query is executed, but also when you go criteria.toString(). The
decoupled Torque 3.0 used by Turbine 2.2 no longer alters these values for
criteria.toString(), but may still do so when you execute the query.
This behaviour depends upon the database in use - this determines whether
the database or village is used to process the offset and limit. Look in
BasePeer.createQueryString() for the relevant code.
HTH,
Scott
--
Scott Eade
Backstage Technologies Pty. Ltd.
http://www.backstagetech.com.au
.Mac Chat/AIM: seade at mac dot com
On 15/02/2003 9:09 PM, "Jürgen Hoffmann" <jh...@byteaction.de> wrote:
> Hi All,
>
> My Apologies for this very long E-Mail, but this seems to be a complex
> problem, and I wanted to hopefully present you each little piece of
> information that the experts need to solve this problem... I have
> Cross-Posted this message, since it could be a turbine or torque
> problem...
>
> I am getting very very frustrated here. Since I have manged to count the
> rows for a given criteria in a table by doing the following in my Action
> which fills the context for the view...
>
> cri.add(cri.getNewCriterion(WebserverDailyPeer.DAY,
> (Object)("(UNIX_TIMESTAMP(" + WebserverDailyPeer.DAY + ") between " +
> (von_date / 1000) + " and " +
> (bis_date / 1000) + " )"), Criteria.CUSTOM));
>
> long pages = WebserverDailyPeer.getPages(cri, limit);
> Log.debug("Number of Pages in Table with Criteria: " + pages);
>
> It works and prints
> [Sat Feb 15 10:42:15 CET 2003] -- DEBUG -- Number of Pages in Table with
> Criteria: 58
> In turbine.log
>
> getPages is from my extended BasePeer Class in which I put the following
> functions:
> public class ByteActionBasePeer extends BasePeer
> {
> public static long getNumberOfRowsInTable(String table) throws
> TorqueException, DataSetException
> {
> String query = "SELECT COUNT(*) FROM " + table;
> return
> ((Record)executeQuery(query).get(0)).getValue(1).asLong();
> }
>
> public static long getNumberOfRowsInTable(Criteria c) throws
> TorqueException, DataSetException
> {
> c.addSelectColumn("COUNT(*)");
> String query = createQueryString(c);
> c.getSelectColumns().clear();
> return
> ((Record)executeQuery(query).get(0)).getValue(1).asLong();
> }
>
> public static long getPages(Criteria c, int limit) throws
> TorqueException, DataSetException
> {
> long numRows = getNumberOfRowsInTable(c);
> long pages = (numRows / limit);
> pages = ( numRows % limit == 0 ) ? pages : pages + 1;
> return pages;
> }
>
> public static long getPages(String table, int limit) throws
> TorqueException, DataSetException
> {
> long numRows = getNumberOfRowsInTable(table);
> long pages = (numRows / limit);
> pages = ( numRows % limit == 0 ) ? pages : pages + 1;
> return pages;
> }
> }
>
> Now I have my number of Pages, I have my Limit and I have my Index. And
> now I am adding these to the criteria,
>
> cri.setLimit(limit);
> cri.setOffset(index);
>
> Doing a WebserverDailyPeer.doSelect(cri); did not use the Limit and
> Index, so I thought maybe I should add the columns, since I have done a
> addSelectColumns("COUNT(*)") and a clear() in my extended BasePeer
> Class, and since Criteria sometimes behaves weird. So I did.
>
> cri.addSelectColumn(WebserverDailyPeer.ID);
> cri.addSelectColumn(WebserverDailyPeer.DAY);
> cri.addSelectColumn(WebserverDailyPeer.AMOUNT);
> cri.addSelectColumn(WebserverDailyPeer.DOMAIN);
> cri.addSelectColumn(WebserverDailyPeer.KID);
> cri.addSelectColumn(WebserverDailyPeer.CREATION);
> cri.addSelectColumn(WebserverDailyPeer.SERVERID);
>
> And then verifying the output via:
>
> Log.debug("AuswerungAction",WebserverDailyPeer.createQueryString(cri));
> And this gets correctly translated to:
> [Sat Feb 15 10:42:15 CET 2003] -- DEBUG -- FROM logger:AuswerungAction:
> SELECT webserver_daily.ID, webserver_daily.DAY, webserver_daily.AMOUNT,
> webserver_daily.DOMAIN, webserver_daily.KID, webserver_daily.CREATION,
> webserver_daily.SERVERID FROM webserver_daily WHERE
> (UNIX_TIMESTAMP(webserver_daily.DAY) between 1009839600 and 1041375600 )
> LIMIT 1, 20
>
> BUT when I then do a
> List list = WebserverDailyPeer.doSelect(cri);
>
> The Limit and Index mysteriously disappears... As you can see here:
> 22802 [Thread-7] DEBUG util.BasePeer - SELECT webserver_daily.ID,
> webserver_daily.DAY, webserver_daily.AMOUNT
> , webserver_daily.DOMAIN, webserver_daily.KID, webserver_daily.CREATION,
> webserver_daily.SERVERID FROM webserv
> er_daily WHERE (UNIX_TIMESTAMP(webserver_daily.DAY) between 1009839600
> and 1041375600 ) LIMIT 1, 20
> 22802 [Thread-7] DEBUG util.BasePeer - SELECT webserver_daily.ID,
> webserver_daily.DAY, webserver_daily.AMOUNT
> , webserver_daily.DOMAIN, webserver_daily.KID, webserver_daily.CREATION,
> webserver_daily.SERVERID FROM webserv
> er_daily WHERE (UNIX_TIMESTAMP(webserver_daily.DAY) between 1009839600
> and 1041375600 )
>
> It seems as if The Peer is doing 2 Selects...
>
> The Complete Action Method follows below, so you can verify that I am
> not calling do Select twice:
>
> public void doGetwebservers(RunData data, Context context)
> throws Exception
> {
> ParameterParser pp = data.getParameters();
> int wsid = pp.getInt("wsid");
> int kdid = pp.getInt("kdid");
> int von_tag = pp.getInt("von_tag");
> int bis_tag = pp.getInt("bis_tag");
> int von_monat = pp.getInt("von_monat");
> int bis_monat = pp.getInt("bis_monat");
> int von_jahr = pp.getInt("von_jahr");
> int bis_jahr = pp.getInt("bis_jahr");
> int limit = pp.getInt("limit",20);
> int index = pp.getInt("index",0);
> int curpage = pp.getInt("page",1);
> String domain = pp.getString("domain");
> Calendar von = new GregorianCalendar(von_jahr, von_monat - 1,
> von_tag);
> long von_date = (long)((Date)von.getTime()).getTime();
> Calendar bis = new GregorianCalendar(bis_jahr, bis_monat - 1,
> bis_tag);
> long bis_date = (long)((Date)bis.getTime()).getTime();
>
> Criteria cri = new Criteria();
>
> if(wsid > 0)
> {
> cri.add(WebserverDailyPeer.SERVERID, wsid);
> }
>
> if(kdid > 0)
> {
> cri.add(WebserverDailyPeer.KID, kdid);
> }
>
> if((domain != null) && (domain.length() > 0))
> {
> cri.add(cri.getNewCriterion(WebserverDailyPeer.DOMAIN,
> (Object)(WebserverDailyPeer.DOMAIN + " LIKE '%" +
> domain + "%' "), Criteria.CUSTOM));
> cri.addJoin(WebserverDailyPeer.DOMAIN,
> DomainConfigPeer.NAME);
> }
>
> cri.add(cri.getNewCriterion(WebserverDailyPeer.DAY,
> (Object)("(UNIX_TIMESTAMP(" + WebserverDailyPeer.DAY +
> ") between " + (von_date / 1000) + " and " +
> (bis_date / 1000) + " )"), Criteria.CUSTOM));
>
> long pages =
> WebserverDailyPeer.getPages(cri, limit);
> Log.debug("Number of Pages in Table with
> Criteria: " + pages);
>
>
> context.put("index", new
> Integer(index));
> context.put("pages", new Long(pages));
> context.put("limit", new
> Integer(limit));
> context.put("curpage", new
> Integer(curpage));
>
>
> Log.debug("AuswerungAction",WebserverDailyPeer.createQueryString(cri));
>
> cri.setLimit(limit);
> cri.setOffset(1);
>
> cri.addSelectColumn(WebserverDailyPeer.ID);
>
> cri.addSelectColumn(WebserverDailyPeer.DAY);
>
> cri.addSelectColumn(WebserverDailyPeer.AMOUNT);
>
> cri.addSelectColumn(WebserverDailyPeer.DOMAIN);
>
> cri.addSelectColumn(WebserverDailyPeer.KID);
>
> cri.addSelectColumn(WebserverDailyPeer.CREATION);
>
> cri.addSelectColumn(WebserverDailyPeer.SERVERID);
>
>
> Log.debug("AuswerungAction",WebserverDailyPeer.createQueryString(cri));
>
> List list = WebserverDailyPeer.doSelect(cri);
>
> context.put("webservers", list);
> }
>
> Kind regards
>
> Jürgen Hoffmann
> ByteACTION GmbH
>
> cert. Perl Programmer
> cert. Linux System Administrator
> cert. Java Programmer
>
> Besuchen Sie uns doch auf der CeBIT 2003
> in Halle 4/Stand 70
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-user-help@jakarta.apache.org