coursedao.java

accp s1毕业项目 考试管理系统

package com.exam.db.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.exam.db.bean.Course;
import com.yutang.dboption.DBOption;
import com.yutang.dboption.IPreparedStatementSetter;

public class CourseDao {
	List<Course> list = new ArrayList<Course>();
	Course course = new Course();
	DBOption<Course> db = new DBOption<Course>();

	
	public int insertCourse(final Course course){
		int result = 0;		
		db.setCon(MyConnection.getConnection(CourseDao.class));
		String sql ="insert into Course values (?,?)";
		result = db.save(sql, new IPreparedStatementSetter(){
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setString(1,course.getCouName());
				pst.setString(2,course.getGrade());
				return pst;
			}			
		});
		db.closeCon();
		return result;
	}
	
	//根据课程名选择课程号
	public Course selectCourseIDByName(final String courseName) {
		db.setCon(MyConnection.getConnection(CourseDao.class));
		String sql = "select * from Course where CouName = ?";
		course = db.queryOne(sql, course, new IPreparedStatementSetter() {
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setString(1, courseName);
				return pst;
			}
		});
		db.closeCon();
		return course;		
	}
	
	//选择所有的课程
	public List<Course> selectAllCourse() {	
		db.setCon(MyConnection.getConnection(CourseDao.class));
		String sql = "select * from Course order by Grade";
		list = db.queryAll(sql,course);
		db.closeCon();
		return list;		
	}
	//根据年级来选择课程名称
	public List<Course> selectCourseNameByGrade(final String grade){
		db.setCon(MyConnection.getConnection(CourseDao.class));
		String sql = "select * from Course where Grade = ? order by CouID";
		list = db.queryAll(sql,course,new IPreparedStatementSetter(){
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setString(1,grade);
				return pst;
			}			
		});
		db.closeCon();
		return list;
	}
	
	public Course selectCourseNameByID(final int couID){
		db.setCon(MyConnection.getConnection(CourseDao.class));
		String sql = "select * from Course where CouID = ?";
		course = db.queryOne(sql,course,new IPreparedStatementSetter(){
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setInt(1,couID);
				return pst;
			}
			
		});
		return course;
	}
	
	public Course selectCourseByNameAndGrade(final String couName,final String grade){
		db.setCon(MyConnection.getConnection(CourseDao.class));
		String sql = "select * from Course where CouName = ? and Grade = ?";
		course = db.queryOne(sql, course, new IPreparedStatementSetter(){
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setString(1, couName);
				pst.setString(2, grade);
				return pst;
			}
			
		});
		return course;
	}
}