operate_db.java

7、学生学籍管理系统 调查所在学校学生处、教务处

package file;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.JOptionPane;
/**
 * 实现对数据库的操作
 * */
public class Operate_db {

	private String url,name,password;
	private Connection con=null;
	private Statement st=null;
	private ResultSet rs=null;
	private String sql=null;
	
	public Operate_db()
	{
	
		url="jdbc:odbc:java";
		name="sa";
	    password="123456";
	    try
	    {
	    
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			con = DriverManager.getConnection(url, name, password);
		    st=con.createStatement();	
	    
	    }
	    catch(Exception e)
	    {
	    	System.out.print("connection datebase error !");
	    }
	    
	}
	 //登陆的验证
	public boolean login_check(String username,String password)
	{
		boolean temp=false;
		sql="select * from member where username='"+username+"' and password='"+password+"'";
		try
		{
			rs=st.executeQuery(sql);
			if(rs.next())
			{
				temp=true;
			}
		}
		catch(Exception e)
		{
			temp=false;
		}
		return temp;
	}
	 //查询是否存在该学号的学生
	public boolean isFound(String number)
	{
		boolean flag=false;
		sql="select * from s_information where number='"+number+"'";
		try
		{
			rs=st.executeQuery(sql);
			if(rs.next())
			{
				flag=true;
			}
		}
		catch(Exception e)
		{
			flag=false;
		}
		return flag;
	}
	//删除学生记录
	public boolean del_s(String number)
	{
		boolean flag=true;
		sql="delete from s_information where number='"+number+"'";
		try
		{
			st.executeUpdate(sql);
		}
		catch(Exception e)
		{
			flag=false;
		}
		sql="delete from s_score where number='"+number+"'";
		try
		{
			st.executeUpdate(sql);
		}
		catch(Exception e)
		{
			flag=false;
		}
		return flag;
	}
	//得到单科最高分
	public ArrayList get_dan_Hige()
	{
		ArrayList al=new ArrayList();
		sql="select top 1 math from s_score order by math desc ";
		try
		{
			int i=0;
			rs=st.executeQuery(sql);
			if(rs.next())
			{
			    i=rs.getInt("math");
			}
			al.add(i);
		}
		catch(Exception e)
		{
			System.out.print("ERROR!1111");
		}
		sql="select top 1 chinese from s_score order by chinese desc";
		try
		{
			int i=0;
			rs=st.executeQuery(sql);
			if(rs.next())
			{
				i=rs.getInt("chinese");
			}
			al.add(i);
		}
		catch(Exception e)
		{
			System.out.print("ERROR!22222");
		}
		sql="select top 1 english from s_score order by english desc";
		try
		{
			int i=0;
			rs=st.executeQuery(sql);
			if(rs.next())
			{
				i=rs.getInt("english");
			}
			al.add(i);
		}
		catch(Exception e)
		{
			System.out.print("ERROR!33333");
		}
		return al;
	}
	
	public String getHige_student()
	{
		String temp=null;
		sql="select number, max(chinese+math+english) from s_score group by number";
		try
		{
			rs=st.executeQuery(sql);
			if(rs.next())
			{
				temp=rs.getString("number");
			}
		}
		catch(Exception e)
		{
			System.out.print("ERROR!");
		}
		return temp;
	}
	//
	//
	
	public S_score getS_score(String number)
	{

		S_score temp1=new S_score();
		sql="select * from s_score where number='"+number+"'";
		try
		{
			rs=st.executeQuery(sql);
			if(rs.next())
			{
				temp1.setChinese(rs.getInt("chinese"));
				temp1.setEnglish(rs.getInt("english"));
				temp1.setMath(rs.getInt("math"));
				temp1.setNumber(number);
			}
		}
		catch(Exception e)
		{
			
		}
		return temp1;
	}
	
	public void close()
	{
		try
		{
			rs.close();
			st.close();
			con.close();
		}
		catch(Exception e)
		{
			this.close_rs();
		}
	}
	
