图书馆
前言
一、基础代码
1.DButil
package com.yuan.util;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
public class DButil {
//由于MysalDataSource 对象只需要一份,所以一个static 就够了
private static final DataSource dataSource;
static {
MysqlDataSource db = new MysqlDataSource();
db.setServerName("");
db.setPort();
db.setUser("");
db.setPassword("");
db.setDatabaseName("");
db.setUseSSL(false);
db.setCharacterEncoding("utf-8");
db.setServerTimezone("Asia/Shanghai");
dataSource = db;
}
//抛出异常
public static Connection connection() throws SQLException{
//所以谁需要直接调用connection就可以
return dataSource.getConnection();
}
}
2.Commands
①.BorrowBookCommand
package com.yuan.commands;
import com.yuan.util.DButil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class BorrowBookCommand {
public static void main(String[] args) throws SQLException {
// 1. 登录
// 1.1 证明你是你
// 1.2 权限(角色)
Scanner scanner = new Scanner(System.in);
System.out.print("请输入用户名: ");
String username = scanner.nextLine();
// select * from readers where name = ?
int currentUserRid;
try (Connection c = DButil.connection()) {
String sql = "select rid from readers where name = ?";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, username);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) {
System.out.println("登录失败");
return;
}
currentUserRid = rs.getInt("rid");
}
}
}
System.out.println("登录成功");
System.out.print("请输入书名: ");
String bookName = scanner.nextLine();
// select 书籍有没有 && count > 0
// insert records + update books
int bid;
int count;
try (Connection c = DButil.connection()) {
String sql = "select bid, count from books where name = ?";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, bookName);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) {
System.out.println("查无此书");
return;
}
bid = rs.getInt("bid");
count = rs.getInt("count");
if (count == 0) {
System.out.println("书被借空了");
return;
}
}
}
}
// 借书操作
try (Connection c = DButil.connection()) {
String sql = "update books set count = count - 1 where bid = ?";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setInt(1, bid);
ps.executeUpdate();
}
}
try (Connection c = DButil.connection()) {
String sql = "insert into records (rid, bid) values (?, ?)";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setInt(1, currentUserRid);
ps.setInt(2, bid);
ps.executeUpdate();
}
}
}
}
②.PutAwaycommand
package com.yuan.commands;
import com.yuan.util.DButil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
//书籍上架命令
//1.属于管理员角色->不需要考虑登录
//2.需要用户输入: 1.书名 2.本次上架的书籍数量
//3.实际执行的SQL:
// select * from books where name = ?; 因为name唯一,所以查询出来的 行数:0 or 1
// insert into book(...)
// update books set ... where bid = ?;
public class PutAwaycommand {
public static void main(String[] args) throws SQLException {
//1.不存在登录
//2.提示用户输入本次操作的基本信息
Scanner scanner = new Scanner(System.in);
System.out.print("请输入书名:");
String name = scanner.nextLine();
System.out.print("请输入本次上架的书籍数量");
int count = scanner.nextInt();
//打印日志
System.out.println("DEBUG:读取到的用户输入的书名是 | " + name + "|");
System.out.println("DEBUG:读取到的用户输入的数量是 | " + count + "|");
//3.开始执行SQL
//3.1先去执行select
Integer bid = null;
try(Connection c = DButil.connection()){
String sql = "select bid from books where name = ?";
try(PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, name);
System.out.println("DEBUG:执行SQL: " + ps);
try(ResultSet rs = ps.executeQuery()){
if (rs.next()){
//说明这里返回一行了
bid = rs.getInt("bid");
}
}
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
System.out.println("DEBUG:根据书名查询到 bid = " +bid );
//根据bid是否为null
if (bid == null) {
//通过insert进行上架
System.out.println("DEBUG:使用 insert 进行上架");
try (Connection c = DButil.connection()) {
String sql = "insert into books (name , count , total) values (? ,? ,?)";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, name);
ps.setInt(2, count);
ps.setInt(3, count);
System.out.println("DEBUG:执行SQL: " + ps);
ps.executeUpdate();
}
}
}else{
//通过update 进行 上架
System.out.println("DEBUG:使用 update 进行上架");
try(Connection c = DButil.connection()){
String sql = "update books set count = count + ? ,total = total + ? where bid = ?";
try (PreparedStatement ps = c.prepareStatement(sql)){
ps.setInt(1,count);
ps.setInt(2,count);
ps.setInt(3,bid);
System.out.println("DEBUG:执行SQL: " + ps);
ps.executeUpdate();
}
}
}
System.out.println("上架成功");
}
}
③.PutOffcommend
import com.yuan.util.DButil;
import java.sql.*;
import java.util.Map;
import java.util.Properties;
import java.util.Scanner;
import java.util.concurrent.Executor;
public class PutOffcommend {
public static void main(String[] args) throws SQLException {
// 1. 不需要的登录
// 2. 读取用户输入
Scanner scanner = new Scanner(System.in);
System.out.print("请输入书名: ");
String name = scanner.nextLine();
System.out.print("请输入本次上架的书籍数量: ");
int count = scanner.nextInt();
System.out.println("DEBUG: 读取到用户输入的书名是 |" + name + "|");
System.out.println("DEBUG: 读取到用户输入的数量是 |" + count + "|");
// 3. sql
// 3.1 select
// 根据用户输入 和 总量的关系,决定是 delete 还是 update
Integer bid = null;
Integer bookCount = null;
Integer total = null;
try (Connection c = DButil.connection()) {
String sql = "select bid, count, total from books where name = ?";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, name);
System.out.println("DEBUG: 执行 SQL: " + ps);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
bid = rs.getInt("bid");
bookCount = rs.getInt("count");
total = rs.getInt("total");
}
}
}
}
System.out.println("DEBUG: 根据书名查询到的 bid = " + bid);
System.out.println("DEBUG: 根据书名查询到的 bookCount = " + bookCount);
System.out.println("DEBUG: 根据书名查询到的 total = " + total);
if (bid == null) {
System.out.println("没有此书");
return;
}
if (count >= total) {
// delete
System.out.println("DEBUG: 使用 delete 下架");
try (Connection c = DButil.connection()) {
String sql = "delete from books where bid = ?";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setInt(1, bid);
System.out.println("DEBUG: 执行 SQL: " + ps);
ps.executeUpdate();
}
}
} else {
// update
System.out.println("DEBUG: 使用 update 下架");
try (Connection c = DButil.connection()) {
String sql = "update books set count = count - ?, total = total - ? where bid = ?";
// TODO: 存在一个 BUG,书籍的存量可能会出现负数
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setInt(1, count);
ps.setInt(2, count);
ps.setInt(3, bid);
System.out.println("DEBUG: 执行 SQL: " + ps);
ps.executeUpdate();
}
}
}
}
}
二、代码改进
1、通过对象封装关联数据——面向对象思想
1.model
①book
import java.util.Objects;
public class book {
public int bid;
public String name;
public int count;
public int total;
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
book book = (book) o;
return bid == book.bid && Objects.equals(name, book.name);
}
@Override
public int hashCode() {
return Objects.hash(bid, name);
}
}
②reader
import java.util.Objects;
public class reader {
public int rid;
public String name;
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
reader reader = (reader) o;
return rid == reader.rid && Objects.equals(name, reader.name);
}
@Override
public int hashCode() {
return Objects.hash(rid, name);
}
}
③record
import java.util.Objects;
public class record {
public int reid;
public int rid;
public int bid;
public String borrowedAt;
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
record record = (record) o;
return reid == record.reid;
}
@Override
public int hashCode() {
return Objects.hash(reid);
}
}
2.commands- BorrowBookCommandV2
import com.yuan.model.book;
import com.yuan.model.reader;
import com.yuan.util.DButil;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class BorrowBookCommandV2 {
public static void main(String[] args) throws SQLException {
reader currrentUser = new reader() ;
// 1. 登录
// 1.1 证明你是你
// 1.2 权限(角色)
Scanner scanner = new Scanner(System.in);
System.out.print("请输入用户名: ");
currrentUser.name = scanner.nextLine();
// select * from readers where name = ?
int currentUserRid;
try (Connection c = DButil.connection()) {
String sql = "select rid from readers where name = ?";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, currrentUser.name);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) {
System.out.println("登录失败");
return;
}
currrentUser.rid = rs.getInt("rid");
}
}
}
book book = new book();
System.out.println("登录成功");
System.out.print("请输入书名: ");
String bookName = scanner.nextLine();
// select 书籍有没有 && count > 0
// insert records + update books
try (Connection c = DButil.connection()) {
String sql = "select bid, count from books where name = ?";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, book.name);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) {
System.out.println("查无此书");
return;
}
book.bid = rs.getInt("bid");
book.count = rs.getInt("count");
if (book.count == 0) {
System.out.println("书被借空了");
return;
}
}
}
}
// 借书操作
try (Connection c = DButil.connection()) {
String sql = "update books set count = count - 1 where bid = ?";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setInt(1, book.bid);
ps.executeUpdate();
}
}
try (Connection c = DButil.connection()) {
String sql = "insert into records (rid, bid) values (?, ?)";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setInt(1, currrentUser.rid);
ps.setInt(2, book.bid);
ps.executeUpdate();
}
}
}
}
2、java中日期相关的类和方法
java.util.Date是java.sql.Date的父类
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
public class DateDemo {
public static void main(String[] args) {
Date date = new Date();//当前时间
System.out.println(date);
DateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
String s = df.format(date);
System.out.println(s);
}
}
对应commands- BorrowBookCommandV2改动为
try (Connection c = DButil.connection()) {
String sql = "insert into records (rid, bid,borrowed_at) values (?, ?,?)";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setInt(1, currrentUser.rid);
ps.setInt(2, book.bid);
ps.setTimestamp(3,new Timestamp(new Date().getTime()));
ps.executeUpdate();
}
}
3、带密码的登录认证
解决方案,在readers表中多存一列密码(当前学习可以使用,但是以后不能这么使用,对用户密码安全产生隐患)
首先
ALTER TABLE `lib`.`readers`
ADD COLUMN `password` VARCHAR(45) NOT NULL AFTER `name`;
对应commands- BorrowBookCommandV2改动为
public static void main(String[] args) throws SQLException {
reader currrentUser = new reader() ;
Scanner scanner = new Scanner(System.in);
System.out.print("请输入用户名: ");
currrentUser.name = scanner.nextLine();
System.out.print("请输入用户密码: ");
currrentUser.password = scanner.nextLine();
int currentUserRid;
try (Connection c = DButil.connection()) {
String sql = "select rid from readers where name = ? and password = ?";
......
}
......