You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@wicket.apache.org by dtoffe <dt...@yahoo.com.ar> on 2007/08/23 03:01:26 UTC
Question regarding old post (ResultSet and DataTable)
Hi !!
I've found this old thread:
http://www.nabble.com/RE%3A-DataView-%28extensions%29-tf1287013.html#a3423281
I hope this link works OK, just in case I quote here the relevant part:
> I have come up with my own subclass of DataTable and implementation of
> IDataProvider to display any arbitrary database java.sql.ResultSet.
-----
> The code is actually quite compact; when I'm finished I'll post it to the
> list if there is any interest.
I'm trying to fill a DataTable with the ResultSet obtained from
executing a query:
cs = con.prepareCall("{ call Region_SelectAll }");
rs = cs.executeQuery();
After I while I discovered that there is no easy or evident way of
filling the DataTable from a ResultSet, I have to provide custom
DataProviders which implement IDataProvider. In case there is some work done
on a generic approach, even if incomplete or undocumented, I'm interested in
taking a look at it.
If there are new or better ways to do this that came up after this
thread, I'm also interested in knowing. Having a way of easily handling the
database resultset and a list of the resultset headers (column titles) would
be great, the more generic, the better.
Thanks !!
Daniel
--
View this message in context: http://www.nabble.com/Question-regarding-old-post-%28ResultSet-and-DataTable%29-tf4314874.html#a12285785
Sent from the Wicket - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@wicket.apache.org
For additional commands, e-mail: users-help@wicket.apache.org
Re: Question regarding old post (ResultSet and DataTable)
Posted by dtoffe <dt...@yahoo.com.ar>.
Hi !!!
Thanks for your reply, this solution is simple, easy to understand and
is not hard to code at all. My only objection would be the MyBean class, if
I understood it right, the DataProvider is tied to the bean in a way that
makes it not reusable:
String query = "SELECT name, age FROM mybean LIMIT " + start + "," +
count;
----
bean.setName(set.getString("name"));
bean.setAge(set.getInt("age"));
What I mean is, what happens if I want to query another table, do I have
to change the ResultSetDataProvider implementation, or provide another
implementation for each table ??
I'm not sure if is it even possible to do what I want, surely it would
be more complex and hard to do, but if it saves from creating custom code
for each query, I guess it would justify the extra work.
In most web usages, this implementation is ok because you don't have
that much database queries, but at my work we have been asked to port a
desktop application to a web implementation, and it uses lots of queries
with flexible filtering criteria, for example (Visual Basic pseudocode):
recordset1 = ExecuteQuery("SELECT a, b, c FROM table1");
datagrid1.setColumns("a", "b", "c");
datagrid1.setRecordSet(recordset1);
datagrid1.refresh();
Perhaps this simplified Visual Basic example shows what I'm trying to
do, as you can see, I don't have to cast the recordset to any specific bean
type, it's just a bunch of cells. That's why I'm looking for that kind of
more abstract approach.
Thanks for your help !!!
Daniel
Ayodeji Aladejebi wrote:
>
> create table mybean (id int primary key, name varchar, age int) bla bla
> bla....
>
> //make a model
> class MyBean implements Serializable {
> private String name;
> private int age;
> }
>
> //create your idataprovider
>
> ---------
>
> //i would not know if there is some other more efficient manner but this
> is
> as simple enough
> // and it works with DataView, Paging, DataTable
>
> this should not be too hard, is it? or you need something else
>
> On 8/23/07, dtoffe <dt...@yahoo.com.ar> wrote:
>>
>>
>> Uhh, well... yes, you are right.....
>>
>> Excuse me but I fail to see how that could help to solve the problem
>> I
>> presented, please keep in mind that I'm not native english speaker and
>> perhaps I'm not using the proper words, I'll try to explain that more
>> precisely.
>> ---------
>
--
View this message in context: http://www.nabble.com/Question-regarding-old-post-%28ResultSet-and-DataTable%29-tf4314874.html#a12295661
Sent from the Wicket - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@wicket.apache.org
For additional commands, e-mail: users-help@wicket.apache.org
Re: Question regarding old post (ResultSet and DataTable)
Posted by Ayodeji Aladejebi <al...@gmail.com>.
create table mybean (id int primary key, name varchar, age int) bla bla
bla....
//make a model
class MyBean implements Serializable {
private String name;
private int age;
}
//create your idataprovider
private class ResultSetDataProvider implements IDataProvider
{
private Connection c;
public ResultSetDataProvider (Connection _c){
c = _c;
}
public Iterator iterator(int start, int count) {
String query = "SELECT name, age FROM mybean LIMIT " + start + "," + count;
PrepareStatement pq = c.createStatement(query );
ResultSet set = pq.executeQuery();
//make a list to carry your records via the bean to your datatable
List<MyBean > dataList = new LinkedList<MyBean>();
while(set.next()){
//create your bean for each record, hopefully JDBC 4 should soon be here
MyBean bean = new MyBean();
bean.setName(set.getString("name"));
bean.setAge(set.getInt("age"));
dataList.add(bean);
}
return dataList.iterator();
}
public int size() {
String countQ = "SELECT COUNT(ID) FROM TABLE";
PrepareStatement pq = c.createStatement(countQ);
ResultSet set = pq.executeQuery();
if(set.next())
return set.getInt(1);
else
return 0;
}
public IModel model(Object arg0) {
return new Model((MyBean)arg);
}
}
//i would not know if there is some other more efficient manner but this is
as simple enough
// and it works with DataView, Paging, DataTable
this should not be too hard, is it? or you need something else
On 8/23/07, dtoffe <dt...@yahoo.com.ar> wrote:
>
>
> Uhh, well... yes, you are right.....
>
> Excuse me but I fail to see how that could help to solve the problem I
> presented, please keep in mind that I'm not native english speaker and
> perhaps I'm not using the proper words, I'll try to explain that more
> precisely.
>
> As I said in my previous post, I do my database queries in this way:
>
> ResultSet rs = cs.executeQuery();
>
> In fact, later I'll use a code generator to ease the task, but let's
> assume for simplicity that I get the result in a java.sql.ResultSet. But,
> as
> stated in the Wicket Extension Javadoc, I must create the DataTable
> instances in this way:
>
> DataTable table = new DataTable("datatable", columns, new
> UserProvider(), 10);
>
> Specifically, the third parameter must implement
> wicket.markup.repeater.data.IDataProvider; which ResultSet doesn't
> implement, so I must provide for a means to overcome this.
>
> I didn't intended to mean that ResultSets are more generic that
> DataProviders. When I talked about a "general" way of handling ResultSet I
> tried to mean independently of whether I'm querying a Database Table,
> Stored
> Porcedure, with read-only or read-write cursors, uni or bi-directional,
> and
> so. I'm concerned about how well this will go in regard of sortable and
> pageable tables.
>
> Perhaps there is a mean to do all that already in the Wicket library,
> but I havent found it yet. In the examples I've seen so far the "database"
> is represented by some kind of static list, like in the ContactsDatabase
> and
> ContactGenerator classes in the DataTable example, but I'm looking for a
> way
> of using data from queries to a database engine.
>
> The way I see it, I'll have to develop a class which could perhaps
> extends ResultSet, or one of the RowSet implementations, and implements
> the
> IDataProvider interface, am I right on this one ??
>
> Thanks for your help !!
>
> Daniel
>
>
>
> igor.vaynberg wrote:
> >
> > actually idataprovider is more generic then resultset :)
> >
> > -igor
> >
> >
> > On 8/22/07, dtoffe <dt...@yahoo.com.ar> wrote:
> >>
> >> Hi !!
> >>
> >> I've found this old thread:
> >>
> >>
> http://www.nabble.com/RE%3A-DataView-%28extensions%29-tf1287013.html#a3423281
> >>
> >> If there are new or better ways to do this that came up after this
> >> thread, I'm also interested in knowing. Having a way of easily handling
> >> the database resultset and a list of the resultset headers (column
> >> titles) would be great, the more generic, the better.
> >>
> >> Thanks !!
> >>
> >> Daniel
> >>
> >
>
> --
> View this message in context:
> http://www.nabble.com/Question-regarding-old-post-%28ResultSet-and-DataTable%29-tf4314874.html#a12287761
> Sent from the Wicket - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@wicket.apache.org
> For additional commands, e-mail: users-help@wicket.apache.org
>
>
Re: Question regarding old post (ResultSet and DataTable)
Posted by dtoffe <dt...@yahoo.com.ar>.
Hi Ayodeji !
I fully agree with you specially if you have few tables and adhere
totally to ORM. But consider what happens when yo have to fill a DataTable
with the result of three+ tables joined, with subselects, group by and
dinamic where clauses...
Would you consider correct to define one Bean-based DataProvider for
each one of such resultsets ?? They are not domain entities, they are just
rows !!
I always had some problems with this side of ORM, sometimes you don't
retrieve a list of entities, sometimes the result is more like a
spreadsheet, and you don't have an easy way of dealing with that based on
beans.
What happens if you call a Stored Procedure and you just don't know how
many columns are returned ?? This is the problem I would like to solve.
Of course I want to do it "the Wicket way", to be able to enjoy all the
benefits already provided by the framework.
I don't want to avoid IDataProvider, I just want to be less tied to the
idea of retrieving beans from the database. This issue troubled me since the
very first time I heard about ORM.
Of course all your concepts regarding the size of the resultset etc. are
completely valid and they must be dealt with.
I'm sorry I haven't had time yet to explore the code you sent me, I'll
see it over the weekend and post it again next week.
Thanks for your help !!
Daniel
Ayodeji Aladejebi wrote:
>
> i think its proper to have a Dataprovider for each table. why i enjoy
> dataprovider in wicket is that it gives me this feeling that SELECT * FROM
> TABLE will not return 1 million records and crash my application, its
> gives
> you a simple way to fetch the data you want to display per time and you
> can
> create a many dataprovider as possible or even implement some internal
> session state logic to tell IDataprovider which table to fetch. its up to
> your implementation.
>
> you will also loose the flexibility you have with yur models if you avoid
> IDataProvider
>
> however actually i think i makes sense for someone to donate a robust
> JDBCDataProvider to wicket stuff
> because attempting to do this for criteria matter, you can only hope you
> are
> not dealing with complex data types
>
>
--
View this message in context: http://www.nabble.com/Question-regarding-old-post-%28ResultSet-and-DataTable%29-tf4314874.html#a12306193
Sent from the Wicket - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@wicket.apache.org
For additional commands, e-mail: users-help@wicket.apache.org
Re: Question regarding old post (ResultSet and DataTable)
Posted by Ayodeji Aladejebi <al...@gmail.com>.
i think its proper to have a Dataprovider for each table. why i enjoy
dataprovider in wicket is that it gives me this feeling that SELECT * FROM
TABLE will not return 1 million records and crash my application, its gives
you a simple way to fetch the data you want to display per time and you can
create a many dataprovider as possible or even implement some internal
session state logic to tell IDataprovider which table to fetch. its up to
your implementation.
you will also loose the flexibility you have with yur models if you avoid
IDataProvider
however actually i think i makes sense for someone to donate a robust
JDBCDataProvider to wicket stuff
because attempting to do this for criteria matter, you can only hope you are
not dealing with complex data types
private class JDBCQueryDataProvider implements IDataProvider
{
private Connection c;
private String query;
private String[] columns;
public ResultSetDataProvider (Connection _c){
c = _c;
}
public ResultSetDataProvider (Connection _c, Query _q){
c = _c;
q = _q;
}
public void setColumnsToFetch(String ... cols){
columns = cols;
}
public void setQuery(String query){this.query = query;}
public String getQuery(){return this.query;}
public Iterator iterator(int start, int count) {
String compiledquery = getQuery + " LIMIT " + start + "," + count;
PrepareStatement pq = c.createStatement(compiledquery );
ResultSet set = pq.executeQuery();
List<List<String>> dataList = new LinkedList<List<String>>();
while(set.next()){
List<String> rowList = new LinkedList<String>();
//iterate over column array
for(columns)
rowList.add(set.getString(column));
//i think the only hell here is the conversions, null to string, timestamp
to string, :)
dataList.add(rowList);
}
return dataList.iterator();
}
public int size() {
String countQ = "SELECT COUNT(ID) FROM TABLE";
PrepareStatement pq = c.createStatement(countQ);
ResultSet set = pq.executeQuery();
if(set.next())
return set.getInt(1);
else
return 0;
}
public IModel model(Object arg0) {
return new Model((List)arg);
}
}
On 8/23/07, dtoffe <dt...@yahoo.com.ar> wrote:
>
>
> Done, I've offered to write some example of use for the wiki or
> example
> page.
>
> Thanks !!
>
> Daniel
>
>
> igor.vaynberg wrote:
> >
> > perhaps you can email frank and ask him, it is unfortunate he did not
> post
> > his code on a wiki page somewhere.
> >
> >
> http://www.nabble.com/displaying-java.sql.Timestamp-tf1333211.html#a3561689
> >
> > -igor
> >
> >
>
> --
> View this message in context:
> http://www.nabble.com/Question-regarding-old-post-%28ResultSet-and-DataTable%29-tf4314874.html#a12297009
> Sent from the Wicket - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@wicket.apache.org
> For additional commands, e-mail: users-help@wicket.apache.org
>
>
Re: Question regarding old post (ResultSet and DataTable)
Posted by dtoffe <dt...@yahoo.com.ar>.
Done, I've offered to write some example of use for the wiki or example
page.
Thanks !!
Daniel
igor.vaynberg wrote:
>
> perhaps you can email frank and ask him, it is unfortunate he did not post
> his code on a wiki page somewhere.
>
> http://www.nabble.com/displaying-java.sql.Timestamp-tf1333211.html#a3561689
>
> -igor
>
>
--
View this message in context: http://www.nabble.com/Question-regarding-old-post-%28ResultSet-and-DataTable%29-tf4314874.html#a12297009
Sent from the Wicket - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@wicket.apache.org
For additional commands, e-mail: users-help@wicket.apache.org
Re: Question regarding old post (ResultSet and DataTable)
Posted by Igor Vaynberg <ig...@gmail.com>.
perhaps you can email frank and ask him, it is unfortunate he did not post
his code on a wiki page somewhere.
http://www.nabble.com/displaying-java.sql.Timestamp-tf1333211.html#a3561689
-igor
On 8/22/07, dtoffe <dt...@yahoo.com.ar> wrote:
>
>
> Uhh, well... yes, you are right.....
>
> Excuse me but I fail to see how that could help to solve the problem I
> presented, please keep in mind that I'm not native english speaker and
> perhaps I'm not using the proper words, I'll try to explain that more
> precisely.
>
> As I said in my previous post, I do my database queries in this way:
>
> ResultSet rs = cs.executeQuery();
>
> In fact, later I'll use a code generator to ease the task, but let's
> assume for simplicity that I get the result in a java.sql.ResultSet. But,
> as
> stated in the Wicket Extension Javadoc, I must create the DataTable
> instances in this way:
>
> DataTable table = new DataTable("datatable", columns, new
> UserProvider(), 10);
>
> Specifically, the third parameter must implement
> wicket.markup.repeater.data.IDataProvider; which ResultSet doesn't
> implement, so I must provide for a means to overcome this.
>
> I didn't intended to mean that ResultSets are more generic that
> DataProviders. When I talked about a "general" way of handling ResultSet I
> tried to mean independently of whether I'm querying a Database Table,
> Stored
> Porcedure, with read-only or read-write cursors, uni or bi-directional,
> and
> so. I'm concerned about how well this will go in regard of sortable and
> pageable tables.
>
> Perhaps there is a mean to do all that already in the Wicket library,
> but I havent found it yet. In the examples I've seen so far the "database"
> is represented by some kind of static list, like in the ContactsDatabase
> and
> ContactGenerator classes in the DataTable example, but I'm looking for a
> way
> of using data from queries to a database engine.
>
> The way I see it, I'll have to develop a class which could perhaps
> extends ResultSet, or one of the RowSet implementations, and implements
> the
> IDataProvider interface, am I right on this one ??
>
> Thanks for your help !!
>
> Daniel
>
>
>
> igor.vaynberg wrote:
> >
> > actually idataprovider is more generic then resultset :)
> >
> > -igor
> >
> >
> > On 8/22/07, dtoffe <dt...@yahoo.com.ar> wrote:
> >>
> >> Hi !!
> >>
> >> I've found this old thread:
> >>
> >>
> http://www.nabble.com/RE%3A-DataView-%28extensions%29-tf1287013.html#a3423281
> >>
> >> If there are new or better ways to do this that came up after this
> >> thread, I'm also interested in knowing. Having a way of easily handling
> >> the database resultset and a list of the resultset headers (column
> >> titles) would be great, the more generic, the better.
> >>
> >> Thanks !!
> >>
> >> Daniel
> >>
> >
>
> --
> View this message in context:
> http://www.nabble.com/Question-regarding-old-post-%28ResultSet-and-DataTable%29-tf4314874.html#a12287761
> Sent from the Wicket - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@wicket.apache.org
> For additional commands, e-mail: users-help@wicket.apache.org
>
>
Re: Question regarding old post (ResultSet and DataTable)
Posted by dtoffe <dt...@yahoo.com.ar>.
Uhh, well... yes, you are right.....
Excuse me but I fail to see how that could help to solve the problem I
presented, please keep in mind that I'm not native english speaker and
perhaps I'm not using the proper words, I'll try to explain that more
precisely.
As I said in my previous post, I do my database queries in this way:
ResultSet rs = cs.executeQuery();
In fact, later I'll use a code generator to ease the task, but let's
assume for simplicity that I get the result in a java.sql.ResultSet. But, as
stated in the Wicket Extension Javadoc, I must create the DataTable
instances in this way:
DataTable table = new DataTable("datatable", columns, new
UserProvider(), 10);
Specifically, the third parameter must implement
wicket.markup.repeater.data.IDataProvider; which ResultSet doesn't
implement, so I must provide for a means to overcome this.
I didn't intended to mean that ResultSets are more generic that
DataProviders. When I talked about a "general" way of handling ResultSet I
tried to mean independently of whether I'm querying a Database Table, Stored
Porcedure, with read-only or read-write cursors, uni or bi-directional, and
so. I'm concerned about how well this will go in regard of sortable and
pageable tables.
Perhaps there is a mean to do all that already in the Wicket library,
but I havent found it yet. In the examples I've seen so far the "database"
is represented by some kind of static list, like in the ContactsDatabase and
ContactGenerator classes in the DataTable example, but I'm looking for a way
of using data from queries to a database engine.
The way I see it, I'll have to develop a class which could perhaps
extends ResultSet, or one of the RowSet implementations, and implements the
IDataProvider interface, am I right on this one ??
Thanks for your help !!
Daniel
igor.vaynberg wrote:
>
> actually idataprovider is more generic then resultset :)
>
> -igor
>
>
> On 8/22/07, dtoffe <dt...@yahoo.com.ar> wrote:
>>
>> Hi !!
>>
>> I've found this old thread:
>>
>> http://www.nabble.com/RE%3A-DataView-%28extensions%29-tf1287013.html#a3423281
>>
>> If there are new or better ways to do this that came up after this
>> thread, I'm also interested in knowing. Having a way of easily handling
>> the database resultset and a list of the resultset headers (column
>> titles) would be great, the more generic, the better.
>>
>> Thanks !!
>>
>> Daniel
>>
>
--
View this message in context: http://www.nabble.com/Question-regarding-old-post-%28ResultSet-and-DataTable%29-tf4314874.html#a12287761
Sent from the Wicket - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@wicket.apache.org
For additional commands, e-mail: users-help@wicket.apache.org
Re: Question regarding old post (ResultSet and DataTable)
Posted by Igor Vaynberg <ig...@gmail.com>.
actually idataprovider is more generic then resultset :)
-igor
On 8/22/07, dtoffe <dt...@yahoo.com.ar> wrote:
>
>
> Hi !!
>
> I've found this old thread:
>
>
>
> http://www.nabble.com/RE%3A-DataView-%28extensions%29-tf1287013.html#a3423281
>
> I hope this link works OK, just in case I quote here the relevant
> part:
>
> > I have come up with my own subclass of DataTable and implementation of
> > IDataProvider to display any arbitrary database java.sql.ResultSet.
> -----
> > The code is actually quite compact; when I'm finished I'll post it to
> the
> > list if there is any interest.
>
> I'm trying to fill a DataTable with the ResultSet obtained from
> executing a query:
>
> cs = con.prepareCall("{ call Region_SelectAll }");
> rs = cs.executeQuery();
>
> After I while I discovered that there is no easy or evident way of
> filling the DataTable from a ResultSet, I have to provide custom
> DataProviders which implement IDataProvider. In case there is some work
> done
> on a generic approach, even if incomplete or undocumented, I'm interested
> in
> taking a look at it.
> If there are new or better ways to do this that came up after this
> thread, I'm also interested in knowing. Having a way of easily handling
> the
> database resultset and a list of the resultset headers (column titles)
> would
> be great, the more generic, the better.
>
> Thanks !!
>
> Daniel
>
> --
> View this message in context:
> http://www.nabble.com/Question-regarding-old-post-%28ResultSet-and-DataTable%29-tf4314874.html#a12285785
> Sent from the Wicket - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@wicket.apache.org
> For additional commands, e-mail: users-help@wicket.apache.org
>
>