JDBC主题
- JDBC初出茅庐-建立数据库连接 https://www.7benshu.com/post/2020/03/21-1/
- JDBC小试牛刀-数据库操作 https://www.7benshu.com/post/2020/03/21-2/
- JDBC略有建树-二进制操作 https://www.7benshu.com/post/2020/03/21-3/
- JDBC出类拔萃-数据库批量操作 https://www.7benshu.com/post/2020/03/21-4/
- JDBC百里挑一|数据库事务 https://www.7benshu.com/post/2020/03/22-1/
- JDBC卓越超群-DAO https://www.7benshu.com/post/2020/03/22-2/
介绍
DAO (DataAccessobjects 数据存取对象)是指位于业务逻辑和持久化数据之间实现对持久化数据的访问。通俗来讲，就是将数据库操作都封装起来。
DAO 模式提供了访问关系型数据库系统所需操作的接口，将数据访问和业务逻辑分离对上层提供面向对象的数据访问接口。
从以上 DAO 模式使用可以看出，DAO 模式的优势就在于它实现了两次隔离。
- 1、隔离了数据访问代码和业务逻辑代码。业务逻辑代码直接调用DAO方法即可，完全感觉不到数据库表的存在。分工明确，数据访问层代码变化不影响业务逻辑代码,这符合单一职能原则，降低了藕合性，提高了可复用性。
- 2、隔离了不同数据库实现。采用面向接口编程，如果底层数据库变化，如由 MySQL 变成 Oracle 只要增加 DAO 接口的新实现类即可，原有 MySQ 实现不用修改。这符合 "开-闭" 原则。该原则降低了代码的藕合性，提高了代码扩展性和系统的可移植性。
一个典型的DAO 模式主要由以下几部分组成。
- 1、DAO接口： 把对数据库的所有操作定义成抽象方法，可以提供多种实现。
- 2、DAO 实现类： 针对不同数据库给出DAO接口定义方法的具体实现。
- 3、实体类：用于存放与传输对象数据。
- 4、数据库连接和关闭工具类： 避免了数据库连接和关闭代码的重复使用，方便修改。
作用：为了实现功能的模块化，更有利于代码的维护和升级。
下图是结构图:
beans
books
<code>package com.kid.bookstore.beans;
import com.google.common.base.Objects;
/**
* 图书类
*
* @author tangf
* @createTime 2020/03/22 22:37:00
*/
public class Book {
private Integer id;
/**
* 书名
*/
private String title;
/**
* 作者
*/
private String author;
/**
* 价格
*/
private double price;
/**
* 销量
*/
private Integer sales;
/**
* 库存
*/
private Integer stock;
/**
* 封面图片的路径
*/
private String imgPath = "static/img/default.jpg";
public Book(String title, String author, double price) {
this.title = title;
this.author = author;
this.price = price;
}
public Book() {
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public Integer getSales() {
return sales;
}
public void setSales(Integer sales) {
this.sales = sales;
}
public Integer getStock() {
return stock;
}
public void setStock(Integer stock) {
this.stock = stock;
}
public String getImgPath() {
return imgPath;
}
public void setImgPath(String imgPath) {
this.imgPath = imgPath;
}
@Override
public String toString() {
return "Book{" +
"id=" + id +
", title='" + title + '\\'' +
", author='" + author + '\\'' +
", price=" + price +
", sales=" + sales +
", stock=" + stock +
", imgPath='" + imgPath + '\\'' +
'}';
}
@Override
public boolean equals(Object o) {
if (this == o) {
return true;
}
if (o == null || getClass() != o.getClass()) {
return false;
}
Book book = (Book) o;
return Double.compare(book.price, price) == 0 &&
Objects.equal(id, book.id) &&
Objects.equal(title, book.title) &&
Objects.equal(author, book.author) &&
Objects.equal(sales, book.sales) &&
Objects.equal(stock, book.stock) &&
Objects.equal(imgPath, book.imgPath);
}
@Override
public int hashCode() {
return Objects.hashCode(id, title, author, price, sales, stock, imgPath);
}
}
/<code>
page
<code>package com.kid.bookstore.beans;
import java.util.List;
/**
* 页码类
*
* @paramthe type parameter /<code>
* @author tangf
* @createTime 2020 /03/22 22:58:21
*/
public class Page{
/**
* 每页查到的记录存放的集合
*/
private Listlist;
/**
* The constant PAGE_SIZE.
*/
public static final int PAGE_SIZE = 4;
/**
* 当前页
*/
private int pageNo;
/**
* 总页数，通过计算得到
*/
private int totalPageNo;
/**
* 总记录数，通过查询数据库得到
*/
private int totalRecord;
public Page() {
}
public ListgetList() {
return list;
}
public void setList(Listlist) {
this.list = list;
}
public static int getPageSize() {
return PAGE_SIZE;
}
public int getPageNo() {
return pageNo;
}
public void setPageNo(int pageNo) {
this.pageNo = pageNo;
}
public int getTotalPageNo() {
return totalPageNo;
}
public void setTotalPageNo(int totalPageNo) {
this.totalPageNo = totalPageNo;
}
public int getTotalRecord() {
return totalRecord;
}
public void setTotalRecord(int totalRecord) {
this.totalRecord = totalRecord;
}
}
user
<code>package com.kid.bookstore.beans;
import com.google.common.base.Objects;
/**
* 用户类
*
* @author tangf
* @createTime 2020/03/22 22:38:00
*/
public class User {
private Integer id;
private String username;
private String password;
private String email;
public User(String username, String password) {
this.username = username;
this.password = password;
}
public User() {
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public boolean equals(Object o) {
if (this == o) {
return true;
}
if (o == null || getClass() != o.getClass()) {
return false;
}
User user = (User) o;
return Objects.equal(id, user.id) &&
Objects.equal(username, user.username) &&
Objects.equal(password, user.password) &&
Objects.equal(email, user.email);
}
@Override
public int hashCode() {
return Objects.hashCode(id, username, password, email);
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\\'' +
", password='" + password + '\\'' +
", email='" + email + '\\'' +
'}';
}
}
/<code>
dao
basedao
<code>package com.kid.bookstore.dao;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
/**
* 数据库进行基本操作的Dao
*
* @paramthe type parameter /<code>
* @author tangf
* @createTime 2020 /03/22 22:52:05
*/
public abstract class BaseDao{
private QueryRunner queryRunner = new QueryRunner();
/**
* 定义一个变量来接收泛型的类型
*/
private Classtype;
/**
* 获取T的Class对象，获取泛型的类型，泛型是在被子类继承时才确定
*/
public BaseDao() {
// 获取子类的类型
Class clazz = this.getClass();
// 获取父类的类型
// getGenericSuperclass()用来获取当前类的父类的类型
// ParameterizedType表示的是带泛型的类型
ParameterizedType parameterizedType = (ParameterizedType) clazz.getGenericSuperclass();
// 获取具体的泛型类型 getActualTypeArguments获取具体的泛型的类型
// 这个方法会返回一个Type的数组
Type[] types = parameterizedType.getActualTypeArguments();
// 获取具体的泛型的类型·
this.type = (Class) types[0];
}
/**
* 增删改操作
*
* @param conn the conn
* @param sql the sql
* @param params the params
* @return the int
* @author tangf
* @createTime 2020 /03/22 22:52:05
*/
public int update(Connection conn, String sql, Object... params) {
int count = 0;
try {
count = queryRunner.update(conn, sql, params);
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
/**
* 获取一个对象
*
* @param conn the conn
* @param sql the sql
* @param params the params
* @return the bean
* @author tangf
* @createTime 2020 /03/22 22:52:05
*/
public T getBean(Connection conn, String sql, Object... params) {
T t = null;
try {
t = queryRunner.query(conn, sql, new BeanHandler(type), params);
} catch (SQLException e) {
e.printStackTrace();
}
return t;
}
/**
* 获取所有对象
*
* @param conn the conn
* @param sql the sql
* @param params the params
* @return the bean list
* @author tangf
* @createTime 2020 /03/22 22:52:05
*/
public ListgetBeanList(Connection conn, String sql, Object... params) {
Listlist = null;
try {
list = queryRunner.query(conn, sql, new BeanListHandler(type), params);
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
/**
* 获取一个但一值得方法，专门用来执行像 select count(*)...这样的sql语句
*
* @param conn the conn
* @param sql the sql
* @param params the params
* @return the value
* @author tangf
* @createTime 2020 /03/22 22:52:05
*/
public Object getValue(Connection conn, String sql, Object... params) {
Object count = null;
try {
// 调用queryRunner的query方法获取一个单一的值
count = queryRunner.query(conn, sql, new ScalarHandler<>(), params);
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
}
bookdao
<code>package com.kid.bookstore.dao;
import com.kid.bookstore.beans.Book;
import com.kid.bookstore.beans.Page;
import java.sql.Connection;
import java.util.List;
/**
* 图书DAO
* @author tangf
*/
public interface BookDao {
/**
* 获取所有图书
*
* @param conn the conn
* @return the books
* @author tangf
* @createTime 2020 /03/22 23:04:02
*/
List<book> getBooks(Connection conn);
/**
* 新增图书
*
* @param conn the conn
* @param book the book
* @author tangf
* @createTime 2020 /03/22 23:04:02
*/
void saveBook(Connection conn, Book book);
/**
* 删除图书通过主键
*
* @param conn the conn
* @param bookId the book id
* @author tangf
* @createTime 2020 /03/22 23:04:02
*/
void deleteBookById(Connection conn, String bookId);
/**
* 获取一本书通过主键
*
* @param conn the conn
* @param bookId the book id
* @return the book by id
* @author tangf
* @createTime 2020 /03/22 23:04:02
*/
Book getBookById(Connection conn, String bookId);
/**
* 更新图书
*
* @param conn the conn
* @param book the book
* @author tangf
* @createTime 2020 /03/22 23:04:02
*/
void updateBook(Connection conn, Book book);
/**
* 获取带分页的图书信息
*
* @param conn the conn
* @param page 是只包含了用户输入的pageNo属性的page对象
* @return 返回的Page对象是包含了所有属性的Page对象
* @author tangf
* @createTime 2020 /03/22 23:04:02
*/
Page<book> getPageBooks(Connection conn, Page<book> page);
/**
* 获取带分页和价格范围的图书信息
*
* @param conn the conn
* @param page 是只包含了用户输入的pageNo属性的page对象
* @param minPrice the min price
* @param maxPrice the max price
* @return 返回的Page对象是包含了所有属性的Page对象
* @author tangf
* @createTime 2020 /03/22 23:04:02
*/
Page<book> getPageBooksByPrice(Connection conn, Page<book> page, double minPrice, double maxPrice);
}
/<book>/<book>/<book>/<book>/<book>/<code>
userdao
<code>package com.kid.bookstore.dao;
import com.kid.bookstore.beans.User;
import java.sql.Connection;
/**
* 用户DAO
*/
public interface UserDao {
/**
* 根据User对象中的用户名和密码从数据库中获取一条记录
*
* @param conn the conn
* @param user the user
* @return the user
* @author tangf
* @createTime 2020 /03/22 23:09:53
*/
User getUser(Connection conn, User user);
/**
* 根据User对象中的用户名从数据库中获取一条记录
*
* @param conn the conn
* @param user the user
* @return the boolean
* @author tangf
* @createTime 2020 /03/22 23:09:53
*/
boolean checkUsername(Connection conn, User user);
/**
* 向数据库中插入User对象
*
* @param conn the conn
* @param user the user
* @author tangf
* @createTime 2020 /03/22 23:09:53
*/
void saveUser(Connection conn, User user);
}
/<code>
impl
bookdaoimpl
<code>package com.kid.bookstore.dao.impl;
import com.kid.bookstore.beans.Book;
import com.kid.bookstore.beans.Page;
import com.kid.bookstore.dao.BaseDao;
import com.kid.bookstore.dao.BookDao;
import java.sql.Connection;
import java.util.List;
/**
* The type Book dao.
*
* @author tangf
* @createTime 2020 /03/22 23:13:32
*/
public class BookDaoImpl extends BaseDao<book> implements BookDao {
@Override
public List<book> getBooks(Connection conn) {
// 调用BaseDao中得到一个List的方法
List<book> beanList = null;
// 写sql语句
String sql = "select id,title,author,price,sales,stock,img_path imgPath from books";
beanList = getBeanList(conn, sql);
return beanList;
}
@Override
public void saveBook(Connection conn, Book book) {
// 写sql语句
String sql = "insert into books(title,author,price,sales,stock,img_path) values(?,?,?,?,?,?)";
// 调用BaseDao中通用的增删改的方法
update(conn, sql, book.getTitle(), book.getAuthor(), book.getPrice(), book.getSales(), book.getStock(), book.getImgPath());
}
@Override
public void deleteBookById(Connection conn, String bookId) {
// 写sql语句
String sql = "DELETE FROM books WHERE id = ?";
// 调用BaseDao中通用增删改的方法
update(conn, sql, bookId);
}
@Override
public Book getBookById(Connection conn, String bookId) {
// 调用BaseDao中获取一个对象的方法
Book book = null;
// 写sql语句
String sql = "select id,title,author,price,sales,stock,img_path imgPath from books where id = ?";
book = getBean(conn, sql, bookId);
return book;
}
@Override
public void updateBook(Connection conn, Book book) {
// 写sql语句
String sql = "update books set title = ? , author = ? , price = ? , sales = ? , stock = ? where id = ?";
// 调用BaseDao中通用的增删改的方法
update(conn, sql, book.getTitle(), book.getAuthor(), book.getPrice(), book.getSales(), book.getStock(), book.getId());
}
@Override
public Page<book> getPageBooks(Connection conn, Page<book> page) {
// 获取数据库中图书的总记录数
String sql = "select count(*) from books";
// 调用BaseDao中获取一个单一值的方法
long totalRecord = (long) getValue(conn, sql);
// 将总记录数设置都page对象中
page.setTotalRecord((int) totalRecord);
// 获取当前页中的记录存放的List
String sql2 = "select id,title,author,price,sales,stock,img_path imgPath from books limit ?,?";
// 调用BaseDao中获取一个集合的方法
List<book> beanList = getBeanList(conn, sql2, (page.getPageNo() - 1) * Page.PAGE_SIZE, Page.PAGE_SIZE);
// 将这个List设置到page对象中
page.setList(beanList);
return page;
}
@Override
public Page<book> getPageBooksByPrice(Connection conn, Page<book> page, double minPrice, double maxPrice) {
// 获取数据库中图书的总记录数
String sql = "select count(*) from books where price between ? and ?";
// 调用BaseDao中获取一个单一值的方法
long totalRecord = (long) getValue(conn, sql, minPrice, maxPrice);
// 将总记录数设置都page对象中
page.setTotalRecord((int) totalRecord);
// 获取当前页中的记录存放的List
String sql2 = "select id,title,author,price,sales,stock,img_path imgPath from books where price between ? and ? limit ?,?";
// 调用BaseDao中获取一个集合的方法
List<book> beanList = getBeanList(conn, sql2, minPrice, maxPrice, (page.getPageNo() - 1) * Page.PAGE_SIZE, Page.PAGE_SIZE);
// 将这个List设置到page对象中
page.setList(beanList);
return page;
}
}
/<book>/<book>/<book>/<book>/<book>/<book>/<book>/<book>/<book>/<code>
userdaoimpl
<code>package com.kid.bookstore.dao.impl;
import com.kid.bookstore.beans.User;
import com.kid.bookstore.dao.BaseDao;
import com.kid.bookstore.dao.UserDao;
import java.sql.Connection;
/**
* The type User dao.
*
* @author tangf
* @createTime 2020 /03/22 23:14:20
*/
public class UserDaoImpl extends BaseDao<user> implements UserDao {
@Override
public User getUser(Connection conn, User user) {
// 调用BaseDao中获取一个对象的方法
User bean = null;
// 写sql语句
String sql = "select id,username,password,email from users where username = ? and password = ?";
bean = getBean(conn, sql, user.getUsername(), user.getPassword());
return bean;
}
@Override
public boolean checkUsername(Connection conn, User user) {
// 调用BaseDao中获取一个对象的方法
User bean = null;
// 写sql语句
String sql = "select id,username,password,email from users where username = ?";
bean = getBean(conn, sql, user.getUsername());
return bean != null;
}
@Override
public void saveUser(Connection conn, User user) {
//写sql语句
String sql = "insert into users(username,password,email) values(?,?,?)";
//调用BaseDao中通用的增删改的方法
update(conn, sql, user.getUsername(), user.getPassword(), user.getEmail());
}
}
/<user>/<code>
测试脚本
<code>-- ----------------------------
-- Table structure for books
-- ----------------------------
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`author` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL,
`sales` int(255) DEFAULT NULL,
`stock` int(255) DEFAULT NULL,
`img_path` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
SET FOREIGN_KEY_CHECKS = 1;
-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
SET FOREIGN_KEY_CHECKS = 1;/<code>
测试程序
<code>package com.kid.bookstore;
import com.kid.bookstore.beans.Book;
import com.kid.bookstore.beans.User;
import com.kid.bookstore.dao.impl.BookDaoImpl;
import com.kid.bookstore.dao.impl.UserDaoImpl;
import com.kid.util.JDBCUtils;
import org.junit.Test;
import java.sql.Connection;
/**
* @author tangf
* @createTime 2020/03/22 23:17:00
*/
public class AppTest {
private UserDaoImpl userDao = new UserDaoImpl();
private BookDaoImpl bookDao = new BookDaoImpl();
@Test
public void addUsers() throws Exception {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
User user = new User("A", "aa");
userDao.saveUser(conn, user);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void addBooks() throws Exception {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
Book book = new Book("A", "AA", 123);
bookDao.saveBook(conn, book);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
}
/<code>
其他的方法有兴趣的可以测试
总结
至少要有一段时间写到吐
分享到:
閱讀更多 拉斐 的文章
