1.下載安裝mysql和sqlyog

2.建數(shù)據(jù)庫db_book，注意指定基字符集為UTF8

建庫

3.建表t_book，注意紅色標(biāo)注部分

建表

4.輸入幾個初始化數(shù)據(jù)

輸入數(shù)據(jù)

5.maven項目的pom文件添加依賴

        <!--Java工具集合的依賴-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>4.2.1</version>
        </dependency>
        <!--jdbc的mysql驅(qū)動依賴-->
        <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

## 可選配置
# 是否在日志中顯示執(zhí)行的SQL
showSql = true
# 是否格式化顯示的SQL
formatSql = false
# 是否顯示SQL參數(shù)
showParams = false

7.src建包，建實體類Book.java，數(shù)據(jù)訪問對象接口BookDAO.java及其實現(xiàn)類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;

/**
 * 圖書類的數(shù)據(jù)訪問對象
 * 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;

    /**
     * 根據(jù)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 {
        //調(diào)用工具類的插入方法，給非空字段傳值
        return Db.use().insert(
                Entity.create("t_book")
                        .set("name", book.getName())
                        .set("price", book.getPrice())
        );
    }

    @Override
    public int delete(int id) throws SQLException {
        //根據(jù)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()), //修改的數(shù)據(jù)
                Entity.create("t_book").set("id", book.getId()) //where條件
        );
    }
}

8.在接口BookDAO.java alt+enter,調(diào)出單元測試界面

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 {
    //通過接口回調(diào)，創(chuàng)建一個BookDAO的實例，用來做CRUD操作
    private BookDAO bookDAO = new BookDAOImpl();

    @Test
    public void getAllBooks() throws SQLException {
        //通過bookDAO接口調(diào)用查詢所有圖書的方法，得到一個集合
        List<Entity> bookList = bookDAO.getAllBooks();
        //通過Lambda表達(dá)式遍歷集合，輸出結(jié)果
        bookList.forEach(entity ->
                System.out.println(entity.get("name")+","+entity.get("price")));
    }

    @Test
    public void insert() throws SQLException {
        //創(chuàng)建一個Book對象，用來插入
        Book book = new Book("高數(shù)",12.8);
        //受影響的記錄行數(shù)n
        int n = bookDAO.insert(book);
        //用斷言判定單元測試結(jié)果
        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.新增一本圖書、立刻查詢、修改這本書、立刻再查詢、刪除這本書、再查詢，所有運行截圖如下:

增

查

改

查

刪

查

11.代碼地址：https://github.com/mqxu/javaoop