using JDBC in servlets

Question

i am really new to the servlet concepts. just googled out some theories, studied and now i want a practical experience. all i want to do is to create a servlet that can access a mysql database. a simple JSP page that accepts a 'name' and 'marks' and then hit the servlet which in-turn access the database and enters the name and marks into the database.

i Have created a database and a JSP page. i have created a servlet too. i have the mysql connector in my library and i am referring to it with Class.forname() concept. but when i run this on my server (Tomcat v6.0) it gives a class not found exception for 'com.mysql.jdbc.Driver'. But when i run this mysql part in another project which is not a servlet (its just a simple java project) it executes properly and the database is also updated.

the problem is only when i use that piece of code on the server with a servlet. i tried googling and it said i have to include the mysql connector in the WEB-INF/lib of the tomcat. But i could not find this path at all. any advice regarding this would prove helpful to me. thanks in advance. :) this is my mysql part of the code, protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    PrintWriter out = response.getWriter();
    String name = request.getParameter("name");

    int marks = Integer.parseInt(request.getParameter("marks"));

    try {
        System.out.println("0");
        Class.forName("com.mysql.jdbc.Driver");

        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/testdb123","root","root");

        String qr= "INSERT INTO stuinfo " +
                   "VALUES ('"+name+"',10)";
        System.out.println(qr);

        System.out.println("1");
        Statement stmt = conn.createStatement();
        System.out.println("2");
        stmt.executeUpdate(qr);
        System.out.println("3");

    } catch (Exception e) {
        e.printStackTrace();
    }

}

Nikos Paraskevopoulos Nikos Paraskevopoulos · Accepted Answer · 2013-09-23T11:08:18

Servlet containers like Tomcat run WAR (Web ARchive) files or folders. The standard folder structure of a WAR is:

(ROOT)
|
+- (content files and folders)
|
+- META-INF (optional)
|  |
|  +- MANIFEST.MF (standard Java manifest file, optional)
|  |
|  +- (other files and folders, optional)
|
+- WEB-INF (Tomcat considers it optional for ease of use)
   |
   +- web.xml (the deployment descriptor)
   |
   +- classes (folder that contains all the classes for this web application)
   |
   +- lib (contains all library JARs for this application THIS IS WHAT YOU WANT)
   |
   +- (other files and folders, optional)

Stuff in WEB-INF/classes and JARs in WEB-INF/lib are automatically included in the classpath of your application.

In your application folder, you should create a WEB-INF folder, inside it a lib folder and place the mysql-connector-XXX.jar in there. This will solve your classpath problems. The servlet container does not automatically create the folder structure.

ALTERNATIVELY, you can copy the mysql-connector-XXX.jar driver to the lib folder of Tomcat. This will make it available to all applications.

Having said that, please consider the following as well:

Always close connections in a finally block:

Connection conn = null;
try {
    ...
    conn = // get it somehow
}
catch( /* any exception you can handle, throw or let go of the rest */ ) {
    ...
}
finally {
    // close other JDBC resources (e.g. Statements, ResultSets etc) in a similar way
    if( conn != null ) {
        try { conn.close(); }
        catch(Exception(ignore) {}
    }
}

Using DriverManager.getConnection all the time will hurt performance in real life. After you feel confident with yourself about the basics, take a look at connection pooling and the javax.sql.DataSource. Later, not now!

using JDBC in servlets

1 Answers