JDBC数据库编程(二) - 建立DAO对象

上次，我们只讲解了JDBC中简单的查询。
任何数据库的操作都包括：增，删，改，查。
今天，我们就来讲一下JDBC中完整地实现这4部分的方法。

为了使代码更加清晰，我们使用了DAO设计模式，就是为
每一个被操作的数据库对象建立一个DAO类，这个DAO类中
包含了被操作对象所应该包含的所有增，删，改，查操作。

首先，我们先建立一个类：CustomerDao
我们先试着在其中加入insert方法，如下：

public void insert(Customer customer)
		throws Exception {
		DbConn conn = null;
		PreparedStatement pstmt = null;
		String sql = "insert into Customers(customerid,city) "
							 + "values(?,?)";
		conn = new DbConn();
		try {
			pstmt = conn.getConnection().prepareStatement(sql);
			pstmt.setString(1, customer.getCustomerId());
			pstmt.setString(2, customer.getCity());
			pstmt.executeUpdate();
			pstmt.close();
			System.out.println("One Customer Inserted Successfully.");
		} catch (RuntimeException e) {
			//e.printStackTrace();
			throw new Exception("Insert Customer Error!");
		} finally {
			conn.close();
		}
	}

我们先看一下Customer表插入前的数据情况：



然后，我们在客户端main方法中测试下该方法：

public void insert(Customer customer)
		throws Exception {
		DbConn conn = null;
		PreparedStatement pstmt = null;
		String sql = "insert into Customers(customerid,city) "
							 + "values(?,?)";
		conn = new DbConn();
		try {
			pstmt = conn.getConnection().prepareStatement(sql);
			pstmt.setString(1, customer.getCustomerId());
			pstmt.setString(2, customer.getCity());
			pstmt.executeUpdate();
			pstmt.close();
			System.out.println("One Customer Inserted Successfully.");
		} catch (RuntimeException e) {
			//e.printStackTrace();
			throw new Exception("Insert Customer Error!");
		} finally {
			conn.close();
		}
	}

我们再来看一下数据库的情况：


可以看到，2条数据已经被成功插入了，而且你可以在控制台看到2条插入成功信息。

在上面代码中我们需要特别提到的是PreparedStatement。
在JDBC中进行数据更新操作时，我们推荐使用PreparedStatement，因为它
可以使用占位符，以便于我们从外部传入参数。

然后，我们一口气把更新，删除，查存全部做了，完整代码如下：

package corejava2.dao;

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

public class CustomerDao {
	//insert
	public void insert(Customer customer)
		throws Exception {
		DbConn conn = null;
		PreparedStatement pstmt = null;
		String sql = "insert into Customers(customerid,city) "
							 + "values(?,?)";
		conn = new DbConn();
		try {
			pstmt = conn.getConnection().prepareStatement(sql);
			pstmt.setString(1, customer.getCustomerId());
			pstmt.setString(2, customer.getCity());
			pstmt.executeUpdate();
			pstmt.close();
			System.out.println("One Customer Inserted Successfully.");
		} catch (RuntimeException e) {
			//e.printStackTrace();
			throw new Exception("Insert Customer Error!");
		} finally {
			conn.close();
		}
	}
	
	//update
	public void update(Customer customer)
		throws Exception {
		DbConn conn = null;
		PreparedStatement pstmt = null;
		String sql = "update Customers set city = ? "
							 + "where customerid = ?";
		conn = new DbConn();
		try {
			pstmt = conn.getConnection().prepareStatement(sql);
			pstmt.setString(1, customer.getCity());
			pstmt.setString(2, customer.getCustomerId());
			pstmt.executeUpdate();
			pstmt.close();
			System.out.println("One Customer Updated Successfully.");
		} catch (RuntimeException e) {
			//e.printStackTrace();
			throw new Exception("Update Customer Error!");
		} finally {
			conn.close();
		}
	}
	
	//delete
	public void delete(String customerId)
		throws Exception {
		DbConn conn = null;
		PreparedStatement pstmt = null;
		String sql = "delete from Customers where customerid = ?";
		conn = new DbConn();
		try {
			pstmt = conn.getConnection().prepareStatement(sql);
			pstmt.setString(1, customerId);
			pstmt.executeUpdate();
			pstmt.close();
			System.out.println("One Customer Deleted Successfully.");
		} catch (RuntimeException e) {
			//e.printStackTrace();
			throw new Exception("Delete Customer Error!");
		} finally {
			conn.close();
		}
	}
	
	//query all
	public ArrayList queryAll() throws Exception {
		ArrayList list = new ArrayList();
		String sql = "select * from customers";
		DbConn conn = null;
		Statement stmt = null;
		
		conn = new DbConn();
		try {
			stmt = conn.getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Customer customer = new Customer(rs.getString("customerid"),rs.getString("city"));
				list.add(customer);
			}
			rs.close();
			stmt.close();
		} catch (RuntimeException e) {
			//e.printStackTrace();
			throw new Exception("Query All Customers Error!");
		} finally {
			conn.close();
		}
		
		return list;
	}
}

然后，我们做个Util类用于显示Customers:

package corejava2.util;

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

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

客户端代码：

package corejava2.jdbc;

import corejava2.dao.CustomerDao;
import corejava2.objects.Customer;
import corejava2.util.CustomerUtil;
import java.util.*;

public class JdbcDemo {
	public static void main(String[]args) 
		throws Exception {
		Customer customer = null;
		CustomerDao cusDao = new CustomerDao();
		
		//insert 2 customers
		customer = new Customer("SH001","Shanghai");
		cusDao.insert(customer);
		customer = new Customer("TK001","Tokyo");
		cusDao.insert(customer);
		
		//update customer city by id
		customer = new Customer("TK001","Osaka");
		cusDao.update(customer);
		
		//delete customer by id
		cusDao.delete("TK001");
		
		//query all customers
		ArrayList customers = cusDao.queryAll();
		CustomerUtil.showCustomers(customers);
	}
}

运行结果：
Id: SH001 , City: Shanghai
Id: FISSA , City: Madrid
Id: FRNDO , City: Madrid
Id: KRLOS , City: Madrid
Id: MRPHS , City: Zion