package db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import util.ConnectionFactory;
import entity.Student;

public class StudentinformationBiz {
	Connection conn=null;
	Statement stmt = null;
	ResultSet rs = null;

	public void addUser(String sql) {
		try {
			conn=ConnectionFactory.getConnection();
			stmt=conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public Vector<Vector> findAll(){
		Vector<Vector> vector=new Vector<Vector>();
		try{
			conn=ConnectionFactory.getConnection();
			String sql="select s.stu_ID,s.stu_name,c.classname,s.nation from studentinformation s,classname c where s.classid=c.classid";
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);//得到結果集
			ResultSetMetaData rmeta=rs.getMetaData();
			int numColumns=rmeta.getColumnCount();//通過數據結果集，得到列數
			while(rs.next()){
				Vector<String> hResult=new Vector<String>();
				for(int i=0;i<numColumns;i++){//分別得到一列的數據
					String temp=rs.getString(i+1);
					hResult.add(temp);//加到集合
				}
				vector.add(hResult);
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			ConnectionFactory.close(conn);
		}
		return vector;
	}
	/*處理查詢數據庫的方法，并反回一個集合類型的數據集*/
	public Vector<String> getColumn(){
        Vector<String> hResult=new Vector<String>();	//定義一個能放一列的字符類型向量
        String sql="select s.stu_ID,s.stu_name,c.classname,s.nation from studentinformation s,classname c where s.classid=c.classid";
        try {
        	conn=ConnectionFactory.getConnection();
            Statement stm=conn.createStatement();
            ResultSet rs=stm.executeQuery(sql);
            ResultSetMetaData rmeta=rs.getMetaData(); //得到結果數據集
            int numColumns=rmeta.getColumnCount();	//通過結果數據集，獲得列數
            for(int i=0;i<numColumns;i++){  //i作為列方向變化的指針，讀一行數據
                String tempString=rmeta.getColumnName(i+1);
                hResult.addElement(tempString);  //把個個列值逐一加入定義好的向量中
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return hResult;
	}
	public boolean find(String userName,String password){
		boolean success=true;
		try {
			String sql="select * from usertable where username=";
			sql+="'"+userName+"'";
			sql+=" and password=";
			sql+="'"+password+"'";
			//System.out.println(sql);
			conn=ConnectionFactory.getConnection();
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			if(!rs.next()){
				success=false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return success;
	}
	public ResultSet getInformation(String sql){
		try {
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		return rs;
	}
	public void add(Student stu){
		String sql="insert into studentinformation(stu_ID,stu_name,classid,nation) values('"+stu.getStu_ID()+"',"+"'"+stu.getStu_name()+"',"+"'"+stu.getClassid()+"',"+"'"+stu.getNation()+"')";
		try {
			conn=ConnectionFactory.getConnection();
			stmt=conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}