CRUD DAO

/*
 *ConnectionManager
 *
 *
 *Version:1.0
 *
 *Date:25-Nov-2011
 *
 */
package com.student.dao;

import java.sql.*;
import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;

public class ConnectionManager {

    static Connection con;
    static String url;
    static final Logger logger = Logger.getLogger(ConnectionManager.class);

    public static Connection getConnection() {
        PropertyConfigurator.configure("log4j.properties");
        try {
            String url = "jdbc:oracle:thin:@172.24.137.30:1521:ora10g";
            // assuming "DataSource" is your DataSource name

            Class.forName("oracle.jdbc.OracleDriver");

            try {
                con = DriverManager.getConnection(url, "e533121", "project1");

                // assuming your SQL Server's username is "username"
                // and password is "password"

            }

            catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

        catch (ClassNotFoundException e) {// "logger" prints in to a file;
            // Normally used to trace the
            // process

            logger.error(e);
        }

        return con;
    }
}



createid:
/*
 *CollegeRegistrationDao
 *
 *
 *Version:1.0
 *
 *Date:31-Nov-2011
 *
 */
package com.student.dao;

import java.sql.*;

import com.student.beans.*;
import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;

public class CreateIdDAO {
    static Connection currentCon = null;
    static ResultSet rs = null;
    static PreparedStatement pstmt = null;
    static ResultSet rs1 = null;
    static PreparedStatement pstmt1 = null;
    static final Logger logger = Logger.getLogger(CreateIdDAO.class);

    public static StudentBean register(StudentBean bean) {
        /**
         * method to search student
         * 
         * @533026,533121
         * 
         */
        // preparing some objects for connection
        Statement stmt = null;
        PropertyConfigurator.configure("log4j.properties");
        try {
            // connect to DataBase
            // connect to DataBase
            currentCon = ConnectionManager.getConnection();
            stmt = currentCon.createStatement();
            String getMax = "select Max(std_id) from studentids";
            rs1 = stmt.executeQuery(getMax);
            rs1.next();
            int maxId = rs1.getInt(1);
            System.out.println(maxId);
            int nextId = maxId + 1;
            String stdId = "S" + Integer.toString(nextId);
            String query1 = "insert into studentids values(?)";
            pstmt1 = currentCon.prepareStatement(query1);
            pstmt1.setInt(1, nextId);
            pstmt1.executeUpdate();
            bean.setStudentId(stdId);
        } catch (Exception ex) {// "logger" prints in to a file; Normally used
            // to trace the process

logger.error("Registration failed: An Exception has occurred! "
+ ex);
}

// exception handling
finally {
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
}
rs = null;
}

if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {
}
stmt = null;
}

if (currentCon != null) {
try {
currentCon.close();
} catch (Exception e) {
}

currentCon = null;
}
}

return bean;


}

}

registration:

try {
            // connect to DataBase
            // connect to DataBase
            currentCon = ConnectionManager.getConnection();
            String stdId=bean.getStudentId();
            System.out.println(stdId);
            // query for inserting into the table
            String query = "insert into newstudent(std_id,std_name,maths,social,hindi,science,english) values(?,?,?,?,?,?,?)";
            pstmt = currentCon.prepareStatement(query);
            // inserting values
            pstmt.setString(1,stdId);
            pstmt.setString(2,bean.getStudentName());
            pstmt.setInt(3, bean.getMaths());
            pstmt.setInt(4, bean.getSocial());
            pstmt.setInt(5, bean.getHindi());
            pstmt.setInt(6, bean.getScience());
            pstmt.setInt(7, bean.getEnglish());
            pstmt.executeUpdate();
            bean.setValid(true);

        }

delete: try {

        String std = bean.getStudentId();
        PropertyConfigurator.configure("log4j.properties");
        String searchQuery = "delete from newstudent where std_id='"+ std + "'";
        bean.setValid(true);

        // // "logger" prints in to a file; Normally used to trace the process

        logger.info("Your employee id is " + std);

        logger.info("Query: " + searchQuery);

        }

        catch (Exception ex) {
            logger.error("Log In failed: An Exception has occurred! " + ex);
        }

