package cn.com.dao;


import java.sql.*;
import java.util.*;

import cn.com.bean.Score;
import cn.com.bean.Student;




public class StudentDao {
	
	public  List stuAll(){
		Connection con=null;
	    Statement stmt=null;
	    ResultSet rs=null;
	    
	     con = ConManager.getConnection();
	     try {
			stmt = con.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			rs = stmt.executeQuery("select * from student");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		List list = new ArrayList();
		
		try {
			while (rs.next()){
			
				Student st=new Student();
				st.setSid(rs.getInt(1));
				st.setName(rs.getString(2));
				st.setStu_no(rs.getString(3));
				st.setSex(rs.getString(4));
				st.setAge(rs.getInt(5));
				st.setAddr(rs.getString(6));
				st.setMajor(rs.getString(7));
				list.add(st);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public void update(int sid,String s_no)
	{
		Connection con=null;
		PreparedStatement pstmt=null;
	    ResultSet rs=null;
	    
	     con = ConManager.getConnection();
		try {
			pstmt=con.prepareStatement("update student set s_no="+"'"+s_no+"'"+"where sid="+sid);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void insert(Student student)
	{
		Connection con=null;
		PreparedStatement pstmt=null;
	    ResultSet rs=null;
	    
	     con = ConManager.getConnection();
		try {
			pstmt=con.prepareStatement("insert into student value(null,?,?,?,?,?,?) ");
			pstmt.setString(1, student.getName());
			pstmt.setString(2,student.getStu_no());
			pstmt.setString(3,student.getSex());
			pstmt.setInt(4,student.getAge());
			pstmt.setString(5,student.getAddr());
			pstmt.setString(6,student.getMajor());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	
	public void delete(int sid)
	{
		Connection con=null;
	    Statement stmt=null;
	    ResultSet rs=null;
	    
	     con = ConManager.getConnection();
	     try {
			stmt = con.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			stmt.executeUpdate("delete from student where sid="+sid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	
	public  List student(String name){
		Connection con=null;
	    Statement stmt=null;
	    ResultSet rs=null;
	    
	     con = ConManager.getConnection();
	     try {
			stmt = con.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			rs = stmt.executeQuery("select * from student where name="+"'"+name+"'");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		List list = new ArrayList();
		Student st=new Student();
		try {
			while (rs.next()){
			
				st.setName(rs.getString(2));
				st.setStu_no(rs.getString(3));
				st.setSex(rs.getString(4));
				st.setAge(rs.getInt(5));
				st.setAddr(rs.getString(6));
				st.setMajor(rs.getString(7));
				list.add(st);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	
	public void update1(String name,String s_no)
	{
		Connection con=null;
		PreparedStatement pstmt=null;
	    ResultSet rs=null;
	    
	     con = ConManager.getConnection();
		try {
			pstmt=con.prepareStatement("update student set s_no="+"'"+s_no+"'"+"where name="+"'"+name+"'");
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	
}