librmanager.java

一个简单的测试系统

package olts;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author [e-mail]
 * @version 2.0
 * 
 */
public class LibrManager {
    public Connection conn;
    public ResultSet rs;
    public int score=0;
    public ItemLibrary itemLib;
    public String url; 

    public LibrManager() {
    	    try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");	//声明驱动程序
            } catch (ClassNotFoundException e) {
          
                e.printStackTrace();
            }
    	    this.url = "jdbc:odbc:a";		//指定数据源a
    	    try {								
                this.conn = DriverManager.getConnection(url,"","");
            } catch (SQLException e1) {
                
                e1.printStackTrace();
                }
            
            }
    /**
     * 添加题目
     * @param queType		题型
     * @param diff			难度	
     * @param ansT			答题时间
     * @param que			题目
     * @param ans			答案
     * @param score			分值
     */
    public void add(String queType,int diff,int ansT,String que,String ans,int score){
    	    try {
    	        this.conn = DriverManager.getConnection(url,"","");
    	        String sql = "SELECT * FROM ";
                sql = sql + queType;		//指定从哪张表抽题
                PreparedStatement ps = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
                rs = ps.executeQuery();
                int oriID = 0;
                while(rs.next()){
                    oriID = rs.getInt("id");
                    }
                rs.moveToInsertRow();		//准备插入题目
                rs.updateInt("id",oriID + 1);
                rs.updateInt("难度",diff);
                rs.updateInt("时间",ansT);
                rs.updateString("题目",que);
                rs.updateString("答案",ans);
                rs.updateInt("分值",score);
                						//将题目添加到数据库
                rs.insertRow();
                conn.close();
            } catch (SQLException e) {
                
                e.printStackTrace();
            }
    	    }
   /**
    * 修改题目
    * @param queType		题型
    * @param id				id
    * @param diff			难度
    * @param ansT			答题时间
    * @param item			题目
    * @param ans			答案
    * @param score			得分
    */
    public void update(String queType,int id,int diff,int ansT,String item,String ans,int score){
        try {
            this.conn = DriverManager.getConnection(url,"","");
            String sql = "SELECT * FROM ";
            sql = sql + queType;		//指定从哪张表抽题
            sql = sql + " WHERE id = " + id;		//指定id
            PreparedStatement ps = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            rs = ps.executeQuery();
            //如果没找到相应id的题目，抛出异常
            if(!(rs.next())) throw new SQLException("没找到相应id的题目");
            rs.updateInt("难度",diff);
            rs.updateInt("时间",ansT);
            rs.updateString("题目",item);
            rs.updateString("答案",ans);
            rs.updateInt("分值",score);
            rs.updateRow();
            conn.close();
            System.out.println("修改成功");
        } catch (SQLException e) {
          
            e.printStackTrace();
        }}
    /**
     * 查询某个题型的所有题目
     * @param queType		题型
     * @return				某个题型的所有题目
     */
    public List queList(String queType){
    	    try {
    	        this.conn = DriverManager.getConnection(url,"","");
    	        String sql = "SELECT * FROM ";
                sql = sql + queType;
                PreparedStatement ps = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
                rs = ps.executeQuery();
            List itemList = new ArrayList();

    	    while(rs.next()){
        	    int id = rs.getInt("id");
        	    int diff = rs.getInt("难度");
        	    int ansT = rs.getInt("时间");
        	    String que = rs.getString("题目");
        	    String ans = rs.getString("答案");
        	    int scor = rs.getInt("分值");
        	    //把符合要求的题目包装成Item类后加入列表中
        	    Item aItem = new Item(id,diff,ansT,que,ans,scor);
        	    itemList.add(aItem);
        	    }
    	    conn.close();
    	    return itemList;
    	     } catch (SQLException e) {
                 // TODO 自动生成 catch 块
                 e.printStackTrace();
             }
    	     return new ArrayList();
    	     }
    /**
     * 删除题目
     * @param queType		题型
     * @param id			id
     */
    public void remove(String queType,int id){
        try {
            this.conn = DriverManager.getConnection(url,"","");
            String sql = "DELETE * FROM ";
            sql = sql + queType;
            sql = sql + " WHERE ID = " + id;
            PreparedStatement ps = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            ps.executeUpdate();
            System.out.println("删除成功");
            conn.close();
        } catch (SQLException e) {
            // TODO 自动生成 catch 块
            e.printStackTrace();
        }
        }
    /**
     * 按照ID查找题目
     * @param queType		题目
     * @param itemID		ID	
     * @return				具有这个ID的题目
     */
    public Item getItemByID(String queType,int itemID){
        try {
            this.conn = DriverManager.getConnection(url,"","");
            String sql = "SELECT * FROM ";
            sql = sql + queType;
            sql = sql + " WHERE ID = " + itemID;
            PreparedStatement ps = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            rs = ps.executeQuery();
            if(!(rs.next())) throw new SQLException("没找到相应id的题目");
        } catch (SQLException e) {
            //如果没找到相应id的题目，抛出异常
            System.out.println("用户输入有误");
            e.printStackTrace();
            return new Item();
        }
        try {
            int id = rs.getInt("id");
            int diff = rs.getInt("难度");
    	    int ansT = rs.getInt("时间");
    	    String que = rs.getString("题目");
    	    String ans = rs.getString("答案");
    	    int scor = rs.getInt("分值");
    	    conn.close();
    	    return new Item(id,diff,ansT,que,ans,scor);
        } catch (SQLException e1) {
            
            e1.printStackTrace();
        }
        	return new Item();
        }
    /**
     * 随机抽取题目
     * @param queType		题型
     * @return				题目
     */
    public Item getItemRandom(String queType){
        this.itemLib = new ItemLibrary(queType);
        int l = this.itemLib.ItemList.size();
        int idRand = (int)(Math.random()*l);
        idRand = idRand >0 ? idRand : 1;	//防止id取到0
        return this.getItemByID(queType,idRand);
        }
    /**
     * 按照难度查询题目
     * @param queType		题型
     * @param itemDiff		给定难度
     * @return				题目
     */
    public List getItemByDiff(String queType,int itemDiff){ 
        try {
            this.conn = DriverManager.getConnection(url,"","");
            String sql = "SELECT * FROM ";
            sql = sql + queType +" WHERE 难度 = " + itemDiff;
            PreparedStatement ps = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            rs = ps.executeQuery();
            List itemList = new ArrayList();
            while(rs.next()){
	           int id = rs.getInt("id");
	           int diff = rs.getInt("难度");
	           int ansT = rs.getInt("时间");
	           String que = rs.getString("题目");
	           String ans = rs.getString("答案");
	           int scor = rs.getInt("分值");
	    
	           Item aItem = new Item(id,diff,ansT,que,ans,scor);
	           itemList.add(aItem);
	    }
            conn.close();
            return itemList;
     } catch (SQLException e) {
         
         e.printStackTrace();
     }
     return new ArrayList();
     }
    }