java连接mysql数据库实现图书馆管理系统
public class DBDao {
private DBConnection dbConnection = null;
private Connection conn = null;
/* private static String username;
private static String password;*/
static Scanner input = new Scanner(System.in);
public DBDao() {
this.dbConnection =new DBConnection();
this.conn = dbConnection.getConnection();
}
//管理员登陆
public boolean denglu(){
System.out.println("----管理员登录----");
System.out.println("输入用户名：");
String adminName=input.next();
System.out.println("输入密码：");
String adminPwd=input.next();
String sql="SELECT ADMIN_NAME,ADMIN_PWD FROM ADMIN WHERE ADMIN_NAME=? AND ADMIN_PWD=?";
PreparedStatement ptmt= null;
try {
ptmt = conn.prepareStatement(sql);
ptmt.setString(1, adminName);
ptmt.setString(2, adminPwd);
ResultSet rs=ptmt.executeQuery();
if (rs.next()) {
System.out.println("登录成功！");
return true;
} else {
System.out.println("账号或密码不正确，请重新登录！\n");
return false;
}
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
//管理员注册
public boolean zhuce(){
System.out.println("----管理员注册----");
System.out.println("输入用户名：");
String adminName=input.next();
System.out.println("输入密码：");
String adminPwd1=input.next();
System.out.println("再次确认密码");
String adminPwd2=input.next();
if(adminPwd1.equals(adminPwd2)){
String password=adminPwd1;
String sql="INSERT INTO ADMIN(ADMIN_NAME,ADMIN_PWD) VALUES(?,?)";
PreparedStatement ptmt= null;
try {
ptmt = conn.prepareStatement(sql);
ptmt.setString(1, adminName);
ptmt.setString(2, password);
ptmt.execute();
System.out.println("注册成功！");
return true;
} catch (SQLException e) {
e.printStackTrace();
}
}else{
System.out.println("你输入的两次密码不一致，请重新注册!");
return false;
}
return false;
}
//新图书入库
public boolean insertBook(Book book){
boolean flag = false;
PreparedStatement pstmt = null;
String sql = "insert into book(book_id,book_name,book_author,book_press) values(?,?,?,?)";
try {
pstmt = conn.prepareStatement(sql); //模糊查询
pstmt.setInt(1, book.getBook_id());
pstmt.setString(2,book.getBook_name());
pstmt.setString(3, book.getBook_author());
pstmt.setString(4,book.getBook_press());
if (pstmt.executeUpdate() > 0) {
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
//查询所有图书信息
public boolean bookInof(){
Statement stmt =null;
String sql="select * from book";
try{
stmt=conn.createStatement();
//执行查询数据库的SQL语句 ，返回一个结果集（ResultSet）对象。
ResultSet Book = stmt.executeQuery(sql);
System.out.println("图书编号"+" 书名"+" " +
"作者"+" 出版社");
while(Book.next()){ //如果对象中有数据，就会循环打印出来
System.out.printf("%-10s",Book.getString("book_id"));
System.out.printf("%-30s",Book.getString("book_name"));
System.out.printf("%-20s",Book.getString("book_author"));
System.out.printf("%-20s\n",Book.getString("book_press"));
}
}catch (SQLException e) {
e.printStackTrace();
} finally {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return true;
}
//查询该图书编号图书信息
public boolean searchBook(int id){
PreparedStatement pstmt=null;
String sql="SELECT book_id,book_name,book_author,book_press FROM book WHERE book_id=?";
try{
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, id);
ResultSet rs=pstmt.executeQuery();
while(rs.next()){
System.out.println("图书编号："+rs.getInt(1)+"\t"+"书名："+rs.getString(2)+"\t"+"图书作者："+rs.getString(3)+"\t"+"出版社："+rs.getString(4));
}
}catch (SQLException e) {
e.printStackTrace();
} finally {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return true;
}
//用图书编号修改图书信息
public boolean bookUpdate(int id,Book book){
boolean flag=false;
PreparedStatement pstmt = null;
String sql="UPDATE book SET book_name=?,book_author=?,book_press=? WHERE book_id=?";
try{
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, book.getBook_name());
pstmt.setString(2, book.getBook_author());
pstmt.setString(3, book.getBook_press());
pstmt.setInt(4, book.getBook_id());
if (pstmt.executeUpdate() > 0) {
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
//按编号删除旧图书
public boolean delelteBook(int id){
boolean flag=false;
PreparedStatement pstmt=null;
String sql="DELETE FROM book WHERE book_id=?";
try{
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, id); //接收id传入sql语句
if (pstmt.executeUpdate() > 0) { // 至少已经更新了一行
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
//办理借阅证
public boolean loanCard(User user){
boolean flag = false;
PreparedStatement pstmt = null;
String sql = "insert into user(user_id,user_name,user_age,user_sex,user_phone) values(?,?,?,?,?)";
try {
pstmt = conn.prepareStatement(sql); //模糊查询
pstmt.setInt(1, user.getUser_id());
pstmt.setString(2, user.getUser_name());
pstmt.setInt(3, user.getUser_age());
pstmt.setString(4, String.valueOf(user.getUser_sex()));
pstmt.setString(5, user.getUser_phone());
if (pstmt.executeUpdate() > 0) {
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
//用借书证搜索读者
public boolean searchUser(int id){
PreparedStatement pstmt=null;
String sql="SELECT user_id,user_name,user_age,user_sex,user_phone FROM user WHERE user_id=?";
try{
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, id);
ResultSet rs=pstmt.executeQuery();
while(rs.next()){
System.out.println("读者借书证号："+rs.getInt(1)+"\t"+"读者姓名："+rs.getString(2)+"\t"+"读者年龄："+rs.getString(3)+"\t"+"读者性别"+rs.getString(4)+"\t"+"读者联系方式"+rs.getString(5));
}
}catch (SQLException e) {
e.printStackTrace();
} finally {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return true;
}
//搜索所有拥有借书证读者
public boolean userInof(){
Statement stmt =null;
String sql="select * from user";
try{
stmt=conn.createStatement();
//执行查询数据库的SQL语句 ，返回一个结果集（ResultSet）对象。
ResultSet Book = stmt.executeQuery(sql);
System.out.println("借书证号"+" 读者姓名"+" " +
"读者年龄"+" 读者性别"+" 联系方式");
while(Book.next()){ //如果对象中有数据，就会循环打印出来
System.out.printf("%-10s",Book.getString("user_id"));
System.out.printf("%20s",Book.getString("user_name"));
System.out.printf("%20s",Book.getString("user_age"));
System.out.printf("%20s",Book.getString("user_sex"));
System.out.printf("%20s\n",Book.getString("user_phone"));
}
}catch (SQLException e) {
e.printStackTrace();
} finally {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return true;
}
//借书
public boolean borrow(Borrow borrow){
if(serachBorrow(borrow.getBook_id())){
return false;
}
boolean flag = false;
PreparedStatement pstmt = null;
String sql = "insert into borrow(user_id,book_id) values(?,?)";
try {
pstmt = conn.prepareStatement(sql); //模糊查询
pstmt.setInt(1, borrow.getUser_id());
pstmt.setInt(2,borrow.getBook_id());
if (pstmt.executeUpdate() > 0) {
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
//还书
public boolean return_book(Borrow borrow){
boolean flag=false;
PreparedStatement pstmt=null;
String sql="DELETE FROM borrow WHERE user_id=? and book_id=?";
try{
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, borrow.getUser_id());
pstmt.setInt(2, borrow.getBook_id());
if (pstmt.executeUpdate() > 0) {
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
//在借阅表中搜索该图书
public boolean serachBorrow(int book_id){
String sql="SELECT book_id FROM borrow WHERE book_id=?";
PreparedStatement ptmt= null;
try {
ptmt = conn.prepareStatement(sql);
ptmt.setInt(1, book_id);
ResultSet rs=ptmt.executeQuery();
if (rs.next()) {
System.out.println("图书已被借走！\n");
return true;
} else {
return false;
}
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
}