java数据库的连接与窗体的设计

连接数据库

package test1;
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;

public class JdbcConnection {
	//数据库驱动名称
	private static String DRIVER_NAME="com.microsoft.sqlserver.jdbc.SQLServerDriver";
	//数据库连接地址
	private static String DATABASE_URL="jdbc:sqlserver://localhost:1433;DatabaseName=sales";
	//数据库用户名称
	private static String DATABASE_USERNAME="aa";
	//数据库密码
	private static String DATABASE_PASSWORD="123";
	//数据库连接
	private static Connection connection;
	//SQL命令对象
	private static Statement statement;
	//数据查询结果
	private static ResultSet resultSet;
//获取数据库连接
	public static Connection getJdbcConnection() throws SQLException{
		if(connection==null||connection.isClosed()) {
			try {
				//加载驱动程序
				Class.forName(DRIVER_NAME);
				//获取数据库连接
				connection = DriverManager.getConnection(DATABASE_URL, DATABASE_USERNAME, DATABASE_PASSWORD);
			}catch (ClassNotFoundException e ) {
				System.err.println("装在JDBC驱动程序失败。");			
				e.printStackTrace();
			}catch(SQLException e) {
				System.err.println("无法连接数据库");
				e.printStackTrace();
			}
		}
		return connection;
	}
		
	//查询操作
	public static ResultSet executeQuery(String sql) {
		try {
			statement=getJdbcConnection().createStatement();
			resultSet=statement.executeQuery(sql);
		}catch(SQLException e) {
			System.out.println(e);
		}
		return resultSet;
	}
	
	//update 操作
	public static int executeUpdate(String sql) {
		int count=0;//受影响数据行数
		try {
			statement=getJdbcConnection().createStatement();
			count=statement.executeUpdate(sql);
		}catch(SQLException e) {
			System.out.println(e);
		}
		return count;
	}
	
	//关闭数据库
	public static void close() {
		if(resultSet!=null) {
			try {
				resultSet.close();
			}catch(SQLException e) {
				e.printStackTrace();
			}
		}
		if(statement!=null) {
			try {
				statement.close();
			}catch(SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection!=null) {
			try {
				connection.close();
			}catch(SQLException e) {
				e.printStackTrace();
			}
		}
	}
	//
	//
	
	
	
	
}

设置窗口

package test1;

import java.awt.BorderLayout;
import java.awt.Container;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Vector;
import javax.swing.table.DefaultTableModel;
import javax.swing.*;
//继承JFrame类
public class JdbcDataView extends JFrame implements ActionListener {
	private static final long serialVersionUID = 1L;
	JMenuBar bar;
	JMenu menuQuery,menuAdd,menuUpdate;
	JMenuItem queryAgents,queryCustomers,queryOrders,queryProducts;
	JMenuItem addAgents,addCustomers,addOrders,addProducts;
	JTextField text;
	JButton button;
	JLabel label;
	JPanel panel;
	JScrollPane jscrollpane;
	JTable table;
	DefaultTableModel dtm;
	ResultSetMetaData rsmd;
	//初始化
	public JdbcDataView() {
		init();///设置窗口位置和大小,前面两个参数分别表示窗口位置的横坐标和纵坐标,后两个参数分别表示窗口大小的宽度和高度
		setBounds(300,100,850,550);
		setVisible(true);//设置可见
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
	}
	private void init() {
		setTitle( " JDBC 连接数据库");//设置标题
		setLayout( new BorderLayout( ));//设置布局管理器
		//菜单条和菜单
		bar = new JMenuBar();
		menuQuery =new JMenu("查询");menuAdd = new JMenu("插入");
		bar.add ( menuQuery);//将组件添加到窗口中
		bar.add( menuAdd);
		setJMenuBar(bar);
		//子菜单
		queryAgents = new JMenuItem("查询代理商");
		queryAgents.addActionListener( this);//绑定事件
		menuQuery.add( queryAgents);
		queryCustomers = new JMenuItem("查询客户");
		queryCustomers.addActionListener( this);//绑定事件
		menuQuery.add( queryCustomers);
		queryOrders = new JMenuItem("查询订单");
		queryOrders.addActionListener( this);//绑定事件
		menuQuery.add( queryOrders);
		queryProducts = new JMenuItem("查询商品");
		queryProducts.addActionListener( this);//绑定事件
		menuQuery.add( queryProducts);
		addAgents = new JMenuItem("增加代理商");
		addAgents.addActionListener( this);//绑定事件
		menuAdd.add( addAgents) ;
		addCustomers = new JMenuItem("增加客户");
		addCustomers.addActionListener(this);//绑定事件
		menuAdd.add( addCustomers);
		addOrders = new JMenuItem("增加订单");
		addOrders.addActionListener( this);//绑定事件
		menuAdd.add( addOrders);
		addProducts = new JMenuItem("增加商品");
		addProducts.addActionListener( this);//绑定事件
		menuAdd.add( addProducts);
		//删除数据按钮、输入框
		text = new JTextField( 16);
		button = new JButton("删除");
		label = new JLabel("输人代理商ID删除数据");
		panel = new JPanel();
		panel.add ( label);//将 label控件添加到面板中
		panel.add( text);
		panel.add( button) ;
		button.addActionListener( this);//为按钮注册监听器
		add( panel,BorderLayout.SOUTH);
		JScrollPane scroller = new JScrollPane( table);
		add ( scroller,BorderLayout.CENTER);	
	}

