Обсуждение: partial resultset in java

From: surabhi.ahuja

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

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

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

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

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

 

***********************
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