Java – Separate database connection method for jsp and servlets

javajspservlets

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 ??

Best Answer

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.

Related Topic