	//触发动作事件时，执行的方法
	public void actionPerformed( ActionEvent e) {
		//如果单击查询代理商按钮
		if ( e.getSource() == queryAgents){
			String sql = " select * from agents" ;
			query(sql);
		}
		//如果单击查询客户按钮
		if( e.getSource() == queryCustomers){
			String sql = "select * from customers";
			query( sql);
		}
		if( e.getSource() == queryOrders){
			String sql = " select * from orders" ;
			query( sql);}
		if( e.getSource( ) == queryProducts){
			String sql = " select * from products" ;
			query( sql);
		}
		if( e.getSource()== addCustomers){
			String sql = " insert into agents values( ' a08', 'test' , 'chongqing' ,8)";
			update( sql );
		}
		if( e.getSource( ) == addOrders){
			String sql = "insert into orders values( '0001','jan'.'cO01’'a08''n01', 2000 ,8)";
			update( sql);
		}
		if( e.getSource() == addOrders) {
			String sql = "insert into orders values('0001' , 'ian''c001’'n08’'n01’.2000,8)";
			update( sql);
		}
		if( e.getSource()== addProducts){
			String sql= " insert into products values( 'p08' , 'test' , ' chongqing’, 1000,1 ) ";
			update( sql);
		}
		if( e.getSource()== button){
			String sql = " delete from agents where aid ='"+text.getText()+"'";
			update( sql);
		}
	}
	
	private void query (String sql){
		ResultSet resultSet = JdbcConnection.executeQuery( sql);//获取数据集
		try {
			displayResultSet (resultSet);
		}catch ( SQLException e1){
			e1.printStackTrace();//在命令行打印异常信息
		}
		JdbcConnection.close();
	}

	private void update( String sql){
		int count = JdbcConnection.executeUpdate( sql);//
		System.out.println("受影响行数:"+count );
		JdbcConnection.close();
	}
	
	@SuppressWarnings({ "unchecked" , "rawtypes" })//用于抑制编译器产生警告信息
	private void displayResultSet ( ResultSet rs) throws SQLException {
		Vector colum = new Vector( );
		Vector rows = new Vector( );
		try {
			rsmd = rs.getMetaData();//获取数据集属性名
			for ( int i= 1; i<= rsmd.getColumnCount(); ++i)
				colum.addElement( rsmd.getColumnName(i));
			while ( rs.next() ){
				Vector currow = new Vector();
				for ( int i = 1; i<= rsmd.getColumnCount();++i){
					currow.addElement( rs.getString(i));
				}
				rows.addElement( currow );
			}
			table = new JTable( rows, colum) ;
			add ( table,BorderLayout.CENTER);
			table.setVisible(true);
			table.setRowHeight(50);
		}catch ( SQLException e1){
			e1.printStackTrace();
		}
		JScrollPane scroller = new JScrollPane( table);
		Container c = getContentPane();
		c.remove(1);
		c.add( scroller,BorderLayout.CENTER);
		//刷新Table
		c.validate();
	}
	
	public static void main(String[] args) {
		JdbcDataView jdbcDataView = new JdbcDataView();
	}
}