select_chengji.java

用来管理在校学生的出勤．作息等的管理系统

/*
 * @author 黎龙飞 , 创建日期 2008-5-22
 *
 * Blog : http://lilongfei1030.blog.163.com
 */
package stu.view;

import java.awt.Color;
import java.awt.Dimension;
import java.awt.Font;
import java.awt.Toolkit;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.border.TitledBorder;
import javax.swing.table.TableModel;

import stu.util.ConnectDatabase;
import stu.util.getResults;

public class Select_chengji extends JFrame {

	private static final long serialVersionUID = 4885351932920031280L;
	
	JTable table;
	JScrollPane scrollPane;

	public Select_chengji() {
		super();
		setTitle("成绩查询");
		getContentPane().setLayout(null);
		setBounds(100, 100, 654, 509);
		setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
		setResizable(false);
		
		final Dimension screen = Toolkit.getDefaultToolkit().getScreenSize();
		final Dimension my = this.getSize();
		setLocationByPlatform(true);
		setLocation((screen.width - my.width) / 2,
				(screen.height - my.height) / 2);
		setVisible(true);
		
		final JLabel label = new JLabel();
		label.setFont(new Font("", Font.PLAIN, 14));
		label.setText("选择班级:");
		label.setBounds(50, 21, 66, 18);
		getContentPane().add(label);

		
		String sql = "select  DISTINCT 班级名 from  授课关系表";
		String[] columnName = null;
		Connection con = new ConnectDatabase().getResultString();
		try {
			Statement stm = con.createStatement();
			ResultSet rs = stm.executeQuery(sql);

			int num = 0;
			while (rs.next())
				num++;
			columnName = new String[num];
			int k = 0;
			rs = stm.executeQuery(sql);
			while (rs.next()) {
				columnName[k++] = (String) rs.getObject(1);
			}
			/*for (int i = 0; i < k; i++)
				System.out.println(columnName[i] + " ");*/

		} catch (SQLException e1) {
			//e1.printStackTrace();
		}

		
		
		final JComboBox comboBox = new JComboBox(columnName);
		comboBox.setFont(new Font("", Font.PLAIN, 14));
		comboBox.setBounds(122, 18, 121, 27);
		getContentPane().add(comboBox);

		final JLabel label_1 = new JLabel();
		label_1.setFont(new Font("", Font.PLAIN, 14));
		label_1.setText("选择课程:");
		label_1.setBounds(263, 21, 66, 18);
		getContentPane().add(label_1);

		sql = "select  DISTINCT 课程名 from  成绩表";
		String[] columnName1 = null;
		con = new ConnectDatabase().getResultString();
		try {
			Statement stm = con.createStatement();
			ResultSet rs = stm.executeQuery(sql);

			int num = 0;
			while (rs.next())
				num++;
			columnName1 = new String[num];
			int k = 0;
			rs = stm.executeQuery(sql);
			while (rs.next()) {
				columnName1[k++] = (String) rs.getObject(1);
			}
			/*for (int i = 0; i < k; i++)
				System.out.println(columnName1[i] + " ");*/

		} catch (SQLException e1) {
			//e1.printStackTrace();
		}

		
		final JComboBox comboBox_1 = new JComboBox(columnName1);
		comboBox_1.setFont(new Font("", Font.PLAIN, 14));
		comboBox_1.setBounds(335, 18, 121, 27);
		getContentPane().add(comboBox_1);

		final JLabel label_2 = new JLabel();
		label_2.setFont(new Font("", Font.PLAIN, 14));
		label_2.setBounds(458, 446, 178, 27);
		getContentPane().add(label_2);
		
		scrollPane = new JScrollPane();
		final TitledBorder titledBorder = new TitledBorder(null, "查询结果", TitledBorder.DEFAULT_JUSTIFICATION, TitledBorder.DEFAULT_POSITION, null, null);
		titledBorder.setTitleFont(new Font("宋体", Font.PLAIN, 15));
		scrollPane.setBorder(titledBorder);
		scrollPane.setBounds(10, 50, 627, 390);
		getContentPane().add(scrollPane);

		table = new JTable();
		scrollPane.setViewportView(table);
		
		final JButton button = new JButton();
		button.setFont(new Font("", Font.PLAIN, 14));
		button.addActionListener(new ActionListener() {
			public void actionPerformed(final ActionEvent e) {

				String sql = "select 学生表.学号, 姓名,考试成绩,出勤成绩,总成绩 from 成绩表,学生表 where 成绩表.学号=学生表.学号 and 成绩表.课程名='"
						+ comboBox_1.getSelectedItem() + "'and 学生表.班级名='"+comboBox.getSelectedItem()+"'";
				//System.out.println(sql);
				TableModel tableModel;

				try {
					tableModel = new getResults().Results(sql);
					table = new JTable(tableModel);
					
					scrollPane.setViewportView(table);
					table.setSelectionForeground(Color.RED);
	
				} catch (Exception e3) {
					//e3.printStackTrace();
				}
				/*System.out.println("行数: " + table.getRowCount() + " 列数: "
						+ table.getColumnCount());
				System.out.println("表中共有: " + table.getRowCount() + " 条记录");*/
				label_2.setText("共有: " + table.getRowCount() + " 条记录");
				if(table.getRowCount()==0)
					JOptionPane.showMessageDialog(null,"没有成绩记录","系统提示",JOptionPane.INFORMATION_MESSAGE);
			
			}
		});
		button.setText("确定");
		button.setBounds(485, 17, 66, 28);
		getContentPane().add(button);

		final JButton button_1 = new JButton();
		button_1.setFont(new Font("", Font.PLAIN, 14));
		button_1.addActionListener(new ActionListener() {
			public void actionPerformed(final ActionEvent e) {
				dispose();
			}
		});
		button_1.setText("退出");
		button_1.setBounds(570, 17, 66, 28);
		getContentPane().add(button_1);

		final JButton button_2 = new JButton();
		button_2.setFont(new Font("宋体", Font.PLAIN, 14));
		button_2.addActionListener(new ActionListener() {
			public void actionPerformed(final ActionEvent e) {

				String input = SelectFile.Test();
				//System.out.println(input);
				try {

					FileWriter output = new FileWriter(input);

					String sql = "select 学生表.学号, 姓名,考试成绩,出勤成绩,总成绩 from 成绩表,学生表 where 成绩表.学号=学生表.学号 and 成绩表.课程名='"
							+ comboBox_1.getSelectedItem()
							+ "'and 学生表.班级名='"
							+ comboBox.getSelectedItem() + "'";

					Connection con = new ConnectDatabase().getResultString();
					ResultSet rs = con.createStatement().executeQuery(sql);

					ResultSetMetaData data = rs.getMetaData();

					int col = data.getColumnCount();
					for (int i = 1; i <= col; i++) {
						if (i < col) {
							output.write(data.getColumnName(i) + "  ");
						} else {
							output.write(data.getColumnName(i) + "\r\n");
						}
					}

					while (rs.next()) {
						for (int i = 1; i <= col; i++) {
							output.write(rs.getObject(i) + "  ");
						}
						output.write("\r\n");
					}
					output.flush();
					output.close();
					JOptionPane.showMessageDialog(null, "数据已成功保存在 "+input+" 中","系统提示",JOptionPane.INFORMATION_MESSAGE);
				} catch (IOException e1) {
					//e1.printStackTrace();
				} catch (SQLException e2) {
					//e2.printStackTrace();
				}
			}
		});
		button_2.setText("导出数据");
		button_2.setBounds(272, 446, 106, 28);
		getContentPane().add(button_2);

		//
	}
}