package com.test.dao.impl;

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 java.util.Map;

import com.test.bean.Student;
import com.test.dao.StudentDao;
import com.test.util.DBUtil;

public class StudentDaoImpl implements StudentDao {

	private final String SELECT_STUDENT = "select * from student where id=?";
	private final String SELECT_STUDENT__BY_NAME = "select * from student where username=?";
	private final String SELECT_STUDENT_LIST = "select * from student limit ?,?";
	private final String ADD_STUDENT = "insert into student(username,gender,birth,password) values(?,?,?,?)";
	private final String DELETE_STUDENT = "delete from student where id = ?";
	private final String EDIT_STUDENT = "update student set username=?,gender=?,birth=?,password=? where id = ?";
	private final String LOGIN = "select count(*) from student where username=? and password=?";
	private final String TOTAL_RECORD = "select count(*) from student";

	public List<Student> getStudentList(Map map) {
		List<Student> list = new ArrayList<Student>();
		Connection conn = DBUtil.getConnection();
		try {
			PreparedStatement stmt = conn.prepareStatement(SELECT_STUDENT_LIST);
			stmt.setInt(1, (Integer) map.get("start"));
			stmt.setInt(2, (Integer) map.get("pageSize"));
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				Student stu = new Student();
				stu.setId(rs.getInt("id"));
				stu.setUsername(rs.getString("username"));
				stu.setBirth(rs.getString("birth"));
				stu.setGender(rs.getString("gender"));
				stu.setPassword(rs.getString("password"));
				list.add(stu);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close();
		}

		return list;
	}

	public int addStudent(Student stu) {
		Connection conn = DBUtil.getConnection();
		int i = 1, ret = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(ADD_STUDENT);
			pstmt.setString(i++, stu.getUsername());
			pstmt.setString(i++, stu.getGender());
			pstmt.setString(i++, stu.getBirth());
			pstmt.setString(i++, stu.getPassword());
			ret = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close();
		}
		return ret;
	}

	public int deleteStudent(Integer id) {
		Connection conn = DBUtil.getConnection();
		int i = 1, ret = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(DELETE_STUDENT);
			pstmt.setInt(i++, id);
			ret = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close();
		}
		return ret;
	}

	public int editStudent(Student stu) {
		Connection conn = DBUtil.getConnection();
		int i = 1, ret = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(EDIT_STUDENT);
			pstmt.setString(i++, stu.getUsername());
			pstmt.setString(i++, stu.getGender());
			pstmt.setString(i++, stu.getBirth());
			pstmt.setString(i++, stu.getPassword());
			pstmt.setInt(i++, stu.getId());
			ret = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close();
		}
		return ret;
	}

	public Student getStudent(Integer id) {
		Connection conn = DBUtil.getConnection();
		Student stu = null;
		try {
			PreparedStatement pstmt = conn.prepareStatement(SELECT_STUDENT);
			pstmt.setInt(1, id);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				stu = new Student();
				stu.setId(rs.getInt("id"));
				stu.setUsername(rs.getString("username"));
				stu.setBirth(rs.getString("birth"));
				stu.setGender(rs.getString("gender"));
				stu.setPassword(rs.getString("password"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close();
		}
		return stu;
	}

	public Student getStudentByName(String username) {
		Connection conn = DBUtil.getConnection();
		Student stu = null;
		try {
			PreparedStatement pstmt = conn
					.prepareStatement(SELECT_STUDENT__BY_NAME);
			pstmt.setString(1, username);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				stu = new Student();
				stu.setId(rs.getInt("id"));
				stu.setUsername(rs.getString("username"));
				stu.setBirth(rs.getString("birth"));
				stu.setGender(rs.getString("gender"));
				stu.setPassword(rs.getString("password"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close();
		}
		return stu;
	}

	public int login(String username, String password) {
		Connection conn = DBUtil.getConnection();
		int ret = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(LOGIN);
			pstmt.setString(1, username);
			pstmt.setString(2, password);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				ret = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close();
		}
		return ret;
	}

	public int total() {
		int count = 0;
		Connection conn = DBUtil.getConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(TOTAL_RECORD);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close();
		}
		return count;
	}
}