package hsqldb.dbo;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import hsqldb.javabean.Score;

public class ScoreDAO {

	// 數據庫的JDBC連接
	private Connection connection;

	private Statement statement;

	// 需要執(zhí)行的SQL語句
	private String sql;

	/**
	 * 構造函數，建立到score數據庫的連接，同時在score數據庫中建立Score表
	 */
	public ScoreDAO() {

		// 建立到score數據庫的連接
		connection = ScoreDAOFactory.createConnection();

		try {
			statement = connection.createStatement();
			sql = "CREATE TABLE Score (SNO CHAR(7)  NOT NULL,CNO CHAR(6) NOT NULL,GRADE NUMERIC(4,1),PRIMARY KEY(SNO,CNO))";
			statement.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 將Score的一個對象插入到數據庫中
	 * 
	 * @param score
	 */
	public void insertScore(Score score) {
		sql = "INSERT INTO SCORE VALUES(" + "\'" + score.getSNO() + "\',"
				+ "\'" + score.getCNO() + "\'," + score.getGRADE() + ")";

		try {
			statement.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 在數據庫中查詢包含某個Score對象信息的記錄
	 * 
	 * @param score
	 * @return
	 */
	public ResultSet selectScore(Score score) {

		ResultSet result = null;

		sql = "select * from score where SNO=" + score.getSNO() + "and CNO="
				+ score.getCNO();
		try {
			result = statement.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;

	}

	/**
	 * 刪除某個數據庫記錄
	 * 
	 * @param score
	 */
	public void deleteScore(Score score) {

		sql = "delete from score where SNO=" + score.getSNO() + "and CNO="
				+ score.getCNO();
		try {
			statement.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 更新數據庫中的某條記錄
	 * 
	 * @param oldScore
	 * @param newScore
	 */
	public void updateScore(Score oldScore, Score newScore) {

		sql = "update score set SNO=" + newScore.getSNO() + ",CNO="
				+ newScore.getCNO() + ",GRADE=" + newScore.getGRADE()
				+ " where SNO=" + oldScore.getSNO() + "and CNO="
				+ oldScore.getCNO();
		try {
			statement.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 查詢數據庫中的所有記錄
	 * 
	 * @return
	 */
	public ResultSet selectAll() {
		ResultSet result = null;
		sql = "select * from score";

		try {
			result = statement.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;
	}

}