JDBC

一． JDBC连接Access实例
1. 准备条件
1.1 创建一个Access数据库，例如：C:\\ test.ldb
1.2 ODBC数据源配置：开始->控制面板->管理工具->数据源(ODBC)->系统DSN->添加->
Microsoft Access Driver (*.mdb)->完成->数据源名->选择数据库
1.3 把Customer.dat,User.dat,CreateDB.properties放到C盘下
2. CreateDB.java

package com.test.db;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class CreateDB {

	/**
	 * 获得数据库连接
	 * @return 获得连接对象
	 * @throws FileNotFoundException
	 * @throws IOException
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	private static Connection getConnection() throws 
		FileNotFoundException,IOException,ClassNotFoundException,SQLException{

		String path="C:\\CreateDB.properties";
		Properties properties=new Properties();
		FileInputStream in=new FileInputStream(path);
		properties.load(in);
		String driverClass=properties.getProperty("jdbc.drivers");
		if(driverClass!=null){
			Class.forName(driverClass);
		}
		String url=properties.getProperty("jdbc.url");
		return DriverManager.getConnection(url);
	}
	
	/**
	 * 创建表及数据
	 * @param tableName 表名
	 * @param in        .dat文件的信息
	 * @param statement 操作对象
	 */
	private static void createTable
		(String tableName,BufferedReader in,Statement statement)
		throws IOException,SQLException{
		String line=in.readLine();
		String command="create table "+tableName+"("+line+")";
		statement.execute(command);
		
		while((line=in.readLine())!=null){
			command="insert into "+tableName+" values("+line+")";
			statement.execute(command);
		}
	}
	
	/**
	 * 查询表数据
	 * @param tableName 表名
	 * @param statement 操作对象
	 */
	private static void showTable(String tableName,Statement statement)throws SQLException{
		
		String command="select * from "+tableName;
		
		ResultSet rs=statement.executeQuery(command);
		
		int columnCount=rs.getMetaData().getColumnCount();
		
		while(rs.next()){
			for(int i=1;i<=columnCount;i++){
				if(i>1){
					System.out.print(",");
				}
				System.out.print(rs.getString(i));
			}
			System.out.println();
		}
		
		rs.close();
	}
	
	public static void main(String[] args){

		try{
			String tableName=null;
			if(args.length==0){
				System.out.println("请输入表名");
				System.exit(0);
			}else{
				tableName=args[0];
			}
			
			Connection conn=getConnection();
			Statement statement=conn.createStatement();
			
			BufferedReader in=new BufferedReader(new FileReader("C:\\"+tableName+".dat"));

			createTable(tableName,in,statement);
			showTable(tableName,statement);
			
			in.close();
			statement.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
	}

}

二． JDBC连接Access实例(基于图形用户界面)
1. WatchDB.java

package com.test.db;

import java.awt.Component;
import java.awt.Container;
import java.awt.GridBagConstraints;
import java.awt.GridBagLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;

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.JTextField;

public class WatchDB
{

	public static void main(String[] args)
	{
		JFrame frame = new WatchDBFrame();
		frame.show();
	}
}

class WatchDBFrame extends JFrame implements ActionListener
{
	private JButton nextButton;

	private JPanel dataPanel;

	private JComboBox tableNames;

	private ArrayList fields;

	private Connection con;

	private Statement stmt;

	private DatabaseMetaData md;

	private ResultSet rs;

	public static Connection getConnectin() throws IOException,
			ClassNotFoundException, SQLException
	{

		Properties props = new Properties();
		String fileName = "C:\\CreateDB.properties";
		FileInputStream in = new FileInputStream(fileName);
		props.load(in);

		String drivers = props.getProperty("jdbc.drivers");
		if (null != drivers)
		{
			Class.forName(drivers);
		}
		String url = props.getProperty("jdbc.url");

		return DriverManager.getConnection(url);
	}

	private void add(Container p, Component c, GridBagConstraints gbc, int x,
			int y, int w, int h)
	{
		gbc.gridx = x;
		gbc.gridy = y;
		gbc.gridwidth = w;
		gbc.gridheight = h;
		p.add(c, gbc);
	}

	public void showNextRow()
	{
		if (null == rs)
		{
			return;
		}
		try
		{
			if (rs.next())
			{
				for (int i = 1; i <= fields.size(); i++)
				{
					String field = rs.getString(i);
					JTextField tb = (JTextField) fields.get(i - 1);
					tb.setText(field);
				}
			}
			else
			{
				rs.close();
				rs = null;
			}
		}
		catch (Exception ex)
		{
			System.out.println("Error:" + ex);
		}
	}

	public void actionPerformed(ActionEvent evt)
	{
		if (evt.getSource() == nextButton)
		{
			showNextRow();
		}
		else if (evt.getSource() == tableNames)
		{
			this.remove(dataPanel);
			dataPanel = new JPanel();
			fields.clear();
			dataPanel.setLayout(new GridBagLayout());
			GridBagConstraints gbc = new GridBagConstraints();
			gbc.weighty = 100;

			try
			{
				String tableName = (String) tableNames.getSelectedItem();
				if (null != rs)
				{
					rs.close();
				}
				rs = stmt.executeQuery("select * from " + tableName);
				ResultSetMetaData rsmd = rs.getMetaData();
				for (int i = 1; i <= rsmd.getColumnCount(); i++)
				{
					String columnName = rsmd.getColumnLabel(i);
					int columnWidth = rsmd.getColumnDisplaySize(i);
					JTextField tb = new JTextField(columnWidth);
					fields.add(tb);
					gbc.weightx = 0;
					gbc.anchor = GridBagConstraints.EAST;
					gbc.fill = GridBagConstraints.NONE;
					this.add(dataPanel, new JLabel(columnName), gbc, 0, i - 1, 1, 1);

					gbc.weightx = 100;
					gbc.anchor = GridBagConstraints.WEST;
					gbc.fill = GridBagConstraints.HORIZONTAL;
					this.add(dataPanel, tb, gbc, 1, i - 1, 1, 1);

				}
			}
			catch (Exception ex)
			{
				JOptionPane.showMessageDialog(this, ex);
			}
			this.getContentPane().add(dataPanel, "Center");
			this.doLayout();
			this.pack();

			showNextRow();
		}
	}

	public WatchDBFrame()
	{
		this.setTitle("查看数据");
		this.setSize(300, 200);
		this.addWindowListener(new WindowAdapter()
		{
			public void windowClosing(WindowEvent e)
			{
				System.exit(0);
			}
		});

		Container contentPane = this.getContentPane();

		tableNames = new JComboBox();
		tableNames.addActionListener(this);

		dataPanel = new JPanel();
		contentPane.add(dataPanel, "Center");

		nextButton = new JButton("Next");
		nextButton.addActionListener(this);

		JPanel p = new JPanel();
		p.add(nextButton);
		contentPane.add(p, "South");

		fields = new ArrayList();

		try
		{
			con = getConnectin();
			stmt = con.createStatement();
			md = con.getMetaData();

			ResultSet rs = md.getTables(null, null, null, null);
			while (rs.next())
			{
				String tableName = rs.getString(3);
				if ("User".equals(tableName) || "Customer".equals(tableName))
				{
					tableNames.addItem(tableName);
				}
			}

			rs.close();

		}
		catch (Exception ex)
		{
			JOptionPane.showMessageDialog(this, ex);
		}

		contentPane.add(tableNames, "North");

	}

}

三． JDBC连接Access实例(基于Servlet与Jsp)
1. DBServlet.java

package com.test.db;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class DBServlet extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		process(request, response);
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		process(request, response);
	}
	
	public void process(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		Properties properties=new Properties();
		String path="C:\\CreateDB.properties";
		FileInputStream in=new FileInputStream(path);
		properties.load(in);
		
		String driverClass=properties.getProperty("jdbc.drivers");
		
		if(driverClass!=null){
			try{
				Class.forName(driverClass);
			}catch(Exception ex){
				System.out.println("驱动类没有发现");
			}
			
		}
		
		String url=properties.getProperty("jdbc.url");

		try {
			Connection conn=DriverManager.getConnection(url);

			Statement stmt=conn.createStatement();

			String command="select * from User";
			ResultSet rs=stmt.executeQuery(command);
			
			ArrayList<Users> dbList=new ArrayList<Users>();
			Users user=null;

			while(rs.next()){
				user=new Users();
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setEmail(rs.getString("email"));
				user.setWww(rs.getString("www"));
				user.setTel(rs.getString("tel"));
				user.setAddress(rs.getString("address"));
				user.setPostcode(rs.getString("postcode"));
				dbList.add(user);
			}
			
			request.setAttribute("dbList", dbList);

		} catch (Exception e) {
			e.printStackTrace();
		}
		
		request.getRequestDispatcher("/showDB.jsp").forward(request, response);
	}

}

2. showDB.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.util.ArrayList,com.test.db.Users" %>
<%
	ArrayList<Users> dbList=(ArrayList<Users>)request.getAttribute("dbList");
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
	<table>
		<tr>
			<td align="center">用户名</td>
			<td align="center">密码</td>
			<td align="center">邮箱</td>
			<td align="center">主页</td>
			<td align="center">电话</td>
			<td align="center">地址</td>
			<td align="center">邮编</td>
		</tr>
		<%
			for(int i=0;i<dbList.size();i++){
				Users user=dbList.get(i);
		%>
		<tr>
			<td align="center"><%=user.getName() %></td>
			<td align="center"><%=user.getPassword() %></td>
			<td align="center"><%=user.getEmail() %></td>
			<td align="center"><%=user.getWww() %></td>
			<td align="center"><%=user.getTel() %></td>
			<td align="center"><%=user.getAddress() %></td>
			<td align="center"><%=user.getPostcode() %></td>
		</tr>
		<%
		}
		%>
	</table>
</body>
</html>