studentdao.java

高校学生信息管理,实现了高校信息化

//数据库访问层，封装数据库具体操作的方法

package student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import db.DatabaseCon;

public class StudentDao 
{
	private Connection conn;
	
	Student st=new Student();
	
	public StudentDao()
	{
		this.conn = DatabaseCon.getConnection();
	}
	
	//根据参数sql语句进行查询，返回学生对象的数组
	public ArrayList<Student> getStudentList (String sql) throws SQLException
	{
		ArrayList<Student> studentlist=new ArrayList<Student>();
		ResultSet rs;
		PreparedStatement pstmt = conn.prepareStatement(sql); //"select * from student"
		rs=pstmt.executeQuery(); 
		while(rs.next())
		{
			Student st=new Student();
			st.setStudentName(rs.getString("name"));
			st.setStudentAge(rs.getString("age"));
			st.setStudentNum(rs.getString("num"));
			st.setStudentClass(rs.getString("class"));
			studentlist.add(st);			
		}
		rs.close();
		pstmt.close();
		return studentlist;
	}
	
    //根据学号返回学生
	public Student returnstudent(String str) throws SQLException
	{
		/**
		ArrayList<Student> studentlist=new ArrayList<Student>();
		String sql =String.format("select * from student where num='%s'",str);
		studentlist=getStudentList(sql);
		return studentlist;
		*/
		ResultSet rs;
		String sql =String.format("select * from student where num='%s'",str);
		Student st=new Student();
		PreparedStatement pstmt = conn.prepareStatement(sql); //"select * from student"
		rs=pstmt.executeQuery(); 
		while(rs.next())
		{
			st.setStudentName(rs.getString("name"));
			st.setStudentAge(rs.getString("age"));
			st.setStudentNum(rs.getString("num"));
			st.setStudentClass(rs.getString("class"));
		}
		rs.close();
		pstmt.close();
		return st;
	}
	
	//删除学生信息
	public void deleteStudent(String str) throws Exception
	{
		String sql =String.format("delete from student where num='%s'",str);
		Statement stmt = conn.createStatement();
	    stmt.execute(sql);
	    stmt.close();
	}
    //根据学号删除学生信息
	public void delete(Student st) throws SQLException
	{
		String sql =String.format("delete from student where num='%s'",st.getStudentNum());
		Statement stmt = conn.createStatement();
	    stmt.execute(sql);
	    stmt.close();
	}
	
	//检查主键是否冲突
	public boolean canInsert(String str) throws SQLException
	{
		boolean flag = true;
		ResultSet rs;
		PreparedStatement pstmt = conn.prepareStatement("select * from student"); 
		rs=pstmt.executeQuery(); 
		while(rs.next())  
		{
	   		if(str.equals(rs.getString("num")))
			{
	   			flag=false;
	   		}
	   	}
		rs.close();
		pstmt.close();
		return flag;
	}
	

    //MVC模式添加学生信息
	public void add(Student st) throws SQLException//add(HttpServletRequest request,HttpServletResponse response) throws SQLException
	{
		PreparedStatement pstmt = conn.prepareStatement("insert into student values(?,?,?,?)");

		pstmt.setString(1, st.getStudentName());
		pstmt.setString(2, String.valueOf(st.getStudentAge()));
		pstmt.setString(3, st.getStudentNum());
		pstmt.setString(4, st.getStudentClass());		
		pstmt.execute();
		pstmt.close();
	}
	
    //	MVC模式修改学生信息
	public boolean modify(Student st,Student student,String numid)//modify(ArrayList<Student> studentlist,HttpServletRequest request,HttpServletResponse response) throws IOException
	{
		boolean ismodify=false;
		try 
		{
			//HttpSession session = request.getSession(); 
			//String numid=(String)session.getAttribute("id");//读取存放在session中的变量"id"，即学生学号
					
			//下面判断是否修改主键
			if(numid.equals(student.getStudentNum()))//if(numid.equals(request.getParameter("num")))
			{
				delete(st);
		       	
		       	//这里需要add传进来的student,而不是st,这样delete(st)再add(st)就没修改
		       	add(student);//add(st);//add(request,response);
		       	ismodify=true;
		    }
			else
			{
				boolean canmodify=true;
				canmodify=canInsert(student.getStudentNum());//(request.getParameter("num"));
				if(canmodify)
				{
					delete(st);
			       	add(student);//add(st);//add(request,response);
			       	ismodify=true;
			    }
				else
				{
					System.out.println("主键（学号）冲突，不能修改该学生信息!");
				}
			}
					       
	    } 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return ismodify;
	}
	
   
    public void addnum(String name) throws SQLException
	{
		String sql;
		String numid = null;
		int num;
		Statement stmt;
		ResultSet rs;
		
		stmt=conn.createStatement();
		rs=stmt.executeQuery(String.format("select * from class where classname='%s'", name));
		while(rs.next())
		{
			numid=rs.getString("studentnum");
		}
		num=Integer.parseInt(numid)+1;
		sql=String.format("update class set studentnum='%s' where classname='%s'", String.valueOf(num),name);
		stmt=conn.createStatement();
		stmt.execute(sql);
		stmt.close();
	}
	

}