i am new to java programming and not good enough.
we are required to make one class which creates and manages connection to database instead of all my servlet.

i cud come up wtih the following code. please assist in what i have done wrong .thanks

My connection file.

/**
 *DAO.java
 *Establishes connection to database
 */
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;

public class DAO
{   private final String DRIVER="com.mysql.jdbc.Driver";//JDBC driver name
    private static final String DATABASE_URL="jdbc:mysql://localhost/travelfares";//Database URL
    private static final String USERNAME="root";//Database user name
    private  static final String PASSWORD ="******";//Database password
    private Connection connection=null;//manages connection
    private Statement statement;


        //constructor
	public DAO(String DRIVER,String DATABASE_URL,String USERNAME,String PASSWORD)
            throws SQLException ,ClassNotFoundException
        {
            Class.forName(DRIVER);//loads the driver class
            //establish database connection
            connection=DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD);

            //create statement to query database
            statement =connection.createStatement();
            
         }//end of constructor
        public void createConnection(String DRIVER,String DATABASE_URL,String USERNAME,String PASSWORD)
            throws SQLException ,ClassNotFoundException
        {
            Class.forName(DRIVER);//loads the driver class
            //establish database connection
            connection=DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD);

            //create statement to query database
            statement =connection.createStatement();
        }

        public Connection getConnection()
        {return connection;}

        public ResultSet setQuery(String query)
            throws SQLException,IllegalStateException
        {
            statement.executeQuery(query);
            ResultSet result = statement.getResultSet();
            return result;
        }

        public void closeConnection()
        {
            try
            {   connection.close ();
                statement.close ();
            }
            catch(Exception e)
            {
                System.out.println("Exception is ;"+e);
            }
        }

}// end of class DAO
/*
 * FareDisplay.java
 *
 */
import java.io.*;
import java.sql.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;


public class FareDisplay extends HttpServlet
{
    private DAO dao;
    String webpage="FareDisplay.jsp";
  
    final String QUERY="SELECT zoneId,peak,offPeak,cashSingleFare FROM ticket";

    public void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException
    {
        PrintWriter out = response.getWriter();
          List dataList = new ArrayList();
        try
        {   dao.getConnection();
            ResultSet rs=dao.setQuery(QUERY);
            while (rs.next ())
            {
                dataList.add(rs.getString("zId"));
                dataList.add(rs.getString("p"));
                dataList.add(rs.getString("oP"));
                dataList.add(rs.getString("CSFare"));

            }
            dao.closeConnection();
            request.setAttribute("data",dataList);
            //Disptching request
             RequestDispatcher dispatcher = request.getRequestDispatcher(webpage);
             if (dispatcher != null)
             {
                 dispatcher.forward(request, response);
             }
        }
      /*  catch(ClassNotFoundException classNotFound)
        {
            System.out.println(classNotFound);
        }*/
        catch(SQLException SqlException)
        {
            System.out.print(SqlException);
        }
        finally {
            out.close();
        }
    }
}

1) You don't initialize the DAO object, you only declare it so it is null.
2) The DAO requires only 1 constructor with no arguments. Because everything that is needed for the opening of the connection are declared inside it.

3) Don't do that:

dataList.add(rs.getString("zId"));
                dataList.add(rs.getString("p"));
                dataList.add(rs.getString("oP"));
                dataList.add(rs.getString("CSFare"));

Simply create a class with those as attributes: zId, p, ... and instantiate it:

Ticket tick = new Ticket(rs.getString("zId"), .....);
dataList.add(tick) ;

Also since your query is like this:
"SELECT zoneId,peak,offPeak,cashSingleFare FROM ticket"
You should be doing this: rs.getString("zoneId") 4) Close the ResultSet.
5) Don't close the out writer. You don't even use it, so you don't even need to call it.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.