JDBC主题

  1. JDBC初出茅庐-建立数据库连接 https://www.7benshu.com/post/2020/03/21-1/
  2. JDBC小试牛刀-数据库操作 https://www.7benshu.com/post/2020/03/21-2/
  3. JDBC略有建树-二进制操作 https://www.7benshu.com/post/2020/03/21-3/
  4. JDBC出类拔萃-数据库批量操作 https://www.7benshu.com/post/2020/03/21-4/
  5. JDBC百里挑一|数据库事务 https://www.7benshu.com/post/2020/03/22-1/
  6. JDBC卓越超群-DAO https://www.7benshu.com/post/2020/03/22-2/
  7. JDBC独步江湖-数据库连接池 https://www.7benshu.com/post/2020/04/9-1/

介绍

DAO (DataAccessobjects 数据存取对象)是指位于业务逻辑和持久化数据之间实现对持久化数据的访问。通俗来讲,就是将数据库操作都封装起来。

DAO 模式提供了访问关系型数据库系统所需操作的接口,将数据访问和业务逻辑分离对上层提供面向对象的数据访问接口。

从以上 DAO 模式使用可以看出,DAO 模式的优势就在于它实现了两次隔离。

  • 1、隔离了数据访问代码和业务逻辑代码。业务逻辑代码直接调用DAO方法即可,完全感觉不到数据库表的存在。分工明确,数据访问层代码变化不影响业务逻辑代码,这符合单一职能原则,降低了藕合性,提高了可复用性。
  • 2、隔离了不同数据库实现。采用面向接口编程,如果底层数据库变化,如由 MySQL 变成 Oracle 只要增加 DAO 接口的新实现类即可,原有 MySQ 实现不用修改。这符合 “开-闭” 原则。该原则降低了代码的藕合性,提高了代码扩展性和系统的可移植性。

一个典型的DAO 模式主要由以下几部分组成。

  • 1、DAO接口: 把对数据库的所有操作定义成抽象方法,可以提供多种实现。
  • 2、DAO 实现类: 针对不同数据库给出DAO接口定义方法的具体实现。
  • 3、实体类:用于存放与传输对象数据。
  • 4、数据库连接和关闭工具类: 避免了数据库连接和关闭代码的重复使用,方便修改。

作用:为了实现功能的模块化,更有利于代码的维护和升级。

下图是结构图:

beans

books

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
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);
    }
}

page

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
package com.kid.bookstore.beans;

import java.util.List;

/**
 * 页码类
 *
 * @param <T> the type parameter
 * @author tangf
 * @createTime 2020 /03/22 22:58:21
 */
public class Page<T> {

    /**
     * 每页查到的记录存放的集合
     */
    private List<T> list;

    /**
     * The constant PAGE_SIZE.
     */
    public static final int PAGE_SIZE = 4;

    /**
     * 当前页
     */
    private int pageNo;

    /**
     * 总页数,通过计算得到
     */
    private int totalPageNo;

    /**
     * 总记录数,通过查询数据库得到
     */
    private int totalRecord;

    public Page() {
    }

    public List<T> getList() {
        return list;
    }

    public void setList(List<T> list) {
        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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
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 + '\'' +
                '}';
    }


}

dao

basedao

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
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
 *
 * @param <T> the type parameter
 * @author tangf
 * @createTime 2020 /03/22 22:52:05
 */
public abstract class BaseDao<T> {
    private QueryRunner queryRunner = new QueryRunner();

    /**
     * 定义一个变量来接收泛型的类型
     */
    private Class<T> type;

    /**
     * 获取T的Class对象,获取泛型的类型,泛型是在被子类继承时才确定
     */
    public BaseDao() {
        // 获取子类的类型
        Class clazz = this.getClass();
        // 获取父类的类型
        // getGenericSuperclass()用来获取当前类的父类的类型
        // ParameterizedType表示的是带泛型的类型
        ParameterizedType parameterizedType = (ParameterizedType) clazz.getGenericSuperclass();
        // 获取具体的泛型类型 getActualTypeArguments获取具体的泛型的类型
        // 这个方法会返回一个Type的数组
        Type[] types = parameterizedType.getActualTypeArguments();
        // 获取具体的泛型的类型·
        this.type = (Class<T>) 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<T>(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 List<T> getBeanList(Connection conn, String sql, Object... params) {
        List<T> list = null;
        try {
            list = queryRunner.query(conn, sql, new BeanListHandler<T>(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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
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);

}

userdao

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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);
}

impl

bookdaoimpl

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
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;
    }

}

userdaoimpl

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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());
    }

}

测试脚本

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- ----------------------------
-- 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;

测试程序

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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);
        }

    }
}

其他的方法有兴趣的可以测试

总结

至少要有一段时间写到吐