JDBC数据库编程(一) - 三大对象及基本步骤

今天我们来讲一下在Java中如何利用JDBC访问数据库的基本步骤。

熟悉微软的ADO的朋友一定知道，在MS中访问数据库有三大对象，它们是：
Connection(连接), Command(命令), RecordSet(记录集)

我们要说，在JDBC中我们也有对应的三大对象：
[b]
Connection (连接)
Statement (语句)
ResultSet (结果集)
[/b]

[color=red]
注意: ResultSet不要与RecordSet搞混。
[/color]

既然有了三大对象，那下面我们就来看一下JDBC访问数据库的基本步骤。

[b]
[color=blue]
1) 获取Connection(连接)。
2) 利用连接创建Statement(语句)。
3) 利用Statement(语句)传入实际sql语句操作(查询，更新)数据库。
4) 关闭连接。
[/color]
[/b]

其中，连接本身包括五大要素：
[b]
[color=red]
a. driver (数据库驱动名)
b. source url (数据库URL)
c. user name (连接数据库的用户名)
d. user password (连接数据库的密码)
e. Connection (连接本身)
[/color]

获取连接的基本步骤如下：
[color=blue]
1) 装载驱动。 (eg: Class.forName("驱动名");)
2) 根据url,用户名,密码取得连接。(DriverManager)
[/color]
[/b]

我们可以把连接封装在一个类中，具体代码如下：

package corejava2.jdbc;

import java.sql.*;

public class DbConn {
	private String driver = "oracle.jdbc.driver.OracleDriver";
	private String srcUrl = "jdbc:oracle:thin:@localhost:1521:OracleDB";
	private String user = "train";
	private String password = "train";
	private Connection conn = null;

	public DbConn() {
		try {
			Class.forName(driver);
			this.conn = DriverManager.getConnection(srcUrl, user, password);
		} catch (Exception e) {
			System.out.println("Get DB Connection Error!");
		}
	}

	public Connection getConnection() {
		return this.conn;
	}

	public void close() {
		try {
			this.conn.close();
		} catch (Exception e) {
			System.out.println("Close DB Connection Error!");
		}
	}
}

下面我们就来做个基本的JDBC程序来显示我们例子数据库(Oracle)中的Customers
表的数据。

首先我们需要做个Customer类，用于获取数据库中的单行记录：

package corejava2.objects;

public class Customer {
	private String customerId;
	private String city;

	public Customer(String customerId, String city) {
		this.customerId = customerId;
		this.city = city;
	}

	public String getCity() {
		return city;
	}
	public void setCity(String city) {
		this.city = city;
	}

	public String getCustomerId() {
		return customerId;
	}
	public void setCustomerId(String customerId) {
		this.customerId = customerId;
	}

          public String toString() {
		return "Id: " + customerId + " , City: " + city; 
	}
}

接下来，是客户端的访问代码：

package corejava2.jdbc;

import java.util.*;
import java.sql.*;
import corejava2.objects.Customer;

public class SimpleQuery {
	public static void main(String[]args)
		throws Exception{
		DbConn conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList customers = null;
		String sql = "select * from Customers";

		conn = new DbConn();

		try {
			stmt = conn.getConnection().createStatement();
			rs = stmt.executeQuery(sql);

			customers = loadCustomers(rs);

			rs.close();
			stmt.close();
			conn.close();

			showCustomers(customers);
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	private static ArrayList loadCustomers(ResultSet rs)
		throws Exception {

		ArrayList customerList = new ArrayList();

		while (rs.next()) {
			String customerId = rs.getString("CUSTOMERID");
			String city = rs.getString("CITY");

			Customer customer = new Customer(customerId,city);
			customerList.add(customer);
		}

		return customerList;
	}

	private static void showCustomers(ArrayList list) {
		Iterator it = list.iterator();
		while (it.hasNext()) {
			Customer customer = (Customer)it.next();
			System.out.println(customer);
		}
	}
}