图书管理系统代码

qq_64766114

已于 2023-06-05 22:32:05 修改

阅读量1.3k

点赞数

文章标签： java 数据库 sql

于 2023-06-05 17:36:36 首次发布

本文链接：https://blog.youkuaiyun.com/qq_64766114/article/details/131052693

版权

本文介绍了一个使用Java语言编写的图书管理系统，涵盖了数据库连接、SQL操作等核心功能，旨在实现图书的增删查改操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

package jdbcmodel;

public class User {
	private Integer id;//用户表主键
    private String name;//用户名
    private String password;//用户密码
    private Integer role;//用户分类 1用户 2管理员
    private String sex;//用户性别
    private String phone;//用户电话
    //无参构造方法
    public User() {
    	
    }
    //id序列产生，不能直接赋值
    public User(String name, String password, Integer role, String sex, String phone) {
        super();
        this.name = name;
        this.password = password;
        this.role = role;
        this.sex = sex;
        this.phone = phone;
    }
    //get set方法
    public Integer getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getPassword() {
        return password;
    }

    public Integer getRole() {
        return role;
    }

    public String getSex() {
        return sex;
    }

    public String getPhone() {
        return phone;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void setRole(Integer role) {
        this.role = role;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }
}

Book类
package jdbcmodel;

public class Book {
	private Integer bookId;//图书表主键
	private String bookName;//图书名称
	private Integer bookTypeId;//图书类别主键
	private String author;//作者
	private String publish;//出版社
	private double price;//图书价格
	private Integer number;//图书名称
	private Integer status; //图书上架状态 1上架 2下架
	private String remark;//图书描述
	
	public Book() {
		
	}

	public Book(String bookName, Integer bookTypeId, String author, String publish, double price, Integer number,
			Integer status, String remark) {
		super();
		this.bookName = bookName;
		this.bookTypeId = bookTypeId;
		this.author = author;
		this.publish = publish;
		this.price = price;
		this.number = number;
		this.status = status;
		this.remark = remark;
	}

	public Integer getBookId() {
		return bookId;
	}
	
	public void setBookId(Integer bookId) {
		this.bookId = bookId;
	}
	
	public String getBookName() {
		return bookName;
	}
	
	public void setBookName(String bookName) {
		this.bookName = bookName;
	}
	
	public String getAuthor() {
		return author;
	}
	
	public void setAuthor(String author) {
		this.author = author;
	}
	
	public String getRemark() {
		return remark;
	}
	
	public void setRemark(String remark) {
		this.remark = remark;
	}
	
	public Integer getStatus() {
		return status;
	}
	
	public void setStatus(Integer status) {
		this.status = status;
	}
	
	public Integer getBookTypeId() {
		return bookTypeId;
	}
	
	public void setBookTypeId(Integer bookTypeId) {
		this.bookTypeId = bookTypeId;
	}
	
	public String getPublish() {
		return publish;
	}
	
	public void setPublish(String publish) {
		this.publish = publish;
	}
	
	public Integer getNumber() {
		return number;
	}
	
	public void setNumber(Integer number) {
		this.number = number;
	}
	
	public double getPrice() {
		return price;
	}
	
	public void setPrice(double price) {
		this.price = price;
	}
}

Borrow类
package jdbcmodel;

public class Borrow {
	private Integer borrowId;//图书借阅表主键
	private Integer userId;//用户ID
	private Integer bookId;//图书ID
	private Integer status;	//借阅状态  1在借  2已还
	private Long borrowTime;//借书时间
	private Long returnTime;//还书时间
	
	
	public Borrow() {
		
	}
	
	public Borrow(Integer userId, Integer bookId, Integer status, Long borrowTime, Long returnTime) {
		super();
		this.userId = userId;
		this.bookId = bookId;
		this.status = status;
		this.borrowTime = borrowTime;
		this.returnTime = returnTime;
	}



	public Integer getBorrowId() {
		return borrowId;
	}
	
	public void setBorrowId(Integer borrowId) {
		this.borrowId = borrowId;
	}
	
	public Integer getUserId() {
		return userId;
	}
	
	public void setUserId(Integer userId) {
		this.userId = userId;
	}
	
	public Integer getBookId() {
		return bookId;
	}
	
	public void setBookId(Integer bookId) {
		this.bookId = bookId;
	}
	
	public Integer getStatus() {
		return status;
	}
	
	public void setStatus(Integer status) {
		this.status = status;
	}
	
	public Long getBorrowTime() {
		return borrowTime;
	}
	
	public void setBorrowTime(Long borrowTime) {
		this.borrowTime = borrowTime;
	}
	
	public Long getReturnTime() {
		return returnTime;
	}
	
	public void setReturnTime(Long returnTime) {
		this.returnTime = returnTime;
	}
}

Book_Type类
package jdbcmodel;

public class Book_Type {
	private Integer typeId;//图书类别表主键
	private String typeName;//类别名称
	private String remark;//类别描述
	
	public Book_Type() {
		
	}
	
	public Book_Type(String typeName, String remark) {
		super();
		this.typeName = typeName;
		this.remark = remark;
	}

	public Integer getTypeId() {
		return typeId;
	}
	
	public void setTypeId(Integer typeId) {
		this.typeId = typeId;
	}
	
	public String getTypeName() {
		return typeName;
	}
	
	public void setTypeName(String typeName) {
		this.typeName = typeName;
	}
	
	public String getRemark() {
		return remark;
	}
	
	public void setRemark(String remark) {
		this.remark = remark;
	}
	
	@Override
	public String toString() {
		return this.typeName;
	}
}

javadao包
package javadao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Vector;

import jdbcmodel.User;
import jdbcutils.JdbcLianjie;



public class UserDao {
	private Connection con; //数据库连接对象
	private PreparedStatement pstmt; //预处理语句对象
	private ResultSet rs; //结果集对象
	private ResultSetMetaData rsmd; //结果集元数据对象

	//插入一条用户记录到user表中
	public boolean doInsert(User u) {
		con = JdbcLianjie.getConnection(); //获取数据库连接
		try {
			String sql = "insert into user (username,password,role,sex,phone) values (?,?,?,?,?)";
			pstmt = con.prepareStatement(sql); //创建PreparedStatement对象
			pstmt.setString(1, u.getName()); //绑定参数
			pstmt.setString(2, u.getPassword());
			pstmt.setInt(3, u.getRole());
			pstmt.setString(4, u.getSex());
			pstmt.setString(5, u.getPhone());
			int result = pstmt.executeUpdate(); //执行SQL语句并返回受影响行数
			return result > 0;
		} catch (SQLException e) { //处理SQL异常
			e.printStackTrace();
			return false;
		} finally {
			JdbcLianjie.closeConnection(); //关闭数据库连接
		}
	}

	//更新一条用户记录到user表中
	public boolean doUpdate(User u) {
		con = JdbcLianjie.getConnection();
		try {
			String sql = "update user set username=?,password=?,sex=?,phone=? where id=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, u.getName());
			pstmt.setString(2, u.getPassword());
			pstmt.setString(3, u.getSex());
			pstmt.setString(4, u.getPhone());
			pstmt.setInt(5, u.getId());
			int result = pstmt.executeUpdate();
			return result > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			JdbcLianjie.closeConnection();
		}
	}

	//从user表中删除一条用户记录
	public boolean doDelete(User u) {
		con = JdbcLianjie.getConnection();
		try {
			String sql = "delete from user where id=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, u.getId());
			int result = pstmt.executeUpdate();
			return result > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			JdbcLianjie.closeConnection();
		}
	}

	//从user表中查找所有用户记录
	public Vector findAll() {
		con = JdbcLianjie.getConnection();
		Vector rowData = new Vector(); //存储行数据的向量
		try {
			String sql = "select * from user order by id";
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery(); //执行查询操作
			while (rs.next()) { //遍历结果集并处理每行数据
				Vector row = new Vector(); //存储单行数据的向量
				row.add(rs.getInt(1)); //将每个字段添加到行向量中
				row.add(rs.getString(2));
				row.add(rs.getString(3));
				row.add(rs.getInt(4));
				row.add(rs.getString(5));
				row.add(rs.getString(6));
				rowData.add(row); //将行向量添加到行数据向量中
			}
			return rowData; //返回行数据向量
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			JdbcLianjie.closeConnection();
		}
	}

	//通过名字从user表查找
	public Vector findByName(String name) {
		con = JdbcLianjie.getConnection();
		Vector rowData = new Vector();  //创建一个容器对象用于存储查询结果
		try {
			String sql = "select * from user where username like ?";  //模糊查询语句
			pstmt = con.prepareStatement(sql);  //预处理SQL语句，防止SQL注入攻击
			pstmt.setString(1, "%" + name + "%");  //设定第一个参数的值
			rs =  pstmt.executeQuery();  //执行查询
			while (rs.next()) {  //将查询结果逐行读取并加入容器中
				Vector row = new Vector();
				row.add(rs.getInt(1));
				row.add(rs.getString(2));  //查询到的列从1开始算，依次对应各种数据类型
				row.add(rs.getString(3));  //记得要按照表结构里字段的顺序操作
				row.add(rs.getInt(4));
				row.add(rs.getString(5));
				row.add(rs.getString(6));
				rowData.add(row);
			}
			return rowData;  //返回查询结果
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			JdbcLianjie.closeConnection();
		}
	}
	//通过名字和密码从user表查找用户
	public User findByNameandPsd(String name, String psd) {
		con = JdbcLianjie.getConnection();
		User user = null;
		try {
			String sql = "select * from user where username = ? and password = ?";  //精确查询语句
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setString(2, psd);
			rs =  pstmt.executeQuery();  //执行查询
			if (rs.next()) {  //如果查询结果不为空，则将其存入User类型的对象中
				user = new User(rs.getString(2), rs.getString(3), rs.getInt(4), rs.getString(5), rs.getString(6));
				user.setId(rs.getInt(1));
			}
			return user;  //返回查询结果
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			JdbcLianjie.closeConnection();
		}
	}
	//通过名字和密码从user表查找用户类型
	public int findByNameandPsd1(String name, String psd) {
		con = JdbcLianjie.getConnection();
		User user = null;
		try {
			String sql = "select * from user where username = ? and password = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setString(2, psd);
			rs =  pstmt.executeQuery();
			if (rs.next()) {
				user = new User(rs.getString(2), rs.getString(3), rs.getInt(4), rs.getString(5), rs.getString(6));
				user.setId(rs.getInt(1));
			}
			return user.getRole();  // 返回User对象的类型
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}finally {
			JdbcLianjie.closeConnection();
		}
	}

	//获取表格的列名，返回一个 Vector 对象
	public Vector findColumnNames() {
		//获取数据库连接对象
		con = JdbcLianjie.getConnection();
		//创建 Vector 对象存储列名
		Vector columnNames = new Vector();
		try {
			//SQL查询语句，在select语句中为列指定别名以便在表格中显示
			String sql = "select id, username 用户名, password 密码, role 权限, sex 性别, phone 手机号码 from user";
			//创建PreparedStatement对象，并传入SQL语句
			pstmt = con.prepareStatement(sql);
			//执行查询
			rs = pstmt.executeQuery();
			//获取 ResultSetMetaData 对象，用于获取列数和列名等信息
			rsmd = rs.getMetaData();
			int colCount = rsmd.getColumnCount();//获取列数
			//循环遍历所有列名并添加到 Vector 中
			for (int i = 1; i <= colCount; i++) {
				columnNames.add(rsmd.getColumnLabel(i));//获取第i列的列名并添加到Vector中
			}
			return columnNames;//返回存储列名的Vector对象
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			JdbcLianjie.closeConnection();
		}
	}

	//根据用户ID查找用户信息并返回User对象
	public User findByID(int id) {
		con = JdbcLianjie.getConnection();
		try {
			//通过用户ID查询
			String sql = "select * from user where id = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, id);//设置占位符的值
			rs = pstmt.executeQuery();//执行查询
			User user = null;
			// 将ResultSet中的结果封装为User对象
			if (rs.next()) {
				user = new User(rs.getString(2), rs.getString(3), rs.getInt(4), rs.getString(5), rs.getString(6));
				user.setId(rs.getInt(1));
			}
			return user;//返回user对象
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			JdbcLianjie.closeConnection();
		}
	}

	//根据用户名查找用户信息并返回user对象
	public User findByFullName(String name) {
		con = JdbcLianjie.getConnection();
		try {
			//通过用户名称查询
			String sql = "select * from user where username = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, name);
			rs = pstmt.executeQuery();
			User user = null;
			if (rs.next()) {
				user = new User(rs.getString(2), rs.getString(3), rs.getInt(4), rs.getString(5), rs.getString(6));
				user.setId(rs.getInt(1));
			}
			return user;// 返回 User 对象
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			JdbcLianjie.closeConnection();
		}
	}
	//修改用户信息，并返回影响行数
	public int doUpdate1(User u) {
		con = JdbcLianjie.getConnection();
		try {
			//通过用户ID修改
			String sql = "update user set username=?,password=?,sex=?,phone=? where id=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, u.getName());// 设置占位符的值
			pstmt.setString(2, u.getPassword());
			pstmt.setString(3, u.getSex());
			pstmt.setString(4, u.getPhone());
			pstmt.setInt(5, u.getId());
			// 执行更新操作并返回影响行数
			int result = pstmt.executeUpdate();
			return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
//			System.out.println(e.getErrorCode());
			System.out.println(e.getMessage());
			if (e.getMessage().substring(0, 9).equals("ORA-00001")) {
				return -1;
			}
			return 0;
		}finally {
			JdbcLianjie.closeConnection();
		}
	}
}

package javadao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Vector;

import jdbcmodel.Book;
import jdbcmodel.User;
import jdbcutils.JdbcLianjie;
import jdbcutils.toolUtil;

public class BookDao {
	private Connection con;
	private PreparedStatement pstmt;
	private ResultSet rs;
	private ResultSetMetaData rsmd;

	public Vector findColumnNames() {
		con = JdbcLianjie.getConnection();
		Vector columnNames = new Vector();
		try {
			String sql = "SELECT b.id,b.book_name 书名,bt.type_name 类别名称,b.author 作者,b.publish 出版社,b.number 图书数量,b.status 1为上架2为下架,b.remark 书籍描述 FROM book b,book_type bt;";
			pstmt = con.prepareStatement(sql);
			rs =  pstmt.executeQuery();//执行查询
			rsmd = rs.getMetaData();
			int colCount = rsmd.getColumnCount();
			for(int i = 1; i <= colCount; i++) {
				columnNames.add(rsmd.getColumnLabel(i));
			}
			return columnNames;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			JdbcLianjie.closeConnection();
		}
	}

	public Vector findByName(String name) {
		con = JdbcLianjie.getConnection();
		Vector rowData = new Vector();
		try {
			String sql = "SELECT b.id,b.book_name 书名,bt.type_name 类别名称,b.author 作者,b.publish 出版社,b.number 图书数量,b.status 上架状态,b.remark 书籍描述 FROM book b,book_type bt WHERE b.type_id=bt.id AND b.book_name LIKE ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "%" + name + "%");
			rs =  pstmt.executeQuery();//执行查询
			while (rs.next()) {
				Vector row = new Vector();
				row.add(rs.getInt(1));
				row.add(rs.getString(2));
				row.add(rs.getString(3));
				row.add(rs.getString(4));
				row.add(rs.getString(5));
				row.add(rs.getInt(6));
				row.add(rs.getInt(7));
				row.add(rs.getString(8));
				rowData.add