JSP: Is this a proper MySQL class?

Question

I'm having a problem with MySQL in my small JSP project. I have asked a question about this earlier (http://stackoverflow.com/questions/9860129/jsp-mysql-jar-file-stops-working) and this is a follow up question based on the info form that question.

The class works and the page fetches info from the database for a few days, then it stops working until I either restart the Tomcat server or deploy the war file again.

Here is my MySQL connection class, any ideas would be much appreciated:

public class Connect {
    final String host = "localhost";
    final String username = "admin";
    final String password = "admin";   
    String connectionURL = "jdbc:mysql://" + host + ":3306/db?user=;password=";
    Connection connection = null;
    Statement statement = null;

    public Connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = (Connection) DriverManager.getConnection(connectionURL, username, password);
            statement = (Statement) connection.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(Connect.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Connect.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(Connect.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(Connect.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ResultSet executeQuery(String query) {
            ResultSet result = null;
            try {
                    if (statement != null) {
                            result = statement.executeQuery(query);

                    } else {
                            System.out.println("Null");
                    }
        } catch (SQLException ex) {
                Logger.getLogger(Connect.class.getName()).log(Level.SEVERE, null, ex);
        }
            return result;
    }
    public boolean execute(String query) {
            System.out.println(query);
            boolean result = false;
            try {
                    if (statement != null) {
                            result = statement.execute(query);
                    } else {
                            System.out.println("Null");
                    }
        } catch (SQLException ex) {
                Logger.getLogger(Connect.class.getName()).log(Level.SEVERE, null, ex);
        }
            return result;
    }

Your indentation is horrible. – ThiefMaster Apr 08 '12 at 20:30 — ThiefMaster, Apr 08 '12 at 20:30

score 1 · Answer 1 · answered Apr 08 '12 at 15:07

1

Looks like you never close out the connections, so after a few days the connection pool and/or your MySQL server has run out of free connections.

Are you creating a new Connect class for every query, or re-using the same one (how are you handling multi-threading if so?)

answered Apr 08 '12 at 15:07

Chris White

29,949
4
71
93

I'm using the same one. I created a closeResultSet() and closeConnection() (which also closes the statement) methods in the class which I call each time it has finished receiving/sending info from/to the db. Do you think that will do the trick? – user1163278 Apr 08 '12 at 15:13
It will probably work, but you should look into not using the same Connect object between sessions (you're going to run into some issues sooner or later). Can you share your JSP code that utilizes this Connect object? I would also look into JSTL SQL Tags - http://www.ibm.com/developerworks/library/j-jstlsql/ – Chris White Apr 08 '12 at 15:41
So it's best to create a new object of the Connect class each time an operation is performed? – user1163278 Apr 08 '12 at 16:08
I would say so yes, and to improve performance, you should look into connection pooling – Chris White Apr 08 '12 at 17:15

score 1 · Accepted Answer · answered Apr 08 '12 at 20:29

    public class Connect {
            final String host = "localhost";
            final String username = "admin";
            final String password = "admin";   
            String connectionURL = "jdbc:mysql://" + host + ":3306/db?user=;password=";
            Connection connection = null;
            Statement statement = null;

    public Connect() {
    try {
           Class.forName("com.mysql.jdbc.Driver").newInstance();
           connection = (Connection) DriverManager.getConnection(connectionURL,      username, password);

          } catch (Exception ex) {
                    out.println("Exception is: " +ex)
                    } 
            }

            public ResultSet executeQuery(String query) {
                    ResultSet result = null;
                    try {

                            statement=(Statement)connection.createStatement();
                            result = statement.executeQuery(query);
                            if(result>0){
                                 result=true;
                               }else{
                                   result=false;
                                  } 
                         } catch (SQLException ex) {
                           Logger.getLogger(Connect.class.getName()).log(Level.SEVERE, null, ex);
                         }finally{
                         if(statement!=null){try{statement.close;}catch(SQLException logOrIgnore){}}
                          if(connection!=null){try{connection.close;}catch(SQLException logOrIgnore){}}
        }
                    return result;
            }
            public boolean execute(String query) {
                    System.out.println(query);
                    boolean result = false;
                    try {

                           statement=(Statement)connection.createStatement();
                           result = statement.executeQuery(query);
                          if(result>0){
                              result=true;
                           }else{
                               result=false;
                           }
                       } catch (SQLException ex) {
                            Logger.getLogger(Connect.class.getName()).log(Level.SEVERE, null, ex);
                           }finally{
                             if(statement!=null){try{statement.close;}catch(SQLException logOrIgnore){}}
                            if(connection!=null){try{connection.close;}catch(SQLException logOrIgnore){}}
        }
                         return result;
      }

     }

and as cited by @Chris White connection pooling is preferable since it allows several users to utilize one connection to the database. — mykey, Apr 08 '12 at 20:52
Yes, I'm hoping to be able to look into later. For now, this will have to do. It's a very small website. — user1163278, Apr 08 '12 at 22:03
Is this code pooling or can you direct me to a place where I can read up on that? — user1163278, Apr 08 '12 at 22:05
Sure, done and done. I'm going to use that code. But, that's not pooling, right? I'd like to learn that for future purposes, do you know of a good tutorial/example? — user1163278, Apr 08 '12 at 22:37
thanks. No it is not pooling. I use netbeans IDE and i recommend this tutorial for creating a resource and a connection pool http://weblogs.java.net/blog/gsporar/archive/2005/10/creating_a_data.html — mykey, Apr 08 '12 at 22:42
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/9836/discussion-between-mykey-and-user1163278) — mykey, Apr 08 '12 at 22:47

JSP: Is this a proper MySQL class?

2 Answers2