练习-Java代码实现书籍管理系统
文章目录
一、项目需求
已知如下两个表：
publisher：id、name(唯一)、address
book：id、isbn、name(唯一)、publisher_id
实现如下功能：
欢迎进入书籍管理系统
1、出版社管理：增、删(name)、改(name)、查(name)
2、书籍管理：增、删(name)、改(name)、查(name)
3、退出
二、创建表
//创建publisher表
CREATE TABLE `publisher` (
`id` char(36) NOT NULL,
`name` varchar(16) NOT NULL,
`address` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
//创建book表
CREATE TABLE `book` (
`id` char(36) NOT NULL,
`name` varchar(16) NOT NULL,
`isbn` char(11) NOT NULL,
`publisher_id` char(36) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
UNIQUE KEY `isbn` (`isbn`),
KEY `publisher_id` (`publisher_id`),
CONSTRAINT `book_ibfk_1` FOREIGN KEY (`publisher_id`) REFERENCES `publisher` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
三、项目结构
四、log4j.properties
因为在控制台打印的信息有限，所以需要把相关的日志文件保存在一个日志文件中，便于查找问题。导入log4j的jar包并配置log4j.properties。
# DEBUG设置输出日志级别，由于为DEBUG，所以ERROR、WARN和INFO 级别日志信息也会显示出来
log4j.rootLogger=DEBUG,Console,RollingFile
#将日志信息输出到控制台
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern= [%-5p]-[%d{yyyy-MM-dd HH:mm:ss}] -%l -%m%n
#将日志信息输出到操作系统D盘根目录下的log.log文件中
log4j.appender.RollingFile=org.apache.log4j.DailyRollingFileAppender
log4j.appender.RollingFile.File=D://log.log
log4j.appender.RollingFile.layout=org.apache.log4j.PatternLayout
log4j.appender.RollingFile.layout.ConversionPattern=%d [%t] %-5p %-40.40c %X{traceId}-%m%n
五、db.properties
使用JDBC连接数据库的时候有时候需要修改连接的配置信息，如果每次修改都更改源代码不利于项目的维护，所以这里是使用db.properties文件把JDBC的配置信息保存在其中。
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.username=root
jdbc.password=0314
六、PropertiesUtil.java
上面已经把数据库的相关配置信息保存在了db.properties文件中。然后再使用一个PropertiesUtil类读取db.properties文件。
/**
* 获取db.properties文件的工具类
*
* @author Administrator
*/
public class PropertiesUtil {
private static Properties properties = new Properties();
static {
InputStream inputStream = PropertiesUtil.class.getClassLoader().getResourceAsStream("db.properties");
try {
properties.load(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 依据key获取value值
*
* @author Administrator
*/
public String getValue(String key) {
return properties.getProperty(key);
}
}
七、DBUtil.java
在上面已经获得了数据库连接的配置信息，创建一个DBUtil类来对数据库进行增删改查。
/**
* 数据库工具类
*
* @author Administrator
*/
public class DBUtil {
private static Logger logger = Logger.getLogger(DBUtil.class);
static Connection connection = null;
static Statement statement = null;
static ResultSet result = null;
static PreparedStatement pStatement = null;
/**
* 加载驱动
*/
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
logger.debug(e.getMessage(), e);
}
}
/**
* 获取连接
*
* @author Administrator
*/
private static Connection getConnection() {
try {
PropertiesUtil pUtil = new PropertiesUtil();
String url = pUtil.getValue("jdbc.url");
String username = pUtil.getValue("jdbc.username");
String password = pUtil.getValue("jdbc.password");
return DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
logger.debug(e.getMessage(), e);
}
return null;
}
/**
* 数据查询(无法防止SQL注入)
*
* @author Administrator
*/
public static void select(String sql, IRowMapper rowMapper) {
try {
connection = getConnection();
statement = connection.createStatement();
result = statement.executeQuery(sql);
rowMapper.rowMapper(result);
} catch (Exception e) {
logger.debug(e.getMessage(), e);
}finally {
close(result, statement, connection);
}
}
/**
* 数据查询(防止 SQL注入)
*
* @author Administrator
*/
public static void select(String sql, IRowMapper rowMapper, Object ... params) {
try {
connection = getConnection();
pStatement = connection.prepareStatement(sql);
for (int i = 1; i <= params.length; i++) {
pStatement.setObject(i, params[i-1]);
}
result = pStatement.executeQuery();
rowMapper.rowMapper(result);
} catch (Exception e) {
logger.debug(e.getMessage(), e);
}finally {
close(result, pStatement, connection);
}
}
/**
* 数据修改(无法防止SQL注入)
*
* @author Administrator
*/
public static boolean update(String sql) {
try {
connection = getConnection();
statement = connection.createStatement();
return statement.executeUpdate(sql) > 0;
} catch (Exception e) {
logger.debug(e.getMessage(), e);
}finally {
close(statement, connection);
}
return false;
}
/**
* 数据查询(可以防止SQL注入)
*
* @author Administrator
*/
public static boolean update(String sql,Object ... params) {
try {
connection = getConnection();
pStatement = connection.prepareStatement(sql);
for (int i = 1; i <= params.length; i++) {
pStatement.setObject(i, params[i-1]);
}
return pStatement.executeUpdate() > 0;
} catch (Exception e) {
logger.debug(e.getMessage(), e);
}finally {
close(pStatement, connection);
}
return false;
}
/**
* JDBC实现事务处理
*
* @author Administrator
*/
public static void transaction(String ...strings ) {
try {
connection = getConnection();
connection.setAutoCommit(false);
statement = connection.createStatement();
for (String string : strings) {
statement.addBatch(string);
}
statement.executeBatch();
connection.commit();
} catch (Exception e) {
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}finally {
close(statement, connection);
}
}
/**
* 关闭资源
*
* @author Administrator
*/
private static void close(Statement statement, Connection connection) {
try {
if (statement != null) {
statement.close();
}
} catch (SQLException e) {
logger.debug(e.getMessage(), e);
}
try {
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
logger.debug(e.getMessage(), e);
}
}
/**
* 关闭资源
*
* @author Administrator
*/
private static void close(ResultSet result, Statement statement, Connection connection) {
try {
if (result != null) {
result.close();
result = null;
}
} catch (SQLException e) {
logger.debug(e.getMessage(), e);
}
close(statement, connection);
}
}
八、IRowMapper.java
/**
* 实现JDBC查询数据库的接口
*
* @author Administrator
*/
public interface IRowMapper{
void rowMapper(ResultSet res);
}
九、PublisherManage.java
出版社管理模块具体代码实现：
/**
* 出版社管理模块
*
* @author Administrator
*/
public class PublisherManage {
public static void menu() {
System.out.println("1、添加出版社信息");
System.out.println("2、删除出版社信息");
System.out.println("3、修改出版社信息");
System.out.println("4、查询出版社信息");
@SuppressWarnings("resource")
Scanner scanner = new Scanner(System.in);
int option = scanner.nextInt();
switch (option) {
case 1:{
addPulisher();
break;
}
case 2:{
deletePublisher();
break;
}
case 3:{
updatePublisher();
break;
}
case 4:{
queryPublisher();
break;
}
default:
System.out.println("对不起，没有该选项，请重新输入......");
break;
}
}
/**
* 添加出版社信息
*
* @author Administrator
*/
public static void addPulisher() {
@SuppressWarnings("resource")
Scanner scanner = new Scanner(System.in);
System.out.println("请输入出版社名字：");
String name = scanner.next();
String sql = "select id from publisher where name=?";
if (ClientManage.exist(sql,name)) {
System.out.println("系统中已经有该出版社");
return;
} else {
System.out.println("请输入出版社地址：");
String address = scanner.next();
String id = UUID.randomUUID().toString();
sql = "insert into publisher(id,name,address) values(?,?,?)";
if (DBUtil.update(sql,id,name,address)) {
System.out.println("添加成功！");
return;
}
}
System.out.println("添加失败！");
}
/**
* 根据name删除出版社信息
*
* @author Administrator
*/
public static void deletePublisher() {
@SuppressWarnings("resource")
Scanner scanner = new Scanner(System.in);
System.out.println("请输入要删除的出版社名字：");
String name = scanner.next();
String sql = "select id from publisher where name=?";
if (!ClientManage.exist(sql,name)) {
System.out.println("系统中没有该出版社");
return;
}else {
sql = "delete from publisher where name=?";
if (DBUtil.update(sql,name)) {
System.out.println("删除成功！");
}else {
System.out.println("删除失败！");
}
}
}
/**
* 根据name修改出版社信息
*
* @author Administrator
*/
public static void updatePublisher() {
@SuppressWarnings("resource")
Scanner scanner = new Scanner(System.in);
System.out.println("请输入要修改出版社的名字：");
String name = scanner.next();
String sql = "select * from publisher where name=?";
if (ClientManage.getId(sql,name)==null) {
System.out.println("系统中没有该出版社");
return;
} else {
String id = ClientManage.getId(sql,name);
System.out.println("请输入新名字：");
name = scanner.next();
System.out.println("请输入新地址：");
String address = scanner.next();
sql = "update publisher set name=?,address=? where id=?";
if (DBUtil.update(sql,name,address,id)) {
System.out.println("修改成功！");
}else {
System.out.println("修改失败！");
}
}
}
/**
* 查询出版社信息
*
* @author Administrator
*/
public static void queryPublisher() {
@SuppressWarnings("resource")
Scanner scanner = new Scanner(System.in);
System.out.println("请输入要查询的出版社的名字：");
String name = scanner.next();
String sql = "select * from publisher where name=?";
class RowMapper implements IRowMapper{
@Override
public void rowMapper(ResultSet res) {
try {
if (res.next()) {
String id = res.getString("id");
String name = res.getString("name");
String address = res.getString("address");
Publisher publisher = new Publisher(id,name,address);
System.out.println(publisher);
}else {
System.out.println("系统中没有该出版社！");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
RowMapper rowMapper = new RowMapper();
DBUtil.select(sql, rowMapper,name);
}
}
十、BookManage.java
书籍管理模块具体代码实现：
/**
* 书籍管理模块
*
* @author Administrator
*/
public class BookManage {
public static void menu() {
System.out.println("1、添加书籍信息");
System.out.println("2、删除书籍信息");
System.out.println("3、修改书籍信息");
System.out.println("4、查询书籍信息");
@SuppressWarnings("resource")
Scanner scanner = new Scanner(System.in);
int option2 = scanner.nextInt();
switch (option2) {
case 1:{
addBook();
break;
}
case 2:{
deleteBook();
break;
}
case 3:{
updateBook();
break;
}
case 4:{
queryBook();
break;
}
default:
System.out.println("对不起，没有该选项，请重新输入......");
break;
}
}
/**
* 添加书籍信息
*
* @author Administrator
*/
public static void addBook() {
@SuppressWarnings("resource")
Scanner scanner = new Scanner(System.in);
System.out.println("请输入书籍名字：");
String name = scanner.next();
String sql = "select id from book where name=?";
if (ClientManage.getId(sql, name)!=null) {
System.out.println("系统中已经有该书籍");
} else {
String publisherId;
String id = UUID.randomUUID().toString();
System.out.println("请输入ISBN：");
String isbn = scanner.next();
System.out.println("请输入书籍所属的出版社：");
String publisherName = scanner.next();
sql = "select * from publisher where name=?";
if (ClientManage.getId(sql, publisherName)!=null) {
publisherId = ClientManage.getId(sql, publisherName);
} else {
System.out.println("系统中没有该出版社，请先添加");
return;
}
sql = "insert into book(id,name,isbn,publisher_id) values(?,?,?,?)";
if (DBUtil.update(sql,id,name,isbn,publisherId)) {
System.out.println("添加成功！");
}else {
System.out.println("添加失败");
}
}
}
/**
* 根据name删除书籍信息
*
* @author Administrator
*/
public static void deleteBook() {
@SuppressWarnings("resource")
Scanner scanner = new Scanner(System.in);
System.out.println("请输入要删除的书籍名字：");
String name = scanner.next();
String sql = "select * from book where name=?";
if (!ClientManage.exist(sql, name)) {
System.out.println("系统中没有该书籍");
} else {
sql = "delete from book where name=?";
if (DBUtil.update(sql,name)) {
System.out.println("删除成功！");
}else {
System.out.println("删除失败！");
}
}
}
/**
* 根据name修改书籍信息
*
* @author Administrator
*/
public static void updateBook() {
@SuppressWarnings("resource")
Scanner scanner = new Scanner(System.in);
System.out.println("请输入要修改书籍的名字：");
String name = scanner.next();
String sql = "select id from book where name=?";
if (ClientManage.getId(sql, name)==null) {
System.out.println("系统中没有该书籍");
return;
} else {
String id = ClientManage.getId(sql, name);
String publisherId = null;
System.out.println("请输入新名字：");
name = scanner.next();
System.out.println("请输入新ISBN：");
String isbn = scanner.next();
System.out.println("请输入新的出版社名字：");
String publisherName = scanner.next();
sql = "select * from publisher where name=?";
if (ClientManage.getId(sql, publisherName)!=null) {
publisherId = ClientManage.getId(sql, publisherName);
} else {
System.out.println("系统中没有该出版社，请先添加");
return;
}
sql = "update book set name=?,isbn=?,publisher_id=? where id=?";
if (DBUtil.update(sql,name,isbn,publisherId,id)) {
System.out.println("修改成功！");
}else {
System.out.println("修改失败！");
}
}
}
/**
* 查询书籍信息
*
* @author Administrator
*/
public static void queryBook() {
@SuppressWarnings("resource")
Scanner scanner = new Scanner(System.in);
System.out.println("请输入要查询的书籍的名字：");
String name = scanner.next();
String sql = "select * from book where name=?";
class RowMapper implements IRowMapper{
List<Book> list = new ArrayList<>();
@Override
public void rowMapper(ResultSet res) {
try {
if(res.next()) {
String id = res.getString("id");
String name = res.getString("name");
String isbn = res.getString("isbn");
String publisherId = res.getString("publisher_id");
Book book = new Book(id, name, isbn, publisherId);
list.add(book);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
RowMapper rowMapper = new RowMapper();
DBUtil.select(sql, rowMapper, name);
List<Book> list = rowMapper.list;
for (Book book : list) {
String id = book.getId();
String isbn = book.getIsbn();
String publisherId = book.getPublisherId();
sql = "select name from publisher where id=?";
String publisherName = ClientManage.getName(sql, publisherId);
System.out.println(id+" ,"+name+" ,"+isbn+" ,"+publisherName);
return;
}
System.out.println("系统中没有该书籍！");
}
}
十一、ClientManage.java
简单的管理界面
public class ClientManage {
public static void main(String[] args) {
print();
while (true) {
menu();
}
}
private static void print() {
System.out.println("*********************************");
System.out.println("*\t\t\t\t\t\t*");
System.out.println("*\t欢迎进入书籍管理系统\t\t*");
System.out.println("*\t\t\t\t\t\t*");
System.out.println("*********************************");
}
private static void menu() {
System.out.println("1、出版社管理");
System.out.println("2、书籍管理");
System.out.println("3、退出");
System.out.println("请输入操作，按Enter键结束...");
@SuppressWarnings("resource")
Scanner scanner = new Scanner(System.in);
int option = scanner.nextInt();
switch (option) {
case 1:
PublisherManage.menu();
break;
case 2:
BookManage.menu();
break;
case 3:
System.exit(0);
break;
default:
System.out.println("对不起，没有该选项，请重新输入......");
}
}
/**
* 根据name判断出版社(书籍)是否存在
*
* @author Administrator
*/
public static boolean exist(String sql, String name) {
return getId(sql, name)!=null;
}
/**
* 根据name查询id
*
* @author Administrator
*/
public static String getId(String sql, String name) {
class RowMapper implements IRowMapper{
String id = null;
@Override
public void rowMapper(ResultSet res) {
try {
if (res.next()) {
id = res.getString("id");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
RowMapper rowMapper = new RowMapper();
DBUtil.select(sql, rowMapper,name);
return rowMapper.id;
}
/**
* 根据id查询name
*
* @author Administrator
*/
public static String getName(String sql, String id) {
class RowMapper implements IRowMapper{
String name = null;
@Override
public void rowMapper(ResultSet res) {
try {
if (res.next()) {
name = res.getString("name");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
RowMapper rowMapper = new RowMapper();
DBUtil.select(sql, rowMapper,id);
return rowMapper.name;
}
}