JavaWeb(二)——使用JDBC实现图书的增删改查

一、 结构分析

二、数据库建表

create table book(
id int primary key auto_increment,
name varchar(200),
author varchar(100),
price decimal(6,2),
publishDate date,
type_id int
);


create table type(
id int primary key auto_increment,
name varchar(100)
);



insert into type (name) values('武侠');
insert into type (name) values('言情');
insert into type (name) values('穿越');
insert into type (name) values('玄幻');

三、创建实例类

图书类Book.java

package Domain;

import java.util.Date;

public class Book {
    Integer id;
    String name;
    String author;
    Double price;
    Date publishDate;

    Type type;

    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 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 Date getPublishDate() {
        return publishDate;
    }

    public void setPublishDate(Date publishDate) {
        this.publishDate = publishDate;
    }

    public Type getType() {
        return type;
    }

    public void setType(Type type) {
        this.type = type;
    }
}

图书类型Type.java

package Domain;

public class Type {

    Integer id;
     String name;
    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;
    }

}

四、创建数据传输层

（1）接口BookDao

package Dao;

import Domain.Book;

import java.util.List;

public interface BookDao {
    //插入书籍
    void insertBook(Book book);
    //删除书籍
    void deleteBookById(Integer id);
    //更换书籍信息
    void updateBook( Book book);
    //通过id找到图书
    Book findBookById(Integer id);
    //查找所有书目
    List<Book> findAll();
}

(2)实现类BookDaoImpl

package Dao;

import Domain.Book;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

public class BookDaoImpl implements  BookDao {
    @Override
    public void insertBook(Book book) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //连接MySQL
            con = DriverManager.getConnection("jdbc:mysql://192.168.202.128:3306/scott?useUnicode=true&characterEncoding=utf8", "root", "123QWEasd!");
            //SQL语句
            pstmt=con.prepareStatement("insert into book (name,author,price,publishDate,type_id) values(?,?,?,?,?)");
            //获值
            pstmt.setString(1,book.getName());
            pstmt.setString(2,book.getAuthor());
            pstmt.setDouble(3,book.getPrice());
            pstmt.setDate(4,new java.sql.Date(book.getPublishDate().getTime()));
            pstmt.setInt(5,book.getType().getId());
            //执行SQL语句
            pstmt.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                pstmt.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    @Override
    public void deleteBookById(Integer id) {

    }

    @Override
    public void updateBook(Book book) {

    }

    @Override
    public Book findBookById(Integer id) {
        return null;
    }

    @Override
    public List<Book> findAll() {
        return null;
    }
}

五、测试类

MainTest

package test;

import Dao.BookDao;
import Dao.BookDaoImpl;
import Domain.Book;
import Domain.Type;

import java.util.Date;

public class MainTest {
    public static void main(String[] args) {
        //建立BookDao接口的实例化类
        BookDao bookDao=new BookDaoImpl();
        //建立Book类的实例化对象
        Book book=new Book();
        book.setAuthor("zhangsan");
        book.setName("tianlunbabu");
        book.setPrice(342.90);
        book.setPublishDate(new Date());
        Type type=new Type();
        type.setId(1);
        book.setType(type);
        //执行插入
        bookDao.insertBook(book);
    }
}