package com.bdqn.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;

import javax.print.attribute.HashAttributeSet;

import com.bdqn.entity.ClassS;
import com.bdqn.entity.Student;
import com.bdqn.util.DbUtil;

public class StudentDao {
	
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	
	public List<Student> SelecttAll(){
		List<Student> list = new ArrayList<Student>();
		String sql = "select * from student";
		Object[] obj = {};
		ps = DbUtil.executeSql(sql, obj);
		try {
			rs = ps.executeQuery();
			while(rs.next()){
				Student stu = new Student();
				stu.setId(rs.getInt("id"));
				stu.setName(rs.getString("name"));
				stu.setClassid(rs.getInt("classid"));
				list.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public List<Student> SelectAllId(int id){
		List<Student> list = new ArrayList<Student>();
		String sql = "select * from student where id=?";
		Object[] obj = {id};
		ps = DbUtil.executeSql(sql, obj);
		try {
			rs = ps.executeQuery();
			while(rs.next()){
				Student stu = new Student();
				stu.setId(rs.getInt("id"));
				stu.setName(rs.getString("name"));
				stu.setClassid(rs.getInt("classid"));
				list.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public List<Student> SelectAllName(String name){
		List<Student> list = new ArrayList<Student>();
		String sql = "select * from student where name=?";
		Object[] obj = {name};
		ps = DbUtil.executeSql(sql, obj);
		System.out.println(ps);
		try {
			rs = ps.executeQuery();
			while(rs.next()){
				Student stu = new Student();
				stu.setId(rs.getInt("id"));
				stu.setName(rs.getString("name"));
				stu.setClassid(rs.getInt("classid"));
				list.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public List<Student> SelectAllIdName(int id,String name){
		List<Student> list = new ArrayList<Student>();
		String sql = "select * from student where id=? and name=?";
		Object[] obj = {id,name};
		ps = DbUtil.executeSql(sql, obj);
		try {
			rs = ps.executeQuery();
			while(rs.next()){
				Student stu = new Student();
				stu.setId(rs.getInt("id"));
				stu.setName(rs.getString("name"));
				stu.setClassid(rs.getInt("classid"));
				list.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public List<Student> SelectAllIdNameClassId(int id,String name,int classId){
		List<Student> list = new ArrayList<Student>();
		String sql = "select * from student where id=? and name=? and classid=?";
		Object[] obj = {id,name,classId};
		ps = DbUtil.executeSql(sql, obj);
		try {
			rs = ps.executeQuery();
			while(rs.next()){
				Student stu = new Student();
				stu.setId(rs.getInt("id"));
				stu.setName(rs.getString("name"));
				stu.setClassid(rs.getInt("classid"));
				list.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public List<Student> SelectAllStudent(Student stu){
		List<Student> list = new ArrayList<Student>();
		String sql = "select * from student where id=? and name=? and classid=?";
		Object[] obj = {stu.getId(),stu.getName(),stu.getClassid()};
		ps = DbUtil.executeSql(sql, obj);
		try {
			rs = ps.executeQuery();
			while(rs.next()){
				stu.setId(rs.getInt("id"));
				stu.setName(rs.getString("name"));
				stu.setClassid(rs.getInt("classid"));
				list.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public Map<Student,ClassS> SelectAllMapCname(){
		Map<Student,ClassS> map = new HashMap<Student,ClassS>();
		String sql = "select s.id,s.name,c.cname from student s,class c where s.classid = c.id";
		Object[] obj = {};
		ps = DbUtil.executeSql(sql, obj);
		try {
			rs = ps.executeQuery();
			while(rs.next()){
				Student stu = new Student();
				stu.setId(rs.getInt("id"));
				stu.setName(rs.getString("name"));
				ClassS cs = new ClassS();
				cs.setCname(rs.getString("cname"));
				map.put(stu, cs);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return map;
	}
	
	public Hashtable<Student, ClassS> SelectAllHashtableCname(){
		Hashtable<Student,ClassS> hash = new Hashtable<Student,ClassS>();
		String sql = "select s.id,s.name,c.cname from student s,class c where s.classid = c.id";
		Object[] obj = {};
		ps = DbUtil.executeSql(sql, obj);
		try {
			rs = ps.executeQuery();
			while(rs.next()){
				Student stu = new Student();
				stu.setId(rs.getInt("id"));
				stu.setName(rs.getString("name"));
				ClassS cs = new ClassS();
				cs.setCname(rs.getString("cname"));
				hash.put(stu, cs);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return hash;
	}
	
	public List<Student> SelectLikeName(String name){
		List<Student> list = new ArrayList<Student>();
		String sql = "select * from student where name like ?";
		String name1 = "%" + name + "%";
		Object [] obj = {name1};
		ps = DbUtil.executeSql(sql, obj);
		try {
			rs = ps.executeQuery();
			while(rs.next()){
				Student stu = new Student();
				stu.setId(rs.getInt("id"));
				stu.setName(rs.getString("name"));
				stu.setClassid(rs.getInt("classid"));
				list.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}
	
	public List<Student> SelectLikeName(Student stu){
		List<Student> list = new ArrayList<Student>();
		String sql = "select * from student where name like ?";
		String name1 = "%" + stu.getName() + "%";
		Object [] obj = {name1};
		ps = DbUtil.executeSql(sql, obj);
		try {
			rs = ps.executeQuery();
			while(rs.next()){
				Student stu1 = new Student();
				stu1.setId(rs.getInt("id"));
				stu1.setName(rs.getString("name"));
				stu1.setClassid(rs.getInt("classid"));
				list.add(stu1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}
	
	public Map<Student,ClassS> SelectLikeMapSname(String name){
		Map<Student,ClassS> map = new HashMap<Student,ClassS>();
		String sql = "select s.id,s.name,c.cname from student s,class c where s.classid = c.id and s.name like ?";
		String name1 = "%" + name + "%";
		Object[] obj = {name1};
		ps = DbUtil.executeSql(sql, obj);
		System.out.println(ps);
		try {
			rs = ps.executeQuery();
			while(rs.next()){
				Student stu = new Student();
				stu.setId(rs.getInt("id"));
				stu.setName(rs.getString("name"));
				ClassS cs = new ClassS();
				cs.setCname(rs.getString("cname"));
				map.put(stu, cs);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return map;
	}
	
	public Map<Student,ClassS> SelectLikeMapSname(Student stu){
		Map<Student,ClassS> map = new HashMap<Student,ClassS>();
		String sql = "select s.id,s.name,c.cname from student s,class c where s.classid = c.id and s.name like ?";
		String name = "%" + stu.getName() + "%";
		Object[] obj = {name};
		ps = DbUtil.executeSql(sql, obj);
		System.out.println(ps);
		try {
			rs = ps.executeQuery();
			while(rs.next()){
				Student stu1 = new Student();
				stu1.setId(rs.getInt("id"));
				stu1.setName(rs.getString("name"));
				ClassS cs = new ClassS();
				cs.setCname(rs.getString("cname"));
				map.put(stu, cs);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return map;
	}
	
	public Map<Student,ClassS> SelectLikeMapCname(String name){
		Map<Student,ClassS> map = new HashMap<Student,ClassS>();
		String sql = "select s.id,s.name,c.cname from student s,class c where s.classid = c.id and c.cname like ?";
		String name1 = "%" + name + "%";
		Object[] obj = {name1};
		ps = DbUtil.executeSql(sql, obj);
		System.out.println(ps);
		try {
			rs = ps.executeQuery();
			while(rs.next()){
				Student stu = new Student();
				stu.setId(rs.getInt("id"));
				stu.setName(rs.getString("name"));
				ClassS cs = new ClassS();
				cs.setCname(rs.getString("cname"));
				map.put(stu, cs);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return map;
	}
}