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.JiangchengDaoWR;
import com.WR.StudentMS.model.JiangchengWR;
import com.WR.StudentMS.model.Tchengjilzh;
import com.WR.StudentMS.model.ZhuanyeWR;
public class JiangchengDaoImpWR implements JiangchengDaoWR {

	@Override
	public List<JiangchengWR> findAll() {
		// TODO 自动生成的方法存根
	String sql = "select id,stu_id,shijian,shuxing,beizhu from t_jiangcheng";
		
		List<JiangchengWR> list = new ArrayList<JiangchengWR>();
		
		try(   
				Connection conn = DBHelper.getConnection();
				PreparedStatement pstmt = conn.prepareStatement(sql);
				ResultSet rs = pstmt.executeQuery(); ){
			while(rs.next()) {
				
				JiangchengWR p = new JiangchengWR();
				
				p.setId(rs.getInt("id"));
                p.setStu_id(rs.getInt("stu_id"));
                p.setShijian(rs.getString("shijian"));
                p.setShuxing(rs.getString("shuxing"));
                p.setBeizhu(rs.getString("beizhu"));
				
				list.add(p);
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public JiangchengWR findById(int id) {
		// TODO 自动生成的方法存根
		Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select id,stu_id,shijian,shuxing,beizhu from t_jiangcheng where id = ?";
        try {
            conn = DBHelper.getConnection(); 
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            if (rs.next()) {
            	JiangchengWR p = new JiangchengWR();
            	p.setId(rs.getInt("id"));
                p.setStu_id(rs.getInt("stu_id"));
                p.setShijian(rs.getString("shijian"));
                p.setShuxing(rs.getString("shuxing"));
                p.setBeizhu(rs.getString("beizhu"));
                return p;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DBHelper.close(rs, pstmt, conn);
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
		return null;
	}

	@Override
	public int create(JiangchengWR jiangchengwr) {
	    String sql = "insert into t_jiangcheng (id, stu_id, shijian, shuxing, beizhu) values (?,?,?,?,?)";
	    try (
	        Connection conn = DBHelper.getConnection();
	        PreparedStatement pstmt = conn.prepareStatement(sql);
	    ) {
	        pstmt.setInt(1, jiangchengwr.getId());
	        pstmt.setInt(2, jiangchengwr.getStu_id());
	        pstmt.setString(3, jiangchengwr.getShijian()); // 确保shijian是日期或字符串格式
	        pstmt.setString(4, jiangchengwr.getShuxing());
	        pstmt.setString(5, jiangchengwr.getBeizhu());
	        
	        int affectedRows = pstmt.executeUpdate();
	        System.out.printf("成功插入%d条数据。\n", affectedRows);
	        return affectedRows; // 返回插入的行数
	    } catch (SQLException e) {
	        // 使用日志框架记录异常信息
	        // log.error("数据库操作失败", e);
	        e.printStackTrace(); // 仅作为示例，实际生产环境应避免
	        return -1; // 发生异常时返回-1或其他错误代码
	    }
	}

	@Override
	public int remove(JiangchengWR jiangchengwr){
	    String sql = "DELETE FROM t_jiangcheng WHERE id = ?"; // 修改SQL语句，只包含一个参数
	    try (
	        Connection conn = DBHelper.getConnection();
	        PreparedStatement pstmt = conn.prepareStatement(sql)
	    ) {
	        pstmt.setInt(1, jiangchengwr.getId()); // 只设置id参数
	        int affectedRows = pstmt.executeUpdate();
	        System.out.printf("删除%d条数据。\n", affectedRows);
	        return affectedRows;
	    } catch (SQLException e) {
	        e.printStackTrace();
	        return -1;
	    }
	}

	@Override
	public int modify(JiangchengWR jiangchengwr) {
		// TODO 自动生成的方法存根
		String sql = "update t_jiangcheng set stu_id = ?,  shijian= ?, shuxing = ?, beizhu = ? where id = ?";
		try (
                Connection conn = DBHelper.getConnection();
                PreparedStatement pstmt= conn.prepareStatement(sql)
        )
        {
            pstmt.setInt(1, jiangchengwr.getStu_id());
            pstmt.setString(2, jiangchengwr.getShijian());
            pstmt.setString(3, jiangchengwr.getShuxing());
            pstmt.setString(4, jiangchengwr.getBeizhu());
        	pstmt.setInt(5, jiangchengwr.getId());

            int executeUpdate = pstmt.executeUpdate();
            System.out.printf("更新%d条数据。\n", executeUpdate);
        } catch (SQLException e) {
            e.printStackTrace();
        }
		return 0;
	}

	

}