jdbcd.java

java编写的比较全的SQL_server数据库的操作,有界面,能根据数据的SQL语句操作数据库

import java.sql.*;
import java.util.*;
import javax.swing.*;
import javax.swing.table.*;
import java.awt.*;
import java.awt.event.*;
public class JDBCD extends JFrame implements ActionListener
{
	Dialog dialog1=new Dialog(this,"插入数据",false);
	Dialog dialog2=new Dialog(this,"删除数据",false);
	Dialog dialog3=new Dialog(this,"查询数据",false);
	Dialog dialog4=new Dialog(this,"修改数据",false);
	JButton d1bt,d2bt,d3bt,d4bt;
	JMenuBar bar;
	JMenu menu1,menu2;
	JMenuItem it1,it2,it3,it4,it5,it6; 
	JTable table;
	Choice chos,chos1,chos2;
	Container cont;
	JTextField fid,fname,fsex,faddress,fqq,fdelete,fselect,fupdateold,fupdatenew;
	
	Connection con;
	Statement  state;
	ResultSet  resultset;
	public JDBCD()
	{
		super("数据库查询 清海制作");
		cont=getContentPane();
        chos=new Choice();
        chos.add("id");
        chos.add("qqname");
        chos.add("sex");
        chos.add("address");
        chos.add("qq");
        
        menu1=new JMenu("文件");                         //菜单的定义
        it1=new JMenuItem("更新");
        it2=new JMenuItem("退出");
        menu1.add(it1); menu1.addSeparator();menu1.add(it2);
        
        menu2=new JMenu("操作");
        it3=new JMenuItem("插入");
        it4=new JMenuItem("查询");
        it5=new JMenuItem("删除");
        it6=new JMenuItem("修改");
        menu2.add(it3); menu2.add(it4); menu2.add(it5);menu2.add(it6);
        
        bar=new JMenuBar();
        bar.add(menu1); bar.add(menu2);
        cont.add(bar,BorderLayout.NORTH); 
        
        table=new JTable();
        cont.add(table,BorderLayout.CENTER);
        
        Box box1=Box.createHorizontalBox();              //插入面版的定义
        JLabel l1=new JLabel("请输入编号");
        fid=new JTextField(20);
        box1.add(l1); box1.add(fid);
        
        Box box2=Box.createHorizontalBox();
        JLabel l2=new JLabel("请输入姓名");
        fname=new JTextField(20);
        box2.add(l2); box2.add(fname);
        
        Box box3=Box.createHorizontalBox();
        JLabel l3=new JLabel("请输入性别");
        fsex=new JTextField(20);
        box3.add(l3); box3.add(fsex);
        
        Box box4=Box.createHorizontalBox();
        JLabel l4=new JLabel("请输入地址");
        faddress=new JTextField(20);
        box4.add(l4); box4.add(faddress);
        
        Box box5=Box.createHorizontalBox();
        JLabel l5=new JLabel("请输入Q Q");
        fqq=new JTextField(20);
        box5.add(l5); box5.add(fqq);
        
        Box box6=Box.createHorizontalBox();
        JLabel l6=new JLabel("请按确定->");
        d1bt=new JButton("确定");
        box6.add(l6); box6.add(d1bt);
        
        Box box7=Box.createVerticalBox();
        box7.add(box1);
        box7.createVerticalStrut(8);
        box7.add(box2);
        box7.createVerticalStrut(8);
        box7.add(box3);
        box7.createVerticalStrut(8);
        box7.add(box4);
        box7.createVerticalStrut(8);
        box7.add(box5);
        box7.createVerticalStrut(8);
        box7.add(box6);
        JPanel pbox7=new JPanel();
        pbox7.add(box7);
        dialog1.add(pbox7,BorderLayout.CENTER);
        
        Box box8=Box.createHorizontalBox();              //删除dialog面版的定义
        JLabel l8=new JLabel("请输入删除数据的编号");
        fdelete=new JTextField(20);
        box8.add(l8); box8.add(fdelete);
        
        Box box9=Box.createHorizontalBox();
        JLabel l9=new JLabel("请按确定->");
        d2bt=new JButton("确定");
        box9.add(l9); box9.add(d2bt);
        
        Box box10=Box.createVerticalBox();
        box10.add(box8);
        box10.createVerticalStrut(8);
        box10.add(box9);
        JPanel pbox10=new JPanel();
        pbox10.add(box10);
        dialog2.add(pbox10,BorderLayout.CENTER);
        
        Box box11=Box.createHorizontalBox();              //查询dialog面版的定义
        JLabel jchos1=new JLabel("请选择查询的方式");
        box11.add(jchos1); box11.add(chos);
        Box box12=Box.createHorizontalBox();
        JLabel jchos2=new JLabel("请输入查询的字段");
        fselect=new JTextField(15);
        box12.add(jchos2); box12.add(fselect);
        Box box13=Box.createVerticalBox();
        box13.add(box11);
        box13.createVerticalStrut(8);
        box13.add(box12);
        d3bt=new JButton("确定");
        JPanel pd3bt=new JPanel();
        JPanel pbox13=new JPanel();
        pbox13.add(box13); pd3bt.add(d3bt);
        JPanel pdialog3=new JPanel();
        pdialog3.add(pbox13); pdialog3.add(pd3bt);
        dialog3.add(pdialog3,BorderLayout.CENTER);
        
        Box box14=Box.createHorizontalBox();               //修改dialog面版的定义
        JLabel jupold=new JLabel("请输入修改条件");
        chos1=new Choice();
        chos1.add("id");
        chos1.add("qqname");
        chos1.add("sex");
        chos1.add("address");
        chos1.add("qq");
        fupdateold=new JTextField(15);
        box14.add(jupold);box14.add(chos1);box14.add(fupdateold);
        Box box15=Box.createHorizontalBox();
        JLabel jupnew=new JLabel("请输入新的数据");
        chos2=new Choice();
        chos2.add("id");
        chos2.add("qqname");
        chos2.add("sex");
        chos2.add("address");
        chos2.add("qq");
        fupdatenew=new JTextField(15);
        box15.add(jupnew);box15.add(chos2);box15.add(fupdatenew);
        Box box16=Box.createHorizontalBox();
        JLabel updatebt=new JLabel("请按确定->");
        d4bt=new JButton("确定");
        box16.add(updatebt); box16.add(d4bt);
        Box box17=Box.createVerticalBox();
        box17.add(box14);
        box17.createVerticalStrut(10);
        box17.add(box15);
        box17.createVerticalStrut(10);
        box17.add(box16);
        JPanel pbox17=new JPanel();
        pbox17.add(box17);
        dialog4.add(pbox17,BorderLayout.CENTER);
        
        it1.addActionListener(this);
        it2.addActionListener(this);
        it3.addActionListener(this);
        it4.addActionListener(this);
        it5.addActionListener(this);
        it6.addActionListener(this);
        
        dialog1.setBounds(150,150,300,300);
        dialog2.setBounds(200,150,400,100);
        dialog3.setBounds(200,150,400,300);
        dialog4.setBounds(200,150,400,300);
		setBounds(100,100,500,400);
		setVisible(true);
		
		try                                                //连接数据库
		{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			con=DriverManager.getConnection("jdbc:odbc:grzl","sa","");
		}
		catch(ClassNotFoundException e1)
		{
			JOptionPane.showMessageDialog(this,e1.getMessage());
		}
		catch(SQLException e2)
		{
			JOptionPane.showMessageDialog(this,e2.getMessage());
		}
		
		dialog1.addWindowListener(new WindowAdapter()          //关闭dialog
		{
			public void windowClosing(WindowEvent ew)
			{
				dialog1.dispose();
			}
		});
		dialog2.addWindowListener(new WindowAdapter()
		{
			public void windowClosing(WindowEvent ew)
			{
				dialog2.dispose();
			}
		});
		dialog3.addWindowListener(new WindowAdapter()
		{
			public void windowClosing(WindowEvent ew)
			{
				dialog3.dispose();
			}
		});
		dialog4.addWindowListener(new WindowAdapter()
		{
			public void windowClosing(WindowEvent ew)
			{
				dialog4.dispose();
			}
		});
		d1bt.addActionListener(new java.awt.event.ActionListener()    //dialog上的"确认"的监听器
		{
			public void actionPerformed(ActionEvent e)
			{
				d1bt_actionPerformed(e);
			}
		});
		
		d2bt.addActionListener(new java.awt.event.ActionListener()
		{
			public void actionPerformed(ActionEvent e)
			{
				d2bt_actionPerformed(e);
			}
		});
		d3bt.addActionListener(new java.awt.event.ActionListener()
		{
			public void actionPerformed(ActionEvent e)
			{
				d3bt_actionPerformed(e);
			}
		});
		d4bt.addActionListener(new java.awt.event.ActionListener()
		{
			public void actionPerformed(ActionEvent e)
			{
				d4bt_actionPerformed(e);
			}
		});
	}
	public void actionPerformed(ActionEvent e)     //通过ActionEvnet调用相应的Dialog
	{
		if(e.getSource()==it1)
		{
			try
			{
				state=con.createStatement();
				resultset=state.executeQuery("select * from message order by id");
				displayResultSet(resultset);
			}
			catch(SQLException e2)
			{
				JOptionPane.showMessageDialog(this,e2.getMessage());
			}	
		}
		else if(e.getSource()==it2)
		{
			System.exit(0);
		}
		else if(e.getSource()==it3)
		{
			dialog1.show();
		}
		else if(e.getSource()==it4)
		{
			dialog3.show();
		}
		else if(e.getSource()==it5)
		{
			dialog2.show();
		}
		else if(e.getSource()==it6)
		{
			dialog4.show();
		}
	}
	void d1bt_actionPerformed(ActionEvent e)    //插入数据事件
	{
		try
		{
			state=con.createStatement();
			String f1=fid.getText();
			String f2=fname.getText();
			String f3=fsex.getText();
			String f4=faddress.getText();
			String f5=fqq.getText();
			int n=JOptionPane.showConfirmDialog(this,"确认要插入数据吗?","确认对话框",JOptionPane.YES_NO_OPTION);
			if(n==JOptionPane.YES_OPTION)
			{
				state.executeUpdate("insert into message values('"+f1+"','"+f2+"','"+f3+"','"+f4+"','"+f5+"')");
			    resultset=state.executeQuery("select * from message order by id");
			    displayResultSet(resultset);
			    fid.setText("");
				fname.setText("");
				fsex.setText("");
				faddress.setText("");
				fqq.setText("");
			    dialog1.dispose();
			}
			else if(n==JOptionPane.NO_OPTION)
			{
				fid.setText("");
				fname.setText("");
				fsex.setText("");
				faddress.setText("");
				fqq.setText("");
				dialog1.dispose();
			}
		}
		catch(SQLException e2)
		{
			JOptionPane.showMessageDialog(this,e2.getMessage());
		}
	}
	void d2bt_actionPerformed(ActionEvent e)        //删除数据事件
	{
		try
		{
			state=con.createStatement();
			String s=fdelete.getText();
			int n=JOptionPane.showConfirmDialog(this,"确认要删除编号"+s+"的数据吗?","确认对话框",JOptionPane.YES_NO_OPTION);
			if(n==JOptionPane.YES_OPTION)
			{
				state.executeUpdate("delete from message where id="+s);
			    resultset=state.executeQuery("select * from message order by id");
			    displayResultSet(resultset);
			    fdelete.setText("");
			    dialog2.dispose();
			}
			else if(n==JOptionPane.NO_OPTION)
			{
				fdelete.setText("");
				dialog2.dispose();
			}
		}
		catch(SQLException e2)
		{
			JOptionPane.showMessageDialog(this,e2.getMessage());
		}
	}
	void d3bt_actionPerformed(ActionEvent e)           //查询数据事件
	{
		String slename1=chos.getSelectedItem();
		String slename2=fselect.getText();
		if(slename1=="id" || slename1=="qq")          //当查询的字段为数字时
		{
			try
			{
				state=con.createStatement();
				resultset=state.executeQuery("select * from message where "+slename1+"="+slename2);
				displayResultSet(resultset);
			}
			catch(SQLException e2)
			{
				JOptionPane.showMessageDialog(this,e2.getMessage());
			}
		}
		else                                          //当查询的字段
		{
			try
			{
				state=con.createStatement();
				resultset=state.executeQuery("select * from message where "+slename1+"='"+slename2+"'");
				displayResultSet(resultset);
			}
			catch(SQLException e2)
			{
				JOptionPane.showMessageDialog(this,e2.getMessage());
			}
		}
		return;	
	}
	void d4bt_actionPerformed(ActionEvent e)           //修改数据事件
	{
		String selold=chos1.getSelectedItem();
		String textold=fupdateold.getText();
		String selnew=chos2.getSelectedItem();
		String textnew=fupdatenew.getText();
		if(selold=="id" || selold=="qq")               //当查询条件为数字时
		{
			if(selnew=="id" || selnew=="qq")           //当查询条件和修改字段都是数字时
			{
				try
				{
					state=con.createStatement();
					state.executeUpdate("update message set "+selnew+"="+textnew+" where "+selold+"="+textold);
					resultset=state.executeQuery("select * from message order by id");
					displayResultSet(resultset);
				}
				catch(SQLException e2)
				{
					JOptionPane.showMessageDialog(this,e2.getMessage());
				}
			}
			else                                     //当查询条件是数字 而修改字段不是数字时
			{
				try
				{
					state=con.createStatement();
					state.executeUpdate("update message set "+selnew+"='"+textnew+"' where "+selold+"="+textold);
					resultset=state.executeQuery("select * from message order by id");
					displayResultSet(resultset);
				}
				catch(SQLException e2)
				{
					JOptionPane.showMessageDialog(this,e2.getMessage());
				}
			}
		}
		else
		{
			if(selnew=="id" || selnew=="qq")        //当查询条件不是数字而修改字段为数字时
			{
				try
				{
					state=con.createStatement();
					state.executeUpdate("update message set "+selnew+"="+textnew+" where "+selold+"='"+textold+"'");
					resultset=state.executeQuery("select * from message order by id");
					displayResultSet(resultset);
				}
				catch(SQLException e2)
				{
					JOptionPane.showMessageDialog(this,e2.getMessage());
				}
			}
			else                                   //当查询条件和修改字段都不是数字时
			{
				try
				{
					state=con.createStatement();
					state.executeUpdate("update message set "+selnew+"='"+textnew+"' where "+selold+"='"+textold+"'");
					resultset=state.executeQuery("select * from message order by id");
					displayResultSet(resultset);
				}
				catch(SQLException e2)
				{
					JOptionPane.showMessageDialog(this,e2.getMessage());
				}
			}
		}
	}
	public void displayResultSet(ResultSet res)throws SQLException
	{
		boolean resc=res.next();
		if(!resc)
		{
			JOptionPane.showMessageDialog(this,"没有查询的结果");
			return;
		}
		Vector cols=new Vector();
		Vector rows=new Vector();
		try
		{
			ResultSetMetaData resm=res.getMetaData();         //获取字段的名称 
			for(int i=1;i<=resm.getColumnCount();i++)
			cols.addElement(resm.getColumnName(i));
			do
			{
				rows.addElement(getNextRow(res,resm));
			}
			while(res.next());
			table=new JTable(rows,cols);
			JScrollPane ptable=new JScrollPane(table);
			cont.remove(1);
			cont.add(ptable,BorderLayout.CENTER);
			cont.validate();
			res.close();
		}
		catch(SQLException e2)
		{
			JOptionPane.showMessageDialog(this,e2.getMessage());
		}
	}
	private Vector getNextRow(ResultSet res,ResultSetMetaData resm)throws SQLException
	{
		Vector current=new Vector();
		for(int i=1;i<=resm.getColumnCount();i++)
		current.addElement(res.getString(i));
		return current;                                         //返回数据结果
	}
	public static void main(String[] args)
	{
		JDBCD win=new JDBCD();
		win.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
	}
}