gradepane.java

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

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.event.*;
import java.util.*;
import java.sql.*;
import java.awt.event.MouseAdapter;



public class GradePane extends JPanel implements ItemListener{
	
	JComboBox cb1=new JComboBox();
	JComboBox cb2=new JComboBox();
	JComboBox cb3=new JComboBox();
	JComboBox cb4=new JComboBox();

	JList list=new JList();
	
	DefaultListModel listModel=new DefaultListModel();
	
	 
	JPanel p1=new JPanel();
	JPanel p2=new JPanel();
//////////
	String sql; 
    Connection conn; 
    Statement stmt; 
    ResultSet rs;
    MouseListener mouseListener=new MouseAdapter(){
    	public void mouseClicked(MouseEvent e){
    		if(e.getClickCount()==2){	
    			
    			String s=list.getSelectedValue().toString();
    			new StudentHW(s);
    			
    			
    		     
    		}	
    	}
    };
    
	
	GradePane(){
		
		this.setLayout(null);

		initP1();initP2();
		this.add(p1);
		p1.setBounds(0, 0, 500, 300);
		
		this.add(p2);
		p2.setBounds(0, 300, 500, 300);
	}
	
	public void initP1(){
		JLabel l1=new JLabel("请选择...");
		JLabel l2=new JLabel("学号");
		JLabel l3=new JLabel("话题");
		JLabel l4=new JLabel("已提交第");
		JLabel l5=new JLabel("阶段    且");
		JLabel l6=new JLabel("选择结果：");
		p1.add(l1);p1.add(l2);p1.add(l3);p1.add(l4);p1.add(l5);p1.add(l6);
		p1.add(cb1);p1.add(cb2);p1.add(cb3);p1.add(cb4);
		
		p1.setLayout(null);
		l1.setBounds(50, 20, 100, 30);
		l2.setBounds(50, 70	 ,100,30);
		cb1.setBounds(130,70  , 100 ,30  );
		l3.setBounds(50,120  ,100  , 30 );
		cb2.setBounds(130,120  ,100  , 30 );
		l4.setBounds(50, 200 , 60 ,30  );
		cb3.setBounds(130, 200 , 50 , 30 );
		l5.setBounds(200, 200 , 60 , 30 );
		cb4.setBounds(300, 200 ,100  ,30  );
		l6.setBounds(50, 260,100, 30);
	    
		////////
		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 SNO from Student";
			rs=stmt.executeQuery(sql);
			
		    cb1.addItem("all");
			while(rs.next()){
			cb1.addItem(rs.getString(1));
			
			}
			
			conn.close();
			stmt.close();		
	       }catch(Exception e1){JOptionPane.showMessageDialog(null,"ri"+e1.getMessage());}
		String s[]={"第一个","第二个","第三个","已批改","未批改","一","二","三","四","五"};
		cb2.addItem("null");
		cb3.addItem("null");
		cb4.addItem("null");
		cb1.setEditable(true);
		cb2.addItem(s[0]);cb2.addItem(s[1]);cb2.addItem(s[2]);
		cb3.addItem(s[5]);cb3.addItem(s[6]);cb3.addItem(s[7]);cb3.addItem(s[8]);cb3.addItem(s[9]);
		cb4.addItem(s[3]);cb4.addItem(s[4]);
		cb1.addItemListener(this);
	    cb2.addItemListener(this);
	    cb3.addItemListener(this);
	    cb4.addItemListener(this);
		
	}
	public void initP2(){
	
		
		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,HomeWork.TOPIC from Student,HomeWork where Student.SNO=HomeWork.SNO";
			rs=stmt.executeQuery(sql);
			
               listModel=new DefaultListModel();
			
			while(rs.next()){
				String tem=rs.getString(1);
				listModel.addElement(tem);
		
				
				
			}
			list=new JList(listModel);
			
			
		  //  rs.close();
			conn.close();
			stmt.close();		
	       }catch(Exception e1){JOptionPane.showMessageDialog(null,e1.getMessage());}
	
		   
	        p2.setLayout(new BorderLayout());
	       list.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
	       JScrollPane sp=new JScrollPane(list);
	        p2.add(sp,"Center");
			JPanel pa=new JPanel();
			
			p2.add(pa,"East");
		//	list.addListSelectionListener(this);
			
			list.addMouseListener(mouseListener);
			
			
		 
		    
		   
		    
		
			
			
		
	
	
		
		
	}
	
	
	public void itemStateChanged(ItemEvent i){
		if(i.getSource()==cb1){
			if(cb1.getSelectedItem().equals("all")){
				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,HomeWork.TOPIC from Student,HomeWork where Student.SNO=HomeWork.SNO";
					rs=stmt.executeQuery(sql);
				
					listModel.removeAllElements();
					while(rs.next()){
						String tem=rs.getString(1);
						listModel.addElement(tem);
					 
					}

					  rs.close();
						conn.close();
						stmt.close();		
				       }catch(Exception e1){JOptionPane.showMessageDialog(null,e1.getMessage());}
					
			}
			else{
				
				String temID=cb1.getSelectedItem().toString().trim();
				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 from Student,HomeWork where Student.SNO=HomeWork.SNO AND Student.SNO='"+temID+"'";
					rs=stmt.executeQuery(sql);
					listModel.removeAllElements();
					while(rs.next()){
						String tem=rs.getString(1);
						listModel.addElement(tem);
					 
					}
					

						conn.close();
						stmt.close();		
				       }catch(Exception e1){JOptionPane.showMessageDialog(null,e1.getMessage());}
				
			}
		}
			if(i.getSource()==cb2){
			
				int index=cb2.getSelectedIndex();
				if(cb2.getSelectedIndex()==0){
					
				}else{
					index=cb2.getSelectedIndex();
					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 SNO from HomeWork where  HomeWork.TOPIC='"+index+"'";
						rs=stmt.executeQuery(sql);
						listModel.removeAllElements();
						while(rs.next()){
							String tem1=rs.getString(1);
							listModel.addElement(tem1);
						 
						}
						

							conn.close();
							stmt.close();		
					       }catch(Exception e1){JOptionPane.showMessageDialog(null,e1.getMessage());}
					
				}
				
			}
			if(i.getSource()==cb3){
				int index1,index2;
				String s=new String();
				if(cb3.getSelectedIndex()!=0){
					if(cb4.getSelectedIndex()==0){s="";}
					if(cb4.getSelectedIndex()==1){s=" IS NOT NULL";}
					if(cb4.getSelectedIndex()==2){s=" IS NULL";}
					
					
						index1=cb3.getSelectedIndex();
						String tem1="HW"+index1;
						String tem2="GRADE"+index1;
						String tem=new String();
						
						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 SNO from HomeWork where "+tem1+" IS NOT NULL  ";
							if(s.equals("")){sql=sql;}
							else{
							sql=sql+"AND "+tem2+s;}
							
							rs=stmt.executeQuery(sql);
							listModel.removeAllElements();
							while(rs.next()){
								tem=rs.getString(1);
								listModel.addElement(tem);	 }
								conn.close();
								stmt.close();		
						       }catch(Exception e1){JOptionPane.showMessageDialog(null,e1.getMessage());}
						
					}
				
			}
			if(i.getSource()==cb4){
				int index1,index2;
				String s=new String();
				if(cb4.getSelectedIndex()!=0){
					if(cb3.getSelectedIndex()!=0){
						index1=cb3.getSelectedIndex();
						index2=cb4.getSelectedIndex();
						if(index2==1){s=" IS NOT NULL";}
						if(index2==2){s=" IS NULL";}
						String tem1="HW"+index1;
						String tem2="GRADE"+index1;
						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 SNO from HomeWork where "+tem1+" IS NOT NULL AND ";
							sql=sql+tem2+s;
							
							rs=stmt.executeQuery(sql);
							listModel.removeAllElements();
							while(rs.next()){
								String tem3=rs.getString(1);
								listModel.addElement(tem3);	 }
								conn.close();
								stmt.close();		
						       }catch(Exception e1){JOptionPane.showMessageDialog(null,e1.getMessage());}
						
					}
					
				}
			}
		
		}

	
	
		
	
	//public void valueChanged(ListSelectionEvent e){
		
	//}
}