datamanager.java

Java大作业+学生成绩管理系统,采用access数据库连接。

//处理数据库
//package grade;

import java.sql.*;
import java.util.Enumeration;
import java.util.Vector;

import javax.swing.*;

public class DataManager {
	private Connection con;
	private Statement st;
	private ResultSet rs;
	//插入数据项
	public String no;
	public String name;
	public String sex;
	public String major;
	public String sclass;
	public String net;
	public String com;
	public String os;
	public String soft;
	public String eng;
//	//定义向量,放置学生姓名信息
//	private Vector<String> stu = new Vector();
	//连接数据库
	public void Connect(){
		try{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String url = "jdbc:odbc:students";
			con = DriverManager.getConnection(url);
			st = con.createStatement();
			
		}catch(Exception e){
			
		}
	}
	
	//插入学生信息
	public void Input(String no,String name,String sex,String major,String sclass, 
			String net,String os,String com,String soft,String eng){
		
		this.Connect();
		String s = "insert into students values('"+no+"','"+name+"','"+sex+"','"+major+"','"+sclass+"'" +
				","+Integer.parseInt(net)+","+Integer.parseInt(os)+","+Integer.parseInt(com)+","+Integer.parseInt(soft)+","+Integer.parseInt(eng)+")";
		try{
			st.execute(s);
		}catch(Exception e){
			
		}
	}
	
	//查询学生信息
	public void Search(String no){
		
		this.Connect();
		String s = "select * from students where sno = '"+no+"'";
		try{
			rs = st.executeQuery(s);
			int count = 0;
			while(rs.next()){
				count++;
				this.no = rs.getString("sno");
				this.name = rs.getString("sname");
				this.sex = rs.getString("ssex");
				this.major = rs.getString("smajor");
				this.sclass = rs.getString("sclass");
				this.net = Integer.toString(rs.getInt("snet"));
				this.os = Integer.toString(rs.getInt("sos"));
				this.com = Integer.toString(rs.getInt("scom"));
				this.soft = Integer.toString(rs.getInt("ssoft"));
				this.eng = Integer.toString(rs.getInt("seng"));
			}
			if(count == 0)
			{
				this.name = null;
				this.sex = null;
				this.major = null;
				this.sclass = null;
				this.net = null;
				this.os = null;
				this.com = null;
				this.soft = null;
				this.eng = null;
				JOptionPane.showMessageDialog(null, "查询失败,没有找到");
			}
			
		}catch(Exception e){
			
		}
	}
	
	//修改学生信息
	public void Modify(String no,String name,String sex,String major,String sclass, 
			String net,String os,String com,String soft,String eng){
		
		this.Connect();
		String s = "update students set sname ='"+name+"',ssex ='"+sex+"',smajor='"+major+"',sclass='"+sclass+"'" +
				",snet="+Integer.parseInt(net)+",sos="+Integer.parseInt(os)+",scom="+Integer.parseInt(com)+",ssoft="+Integer.parseInt(soft)+",seng="+Integer.parseInt(eng)+" where sno = '"+no+"'";
		try{
			st.execute(s);
		}catch(Exception e){
			System.out.println("1");
		}
	}
	
	
	//删除学生信息
	public void Delete(String no){
		
		this.Connect();
		String s = "delete from students where sno = '"+no+"'";
		try{
			st.execute(s);
		}catch(Exception e){
			System.out.println("1");
		}
	}
	
	//查询某一科目成绩大于或小于60的学生
	public Vector<String> Find(String subject,String symbol){
		
		this.Connect();
		//定义向量,放置学生姓名信息
		Vector<String> stu = new Vector();
		String s = null;
		if(subject.equals("网络工程"))
		{
			if(symbol.equals(">="))
				s =  "select sname,snet from students where snet >= 60";
			else
				s = "select sname,snet from students where snet < 60";
			try{
				rs = st.executeQuery(s);
				String tmp = null;
				int t ;
				while(rs.next())
				{
					tmp = rs.getString("sname");
					t = rs.getInt("snet");
					tmp = tmp +"   "+ Integer.toString(t);
					stu.addElement(tmp);
				}
			}catch(Exception e){
				System.out.println("1");
			}
		}
		if(subject.equals("操作系统"))
		{
			if(symbol.equals(">="))
				s =  "select sname,sos from students where sos >= 60";
			else
				s =  "select sname,sos from students where sos < 60";
			try{
				rs = st.executeQuery(s);
				String tmp = null;
				int t ;
				while(rs.next())
				{
					tmp = rs.getString("sname");
					t = rs.getInt("sos");
					tmp = tmp +"   "+ Integer.toString(t);
					stu.addElement(tmp);
				}
			}catch(Exception e){
				System.out.println("1");
			}
		}
		if(subject.equals("编译原理"))
		{
			if(symbol.equals(">="))
				s =  "select sname,scom from students where scom >= 60";
			else
				s =  "select sname,scom from students where scom < 60";
			try{
				rs = st.executeQuery(s);
				String tmp = null;
				int t ;
				while(rs.next())
				{
					tmp = rs.getString("sname");
					t = rs.getInt("scom");
					tmp = tmp +"   "+ Integer.toString(t);
					stu.addElement(tmp);
				}
			}catch(Exception e){
				System.out.println("1");
			}
		}
		if(subject.equals("软件工程"))
		{
			if(symbol.equals(">="))
				s =   "select sname,ssoft from students where ssoft >= 60";
			else
				s =  "select sname,ssoft from students where ssoft < 60";
			try{
				rs = st.executeQuery(s);
				String tmp = null;
				int t ;
				while(rs.next())
				{
					tmp = rs.getString("sname");
					t = rs.getInt("ssoft");
					tmp = tmp +"   "+ Integer.toString(t);
					stu.addElement(tmp);
				}
			}catch(Exception e){
				System.out.println("1");
			}
		}
		if(subject.equals("专业英语"))
		{
			if(symbol.equals(">="))
				s =  "select sname,seng from students where seng >= 60";
			else
				s =  "select sname,seng from students where seng < 60";
			try{
				rs = st.executeQuery(s);
				String tmp = null;
				int t ;
				while(rs.next())
				{
					tmp = rs.getString("sname");
					t = rs.getInt("seng");
					tmp = tmp +"   "+ Integer.toString(t);
					stu.addElement(tmp);
				}
			}catch(Exception e){
				System.out.println("1");
			}
		}
		
		return stu;
	}
	
