You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@turbine.apache.org by wuym <wu...@163.com> on 2001/04/26 11:48:56 UTC
Re[2]: Why DBOracle.java not support supportsNativeOffset().
Thanks your suggestion.
also I already found this problem.
Because these problem,So my first idea can not implement.
Now I use SQL syntax like this:
------------------------------------------------------------------
select * from (select ttt.*,rownum crownum from ( select * from
table_name where id>2 .. order by id) ttt ) where crownum>start and
crownum <=end
------------------------------------------------------------------
So my Solutiong is modify BasePeer.java like below:
-----------------------------------------------------------------
BasePeer.doselectOracle(Critiera cri,int start, int numberofResult) {
.....
....
String sqlstr = createQueryByOffsetandLimit(createQueryString(criteria) ,start, numberOfResult)
results = executeQuery( sqlstr,criteria.isSingleRecord(),dbCon);
....
....
}
private static String createQueryByOffsetandLimit(String sqlstr,int start,int numberOfResult) {
String querystr = "";
querystr = "select * from ( select ttt.*,rownum countrownum from (" + sqlstr + ") ttt )" +
" where ( countrownum > " + start + " ) " ;
if (numberOfResult > 0) {
int end = start + numberOfResult;
querystr = querystr +" and ( countrownum <= " + end + " ) " ;
}
return querystr;
}
-----------------------------------------------------------------
So, in the XXXPeer.java i can use doselectOracle();
-regards
Eddie wu
On Wed, 25 Apr 2001 10:26:44 -0600
Dave Polito <da...@planetcad.com> wrote:
> This will not work with Oracle. In Oracle, rownum is a pseudo-column and
> the row number is assigned as the data is pulled from the database. When
> you issue the following;
>
> select somevarchar, somenumber from sometable order by somenumber where
> ROWNUM < 10;
>
> Oracle will return the first 10 rows then sort them. This is clearly not
> what you would want. This can be solved with an in-line view;
>
> select *
> from (select somevarchar, somenumber from sometable order by somenumber)
> where rownum < 10;
>
> If you want;
>
> select somevarchar, somenumber from sometable where ROWNUM > 10;
>
> Nothing will get returned from this query, because the first row fetched is
> assigned a ROWNUM of 1 and makes the condition false. The second row to be
> fetched is now the first row and is also
> assigned a ROWNUM of 1 and makes the condition false. All rows subsequently
> fail to satisfy the condition, so no rows are returned.
>
> So that means that a range, ROWNUM > 5 and ROWNUM < 10, will not work
> either.
>
> Possible solution;
>
> select * from
> (select * from sometable where rownum < 10 order by rownum desc)
> where rownum < 5;
>
> But this will return the rows in the wrong order. That may not be a big
> deal.
>
> Another solution is to use PL/SQL.
>
> The ROWNUM pseudo-column is not very useful.
>
> Dave Polito
>
>
> -----Original Message-----
> From: John McNally [mailto:jmcnally@collab.net]
> Sent: Wednesday, April 25, 2001 9:33 AM
> To: turbine-dev@jakarta.apache.org
> Subject: Re: Why DBOracle.java not support supportsNativeOffset().
>
>
> you can. just post the results of cvs diff -u when you have it working.
>
> john mcnally
>
> wuym wrote:
> >
> > Hi, all
> >
> > I know Oracle support SQL syntax like this:
> > SQL SELECT blah from foo where id = 1 and rownum <= 10;
> > like Mysql's limit syntax.
> >
> > who can patch the DBOracle.java and BasePeer.java get support Oracle's
> > limit syntax???
> >
> > Eddie wu
> >
> > --
> > wuym <wu...@163.com>
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: turbine-dev-help@jakarta.apache.org
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: turbine-dev-help@jakarta.apache.org
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: turbine-dev-help@jakarta.apache.org
--
wuym <wu...@163.com>
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-dev-help@jakarta.apache.org
om/peer class hierarchy [was Re: Why DBOracle.java not support
supportsNativeOffset().]
Posted by John McNally <jm...@collab.net>.
you can use the
<database baseClass="MyBaseClass" basePeer="MyBasePeer">
attributes to form a class hierarchy like
BasePeer
MyBasePeer
BaseTableFooPeer
TableFooPeer
and
possibly BaseObject
MyBaseClass
BaseTableFoo
TableFoo
for a given table, TABLE_FOO. Note it is very likely that you will want
MyBasePeer to extend BasePeer. It is also likely, though less so, that
MyBaseClass would extend BaseObject.
BaseObject is not required as you can implement the Persistent interface
in TableFoo. This gives a lot of flexibility in implementing the
business objects.
You can also specify the baseClass and basePeer attributes at the
<table> level to override any setting at the <database> level.
There should be no need add or modify BasePeer or BaseObject, and
avoiding it will provide easier upgrade.
John McNally
wuym wrote:
>
> Thanks your suggestion.
> also I already found this problem.
> Because these problem,So my first idea can not implement.
> Now I use SQL syntax like this:
> ------------------------------------------------------------------
> select * from (select ttt.*,rownum crownum from ( select * from
> table_name where id>2 .. order by id) ttt ) where crownum>start and
> crownum <=end
> ------------------------------------------------------------------
> So my Solutiong is modify BasePeer.java like below:
> -----------------------------------------------------------------
> BasePeer.doselectOracle(Critiera cri,int start, int numberofResult) {
> .....
> ....
> String sqlstr = createQueryByOffsetandLimit(createQueryString(criteria) ,start, numberOfResult)
> results = executeQuery( sqlstr,criteria.isSingleRecord(),dbCon);
> ....
> ....
> }
> private static String createQueryByOffsetandLimit(String sqlstr,int start,int numberOfResult) {
> String querystr = "";
> querystr = "select * from ( select ttt.*,rownum countrownum from (" + sqlstr + ") ttt )" +
> " where ( countrownum > " + start + " ) " ;
> if (numberOfResult > 0) {
> int end = start + numberOfResult;
> querystr = querystr +" and ( countrownum <= " + end + " ) " ;
> }
> return querystr;
> }
> -----------------------------------------------------------------
> So, in the XXXPeer.java i can use doselectOracle();
>
> -regards
>
> Eddie wu
>
> On Wed, 25 Apr 2001 10:26:44 -0600
> Dave Polito <da...@planetcad.com> wrote:
>
> > This will not work with Oracle. In Oracle, rownum is a pseudo-column and
> > the row number is assigned as the data is pulled from the database. When
> > you issue the following;
> >
> > select somevarchar, somenumber from sometable order by somenumber where
> > ROWNUM < 10;
> >
> > Oracle will return the first 10 rows then sort them. This is clearly not
> > what you would want. This can be solved with an in-line view;
> >
> > select *
> > from (select somevarchar, somenumber from sometable order by somenumber)
> > where rownum < 10;
> >
> > If you want;
> >
> > select somevarchar, somenumber from sometable where ROWNUM > 10;
> >
> > Nothing will get returned from this query, because the first row fetched is
> > assigned a ROWNUM of 1 and makes the condition false. The second row to be
> > fetched is now the first row and is also
> > assigned a ROWNUM of 1 and makes the condition false. All rows subsequently
> > fail to satisfy the condition, so no rows are returned.
> >
> > So that means that a range, ROWNUM > 5 and ROWNUM < 10, will not work
> > either.
> >
> > Possible solution;
> >
> > select * from
> > (select * from sometable where rownum < 10 order by rownum desc)
> > where rownum < 5;
> >
> > But this will return the rows in the wrong order. That may not be a big
> > deal.
> >
> > Another solution is to use PL/SQL.
> >
> > The ROWNUM pseudo-column is not very useful.
> >
> > Dave Polito
> >
> >
> > -----Original Message-----
> > From: John McNally [mailto:jmcnally@collab.net]
> > Sent: Wednesday, April 25, 2001 9:33 AM
> > To: turbine-dev@jakarta.apache.org
> > Subject: Re: Why DBOracle.java not support supportsNativeOffset().
> >
> >
> > you can. just post the results of cvs diff -u when you have it working.
> >
> > john mcnally
> >
> > wuym wrote:
> > >
> > > Hi, all
> > >
> > > I know Oracle support SQL syntax like this:
> > > SQL SELECT blah from foo where id = 1 and rownum <= 10;
> > > like Mysql's limit syntax.
> > >
> > > who can patch the DBOracle.java and BasePeer.java get support Oracle's
> > > limit syntax???
> > >
> > > Eddie wu
> > >
> > > --
> > > wuym <wu...@163.com>
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
> > > For additional commands, e-mail: turbine-dev-help@jakarta.apache.org
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: turbine-dev-help@jakarta.apache.org
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: turbine-dev-help@jakarta.apache.org
>
> --
> wuym <wu...@163.com>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: turbine-dev-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-dev-help@jakarta.apache.org