creatmytable.java

数据库连接用的jar包

package com.test;

import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

public class CreatMytable extends JFrame implements ActionListener {
	private Connection conn;

	private Statement sql;

	private ResultSet rs;

	private JButton b1, b2, b3, b4, b5, b6;

	private Vector vt, va;

	private DefaultTableModel dm;

	private JTable ta;

	private JLabel l1;

	private int id, age;

	private String name, sex1;

	private Float grade;

	private JLabel max, min, avggrade, count;

	private String max1, min1, avg1, count1;

	private JPanel p1, p2, p3, p4, p5, p6;

	public CreatMytable() {
		super();
		this.setBounds(150, 50, 600, 600);
		this.setDefaultCloseOperation(3);
		p1 = new JPanel();
		l1 = new JLabel("欢迎您进入表的操作！");
		l1.setForeground(Color.blue);
		l1.setFont(new Font("楷体",1,22));
		p1.setLayout(new BorderLayout());
		p4 = new JPanel();
		p4.add(l1);
		p5 = new JPanel();
		max = new JLabel("最高分");
		max.setFont(new Font("楷体",1,18));
		max.setForeground(Color.magenta);
		min = new JLabel("最低分");
		min.setFont(new Font("楷体",1,18));
		min.setForeground(Color.magenta);
		avggrade = new JLabel("平均分");
		avggrade.setFont(new Font("楷体",1,18));
		avggrade.setForeground(Color.magenta);
		count = new JLabel("总分数");
		count.setFont(new Font("楷体",1,18));
		count.setForeground(Color.magenta);
		p5.add(max);
		p5.add(min);
		p5.add(avggrade);
		p5.add(count);
		p5.setVisible(false);
		p6 = new JPanel();
		p6.add(count);
		p6.setVisible(false);
		p1.add(p4, "North");
		p1.add(p5, "Center");
		p1.add(p6, "South");

		
		p2 = new JPanel();
		vt = new Vector();
		vt.add("编号");
		vt.add("姓名");
		vt.add("年龄");
		vt.add("成绩");
		vt.add("性别");
		va = new Vector();
		dm = new DefaultTableModel(va, vt);
		ta = new JTable(dm);
		p2.add(new JScrollPane(ta));

		p3 = new JPanel();
		b1 = new JButton("按性别查询");
		b2 = new JButton("计算成绩");
		b3 = new JButton("总计人数");
		b4 = new JButton("条件查询");
		b5 = new JButton("插入记录");
		b6 = new JButton("按姓名删除");
		p3.add(b1);
		p3.add(b2);
		p3.add(b3);
		p3.add(b4);
		p3.add(b5);
		p3.add(b6);

		this.getContentPane().add(p1, "North");
		this.getContentPane().add(p2, "Center");
		this.getContentPane().add(p3, "South");
		b1.addActionListener(this);
		b2.addActionListener(this);
		b3.addActionListener(this);
		b4.addActionListener(this);
		b5.addActionListener(this);
		b6.addActionListener(this);
		this.addWindowListener(new WindowAdapter() {

			public void windowClosing(WindowEvent e) {
				try {
					sql.close();
					conn.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}

			}

		});
		this.setVisible(true);
		this.validate();
		inDB();
		createtabel();
	}

	// 创建表
	private void createtabel() {
		try {
			// 如果系统存在表把表删除在建表
			String str1 = "if exists (select name from sysobjects where name='ustudent')"
					+ " drop table ustudent";
			sql.execute(str1);

			// 建表
			String ctable = "create table ustudent " + "( "
					+ "uid int identity(1,1) primary key," + "uname char(10),"
					+ "uage int ," + "usex char(2)," + "ugrade float )";
			sql.execute(ctable);
			String str = "insert into ustudent (uname,uage,usex,ugrade) values('aa',20,'女',75)"
					+ "insert into ustudent (uname,uage,usex,ugrade) values('bb',21,'女',85)"
					+ "insert into ustudent (uname,uage,usex,ugrade) values('cc',23,'男',90)"
					+ "insert into ustudent (uname,uage,usex,ugrade) values('dd',24,'女',45)"
					+ "insert into ustudent (uname,uage,usex,ugrade) values('ee',12,'男',64)"
					+ "insert into ustudent (uname,uage,usex,ugrade) values('ff',16,'男',80)"
					+ "insert into ustudent (uname,uage,usex,ugrade) values('gg',14,'女',74)";
			sql.execute(str);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			rs = sql.executeQuery("select * from ustudent");
			query();
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}

	}

