Mysql实战练习之简单图书管理系统

由于课设需要做这个,于是就抽了点闲余时间,写了下,用Mysql与Java,基本全部都涉及到,包括借书/还书,以及书籍信息的更新,查看所有的书籍。需要的朋友可以参考下

一、梳理功能

1.能够表示书籍信息,针对每本书来说,序号,书名,作者,价格,类型。
2.能够表示用户信息,普通用户,管理员。
3.支持的操作:

  • 对于普通用户:查看书籍列表,查询指定书籍,借书还书。
  • 对于 管理员:查看书籍列表,新增删除书籍。

Mysql实战练习之简单图书管理系统

二、准备数据库

创建用户表和书籍表

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

create database if not exists java100_bookmanager;

use java100_bookmanager;

drop table if exists book;

//设置id为自增主键

create table book(id int primary key auto_increment,name varchar(20),author varchar(20),price int,type varchar(20),isborrowed int);

drop table if exists user;

//同样设置 userid为自增主键并且用户名字不重复

create table user(

userid int primary key auto_increment,

username varchar(20) unique,

password varchar(20),

isadmin int

);

— 插入一些书籍

insert into book values(null,'西游记','吴承恩',10000,'古典小说',0);

insert into book values(null,'三国演义','罗贯中',10000,'古典小说',0);

insert into book values(null,'水浒传','施耐庵',10000,'古典小说',0);

insert into book values(null,'金瓶梅','兰陵笑笑生',10000,'古典小说',0);

–插入一些用户

insert into user values(null,'admin','123',1);

insert into user values(null,'zhangsan','123',0);

三、构造和数据库相关的实体类

书籍

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

public class books {

private int bookid;//书籍编号

private string name;//书名

private string author;//作者

private int price;//价格

private string type;//类型

private boolean isborrowed;//是否被借阅

//set get方法

public int getbookid() {

return bookid;

}

public void setbookid(int bookid) {

this.bookid = bookid;

}

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 int getprice() {

return price;

}

public void setprice(int price) {

this.price = price;

}

public string gettype() {

return type;

}

public void settype(string type) {

this.type = type;

}

public boolean isborrowed() {

return isborrowed;

}

public void setborrowed(boolean borrowed) {

isborrowed = borrowed;

}

@override

public string tostring() {

return "book{" +

"bookid=" + bookid +

", name='" + name + '\\'' +

", author='" + author + '\\'' +

", price=" + price +

", type='" + type + '\\'' +

", isborrowed=" + isborrowed +

'}';

}

用户

有两种用户,一种为普通用户,另一种为管理员,管理员和普通用户看到的menu不同,管理员和普通 用户的类方法也不同
先定义一个抽象类user 让普通用户noramluser和管理员类admin来继承user类

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

abstract public class user {

private int userid;

private string username;

private string password;

ioperation[] operations;//方法数组,表示user类所包含的方法

abstract int menu();//子类要重写menu方法,因为两个子类看到的menu不同

public void dooperation(int choice){//此方法来执行一些操作,如借书还书等

operations[choice].work();

}

public int getuserid() {

return userid;

}

public void setuserid(int userid) {

this.userid = userid;

}

public string getusername() {

return username;

}

public void setusername(string username) {

this.username = username;

}

public string getpassword() {

return password;

}

public void setpassword(string password) {

this.password = password;

}

@override

public string tostring() {

return "user{" +

"userid=" + userid +

", username='" + username + '\\'' +

", password='" + password + '\\'' +

'}';

}

}

normaluser类

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

public class normaluser extends user{

public normaluser(){

this.operations=new ioperation[]{//之后单独开辟一个包,包里存储和实现这些方法

new exitoperation(),//退出系统

new displayoperation(),//查看书籍列表

new findoperation(),//查找书籍

new borrowoperation(),//借阅书籍

new returnoperation(),//还书

};

}

@override

public int menu() {//重写父类menu方法

system.out.println("========================");

system.out.println("欢迎您,"+this.getusername()+"!");

system.out.println("1.查看书籍列表");

system.out.println("2.查找指定书籍");

system.out.println("3.借阅书籍");

system.out.println("4.归还书籍");

system.out.println("0.退出系统");

system.out.println("========================");

system.out.println("请输入选项");

scanner sc=new scanner(system.in);

int choice=sc.nextint();

return choice;

}

}

admin类

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

public class admin extends user {

public admin(){

this.operations=new ioperation[]{

new exitoperation(),//退出系统

new displayoperation(),//查看书籍列表

new findoperation(),//查找书籍

new addoperation(),//添加书籍

new deloperation(),//删除书籍

};

}

@override

public int menu() {

system.out.println("========================");

system.out.println("欢迎您,"+this.getusername()+"您是管理员!");

system.out.println("1.查看书籍列表");

system.out.println("2.查找指定书籍");

system.out.println("3.新增书籍");

system.out.println("4.删除书籍");

system.out.println("0.退出系统");

system.out.println("========================");

system.out.println("请输入选项");

scanner sc=new scanner(system.in);

int choice=sc.nextint();

return choice;

}

}

四、封装数据库相关操作

