lookallpane.java

使用java连接server sql 2000 的一个小作业

import java.awt.*;
import java.awt.event.*;

import javax.swing.*;
import javax.swing.table.*;
import java.util.*;
import java.sql.*;


public class lookAllPane extends JPanel implements ActionListener
{
	JTable table;
    DefaultTableModel dtm;	
    Vector title=new Vector();
    JScrollPane sp;
    JPanel p1=new JPanel();
    
	JCheckBox c[]=new JCheckBox[9];
	JTextField tf=new JTextField(13);
	JButton b1=new JButton("确定");
	JButton b2=new JButton("查看");
	String Sa[]={"学号","姓名","话题","阶段一","阶段二","阶段三","阶段四","阶段五","总分"};
	
	
	String sql; 
    Connection conn; 
    Statement stmt; 
    ResultSet rs;

    lookAllPane(){
    	setLayout(null);
    	initP1();
    	initTable();
    	add(sp);
        add(p1);
    	p1.setBounds(0, 0, 500, 300);
     	sp.setBounds(0, 300, 500, 300);

    }
    public void initP1(){
    	
    	
    	
    	for(int i=0;i<9;i++){
    		c[i]=new JCheckBox(Sa[i],true);
    		p1.add(c[i]);
    	}
       p1.setLayout(null);
       c[0].setBounds(20, 20, 100, 30);
       c[1].setBounds(120, 20, 100, 30);
       c[2].setBounds(220, 20, 100, 30);
       
       c[3].setBounds(20, 70, 80, 30);
       c[4].setBounds(100, 70, 80, 30);
       c[5].setBounds(180, 70, 80, 30);
       c[6].setBounds(260, 70, 80, 30);
       c[7].setBounds(340, 70, 80, 30);
       c[8].setBounds(420,70,80,30);
      
       p1.add(b1);
       p1.add(b2);
       JLabel l1=new JLabel("学号");
       
       p1.add(l1);
       p1.add(tf);
       p1.add(b2);
       b1.setBounds(30, 150, 100, 30);
       
       l1.setBounds(20, 200, 50, 30);
       tf.setBounds(70, 200, 100, 30);
       b2.setBounds(170, 200, 100,30);
       
       b1.addActionListener(this);
       b2.addActionListener(this);
    	
    	
    	
    	
    }
    public void initTable(){
    	
    
    	for(int i=0;i<9;i++){
    		title.addElement(Sa[i]);
    	}
    	dtm=new DefaultTableModel(null,title);
    	table=new JTable(dtm);
    	sp=new JScrollPane(table);
    	try{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 
			String url="jdbc:odbc:SUMMIT"; 
			String user="sa"; 
			String password="198662"; 
			conn= DriverManager.getConnection(url,user,password);
			stmt=conn.createStatement(); 
			sql="select Student.SNO,Student.SNAME,TOPIC,GRADE1,GRADE2,GRADE3,GRADE4,GRADE5,GRADE1+GRADE2+GRADE3+GRADE4+GRADE5 from Student,HomeWork where Student.SNO=HomeWork.SNO";
		    rs=stmt.executeQuery(sql);
				while(rs.next()){
				Vector v=new Vector();
				
			for(int i=1;i<10;i++){
				v.addElement(rs.getString(i)); 
			}
    	    dtm.addRow(v);
				}
			rs.close();
			stmt.close();
			conn.close();
    	
    }catch(Exception e){JOptionPane.showMessageDialog(null, e.getMessage());}
    }
    
    
    
    public void actionPerformed(ActionEvent e){
    	if(e.getSource()==b1){
//    		//清楚表格
    		
    		dtm.setColumnCount(0);
    		int k=dtm.getRowCount();
    		while(k>0){
    		dtm.removeRow(--k);}
    		///////
    		int a[]={1,1,1,1,1,1,1,1,1};
    		for(int i=0;i<9;i++){
    			if(c[i].isSelected()){
    				a[i]=1;dtm.addColumn(Sa[i]);}
    			else{ a[i]=0;}
    		}
    		
    		///////
    		try{
    			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 
    			String url="jdbc:odbc:SUMMIT"; 
    			String user="sa"; 
    			String password="198662"; 
    			conn= DriverManager.getConnection(url,user,password);
    			stmt=conn.createStatement(); 
    			sql="select Student.SNO,Student.SNAME,TOPIC,GRADE1,GRADE2,GRADE3,GRADE4,GRADE5,GRADE1+GRADE2+GRADE3+GRADE4+GRADE5 from Student,HomeWork where Student.SNO=HomeWork.SNO";
    		    rs=stmt.executeQuery(sql);
    		    Vector v=new Vector();
    		    String tem=new String();
    		    int k1=0;
    		    int sum=0;
    				while(rs.next())
    				{
    					v=new Vector();
    	    			for(int i=0;i<9;i++){
    	    				if(c[i].isSelected()){
    	    					if(i<3){
    	    						v.addElement(rs.getString(i+1));
    	    						}
    	    					else{
    	    				       if(i!=8){
    	    				    	   tem=rs.getString(i+1);
    	    				    	
    	    				    	   if(tem==null){k1=0;}
    	    				    	   else{
    	    				           k1=Integer.parseInt(tem);}
    	    				    	   v.addElement(tem);
    	    				    	   sum=sum+k1;
    	    				    	
    	    				       }
    	    				       else{
    	    				    	  
    	    				    	   tem=String.valueOf(sum);
    	    				       
    	    				    	   v.addElement(tem);
    	    				       }
    	    					}
    	    				}
    	    			}
    	    			
    	    			dtm.addRow(v);
    	    			sum=0;
    	    			
    				}
    				rs.close();
    				stmt.close();
    				conn.close();
    				
    		}catch(Exception e1){JOptionPane.showMessageDialog(null, e1.getMessage());}
    	
    	}
    	if(e.getSource()==b2){
    		String tem;
    		tem=tf.getText().trim();
            //清楚表格
    		
    		dtm.setColumnCount(0);
    		int k=dtm.getRowCount();
    		while(k>0){
    		dtm.removeRow(--k);}
    		///////
    		int a[]={1,1,1,1,1,1,1,1,1};
    		for(int i=0;i<9;i++){
    			if(c[i].isSelected()){
    				a[i]=1;dtm.addColumn(Sa[i]);}
    			else{ a[i]=0;}
    		}
    		
    		///////
    		try{
    			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 
    			String url="jdbc:odbc:SUMMIT"; 
    			String user="sa"; 
    			String password="198662"; 
    			conn= DriverManager.getConnection(url,user,password);
    			stmt=conn.createStatement(); 
    			sql="select Student.SNO,Student.SNAME,TOPIC,GRADE1,GRADE2,GRADE3,GRADE4,GRADE5,GRADE1+GRADE2+GRADE3+GRADE4+GRADE5 from Student,HomeWork where Student.SNO=HomeWork.SNO";
    		   sql=sql+" AND Student.SNO='"+tem+"'";
    			rs=stmt.executeQuery(sql);
    				
    				Vector v=new Vector();
    				rs.next();
    			for(int i=1;i<10;i++){
    				v.addElement(rs.getString(i)); 
    			}
        	    dtm.addRow(v);
    				
    			rs.close();
    			stmt.close();
    			conn.close();
        	
        }catch(Exception e2){JOptionPane.showMessageDialog(null, "没有这个学号吧");}
    		
    	}
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    }
}