	//统计各科平均分
	public String SubjectAvg(String subject){
		
		String s = null;
		float t = 0 ;
		int m =0;
		this.Connect();
		if(subject.equals("网络工程"))
		{
			s =  "select avg(snet) from students";
			try{
				rs = st.executeQuery(s);
				while(rs.next())
				{
					t = rs.getFloat(1);
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		if(subject.equals("操作系统"))
		{
			s =  "select avg(sos) from students";
			try{
				rs = st.executeQuery(s);
				while(rs.next())
				{
					t = rs.getFloat(1);
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		if(subject.equals("编译原理"))
		{
			s =  "select avg(scom) from students";
			try{
				rs = st.executeQuery(s);
				while(rs.next())
				{
					t = rs.getFloat(1);
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		if(subject.equals("软件工程"))
		{
			s =  "select avg(ssoft) from students";
			try{
				rs = st.executeQuery(s);
				while(rs.next())
				{
					t = rs.getFloat(1);
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		if(subject.equals("专业英语"))
		{
			s =  "select avg(seng) from students";
			try{
				rs = st.executeQuery(s);
				while(rs.next())
				{
					t = rs.getFloat(1);
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		
		return Float.toString(t);
	}
	
	//总分平均分统计
	public String allAvg(){
		
		float sum = 0;
		int count = 0;
		this.Connect();
		String s = "select sum(snet) from students";
		try{
			rs = st.executeQuery(s);
			if(rs.next())
			{
				sum = rs.getFloat(1);
			}
			rs = null;
			s = "select sum(sos) from students";
			rs = st.executeQuery(s);
			if(rs.next())
			{
				sum =sum + rs.getFloat(1);
			}
			rs = null;
			s = "select sum(scom) from students";
			rs = st.executeQuery(s);
			if(rs.next())
			{
				sum =sum + rs.getFloat(1);
			}
			rs = null;
			s = "select sum(ssoft) from students";
			rs = st.executeQuery(s);
			if(rs.next())
			{
				sum =sum + rs.getFloat(1);
			}
			rs = null;
			s = "select sum(seng) from students";
			rs = st.executeQuery(s);
			if(rs.next())
			{
				sum =sum + rs.getFloat(1);
			}
			rs = null;
			s = "select count(*) from students";
			rs = st.executeQuery(s);
			if(rs.next())
			{
				count = rs.getInt(1);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		if(count == 0)
			return "0";
		else
		{
			sum = sum/count;
			return Float.toString(sum);
		}
	}
	
	//各科成绩排名
	public String signalCount(String no,String subject){
		
		int index = 0;
		this.Connect();
		String s = null;
		if(subject.equals("网络工程"))
		{
			s = "select sno,snet from students order by snet desc";
			try{
				rs = st.executeQuery(s);
				while(rs.next())
				{
					index++;
					if(no.equals(rs.getString("sno")))
						break;
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		if(subject.equals("操作系统"))
		{
			s = "select sno,sos from students order by sos desc";
			try{
				rs = st.executeQuery(s);
				while(rs.next())
				{
					index++;
					if(no.equals(rs.getString("sno")))
						break;
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		if(subject.equals("编译原理"))
		{
			s = "select sno,scom from students order by scom desc";
			try{
				rs = st.executeQuery(s);
				while(rs.next())
				{
					index++;
					if(no.equals(rs.getString("sno")))
						break;
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		if(subject.equals("软件工程"))
		{
			s = "select sno,ssoft from students order by ssoft desc";
			try{
				rs = st.executeQuery(s);
				while(rs.next())
				{
					index++;
					if(no.equals(rs.getString("sno")))
						break;
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		if(subject.equals("专业英语"))
		{
			s = "select sno,seng from students order by seng desc";
			try{
				rs = st.executeQuery(s);
				while(rs.next())
				{
					index++;
					if(no.equals(rs.getString("sno")))
						break;
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return Integer.toString(index);
	}
	
	//获得各科的分数段人数
	public int gethigh(String subject,int grade){
		
		//总的人数
		int num = 0;
		String s = null;
		this.Connect();
		if(subject.equals("网络工程"))
		{
			if(grade == 60)
				s = "select count(*) from students where snet < 60";
			if(grade == 70)
				s = "select count(*) from students where snet < 70and snet >=60";
			if(grade == 80)
				s = "select count(*) from students where snet < 80and snet >=70";
			if(grade == 90)
				s = "select count(*) from students where snet < 90and snet >=80";
			if(grade == 100)
				s = "select count(*) from students where snet < 100and snet >=90";
			try{
				rs = st.executeQuery(s);
				while(rs.next())
				{
					num= rs.getInt(1);
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		if(subject.equals("操作系统"))
		{
			if(grade == 60)
				s = "select count(*) from students where sos < 60";
			if(grade == 70)
				s = "select count(*) from students where sos < 70and sos >=60";
			if(grade == 80)
				s = "select count(*) from students where sos < 80and sos >=70";
			if(grade == 90)
				s = "select count(*) from students where sos < 90and sos >=80";
			if(grade == 100)
				s = "select count(*) from students where sos < 100and sos >=90";
			try{
				rs = st.executeQuery(s);
				while(rs.next())
				{
					num= rs.getInt(1);
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		if(subject.equals("编译原理"))
		{
			if(grade == 60)
				s = "select count(*) from students where scom < 60";
			if(grade == 70)
				s = "select count(*) from students where scom < 70and scom >=60";
			if(grade == 80)
				s = "select count(*) from students where scom < 80and scom >=70";
			if(grade == 90)
				s = "select count(*) from students where scom < 90and scom >=80";
			if(grade == 100)
				s = "select count(*) from students where scom < 100and scom >=90";
			try{
				rs = st.executeQuery(s);
				while(rs.next())
				{
					num= rs.getInt(1);
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		if(subject.equals("软件工程"))
		{
			if(grade == 60)
				s = "select count(*) from students where ssoft < 60";
			if(grade == 70)
				s = "select count(*) from students where ssoft < 70and ssoft >=60";
			if(grade == 80)
				s = "select count(*) from students where ssoft < 80and ssoft >=70";
			if(grade == 90)
				s = "select count(*) from students where ssoft < 90and ssoft >=80";
			if(grade == 100)
				s = "select count(*) from students where ssoft < 100and ssoft >=90";
			try{
				rs = st.executeQuery(s);
				while(rs.next())
				{
					num= rs.getInt(1);
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		if(subject.equals("专业英语"))
		{
			if(grade == 60)
				s = "select count(*) from students where seng < 60";
			if(grade == 70)
				s = "select count(*) from students where seng < 70and seng >=60";
			if(grade == 80)
				s = "select count(*) from students where seng < 80and seng >=70";
			if(grade == 90)
				s = "select count(*) from students where seng < 90and seng >=80";
			if(grade == 100)
				s = "select count(*) from students where seng < 100and seng >=90";
			try{
				rs = st.executeQuery(s);
				while(rs.next())
				{
					num= rs.getInt(1);
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return num;
	}
	
	//获得总的人数
	public int count(){
		
		int count = 0;
		String s = "select count(*) from students";
		this.Connect();
		try{
			rs = st.executeQuery(s);
			while(rs.next())
			{
				count = rs.getInt(1);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return count;
	}
	
	public static void main(String[] args) {
		// TODO 自动生成方法存根
		
		DataManager a = new DataManager();
//		a.Search("3105007109");
//		System.out.println(a.com);
//		a.Modify("3105007109", "朱才文", "女", "计算机", "7", "82", "78", "85", "80", "80");
//		a.Delete("3105007109");
		
//		Vector<String> stu = new Vector();
//		stu = a.Find("网络工程", "<");
//		for(Enumeration<String> e = stu.elements();e.hasMoreElements();)
//		{
//			String t = e.nextElement();
//			System.out.println(t);
//		}
//		a.Input("3105007109", "朱才文", "女", "计算机", "7", "80", "80", "80", "80", "80");
//		String b = a.SubjectAvg("网络工程");
//		System.out.println(b);
//		String c = a.allAvg();
//		System.out.println(c);
//		String b = a.signalCount("3105007109", "操作系统");
//			System.out.println(b);
		float b = a.gethigh("网络工程", 70);
		System.out.println(b);
	}

}