databaseinsert1.java

此为用JAVA写的一个数据库连接,查询,插入于一体的JAVA数据库连接程序,其中还用到了线程控制,对JAVA线程学习也有一定帮助!

package test7;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JPasswordField;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.border.TitledBorder;

class MianWindow extends JFrame implements Runnable{//建立Runnable接口
	private static final long serialVersionUID = 1L;
	private Thread Scrollwords=null;
	private Connection connections;//连接到数据库
	private ResultSet rs;//数据库结果集
	private JTextArea sqlhelp,result;
	private JTextField url,port,databasename,username,datatable,name,nummber,english,java;
	private JPasswordField password;
	private Label lurl,lport,ldatabasename,lusername,lpassword,ldatatable,connectionhelp,lname,lnummber,lenglish,ljava,myinformation;
	private JScrollPane jspresult;
	private JButton connection,command,insert,order,closeconnection;
	private JPanel jp1,jp2,jp3,jp4,jp5,jp6,jp7,jp8,jp9,jp10,jp11,jp12,jp13,jp14,jp,jps;
	int count=0;
	MianWindow(){
		super("数据库插入操作");
		
		lurl=new Label("URL");
		lport=new Label("端口号");
		ldatabasename=new Label("数据库");
		lusername=new Label("用户名");
		lpassword=new Label("密码");
		jp1=new JPanel(new GridLayout(5,1));
		jp1.add(lurl);
		jp1.add(lport);
		jp1.add(ldatabasename);
		jp1.add(lusername);
		jp1.add(lpassword);
		
		url=new JTextField(15);
		port=new JTextField(15);
		databasename=new JTextField(15);
		username=new JTextField(15);
		password=new JPasswordField(15);//密码
		password.setEchoChar('*');
		jp2=new JPanel(new GridLayout(5,1));
		jp2.add(url);
		jp2.add(port);
		jp2.add(databasename);
		jp2.add(username);
		jp2.add(password);
		
		connectionhelp=new Label("未连接");
		connectionhelp.setForeground(Color.red);
		connection=new JButton("连接到数据库");
		jp3=new JPanel(new BorderLayout());
		jp3.add(connectionhelp,BorderLayout.CENTER);
		jp3.add(connection,BorderLayout.EAST);
		
		jp4=new JPanel(new BorderLayout());
		jp4.add(jp1,BorderLayout.WEST);
		jp4.add(jp2,BorderLayout.CENTER);
		jp4.add(jp3,BorderLayout.SOUTH);
		jp4.setBorder(new TitledBorder("登录信息窗口"));//到此,左上部控件做好了
		
		sqlhelp=new JTextArea("本地URL为:127.0.0.1或localhost\n默认端口号为:1433\n默认用户名为:sa\n" +
				"-----以上信息为空表示本机或默认-----\n(提示:每录入5条数据后将自动按总分降序排序)");
		sqlhelp.setForeground(Color.CYAN);
		sqlhelp.setEditable(false);
		sqlhelp.setBorder(new TitledBorder("提示窗口"));
		ldatatable=new Label("表名");
		datatable=new JTextField(15);
		datatable.setEnabled(false);
		command=new JButton("执行");
		command.setEnabled(false);
		jp5=new JPanel(new FlowLayout());
		jp5.add(ldatatable);
		jp5.add(datatable);
		jp5.add(command);
		jp5.setBorder(new TitledBorder("请输入表名"));
		jp6=new JPanel(new BorderLayout());
		jp6.add(sqlhelp,BorderLayout.CENTER);
		jp6.add(jp5,BorderLayout.SOUTH);//到此右上部分控件做好了
		jp7=new JPanel(new BorderLayout());
		jp7.add(jp4,BorderLayout.CENTER);
		jp7.add(jp6,BorderLayout.EAST);//到此上部分控件做好了
		
		lname=new Label("姓名");
		lnummber=new Label("学号");
		lenglish=new Label("英语");
		ljava=new Label("JAVA");
		jp8=new JPanel(new GridLayout(4,1));
		jp8.add(lname);
		jp8.add(lnummber);
		jp8.add(lenglish);
		jp8.add(ljava);
		
		name=new JTextField(6);
		nummber=new JTextField(6);
		english=new JTextField(6);
		java=new JTextField(6);
		jp9=new JPanel(new GridLayout(4,1));
		jp9.add(name);
		jp9.add(nummber);
		jp9.add(english);
		jp9.add(java);
		
		jp10=new JPanel(new BorderLayout());
		jp10.add(jp8,BorderLayout.CENTER);
		jp10.add(jp9,BorderLayout.EAST);
		jp10.setBorder(new TitledBorder("数据插入窗口"));
		insert=new JButton("插入");
		insert.setEnabled(false);
		jps=new JPanel(new FlowLayout(FlowLayout.CENTER));
		jps.add(insert);
		jp11=new JPanel(new BorderLayout());
		jp11.add(jp10,BorderLayout.CENTER);
		jp11.add(jps,BorderLayout.SOUTH);//到此,左下部分控件做好了
		
		result=new JTextArea();
		result.setEditable(false);
		jspresult=new JScrollPane(result);
		jspresult.setBorder(new TitledBorder("数据显示窗口"));
		order=new JButton("按总分排序");
		order.setEnabled(false);
		closeconnection=new JButton("断开数据库连接");
		closeconnection.setEnabled(false);
		jp=new JPanel(new FlowLayout(FlowLayout.CENTER));
		jp.add(order);
		jp.add(closeconnection);
		jp12=new JPanel(new BorderLayout());
		jp12.add(jspresult,BorderLayout.CENTER);
		jp12.add(jp,BorderLayout.SOUTH);//到此,右下部分控件做好了
		jp13=new JPanel(new BorderLayout());
		jp13.add(jp11,BorderLayout.WEST);
		jp13.add(jp12,BorderLayout.CENTER);//到此,下部分控件做好了
		myinformation=new Label("欢迎使用由Hummingbird编写的一个小程序,由于初学,还请多多赐教!");
		myinformation.setForeground(Color.magenta);
		jp14=new JPanel(new FlowLayout(FlowLayout.CENTER));
		jp14.add(myinformation);
		getContentPane().add(jp14,BorderLayout.NORTH);
		getContentPane().add(jp7,BorderLayout.CENTER);
		getContentPane().add(jp13,BorderLayout.SOUTH);
		setBounds(350, 100, 600, 430);
		setVisible(true);
		setResizable(false);
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);//到此窗口做好了
		