	private void query() {
		try {

			while (rs.next()) {
				id = rs.getInt(1);
				name = rs.getString(2);
				age = rs.getInt(3);
				sex1 = rs.getString(4);
				grade = rs.getFloat(5);
				Vector vl = new Vector();
				vl.add(id);
				vl.add(name);
				vl.add(age);
				vl.add(sex1);
				vl.add(grade);
				dm.addRow(vl);
			}
			rs.close();

		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}

	}

	private void inDB() {
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			conn = DriverManager
					.getConnection("jdbc:odbc:Driver={SQL Server};Server=127.0.0.1;uid=sa;pwd=123;Database=user");
			if (!conn.isClosed())
				JOptionPane.showMessageDialog(null, "DBconnection is ok");
			sql = conn.createStatement(1005, 1008);
		} catch (ClassNotFoundException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}

	}

	public static void main(String[] args) {
		new CreatMytable();
	}

	public void actionPerformed(ActionEvent e) {
		if (e.getSource() == b1) {
			try {

				// 判断存储过程是否存在
				String exist = "if exists (select name from sysobjects where name='selectsex')"
						+ " drop procedure selectsex";
				sql.execute(exist);

				// 创建存储过程
				String str = "create procedure selectsex @sex char(2) "
						+ "as select * from ustudent where usex=@sex ";
				sql.execute(str);
				CallableStatement pro = conn.prepareCall("{call selectsex(?)}");
				String s = JOptionPane.showInputDialog("请输入查询的条件");
				pro.setString(1, s);
				rs = pro.executeQuery();
				dm.setRowCount(0);
				query();
			} catch (SQLException e1) {

				e1.printStackTrace();
			}
		}

		// 计算最高分，最低分，平均分
		if (e.getSource() == b2) {

			try {
				// 判断存储过程是否存在
				String exist = "if exists (select name from sysobjects where name='selectcount1')"
						+ " drop procedure selectcount1";
				sql.execute(exist);

				// 创建存储过程
				String str = "create procedure selectcount1 "
						+ "as "
						+ "select max(ugrade) as 最高分,min(ugrade) as 最低分,avg(ugrade) as 平均分 from ustudent";
				sql.execute(str);
				CallableStatement pro = conn.prepareCall("{call selectcount1}");
				rs = pro.executeQuery();
				while (rs.next()) {
					max1 = rs.getInt(1) + "";
					min1 = rs.getInt(2) + "";
					avg1 = rs.getInt(3) + "";
					max.setText("最高分:" + max1);
					min.setText("最低分:" + min1);
					avggrade.setText("平均分:" + avg1);
					p5.setVisible(true);
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}

		}
		if (e.getSource() == b3) {
			try {
				// 判断存储过程是否存在
				String exist = "if exists (select name from sysobjects where name='selectcount2')"
						+ " drop procedure selectcount2";
				sql.execute(exist);

				// 创建存储过程
				String str = "create procedure selectcount2" + " as "
						+ " select count(*) as 总人数 from ustudent ";
				sql.execute(str);
				CallableStatement pro = conn.prepareCall("{call selectcount2}");
				rs = pro.executeQuery();
				while (rs.next()) {
					count1 = rs.getInt(1) + "";
					count.setText("总人数: " + count1);
					p6.setVisible(true);
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}

		}
		if (e.getSource() == b4) {

			try {
				// 判断存储过程是否存在
				String exist = "if exists (select name from sysobjects where name='selectage')"
						+ " drop procedure selectage";
				sql.execute(exist);

				// 创建存储过程
				String str = "create procedure selectage "
						+ "as "
						+ " select * from ustudent where uage>20 and usex='男' and ugrade>(select avg(ugrade) from ustudent)";
				sql.execute(str);
				CallableStatement pro = conn.prepareCall("{call selectage}");
				rs = pro.executeQuery();
				dm.setRowCount(0);
				query();
			} catch (SQLException e1) {
				// TODO 自动生成 catch 块
				e1.printStackTrace();
			}
		}

		if (e.getSource() == b5) {
			try {

				// 判断存储过程是否存在
				String exist = "if exists (select name from sysobjects where name='insertall')"
						+ " drop procedure insertall";
				sql.execute(exist);

				// 创建存储过程
				String str = "create procedure insertall @name char(10), @age int, @sex char(2), @grade float "
						+ "as insert into ustudent (uname,uage,usex,ugrade) values(@name,@age,@sex,@grade) ";
				sql.execute(str);
				CallableStatement pro = conn.prepareCall("{call insertall(?,?,?,?)}");
				String n = JOptionPane.showInputDialog("请输入姓名");
				JComboBox box1;
	    		box1=new JComboBox();
	    		box1.addItem("13");
	    		box1.addItem("14");
	    		box1.addItem("15");
	    		box1.addItem("16");
	    		box1.addItem("17");
	    		box1.addItem("18");
	    		box1.addItem("19");
	    		box1.addItem("20");
	    		box1.addItem("21");
	    		box1.addItem("22");
	    		box1.addItem("23");
	    		box1.addItem("24");
	    		box1.addItem("25");
	    		box1.addItem("26");
	    		int stat1=JOptionPane.showConfirmDialog(null,box1,"请选择年龄",JOptionPane.YES_NO_OPTION); 
	    		String a = null;
	    		if(stat1==JOptionPane.YES_OPTION){
	    		   a=box1.getSelectedItem().toString();
	    		}
				JComboBox box2;
	    		box2=new JComboBox();
	    		box2.addItem("女");
	    		box2.addItem("男");
	    		int stat2=JOptionPane.showConfirmDialog(null,box2,"请选择性别",JOptionPane.YES_NO_OPTION); 
	    		String s = null;
	    		if(stat2==JOptionPane.YES_OPTION){
	    		   s=box2.getSelectedItem().toString();
	    		}
				String g=JOptionPane.showInputDialog("请输入成绩");
				int aa=Integer.parseInt(a);
				Float gg=Float.parseFloat(g);
				pro.setString(1, n);
				pro.setInt(2,aa);
				pro.setString(3,s);
				pro.setFloat(4,gg);
				pro.execute();
				dm.setRowCount(0);
				rs = sql.executeQuery("select * from ustudent");
			    query();
				
			} catch (SQLException e1) {

				e1.printStackTrace();
			}catch(NumberFormatException e1){
				System.out.println("请输入正确的数字在插入");
			}
			
		}
		
		
		if(e.getSource()==b6){
			try {

				// 判断存储过程是否存在
				String exist = "if exists (select name from sysobjects where name='deletename')"
						+ " drop procedure deletename";
				sql.execute(exist);

				// 创建存储过程
				String str = "create procedure deletename @name char(10) "
						+ "as delete ustudent where uname=@name ";
				sql.execute(str);
				CallableStatement pro = conn.prepareCall("{call deletename(?)}");
				String n = JOptionPane.showInputDialog("请输入要删除人的名字");
				pro.setString(1, n);
				pro.execute();
				dm.setRowCount(0);
				rs = sql.executeQuery("select * from ustudent");
			    query();
			} catch (SQLException e1) {

				e1.printStackTrace();
			}
		}

		
		
	}

}