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();
}
}
}