Error with connection in my database servlet

Question

Hello, I am writing a Database servlet, all seems well except that there seems to be an error in my connection

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class DBServlet3 extends HttpServlet
{
    private static final long serialVersionUID = 1L;

    @Override
    public void init() throws ServletException
    {
        super.init();
        try
        {
            String jdbcDriverClass= 
                getServletContext().getInitParameter( "jdbcDriverClass" );
            if (jdbcDriverClass == null)
                throw new ServletException( "Could not find jdbcDriverClass initialization parameter" );
            Class.forName( jdbcDriverClass );
        }
        catch (ClassNotFoundException e)
        {
            throw new ServletException( "Could not load JDBC driver class", e );
        }
    }

    @Override
    protected void doGet( HttpServletRequest request, HttpServletResponse response )
        throws ServletException, IOException
    {
        RequestDispatcher dispatcher=
            request.getRequestDispatcher( "/db.jsp" );

        ServletContext application= getServletContext();

        ArrayList<String> names= new ArrayList<String>();

        try
        {

            Connection connection= null;
            Statement statement= null;
            ResultSet results= null;

            try
            {
                String jdbcUrl= application.getInitParameter( "jdbcUrl" );
                String jdbcUser= application.getInitParameter( "jdbcUser" );
                String jdbcPassword= application.getInitParameter( "jdbcPassword" );

                connection=
                    DriverManager.getConnection( jdbcUrl, jdbcUser, jdbcPassword );

                statement= connection.createStatement();

                results= statement.executeQuery( "SELECT * FROM students" );

                while (results.next())
                {
                    String name= results.getString( "name" );
                    names.add( name );
                }
            }
            finally
            {
                if (results != null)
                    results.close();
                if (statement != null)
                    statement.close();
                if (connection != null)
                    connection.close();
            }
        }
        catch (SQLException e)
        {
            throw new ServletException( e );
        }

        request.setAttribute( "names", names );

        dispatcher.forward( request, response );
    }

    @Override
    protected void doPost( HttpServletRequest request, HttpServletResponse response )
        throws ServletException, IOException
    {
        String sql= "INSERT INTO students VALUES (" +
            request.getParameter( "id" ) + ", '" + request.getParameter( "name" ) + "')";

        sql= "INSERT INTO students VALUES (?, ?, ?, ?)";

        PreparedStatement statement= connection.prepareStatement( sql ); //error on this line

        statement.setString( 1, request.getParameter( "id" ) );
        statement.setString( 2, request.getParameter( "name" ) );
    }

}

Answer 1

I'm not at all clear what the specific problem is, and I would edit to add a stacktrace. However, some observations.

1. you're getting and closing a connection in your doGet() method, but you don't have one in your doPost() method. So that will result in an error. You should get your connection in a consistent fashion per request, and perhaps (further on) check out connection pooling frameworks such as C3P0 or Apache DBCP.
2. rather than form SQL from building strings, check out PreparedStatements. They will lead to less error-prone code and protect you from SQL injection attacks.
3. you have a serialVersionUid in your servlet. Do you really need to serialise your servlet (I suspect not) ?
4. Apache DbUtils will do a lot of heavy lifting for you wrt. vanilla JDBC (e.g. it'll look after the resultset/statement/connection closing sequence).