search:

Statement stmt = null;

        String username = bean.getStudentId();

        String searchQuery = "select * from newstudent where std_id ='"
                + username + "' ";

        // "logger" prints in to a file; Normally used to trace the process

        logger.info("Student ID is " + username);
        logger.info("Query: " + searchQuery);

        try {
            // connect to DB
            currentCon = ConnectionManager.getConnection();
            stmt = currentCon.createStatement();
            rs = stmt.executeQuery(searchQuery);
            boolean more = rs.next();

            // if user does not exist set the isValid variable to false
            if (!more) {
                logger
                        .warn("Sorry, you are not a registered user! Please sign up first");
                bean.setValid(false);
            }

            // if user exists set the isValid variable to true
            else if (more) {
                String studentId = rs.getString("std_id");
                String studentName = rs.getString("std_NAME");
                int maths = rs.getInt("maths");
                int social = rs.getInt("social");
                int hindi = rs.getInt("hindi");
                int science = rs.getInt("science");
                int english = rs.getInt("english");
                bean.setStudentId(studentId);
                bean.setStudentName(studentName);
                bean.setMaths(maths);
                bean.setSocial(social);
                bean.setHindi(hindi);
                bean.setScience(science);
                bean.setEnglish(english);
                bean.setValid(true);
                int total=maths+social+hindi+science+english;
                if(total>=450){
                    bean.setGrade("A+");
                }
                if(total>=400 && total<=449){
                    bean.setGrade("A");
                }
                if(total>=350 && total<=399){
                    bean.setGrade("B");
                }
                if(total>=300 && total<=349){
                    bean.setGrade("C");
                }
                if(total>=250 && total<=299){
                    bean.setGrade("D");
                }
                if(total>=200 && total<=249){
                    bean.setGrade("E");
                }
                if(total<200){
                    bean.setGrade("Fail");
                }
                }

        }

profile and update:
Statement stmt = null;

        String username = bean.getStudentId();
        // query to select row from College using College_Id
        String searchQuery = "select * from newstudent where std_Id='"
                + username + "' ";
        // "logger" prints in to a file; Normally used to trace the process
        logger.info("Your user name is " + username);
        logger.info("Query: " + searchQuery);

        try {
            // connect to DataBase
            currentCon = ConnectionManager.getConnection();
            stmt = currentCon.createStatement();
            rs = stmt.executeQuery(searchQuery);
            boolean more = rs.next();
            // check for rows
            if (more) {

                String studentId = rs.getString("std_id");
                String studentName = rs.getString("std_NAME");
                int maths = rs.getInt("maths");
                int social = rs.getInt("social");
                int hindi = rs.getInt("hindi");
                int science = rs.getInt("science");
                int english = rs.getInt("english");
                bean.setStudentId(studentId);
                bean.setStudentName(studentName);
                bean.setMaths(maths);
                bean.setSocial(social);
                bean.setHindi(hindi);
                bean.setScience(science);
                bean.setEnglish(english);
                bean.setValid(true);

            } else {
                bean.setValid(false);
            }
        }

        catch (Exception ex) {
            logger.error("Log In failed: An Exception has occurred! " + ex);
        }


