package com.WR.StudentMS.dao.mysql;

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

import com.WR.StudentMS.dao.RegistrationDaowmm;

import com.WR.StudentMS.model.Registrationwmm;



//用户管理DAO
public class Registrationdaoimplwmm implements RegistrationDaowmm {

	@Override
	public List<Registrationwmm> findAll() {
		String sql = "select* from registration";
		List<Registrationwmm> xuejiList = new ArrayList<Registrationwmm>();

		try (
				// 2.创建数据库连接
				Connection conn = DBHelper.getConnection();
				// 3. 创建语句对象
				PreparedStatement pstmt = conn.prepareStatement(sql);
				// 4. 绑定参数
				// 5. 执行查询（R）
				ResultSet rs = pstmt.executeQuery()) {

			// 6. 遍历结果集
			while (rs.next()) {
				Registrationwmm xueji = new Registrationwmm();
				xueji.setStudentidwmm(rs.getString("studentid"));
				xueji.setNamewmm(rs.getString("name"));
				xueji.setSexwmm(rs.getString("sex"));
				xueji.setAgewmm(rs.getString("age"));
				xueji.setClasswmm(rs.getString("class"));
				xueji.setAdmissionwmm(rs.getString("admission"));
				xueji.setGraduationwmm(rs.getString("graduation"));
				xueji.setLengthwmm(rs.getString("length"));
				xueji.setCollegewmm(rs.getString("college"));
				xuejiList.add(xueji);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return xuejiList;
	}


	@Override
	public Registrationwmm findById(String studentid) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Registrationwmm account = null;
		try {
			// 2.创建数据库连接
			conn = DBHelper.getConnection();
			// 3. 创建语句对象
			String sql = "select studentid,name,sex,age,class,admission,graduation,length,college"
					+ " from registration where studentid = ?";
			pstmt = conn.prepareStatement(sql);
			// 4. 绑定参数
			pstmt.setString(1, studentid);
			// 5. 执行查询（R）
			rs = pstmt.executeQuery();
			// 6. 遍历结果集
			if (rs.next()) {
				account = new Registrationwmm();

				account.setStudentidwmm(rs.getString("studentid"));
				account.setNamewmm(rs.getString("name"));
				account.setSexwmm(rs.getString("sex"));
				account.setAgewmm(rs.getString("age"));
				account.setClasswmm(rs.getString("class"));
				account.setAdmissionwmm(rs.getString("admission"));
				account.setGraduationwmm(rs.getString("graduation"));
				account.setLengthwmm(rs.getString("length"));
				account.setCollegewmm(rs.getString("college"));
				
				return account;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally { // 释放资源
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}

		return null;
	}

	@Override
	public int create(Registrationwmm xuejim) {
	    String sql = "insert into registration (studentid, name, sex, age, class, admission, graduation, length, college) "+ "values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
	    try (
	        Connection conn = DBHelper.getConnection();
	        PreparedStatement pstmt = conn.prepareStatement(sql)
	    ) {
	        // 绑定参数到SQL语句的占位符
	        pstmt.setString(1, xuejim.getStudentidwmm());
	        pstmt.setString(2, xuejim.getNamewmm());
	        pstmt.setString(3, xuejim.getSexwmm());
	        pstmt.setString(4, xuejim.getAgewmm()); // 假设age是整型
	        pstmt.setString(5, xuejim.getClasswmm());
	        pstmt.setString(6, xuejim.getAdmissionwmm());
	        pstmt.setString(7, xuejim.getGraduationwmm());
	        pstmt.setString(8, xuejim.getLengthwmm()); // 假设length是整型
	        pstmt.setString(9, xuejim.getCollegewmm());

	        // 执行插入操作
	        int affectedRows = pstmt.executeUpdate();
	        System.out.printf("成功插入%d条数据。\n", affectedRows);
	        return affectedRows; // 返回插入的行数
	    } catch (SQLException e) {
	        e.printStackTrace(); // 打印异常信息
	        return -1; // 发生异常时返回-1
	    }
	}

	@Override
	public int modify(Registrationwmm xuejim) {
	    // 假设所有字段都是字符串类型，如果不是，需要根据实际类型使用setInt等方法
	    String sql = "UPDATE registration SET "
	            + "studentid = ?, "
	            + "name = ?, "
	            + "sex = ?, "
	            + "age = ?, "
	            + "class = ?, "
	            + "admission = ?, "
	            + "graduation = ?, "
	            + "length = ?, "
	            + "college = ? "
	            + "WHERE studentid = ?"; // 注意：这里的WHERE studentid = ?是第11个参数

	    try (
	            Connection conn = DBHelper.getConnection();
	            PreparedStatement pstmt = conn.prepareStatement(sql);
	    ) {
	        // 设置参数值，从1开始计数，需要确保每个占位符都有对应的值
	        pstmt.setString(1, xuejim.getStudentidwmm()); // studentid
	        pstmt.setString(2, xuejim.getNamewmm()); // name
	        pstmt.setString(3, xuejim.getSexwmm()); // sex
	        pstmt.setString(4, xuejim.getAgewmm()); // age
	        pstmt.setString(5, xuejim.getClasswmm()); // class
	        pstmt.setString(6, xuejim.getAdmissionwmm()); // admission
	        pstmt.setString(7, xuejim.getGraduationwmm()); // graduation
	        pstmt.setString(8, xuejim.getLengthwmm()); // length
	        pstmt.setString(9, xuejim.getCollegewmm()); // college
	        pstmt.setString(10, xuejim.getStudentidwmm()); // WHERE条件的studentid

	        // 执行更新操作
	        int affectedRows = pstmt.executeUpdate();
	        System.out.printf("成功修改了%d条数据。\n", affectedRows);
	        return affectedRows; // 返回实际受影响的行数
	    } catch (SQLException e) {
	        e.printStackTrace();
	        return -1; // 发生异常时返回-1
	    }
	}

	@Override
	public int remove(Registrationwmm xuejim) {
	    String sql = "DELETE FROM registration WHERE studentid = ?";
	    try (Connection conn = DBHelper.getConnection();
	         PreparedStatement pstmt = conn.prepareStatement(sql)) {
	        pstmt.setString(1, xuejim.getStudentidwmm()); // 只设置 studentid 作为删除条件
	        int affectedRows = pstmt.executeUpdate();
	        System.out.printf("成功删除%d条数据。\n", affectedRows);
	        return affectedRows;
	    } catch (SQLException e) {
	        e.printStackTrace();
	        return -1;
	    }
	}
		
}