1. O/R 映射2. 实战——客户信息系统分层设计
2.1 Customer2.2 CustomerDAO2.3 Demo 3. 方法二——封装 DB 层
3.1 DB3.2 CustomerDAO3.3 Demo
1. O/R 映射Java是面向对象的。对象关系映射（ORM）是为了解决面向对象与关系数据库互不匹配的现象的技术。简单来说，就是对一些数据库操作方法的集成。看过前面的就知道，每次连接数据库都有很大的代码。我们就是要简化他们，创建映射。 2. 实战——客户信息系统分层设计
创建数据库：mydb
create database mydb;
创建Customers表：
create table customers(
cusid varchar(20) not null primary key,
cusname varchar(20),
cusphone varchar(20)
);
2.1 Customer
package javaweb.database;
public class Customer {
private String cusid, cusname, cusphone;
public Customer() {
}
public Customer(String cusid, String cusname, String cusphone) {
this.cusid = cusid;
this.cusname = cusname;
this.cusphone = cusphone;
}
public String getCusid() {
return cusid;
}
public void setCusid(String cusid) {
this.cusid = cusid;
}
public String getCusname() {
return cusname;
}
public void setCusname(String cusname) {
this.cusname = cusname;
}
public String getCusphone() {
return cusphone;
}
public void setCusphone(String cusphone) {
this.cusphone = cusphone;
}
} // Customers
2.2 CustomerDAO
package javaweb.database;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class CustomerDAO {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/mydb";
String user = "root";
String passwd = "123456";
public void addCustomer(Customer cus) {
try {
Class.forName(driver);
Connection con = DriverManager.getConnection(url, user, passwd);
String sql = "insert into customers(cusid, cusname, cusphone) values(?, ?, ?);";
PreparedStatement cmd = con.prepareStatement(sql);
cmd.setString(1, cus.getCusid());
cmd.setString(2, cus.getCusname());
cmd.setString(3, cus.getCusphone());
cmd.executeUpdate();
con.close();
} catch (Exception e) {
System.out.println(e);
}
}
public void deleteCustomerByID(Customer cus) {
try {
Class.forName(driver);
Connection con = DriverManager.getConnection(url, user, passwd);
String sql = "delete from customers where cusid = ?;";
PreparedStatement cmd = con.prepareStatement(sql);
cmd.setString(1, cus.getCusid());
cmd.executeUpdate();
con.close();
} catch (Exception e) {
System.out.println(e);
}
}
public List allCustomers() {
List list = new ArrayList();
try {
Class.forName(driver);
Connection con = DriverManager.getConnection(url, user, passwd);
Statement cmd = con.createStatement();
String sql = "select * from customers;";
ResultSet rs = cmd.executeQuery(sql);
while (rs.next()) {
Customer cus = new Customer();
cus.setCusid(rs.getString(1));
cus.setCusname(rs.getString(2));
cus.setCusphone(rs.getString(3));
list.add(cus);
}
con.close();
} catch (Exception e) {
System.out.println(e);
}
return list;
}
public void printCustomersTable() {
try {
Class.forName(driver);
Connection con = DriverManager.getConnection(url, user, passwd);
Statement cmd = con.createStatement();
String sql = "select * from customers;";
ResultSet rs = cmd.executeQuery(sql);
while (rs.next()) {
String cusid = rs.getString("cusid");
String cusname = rs.getString("cusname");
String cusphone = rs.getString("cusphone");
System.out.printf("%-8s%-6s%-3sn", cusid, cusname, cusphone);
}
con.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
2.3 Demo
package javaweb.database;
public class Demo {
public static void main(String[] args) {
CustomerDAO dao = new CustomerDAO();
Customer cus1 = new Customer("1001", "陈军", "191 2526 5891");
Customer cus2 = new Customer("1002", "张三", "181 2551 9999");
Customer cus3 = new Customer("1003", "李四", "115 5555 6658");
dao.addCustomer(cus1);
dao.addCustomer(cus2);
dao.addCustomer(cus3);
dao.printCustomersTable();
System.out.println("删除顾客之后：");
dao.deleteCustomerByID(cus1);
dao.printCustomersTable();
} // main
}
3. 方法二——封装 DB 层
创建数据库：mydb
create database mydb;
创建Customers表：
create table customers(
cusid varchar(20) not null primary key,
cusname varchar(20),
cusphone varchar(20)
);
3.1 DB
package javaweb.database.mydb;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DB {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/mydb";
String user = "root";
String passwd = "123456";
Connection con = null;
PreparedStatement cmd = null;
ResultSet rs = null;
public Connection getCon() throws ClassNotFoundException, SQLException {
Class.forName(driver);
con = DriverManager.getConnection(url, user, passwd);
return con;
}
public void closeAll() {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
if (cmd != null) {
try {
cmd.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
}
public ResultSet executeQuery(String sql, String[] queue) {
try {
cmd = con.prepareStatement(sql);
if (queue != null) {
for (int i = 0; i < queue.length; i++) {
cmd.setString(i + 1, queue[i]);
}
}
rs = cmd.executeQuery();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
return rs;
}
public int executeUpdate(String sql, String[] queue) {
int num = 0;
try {
cmd = con.prepareStatement(sql);
if (queue != null) {
for (int i = 0; i < queue.length; i++) {
cmd.setString(i + 1, queue[i]);
}
}
num = cmd.executeUpdate();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
return num;
}
}
3.2 CustomerDAO
package javaweb.database.mydb;
import java.sql.ResultSet;
import java.sql.SQLException;
import javaweb.database.Customer;
public class CustomerDAO {
DB db = new DB();
public void addCustomer(Customer cus) throws ClassNotFoundException, SQLException {
String sql = "insert into customers(cusid, cusname, cusphone) values(?, ?, ?);";
try {
db.getCon();
db.executeUpdate(sql, new String[] { cus.getCusid(), cus.getCusname(), cus.getCusphone() });
db.closeAll();
} catch (Exception e) {
System.out.println(e);
}
}
public void deleteCustomerByID(String cusID) {
String sql = "delete from customers where cusid = ?;";
try {
db.getCon();
db.executeUpdate(sql, new String[] { cusID });
db.closeAll();
} catch (Exception e) {
System.out.println(e);
}
}
public void printCustomersTable() {
String sql = "select * from customers;";
try {
db.getCon();
ResultSet rs = db.executeQuery(sql, null);
while (rs.next()) {
String cusid = rs.getString("cusid");
String cusname = rs.getString("cusname");
String cusphone = rs.getString("cusphone");
System.out.printf("%-8s%-6s%-3sn", cusid, cusname, cusphone);
}
db.closeAll();
} catch (Exception e) {
System.out.println(e);
}
}
}
3.3 Demo
package javaweb.database.mydb;
import java.sql.SQLException;
import javaweb.database.Customer;
public class Demo {
public static void main(String[] args) {
CustomerDAO dao = new CustomerDAO();
Customer cus1 = new Customer("1001", "陈军", "191 2526 5891");
Customer cus2 = new Customer("1002", "张三", "181 2551 9999");
Customer cus3 = new Customer("1003", "李四", "115 5555 6658");
try {
dao.addCustomer(cus1);
dao.addCustomer(cus2);
dao.addCustomer(cus3);
} catch (ClassNotFoundException | SQLException e) {
System.out.println(e);
}
dao.printCustomersTable();
System.out.println("删除顾客之后：");
dao.deleteCustomerByID("1003");
dao.printCustomersTable();
} // main
}