	public void close_rs()
	{
		try
		{
			st.close();
			con.close();
		}
		catch(Exception e)
		{
		
			System.out.print("close datebase failed");
		}
	}
	 //插入成绩
	public void insert_score(String number,int chinese,int math,int english)
	{
		sql="insert into s_score (number,math,chinese,english) values('"+number+"',"+math+","+chinese+","+english+")";
	    try
	    {
	    	st.executeUpdate(sql);
	    }
	    catch(Exception e)
	    {
	    	System.out.print("insert into score error !");
	    }
	
	}
	//插入学生信息
	public void insert_information(String number,String name,String s_class,int age,String sex)
	{
		sql="insert into s_information (number,name,sex,age,class) values ('"+number+"','"+name+"','"+sex+"',"+age+",'"+s_class+"')";
		try
		{
		
			st.executeUpdate(sql);
		}
		catch(Exception e)
		{
			System.out.print("insert into information error !");
		}
	}
	//得到所有学号
	public ArrayList getAllnumber()
	{
		ArrayList al=new ArrayList();
		sql="select number from s_information";
		try
		{
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				String temp=new String(rs.getString("number"));
				al.add(temp);
				
			}
		}
		catch(Exception e)
		{
			
		}
		return al;
	}
	//得到指定学号的学生信息
	public S_information getS_information(String number)
	{
		S_information temp2=new S_information();
		sql="select * from s_information where number='"+number+"'";
		try
		{
			rs=st.executeQuery(sql);
			if(rs.next())
			{
				temp2.setName(rs.getString("name"));
				temp2.setAge(rs.getInt("age"));
				temp2.setS_class(rs.getString("class"));
				temp2.setSex(rs.getString("sex"));
				temp2.setNumber(number);
			}
		}
		catch(Exception e)
		{
			
		}
		return temp2;
	}
	
	public String getSex(String number)
	{
		sql="select sex from s_information where number='"+number+"'";
		String sex="";
		try
		{
			rs=st.executeQuery(sql);
		
			if(rs.next())
			{
				sex=rs.getString("sex");
			}
		}
		catch(Exception e)
		{
			
		}
		return sex;
	}
	
	public int getMath(String number)
	{
		int math=0;
		sql="select math from s_score where number='"+number+"'";
		try
		{
		rs=st.executeQuery(sql);
		if(rs.next())
		{
			math=rs.getInt("math");
		}
		}
		catch(Exception e)
		{
			
		}
		return math;
	}
	
	public void updateScore(String number,int chinese,int math,int english)
	{
		sql="update s_score set math="+math+" where number='"+number+"'";
		try
		{
			st.executeQuery(sql);
		}
		catch(Exception e)
		{
			
		}

		sql="update s_score set chinese="+chinese+" where number='"+number+"'";
		try
		{
			st.executeQuery(sql);
		}
		catch(Exception e)
		{
			
		}
		

		sql="update s_score set english="+english+" where number='"+number+"'";
		try
		{
			st.executeQuery(sql);
		}
		catch(Exception e)
		{
			
		}
		
		
	}
	
	public void updateInformation(String number,String name,String sex,String s_class,int age)
	{
		sql="update s_Information set age="+age+" where number='"+number+"'";
		try
		{
			st.executeQuery(sql);
		}
		catch(Exception e)
		{
			
		}
		
		sql="update s_Information set name='"+name+"' where number='"+number+"'";
		try
		{
			st.executeQuery(sql);
		}
		catch(Exception e)
		{
			
		}
		sql="update s_Information set sex='"+sex+"' where number='"+number+"'";
		try
		{
			st.executeQuery(sql);
		}
		catch(Exception e)
		{
			
		}
		
		sql="update s_Information set s_class='"+s_class+"' where number='"+number+"'";
		try
		{
			st.executeQuery(sql);
		}
		catch(Exception e)
		{
			
		}
	}
	
	public ArrayList getALLscore()
	{
		ArrayList al=new ArrayList();
		sql="select * from s_score";
		try
		{
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				S_score temp1=new S_score();
				temp1.setChinese(rs.getInt("chinese"));
				temp1.setEnglish(rs.getInt("english"));
				//temp1.setMath(rs.getInt("math"));
				//temp1.setNumber(rs.getString("number"));
				al.add(temp1);
			}
		}
		catch(Exception e)
		{
			System.out.print("get all score error!");
		}
		
		return al;
		
	}
	
	public void setSex(String number,String sex)
	{
		sql="update s_information set sex='"+sex+"' where number='"+number+"'";
		try
		{
			st.executeQuery(sql);
		}
		catch(Exception e)
		{
			
		}
	}
	
	public ArrayList getALLmath()