Java通过JDBC访问MySQL数据库实例

本文是在linux下用eclipse写的java程序，目的在于访问本地MySQL数据库，在java图形界面下用SQL命令访问数据库，并将结果以表格形式返回输出在界面上。

准备工作需要：

1.mysql数据库安装和基本命令，参考：点击打开链接http://blog.csdn.net/fykhlp/article/details/5949326

2.eclipse下jdbc驱动程序加载，驱动程序下载：点击打开链接http://download.csdn.net/detail/u010297828/8704503

jdbc链接数据库方法参考：点击打开链接http://www.2cto.com/os/201404/292825.html

注意，每新建一个java数据库工程都要在eclipse里导入jar包驱动，我的mysql版本是5.5的，JDBC驱动程序5.1.28完全适合mysql5.5,jdbc驱动可以到mysql官网下载，jdbc/connector/j，需要mysql账户（如果没有就注册），也可以下载我使用的版本，下载后解压 tarzxvf 下载的包，解要后的文件夹中有mysql-connector-java-[version]-bin.jar包

驱动程序测试成功能够正常访问mysql后，下面是本人关于在计算机屏幕上显示数据库表中数据的代码，其中"查询“窗口输入的SQL命令只能是要显示表格的命令，比如show，select ×from table等，对数据库的操作指令在”执行“窗口输入，点击执行或者查询按钮即可得到结果;

下面是界面截图：

import java.awt.BorderLayout;
import java.awt.Container;
import java.awt.Font;
import java.awt.Panel;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.awt.event.WindowListener;
import java.security.PrivateKey;
import java.security.PublicKey;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextArea;
import javax.swing.JTextField;

import com.mysql.jdbc.ResultSetMetaData;
import com.mysql.jdbc.Statement;

public class DisplayQueryResults extends JFrame {

	private String driverName = "com.mysql.jdbc.Driver";
	private String databaseName = "jdbc:mysql://localhost/test";
	private String user = "root";
	private String password = "××mysql";
	private JTextField inputQuery;
	private JTextArea inputCommand;
	private JButton queryButton,executeButton;
	private JTable table;
	private ResultSet resultSet;
	private String queryStr = "show databases";
	private String commandStr = "  ";
	private Connection conn;

	public DisplayQueryResults() {
		super("输入SQL语句，按查询显示查看结果！");
		try {
			Class.forName(driverName);
			System.out.println("加载JDBC驱动程序成功");
			conn = DriverManager.getConnection(databaseName, user, password);
		} catch (ClassNotFoundException cne) {
			// TODO Auto-generated catch block
			System.err.println("加载JDBC驱动程序失败！");
			cne.printStackTrace();
			System.exit(1);
		} catch (SQLException sqle) {
			System.err.println("链接数据库失败");
			sqle.printStackTrace();
			System.exit(1);
		}
		Font font = new Font("宋体",Font.PLAIN,16);
		inputQuery = new JTextField(queryStr);
		inputCommand = new JTextArea(commandStr, 4, 30);
		inputQuery.setFont(font);
		inputCommand.setFont(font);
		executeButton = new JButton("执行");
		queryButton = new JButton("查询");
		queryButton.addActionListener(new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e) {
				// TODO Auto-generated method stub			
				getTable();// 执行sql语句并显示数据表
			}
		});
		executeButton.addActionListener(new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e) {
				// TODO Auto-generated method stub	
				executeSqlCommand();// 执行sql语句	
			}
		
		});

		JPanel executePanel = new JPanel();
		executePanel.setLayout(new BorderLayout(5,5));
		executePanel.add(new JScrollPane(inputCommand), BorderLayout.CENTER);
		executePanel.add(executeButton,BorderLayout.EAST);
		
		JPanel queryPanel = new JPanel();
		queryPanel.setLayout(new BorderLayout(5,5));
		queryPanel.add(inputQuery,BorderLayout.CENTER);
		queryPanel.add(queryButton, BorderLayout.EAST);
	
		JPanel topPanel = new JPanel();
		topPanel.setLayout(new BorderLayout(5,5));
		topPanel.add(executePanel, BorderLayout.NORTH);
		topPanel.add(queryPanel, BorderLayout.SOUTH);

		table = new JTable();
		getContentPane().setLayout(new BorderLayout(5,5));
		//getContentPane().add(executePanel, BorderLayout.NORTH);
		getContentPane().add(topPanel, BorderLayout.NORTH);
		getContentPane().add(table, BorderLayout.SOUTH);
		getTable();
		setSize(500, 300);
		setVisible(true);
	}
	
	private void executeSqlCommand() { 
		// TODO Auto-generated method stub
		String command = inputCommand.getText();
		try {
			Statement statement = (Statement) conn.createStatement();
			statement.executeUpdate(command);
			statement.close();
			System.out.println("执行SQL命令成功！");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.append("执行SQL命令失败，请检查语法");
			e.printStackTrace();
		}
		
	}

	private void getTable() {
		// TODO Auto-generated method stub
		try {
			String query = inputQuery.getText();
			Statement statement = (Statement) conn.createStatement();
			resultSet = statement.executeQuery(query);
			displayResultSet(resultSet);
			statement.close();
			System.out.println("执行SQL查询命令成功,Query OK");
		} catch (SQLException sqle) {
			System.out.println("执行sql语句失败！");
		}
	}

	private void displayResultSet(ResultSet rs) {
		// TODO Auto-generated method stub
		boolean moreRecords;
		try {
			moreRecords = rs.next();
			if (!moreRecords) {
				JOptionPane.showMessageDialog(this, "结果中无记录");
				setTitle("无显示");
				return;
			}
			Vector columHeads = new Vector();
			Vector rows = new Vector();
			ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();// 获取字段的名称
			//获取字段名称组成的向量
			
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {            //注意索引值从1开始
				columHeads.addElement(rsmd.getColumnName(i));
			}
			do {
				rows.addElement(getTextRow(rs, rsmd));
			} while (rs.next());
			table = new JTable(rows, columHeads);
			JScrollPane scroller = new JScrollPane(table);
			getContentPane().remove(1);
			getContentPane().add(scroller, BorderLayout.CENTER);
			getContentPane().validate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println("显示结果失败");
			e.printStackTrace();
		}

	}

	private Vector getTextRow(ResultSet rs, ResultSetMetaData rsmd)
			throws SQLException {
		// TODO Auto-generated method stub
		Vector currentRow = new Vector();
		for (int i = 1; i <= rsmd.getColumnCount(); i++) {
			currentRow.addElement(rs.getString(i));
		}
		return currentRow;
	}

	public void shutDown() {
		try {
			conn.close();
		} catch (SQLException sqlex) {
			System.err.println("不能断开连接");
			sqlex.printStackTrace();
		}
	}

	public static void main(String args[]) {
		final DisplayQueryResults app = new DisplayQueryResults();
		app.addWindowListener(new WindowAdapter() {
			public void windowClosing(WindowEvent e) {
				app.shutDown();
				System.exit(0);
			}
		});
	}

}