JDBC三层架构深入刨析

Java Fans 2022-12-13 我要评论

什么是三层

（1）表示层（View）

命名：XxxView
职责：收集用户的数据和需求、数据

（2）业务逻辑层（service）

命名：XxxServiceImpl
职责：数据加工处理、调用DAO完成业务实现、控制事务

（3）数据访问层（Dao）

命名：XxxDaoImpl
职责：向业务层提供数据，将业务层加工后的数据同步到数据库

三层架构项目搭建步骤

项目环境搭建

1）新建一个项目，在项目中创建lib文件夹，将MySQL数据库jar包放在lib文件夹中，配置环境。

2）在src文件夹下面创建db.properties文件，编写数据库driver、url、user、password信息。

3）创建三层需要的各个包。

（1）utils包：存放工具类（DBUtils类、DateUtils类）

（2）entity包：存放实体类（Xxx.java）

（3）dao包：存放DAO接口

impl包：存放DAO接口实现类

（4）service包：存放service接口

impl：存放service接口实现类

（5）view包：存放程序启动类或测试类（main）

创建book表

创建表

CREATE TABLE IF NOT EXISTS `book`(
`bid` INT PRIMARY KEY AUTO_INCREMENT COMMENT '图书编号',
`isbn` VARCHAR(20) UNIQUE NOT NULL COMMENT '国际标准书号',
`name` VARCHAR(20) NOT NULL COMMENT '书名',
`author` VARCHAR(20) NOT NULL COMMENT '作者',
`press` VARCHAR(20) NOT NULL COMMENT '出版社',
`price` DOUBLE NOT NULL COMMENT '价格',
`classification` VARCHAR(20) NOT NULL COMMENT '分类'
);

向表中插入数据

INSERT INTO `book`(`bid`,`isbn`,`name`,`author`,`press`,`price`,`classification`)
VALUES
(1001,'978-7-5170-7654-4','SQL从入门到精通','张三','中国水利水电出版社',79.80,'数据库');
INSERT INTO `book`(`bid`,`isbn`,`name`,`author`,`press`,`price`,`classification`)
VALUES
(1002,'976-9-5245-7633-5','java从入门到精通','李四','清华大学出版社',99.80,'程序设计');

创建entity实体类Book

package com.cxyzxc.www.entity;
import java.util.Date;
/**
 * entity实体类Booke类
 */