  • 1.先把数据库链接的操作封装好
  • 2.再把针对书籍表的增删查改操作封装好
  • 3.再把针对用户表的操作封装好

数据库链接操作

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

//在这里封装数据库的连接操作

public class dbutil {

//设置url 账号密码 根据个人设置

private static final string url="jdbc:mysql://127.0.0.1:3306/java100_bookmanager?characterencoding=utf8&&usessl=false";

private static final string username="root";

private static final string password="q986681563";

//饿汉模式

//类加载阶段就会调用静态代码块进行实例化

/*private static datasource datasource=new mysqldatasource();

static{

((mysqldatasource)datasource).seturl(url);

((mysqldatasource)datasource).setuser(username);

((mysqldatasource)datasource).setpassword(password);

}*/

//懒汉模式

//只有首次调用getdatasource方法 才会实例化

private static datasource datasource=null;

public static datasource getdatasource(){

if(datasource==null){

datasource=new mysqldatasource();

((mysqldatasource)datasource).seturl(url);

((mysqldatasource)datasource).setuser(username);

((mysqldatasource)datasource).setpassword(password);

}

return datasource;

}

public static connection getconnection() throws sqlexception {

return getdatasource().getconnection();

}

public static void close(resultset resultset, preparedstatement statement,connection connection){//释放资源

//注释掉的方式更安全

/*if(resultset!=null){

try {

resultset.close();

} catch (sqlexception e) {

e.printstacktrace();

}

}

if(statement!=null){

try {

statement.close();

} catch (sqlexception e) {

e.printstacktrace();

}

}

if(connection!=null){

try {

connection.close();

} catch (sqlexception e) {

e.printstacktrace();

}

}*/

try {

if(resultset!=null) resultset.close();

if(statement!=null) statement.close();

if(connection!=null) connection.close();

} catch (sqlexception e) {

e.printstacktrace();

}

}

}

针对书籍表操作

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

//dao data access object 数据访问对象

public class bookdao {

//1.新增书籍

public boolean add(books book){

connection connection=null;

preparedstatement statement=null;

try {

connection= dbutil.getconnection();

string sql="insert into book values(null,?,?,?,?,?)";

statement=connection.preparestatement(sql);

statement.setstring(1,book.getname());

statement.setstring(2,book.getauthor());

statement.setint(3,book.getprice());

statement.setstring(4,book.gettype());

statement.setint(5,book.isborrowed()?1:0);

int ret=statement.executeupdate();

if(ret!=1) return false;

return true;

} catch (sqlexception e) {

e.printstacktrace();

}finally {

dbutil.close(null,statement,connection);

}

return false;

}

//2.查看所有书籍

public list<books> selectall(){

list<books> list=new arraylist<>();

connection connection=null;

preparedstatement statement=null;

resultset resultset=null;

try {

connection=dbutil.getconnection();

string sql="select*from book";

statement=connection.preparestatement(sql);

resultset=statement.executequery();

while(resultset.next()){

books book=new books();

book.setbookid(resultset.getint("id"));

book.setname(resultset.getstring("name"));

book.setauthor(resultset.getstring("author"));

book.setprice(resultset.getint("price"));

book.settype(resultset.getstring("type"));

book.setborrowed(resultset.getint("isborrowed")==1);

list.add(book);

}

} catch (sqlexception e) {

e.printstacktrace();

}finally {

dbutil.close(resultset,statement,connection);

}

return list;

}

//3.根据名字找书籍

public list<books> selectbyname(string name) {

list<books> list=new arraylist<>();

connection connection=null;

preparedstatement statement=null;

resultset resultset=null;

try {

connection=dbutil.getconnection();

string sql="select* from book where name=?";

statement=connection.preparestatement(sql);

statement.setstring(1,name);

resultset=statement.executequery();

while(resultset.next()){

books book=new books();

book.setbookid(resultset.getint("id"));

book.setname(resultset.getstring("name"));

book.setauthor(resultset.getstring("author"));

book.settype(resultset.getstring("type"));

book.setprice(resultset.getint("price"));

book.setborrowed(resultset.getint("isborrowed")==1);

list.add(book);

}

} catch (sqlexception e) {

e.printstacktrace();

}finally {

dbutil.close(resultset,statement,connection);

}

return list;

}

//4.删除书籍

public boolean delete(int bookid){

connection connection=null;

preparedstatement statement=null;

try {

connection=dbutil.getconnection();

string sql="delete from book where id=?";

statement=connection.preparestatement(sql);

statement.setint(1,bookid);

int ret=statement.executeupdate();

if(ret!=1) return false;

return true;

} catch (sqlexception e) {

e.printstacktrace();

}finally {

dbutil.close(null,statement,connection);

}

return false;

}

//5.借书

public boolean borrowbook(int bookid){

connection connection=null;

preparedstatement statement=null;

preparedstatement statement2=null;

resultset resultset=null;

try {

connection=dbutil.getconnection();

string sql="select * from book where id=?";

statement=connection.preparestatement(sql);

statement.setint(1,bookid);

resultset=statement.executequery();

if(resultset.next()){

boolean isborrowed=(resultset.getint("isborrowed")==1);

if(isborrowed){

system.out.println("书已借出,无法再次借出! bookid="+bookid);

return false;

}

}else{

system.out.println("书不存在 bookid="+bookid);

return false;

}

sql="update book set isborrowed=1 where id=?";

statement2=connection.preparestatement(sql);

statement2.setint(1,bookid);

int ret = statement2.executeupdate();

if(ret!=1) {

system.out.println("借阅失败");

return false;

}

system.out.println("借阅成功");

return true;

} catch (sqlexception e) {

e.printstacktrace();

}finally {

if(resultset!=null) {

try {

connection.close();

} catch (sqlexception throwables) {

throwables.printstacktrace();

}

}

if(statement!=null) {

try {

statement.close();

} catch (sqlexception throwables) {

throwables.printstacktrace();

}

}

if(statement2!=null) {

try {

statement2.close();

} catch (sqlexception throwables) {

throwables.printstacktrace();

}

}

if(connection!=null){

try {

connection.close();

} catch (sqlexception throwables) {

throwables.printstacktrace();

}

}

}

return false;

}

//6.归还

public boolean returnbook(int bookid){

connection connection=null;

preparedstatement statement=null;

preparedstatement statement2=null;

resultset resultset=null;

try {

connection=dbutil.getconnection();

string sql="select* from book where id=?";

statement=connection.preparestatement(sql);

statement.setint(1,bookid);

resultset= statement.executequery();

if(resultset.next()){

boolean isborrowed=(resultset.getint("isborrowed")==1);

if(!isborrowed){

system.out.println("书没有被借出,不需要归还 bookid="+bookid);

return false;

}

}else{

system.out.println("没有该书! bookid="+bookid);

return false;

}

sql="update book set isborrowed=0 where id=?";

statement2=connection.preparestatement(sql);

statement2.setint(1,bookid);

int ret = statement2.executeupdate();

if(ret!=1) return false;

return true;

} catch (sqlexception e) {

e.printstacktrace();

}finally {

if(resultset!=null) {

try {

connection.close();

} catch (sqlexception throwables) {

throwables.printstacktrace();

}

}

if(statement!=null) {

try {

statement.close();

} catch (sqlexception throwables) {

throwables.printstacktrace();

}

}

if(statement2!=null) {

try {

statement2.close();

} catch (sqlexception throwables) {

throwables.printstacktrace();

}

}

if(connection!=null){

try {

connection.close();

} catch (sqlexception throwables) {

throwables.printstacktrace();

}

}

}

return false;

}

}

Mysql实战练习之简单图书管理系统

针对用户表的操作

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

public class userdao {

//根据用户名找密码的逻辑

//username是unique约束的

public user selectbyname(string name){

connection connection=null;

preparedstatement statement=null;

resultset resultset=null;

try {

connection=dbutil.getconnection();

string sql="select* from user where username=?";

statement=connection.preparestatement(sql);

statement.setstring(1,name);

resultset = statement.executequery();

if(resultset.next()){

boolean isadmin=(resultset.getint("isadmin")==1);

user users=null;

if(isadmin){

users=new admin();

}else users=new normaluser();

users.setpassword(resultset.getstring("password"));

users.setuserid(resultset.getint("userid"));

users.setusername(resultset.getstring("username"));

return users;

}

} catch (sqlexception e) {

e.printstacktrace();

}finally {

dbutil.close(resultset,statement,connection);

}

return null;

}

}

编写主逻辑(main方法和login方法)

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

public class main {

public static void main(string[] args) {

user users=login();

while(true){

int choice=users.menu();

users.dooperation(choice);

}

}

private static user login(){

scanner sc=new scanner(system.in);

system.out.println("请输入用户名");

string name=sc.next();

system.out.println("请输入密码");

string password=sc.next();

userdao userdao=new userdao();

user users=userdao.selectbyname(name);

if(users==null){

system.out.println("登陆失败!");

system.exit(0);

}

if(!users.getpassword().equals(password)){

system.out.println("密码错误");

system.exit(0);

}

return users;

}

}

编写operation各种细节

将所有operations操作放在一个包中,定义一个接口operations,所有操作实现这个接口并重写方法
ioperation接口

?

1

2

3

public interface ioperation {

void work();

}

添加书籍操作

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

public class addoperation implements ioperation{

@override

public void work() {

system.out.println("新增书籍!");

scanner sc=new scanner(system.in);

system.out.println("请输入书名");

string name=sc.next();

system.out.println("请输入作者");

string author=sc.next();

system.out.println("请输入价格");

int price=sc.nextint();

system.out.println("请输入类别");

string type=sc.next();

books book=new books();

book.setname(name);

book.setprice(price);

book.settype(type);

book.setauthor(author);

bookdao bookdao=new bookdao();

boolean ret=bookdao.add(book);

if(ret) system.out.println("新增成功");

else system.out.println("新增失败");

}

}

借书操作

?

1

2

3

4

5

6

7

8

9

10

11

public class borrowoperation implements ioperation {

@override

public void work() {

system.out.println("借阅书籍");

system.out.println("请输入要借阅的书籍id");

scanner sc=new scanner(system.in);

int id=sc.nextint();

bookdao bookdao=new bookdao();

boolean ret = bookdao.borrowbook(id);

}

}

删除书籍操作

?

1

2

3

4

5

6

7

8

9

10

11

12

13

public class deloperation implements ioperation{

@override

public void work() {

system.out.println("删除书籍!");

scanner sc=new scanner(system.in);

system.out.println("请输入删除书籍的id");

int id=sc.nextint();

bookdao bookdao=new bookdao();

boolean ret = bookdao.delete(id);

if(ret) system.out.println("删除成功");

else system.out.println("删除失败");

}

}

查看书籍列表操作

?

1

2

3

4

5

6

7

8

9

10

11

12

public class displayoperation implements ioperation {

@override

public void work() {

system.out.println("展示所有书籍");

bookdao bookdao=new bookdao();

list<books> list=bookdao.selectall();

for(books book:list){

system.out.println(book);

}

system.out.println("展示书籍完毕");

}

}

退出系统操作

?

1

2

3

4

5

6

7

public class exitoperation implements ioperation{

@override

public void work() {

system.out.println("退出程序");

system.exit(0);

}

}

查找书籍操作

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

public class findoperation implements ioperation{

@override

public void work() {

system.out.println("根据名字查找书籍");

system.out.println("请输入书名");

scanner sc=new scanner(system.in);

string name=sc.next();

bookdao bookdao=new bookdao();

list<books> books = bookdao.selectbyname(name);

for(books book:books){

system.out.println(book);

}

system.out.println("根据名字查找书籍完毕");

}

}

还书操作

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

public class returnoperation implements ioperation{

@override

public void work() {

system.out.println("归还书籍!");

system.out.println("请输入要归还的书籍的id");

scanner sc=new scanner(system.in);

int id=sc.nextint();

bookdao bookdao=new bookdao();

boolean ret = bookdao.returnbook(id);

if(ret){

system.out.println("归还成功");

}else{

system.out.println("归还失败");

}

}

}

总结:简单的图书管理系统,通过练习掌握简单jdbc语法和api,同时可以帮助理解java中多态继承等概念。

到此这篇关于mysql实战练习之简单图书管理系统的文章就介绍到这了,更多相关mysql 图书管理系统内容请搜索钦钦技术栈以前的文章或继续浏览下面的相关文章希望大家以后多多支持钦钦技术栈!

原文链接:https://blog.csdn.net/m0_52276165/article/details/120469926

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/5032/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-07-24 12:08:25
下一篇 2022-07-24 12:08:38

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!