Re: looking for opinons regarding best practices (jdbc, resultsets, and servlet design)

From:

"dnass" <dnasmars@gmail.com>

Newsgroups:

comp.lang.java.programmer

Date:

7 Feb 2007 06:47:39 -0800

Message-ID:

<1170859659.076648.35590@m58g2000cwm.googlegroups.com>

On Feb 7, 2:27 pm, "javerra" <tnava...@flipforwardinteractive.com>
wrote:

Hello all,

Im looking for an opinion regarding best practices. Recently a friend
and I were talking about how we write our code for our web
applications. I tend to keep my jdbc code with my logic in any
servlet I am writing. My friend says that this is bad practice and
that data quries should be broken out into data access objects with
methods that pass back a result set. Is he right? Is this really bad
practice or is it really just a different type of design pattern?
Love to hear everyones thoughts...

Im always doing something like this....

            try {
                Connection Conn = DriverManager.getConnection("jdbc:mysql://
sql.useractive.com/USERNAME?user=USERNAME&password=PASSWORD");

                // Do something with the Connection
                Statement Stmt = Conn.createStatement();
                ResultSet RS = Stmt.executeQuery("SELECT * from SOMETABLE");

                while (RS.next()) {
                    out.println(RS.getString(1));
                }
                // Clean up
                RS.close();
                Stmt.close();
                Conn.close();

            }
            catch (SQLException E) {

}

Hello Javera,

You should have a look at MVC model.

And for the code you've written I would have wrote it this way :
Connection Conn = null;
Statement Stmt = null;
ResultSet RS = null;
        try {
                Conn =
DriverManager.getConnection("jdbc:mysql://
sql.useractive.com/USERNAME?user=USERNAME&password=PASSWORD");

                // Do something with the Connection
                Stmt = Conn.createStatement();
                RS = Stmt.executeQuery("SELECT * from SOMETABLE");

                while (RS.next()) {
                    out.println(RS.getString(1));
                }

            }
            catch (SQLException E) {
                 // handle the exception
            }
            finally{
                // Clean up
               try{
                  RS.close();
               }
               catch(Exception ex){}
               RS = null;
               try{
                  RS.close();
               }
               catch(Exception ex){}
               RS = null;
               try{
                Conn.close();
               }
               catch(Exception ex){}
              Conn= null;

            }
because if you have an exception while executing the query
with your source code you'll never close your connection.

I hope this helps
DNass