Updating an SQL database between queries?

Question

I had a question about the code I am running. It is meant to update a display based on a value of a variable retrieved from an SQL database. When I run the code and insert another entry to the table the loop printing the values of the table does not change. I am not sure if it is because the pointer in the table has not moved or because the code doesn't allow for dynamic updating. When I terminate code and rerun new data is displayed. I am using eclipse.

import java.awt.Graphics;
import javax.swing.JFrame;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Tutorial1 extends JFrame 
{
    static String State; 
      public Tutorial1()
      {
             setTitle("Tutorial1");
             setSize(900, 900);
             setVisible(true);
             setDefaultCloseOperation(EXIT_ON_CLOSE);
      }

      public void paint(Graphics g)
      {


          if (State.equals("0"))
          {
          g.drawRect(480, 480, 200, 100);
          }

          if (State.equals("1"))
          {
             g.fillRect(240, 240, 200, 100);
          }


      }


       public static void main(String[] args)
       {

           Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            try {
                new com.mysql.jdbc.Driver();
                Class.forName("com.mysql.jdbc.Driver").newInstance();
                    String connectionUrl = "jdbc:mysql://localhost:3306/capstone";
                String connectionUser = "root";
                String connectionPassword = "root";
                conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);
                stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
                rs = stmt.executeQuery("SELECT id,Name,State FROM Sensors ORDER BY id DESC");

                {       

                while (rs.next()) {


                    Tutorial1 t = new Tutorial1();
                    //t.paint(null);

                     while (true) 
                    {

                         rs.refreshRow();
                         rs.updateRow(); 


                         String id = rs.getString("id");
                            String Name = rs.getString("Name");
                             State = rs.getString("State");
                            System.out.println("ID: " + id + ",Name: " + Name
                                    + ", State: " + State);

                            id = "id+1"; 

                        System.out.println("Success");
                        System.out.println(State);

                        t.repaint();

                    }

                }


            }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {




                //try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); }
            //  try { if (stmt != null) stmt.close(); } catch (SQLException e) { e.printStackTrace(); }
                //try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); }
            }            
       } 
}

If you are referring to the while(true) loop not updating the values, then that is the correct behaviour as the results have already been retrieved from the database. Should you need new results from the database, you need to query the db again. — J_D, Apr 22 '17 at 20:17
That `while (true)` look is going to spin like crazy. You really should throttle it or you may have thousands of repaints with stale data queued up. — tadman, Apr 22 '17 at 21:43
@tadman What would be an efficient way to throttle the loop to execute every 10 seconds? — David Camille, Apr 22 '17 at 22:42
[Sleeping](http://stackoverflow.com/questions/24104313/how-to-delay-in-java). — tadman, Apr 22 '17 at 22:42
@J_D I believe every time the "rs." runs it makes a connection to the database — David Camille, Apr 22 '17 at 22:43

PKey · Answer 1 · 2017-04-23T08:23:32.260

So, what your code actually does is the following:

You execute a query with the following statement:

rs = stmt.executeQuery("SELECT id,Name,State FROM Sensors ORDER BY id DESC");

now your resultset holds the result of the query, and the first row in the resultset is the one with the highest id.
Then you go to the first row of the resultset:

while (rs.next())
After that you are constantly refreshing that particular row in an infinite loop:

while (true) { rs.refreshRow(); ...

You never move to the next row (never again rs.next() is called) nor you will ever see any new inserted rows in your table (you are not refreshing the whole resultset - just the current row)

Of course, when you stop your program and run again - query executes, it reads the new values from the table, with the first row having the higher id and so you see the expected result on your screen.

That's why the best thing to do is, just close the result set. And execute a query again - if you want to get new values from the table.

Also, as mentioned by the tadman in comments - you should pause between re-queries by using sleep on the thread.

@DavidCamille I actually noticed something now - which brings me to a question - How many entries are there in your `Sensors` table? Because you are treating it as if it has only **one** entry which is just being constantly updated...If it is not the case then, may be, that is the source of your problem... Also tadman's concern is legit... — PKey, Apr 23 '17 at 06:50

Updating an SQL database between queries?

1 Answers1