10 Replies - 588 Views - Last Post: 04 March 2012 - 02:58 PM Rate Topic: -----

#1 SaiLee  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 19-February 12

Implementing Threading into a Database Connector Class?

Posted 03 March 2012 - 10:47 AM

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());
        }
    }



Is This A Good Question/Topic? 0
  • +

Replies To: Implementing Threading into a Database Connector Class?

#2 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8334
  • View blog
  • Posts: 31,857
  • Joined: 06-March 08

Re: Implementing Threading into a Database Connector Class?

Posted 03 March 2012 - 10:58 AM

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 ?
Was This Post Helpful? 1
  • +
  • -

#3 SaiLee  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 19-February 12

Re: Implementing Threading into a Database Connector Class?

Posted 03 March 2012 - 11:16 AM

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
Was This Post Helpful? 0
  • +
  • -

#4 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8334
  • View blog
  • Posts: 31,857
  • Joined: 06-March 08

Re: Implementing Threading into a Database Connector Class?

Posted 03 March 2012 - 11:24 AM

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

How many users on your database ?
Was This Post Helpful? 0
  • +
  • -

#5 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2734
  • View blog
  • Posts: 11,513
  • Joined: 20-September 08

Re: Implementing Threading into a Database Connector Class?

Posted 03 March 2012 - 11:45 AM

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
Was This Post Helpful? 0
  • +
  • -

#6 SaiLee  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 19-February 12

Re: Implementing Threading into a Database Connector Class?

Posted 04 March 2012 - 05:17 AM

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
Was This Post Helpful? 0
  • +
  • -

#7 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2734
  • View blog
  • Posts: 11,513
  • Joined: 20-September 08

Re: Implementing Threading into a Database Connector Class?

Posted 04 March 2012 - 05:48 AM

http://docs.oracle.c...wingWorker.html is probably all you need to know
Was This Post Helpful? 0
  • +
  • -

#8 blackcompe  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1155
  • View blog
  • Posts: 2,534
  • Joined: 05-May 05

Re: Implementing Threading into a Database Connector Class?

Posted 04 March 2012 - 02:48 PM

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

Was This Post Helpful? 1
  • +
  • -

#9 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2734
  • View blog
  • Posts: 11,513
  • Joined: 20-September 08

Re: Implementing Threading into a Database Connector Class?

Posted 04 March 2012 - 02:52 PM

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
Was This Post Helpful? 2
  • +
  • -

#10 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8334
  • View blog
  • Posts: 31,857
  • Joined: 06-March 08

Re: Implementing Threading into a Database Connector Class?

Posted 04 March 2012 - 02:56 PM

Are your 5 users at the same physical location ? (Restaurant I guess)
Was This Post Helpful? 0
  • +
  • -

#11 blackcompe  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1155
  • View blog
  • Posts: 2,534
  • Joined: 05-May 05

Re: Implementing Threading into a Database Connector Class?

Posted 04 March 2012 - 02:58 PM

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

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1