Dao

package studentinformation.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import studentinformation.model.ClassInfo;
import studentinformation.model.StudentInfo;

public class Dao {

public static Connection getConnection()throws SQLException, ClassNotFoundException{

		Connection connection=null;

			Class.forName("com.mysql.jdbc.Driver");
			connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/StudentManagement","root","root");

		return connection;
	}

	public List<ClassInfo> getClassName() throws Exception {
		List<ClassInfo> classInfoList=new ArrayList<ClassInfo>();
		Connection connection=null;
		try{
			connection=getConnection();
			String getName="select classId,className from classinfo";
			Statement statement=connection.createStatement();
			ResultSet rs=statement.executeQuery(getName);
			while(rs.next()){
				ClassInfo classInfo=new ClassInfo();
				classInfo.setClassId(rs.getInt("classId"));
				classInfo.setClassName(rs.getString("className"));
				classInfoList.add(classInfo);
			}
		}
		finally {
			connection.close();
		}
		return classInfoList;
	}

	public int addStudentInfo(StudentInfo studentInfo) throws SQLException, ClassNotFoundException {
		int x=0;
		Connection connection=null;
		try{
			connection=getConnection();
			String addStudent="insert into studentinfo(studentName,birthdate,mobileNo,classId) values('"+studentInfo.getStudentName()+"','"+studentInfo.getBirthdate()+"','"+studentInfo.getMobileNo()+"','"+studentInfo.getClassId()+"')";
			Statement statement=connection.createStatement();
			x=statement.executeUpdate(addStudent);
		}catch (Exception e) {
			System.out.println(e);
		}
		finally{
			connection.close();
		}
		return x;
	}

	public List<StudentInfo> listStudent(int classId) throws SQLException, ClassNotFoundException {
		List<StudentInfo> listStudent=new ArrayList<StudentInfo>();
		StudentInfo student=null;
		Connection connection=null;
		try{
			connection=getConnection();
			StringBuilder builder = new StringBuilder();
			builder.append("select si.studentId,si.studentName,si.birthdate,si.mobileNo,si.classId,ci.className");
			builder.append(" from studentinfo si ");
			builder.append(" inner join classinfo ci ON si.classId=ci.classId ");
			if(classId>0)
				builder.append(" where si.classId=").append(classId);

			String retriveStudentList=builder.toString();
			Statement statement=connection.createStatement();
			//System.out.println(retriveStudentList);
			ResultSet rs=statement.executeQuery(retriveStudentList);
			while(rs.next()){
				student=new StudentInfo();
				student.setStudentId(rs.getInt("studentId"));
				student.setStudentName(rs.getString("studentName"));
				student.setBirthdate(rs.getString("birthdate"));
				student.setMobileNo(rs.getString("mobileNo"));
				student.setClassId(rs.getInt("classId"));
				student.setClassName(rs.getString("className"));
				listStudent.add(student);
			}
		}
		finally {
			connection.close();
		}
		return listStudent;
	}

	public StudentInfo getStudentById(int id) throws SQLException, ClassNotFoundException {
		StudentInfo student=null;
		Connection connection=null;
		//List<StudentInfo> list=new ArrayList<StudentInfo>();
		try{
			connection=getConnection();
            String retriveUserByID="select * from studentinfo where studentId="+id;

            Statement statement=connection.createStatement();
            ResultSet rs=statement.executeQuery(retriveUserByID);
            if(rs.next()){

            	student=new StudentInfo();
            	student.setStudentId(rs.getInt("studentId"));
            	student.setStudentName(rs.getString("studentName"));
            	student.setBirthdate(rs.getString("birthdate"));
            	student.setMobileNo(rs.getString("mobileNo"));
            	student.setClassId(rs.getInt("classId"));
            	return student;
            	//list.add(student);
            }else{
            	return null;
            }
		}
		finally{
			connection.close();
		}
		//return student;
	}

	public int updateStudentInfo(StudentInfo student) throws SQLException, ClassNotFoundException {
		int x=0;
		Connection connection=null;
		try{
			connection=getConnection();
			String updateUser="update studentinfo set studentName='"+student.getStudentName()+"',birthdate='"+student.getBirthdate()+"',mobileNo='"+student.getMobileNo()+"',classId='"+student.getClassId()+"' where studentId='"+student.getStudentId()+"'";
			Statement statement=connection.createStatement();
			x=statement.executeUpdate(updateUser);
		}
		finally{
			connection.close();
		}
		return x;
	}

	public int deleteUser(int id) throws SQLException, ClassNotFoundException {
		int status=0;
        Connection connection=null;
       try{
           connection=getConnection();
           //StudentInfo student=new StudentInfo();

           Statement st=connection.createStatement();
           String deleteStudent="delete from studentinfo where studentId="+id;

           status=st.executeUpdate(deleteStudent);
       }
       finally{
			connection.close();
		}
       return status;
	}

}