1.下载安装mysql和sqlyog
2.建数据库db_book，注意指定基字符集为UTF8
建库
3.建表t_book，注意红色标注部分
建表
4.输入几个初始化数据
输入数据
5.maven项目的pom文件添加依赖
<!--Java工具集合的依赖-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>4.2.1</version>
</dependency>
<!--jdbc的mysql驱动依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
<!--单元测试依赖-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
6.在resources目录建一个config文件夹，放入db.setting文件
## db.setting文件
url = jdbc:mysql://localhost:3306/db_book?useUnicode=true&characterEncoding=utf-8
user = root
pass = root
## 可选配置
# 是否在日志中显示执行的SQL
showSql = true
# 是否格式化显示的SQL
formatSql = false
# 是否显示SQL参数
showParams = false
7.src建包，建实体类Book.java，数据访问对象接口BookDAO.java及其实现类BookDAOImpl.java，代码如下：
package com.soft1841.oop.jdbc;
/**
* 图书实体类
*/
public class Book {
private Integer id;
private String name;
private Double price;
public Book(String name, Double price) {
this.name = name;
this.price = price;
}
public Book() {
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
@Override
public String toString() {
return "Book{" +
"id=" + id +
", name='" + name + '\'' +
", price=" + price +
'}';
}
}
package com.soft1841.oop.jdbc;
import cn.hutool.db.Entity;
import java.sql.SQLException;
import java.util.List;
/**
* 图书类的数据访问对象
* Book Data access object
* 用来定义增删改查操作CRUD
*/
public interface BookDAO {
/**
* 查询所有图书信息
*
* @return
*/
List<Entity> getAllBooks() throws SQLException;
/**
* 新增图书，如果成功返回1，否则返回0
*
* @param book
* @return
* @throws SQLException
*/
int insert(Book book) throws SQLException;
/**
* 根据id删除图书
*
* @param id
* @return
* @throws SQLException
*/
int delete(int id) throws SQLException;
/**
* 修改图书信息
* @param book
* @return
* @throws SQLException
*/
int update(Book book) throws SQLException;
}
package com.soft1841.oop.jdbc;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import java.sql.SQLException;
import java.util.List;
public class BookDAOImpl implements BookDAO {
@Override
public List<Entity> getAllBooks() throws SQLException {
return Db.use().findAll("t_book");
}
@Override
public int insert(Book book) throws SQLException {
//调用工具类的插入方法，给非空字段传值
return Db.use().insert(
Entity.create("t_book")
.set("name", book.getName())
.set("price", book.getPrice())
);
}
@Override
public int delete(int id) throws SQLException {
//根据id删除记录
return Db.use().del(
Entity.create("t_book").set("id", id));
}
@Override
public int update(Book book) throws SQLException {
return Db.use().update(
Entity.create().set("price", book.getPrice()), //修改的数据
Entity.create("t_book").set("id", book.getId()) //where条件
);
}
}
8.在接口BookDAO.java alt+enter,调出单元测试界面
image.png
image.png
9.编写单元测试代码BookDAOTest.java，代码如下：
package com.soft1841.oop.jdbc;
import cn.hutool.db.Entity;
import org.junit.Test;
import java.sql.SQLException;
import java.util.List;
import static org.junit.Assert.*;
public class BookDAOTest {
//通过接口回调，创建一个BookDAO的实例，用来做CRUD操作
private BookDAO bookDAO = new BookDAOImpl();
@Test
public void getAllBooks() throws SQLException {
//通过bookDAO接口调用查询所有图书的方法，得到一个集合
List<Entity> bookList = bookDAO.getAllBooks();
//通过Lambda表达式遍历集合，输出结果
bookList.forEach(entity ->
System.out.println(entity.get("name")+","+entity.get("price")));
}
@Test
public void insert() throws SQLException {
//创建一个Book对象，用来插入
Book book = new Book("高数",12.8);
//受影响的记录行数n
int n = bookDAO.insert(book);
//用断言判定单元测试结果
assertEquals(1,n);
}
@Test
public void delete() throws SQLException {
int n = bookDAO.delete(10);
assertEquals(1,n);
}
@Test
public void update() throws SQLException {
Book book = new Book();
book.setPrice(128.8);
book.setId(1);
int n = bookDAO.update(book);
assertEquals(1,n);
}
}
10.新增一本图书、立刻查询、修改这本书、立刻再查询、删除这本书、再查询，所有运行截图如下:
增
查
改
查
删
查
