package com.svse.dao;

import com.svse.entity.*;
import com.svse.util.DBConnect;

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

public class StuInfoDao {
	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;

	/**
	 * 得到所有學生信息
	 * 
	 * @return學生信息集合
	 */
	public List<StuInfo> getStuInfo() {
		List<StuInfo> list = new ArrayList<StuInfo>();
		String sql = "select * from stuInfo";
		// 獲取連接
		conn = DBConnect.getConnect();
		try {
			// 創建SQL語句執行對象
			pstmt = conn.prepareStatement(sql);
			// 執行SQL語句得到的結果集
			rs = pstmt.executeQuery();
			// 遍歷結果集，封裝對象
			while (rs.next()) {
				StuInfo stu = new StuInfo();
				stu.setStuName(rs.getString("stuName"));
				stu.setStuNo(rs.getString("stuNo"));
				stu.setStuSex(rs.getString("stuSex"));
				stu.setStuAge(rs.getInt("stuAge"));
				stu.setStuSeat(rs.getInt("stuSeat"));
				stu.setStuAddress(rs.getString("stuAddress"));
				// 將封裝后的學生信息添加到集合里
				list.add(stu);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnect.closeConnect(conn);
		}
		return list;
	}

	/**
	 * 添加新學員
	 * 
	 * @param stuinfo學生信息實體
	 * @return對數據庫影響的行數
	 */
	public int addStudent(StuInfo stuinfo) {
		int result = 0;
		String sql = "INSERT INTO stuInfo VALUES(?,?,?,?,?)";
		conn = DBConnect.getConnect();
		try {
			pstmt = conn.prepareStatement(sql);
			// 解釋SQL語句中的？設置參數
			pstmt.setString(1, stuinfo.getStuName());
			pstmt.setString(2, stuinfo.getStuNo());
			pstmt.setString(3, stuinfo.getStuSex());
			pstmt.setInt(4, stuinfo.getStuAge());
			pstmt.setString(5, stuinfo.getStuAddress());
			// 更新數據庫，返回當前執行SQL語句對數據庫影響的行數
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("插入失敗！\n消息：" + e.getMessage());
		} finally {
			DBConnect.closeConnect(conn);
		}
		return result;
	}

	/**
	 * 刪除一條學生信息
	 * 
	 * @param stuNo學號
	 * @return對數據庫影響的行數
	 */
	public int deleteStudent(String stuNo) {
		int result = 0;
		String sql = "delete from stuInfo where stuNo=?";
		conn = DBConnect.getConnect();
		try {
			pstmt = conn.prepareStatement(sql);
			// 解釋SQL語句中的？設置參數
			pstmt.setString(1, stuNo);
			// 更新數據庫，返回當前執行SQL語句對數據庫影響的行數
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("刪除失敗！\n消息:" + e.getMessage());
		} finally {
			DBConnect.closeConnect(conn);
		}
		return result;
	}

	/**
	 * 修改學生信息
	 * 
	 * @param stuinfo學生實體信息
	 * @return對數據庫影響的行數
	 */
	public int update(StuInfo stuinfo) {
		int result = 0;
		String sql = "update stuInfo set stuName=?,stuSex=?,stuAge=?,stuAddress=? where stuNo=?";
		conn = DBConnect.getConnect();
		try {
			pstmt = conn.prepareStatement(sql);
			// 解釋SQL語句中的？設置參數
			pstmt.setString(1, stuinfo.getStuName());
			pstmt.setString(2, stuinfo.getStuSex());
			pstmt.setInt(3, stuinfo.getStuAge());
			pstmt.setString(4, stuinfo.getStuAddress());
			pstmt.setString(5, stuinfo.getStuNo());
			// 更新數據庫，返回當前執行SQL語句對數據庫影響的行數
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("修改失敗！\n消息:" + e.getMessage());
		} finally {
			DBConnect.closeConnect(conn);
		}
		return result;
	}
	
	/**
	 * 通過學號查詢學生信息
	 * @param stuNo
	 * @return
	 */
	public boolean isExist(String stuNo){
		boolean bool=false;
		String sql="select * from stuInfo where stuNo=?";
		conn=DBConnect.getConnect();
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, stuNo);
			rs=pstmt.executeQuery();
			if(rs.next()){
				bool=true;
			}
		} catch (SQLException e) {
			System.out.println("查詢失敗！\n消息："+e.getMessage());
		}finally{
			DBConnect.closeConnect(conn);
		}
		return bool;
	}
}