上次，我们只讲解了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表插入前的数据情况：
[img]/upload/attachment/139320/98da363f-8d35-33f0-9d1a-c6e1aa87ac19.png[/img]
然后，我们在客户端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(); } }
我们再来看一下数据库的情况：
[img]/upload/attachment/139322/92d41aaf-cf87-332f-ae4e-dfee01e4f7a2.png[/img]
可以看到，2条数据已经被成功插入了，而且你可以在控制台看到2条插入成功信息。
在上面代码中我们需要特别提到的是PreparedStatement。
[color=red][b]在JDBC中进行数据更新操作时，我们推荐使用PreparedStatement，因为它
可以使用占位符，以便于我们从外部传入参数。[/b][/color]
然后，我们一口气把更新，删除，查存全部做了，完整代码如下：
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