public static StudentBean register(StudentBean bean) {
        Statement stmt = null;
        /**
         * method to search student
         * 
         * @533026
         * 
         */
        // preparing some objects for connection
        /*
         * String sName = bean.getStudentName(); String searchQuery =
         * "select * from Student where Student_Name='" + sName + "' "; //
         * "System.out.println" prints in the console; Normally used to trace
         * the process System.out.println("Your user name is " + sName);
         * System.out.println("Query: "+searchQuery);
         */
        PropertyConfigurator.configure("log4j.properties");
        try {
            // connect to DataBase
            currentCon = ConnectionManager.getConnection();

            // if user does not exist set the isValid variable to false

            // query for inserting into the table
            String sid = bean.getStudentId();
            String sn = bean.getStudentName();
            int ma = bean.getMaths();
            int sc = bean.getSocial();
            int hi = bean.getHindi();
            int si = bean.getScience();
            int eng = bean.getEnglish();

            String query = "update newstudent set std_name='" + sn + "',maths=" + ma + ",social="
                    + sc + ",hindi=" + hi + ",science=" + si + ",english=" + eng + " where std_id='" + sid + "'";
            // "logger" prints in to a file; Normally used to trace the process
            logger.info(query);
            pstmt = currentCon.prepareStatement(query);
            pstmt.executeUpdate();

        } catch (Exception ex) {
            logger.error("Log In failed: An Exception has occurred! " + ex);
        }

/*
 *ConnectionManager
 *
 *
 *Version:1.0
 *
 *Date:25-Nov-2011
 *
 */
package com.student.dao;

import java.sql.*;
import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;

public class ConnectionManager {

    static Connection con;
    static String url;
    static final Logger logger = Logger.getLogger(ConnectionManager.class);

    public static Connection getConnection() {
        PropertyConfigurator.configure("log4j.properties");
        try {
            String url = "jdbc:oracle:thin:@172.24.137.30:1521:ora10g";
            // assuming "DataSource" is your DataSource name

            Class.forName("oracle.jdbc.OracleDriver");

            try {
                con = DriverManager.getConnection(url, "e533121", "project1");

                // assuming your SQL Server's username is "username"
                // and password is "password"

            }

            catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

        catch (ClassNotFoundException e) {// "logger" prints in to a file;
            // Normally used to trace the
            // process

            logger.error(e);
        }

        return con;
    }
}



createid:
/*
 *CollegeRegistrationDao
 *
 *
 *Version:1.0
 *
 *Date:31-Nov-2011
 *
 */
package com.student.dao;

import java.sql.*;

import com.student.beans.*;
import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;

public class CreateIdDAO {
    static Connection currentCon = null;
    static ResultSet rs = null;
    static PreparedStatement pstmt = null;
    static ResultSet rs1 = null;
    static PreparedStatement pstmt1 = null;
    static final Logger logger = Logger.getLogger(CreateIdDAO.class);

    public static StudentBean register(StudentBean bean) {
        /**
         * method to search student
         * 
         * @533026,533121
         * 
         */
        // preparing some objects for connection
        Statement stmt = null;
        PropertyConfigurator.configure("log4j.properties");
        try {
            // connect to DataBase
            // connect to DataBase
            currentCon = ConnectionManager.getConnection();
            stmt = currentCon.createStatement();
            String getMax = "select Max(std_id) from studentids";
            rs1 = stmt.executeQuery(getMax);
            rs1.next();
            int maxId = rs1.getInt(1);
            System.out.println(maxId);
            int nextId = maxId + 1;
            String stdId = "S" + Integer.toString(nextId);
            String query1 = "insert into studentids values(?)";
            pstmt1 = currentCon.prepareStatement(query1);
            pstmt1.setInt(1, nextId);
            pstmt1.executeUpdate();
            bean.setStudentId(stdId);
        } catch (Exception ex) {// "logger" prints in to a file; Normally used
            // to trace the process

logger.error("Registration failed: An Exception has occurred! "
+ ex);
}

// exception handling
finally {
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
}
rs = null;
}

if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {
}
stmt = null;
}

if (currentCon != null) {
try {
currentCon.close();
} catch (Exception e) {
}

currentCon = null;
}
}

return bean;


}

}


registration:

try {
            // connect to DataBase
            // connect to DataBase
            currentCon = ConnectionManager.getConnection();
            String stdId=bean.getStudentId();
            System.out.println(stdId);
            // query for inserting into the table
            String query = "insert into newstudent(std_id,std_name,maths,social,hindi,science,english) values(?,?,?,?,?,?,?)";
            pstmt = currentCon.prepareStatement(query);
            // inserting values
            pstmt.setString(1,stdId);
            pstmt.setString(2,bean.getStudentName());
            pstmt.setInt(3, bean.getMaths());
            pstmt.setInt(4, bean.getSocial());
            pstmt.setInt(5, bean.getHindi());
            pstmt.setInt(6, bean.getScience());
            pstmt.setInt(7, bean.getEnglish());
            pstmt.executeUpdate();
            bean.setValid(true);

        }

