managertoolset.java

《Java案例开发》源代码252KB大小

/*
 * ManagerToolSet.java
 *
 * Created on 2004年1月13日, 下午8:40
 */

package romulus.Manager;

import java.sql.*;
import java.util.*;

/**
 *
 * @author  S
 */
public class ManagerToolSet{
    
    /** The connection of the database.*/
    private Connection con;

    /** Creates a new instance of ManagerToolSet */
    public ManagerToolSet(Connection con) {
        this.con = con;
    }
    
    public void close() throws SQLException{
        con.close();
    }
    
    public void finalize(){
        try{
            con.close();
        }
        catch(Exception e){
        }
    }
    
    /** Get ManagerToolSet use a database connection*/
    public static ManagerToolSet getInstance(Connection con){
        return new ManagerToolSet(con);
    }
    
    /** Check a String
     * @return true when String contains only ASCII letter and number
     */
    public static boolean CheckString(String input){
        char[] letters = input.toCharArray();
        for(int i = 0, temp; i<letters.length; i++){
            temp = letters[i];
            if(temp<0x0030 || temp>0x007A || (temp<0x0041&&temp>0x0039) || (temp<0x0061&&temp>0x005A)){
                return false;
            }
        }
        return true;
    }
    
    /** Check the user*/
    public boolean CheckUser(String Username, String Password) throws SQLException{
        if(!CheckString(Username) || !CheckString(Password)){
            return false;
        }
        String sql  = "select * from student where student  = ? and pass = ?";
        PreparedStatement pre = con.prepareStatement(sql);
        pre.setString(1, Username);
        pre.setString(2, Password);
        ResultSet res = pre.executeQuery();
        if(res.next()){
            res.close();
            pre.close();
            return true;
        }
        else{
            res.close();
            pre.close();
            return false;
        }
    }
    
    /** Check the Test*/
    public boolean CheckTest(String Username, String Password, String Testname) throws SQLException{
        if(!CheckUser(Username, Password)){
            return false;
        }
        String sql = "select * from test where student = ? and test = ? and score = -1";
        PreparedStatement pre = con.prepareStatement(sql);
        pre.setString(1, Username);
        pre.setString(2, Testname);
        ResultSet res = pre.executeQuery();
        if(res.next()){
            res.close();
            pre.close();
            sql  = "update test set score = 0 where student = ? and test = ?";
            pre = con.prepareStatement(sql);
            pre.setString(1, Username);
            pre.setString(2, Testname);
            pre.executeUpdate();
            pre.close();
            return true;
        }
        else{
          res.close();
            pre.close();
            return false;
        }
    }
    
    /** Store result
     */
    public void StoreResult(String Username, String Testname, double score, String info) throws SQLException{
        String sql = "update test set score = ?, info = ? where student = ? and test = ?";
        PreparedStatement pre = con.prepareStatement(sql);
        pre.setDouble(1, score);
        pre.setString(2, info);
        pre.setString(3, Username);
        pre.setString(4, Testname);
        pre.executeUpdate();
        pre.close();
    }
    
    /** Add a new username with the same password
     */
    public boolean AddNewUser(String username) throws SQLException{
        if(!CheckString(username)){
            return false;
        }
        String sel_sql = "select * from student where student = ?";
        String ins_sql  = "insert into student(student, pass) values (?, ?) ";
        PreparedStatement pre = con.prepareStatement(sel_sql);
        pre.setString(1, username);
        ResultSet res = pre.executeQuery();
        if(res.next()){
            res.close();
            pre.close();
            return false;
        }
        else{
            res.close();
            pre.close();
            pre = con.prepareStatement(ins_sql);
            pre.setString(1, username);
            pre.setString(2, username);
            int i = pre.executeUpdate();
            pre.close();
            if(i == 1){
                return true;
            }
            else{
                return false;
            }
        }
    }
    
    public Vector getUsers() throws SQLException{
        String sql  = "select student from student";
        Statement sta = con.createStatement();
        ResultSet res = sta.executeQuery(sql);
        Vector ret = new Vector();
        for( ;res.next(); ){
            ret.add(res.getString(1));
        }
        res.close();
        sta.close();
        return ret;
    }
    
    public Vector getTests() throws SQLException{
        String sql  = "select testident from testlist";
        Statement sta = con.createStatement();
        ResultSet res = sta.executeQuery(sql);
        Vector ret = new Vector();
        for( ;res.next(); ){
            ret.add(res.getString(1));
        }
        res.close();
        sta.close();
        return ret;
    }
    
    public void assignTest(String user, String test) throws SQLException{
        String sql = "insert into test(student, test) values(?, ?)";
        PreparedStatement pre = con.prepareStatement(sql);
        pre.setString(1, user);
        pre.setString(2, test);
        pre.executeUpdate();
        pre.close();
    }
    
    public Vector getUserTests(String user) throws SQLException{
        String sql = "select test from test where student = ?";
        PreparedStatement pre = con.prepareStatement(sql);
        pre.setString(1, user);
        ResultSet res = pre.executeQuery();
        Vector ret = new Vector();
        for( ;res.next(); ){
            ret.add(res.getString(1));
        }
        res.close();
        pre.close();
        return ret;
    }
    
    public String getUserPass(String user) throws SQLException{
        String sql = "select pass from student where student = ?";
        PreparedStatement pre = con.prepareStatement(sql);
        pre.setString(1, user);
        ResultSet res = pre.executeQuery();
        if(res.next()){
            String ret = res.getString(1);
            res.close();
            pre.close();
            return ret;
        }
        else{
            res.close();
            pre.close();
            return null;
        }
    }
    
