package com.nitpro.school.dao;

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

import com.nitpro.school.bean.Clazz;
import com.nitpro.school.util.ConnectionPoolHelper;

public class ClazzDao {
    public List<Clazz> getClazz(){
    	List<Clazz> list = new ArrayList<Clazz>();
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			String sql = "SELECT clazzid, clazzname, sdate, edate FROM clazz";
			Statement state = conn.createStatement();
			ResultSet rs = state.executeQuery(sql);
			while(rs.next()){
				Clazz clazz = new Clazz();
				clazz.setClazzId(rs.getInt("clazzid"));
				clazz.setClazzName(rs.getString("clazzname"));
				clazz.setSdate(rs.getDate("sdate"));
				clazz.setEdate(rs.getDate("edate"));
				list.add(clazz);
			}
			ConnectionPoolHelper.closeResultSet(rs);
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return list;
    }
    
    public int insertClazz(Clazz clazz){
    	int result = 0;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			String sql = "INSERT INTO clazz(clazzname, sdate, edate) VALUES(?, ?, ?)";
			PreparedStatement state = conn.prepareStatement(sql);
			state.setString(1, clazz.getClazzName());
			state.setDate(2, clazz.getSdate()!=null ? new java.sql.Date(clazz.getSdate().getTime()) : null);
			state.setDate(3, clazz.getEdate()!=null ? new java.sql.Date(clazz.getEdate().getTime()) : null);
			result = state.executeUpdate();
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return result;
    }

    public int updateClazz(Clazz clazz){
    	int result = 0;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			String sql = "UPDATE clazz SET clazzname = ?, sdate = ?, edate = ? WHERE clazzid = ?";
			PreparedStatement state = conn.prepareStatement(sql);
			state.setString(1, clazz.getClazzName());
			state.setDate(2, clazz.getSdate()!=null ? new java.sql.Date(clazz.getSdate().getTime()) : null);
			state.setDate(3, clazz.getEdate()!=null ? new java.sql.Date(clazz.getEdate().getTime()) : null);
			state.setInt(4, clazz.getClazzId());
			result = state.executeUpdate();
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return result;
    }

    public Clazz getClazzById(int clazzId){
    	Clazz clazz = null;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			String sql = "SELECT clazzid, clazzname, sdate, edate FROM clazz WHERE clazzid = ?";
			PreparedStatement state = conn.prepareStatement(sql);
			state.setInt(1, clazzId);
			ResultSet rs = state.executeQuery();
			while(rs.next()){
				clazz = new Clazz();
				clazz.setClazzId(rs.getInt("clazzid"));
				clazz.setClazzName(rs.getString("clazzname"));
				clazz.setSdate(rs.getDate("sdate"));
				clazz.setEdate(rs.getDate("edate"));
			}
			ConnectionPoolHelper.closeResultSet(rs);
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return clazz;
    }

}