[SaiLee]

Hi. I have a JAVA program which is designed to be used in restaurants for booking reservations and managing "gourmet members" and such. The program hangs when my connection to the DB is slow and unreliable which is quite a problem. The program also hangs when adding records into the system. I have read about the Java Thread API and know a little bit, but I best learn by example. I read it would be quite simple to extend threading from a class where all the time consuming processes take place. Luckily all my interaction with the Oracle DB is in its own class already. Below is some sample code, I would like to show you. I don't really know where to begin when trying to implement threading, would it be possible to help me with one method and i'll learn hopefully learn how to do it for the rest. Thanks!

 
public ReservationList allReservations() throws SQLException, ClassNotFoundException {
        String queryString;
        ReservationList Reservations;
        queryString = "SELECT * FROM RESERVATIONS ORDER BY GOURMET_ID ASC";
        pstmt = conn.prepareStatement(queryString);
        rset = pstmt.executeQuery();
        Reservations = new ReservationList(rset);
        return Reservations;
    }

    public void addMember(int gourmetID, String password, String homeNumber,
            String postcode, String firstName,
            String surname, String email, String mobileNo) throws SQLException {
        try {
            Statement st = conn.createStatement();
            String sql = "INSERT INTO GOURMET VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            PreparedStatement prest = conn.prepareStatement(sql);
            prest.setInt(1, gourmetID);
            prest.setString(2, password);
            prest.setString(3, homeNumber);
            prest.setString(4, postcode);
            prest.setString(5, firstName);
            prest.setString(6, surname);
            prest.setString(7, email);
            prest.setString(8, mobileNo);
            int rows = prest.executeUpdate();
            conn.close();
        } catch (NullPointerException e) {
            System.out.println(e.getMessage());
        }
    }

[pbl]

I would rather investigate why your connection to the database is slow or why it takes long time to perform a SQL operation.

Multi threading will just slow down your operations as the database engine will have to synchronize the different threads access.

Also how many users are connected to your database ?

[SaiLee]

Its really fast when I'm on the same network as the actual server where the DB is stored. When i'm away from campus and have to VPN in, things get quite sluggish

[pbl]

So it's your connection that is slow
Multiple threads sharing that connection will make things even worst

How many users on your database ?

[g00se]

Yes, you don't want potentially thread-intensive operations going on in the gui thread. Use a SwingWorker and do your db stuff in doInBackground

[SaiLee]

The database is stored on a server which serves many databases which users access. My own individual DB is only used by 5 people. g00se could you give more details on SwingWorker and doInBackground? Thanks

[g00se]

http://docs.oracle.c...wingWorker.html is probably all you need to know

[blackcompe]

Quote

g00se could you give more details on SwingWorker and doInBackground?

I would hold off on *manual* threading. As pbl said, it may be the database connection. Furthermore, as pbl mentioned, you'll probably have to deal with concurrency issues if you access the database in parallel. Perhaps you could try sending a raw query to the server via SSH or Telnet.

First, I'd trying sending a raw query to see what the response time was like. There's no reason to modify your code for nothing.

Second, I'd put my database code in a swing worker. I'd like to think that database access is quick enough not to cause noticeable latencies in your UI, but you never know. Furthermore, mixing Swing and JDBC code is not a good practice.

Third, I'd try adding connection pooling. Constantly creating database connections is quite costly, and if your application hangs under the use of multiple users (or even one user), this may be why.

This post has been edited by blackcompe: 04 March 2012 - 02:59 PM

[g00se]

Quote

I would hold off on the threading.

Quote

Second, I'd put my database code in a swing worker.

Well, SwingWorker is threading It just makes synchronization with the gui thread easier

[pbl]

Are your 5 users at the same physical location ? (Restaurant I guess)

[blackcompe]

Quote

Well, SwingWorker is threading It just makes synchronization with the gui thread easier

Yeah. I meant manual threading. Thanks for clarifying though.

This post has been edited by blackcompe: 04 March 2012 - 02:58 PM