Separate database connection method for jsp and servlets

Question

I was trying to write a separate database connection method in .java file which can be called upon by any servlet or jsp file needing the database connection. My code is

import java.sql.*;
import java.lang.*;

public class ConnectionClass {

private String username="root";
private String password="passwd";

/* Adjust the above two as per the username
 * password combination of your MySql databse */

public Connection connect()
{
    try
    {
        Class.forName("com.mysql.jdbc.Driver");  
        String url="jdbc:mysql://localhost/schooldatabase";
        Connection con = DriverManager.getConnection(url,username,password);
        return con;
    }

    catch(Exception e)
    {
         response.sendRedirect("studentserr.html");
         out.println(e);
    }        
 }
}

Now, the problem is that i'll be returning a Connection type so that all the servlets (which require database connection) may use it to execute various statements. However, in my code what should i return in the catch block ( which means that the connection to the database could not be established) ? Also, in case of a connection failure, I'm redirecting the user to the following page:

"studentserr.html"

This works fine if i use it in a servlet but not in .java class. What should i do for this ??

You may want to re-consider the use of sendRedirect. See for example http://stackoverflow.com/questions/2047122/requestdispatcher-interface-vs-sendredirect — Sridhar, Jun 24 '12 at 11:07

score 2 · Answer 1 · answered Jun 25 '12 at 14:24

You should only be catching exceptions at exactly that moment where you can sensibly deal with them. You can't sensibly deal with them in the getConnection() method, so you should instead throw it so that the caller itself needs to deal with it.

Displaying an error page in case of a specific exception is however the responsibility of the servlet container itself. You normally configure the error page in web.xml as follows:

<error-page>
    <exception-type>java.sql.SQLException</exception-type>
    <location>/WEB-INF/errorpages/database.jsp</location>
</error-page>

You only need to change your code accordingly that you never catch the exception, or at least rethrow as ServletException where necessary.

Here's a minor rewrite:

public class Database {

    private String url = "jdbc:mysql://localhost/schooldatabase";
    private String username = "root";
    private String password = "passwd";

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver"); // You don't need to load it on every single opened connection.
        } catch (ClassNotFoundException) {
            throw new ExceptionInInitializerError("MySQL JDBC driver missing in classpath", e);
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

}

And here's how you should use it in your DAO classes:

public List<Student> list() throws SQLException {
    List<Student> students = new ArrayList<Student>();
    Connection connection = null;
    // ...

    try {
        connection = Database.getConnection();
        // ...
    } finally { // Note: no catch block!
        // ...
        if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
    }

    return students;
}

And here is how you should use the DAO class in your servlet's doGet() or doPost().

try {
    List<Student> students = studentDAO.list();
    request.setAttribute("students", students);
    request.getRequestDispatcher("/WEB-INF/students.jsp").forward(request, response);
} catch (SQLException e) {
    throw new ServletException(e);
}

It has to be rethrown as ServletException simply because you can't add SQLException to the throws clause of any HttpServlet method. The servletcontainer will unwrap the SQLException while locating the error page.

score 1 · Answer 2 · edited May 23 '17 at 12:16

Return a null value in case an exception is thrown. In case of getting a null value from your method, handle the exception and don't perform any database operation.

Also, remember to separate the database logic (connection, statement execution) from the business logic and the presentation. In your actual method, this code

response.sendRedirect("studentserr.html");

should never be in the database logic, because is presentation logic.

More info:

score 0 · Answer 3 · answered Jun 25 '12 at 07:17

Try moving class "ConnectionClass" into some package. and then call using this package in your java class where you require this (seems to be classpath issue in java file) or in jsp or servlet page. Example You need this in Demo.java as pkg1.ConnectionClass obj = new pkg1.ConnectionClass ();

It is recommended to have database connection class as singleton. So that through out the application only a single instance of connection will be created and shared.

Separate database connection method for jsp and servlets

3 Answers3