import java.sql.*; 

public class DBDemo {
	private Connection conn;
	private Statement stmt;
	private ResultSet rs;
	
	//構造方法,以JDBC-ODBC橋方式建立數據庫連接
	public DBDemo(String url, String userName, String password ){
		try {
			// 加載JDBC-ODBC驅動程序
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			// 創建連接
			conn = DriverManager.getConnection(url, userName, password);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	//獲取Statement
	public Statement getStmt(){
    	try {
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
            return stmt;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    
    //一般查詢操作
   public ResultSet query(String sql) {
        try {
            stmt = getStmt();
            rs = stmt.executeQuery(sql);
            return rs;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    
	//顯示查詢結果,顯示學生相關信息
   public void display(ResultSet rs) {
        try {
			while (rs.next()) {
				String id = rs.getString(1);
				String name = rs.getString(2);
				String sex = rs.getString("性別");
				String major = rs.getString("專業方向");
				String dormitory = rs.getString("宿舍");
				System.out.println("學號:" + id + "\t姓名:" + name + "\t性別:"
						+ sex + "\t專業方向:" + major + "\t宿舍:"
						+ dormitory);
			}
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

	//統計查詢記錄的個數
    public int getRowCount(String strSql) {
        int intCount = 0;
        try {
            stmt =getStmt();
            rs = stmt.executeQuery("SELECT COUNT(*) FROM " + strSql);
            if (rs.next()) {
                intCount = rs.getInt(1);
            } else {
                intCount = -1;
            }
        } catch (Exception e) {
            intCount = -2;
            e.printStackTrace();
        } finally {
            return intCount;
        }
    }
    
	//插入操作,返回受此操作影響的行數
    public int insert(String sql) {
        int count = 0;
        stmt =getStmt();
        try {
            count = stmt.executeUpdate(sql);
        } catch (Exception e) {
            count = -2;
            e.printStackTrace();
        } finally {
            return count;
        }
    }
    
	//更新操作,返回受此操作影響的行數
    public int update(String sql) {
        int count = 0;
        stmt =getStmt();
        try {
            count = stmt.executeUpdate(sql);
        } catch (Exception e) {
            count = -2;
            e.printStackTrace();
        } finally {
            return count;
        }
    }
        
   	//刪除操作,返回受此操作影響的行數
    public int delete(String sql) {
        int count = 0;
        stmt =getStmt();
        try {
            count = stmt.executeUpdate(sql);
        } catch (Exception e) {
            count = -2;
            e.printStackTrace();
        } finally {
            return count;
        }
    }

	//關閉操作
   public void close() {
        try {
			if (rs!=null)
  				rs.close(); 
			if (stmt!=null)
				stmt.close();
			if (conn!=null)
				conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }	
	
	public static void main(String[] args) {
		DBDemo db=new DBDemo("jdbc:odbc:myAccess","","");//創建與"軟件系06級本科學生信息.mdb"的連接

		int count=db.getRowCount("student");//統計student表的人數
		System.out.println("軟件系06級本科生的人數為:"+count);	
				
		db.display(db.query("select * from student where 性別='女'"));//顯示所有女生的信息

		//增加一條記錄:0123456789,張小山, 男,計算機科學與技術(軟件開發),綠楊樓 G1234 
		db.insert("insert into student (學號,姓名,性別,專業方向,宿舍) values ('0123456789','張小山','男','計算機科學與技術(軟件開發)','綠楊樓 G1234')");
		db.display(db.query("select * from student where 學號='0123456789'"));//顯示剛插入的記錄
		
		//修改張小山的記錄:宿舍調整為"青楓閣 M2345" 
		db.update("update student set 宿舍='青楓閣 M2345' where 學號='0123456789'");
		db.display(db.query("select * from student where 學號='0123456789'"));//顯示"張小山"的記錄		

		//刪除張小山的記錄 
		db.delete("delete from student  where 學號='0123456789'");

		db.close();//執行關閉操作
	}
}