0

I have created a database application using java swing. My program retrieves data from the database whenever I call the find class. The find class simply creates a statement, get the current database connection, then execute the statement. The returned values from the database will be placed on a ResultSet then will be displayed on a jTable. The problem is this:

I opened the find class, the result was displayed. Then I go to SQLyog or HeidiSQL (applications to manipulate the database), edit the values which was displayed on my program, then save. I went back to my program, close the find class then reopen it, I still get the previous data, not the edited one. Please help. Updates must be displayed once the find class is open. The only way for me to get the updated data is to close my entire program then reopen it, which I dont want to do.

EDIT: This is what I tried. Basically, once my program creates a connection to the database the first time, I save it to another class which makes the connection always open(I assume). So whenever I want to create a query, I'll just call the class to get the connection. What I did now is, after executing the query from the find class, I close the connection with the .close() function. It works, but do I really need to do close the connection every time? Again this is just a desktop application, not a web program.

John
  • 836
  • 2
  • 25
  • 39
  • Are you sure that these tools actually commit the changes made to your data? – Luiggi Mendoza Nov 06 '12 at 23:01
  • have you committed your change in the database? – shonky linux user Nov 06 '12 at 23:01
  • Yes. The only way for me to get the updated data is to exit my entire program and reopen it, which I dont want to do. – John Nov 06 '12 at 23:02
  • The find class is just a part of my program. It just searches the values then display the data to the user. The entire program does adding, editing, and deleting values from the database. – John Nov 06 '12 at 23:05
  • It looks like you're not executing the `SELECT` statement when accesing to your `Find` class. How are you opening it? – Luiggi Mendoza Nov 06 '12 at 23:05
  • @LuiggiMendoza The find class runs the select statement. _sqlCmd = "SELECT * FROM " + tableName; ResultSet rs = DBConnection.getActiveConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY).executeQuery(_sqlCmd); – John Nov 06 '12 at 23:08
  • It looks like your `Find` class isn't running the select statement when you reopen it. Please verify this using a debugger or logging messages (`System.out.println` could do it). By the way, JDBC doesn't cache by default. – Luiggi Mendoza Nov 06 '12 at 23:10
  • Yup I checked it using the debugger. I used breakpoints for the two statements written above and the program hit both breakpoints. – John Nov 06 '12 at 23:12
  • Are you sure you get the new data when debugging? If not, make sure the tools you're using commit the changes in the database. Otherwise, see if you're repainting your `JTable` with the new data. To make even sure about this, it would be good to print the results of the `SELECT` statement and compare the results. – Luiggi Mendoza Nov 06 '12 at 23:16
  • This is what I tried. Basically, once my program create a connection to the database the first time, I save it to another class which makes the connection always open(I assume). So whenever I want to create a query, I'll just call the class to get the connection. What I did now is, after executing the query from the find class, I close the connection with the .close() function. It works, but do I really need to do close the connection every time? Again this is just a desktop application, not a web program. – John Nov 06 '12 at 23:23
  • Yes, the connection must be opened and closed for every group of requests you made. I'm aware this is a desktop app. If you want to have better performance on this, you can see related questions: [Connection pooling in Swing based Application](http://stackoverflow.com/q/10409626/1065197), [How to implement connection pooling in core java application](http://java.ittoolbox.com/groups/technical-functional/java-l/how-to-implement-connection-pooling-in-core-java-application-1420094) – Luiggi Mendoza Nov 06 '12 at 23:40

2 Answers2

1

I think you're having a transaction isolation issue. Some drivers start an implicit transaction when it grabs the connection so you're seeing a snapshot of the database at that time. You probably want a READ COMMITTED level which should show the database with all committed transactions applied.

Joshua Martell
  • 7,074
  • 2
  • 30
  • 37
0

Looks like either you have not commit the edit or you need to refresh data by refreshing query.

neo
  • 1,054
  • 1
  • 10
  • 19
  • The commit was made on the SQLyog or HeidiSQL. Im sure about that because I open both of them at the same time. I edit it in HeidiSQL then check if the values were updated using SQLyog and it is updated. But with my program it is not. I also checked whether the find class executes the SELECT statement properly and yes, it did. – John Nov 06 '12 at 23:14