scoredao.java

是Eclipse web开发从入门到精通的源码

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;

	// 需要执行的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;
	}

}