jdbc编程练习

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.新增一本图书、立刻查询、修改这本书、立刻再查询、删除这本书、再查询，所有运行截图如下:

增
查
改
查
删
查

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