Resultset

[apffal]

I would like move to next or prev records in a Resultset, clicking other listeners bottons.
What the best way to do it ?
Thanks.

There is my code :

class Listener1 implements ActionListener {
public void actionPerformed(ActionEvent ev) {

String url= "jdbc:mysql://127.0.0.1/mydb";
Connection con;

String query = "SELECT * FROM demo WHERE (" + search1 + " LIKE '%" + value1 + "%' AND " + search2 + " LIKE '%" + value2 + "%' AND " + search3 + " LIKE '%" + value3 + "%')";

Statement stmt;

try {
Class.forName("com.mysql.jdbc.Driver");
}
catch (java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}

try {
System.out.println("Trying to connect...");
con = DriverManager.getConnection (url,"apffal", "");

System.out.println("connected!");

stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);

ResultSet result = stmt.executeQuery(query);
result.afterLast();
while (result.previous()) {
String rel = result.getString(1);
String proc = result.getString(2);
String data = result.getString(3);
trl8.t1.setText(rel);
trl8.t2.setText(proc);
trl8.t3.setText(data);
}

stmt.close();
con.close();
}
catch(SQLException ex) {
System.err.print("SQLException: ");
System.err.println(ex.getMessage());
}

}
}

 

[sedj]

You can scroll back and forth through a ResultSet using the ResultSet methods next() and previous() - as long as your JDBC driver supports this (not all do).

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[apffal]

But as I' m using a listener to get the records, I think cannot use those methods in another listener.
How to do it ?

 

[sedj]

Not quite sure what you mean by "listener" in this context ... a listener is usually a programme that attaches itself to a port, and accepts TCP/IP or UDP socket connections from clients. You code does not appear to be exibiting that behaviour ...

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[Diancecht]

I assume he means by listener the ActionListener, a class designed to act in response to an action event.

I'm not sure what is the structure of your application, but I imagine you have some kind of GUI with a "Get info" button and "Next" and "Previous" button.

If that's rigth, I'd use the code you post with the listener to the "Get Info" button. I'd also make the ResultSet visible for other members of the class and display at that moment the info of the first record.

Then you will need two more ActionListeners to the "Next" and "Previous" buttons. Those methods will invoke the next() and previous() method of the ResultSet to get the info and update de GUI.

HTH.

Cheers.

Dian

 

[apffal]

That's right.
I think I can invoke the next() or previous() methods of the ResultSet in other ActionListeners, setting that ResultSet as public and static.
But, when the first listener closes the connection, the ResultSet results are not destroyed, and so the second and third listeners cannot get those results ?

 

[Diancecht]

I don't think that you need to make it static, as long as you're initializing it for each instance of your class.

In the other hand, AFAIK, once the data is retrieved the ResultSet object has nothing to do with the connection.

Cheers.

Dian

 

[sedj]

once the data is retrieved the ResultSet object has nothing to do with the connection.

Not so sure about that one Dian ... JDBC drivers that support scrolling (most do) make network calls to the db to retrieve the cursors - the data is not all downloaded in one call ... try disconnecting your network cable and then call a method in ResultSet - it'll fail.

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[apffal]

So, using my code above, what do you suggest for navigating through ResultSet, to next or previous records ?

 

[sedj]

I would dump the data from the ResultSet into a custom DAO or hashtable or something, and then access the data at your own leisure.



--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[Diancecht]

I included the "AFAIK"

If not, I see two options:

- Store the info of the ResultSet on another variable (poor performance if it's large)

- Closing the connection later, when the user closes the GUI.

Cheers.

Dian

 

[sedj]

I would warn against leaving the Connection object open. You should treat them as a finite resource which you should access as quickly as possible, and return it (prefarably to a pool ) as quickly as possible.

What if you have a lot of users each with several connections open for a long time ? You'll annoy your DBA and users quicker than you can say "damn, I should have closed those connections", just as the db locks up because all connection instances are taken up by all the users that have gone to lunch and left open connections !!!

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[apffal]

I've followed your suggestion and stored data from ResultSet in a list.
Now, I can, using button listeners, access its first and last elements.
However, when clicking next or previous buttons, the list only moves once, to next or previous items.
How can I move it, forward or back, every time I click the button ?
Thanks again.

That's my code (for next button)

class Listener implements ActionListener {
public void actionPerformed(ActionEvent e) {

int x = list1.getItemCount()-1;
int i = 0;
if(x > i){
x--;
String str = list1.getItem(x);
t1.setText(str);
}
}
}

 

[Diancecht]

When talking about performance, everything depends on the characteristics of the system.

If you have a distributed application with many clients, a pool makes sense, especially if you DBA has number of connection constraints.

But imagine this is a management application only run from a few servers that retrieves zillions of data. Maybe getting all the data will end in a problem with network traffic.

If I understand it ok, the code above should be for the Back button. Anyway, you can do that by replacin x-- with x-=offset


Every system is a new world, are we creators?

Dian

 

[apffal]

Don't need to change any more in my code ?