public class Book {
	/** 图书编号 */
	private int bid;
	/** 国际标准书号 */
	private String isbn;
	/** 书名 */
	private String name;
	/** 作者 */
	private String author;
	/** 出版社 */
	private String press;
	/** 价格 */
	private double price;
	/** 分类 */
	private String classification;
	/** 出版日期 */
	private Date pubdate;
	public Book() {
		super();
	}
	public Book(String isbn, String name, String author, String press,
			double price, String classification, Date pubdate) {
		super();
		this.isbn = isbn;
		this.name = name;
		this.author = author;
		this.press = press;
		this.price = price;
		this.classification = classification;
		this.pubdate = pubdate;
	}
	public Book(int bid, String isbn, String name, String author, String press,
			double price, String classification, Date pubdate) {
		super();
		this.bid = bid;
		this.isbn = isbn;
		this.name = name;
		this.author = author;
		this.press = press;
		this.price = price;
		this.classification = classification;
		this.pubdate = pubdate;
	}
	public int getBid() {
		return bid;
	}
	public void setBid(int bid) {
		this.bid = bid;
	}
	public String getIsbn() {
		return isbn;
	}
	public void setIsbn(String isbn) {
		this.isbn = isbn;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
	public String getPress() {
		return press;
	}
	public void setPress(String press) {
		this.press = press;
	}
	public double getPrice() {
		return price;
	}
	public void setPrice(double price) {
		this.price = price;
	}
	public String getClassification() {
		return classification;
	}
	public void setClassification(String classification) {
		this.classification = classification;
	}
	public Date getPubdate() {
		return pubdate;
	}
	public void setPubdate(Date pubdate) {
		this.pubdate = pubdate;
	}
	@Override
	public String toString() {
		return "Book [bid=" + bid + ", isbn=" + isbn + ", name=" + name
				+ ", author=" + author + ", press=" + press + ", price="
				+ price + ", classification=" + classification + ", pubdate="
				+ pubdate + "]";
	}
}

创建BookDao接口

package com.cxyzxc.www.dao;
import java.util.List;
import com.cxyzxc.www.entity.Book;
/**
 * 定义BookDao接口，接口中定义对book表增删改查的方法
 */
public interface BookDao {
	//增
	int insert(Book book);
	//删
	int delete(int bid);
	//改
	int update(Book book);
	//查单个
	Book selectOne(int bid);
	//查所有
	List<Book> selectAll();
}

创建BookDaoImpl实现类

package com.cxyzxc.www.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.cxyzxc.www.dao.BookDao;
import com.cxyzxc.www.entity.Book;
import com.cxyzxc.www.utils.DBUtils;
import com.cxyzxc.www.utils.DateUtils;
/**
 * 定义BookDaoImpl类，实现BookDao接口，重写接口中的增删改查方法
 */
public class BookDaoImpl implements BookDao {
	@Override
	public int insert(Book book) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		connection = DBUtils.getConnection();
		String sql = "INSERT INTO `book`(`isbn`,`name`,`author`,`press`,`price`,`classification`,`pubdate`)VALUES(?,?,?,?,?,?,?);";
		try {
			preparedStatement = connection.prepareStatement(sql);
			// 绑定参数
			preparedStatement.setString(1, book.getIsbn());
			preparedStatement.setString(2, book.getName());
			preparedStatement.setString(3, book.getAuthor());
			preparedStatement.setString(4, book.getPress());
			preparedStatement.setDouble(5, book.getPrice());
			preparedStatement.setString(6, book.getClassification());
			preparedStatement.setDate(7,
					DateUtils.utilDateToSqlDate(book.getPubdate()));
			int result = preparedStatement.executeUpdate();
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	@Override
	public int delete(int bid) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		connection = DBUtils.getConnection();
		String sql = "DELETE FROM `book` WHERE `bid`=?;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			// 绑定参数
			preparedStatement.setInt(1, bid);
			int result = preparedStatement.executeUpdate();
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	@Override
	public int update(Book book) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		connection = DBUtils.getConnection();
		String sql = "UPDATE `book` SET `isbn`=?,`name`=?,`author`=?,`press`=?,`price`=?,`classification`=?,`pubdate`=? WHERE `bid`=?;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			// 绑定参数
			preparedStatement.setString(1, book.getIsbn());
			preparedStatement.setString(2, book.getName());
			preparedStatement.setString(3, book.getAuthor());
			preparedStatement.setString(4, book.getPress());
			preparedStatement.setDouble(5, book.getPrice());
			preparedStatement.setString(6, book.getClassification());
			preparedStatement.setDate(7,
					DateUtils.utilDateToSqlDate(book.getPubdate()));
			preparedStatement.setDouble(8, book.getBid());
			int result = preparedStatement.executeUpdate();
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	@Override
	public Book selectOne(int bid) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Book book = null;
		connection = DBUtils.getConnection();
		String sql = "Select * FROM `book` WHERE `bid`=?;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			// 绑定参数
			preparedStatement.setInt(1, bid);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				int bookid = resultSet.getInt(1);
				String isbn = resultSet.getString(2);
				String name = resultSet.getString(3);
				String author = resultSet.getString(4);
				String press = resultSet.getString(5);
				double price = resultSet.getDouble(6);
				String classification = resultSet.getString(7);
				Date pubdate = resultSet.getDate(8);
				book = new Book(bookid, isbn, name, author, press, price,
						classification, pubdate);
			}
			return book;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	@Override
	public List<Book> selectAll() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Book book = null;
		List<Book> bookList = new ArrayList<Book>();
		connection = DBUtils.getConnection();
		String sql = "Select * FROM `book`;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int bookid = resultSet.getInt(1);
				String isbn = resultSet.getString(2);
				String name = resultSet.getString(3);
				String author = resultSet.getString(4);
				String press = resultSet.getString(5);
				double price = resultSet.getDouble(6);
				String classification = resultSet.getString(7);
				Date pubdate = resultSet.getDate(8);
				book = new Book(bookid, isbn, name, author, press, price,
						classification, pubdate);
				bookList.add(book);
			}
			return bookList;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
}

创建BookService接口

package com.cxyzxc.www.service;
import java.util.List;
import com.cxyzxc.www.entity.Book;
/**
 * 定义BookService接口，接口中定义业务逻辑方法
 */
public interface BookService {
	//添加图书
	int addBook(Book book);
	//删除图书
	int deleteBook(int bid);
	//修改图书
	int updateBook(Book book);
	//查询一本图书
	Book selectOne(int bid);
	//查询所有图书
	List<Book> selectAll();
}

创建BookServiceImpl实现类

package com.cxyzxc.www.service.impl;
import java.util.List;
import com.cxyzxc.www.dao.BookDao;
import com.cxyzxc.www.dao.impl.BookDaoImpl;
import com.cxyzxc.www.entity.Book;
import com.cxyzxc.www.service.BookService;
/**
 * 定义BookServiceImpl类，实现BookService接口，重写BookService接口中的所有方法
 */
