[SOLVED] Checking database connectivity in JSP

Checking database connectivity in JSP

How can I check the database connectivity in JSP. I want to print an error message if there is any problem occurs with the database connectivity.

I m using the following code:

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost;databaseName=dbname;user=username;password=password";
Connection conn = DriverManager.getConnection(connectionUrl); 
Statement stmt = conn.createStatement();

After successfull connection, I want to insert data to the database. I also want to check the whether the data is inserted properly. Can anyoone help me on this...

Solution

The JSP is the wrong place for this. You need to create a standalone class which does the JDBC job and let each of the methods throw an exception whenever the SQL stuff fails.

Here's an example of a "DAO" class which does all the JDBC stuff on the User table:

public class UserDAO {

    public User find(String username, String password) throws SQLException {
        // ...
    }

    public void save(User user) throws SQLException {
        // ...
    }

    public void delete(User user) throws SQLException {
        // ...
    }

}

Then, create a servlet which uses this class and handles the exception. Here's an example of a LoginServlet:

@WebServlet(urlPatterns={"/login"})
public class LoginServlet extends HttpServlet {

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String username = request.getParameter("username");
        String password = request.getParameter("password");
        UserDAO userDAO = new UserDAO();

        try {
            User user = userDAO.find(username, password);

            if (user != null) {
                request.getSession().setAttribute("user", user); // Login.
                response.sendRedirect("userhome");
            } else {
                request.setAttribute("message", "Unknown login, try again"); // Set error message.
                request.getRequestDispatcher("/WEB-INF/login.jsp").forward(request, response); // Redisplay form with error.
            }
        } catch (SQLException e) {
            throw new ServletException("Fatal database failure", e); // <-- Here
        }
    }

}

Let JSP submit to this servlet

<form action="login" method="post">
    <input type="text" name="username" />
    <input type="password" name="password" />
    <input type="submit" />
    ${message}
</form>

You see, when the DAO class throws an SQLException, the servlet rethrows it as ServletException. It will by default end up in a container-default HTTP 500 error page. You can if necessary customize this with a JSP in your own look'n'feel as follows

<error-page>
    <error-code>500</error-code>
    <location>/error.jsp</location>
</error-page>

Checking database connectivity in JSP

See also: