Checking database connectivity in JSP

Question

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

You should not do the database connection management from the JSPs but from a lower level, JSP should be primary for presentation. — Waldheinz, Jun 01 '11 at 12:07
Hi Waldheinz I m new to JSP. So I don't know the coding standards. I will try to learn about servlet — Dijo David, Jun 01 '11 at 12:11

score 3 · Accepted Answer · edited May 23 '17 at 12:04

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>

    try {
            conn = java.sql.DriverManager.getConnection(connectionUrl);
            System.out.println("Connection established");
            //--- Do operation on database.
    }
    catch (Exception e) {
            System.out.println(e);
            System.out.println("Connection not established");
    }

Try to avoid this operation in jsp better to do database connection in servlet.

answered Jun 01 '11 at 12:07

Harry Joy

58,650
30
162
207

@Dijo: are you using tomcat? if yes then see log files in log folder. – Harry Joy Jun 01 '11 at 12:54
Ok. I will check the log file, – Dijo David Jun 01 '11 at 12:59
@Dijo: You will either see `Connection established` if connection is successful or see `Connection not established` followed by exception stack trace if connection is not successful. – Harry Joy Jun 01 '11 at 13:00
An error occurred at line: 11 in the jsp file: /index_en.jsp Syntax error, insert "Finally" to complete TryStatement – Dijo David Jun 01 '11 at 13:05
@Dijo: show me your code. I can not assume what is at line 11 of your jsp page. – Harry Joy Jun 01 '11 at 13:10
@Harry Joy thaks for supporting me. I have created a new java file and write methods to connect db in it. Included the class file and called the method in JSP. Its working fine now.. – Dijo David Jun 03 '11 at 12:41

Ramesh PVK · Answer 3 · 2011-06-01T12:23:11.830

1

Avoid scriplets in jsp, use jstl sql library inside jsp's. Sample code goes here

<c:catch var ="catchException">
The exception will be thrown inside the catch:<br>
<sql:setDataSource var="dataSource" driver="com.mysql.jdbc.Driver" url="jdbc:mysql//localhost/datasouce" user="admin" password="passowrd"/>
<sql:query var="ids" dataSource="${dataSource}">SELECT * FROM table</sql:query>
</c:catch>
<c:if test = "${catchException!=null}">The exception is : ${catchException}<br><br>There is an exception: ${catchException.message}<br></c:if>

edited Jun 01 '11 at 12:23

answered Jun 01 '11 at 12:14

Ramesh PVK

15,200
2
46
50

Actually, JSTL SQL (and XML) taglibs should also be avoided. – BalusC Jun 01 '11 at 12:52
@BaluC Any specific reasons. Anyways, i was just telling the way to test db connections in a jsp. But, if one has such requirement , this is the only way to do right. Otherwise use custom tags, which is also similar to JSTL tags right? please correct me! – Ramesh PVK Jun 01 '11 at 12:57

score 0 · Answer 4 · answered Jun 05 '13 at 20:04

use try catch and if-else statement to check whether the data insert properly and use rollback if there is error during the insert transaction.

Connection conn = null;
Statement stmt = null;

try{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost;databaseName=dbname;user=username;password=password";
conn = DriverManager.getConnection(connectionUrl); 
conn.setAutoCommit(false);
stmt = conn.createStatement();
stmt.executeUpdate(sql);
stmt.executeUpdate(sql);
stmt.executeUpdate(sql);
stmt.executeUpdate(sql);

conn.commit();
}
catch(Exception e) {
if(!conn.isClosed()){
conn.rollback();
}
System.out.println(e);
}
finally{
if(!stmt.isClosed()){
stmt.close();
}
if(!conn.isClosed()){
conn.close();
}
}

or try to use JSTL to make it easier Watch JSF Framework Tutorials

score 0 · Answer 5 · edited May 23 '17 at 12:26

0

How can I check the database connectivity in JSP.

The better design would be to check it at app deploy time and share the connection from applicationContext.

You can also use connection-pooling.

and yes don't write java code in JSP

See Also

why-business-logic-should-be-moved-out-of-jsp

edited May 23 '17 at 12:26

Community

1
1

answered Jun 01 '11 at 12:12

jmj

237,923
42
401
438

Checking database connectivity in JSP

5 Answers5

See also: