学习swing实现图书管理系统,本文实现该系统的功能截图,和数据库设计SQL语句供大家参考
1.图书管理系统技术架构
主要技术
Java Swing
开发工具和环境
Eclipse
JDK 1.7
Mysql 5.6
Win10 操作系统
2.图书管理系统数据库设计
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`role` int(255) DEFAULT NULL COMMENT '角色 1学生 2管理员',
`sex` varchar(1) DEFAULT NULL,
`phone` char(11) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
CREATE TABLE `book_type` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type_name` varchar(255) DEFAULT NULL,
`remark` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
CREATE TABLE `book` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`book_name` varchar(255) DEFAULT NULL,
`type_id` int(11) DEFAULT NULL,
`author` varchar(255) DEFAULT NULL,
`publish` varchar(255) DEFAULT NULL,
`price` double(10,2) DEFAULT NULL,
`number` int(11) DEFAULT NULL,
`status` int(11) DEFAULT '1' COMMENT '状态 1上架0下架',
`remark` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
CREATE TABLE `borrowdetail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`book_id` int(11) NOT NULL,
`status` int(11) NOT NULL COMMENT '状态 1在借2已还',
`borrow_time` bigint(20) DEFAULT NULL,
`return_time` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
public class UserDao {
public User login(Connection con,User user)throws Exception {
User resultUser = null;
String sql = "select * from user where username=? and password=? and role = ?";
PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
pstmt.setString(1,user.getUserName());
pstmt.setString(2,user.getPassword());
pstmt.setInt(3,user.getRole());
ResultSet rs = pstmt.executeQuery();
if(rs.next()){
resultUser = new User();
resultUser.setUserId(rs.getInt("id"));
resultUser.setUserName(rs.getString("username"));
resultUser.setSex(rs.getString("sex"));
resultUser.setPhone(rs.getString("phone"));
}
return resultUser;
}
public int addUser(Connection con,User user) throws Exception{
//查询注册用户名是否存在
String sql = "select * from user where userName=? ";
PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
pstmt.setString(1,user.getUserName());
ResultSet rs = pstmt.executeQuery();
if(rs.next()){
return 2;
}
sql="insert into user (username,password,role,sex,phone) values (?,?,?,?,?)";
PreparedStatement pstmt2=(PreparedStatement) con.prepareStatement(sql);
pstmt2.setString(1, user.getUserName());
pstmt2.setString(2, user.getPassword());
pstmt2.setInt(3, user.getRole());
pstmt2.setString(4,user.getSex());
pstmt2.setString(5,user.getPhone());
return pstmt2.executeUpdate();
}
public ResultSet list(Connection con,User user)throws Exception{
StringBuffer sb=new StringBuffer("select * from user where role = 1");
if(!toolUtil.isEmpty(user.getUserName())){
sb.append(" and username like '%"+user.getUserName()+"%'");
}
PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sb.toString());
return pstmt.executeQuery();
}
public int update(Connection con,User user)throws Exception{
String sql="update user set username=?,password=?,sex=?,phone=? where id=?";
PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sql);
pstmt.setString(1, user.getUserName());
pstmt.setString(2, user.getPassword());
pstmt.setString(3, user.getSex());
pstmt.setString(4, user.getPhone());
pstmt.setInt(5, user.getUserId());
return pstmt.executeUpdate();
}
}
public class BookDao {
// 图书添加
public int add(Connection con,Book book)throws Exception{
String sql="insert into book (book_name,type_id,author,publish,price,number,status,remark) values(?,?,?,?,?,?,?,?)";
PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sql);
pstmt.setString(1, book.getBookName());
pstmt.setInt(2, book.getBookTypeId());
pstmt.setString(3, book.getAuthor());
pstmt.setString(4, book.getPublish());
pstmt.setDouble(5, book.getPrice());
pstmt.setInt(6, book.getNumber());
pstmt.setInt(7, book.getStatus());
pstmt.setString(8, book.getRemark());
return pstmt.executeUpdate();
}
// 图书信息查询
public ResultSet list(Connection con,Book book)throws Exception{
StringBuffer sb=new StringBuffer("select b.*,bt.type_name from book b,book_type bt where b.type_id=bt.id");
if(!toolUtil.isEmpty(book.getBookName())){
sb.append(" and b.book_name like '%"+book.getBookName()+"%'");
}
if(!toolUtil.isEmpty(book.getAuthor())){
sb.append(" and b.author like '%"+book.getAuthor()+"%'");
}
if(book.getBookTypeId()!=null && book.getBookTypeId()!=0){
sb.append(" and b.type_id="+book.getBookTypeId());
}
if(book.getStatus()!=null){
sb.append(" and b.status="+book.getStatus());
}
if(book.getBookId() != null){
sb.append(" and b.id="+book.getBookId());
}
sb.append(" ORDER BY b.status");
PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sb.toString());
return pstmt.executeQuery();
}
// 图书信息查询(学生)
public ResultSet listCan(Connection con,Book book)throws Exception{
StringBuffer sb=new StringBuffer("select b.*,bt.type_name from book b,book_type bt where type_id=bt.id and b.status = 1");
if(!toolUtil.isEmpty(book.getBookName())){
sb.append(" and b.book_name like '%"+book.getBookName()+"%'");
}
if(book.getBookId() != null){
sb.append(" and b.id="+book.getBookId());
}
PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sb.toString());
return pstmt.executeQuery();
}
//图书信息删除
public int delete(Connection con,String id)throws Exception{
String sql="delete from book where id=?";
PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sql);
pstmt.setString(1, id);
return pstmt.executeUpdate();
}
//图书信息修改
public int update(Connection con,Book book)throws Exception{
String sql="update book set book_name=?,type_id=?,author=?,publish=?,price=?,number=?,status=?,remark=? where id=?";
PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sql);
pstmt.setString(1, book.getBookName());
pstmt.setInt(2, book.getBookTypeId());
pstmt.setString(3, book.getAuthor());
pstmt.setString(4, book.getPublish());
pstmt.setDouble(5, book.getPrice());
pstmt.setInt(6, book.getNumber());
pstmt.setInt(7, book.getStatus());
pstmt.setString(8, book.getRemark());
pstmt.setInt(9, book.getBookId());
return pstmt.executeUpdate();
}
}
3.图书管理系统管理员功能
3.1图书管理系统登录页面,登录者可根据不同角色进行登录
3.2图书管理系统注册页面,用户可以通过注册后登录该系统
3.3图书管理系统管理员添加书目类别页面
管理员可以添加图书属于哪种类型的,并对该类别做详细说明
3.4图书管理系统管理员查看、修改、删除书目类别页面
3.5图书管理系统管理员添加书籍页面
3.6图书管理系统管理员查看、修改、删除书籍页面
3.7图书管理系统管理员查看、修改用户页面
3.8图书管理系统管理员查看用户借阅信息页面
4.图书管理系统用户功能
用户可以查看历史借阅信息,也可以借书还书。