JavaWeb4——数据库分层设计

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;

/**
 * 顾客类
 *
 * @author NoBug
 * @version 1.0
 * @time 2022/3/19
 * @Blog https://blog.csdn.net/qq_51184516
 *
 */
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;

/**
 * 操作类 DAO
 *
 * @author NoBug
 * @version 1.0
 * @time 2022/3/19
 * @Blog https://blog.csdn.net/qq_51184516
 *
 */
public class CustomerDAO {
	String driver = "com.mysql.jdbc.Driver";
	String url = "jdbc:mysql://localhost:3306/mydb";
	String user = "root";
	String passwd = "123456";

	/**
	 * 增加顾客
	 * 
	 * @param cus
	 */
	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);
		}
	}

	/**
	 * 删除顾客
	 * 
	 * @param cus
	 */
	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);
		}
	}

	/**
	 * 查询所有顾客
	 * 
	 * @return
	 */
	public List<Customer> allCustomers() {
		List<Customer> list = new ArrayList<Customer>();

		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%-3s\n", cusid, cusname, cusphone);
			}

			con.close();
		} catch (Exception e) {
			System.out.println(e);
		}
	}
}

2.3 Demo

package javaweb.database;

/**
 * 测试 DAO
 *
 * @author NoBug
 * @version 1.0
 * @time 2022/3/19
 * @Blog https://blog.csdn.net/qq_51184516
 *
 */
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;

/**
 * 封装 JDBC 对数据库的操作
 *
 * @author NoBug
 * @version 1.0
 * @time 2022/3/19
 * @Blog https://blog.csdn.net/qq_51184516
 *
 */
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;

	/**
	 * 连接数据库
	 * 
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	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();
			}
		}
	}

	/**
	 * 添加到执行队列
	 * 
	 * @param sql
	 * @param queue
	 * @return
	 */
	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;
	}

	/**
	 * 执行
	 * 
	 * @param sql
	 * @param queue
	 * @return
	 */
	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;

/**
 * 对顾客的操作
 *
 * @author NoBug
 * @version 2.0
 * @time 2022/3/19
 * @Blog https://blog.csdn.net/qq_51184516
 *
 */
public class CustomerDAO {

	/** 创建 DB 层对象 */
	DB db = new DB();

	/**
	 * 添加顾客
	 * 
	 * @param cus
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	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);
		}
	}

	/**
	 * 删除顾客
	 * 
	 * @param cusID
	 */
	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%-3s\n", 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

}