1.创建数据库，向里面添加数据
代码语言：javascript复制数据库名：BookShop
#用户表 CREATE TABLE USER( id INT(11) PRIMARY KEY AUTO_INCREMENT, username VARCHAR(20) NOT NULL, PASSWORD VARCHAR(32) NOT NULL, realname VARCHAR(20), email VARCHAR(50) NOT NULL, gender VARCHAR(50) NOT NULL,
flag INT(11), role INT(11) );#书籍类别表 CREATE TABLE category( cid INT PRIMARY KEY, cname VARCHAR(30) NOT NULL );#书籍表 CREATE TABLE book( id INT PRIMARY KEY AUTO_INCREMENT , title VARCHAR(50) NOT NULL, author VARCHAR(20) NOT NULL, publicDate DATETIME, publisher VARCHAR(50), isbn VARCHAR(15) NOT NULL, price DECIMAL(8,2) NOT NULL, picture varchar(50), cid INT, CONSTRAINT FOREIGN KEY(cid) REFERENCES category(cid) );
#user添加数据 INSERT INTO USER (username,PASSWORD,realname,email,gender,flag,role) VALUES('admin','888','李明','liming@qq.com','男',1,0); INSERT INTO USER (username,PASSWORD,realname,email,gender,flag,role) VALUES('jiangjiang','123456','犟 犟','jiangjiang@163.com','男',1,1); INSERT INTO USER (username,PASSWORD,realname,email,gender,flag,role) VALUES('yitao','123456','艺 涛','shuliang@163.com','女',1,1); #categroy添加数据 INSERT INTO category (cid,cname) VALUES(10,'科技'),(11,'教育'),(12,'小说'),(13,'文艺'),(14,'经管'),(15,'成功'),(16,'生活'); #添加书籍 INSERT INTO book(title,author,publicDate,publisher,isbn,price,picture,cid) VALUES('Java核心技术 卷I 基础知识','霍斯特曼','2019-12-1','机械工业出版社','9787111636663',102.80,null,10) ,('高性能MySQL（第3版）','特卡琴科','2013-05-10','电子工业出版社','9787121198854',122.90,null,10) ,('Java从入门到精通（第5版）','明日科技','2019-03-1','清华大学出版社','9787302517597',61.40,null,10) ,('Java编程思想（第4版）','Bruce Eckel','2007-06-1','机械工业出版社','9787111213826',100.30,null,10) ,('深入理解Java虚拟机','周志明','2013-06-1','机械工业出版社','9787111421900',62.40,null,10) ,('高等数学（第七版）（上册）','同济大学数学系','2014-07-1','高等教育出版社','9787040396638',40.20,null,11) ,('管理学(第13版)','斯蒂芬·P·罗宾斯','2017-01-1','中国人民大学出版社','9787300234601',66.50,null,14) ,('红楼梦原著版（上、下册）','曹雪芹','2013-01-1','人民文学出版社','9787020002207',38.9,null,12) ,('水浒传（上下册）（全两册）','施耐庵 ,罗贯中','2004-09-1','人民文学出版社','9787020008742',32.9,null,12) ,('西游记（共两册）','吴承恩 ','2007-05-1','人民文学出版社','9787020051564',48.00,null,12);2.搭建开发环境
代码语言：javascript复制 1.创建项目创建项目:bookshop、
2.导入jar包:
commons-dbutils-1.7.jar
commons-logging-1.2.jar
druid-1.1.5.jar
mysql-connector-java-5.1.49.jar
3.创建包结构、配置文件
com.qf.bookshop.domain 实体类
com.qf.bookshop.utils 工具类
com.qf.bookshop.dao Dao接口
com.qf.bookshop.service Service接口
com.qf.bookshop.test 测试类druid.properties
代码语言：javascript复制driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/bookshop?useSSL=false&characterEncoding=utf8
username=root
password=root
initialSize=10
maxActive=50
minIdle=5
maxWait=50003.项目代码
目录
3.1domain层 —> Book
代码语言：javascript复制package com.qf.domain;
import java.math.BigDecimal;
import java.util.Date;
/*
Create Table
CREATE TABLE `book` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(50) NOT NULL,
`author` varchar(20) NOT NULL,
`publicDate` datetime DEFAULT NULL,
`publisher` varchar(50) DEFAULT NULL,
`isbn` varchar(15) NOT NULL,
`price` decimal(8,2) NOT NULL,
`picture` varchar(50) DEFAULT NULL,
`cid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `cid` (`cid`),
CONSTRAINT `book_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `category` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4
*/
public class Book {
private int id;
private String title;
private String author;
private Date publicDate;
private String publisher;
private String isbn;
private BigDecimal price;
private String picture;
private int cid;
public Book(int id, String title, String author, Date publicDate, String publisher, String isbn, BigDecimal price, String picture, int cid) {
this.id = id;
this.title = title;
this.author = author;
this.publicDate = publicDate;
this.publisher = publisher;
this.isbn = isbn;
this.price = price;
this.picture = picture;
this.cid = cid;
}
public Book() {
}
public int getId() {
return id;
}
public void setId(int 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 Date getPublicDate() {
return publicDate;
}
public void setPublicDate(Date publicDate) {
this.publicDate = publicDate;
}
public String getPublisher() {
return publisher;
}
public void setPublisher(String publisher) {
this.publisher = publisher;
}
public String getIsbn() {
return isbn;
}
public void setIsbn(String isbn) {
this.isbn = isbn;
}
public BigDecimal getPrice() {
return price;
}
public void setPrice(BigDecimal price) {
this.price = price;
}
public String getPicture() {
return picture;
}
public void setPicture(String picture) {
this.picture = picture;
}
public int getCid() {
return cid;
}
public void setCid(int cid) {
this.cid = cid;
}
@Override
public String toString() {
return "Book{"
"id=" id
", title='" title '''
", author='" author '''
", publicDate=" publicDate
", publisher='" publisher '''
", isbn='" isbn '''
", price=" price
", picture='" picture '''
", cid=" cid
'}';
}
}3.2dao层 —> BookDao
代码语言：javascript复制public interface BookDao {
//查询所有
List<Book> queryAll();
//根据id查询
Book queryById(int bookId);
//根据书名查询
List<Book> queryByTitle(String title);
//添加
void insert(Book book);
//删除
int delete(int bookId);
//更新
void update(Book book);
//返回数据个数
long getCount();
//查询所有书名
List<String> getTitles();
}3.3 dao层 —> impl层 —> BookDaoImpl
代码语言：javascript复制public class BookDaoImpl implements BookDao {
QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());
@Override
public List<Book> queryAll() {
//创建查询执行器
try {
return qr.query("select id,title,author,publicDate,publisher,isbn,price,picture,cid from book",new BeanListHandler<>(Book.class));
} catch (SQLException e) {
throw new RuntimeException("查询所有书籍失败",e);
}
}
@Override
public Book queryById(int bookId) {
//创建查询执行器
try {
return qr.query("select id,title,author,publicDate,publisher,isbn,price,picture,cid from book where id=?",new BeanHandler<>(Book.class),bookId);
} catch (SQLException e) {
throw new RuntimeException("根据id查询所有书籍失败",e);
}
}
@Override
public List<Book> queryByTitle(String title) {
//创建查询执行器
try {
return qr.query("select id,title,author,publicDate,publisher,isbn,price,picture,cid from book where title like ?",new BeanListHandler<>(Book.class),"%" title "%");
} catch (SQLException e) {
throw new RuntimeException("根据书名查询所有书籍失败",e);
}
}
@Override
public void insert(Book book) {
String sql="insert into book(title,author,publicDate,publisher,isbn,price,picture,cid) values(?,?,?,?,?,?,?,?);";
Object[] params={book.getTitle(),book.getAuthor(),book.getPublicDate(),book.getPublisher(),book.getIsbn(),book.getPrice(),book.getPicture(),book.getCid()};
try {
qr.update(sql,params);
} catch (SQLException e) {
throw new RuntimeException("添加书籍失败",e);
}
}
@Override
public int delete(int bookId) {
String sql="delete from book where id=?";
try {
return qr.update(sql,bookId);
} catch (SQLException e) {
throw new RuntimeException("删除书籍失败",e);
}
}
@Override
public void update(Book book) {
String sql="update book set title=?,author=?,publicDate=?,publisher=?,isbn=?,price=?,picture=?,cid=? where id=?;";
Object[] params={book.getTitle(),book.getAuthor(),book.getPublicDate(),book.getPublisher(),book.getIsbn(),book.getPrice(),book.getPicture(),book.getCid(),book.getId()};
try {
qr.update(sql,params);
} catch (SQLException e) {
throw new RuntimeException("修改书籍失败",e);
}
}
@Override
public long getCount() {
String sql="select count(*) from book";
try {
return qr.query(sql, new ScalarHandler<>());
} catch (SQLException e) {
throw new RuntimeException("查询书籍数量失败",e);
}
}
@Override
public List<String> getTitles() {
String sql="select title from book;";
try {
return qr.query(sql, new ColumnListHandler<String>());
} catch (SQLException e) {
throw new RuntimeException("查询书籍名称失败",e);
}
}
}3.4service层 —> BookService
代码语言：javascript复制public interface BookService {
//查询
List<Book> queryAll();
Book queryById(int bookId);
List<Book> QueryByTitle(String title);
//添加
void add(Book book);
//删除
void remove(int bookId);
//更新
void modify(Book book);
}3.5service层 —> impl —> BookServiceImpl
代码语言：javascript复制public class BookServiceImpl implements BookService {
private BookDao bookDao=new BookDaoImpl();
@Override
public List<Book> queryAll() {
return bookDao.queryAll();
}
@Override
public Book queryById(int bookId) {
return bookDao.queryById(bookId);
}
@Override
public List<Book> QueryByTitle(String title) {
return bookDao.queryByTitle(title);
}
@Override
public void add(Book book) {
bookDao.insert(book);
}
@Override
public void remove(int bookId) {
int count=bookDao.delete(bookId);
if(count<=0){
throw new RuntimeException("书籍不存在");
}
}
@Override
public void modify(Book book) {
bookDao.update(book);
}
}3.6Utils层
代码语言：javascript复制public class DruidUtils {
private static DataSource dataSource=null;
static {
try {
Properties properties=new Properties();
InputStream is=DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(is);
is.close();
dataSource= DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
System.out.println("连接池初始化失败");
}
}
public static DataSource getDataSource(){
return dataSource;
}
}3.7view层
代码语言：javascript复制public class BookSystem {
private static final ThreadLocal<SimpleDateFormat> threadLocal=new ThreadLocal<SimpleDateFormat>(){
@Override
protected SimpleDateFormat initialValue() {
return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
}
};
public static void main(String[] args) {
Scanner input = new Scanner(System.in);
System.out.println("=============欢迎进入千锋书籍系统=============");
System.out.println("请登录");
System.out.println("请输入用户名");
String username = input.next();
System.out.println("请输入密码");
String password = input.next();
UserService userService = new UserServiceImpl();
User user = userService.login(username, password);
//判断是否是管理员
BookService bookService=new BookServiceImpl();
if (user.getRole() == 0) {
//管理员
label:do {
System.out.println("--------1查询所有 2 根据id查询 3根据书名查询 4 添加 5删除 0退出--------");
System.out.println("请输入");
int choice = input.nextInt();
switch (choice) {
case 1:
List<Book> books = bookService.queryAll();
if(books!=null){
for (Book book : books) {
System.out.println(book.toString());
}
}
break;
case 2:
System.out.println("请输入要查询的书籍id");
int bookId=input.nextInt();
Book book = bookService.queryById(bookId);
if(book!=null){
System.out.println(book.toString());
}else{
System.out.println("没有找到");
}
break;
case 3:
System.out.println("请输入要查询的书名");
String title=input.next();
List<Book> booksList = bookService.QueryByTitle(title);
if(booksList!=null){
for (Book b : booksList) {
System.out.println(b.toString());
}
}
break;
case 4:
Book b=bookInfo();
bookService.add(b);
break;
case 5:
System.out.println("请输入删除的书籍id");
int bId=input.nextInt();
try {
bookService.remove(bId);
System.out.println("删除成功");
} catch (Exception e) {
System.out.println(e.getMessage());
}
break;
case 0:
System.out.println("欢迎下次光临");
break label;
default:
System.out.println("输入有误，请重新输入");
break;
}
} while (true);
} else {
//普通用户
System.out.println("--------1查询所有 2 根据id查询 3根据书名查询 0退出--------");
}
}
public static Book bookInfo(){
Scanner input=new Scanner(System.in);
System.out.println("请输入书名");
String title=input.next();
System.out.println("请输入作者");
String author=input.next();
System.out.println("请输入出版日期");
input.nextLine();//读取换行
String publicDate=input.nextLine();
System.out.println("请输入出版社");
String publisher=input.next();
System.out.println("请输入isbn");
String isbn=input.next();
System.out.println("请输入价格");
double price=input.nextDouble();
System.out.println("请输入类别id");
int cid=input.nextInt();
SimpleDateFormat sdf=threadLocal.get();
try {
Book book=new Book(0, title, author, sdf.parse(publicDate), publisher, isbn, new BigDecimal(price), null,cid );
return book;
} catch (ParseException e) {
e.printStackTrace();
}
return null;
}
}3.8运行结果
