使用MySQL实现简易图书管理系统
概述:本系统借鉴了MVC思想,由Maven构建,使用MyBatis+MySQL作为持久层进行数据存储与处理
由于系统三个部分的功能实现思路类似,本文只说明第一部分的实现思路
分析项目结构:
- Data.pojo包下有三个实体类
bookInfo
、readerInfo
、borrowInfo
,依次代表图书信息,读者信息,借阅信息 - Data.UI包为表现层与逻辑层二合一,
bookInfoUI
、readerInfoUI
、borrowInfoUI
三个类依次实现了交互界面的输出和用户请求的处理,并与持久层进行通信 - Data.Mapper包为三个DAO接口,与Mapper配置文件匹配
- resource目录下的三个xml文件为Mapper映射配置文件
数据库结构
功能具体实现
1.图书信息添加功能
使用MyBatis建立与数据库的会话,使用Scanner读入信息,调用DAO(Mapper)接口保存数据库中。
private static void bookInfoAdd() throws Exception {
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");//使用输入流对象加载MyBatis配置文件
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//获取SqlSessionFactory工厂方法
SqlSession sqlSession = sqlSessionFactory.openSession();//调用SqlSessionFactory工厂方法,获取数据库通信,打开会话
bookinfoMapper bookinfoMapper = sqlSession.getMapper(bookinfoMapper.class);//获取DAO接口实例
System.out.println("请输入要添加的图书数量:");
Scanner scanner = new Scanner(System.in);
int n = scanner.nextInt();
bookInfo bookinfo = new bookInfo();
for (int i = 0; i < n; i++) {
System.out.println("请输入书号:");
bookinfo.setId(scanner.nextInt()); scanner.nextLine();
System.out.println("请输入书名:");
bookinfo.setName(scanner.nextLine());
System.out.println("请输入作者:");
bookinfo.setAuthor(scanner.nextLine());
System.out.println("请输入出版社:");
bookinfo.setPress(scanner.nextLine());
System.out.println("请输入出版日期:");
bookinfo.setDate(scanner.nextLine());
System.out.println("请输入存馆数量:");
bookinfo.setNumber(scanner.nextInt());
System.out.println("请输入定价:");
bookinfo.setPrice(scanner.nextDouble());
bookinfoMapper.bookInfoAdd(bookinfo);
sqlSession.commit();
System.out.println("添加成功!");
}
sqlSession.close(); //关闭数据库会话
showbookInfo(); //返回上一级菜单
}
2.图书信息查询
与功能一思路类似
private static void bookInfoQuery() throws Exception {
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
bookinfoMapper bookinfoMapper = sqlSession.getMapper(bookinfoMapper.class);
System.out.println("请输入查询对象:");
System.out.println("1.按书名查询");
System.out.println("2.按作者名");
System.out.println("3.按出版社");
Scanner scanner = new Scanner(System.in);
int choose = scanner.nextInt(); scanner.nextLine();
switch (choose){
case 1 -> {
System.out.println("请输入书的名称:");
String name = scanner.nextLine();
List<bookInfo> bookinfo = bookinfoMapper.selectByName(name);
for (bookInfo bookInfo : bookinfo) {
System.out.println(bookInfo);
}
sqlSession.close();
showbookInfo();
}
case 2 -> {
System.out.println("请输入作者:");
String author = scanner.nextLine();
List<bookInfo> bookinfo = bookinfoMapper.selectByAuthor(author);
for (bookInfo bookInfo : bookinfo) {
System.out.println(bookInfo);
}
sqlSession.close();
showbookInfo();
}
case 3 -> {
System.out.println("请输入出版社:");
String press = scanner.nextLine();
List<bookInfo> bookinfo = bookinfoMapper.selectByPress(press);
for (bookInfo bookInfo : bookinfo) {
System.out.println(bookInfo);
}
sqlSession.close();
showbookInfo();
}
}
}
3.图书信息排序
与功能一思路类似
private static void bookInfoSort() throws Exception {
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
bookinfoMapper bookinfoMapper = sqlSession.getMapper(bookinfoMapper.class);
System.out.println("1.按书号进行升序排序");
System.out.println("2.按书名进行升序排序");
System.out.println("请输入要执行的操作:");
Scanner scanner = new Scanner(System.in);
int choose = scanner.nextInt();
switch (choose){
case 1 -> {
List<bookInfo> bookinfo = bookinfoMapper.selectByIdUp();
for (bookInfo bookInfo : bookinfo) {
System.out.println(bookInfo);
}
sqlSession.close();
showbookInfo();
}
case 2 ->{
List<bookInfo> bookinfo = bookinfoMapper.selectByNameUp();
for (bookInfo bookInfo : bookinfo) {
System.out.println(bookInfo);
}
sqlSession.close();
showbookInfo();
}
}
}
4.图书信息的修改、删除
与功能一思路类似
private static void bookInfoEdit() throws Exception {
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
bookinfoMapper bookinfoMapper = sqlSession.getMapper(bookinfoMapper.class);
bookInfo bookinfo = new bookInfo();
System.out.println("1.按书号进行修改");
System.out.println("2.按书名进行修改");
System.out.println("请输入要进行的操作:");
Scanner scanner = new Scanner(System.in);
int choose = scanner.nextInt(); scanner.nextLine();
switch(choose){
case 1 -> {
System.out.println("请输入书号:");
bookinfo.setId(scanner.nextInt()); scanner.nextLine();
System.out.println("请输入书名:");
bookinfo.setName(scanner.nextLine());
System.out.println("请输入作者:");
bookinfo.setAuthor(scanner.nextLine());
System.out.println("请输入出版社:");
bookinfo.setPress(scanner.nextLine());
System.out.println("请输入出版日期:");
bookinfo.setDate(scanner.nextLine());
System.out.println("请输入存馆数量:");
bookinfo.setNumber(scanner.nextInt());
System.out.println("请输入定价:");
bookinfo.setPrice(scanner.nextDouble());
bookinfoMapper.EditById(bookinfo);
sqlSession.commit();
System.out.println("修改成功!");
sqlSession.close();
showbookInfo();
}
case 2 -> {
System.out.println("请输入书名:");
bookinfo.setName(scanner.nextLine());
System.out.println("请输入书号:");
bookinfo.setId(scanner.nextInt()); scanner.nextLine();
System.out.println("请输入作者:");
bookinfo.setAuthor(scanner.nextLine());
System.out.println("请输入出版社:");
bookinfo.setPress(scanner.nextLine());
System.out.println("请输入出版日期:");
bookinfo.setDate(scanner.nextLine());
System.out.println("请输入存馆数量:");
bookinfo.setNumber(scanner.nextInt());
System.out.println("请输入定价:");
bookinfo.setPrice(scanner.nextDouble());
bookinfoMapper.EditByName(bookinfo);
sqlSession.commit();
System.out.println("修改成功!");
sqlSession.close();
showbookInfo();
}
}
}
private static void bookInfoDelete() throws Exception {
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
bookinfoMapper bookinfoMapper = sqlSession.getMapper(bookinfoMapper.class);
System.out.println("1.按书号删除");
System.out.println("2.按书名删除");
Scanner scanner = new Scanner(System.in);
int choose = scanner.nextInt(); scanner.nextLine();
switch (choose){
case 1 -> {
System.out.println("请输入书号:");
int id = scanner.nextInt();
bookinfoMapper.bookInfoDeleteById(id);
sqlSession.commit();
System.out.println("删除成功");
sqlSession.close();
showbookInfo();
}
case 2 -> {
System.out.println("请输入书名:");
String name = scanner.nextLine();
bookinfoMapper.bookInfoDeleteByName(name);
sqlSession.commit();
System.out.println("删除成功");
sqlSession.close();
showbookInfo();
}
}
}
DAO接口类
package Data.Mapper;
import Data.pojo.bookInfo;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface bookinfoMapper {
List<bookInfo> selectByName(@Param("name") String name);
List<bookInfo> selectByAuthor(@Param("author") String author);
List<bookInfo> selectByPress(@Param("press") String press);
List<bookInfo> selectByNameUp();
List<bookInfo> selectByIdUp();
void bookInfoDeleteByName(@Param("name") String name);
void bookInfoDeleteById(@Param("id") int id);
void bookInfoAdd(bookInfo bookinfo);
int bookNumberQuery(@Param("id") int id);
void bookNumberDec(@Param("id") int id, @Param("bookNum") int bookNum);
void bookNumberInc(@Param("id") int id, @Param("bookNum") int bookNum);
void EditById(bookInfo bookinfo);
void EditByName(bookInfo bookInfo);
String getBname(@Param("id") int id);
}
Mapper配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="Data.Mapper.bookinfoMapper">
<select id="selectByName" resultType="Data.pojo.bookInfo">
select * from bookinfo where name = #{name}
</select>
<select id="selectByAuthor" resultType="Data.pojo.bookInfo">
select * from bookinfo where author = #{author}
</select>
<select id="selectByPress" resultType="Data.pojo.bookInfo">
select * from bookinfo where press = #{press}
</select>
<insert id="bookInfoAdd" >
insert into bookinfo (id, name, author, press, date, number, price)
values (#{id}, #{name}, #{author}, #{press}, #{date}, #{number}, #{price})
</insert>
<select id="selectByNameUp" resultType="Data.pojo.bookInfo">
select * from bookinfo order by name
</select>
<select id="selectByIdUp" resultType="Data.pojo.bookInfo">
select * from bookinfo order by id
</select>
<delete id="bookInfoDeleteByName">
delete from bookinfo where name = #{name}
</delete>
<delete id="bookInfoDeleteById">
delete from bookinfo where id = #{id}
</delete>
<select id="bookNumberQuery" resultType="java.lang.Integer">
select number from bookinfo where id = #{id}
</select>
<select id="getBname" resultType="java.lang.String">
select name from bookinfo where id = #{id}
</select>
<update id="bookNumberDec">
update bookinfo set number = number - #{bookNum} where id = #{id}
</update>
<update id="bookNumberInc">
update bookinfo set number = number + #{bookNum} where id = #{id}
</update>
<update id="EditById">
update bookinfo set name = #{name}, author = #{author}, press = #{press}, date = #{date}, number = #{number}, price = #{price}
where id = #{id}
</update>
<update id="EditByName">
update bookinfo set id = #{id}, author = #{author}, press = #{press}, date = #{date}, number = #{number}, price = #{price}
where name = #{name}
</update>
</mapper>