delete:
try
        {


        String std = bean.getStudentId();
        PropertyConfigurator.configure("log4j.properties");
        String searchQuery = "delete from newstudent where std_id='"+ std + "'";
        bean.setValid(true);

        // // "logger" prints in to a file; Normally used to trace the process

        logger.info("Your employee id is " + std);

        logger.info("Query: " + searchQuery);

        }

        catch (Exception ex) {
            logger.error("Log In failed: An Exception has occurred! " + ex);
        }


search:

Statement stmt = null;

        String username = bean.getStudentId();

        String searchQuery = "select * from newstudent where std_id ='"
                + username + "' ";

        // "logger" prints in to a file; Normally used to trace the process

        logger.info("Student ID is " + username);
        logger.info("Query: " + searchQuery);

        try {
            // connect to DB
            currentCon = ConnectionManager.getConnection();
            stmt = currentCon.createStatement();
            rs = stmt.executeQuery(searchQuery);
            boolean more = rs.next();

            // if user does not exist set the isValid variable to false
            if (!more) {
                logger
                        .warn("Sorry, you are not a registered user! Please sign up first");
                bean.setValid(false);
            }

            // if user exists set the isValid variable to true
            else if (more) {
                String studentId = rs.getString("std_id");
                String studentName = rs.getString("std_NAME");
                int maths = rs.getInt("maths");
                int social = rs.getInt("social");
                int hindi = rs.getInt("hindi");
                int science = rs.getInt("science");
                int english = rs.getInt("english");
                bean.setStudentId(studentId);
                bean.setStudentName(studentName);
                bean.setMaths(maths);
                bean.setSocial(social);
                bean.setHindi(hindi);
                bean.setScience(science);
                bean.setEnglish(english);
                bean.setValid(true);
                int total=maths+social+hindi+science+english;
                if(total>=450){
                    bean.setGrade("A+");
                }
                if(total>=400 && total<=449){
                    bean.setGrade("A");
                }
                if(total>=350 && total<=399){
                    bean.setGrade("B");
                }
                if(total>=300 && total<=349){
                    bean.setGrade("C");
                }
                if(total>=250 && total<=299){
                    bean.setGrade("D");
                }
                if(total>=200 && total<=249){
                    bean.setGrade("E");
                }
                if(total<200){
                    bean.setGrade("Fail");
                }
                }

        }

profile and update:
Statement stmt = null;

        String username = bean.getStudentId();
        // query to select row from College using College_Id
        String searchQuery = "select * from newstudent where std_Id='"
                + username + "' ";
        // "logger" prints in to a file; Normally used to trace the process
        logger.info("Your user name is " + username);
        logger.info("Query: " + searchQuery);

        try {
            // connect to DataBase
            currentCon = ConnectionManager.getConnection();
            stmt = currentCon.createStatement();
            rs = stmt.executeQuery(searchQuery);
            boolean more = rs.next();
            // check for rows
            if (more) {

                String studentId = rs.getString("std_id");
                String studentName = rs.getString("std_NAME");
                int maths = rs.getInt("maths");
                int social = rs.getInt("social");
                int hindi = rs.getInt("hindi");
                int science = rs.getInt("science");
                int english = rs.getInt("english");
                bean.setStudentId(studentId);
                bean.setStudentName(studentName);
                bean.setMaths(maths);
                bean.setSocial(social);
                bean.setHindi(hindi);
                bean.setScience(science);
                bean.setEnglish(english);
                bean.setValid(true);

            } else {
                bean.setValid(false);
            }
        }

        catch (Exception ex) {
            logger.error("Log In failed: An Exception has occurred! " + ex);
        }


