select.java

简单java成绩管理 提供添加 统计 修改功能

import java.awt.GraphicsConfiguration;
import java.awt.HeadlessException;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import javax.swing.DefaultComboBoxModel;
import javax.swing.JCheckBox;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;


public class select extends JPanel implements ActionListener{
	JTable table;
	JScrollPane sc;
	databasemanager dbm;
	
	JLabel label_num;
	JTextField textfield_num;
	
	JCheckBox checkbox_1;
	JCheckBox checkbox_2;
	JCheckBox checkbox_3;
	JCheckBox checkbox_4;
	
	JComboBox combobox;
	DefaultComboBoxModel combobox_model;
	public select(databasemanager dbm){
		this.dbm=dbm;
		init();
	}
	public void init(){
		
		this.setLayout(null);
		
		{
			label_num=new JLabel("输入学号:");
			label_num.setFont(allset.font_label);
			label_num.setBounds(20, 10,60,22);
			this.add(label_num);
		}
		{
			textfield_num=new JTextField();
			textfield_num.setBounds(80,10,80,22);
			this.add(textfield_num);
			textfield_num.addActionListener(this);
		}
		{
			checkbox_1=new JCheckBox("包含1班");
			checkbox_2=new JCheckBox("包含2班");
			checkbox_3=new JCheckBox("包含3班");
			checkbox_4=new JCheckBox("包含4班");
			
			checkbox_1.setSelected(true);
			checkbox_2.setSelected(true);
			checkbox_3.setSelected(true);
			checkbox_4.setSelected(true);
			
			checkbox_1.setFont(allset.font_label);
			checkbox_2.setFont(allset.font_label);
			checkbox_3.setFont(allset.font_label);
			checkbox_4.setFont(allset.font_label);
			
			
			checkbox_1.addActionListener(this);
			checkbox_2.addActionListener(this);
			checkbox_3.addActionListener(this);
			checkbox_4.addActionListener(this);
			
			checkbox_1.setBounds(10,35,90,22);
			this.add(checkbox_1);
			
			checkbox_2.setBounds(110,35,90,22);
			this.add(checkbox_2);
			
			checkbox_3.setBounds(210,35,90,22);
			this.add(checkbox_3);
			
			checkbox_4.setBounds(310,35,90,22);
			this.add(checkbox_4);
			
			
		}
		{
			combobox=new JComboBox();
			
			combobox_model=new DefaultComboBoxModel(new String[]{
					"总成绩","JAVA语言","C语言","数据库","操作系统","日语"
			});
			
			combobox.setBounds(280, 10, 100, 22);
			combobox.setFont(allset.font_label);
			combobox.setModel(combobox_model);
			
			combobox.addActionListener(this);
			add(combobox);
			
		}
		sc=new JScrollPane();
		sc.setBounds(10,70,450,200);
		this.add(sc);
		
		
	}
	public static void main(String[] args) {
		JFrame f=new JFrame("dsf");
		f.setSize(480,350);
		
		
		select se=new select(new databasemanager());
		f.getContentPane().add(se);
		f.setVisible(true);
		
		
	}
	public String createsql(){
		String sql="";
		
		sql+="select rank() over(order by ?? desc) as 名次,学生信息表.学号,姓名,??"+"\n"+
				"from (select ??,学号"+"\n"+
					"from 成绩表 ??)"+"\n"+
					"as b inner join 学生信息表 on b.学号=学生信息表.学号";
		String[]ss=new String[4];
		ss[0]="";
		ss[1]="";
		ss[2]="";
		ss[3]="";
		String scheck;//班级代码in()使用
		scheck="in(";
		boolean hasfront=false;
		if(checkbox_1.isSelected()){
			if(hasfront==true)scheck+=",";
			scheck+="'070101'";
			hasfront=true;
		}
		if(checkbox_2.isSelected()){
			if(hasfront==true)scheck+=",";
			scheck+="'070102'";
			hasfront=true;
		}
		if(checkbox_3.isSelected()){
			if(hasfront==true)scheck+=",";
			scheck+="'070103'";
			hasfront=true;
		}
		if(checkbox_4.isSelected()){
			if(hasfront==true)scheck+=",";
			scheck+="'070104'";
			hasfront=true;
		}
		scheck+="))";
		if(combobox.getSelectedIndex()==0){
			ss[0]+="总成绩";
			ss[1]+="b.总成绩,b.平均分";
			ss[2]+="avg(成绩) as 平均分,sum(成绩) as 总成绩";
			ss[3]+="group by 学号 "+
					"having 学号 in(select 学号 from 班级表 ";
					//"where 班级代码  ";
			
			if(checkbox_1.isSelected()||checkbox_2.isSelected()||
					checkbox_3.isSelected()||checkbox_4.isSelected()){
				ss[3]+="where 班级代码  "+scheck;
			}
			else{
				ss[3]+=")";
			}
		}
		else{
			ss[0]+=combobox_model.getElementAt(combobox.getSelectedIndex());
			ss[1]+="b."+combobox_model.getElementAt(combobox.getSelectedIndex());
			ss[2]+="成绩 as "+combobox_model.getElementAt(combobox.getSelectedIndex());
			if(hasfront==true){
				ss[3]+="where 课程代码=(select 课程代码 from 课程信息表 "+
			
									"where 课程名称='";
				
				ss[3]+=combobox_model.getElementAt(combobox.getSelectedIndex())+"'";
				
				ss[3]+="and 学号 in(select 学号 from 班级表 where 班级代码 ";
				if(checkbox_1.isSelected()||checkbox_2.isSelected()||
						checkbox_3.isSelected()||checkbox_4.isSelected()){
					ss[3]+=scheck+")";
				}
			}
			
		}
		sql=dbm.createsql(sql,ss);
		
		if(textfield_num.getText().length()>0&&textfield_num.getText().length()<11){
			String shead="select * from (";
			shead+=sql;
			shead+=")as c where c.学号 like '"+textfield_num.getText()+"%'";
			sql=shead;
		}
		
		//System.out.println(sql);
		return sql;
		
	}
	public void actionPerformed(ActionEvent ae) {
		String sql=createsql();
		remove(sc);
		sc=dbm.makescrollpane(dbm.select(sql));
		
		sc.setBounds(10,70,450,200);
		this.add(sc);
		this.repaint();
	}

}