    public void setUserPass(String user, String pass) throws SQLException{
        String sql = "update student set pass = ? where student = ?";
        PreparedStatement pre = con.prepareStatement(sql);
        pre.setString(1, pass);
        pre.setString(2, user);
        pre.executeUpdate();
        pre.close();
    }
    
    /** get the report table
     */
    public javax.swing.table.AbstractTableModel getReportTable(String param, boolean asc) throws SQLException{
        String sql = "select Student, Test, Score, Info from test order by ";
        
        if(param.equals(this.MTS_Default)){
            sql = sql+this.MTS_Student;
            sql = sql+" ASC";
            Statement sta = con.createStatement();
            ResultSet res = sta.executeQuery(sql);
            
            java.util.SortedSet set = new java.util.TreeSet();
            for( ; res.next(); ){
                SortedTestInfo sortitem = new SortedTestInfo(res.getString(1), res.getString(2), res.getDouble(3), res.getString(4));
                set.add(sortitem);
            }
            
            int size = set.size();
            String[][] data = new String[size][];
            for(int i = 0; i<size && (!set.isEmpty()); i++){
                if(asc){
                    data[i] = ((SortedTestInfo)(set.first())).toArray();
                    set.remove(set.first());
                }
                else{
                    data[i] = ((SortedTestInfo)(set.last())).toArray();
                    set.remove(set.last());
                }
            }
            String[] column = new String[4];
            column[0] = this.MTS_Student;
            column[1] = this.MTS_Test;
            column[2] = this.MTS_Score;
            column[3] = this.MTS_Info;
            
            res.close();
            sta.close();
            
            return new javax.swing.table.DefaultTableModel(data, column);

        }
        
        else if(param.equals(this.MTS_Score)){
            sql = sql+this.MTS_Score;
        }
        else if(param.equals(this.MTS_Student)){
            sql = sql+this.MTS_Student;
        }
        else if(param.equals(this.MTS_Test)){
            sql = sql+this.MTS_Test;
        }
        
        if(asc){
            sql = sql+" ASC";
        }
        else{
            sql = sql+" DESC";
        }
        Statement sta = con.createStatement();
        ResultSet res = sta.executeQuery(sql);
        ReportTableModel ret = new ReportTableModel(res);
        res.close();
        sta.close();
        return ret;
    }
    
    public String getInfo(String stu, String test) throws SQLException{
        String sql = "select score, info from test where student = ? and test = ?";
        PreparedStatement pre = con.prepareStatement(sql);
        pre.setString(1, stu);
        pre.setString(2, test);
        ResultSet res = pre.executeQuery();
        if(res.next()){
            String ret =  "Score: "+res.getDouble(1)+" AND Info:"+res.getString(2);
            res.close();
            pre.close();
            return ret;
        }
        else{
            res.close();
            pre.close();
            return "N/A";
        }
    }
    
    public javax.swing.tree.TreeModel getResultTree(boolean userbase, boolean taketest, boolean passtest) throws SQLException{
        javax.swing.tree.DefaultMutableTreeNode root = new javax.swing.tree.DefaultMutableTreeNode("Test Results Tree");
        javax.swing.tree.DefaultTreeModel ret = new javax.swing.tree.DefaultTreeModel(root);
        String basesql = null;
        if(userbase){
            basesql = "select distinct student from test";
            Statement sta = con.createStatement();
            ResultSet res = sta.executeQuery(basesql);
            
            String resultsql = "select test from test where student = ?";
            if(taketest){
                resultsql = resultsql + " and score <> -1";
            }
            if(passtest){
                resultsql = resultsql + " and score >= 0.6";
            }
            
            PreparedStatement pre = con.prepareStatement(resultsql);
            
            for( ; res.next(); ){
                String studata = res.getString(1);
                javax.swing.tree.DefaultMutableTreeNode stu = new javax.swing.tree.DefaultMutableTreeNode(studata);
                root.add(stu);
                pre.setString(1, studata);
                ResultSet res2 = pre.executeQuery();
                for( ; res2.next(); ){
                    javax.swing.tree.DefaultMutableTreeNode tes = new javax.swing.tree.DefaultMutableTreeNode(res2.getString(1));
                    stu.add(tes);
                }
                if(stu.getChildCount() == 0){
                    stu.add(new javax.swing.tree.DefaultMutableTreeNode("N/A"));
                }
                res2.close();
            }
            res.close();
            pre.close();
            sta.close();
        }
        else{
            basesql = "select distinct test from test";
            Statement sta = con.createStatement();
            ResultSet res = sta.executeQuery(basesql);
            
            String resultsql = "select student from test where test = ?";
            if(taketest){
                resultsql = resultsql + " and score <> -1";
            }
            if(passtest){
                resultsql = resultsql + " and score >= 0.6";
            }
            
            PreparedStatement pre = con.prepareStatement(resultsql);
            
            for( ; res.next(); ){
                String studata = res.getString(1);
                javax.swing.tree.DefaultMutableTreeNode stu = new javax.swing.tree.DefaultMutableTreeNode(studata);
                root.add(stu);
                pre.setString(1, studata);
                ResultSet res2 = pre.executeQuery();
                for( ; res2.next(); ){
                    javax.swing.tree.DefaultMutableTreeNode tes = new javax.swing.tree.DefaultMutableTreeNode(res2.getString(1));
                    stu.add(tes);
                }
                if(stu.getChildCount() == 0){
                    stu.add(new javax.swing.tree.DefaultMutableTreeNode("N/A"));
                }
                res2.close();
            }
            res.close();
            pre.close();
            sta.close();
        }
        return ret;
    }
    
    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
    }
    
    protected static String MTS_Student = "Student";
    protected static String MTS_Test = "Test";
    protected static String MTS_Score = "Score";
    protected static String MTS_Info = "Info";
    protected static String MTS_Default = "Default";
    
}