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>
其他的方法有興趣的可以測試
總結
至少要有一段時間寫到吐
分享到:
閱讀更多 拉斐 的文章