		Scrollwords=new Thread(this);//添加线程
		Scrollwords.start();
		//连接到数据库响应
		connection.addActionListener(new ActionListener()
        {
			public void actionPerformed(ActionEvent e)
			{
			  connectToDB();
			  try{
				  if(!connections.isClosed())
				  {
					connection.setEnabled(false);
					url.setEnabled(false);
					port.setEnabled(false);
					databasename.setEnabled(false);
					username.setEnabled(false);
					password.setEnabled(false);
					datatable.setEnabled(true);
					command.setEnabled(true);
					closeconnection.setEnabled(true);
				  }
			  }catch(Exception ex)
			  {
				  //
			  }
			}
        });
		command.addActionListener(new ActionListener()
        {
			public void actionPerformed(ActionEvent e)
			{
				String sqlCommands=datatable.getText().trim();
				String sql="SELECT * FROM "+sqlCommands;
				if(sqlCommands.equals(""))
				{
					//表为空则提示并反回
					JOptionPane.showMessageDialog(jp12, "请输入需查询的表", "错误提示", JOptionPane.ERROR_MESSAGE);
				}
				else{
					executeSQL(sql);
				}
			}
        });
		//数据插入响应
		insert.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e){
				String sqlCommands=datatable.getText().trim();
				final String cname,cnummber,cenglish,cjava;
				cname=name.getText().trim();
				cnummber=nummber.getText().trim();
				cenglish=english.getText().trim();
				cjava=java.getText().trim();
				if(cname.equals("")||cnummber.equals("")||cenglish.equals("")||cjava.equals("")){
					JOptionPane.showMessageDialog(jp12, "输入数据不完整!", "错误提示", JOptionPane.ERROR_MESSAGE);
				}
				else{
					String sql="INSERT INTO "+sqlCommands+" (姓名,学号,英语,JAVA) VALUES ('"+cname+"','"+cnummber+"','"+cenglish+"','"+cjava+"')";
					executeSQL(sql);
					count++;
					if(count%5==0){//每录入五次数据后自动按总分降序排序
						sql="SELECT 姓名,学号,英语,JAVA,JAVA+英语 总分 FROM "+sqlCommands+" order by java+英语 desc";
						executeSQL(sql);
					}
					name.setText("");
					nummber.setText("");
					english.setText("");
					java.setText("");
				}
			}
		});
		//数据排序响应
		order.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e){
				String sqlCommands=datatable.getText().trim();
				String sql="SELECT 姓名,学号,英语,JAVA,JAVA+英语 总分 FROM "+sqlCommands+" order by java+英语 desc";
				executeSQL(sql);
			}
		});
		//数据库关闭响应
		closeconnection.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e){
				try{
					if(!connections.isClosed())
					{
						connections.close();
						connectionhelp.setText("未连接");
						connectionhelp.setForeground(Color.red);
						closeconnection.setEnabled(false);
						datatable.setEnabled(false);
						command.setEnabled(false);
						insert.setEnabled(false);
						order.setEnabled(false);
						connection.setEnabled(true);
						url.setEnabled(true);
						port.setEnabled(true);
						databasename.setEnabled(true);
						username.setEnabled(true);
						password.setEnabled(true);
						result.append("数据库已被关闭!\n");
					}
					else
					{
						result.append("您未连接到数据库!\n");
					}
				}catch(Exception ex)
				{
					result.append(ex.getMessage());
				}
			}
		});
		nummber.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e){
				
			}
		});
	}
	//数据库的连接
	public  Connection connectToDB(){
		final String driverName = "com.microsoft.jdbc.sqlserver.SQLServerDriver";  //加载JDBC驱动
		final StringBuffer dbURL =new StringBuffer("jdbc:microsoft:sqlserver://");//连接服务器和sample
		String curl=url.getText().trim();//获得IP地址,trim()返回字符串的副本，忽略前导空白和尾部空白.
		String cport=port.getText().trim();//获得端口号
		String cdatabasename =databasename.getText().trim();//获得数据库名
		String cusername =username.getText().trim();  //获得用户名
		String cpassword =new String(password.getPassword());//获得密码
		if(curl.equals("")){
			curl="127.0.0.1";
		}
		if(cport.equals("")){
			cport="1433";
		}
		if(cusername.equals("")){
			cusername="sa";
		}
		try
		{
			Class.forName(driverName);
			//connection=DriverManager.getConnection(dbURL,"sa","yzb818");
			connections=DriverManager.getConnection(dbURL.append(curl).append(":").append(cport)
					.append(";DatabaseName=").append(cdatabasename).toString(),cusername,cpassword);
			connectionhelp.setText("已连接到数据库");//将登录信息加入
			connectionhelp.setForeground(Color.green);
		}catch(java.lang.Exception e)
		{
		//	e.printStackTrace();
			JOptionPane.showMessageDialog(this,"错误信息:"+e.getMessage()+"\n请重新输入","错误提示",JOptionPane.ERROR_MESSAGE);
		}
		return connections;
	}
	//数据库查询输出
	private ResultSet executeSQL(String sql){
		try{
			Statement st=connections.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
			rs=st.executeQuery(sql);
			int columnCount=rs.getMetaData().getColumnCount();
			String row="";
			result.setText("");
			for(int i=1;i<=columnCount;i++)
			{
				row+=rs.getMetaData().getColumnName(i)+'\t';
			}
			result.append(row+'\n');
			result.append("--------------------------------------------------------------------------------------------------------"+'\n');
			while(rs.next())
			{
				row="";
				for(int i=1;i<=columnCount;i++)
				{
					row+=rs.getString(i)+'\t';
				}
				result.append(row+'\n');
			}
			result.append(".........................................................................................................................................."+'\n');
			insert.setEnabled(true);
			order.setEnabled(true);
		}catch(SQLException e){
			if(sql.equals("SELECT * FROM "+datatable.getText().trim())){
				JOptionPane.showMessageDialog(jp12, "该表不存在,请重新输入!", "错误提示", JOptionPane.ERROR_MESSAGE);
				datatable.setText("");
			}
		}
		return rs;
	}
	//接口中的方法
	public void run(){
		while(true){//滚动字幕
			int y=myinformation.getBounds().y;
			int x=myinformation.getBounds().x;
			x=x-10;
			myinformation.setLocation(x,y);
			if(x<-265){
				x=600;
				myinformation.setLocation(x,y);
			}
			try{
				Thread.sleep(100);
			}catch(InterruptedException e){}
			
		}
	}
}
public class DatabaseInsert{
	public static void main(String[] args){
		new MianWindow();
	}
}