0

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();
        }
    }
}
2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by javaAddict
0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.