partial resultset in java

This looks like an issue for helper threads. In Eclipse, this is easy (unfortunately, Eclipse in general is not very easy). One creates a Job and runs the query in it with frequent checks for "cancel" (e.g., at the beginning of the "for loop". The Job can be shown in a dialog with a progress indicator or it can be run in the background. In either case, a "cancel" button is available.

Threading is also available in "standard" Java, but I am not as familiar with how to set this up.

Michael Schmidt

----- Original Message -----
From: surabhi.ahuja<mailto:surabhi.ahuja@iiitb.ac.in>
To: pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>
Sent: Tuesday, March 21, 2006 8:53 PM
Subject: [GENERAL] partial resultset in java

hi,
i have a small question
I have a table, with one of the fields called x (x is not the primary key, here)

now i have to make a query like this:
select * from table where x = 1;

the number of rows retrieved from the above query is of the order of 20,000 or more.

the result is contained in a resultset.

i have copy pasted the code as follows:
ResultSet rs = stmt.executeQuery(qString); //qstring is " select * from table where x = some_value;"
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();
int k = 0;
while (rs.next()) {
vals = new HashMap();
for (int j = 0; j < colCount; j++) {
vals.put(rsmd.getColumnName(j + 1), rs.getObject(j + 1));
}
objList.add(k++, new DBObject(qLevel, vals, this));
}

as u can say i take each of the rows and do some processing, to form some objects from that row. And then i add those object to the objList.

the above peice of code is executed when the user select some particular value of x,

and if the number of rows is as large as 20k, its taking a lot of time.
since it takes time to get all the 20k objects a busy cursor comes.(in the front end)

What the user is expectiing here is that, if he/she realizes that they selected the wrong x, they want to cancel that query here, and proceed by selecting some other x(in the front end).

My question is that, how can i do it? is there any way of cancelling the query in between?

or is it possible to get partial results so that it may be cancelled or something.

Any info in this regard will be valuable.

Thanks,
regards
Surabhi

I believe you should restrict number of rows that needs to be returned, or
giving a choice to the user, although showing the total count. Even if you
display all 20K records, no one is going to see them all, you can even add
one more LIKE condition to match the user's criteria.

I have only one condition that is x = some value, so the sql query will look like select * from table where x = some value.

But what can i do to "restrict the number of rows".
how do i do that?

thanks,
regards
Surabhi

________________________________

From: Luckys [mailto:plpgsql@gmail.com]
Sent: Wed 3/22/2006 11:23 AM
To: Michael Schmidt
Cc: surabhi.ahuja; PostgreSQL General
Subject: Re: [GENERAL] partial resultset in java

***********************
Your mail has been scanned by InterScan VirusWall.
***********-***********

I believe you should restrict number of rows that needs to be returned, or giving a choice to the user, although showing the total count. Even if you display all 20K records, no one is going to see them all, you can even add one more LIKE condition to match the user's criteria.

On 3/22/06, Michael Schmidt <michaelmschmidt@msn.com> wrote:

This looks like an issue for helper threads. In Eclipse, this is easy (unfortunately, Eclipse in general is not very easy). One creates a Job and runs the query in it with frequent checks for "cancel" (e.g ., at the beginning of the "for loop". The Job can be shown in a dialog with a progress indicator or it can be run in the background. In either case, a "cancel" button is available.

Threading is also available in "standard" Java, but I am not as familiar with how to set this up.

Michael Schmidt

----- Original Message -----

From: surabhi.ahuja <mailto:surabhi.ahuja@iiitb.ac.in>
To: pgsql-general@postgresql.org
Sent: Tuesday, March 21, 2006 8:53 PM
Subject: [GENERAL] partial resultset in java

hi,
i have a small question
I have a table, with one of the fields called x (x is not the primary key, here)

now i have to make a query like this:
select * from table where x = 1;

the number of rows retrieved from the above query is of the order of 20,000 or more.

the result is contained in a resultset.

i have copy pasted the code as follows:
ResultSet rs = stmt.executeQuery(qString); //qstring is " select * from table where x = some_value;"
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();
int k = 0;
while (rs.next()) {
vals = new HashMap();
for (int j = 0; j < colCount; j++) {
vals.put(rsmd.getColumnName(j + 1), rs.getObject (j + 1));
}
objList.add(k++, new DBObject(qLevel, vals, this));
}

as u can say i take each of the rows and do some processing, to form some objects from that row. And then i add those object to the objList.

the above peice of code is executed when the user select some particular value of x,

and if the number of rows is as large as 20k, its taking a lot of time.
since it takes time to get all the 20k objects a busy cursor comes.(in the front end)

What the user is expectiing here is that, if he/she realizes that they selected the wrong x, they want to cancel that query here, and proceed by selecting some other x(in the front end).

My question is that, how can i do it? is there any way of cancelling the query in between?

or is it possible to get partial results so that it may be cancelled or something.

Any info in this regard will be valuable.

Thanks,
regards
Surabhi

On 3/22/06, surabhi.ahuja <surabhi.ahuja@iiitb.ac.in> wrote:

I have only one condition that is x = some value, so the sql query will
look like select * from table where x = some value.

But what can i do to "restrict the number of rows".
how do i do that?

SELECT * FROM table WHERE x = 'bla' LIMIT 200 (Rows to be fetched)
You can check the LIMIT and OFFSET clause in the SELECT statement.
Again, the user would definately have further filter condition after
retrieving 20K records, which you can deal before hand as I mentioned
earlier.

There is a method called cancel() in interface Statement. But I don't know
if it is
implemented correctly in JDBC. Maybe you can try it.

Regards,
William ZHANG

In article <b80d582c0603212153p654d0f06t4ddfec76ada2bfce@mail.gmail.com>,
Luckys <plpgsql@gmail.com> writes:

I believe you should restrict number of rows that needs to be returned, or
giving a choice to the user, although showing the total count. Even if you
display all 20K records, no one is going to see them all,  you can even add
one more LIKE condition to match the user's criteria.

I second that. Whenever you connect an interactive application to a
DB backend and you don't know in advance some upper limit for the
number of rows returned by a query, append a "LIMIT 1000" or something
like that.

on which object is this method "cancel()" avaialble.
the peice of code that i am looking at right now is :

Statement stmt_ = this.dbSession_.getDBConnection().createStatement();
String queryStr = dbxQuery.createQuery(DBObject.getLevelNumber(qLevel), q, this);
long t = System.currentTimeMillis();
rs = stmt_.executeQuery(queryStr);
this.dbSession_.logger_.log(Level.FINE, "GOT RESULT SET;"
+ (System.currentTimeMillis() - t));

ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();
t = System.currentTimeMillis();
while (rs.next()) {
vals = new HashMap();
for (int j = 0; j < colCount; j++) {
vals.put(rsmd.getColumnName(j + 1), rs.getObject(j + 1));
}
objList.add(new DBObject(qLevel, vals, this.dbSession_));
}
this.dbSession_.logger_.log(Level.FINE, "CONVERT RESULTSET TO DBOBJECT;"
+ (System.currentTimeMillis() - t));

where exactly will the cancel be placed?
thanks,
regards
Surabhi

________________________________

From: pgsql-general-owner@postgresql.org on behalf of William ZHANG
Sent: Wed 3/22/2006 4:27 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] partial resultset in java

***********************
Your mail has been scanned by InterScan VirusWall.
***********-***********

There is a method called cancel() in interface Statement. But I don't know
if it is
implemented correctly in JDBC. Maybe you can try it.

Regards,
William ZHANG

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] partial resultset in javaI have not install pgsql's jdbc. But
I think the following link may help.

Regards,
William ZHANG

Sorry. Forgot the link:

http://www.onjava.com/pub/a/onjava/2004/06/16/dbcancel.html?page=2

"William ZHANG" <uniware@zedware.org>

Include a "Limit" in your query.
Michael Schmidt