conntoacc.java

功能简介： 1.增减学生对象（该对象包含学号、姓名、分数等信息） 2.登记、编辑分数 3.计算出平均成绩 4.统计出优秀（100-90）、良好（89-75）、中等（74-60）和不及格（59-

/*
 * 注意：本程序使用ODBC驱动连接Access数据库
 * 为了能够正常使用本程序，请将程序附带的“db.mdb”数据库文件与“学生成绩管理程序.jar”放置在同一目录下
 * 测试源码时需指定db.mdb的绝对路径
 * 
 * 数据库结构：
 * db.mdb: 表“table1”
 * table1：	sid		表示学号 
 * 			name 	表示学生姓名
 * 			score 	表示成绩
 */


import java.sql.*;
import javax.swing.JOptionPane;
public class ConnToAcc {
	//连接、操作数据库
	
	//读取数据库中所有学生对象的信息，并以Student[]数组的形式返回
	public Student[] getData(String sql){
		ResultSet rs = null;
		Student[] data = null;	
		try{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		}
		catch(ClassNotFoundException e){
		//System.err.println("连接数据源错误："+e.getMessage());
			JOptionPane.showMessageDialog(null, "连接数据库错误："+e.getMessage(), "连接数据库错误", 0);
		}
		try{
			Connection con = DriverManager.getConnection(DBurl,"","");
			stmt = con.createStatement();	
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(sql);
			rs.last();
			int i = rs.getRow();
			Student.totalNum = 0;
			Student.aGradeNum = 0;
			Student.bGradeNum = 0;
			Student.cGradeNum = 0;
			Student.dGradeNum = 0;
			
			data = new Student[i];
			rs.afterLast();
			while(rs.previous()){
				int sid=rs.getInt(1);
				String name=rs.getString(2);
				float score=rs.getFloat(3);
				data[--i] = new Student(sid,name,score) ;
				Student.totalNum++;
				Student.averScore = (Student.averScore*(Student.totalNum-1)+score)/Student.totalNum;
				if(score >= 90)
					Student.aGradeNum++;
				else if(score >= 75)
					Student.bGradeNum++;
				else if(score >= 60)
					Student.cGradeNum++;
				else Student.dGradeNum++;
			}
			con.close();	
		}
		catch(SQLException e){
			JOptionPane.showMessageDialog(null, "查询数据库错误："+e.getMessage(), "查询数据库错误", 0);
		}
		return data;
	}
	
	//对数据库执行插入、更新、删除操作
	public void DBOperation(int type, String sql){
		try{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		}
		catch(ClassNotFoundException e){
			System.err.println("连接数据源错误："+e.getMessage());
		}
		try{
			Connection con = DriverManager.getConnection(DBurl,"","");
			stmt = con.createStatement();	
			switch(type){
			case INSERT:
				try{
					stmt.executeUpdate(sql); 
				}
				catch(SQLException e){
					JOptionPane.showMessageDialog(null, "输入的数据不正确： 学号已存在", "输入错误", 0);
				}
				break;
			case UPDATE:
			case DELETE:
				stmt.executeUpdate(sql); 
				break;
			case QUERY:
				try{
					queryName = null;
					queryScore = 0;
					ResultSet queryRS = stmt.executeQuery(sql);
					queryRS.next();	
					queryName = queryRS.getString(2);
					queryScore = queryRS.getFloat(3);
					queryFailed = false;
				}
				catch(SQLException e){
					queryFailed = true;
					JOptionPane.showMessageDialog(null, "查询错误：查询的学号不存在", "查询错误", 0);
				}
			default:
				break;
			}
			con.close();
		}
		catch(SQLException e){
			JOptionPane.showMessageDialog(null, "语句错误："+e.getMessage(), "语句错误", 0);
		}
	}
	
	public Student getQueryResult(int querySID){
		DBOperation(ConnToAcc.QUERY, "SELECT * FROM "+StudentsInfomation+" WHERE sid="+querySID);
		if(queryFailed)
			return null;
		return new Student(querySID, queryName, queryScore);
	}
	
	private String queryName;
	private float queryScore;
	private boolean queryFailed = false;
	
	Statement  stmt;
	
	//定义操作类型
	public static final int INIT = 1 ;
	public static final int SORT_BY_SCORE_ASC = 2 ;
	public static final int SORT_BY_SCORE_DESC = 3 ;
	public static final int INSERT = 1 ;
	public static final int UPDATE = 2 ;
	public static final int DELETE = 3 ;
	public static final int QUERY = 4 ;
	
	private static final String DBurl = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=C:\\db.mdb";
	public static final String StudentsInfomation = "table1";
}