questiondao.java~12~

学生考试系统 有学生和老师登录。 学生登录可以选择不同的题库进行考试或练习

package com.svse.dao;

//导包
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//自己的包
import com.svse.db.DBConn;
import com.svse.bean.QuestionBean;
//集合
import java.util.ArrayList;
import com.svse.bean.QuestionBean;
import com.svse.db.DBConn;

public class QuestionDao {
    //封装属性
    Connection conn;
    PreparedStatement ps;
    ResultSet rs;
    //添加信息
    public boolean insert(String title, String optionA, String optionB,
                          String optionC, String optionD, String answer,
                          int courseID) {
        boolean flag = false;
        String sql = "insert into question values(?,?,?,?,?,?,?)";
        conn = DBConn.getConn();

        try {
            ps = conn.prepareStatement(sql);
            //解释？
            ps.setString(1, title);
            ps.setString(2, optionA);
            ps.setString(3, optionB);
            ps.setString(4, optionC);
            ps.setString(5, optionD);
            ps.setString(6, answer);
            ps.setInt(7, courseID);
            flag = ps.execute();

        } catch (SQLException ex) {
            System.err.println("添加新题异常！");
            ex.printStackTrace();
        } finally {
            DBConn.closeDB(ps, conn);
        }

        return flag;

    }

    //删除
    public boolean delete(int questionID) {
        boolean flag = false;
        String sql = "delete from question where questionID = ?";
        conn = DBConn.getConn();
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, questionID);
            flag = ps.execute();
        } catch (SQLException ex) {
            System.err.println("删除题目异常！");
            ex.printStackTrace();
        } finally {
            DBConn.closeDB(ps, conn);
        }

        return flag;
    }

    //更新
    public boolean update(int questionID, String title, String optionA,
                          String optionB,
                          String optionC, String optionD, String answer,
                          int courseID) {
        boolean flag = false;
        String sql = "update question set title = ?,optionA = ?,optionB = ?,optionC = ?,optionD = ?,answer = ?,courseID = ? where questionID = ?";
        conn = DBConn.getConn();

        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, title);
            ps.setString(2, optionA);
            ps.setString(3, optionB);
            ps.setString(4, optionC);
            ps.setString(5, optionD);
            ps.setString(6, answer);
            ps.setInt(7, courseID);
            ps.setInt(8, questionID);
            flag = ps.execute();

        } catch (SQLException ex) {
            System.err.println("修改题异常！");
            ex.printStackTrace();
        } finally {
            DBConn.closeDB(ps, conn);
        }

        return flag;
    }

    public QuestionBean getOne(int questionID) {
        QuestionBean bean = null;
        String sql = "select * from question where questionID = ?";
        conn = DBConn.getConn();

        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, questionID);
            rs = ps.executeQuery();
            if (rs.next()) {
                bean = new QuestionBean();
                bean.setTitle(rs.getString("title"));
                bean.setOptionA(rs.getString("optionA"));
                bean.setOptionB(rs.getString("optionB"));
                bean.setOptionC(rs.getString("optionC"));
                bean.setOptionD(rs.getString("optionD"));
                bean.setAnswer(rs.getString("answer"));
                bean.setCourseID(rs.getInt("courseID"));
            }
        } catch (SQLException ex) {
            System.err.println("查询返回一行纪录异常！");
            ex.printStackTrace();
        } finally {
            DBConn.closeDB(ps, conn);
        }

        return bean;
    }

    //返回所有信息--------用于练习
    public ArrayList<QuestionBean> getTestAll(int UsercourseID) {
        ArrayList<QuestionBean> list = new ArrayList<QuestionBean>();
        conn = DBConn.getConn();
        String sql = "select * from question where UsercourseID = " +
                     UsercourseID + " ";
        if (UsercourseID == 0) {
            sql = "select * from question";
        }

        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                QuestionBean bean = new QuestionBean();
                bean.setTitle(rs.getString("title"));
                bean.setOptionA(rs.getString("optionA"));
                bean.setOptionB(rs.getString("optionB"));
                bean.setOptionC(rs.getString("optionC"));
                bean.setOptionD(rs.getString("optionD"));
                bean.setAnswer(rs.getString("Answer"));
                list.add(bean);
            }
        } catch (SQLException ex) {
            System.err.println("返回多行信息异常！");
            ex.printStackTrace();
        } finally {
            DBConn.closeDB(ps, conn);
        }
        return list;
    }

    //查询全部数据
    public ArrayList<QuestionBean> getAll(int courseID) {
        ArrayList<QuestionBean> list = new ArrayList<QuestionBean>();
        conn = DBConn.getConn();
        String sql =
                "select top 6 * from question where courseID = " + courseID +
                "";
        if (courseID == 0) {
            sql = "select * from question ";
            //sql = "select top 100 * from question order by newid()";
        }
        try {
            ps = conn.prepareStatement(sql);
//            ps.setInt(1, courseID);
            rs = ps.executeQuery();
            while (rs.next()) {
                QuestionBean bean = new QuestionBean();
                bean.setTitle(rs.getString("title"));
                bean.setOptionA(rs.getString("optionA"));
                bean.setOptionB(rs.getString("optionB"));
                bean.setOptionC(rs.getString("optionC"));
                bean.setOptionD(rs.getString("optionD"));
                bean.setAnswer(rs.getString("answer"));
                list.add(bean);
            }
        } catch (SQLException ex) {
            System.err.println("查询全部数据异常！");
            ex.printStackTrace();
        } finally {
            DBConn.closeDB(ps, conn);
        }
        return list;
    }

    //查询并返回记录
    public QuestionBean getInfo(String type) {
        QuestionBean bean = null;
        conn = DBConn.getConn();
        String sql = "select * from userInfo order by  " + type;

        try { //amidName=? or stuName=?
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                bean = new QuestionBean();
                bean.setAdminName(rs.getString("AdminName"));
                bean.setStuName(rs.getString("StuName"));
                bean.setAdminPwd(rs.getString("AdminPwd"));
                bean.setStuPwd(rs.getString("StuPwd"));
            }
        } catch (SQLException ex) {
            System.err.println("查询并返回信息异常");
            ex.printStackTrace();
        } finally {
            DBConn.closeDB(ps, conn);
        }
        return bean;
    }

}