public static StudentBean register(StudentBean bean) {
        Statement stmt = null;
        /**
         * method to search student
         * 
         * @533026
         * 
         */
        // preparing some objects for connection
        /*
         * String sName = bean.getStudentName(); String searchQuery =
         * "select * from Student where Student_Name='" + sName + "' "; //
         * "System.out.println" prints in the console; Normally used to trace
         * the process System.out.println("Your user name is " + sName);
         * System.out.println("Query: "+searchQuery);
         */
        PropertyConfigurator.configure("log4j.properties");
        try {
            // connect to DataBase
            currentCon = ConnectionManager.getConnection();

            // if user does not exist set the isValid variable to false

            // query for inserting into the table
            String sid = bean.getStudentId();
            String sn = bean.getStudentName();
            int ma = bean.getMaths();
            int sc = bean.getSocial();
            int hi = bean.getHindi();
            int si = bean.getScience();
            int eng = bean.getEnglish();

            String query = "update newstudent set std_name='" + sn + "',maths=" + ma + ",social="
                    + sc + ",hindi=" + hi + ",science=" + si + ",english=" + eng + " where std_id='" + sid + "'";
            // "logger" prints in to a file; Normally used to trace the process
            logger.info(query);
            pstmt = currentCon.prepareStatement(query);
            pstmt.executeUpdate();

        } catch (Exception ex) {
            logger.error("Log In failed: An Exception has occurred! " + ex);
        }

/*
 *college Registration Bean
 *
 *
 *Version:1.0
 *
 *Date:2-DEC-2011
 *
 */

package com.student.beans;

public class StudentBean {
    /**
     * Bean class for Student details
     * 
     * @author 533026,533121 version1.0 27/11/2011
     */
    // declaring attributes of the student table
    private String studentId;
    private String studentName;
    private int maths;
    private int social;
    private int hindi;
    private int science;
    private int english;
    private String grade;
    public boolean valid;

    // getter method for college id
    public String getStudentId() {
        return studentId;
    }

    // setter method for college id
    public void setStudentId(String newStudentId) {
        studentId = newStudentId;
    }

    // getter method for college name
    public String getStudentName() {
        return studentName;
    }

    // setter method for college name
    public void setStudentName(String newStudentName) {
        studentName = newStudentName;
    }

    // getter method for college authority
    public int getMaths() {
        return maths;
    }

    // setter method for college authority
    public void setMaths(int newMaths) {
        maths= newMaths;
    }

    // getter method for college address
    public int getSocial() {
        return social;
    }

    // setter method for college address
    public void setSocial(int newSocial) {
        social = newSocial;
    }

    // getter method for college address2
    public int getHindi() {
        return hindi;
    }

    // setter method for college address2
    public void setHindi(int newHindi) {
        hindi = newHindi;
    }

    // getter method for college city
    public int getScience() {
        return science;
    }

    // setter method for college city
    public void setScience(int newScience) {
        science = newScience;
    }

    // getter method for college state
    public int getEnglish() {
        return english;
    }

    // setter method for college state
    public void setEnglish(int newEnglish) {
        english = newEnglish;
    }
    // getter method for college state
    public String getGrade() {
        return grade;
    }

    // setter method for college state
    public void setGrade(String newGrade) {
        grade = newGrade;
    }


    // getter method for is valid
    public boolean isValid() {
        return valid;
    }

    // setter method for set valid
    public void setValid(boolean newValid) {
        valid = newValid;
    }

}

package com.student.controllers;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;
import com.student.dao.*;
import com.student.beans.*;
import javax.servlet.http.HttpSession;
import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;

/**
 * Servlet implementation class studentServlet
 */
public class studentServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
    static final Logger logger = Logger.getLogger(studentServlet.class);

    /**
     * Default constructor. 
     */
    public studentServlet() {
        // TODO Auto-generated constructor stub
        PropertyConfigurator.configure("log4j.properties");
        logger.info("Entered Servlet");
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doPost(request,response);
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        try

        {
            HttpSession session = request.getSession(true);
            switch (Integer.parseInt(request.getParameter("name"))) {
            // case for college registration
            case 1:
                // creating object to java beans
                StudentBean createId = new StudentBean();
                createId = CreateIdDAO.register(createId);
                // storing the values in variables
                logger.info("Entered Case 1:Generation of ID");
                session.setAttribute("currentSessionUser", createId);
                String destination = "./jsp/studentReport.jsp";
                response.sendRedirect(response.encodeRedirectURL(destination));
                break;
            case 2:
                // creating object to java beans
                StudentBean registration = (StudentBean)(session.getAttribute("currentSessionUser"));
                // storing the values in variables
                logger.info("Entered Case 1:College Registration");
                registration.getStudentId();
                registration.setStudentName(request.getParameter("sn"));
                registration.setMaths(Integer.parseInt(request.getParameter("ma")));
                registration.setSocial(Integer.parseInt(request.getParameter("so")));
                registration.setHindi(Integer.parseInt(request.getParameter("hi")));
                registration.setScience(Integer.parseInt(request.getParameter("sc")));
                registration.setEnglish(Integer.parseInt(request.getParameter("eng")));
                registration = RegistrationDAO.register(registration);
                session.setAttribute("currentSessionUser", registration);
                String destination1 = "./jsp/collegeReportSuccess.jsp";
                response.sendRedirect(response.encodeRedirectURL(destination1));
                break;
            case 3:
                logger.info("Entered Case 2:College Search");
                StudentBean user = new StudentBean();
                user.setStudentId(request.getParameter("sid"));
                user = StudentSearchDao.search(user);
                if (user.isValid()) {
                    session.setAttribute("currentUser", user);
                    String destination2 = "./jsp/collegeSearchSuccess.jsp";
                    response.sendRedirect(response
                            .encodeRedirectURL(destination2)); // logged-in page
                } else {
                    String destination3 = "./jsp/invalidStudentSearch.jsp";
                    response.sendRedirect(response
                            .encodeRedirectURL(destination3)); // error page
                }
                break;
            case 4:
                logger.info("Entered Case 3:College Deletion");
                StudentBean collegeStudent = new StudentBean();
                collegeStudent.setStudentId(request.getParameter("sid"));

                collegeStudent = DeleteStudentDAO.login(collegeStudent);


                    session.setAttribute("currentSessionUser", collegeStudent);
                    response.sendRedirect("./jsp/collegeDeleteSuccess.jsp"); // logged-in
                    // page

                break;
            case 5:
                logger.info("Entered Case 4:Retreving college details");
                StudentBean studentProfile = new StudentBean();
                studentProfile.setStudentId(request.getParameter("sid"));
                studentProfile = StudentProfileDao.details(studentProfile);
                if (studentProfile.isValid()) {
                    session.setAttribute("current", studentProfile);
                    String destination2 = "./jsp/updateStudentProfile.jsp";
                    response.sendRedirect(response
                            .encodeRedirectURL(destination2));
                } else {
                    String destination2 = "./jsp/InvalidUpdateStudent.jsp";
                    response.sendRedirect(response
                            .encodeRedirectURL(destination2));
                }
                break;
            // case for updationg college details
            case 6:// creating object to java beans
                logger.info("Entered Case 5:Updating college details");
                StudentBean update = (StudentBean)(session.getAttribute("current"));
                // storing the values in variables
                update.setStudentId(update.getStudentId());
                update.setStudentName(request.getParameter("sn"));
                update.setMaths(Integer.parseInt(request.getParameter("ma")));
                update.setSocial(Integer.parseInt(request.getParameter("so")));
                update.setHindi(Integer.parseInt(request.getParameter("hi")));
                update.setScience(Integer.parseInt(request.getParameter("sc")));
                update.setEnglish(Integer.parseInt(request.getParameter("eng")));
                update = StudentUpdateDao.register(update);
                session.setAttribute("currentUpdateUser", update);
                String destination9 = "./jsp/updateStudentSuccess.jsp";
                response.sendRedirect(response.encodeRedirectURL(destination9));
                break;
        }

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