proceesql.java

来自「学籍管理系统:利用JAVA的界面对MS SQL进行操作,实现了学生学籍管理系统基」· Java 代码 · 共 1,459 行 · 第 1/4 页

package E4;

import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.util.Vector;
import java.io.File;
import java.io.*;
import java.util.StringTokenizer;
import java.sql.*;

 //处理数据库相关功能
class proceeSQL{
	 public static String url = "jdbc:odbc:stu";
     public static  Connection con;
     public static  String sql;
     public static  Statement stmt;
     Object SC [] = new Object[6];
       
     //打开数据库  
	public static void openSQL(){
		
	try{
         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
       } catch(java.lang.ClassNotFoundException e1) {
           System.err.print("ClassNotFoundException: ");
           System.err.println(e1.getMessage());
       }
                                      
     try{
       	  con = DriverManager.getConnection(url, "sa", "yang");
          stmt = con.createStatement();
       	} catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }   
	}
	//关闭数据库
	void closeSQL(){
	 try{
		    	//关闭连接。
        	stmt.close();
        	con.close();
     	} catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }  
	}
	
	
	//插入学生信息
	public void insertStudentDetail(Object StudentInfor[]){	
	 try {	
			sql = "INSERT INTO DETAIL " +
                    "VALUES('"+String.valueOf(StudentInfor[0])+"','"+String.valueOf(StudentInfor[1])+"','"+String.valueOf(StudentInfor[2])+"',"+
                     String.valueOf(StudentInfor[3]).trim()+","+String.valueOf(StudentInfor[4]).trim()+","+String.valueOf(StudentInfor[5]).trim()
                     +",'"+String.valueOf(StudentInfor[6])+"','"+String.valueOf(StudentInfor[7])+"','"+String.valueOf(StudentInfor[8])+"','"+String.valueOf(StudentInfor[9])+"');";
                     
        	stmt.executeUpdate(sql);
        	con.commit();
        	this.insertIDTABLEofCustomer(String.valueOf(StudentInfor[0]),String.valueOf(StudentInfor[9]));//插入到密码表

       	} catch(SQLException ex) {
       		ex.printStackTrace();
            	JOptionPane.showMessageDialog(null, "对象已经存在");
        } 
            		
	}
	
  //修改学生信息
	public void updateStudentDetail(Object StuInfor[]){	
     try {	 

    sql ="UPDATE DETAIL SET name= '" +String.valueOf(StuInfor[1])+"',sex='"+String.valueOf(StuInfor[2]) +"',age="+
    String.valueOf(StuInfor[3])+",domBuilding="+String.valueOf(StuInfor[4]) +",domNum="+String.valueOf(StuInfor[5]) +",postNum='"+ String.valueOf(StuInfor[6])+
    "',telNum = '"+String.valueOf(StuInfor[7]) +"',address= '"+String.valueOf(StuInfor[8])+"',password= '"+String.valueOf(StuInfor[9])+"' WHERE id ='"+String.valueOf(StuInfor[0])+"';";
     stmt.executeUpdate(sql);            
     con.commit();                           


       	} catch(SQLException ex) {
       		JOptionPane.showMessageDialog(null, "修改无效");
            System.err.println("SQLException: " + ex.getMessage());
            	

        }             		
	}
		
	//插入密码表
	void insertIDTABLEofCustomer(String id,String password)
	{
		try{
		
		     sql = "INSERT INTO IDTABLE " +
            "VALUES("+"'"+id+"','"+password+"',1)";

             stmt.executeUpdate(sql);
        	con.commit();
        	      
       	} catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }   
	}
		
		//验证密码
	public int checkUser(String name,String password,int state){
		String temp_id="";
		String temp_password="";
		int temp_state=0;
		try{
			sql = "SELECT ID,PASSWORD,MARK FROM IDTABLE WHERE ID = '"+name+"' AND MARK="+state;
			ResultSet rs = stmt.executeQuery(sql);
		  if(rs.next()){
            temp_id=rs.getString("ID");
            temp_password=rs.getString("PASSWORD");
            temp_state=rs.getInt("MARK");
	        }
	    
		}catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        } 
       
        if((name.trim()).equals((temp_id).trim())&&(temp_password.trim()).equals(password.trim())&&temp_state==state)
        {
          
        	return state;
        }
        
        else return -1;
         
 }
	

	//删除密码表	
  public void DistroyIDTABLE(String id){
  	
  	try{
		
		sql =" DELETE FROM IDTABLE WHERE ID = "+"'"+id+"'";      
        	    	      	
        	    stmt.executeUpdate(sql);
             	con.commit();	         
        	
        	}catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }  
  	
  }

		//更新密码
   void UpdatePassWord(String id,String password,int state){
   	try{
		sql = " UPDATE IDTABLE SET PASSWORD='"+password+"' WHERE ID ='"+id+"'; UPDATE STUDENT SET password ='"
		    +password+"' WHERE sno='"+id+"';";      
        	    stmt.executeUpdate(sql);
             	con.commit();	         
        	
        	}catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
           
        }  
   	
   }


	//查询学生信息
	int SearchStudentDetail(Vector vc){
		int i=0;
		try{
		
		sql = " SELECT * FROM DETAIL";         
        	ResultSet rs = stmt.executeQuery(sql);
        	       
         while(rs.next()){
                  Vector temp_v= new Vector(12);
            	  temp_v.add(0,(i+1));
                  temp_v.add(1,rs.getObject(1));
                  temp_v.add(2,rs.getObject(2));
                  temp_v.add(3,rs.getObject(3));
                  temp_v.add(4,rs.getObject(4));
                  temp_v.add(5,rs.getObject(5));
                  temp_v.add(6,rs.getObject(6));
                  temp_v.add(7,rs.getObject(7));
                  temp_v.add(8,rs.getObject(8));
                  temp_v.add(9,rs.getObject(9));
                  temp_v.add(10,rs.getObject(10));
                  vc.addElement(temp_v);
                  i++;
	           }
	         }catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }    
        return i;   	
	}	
	
	//查询学生信息2
	void SearchStudentDetailForCustormer(Object studentDetail[],String id){
		int i=0;
		try{
		
		sql = " SELECT * FROM DETAIL WHERE id = '"+id+"';";         
        	ResultSet rs = stmt.executeQuery(sql);
        	       
         while(rs.next()){
          
            	  studentDetail[0] = rs.getObject(1);
            	  studentDetail[1] = rs.getObject(2);
            	  studentDetail[2] = rs.getObject(3);
            	  studentDetail[3] = rs.getObject(4);
            	  studentDetail[4] = rs.getObject(5);
            	  studentDetail[5] = rs.getObject(6);
            	  studentDetail[6] = rs.getObject(7);
            	  studentDetail[7] = rs.getObject(8);
            	  studentDetail[8] = rs.getObject(9);

	           }
	         }catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }    
	
	}
		
    //插入课程信息
	public void insertCourseDetail(Object course[]){	
          	 try {	          
           sql ="INSERT INTO COURSE " +
            "VALUES("+"'"+String.valueOf(course[0])+"','"+String.valueOf(course[1])+"','"+String.valueOf(course[2]) +"',"+
            String.valueOf(course[3])+",'"+String.valueOf(course[4])+"',"+String.valueOf(course[5])+");";
            stmt.executeUpdate(sql);            
        	con.commit();                           


       	} catch(SQLException ex) {
       		JOptionPane.showMessageDialog(null, "对象已经存在");
            System.err.println("SQLException: " + ex.getMessage());
            	

        }             		
	}
	
	//提取课程
	int SearchCourseDetail(Vector vc){
    	int i=0;
		try{
	
		sql = " SELECT * FROM COURSE";         
        	ResultSet rs = stmt.executeQuery(sql);
        	
            while(rs.next()){
            	Vector temp_v= new Vector(6);
            	  temp_v.add(0,(i+1));
                  temp_v.add(1,rs.getObject(1));
                  temp_v.add(2,rs.getObject(2));
                  temp_v.add(3,rs.getObject(3));
                  temp_v.add(4,rs.getObject(4));
                  temp_v.add(5,rs.getObject(5));
                  temp_v.add(6,rs.getObject(6));
                  vc.addElement(temp_v);
	          i++; 
	           }
	         }catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }    
        return i;   	
	}
	
	//按课程号提取课程信息
	void getCourseByCno(Object course[],String cno){
	  try{
	  	
		sql = " SELECT * FROM COURSE WHERE  cno=" +"'"+cno+"'";                 
        	ResultSet rs = stmt.executeQuery(sql);
        while(rs.next()){
        	course[2]=rs.getObject(1);
        	course[3]=rs.getObject(2);
        	course[4]=rs.getObject(3);
        	course[5]=rs.getObject(4);
        	course[6]=rs.getObject(5);
        	course[7]=rs.getObject(6);
        }	
        	
           
	         }catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
	}
	
	//按课程号查找课程信息
	int SearchCourseByCno(Vector vc,String cno){
    	int i=0;
		try{
	
	   sql = "SELECT * FROM FORCOURSE WHERE cno = '"+cno+"';";         	 	
       ResultSet rs = stmt.executeQuery(sql);     

        	
            while(rs.next()){
                  Vector temp_v= new Vector(9);
            	  temp_v.add(0,(i+1));
                  temp_v.add(1,rs.getObject(1));
                  temp_v.add(2,rs.getObject(2));
                  temp_v.add(3,rs.getObject(3));
                  temp_v.add(4,rs.getObject(4));
                  temp_v.add(5,rs.getObject(5));
                  temp_v.add(6,rs.getObject(6));
                  temp_v.add(7,rs.getObject(7));
                  temp_v.add(8,rs.getObject(8));
                  vc.addElement(temp_v);
	          i++; 
	           }
	         }catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }    
        return i;  
	}
	
	//按课程名查找课程信息
	int SearchCourseByCname(Vector vc,String cname){
    	int i=0;
		try{
	
	   sql = "SELECT * FROM FORCOURSE WHERE cname = '"+cname+"';";         	 	
       ResultSet rs = stmt.executeQuery(sql);     

        	
            while(rs.next()){
                  Vector temp_v= new Vector(9);
            	  temp_v.add(0,(i+1));
                  temp_v.add(1,rs.getObject(1));
                  temp_v.add(2,rs.getObject(2));
                  temp_v.add(3,rs.getObject(3));
                  temp_v.add(4,rs.getObject(4));
                  temp_v.add(5,rs.getObject(5));
                  temp_v.add(6,rs.getObject(6));
                  temp_v.add(7,rs.getObject(7));
                  temp_v.add(8,rs.getObject(8));
                  vc.addElement(temp_v);
	          i++; 
	           }
	         }catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }    
        return i;  
	}
	
	//按课班级查找课程信息
	int SearchCourseByClasses(Vector vc,String classes){
    	int i=0;
		try{
	
	   sql = "SELECT * FROM FORCOURSE WHERE sno IN (SELECT id FROM STUINFOR WHERE classes = '"+classes+"');";         	 	
       ResultSet rs = stmt.executeQuery(sql);     

        	
            while(rs.next()){
                  Vector temp_v= new Vector(9);
            	  temp_v.add(0,(i+1));
                  temp_v.add(1,rs.getObject(1));
                  temp_v.add(2,rs.getObject(2));
                  temp_v.add(3,rs.getObject(3));
                  temp_v.add(4,rs.getObject(4));
                  temp_v.add(5,rs.getObject(5));
                  temp_v.add(6,rs.getObject(6));
                  temp_v.add(7,rs.getObject(7));
                  temp_v.add(8,rs.getObject(8));
                  vc.addElement(temp_v);
	          i++;