package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;

import po.Student;

//訪問數據庫
public class StudentDao {
	private Connection conn = null;
	
	public void initConnection(){//初始化數據庫連接
		try{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			conn = DriverManager.getConnection("jdbc:odbc:School","scott","tiger");
		}catch(Exception ex){ex.printStackTrace();}
	}
	
	public ArrayList queryStu(){
		ArrayList stus = new ArrayList();
		String sql = "SELECT 學號,姓名,性別,出生年月,家庭住址 FROM 學籍";
		try{
			this.initConnection();
			ResultSet rs = conn.createStatement().executeQuery(sql);
			while(rs.next()){
				Student stu = new Student();
				stu.setStuId(rs.getString("學號"));
				stu.setStuName(rs.getString("姓名"));
				stu.setStuSex(rs.getString("性別"));
				stu.setStuBir(rs.getString("出生年月"));
				stu.setStuAdd(rs.getString("家庭住址"));
				stus.add(stu);
			}
		}catch(Exception ex){ex.printStackTrace();}
		finally{
			this.closeConnection();
		}
		return stus;
	}
	
	public ArrayList queryStuBySex(String sex){
		ArrayList stus = new ArrayList();
		String sql = "SELECT 學號,姓名,性別,出生年月,家庭住址 FROM 學籍 WHERE 性別='"+sex+"'";
		try{
			this.initConnection();
			ResultSet rs = conn.createStatement().executeQuery(sql);
			while(rs.next()){
				Student stu = new Student();
				stu.setStuId(rs.getString("學號"));
				stu.setStuName(rs.getString("姓名"));
				stu.setStuSex(rs.getString("性別"));
				stu.setStuBir(rs.getString("出生年月"));
				stu.setStuAdd(rs.getString("家庭住址"));
				stus.add(stu);
			}
		}catch(Exception ex){ex.printStackTrace();}
		finally{
			this.closeConnection();
		}
		return stus;
	}
	
	public ArrayList queryStuBySA(String sex,String add){
		ArrayList stus = new ArrayList();
		String sql = "SELECT 學號,姓名,性別,出生年月,家庭住址 FROM 學籍 WHERE 性別='"+sex+"' AND 家庭住址='"+add+"'";
		try{
			this.initConnection();
			ResultSet rs = conn.createStatement().executeQuery(sql);
			while(rs.next()){
				Student stu = new Student();
				stu.setStuId(rs.getString("學號"));
				stu.setStuName(rs.getString("姓名"));
				stu.setStuSex(rs.getString("性別"));
				stu.setStuBir(rs.getString("出生年月"));
				stu.setStuAdd(rs.getString("家庭住址"));
				stus.add(stu);
			}
		}catch(Exception ex){ex.printStackTrace();}
		finally{
			this.closeConnection();
		}
		return stus;
	}
	
	public void closeConnection(){
		try{
			if(conn!=null){
				conn.close();
				conn = null;
			}
		}catch(Exception ex){ex.printStackTrace();}
	}
}