public class BookServiceImpl implements BookService {
	BookDao bookDao = new BookDaoImpl();
	@Override
	public int addBook(Book book) {
		// 首先查询一下插入的图书在数据库中是否存在
		Book book2 = bookDao.selectOne(book.getBid());
		if (book2 == null) {
			return bookDao.insert(book);
		} else {
			System.out.println("插入的图书已经存在，插入失败");
		}
		return 0;
	}
	@Override
	public int deleteBook(int bid) {
		// 查询要删除的图书是否存在
		Book book2 = bookDao.selectOne(bid);
		if (book2 == null) {
			System.out.println("删除的图书不存在，无法删除");
		} else {
			return bookDao.delete(bid);
		}
		return 0;
	}
	@Override
	public int updateBook(Book book) {
		// 查询要修改的图书是否存在
		Book book2 = bookDao.selectOne(book.getBid());
		if (book2 == null) {
			System.out.println("你要修改的图书不存在");
		} else {
			return bookDao.update(book);
		}
		return 0;
	}
	@Override
	public Book selectOne(int bid) {
		Book book2 = bookDao.selectOne(bid);
		return book2;
	}
	@Override
	public List<Book> selectAll() {
		List<Book> bookList = bookDao.selectAll();
		return bookList;
	}
}

编写测试类

测试增加数据

package com.cxyzxc.www.view;
import com.cxyzxc.www.entity.Book;
import com.cxyzxc.www.service.BookService;
import com.cxyzxc.www.service.impl.BookServiceImpl;
import com.cxyzxc.www.utils.DateUtils;
public class Test01InsertBook {
	public static void main(String[] args) {
		BookService bookService = new BookServiceImpl();
		// 添加图书
		Book book = new Book("978-9-9456-3286-9", "JSP从入门到精通", "李二狗", "邮电出版社",129, "编程设计", DateUtils.strDateToUtilDate("2022-01-13"));
		int result = bookService.addBook(book);
		if (result == 1) {
			System.out.println("图书添加成功");
		} else {
			System.out.println("图书添加失败");
		}
	}
}

测试删除数据

package com.cxyzxc.www.view;
import com.cxyzxc.www.service.BookService;
import com.cxyzxc.www.service.impl.BookServiceImpl;
public class Test02DeleteBook {
	public static void main(String[] args) {
		BookService bookService = new BookServiceImpl();
		// 删除图书
		int result = bookService.deleteBook(1003);
		if (result == 1) {
			System.out.println("删除成功");
		} else {
			System.out.println("删除失败");
		}
	}
}

测试修改数据

package com.cxyzxc.www.view;
import com.cxyzxc.www.entity.Book;
import com.cxyzxc.www.service.BookService;
import com.cxyzxc.www.service.impl.BookServiceImpl;
import com.cxyzxc.www.utils.DateUtils;
public class Test03UpdateBook {
	public static void main(String[] args) {
		BookService bookService = new BookServiceImpl();
		// //修改图书
		Book book = new Book(1002, "976-9-5245-7633-5", "JSP从入门到放弃", "李四","清华大学出版社", 109.8, "编程设计",DateUtils.strDateToUtilDate("2022-10-13"));
		int result = bookService.updateBook(book);
		if (result == 1) {
			System.out.println("修改成功");
		} else {
			System.out.println("修改失败");
		}
	}
}

测试查询单个

package com.cxyzxc.www.view;
import com.cxyzxc.www.entity.Book;
import com.cxyzxc.www.service.BookService;
import com.cxyzxc.www.service.impl.BookServiceImpl;
public class Test04SelectOneBook {
	public static void main(String[] args) {
		BookService bookService = new BookServiceImpl();
		Book book = bookService.selectOne(1003);
		if (book == null) {
			System.out.println("没有你要查找的图书");
		} else {
			System.out.println(book);
		}
	}
}

测试查询所有

package com.cxyzxc.www.view;
import java.util.List;
import com.cxyzxc.www.entity.Book;
import com.cxyzxc.www.service.BookService;
import com.cxyzxc.www.service.impl.BookServiceImpl;
public class Test05SelectAllBook {
	public static void main(String[] args) {
		BookService bookService = new BookServiceImpl();
		List<Book> books = bookService.selectAll();
		if (books.isEmpty()) {
			System.out.println("数据库里没有书的数据");
		} else {
			for (int i = 0; i < books.size(); i++) {
				System.out.println(books.get(i));
			}
		}
	}
}