Java连接数据库在控制台上操作图书管理系统

Java 专栏收录该内容
5 篇文章 0 订阅

本人是一名大二学生,自学Java做了一个图书管理系统的数据库课程设计,能力有限,欢迎大佬指点!

连接数据库操作

/**
 * 建立数据库连接!!
 */
package util;

import java.sql.*;
public class DBConnection {

	public static final String DRIVER ="com.microsoft.sqlserver.jdbc.SQLServerDriver";
	public static final String URL="jdbc:sqlserver://127.0.0.1:1433;databaseName=StudentManagement";
	public static final String USER="sa";
	public static final String PASSWORD="hyc5670526";
	
	Connection conn =null;
	public DBConnection(){
		
		try{
			Class.forName(DRIVER);
		}catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
		
		try{
			conn=DriverManager.getConnection(URL,USER,PASSWORD);
		}catch (SQLException e) {
            e.printStackTrace();
        }
	}
	public Connection getConnection(){
		return this.conn;
	}
	
	
	public void close(){
		if(this.conn!=null){
			try{
				this.conn.close();
			} catch (SQLException e) {
                e.printStackTrace();
            }
		}
	}
}

DAO类

package dao;
import vo.Borrow;



import util.DateConvert;
import vo.User;
public interface IUserDao {
     //插入读者信息
	public boolean doInsert(User user) throws Exception;
	//显示图书信息
	public boolean doSelectAllBook()throws Exception;
	//显示读者信息
	public boolean doSelectAllStudent()throws Exception;
	//显示借阅信息
	public boolean doSelectAllBorrow()throws Exception;
	//按学号修改读者信息
	public boolean doUpdate(int id,User user)throws Exception;
	//按学号删除读者信息
	public boolean doDelete(int id)throws Exception;
	//按图书编号查找图书信息
	public boolean doFindBook(int id)throws Exception;
	//插入借阅信息
	public boolean doInsert(Borrow borrow,DateConvert dateconvert)throws Exception;
	//删除借阅信息
	public boolean doDelete2(int id)throws Exception;

}

DAOImp类

package dao;

import java.sql.*;
import java.util.Scanner;

import util.DBConnection;
import util.DateConvert;
import vo.Borrow;
import vo.User;

public class IUserDaoImp implements IUserDao{
    private DBConnection dbc = null;
    private Connection conn = null;
    private static String username;
    private static String password;
    static Scanner input = new Scanner(System.in);
    
    public IUserDaoImp() {
        this.dbc = new DBConnection();
        this.conn = dbc.getConnection();
    }
    //插入学生信息!!!
    public boolean doInsert(User user) {    
        boolean flag = false;
        PreparedStatement pstmt = null;
        String sql = "insert into Student(Student_id,Student_name,Student_phone,Student_department,Borrow_id) values(?,?,?,?,?)";
        try {
            pstmt = conn.prepareStatement(sql); //模糊查询
            pstmt.setInt(1, user.getId());
            pstmt.setString(2, user.getName());
            pstmt.setString(3, user.getPhone());
            pstmt.setString(4,user.getDepartment());
            pstmt.setString(5, user.getB_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 doSelectAllBook(){

    	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_publisher"));      
       }
    	}catch (SQLException e) {
            e.printStackTrace();
    	 } finally {
             try {
                stmt.close();
             } catch (SQLException e) {
                 e.printStackTrace();
             }
         }
    	return true;
    }
    //查找全部学生信息!!!
    public boolean doSelectAllStudent(){
    	Statement stmt =null;
    	String sql="select * from Student";
    	try{
    		stmt=conn.createStatement();
    		ResultSet Student=stmt.executeQuery(sql);

      	   while(Student.next()){
      		System.out.println("学号:"+Student.getString("Student_id")+"\t"+"姓名:"+Student.getString("Student_name")+"\t"+"联系方式:"+Student.getString("Student_phone")+"\t"+"所在院系:"+Student.getString("Student_department")+"\t"+ "借书证号:"+Student.getString("Borrow_id"));
      	   }
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
    		try{
    			stmt.close();
    		}catch(SQLException e){
    			e.printStackTrace();
    		}
    	}
    	return true;
    }
    //查找全部借阅信息!!!
    public boolean doSelectAllBorrow(){
    	Statement stmt=null;
    	String sql="select *from Borrow";
    	try{
    		stmt=conn.createStatement();
    		ResultSet Borrow=stmt.executeQuery(sql);
    		while(Borrow.next()){
    			System.out.println("借书证号:"+Borrow.getString("Borrow_id")+"\t"+"学号:"+Borrow.getString("Student_id")+"\t"+"图书编号:"+Borrow.getString("Book_id")+"\t"+"借书日期:"+Borrow.getString("Borrow_date")+"\t"+"还书日期:"+Borrow.getString("Return_date")+"\t"+"是否续借:"+Borrow.getString("Judge"));
    			
    		}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
    		try{
    			stmt.close();
    		}catch(SQLException e){
    			e.printStackTrace();
    		}
    	}
    
    	return true;
    }
    //按学号修改学生信息!!!
    public boolean doUpdate(int id,User user){
    	boolean flag=false;
    	 PreparedStatement pstmt = null;
    	 String sql="UPDATE Student SET Student_name=?,Student_phone=?,Student_department=?,Borrow_id=? WHERE Student_id=?";
    	 try{
    		 pstmt=conn.prepareStatement(sql);
    		 pstmt.setString(1, user.getName());
    		 pstmt.setString(2, user.getPhone());
    		 pstmt.setString(3, user.getDepartment());
    		 pstmt.setString(4, user.getB_id());
    		 pstmt.setInt(5, user.getId());
    		 if (pstmt.executeUpdate() > 0) {
                 flag = true;
             }
         } catch (SQLException e) {
             e.printStackTrace();
         } finally {
             try {
                 pstmt.close();
             } catch (SQLException e) {
                 e.printStackTrace();
             }
         }
         return flag;
    }
    
    //按学号删除学生信息!!!
    public boolean doDelete(int id){
    	boolean flag=false;
    	PreparedStatement pstmt=null;
    	String sql="DELETE FROM Student WHERE Student_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 doFindBook(int id){

    	PreparedStatement pstmt=null;
    	String sql="SELECT Book_id,Book_name,Book_author,Book_publisher 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 doInsert(Borrow borrow,DateConvert dateconvert){
    	boolean flag=false;
    	PreparedStatement pstmt=null;
    	String sql="INSERT INTO Borrow(Borrow_id,Student_id,Book_id,Borrow_date,Return_date,Judge) VALUES(?,?,?,?,?,?)";
        try{
        	pstmt=conn.prepareStatement(sql);
        	pstmt.setInt(1, borrow.getInt1());
        	pstmt.setInt(2, borrow.getInt2());
        	pstmt.setInt(3, borrow.getInt3());
        	pstmt.setString(4, borrow.getDate1());
        	pstmt.setString(5, borrow.getDate2());
        	pstmt.setString(6, borrow.getString());
        	
        	  if (pstmt.executeUpdate() > 0) {
                  flag = true;
              }
          } catch (SQLException e) {
              e.printStackTrace();
          } finally {
              try {
                  pstmt.close();
              } catch (SQLException e) {
                  e.printStackTrace();
              }
          }
          return flag;
    }
    
    public boolean doDelete2(int id){
    	boolean flag=false;
    	PreparedStatement pstmt=null;
    	String sql="DELETE FROM Borrow WHERE Borrow_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  void zhuce() throws SQLException{
    	 System.out.println("请输入用户名:");
		  username=input.next();
		  System.out.println("请输入登录密码:");
		  String p1=input.next();
		  System.out.println("请再次输入密码:");
		  String p2=input.next();
		  if(p1.equals(p2)){
			  password=p1;
			  String sql="INSERT INTO Users(Username,Password) VALUES(?,?)";
			  PreparedStatement ptmt=conn.prepareStatement(sql);
			  ptmt.setString(1, username);
			  ptmt.setString(2, password);
			  ptmt.execute();
			  System.out.println("注册成功!");
			  
		  }else{
			  System.out.println("你输入的两次密码不一致,请重新注册!");
			  zhuce();
		  }
    }
    
    public void denglu()throws SQLException{
    	 System.out.println("用户名:");
    	 String username=input.next();
		  System.out.println("密码:");
		  String password=input.next();
		  String sql="SELECT Username,Password FROM Users WHERE Username=? AND Password=?";
		  PreparedStatement ptmt=conn.prepareStatement(sql);
		  ptmt.setString(1, username);
		  ptmt.setString(2, password);
		  ResultSet rs=ptmt.executeQuery();
		  if(rs.next()){
			  System.out.println("登录成功!");
		  }else{
			  System.out.println("账号或密码不正确,请重新登录!\n");
			  denglu();
		  }
    }
}

格式化日期

package util;


import java.text.SimpleDateFormat;//SimpleDateFormat 使得可以选择任何用户定义的日期-时间格式的模式
import java.util.Calendar;
import java.util.Date;

public class  DateConvert {
	
	 SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
     public String nowDay(){
		Calendar c = Calendar.getInstance();
	     c.setTime(new Date());
	     Date d=c.getTime();
	  String day=format.format(d);
	     return day;
	
     }
    public String nextDay(){
    	Calendar c = Calendar.getInstance();
    	 c.setTime(new Date());
    	 c.add(Calendar.MONTH, +1);
    	 Date d = c.getTime();
    	 String month = format.format(d);
         return month;
    }
   
}


InputData类

package util;

import java.io.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class InputData {

	private BufferedReader buf=null;
	
	public InputData(){
		this.buf=new BufferedReader(new InputStreamReader(System.in));
	}
	
	public String getString(String info){
		String temp=null;
		System.out.print(info);
		try{
			temp=this.buf.readLine();
		}catch (IOException e) {
            e.printStackTrace();
        }
		return temp;
		
	}
	
	 public int getInt(String info, String err) {
	        int temp = 0;
	        String str = null;
	        boolean flag = true;
	        while (flag) {
	            str = this.getString(info);
	            if (str.matches("^\\d+$")) {
	                temp = Integer.parseInt(str);//将str转化为int
	                flag = false;
	            } else {
	                System.out.println(err);
	            }
	        }
	        return temp;
	    }
	 
	    
}

JavaBean

package vo;

public class User {
	private int student_id;
	private String student_name;
	private String student_phone;
	private String student_department;
	private String borrow_id;
	
	public User(){}
	
	public User( int id,String name,String phone,String department,String b_id){
		this.student_id=id;
		this.student_name=name;
		this.student_phone=phone;
		this.student_department=department;
		this.borrow_id=b_id;
	}
	
	public User(int id){
		this.student_id=id;
	}
	

	
	public void setId(int id){
		this.student_id=id;
	}
	
	public int  getId(){
		return student_id;
	}
	
	public void setName(String name){
		this.student_name=name;
	}
	
	public String getName(){
		return student_name;
	}
	
	public void setPhone(String phone){
		this.student_phone=phone;
	}
	
	public String getPhone(){
		return student_phone;
	}
	
	public void setDepartment(String department){
		this.student_department=department;
	}
	
	public String getDepartment(){
		return student_department;
	}
	
	public void setB_id(String b_id){
	this.borrow_id=b_id;
	}
	
	public String getB_id(){
		return borrow_id;
	}
	
	public String toString(){
		return "学号"+this.getId()+"姓名"+this.getName()+"联系方式"+this.student_phone+"院

系"+this.student_department+"借书证号"+this.borrow_id;
	}
}

package vo;



public class Borrow {
    private int borrow_id;
    private int student_id;
    private int book_id;
    private String borrow_date;
    private String return_date;
    private String judge;
    
  public  Borrow(int borrow_id,int student_id,int book_id,String borrow_date,String return_date,String judge){
    	this.borrow_id=borrow_id;
    	this.student_id=student_id;
    	this.book_id=book_id;
    	this.borrow_date=borrow_date;
    	this.return_date=return_date;
    	this.judge=judge;
    }
    
    public void setInt1(int borrow_id){
    	this.borrow_id=borrow_id;
    }
    
    public int getInt1(){
    	return borrow_id;
    }
    
    public void setInt2(int student_id){
    	this.student_id=student_id;
    }
    
    public int getInt2(){
    	return student_id;
    }
    
    public void setInt3(int book_id){
    	this.book_id=book_id;
    }
    
    public int getInt3(){
    	return book_id;
    }
    
    public void setDate1(String borrow_date){
    	this.borrow_date=borrow_date;
    }
    
    public String getDate1(){
    	return borrow_date;
    }
    
    public void setDate2(String return_date){
    	this.return_date=return_date;
    }
    
    public String getDate2(){
    	return return_date;
    }
    
    public void setString(String judge){
    	this.judge=judge;
    }
    
    public String getString(){
    	return judge;
    }
}

Menu类

package view;

import java.util.Scanner;


import java.sql.SQLException;
import java.text.ParseException;

import dao.IUserDaoImp;
import util.InputData;
import vo.User;
import vo.Borrow;
import util.DateConvert;
public class Menu {
    IUserDaoImp iu = new IUserDaoImp();
    InputData input = new InputData();
    DateConvert dc=new DateConvert();
    static String MANAGER="hyc";
    static String KEY="123456";


	public Menu() throws ParseException, SQLException{
		while(true){
			this.show();
		}
	}
	public void show() throws ParseException, SQLException{
		 @SuppressWarnings("resource")    //不显示使用了不赞成使用的类或方法时的警告
		Scanner iput=new Scanner(System.in); 
		  System.out.println("-----------------------------欢迎使用图书管理系统------------------------");
		  System.out.println("1.用户登录");
		  System.out.println("2.用户注册");
		  System.out.println("3.管理员登录");
		  int k=iput.nextInt();
		  switch(k){
		  case 1:
			  iu.denglu();
		
		         System.out.println("*************************************************************************");
		         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("请选择:");     
		         int q=iput.nextInt();
		         User user=null; 
		         Borrow borrow=null;
		         DateConvert dateconvert=null;
		         
		         switch(q){
		         case 1:
		        	 int id=input.getInt("请输入学号:", "格式不对,请重新输入!");
			    	   String name=input.getString("请输入人员姓名:");
			    	   String phone=input.getString("请输入联系方式:");
			    	   String department=input.getString("请输入院系:");
			    	  String b_id=input.getString("请输入读书证号:");

			    	   user=new User(id,name,phone,department,b_id);
			    	   if (iu.doInsert(user)) {
		                   System.out.println("添加成功!");        
		               } else {
		                   System.out.println("添加失败!");
		               }break;
		         case 2:
		      	   iu.doSelectAllBook();break;
		         case 3:
		        	 int id5=input.getInt("请输入借书证号:", "格式不对,请重新输入!");
	            	   int id6=input.getInt("请输入学号:", "格式不对,请重新输入!");
	            	   int id7=input.getInt("请输入图书编号:", "格式不对,请重新输入!");
	            	   String dt1=dc.nowDay();
	            	   String dt2=dc.nextDay();
	                  System.out.println("借书时间为:"+dt1);
	                  System.out.println("还书时间为:"+dt2);
	                  String judge="否";
	            	 
	            	   borrow=new Borrow(id5,id6,id7,dt1,dt2,judge);
	            	   
	            	   if (iu.doInsert(borrow,dateconvert)) {
	                       System.out.println("借书成功!");        
	                   } else {
	                       System.out.println("借书失败!");
	                   }
	                   break;
	                   
		         case 4:
		           int id8=input.getInt("请输入还书同学的借书证号:", "格式不对,请重新输入!");            	   
	        		   if(iu.doDelete2(id8)){
	        			   System.out.println("还书成功!");
	        			   
	        		   }else{
	        			   System.out.println("还书失败!");
	        		   }
		         }break;
	            	   
		         case 0:
		         { System.out.println("谢谢使用!");
		        	 System.exit(0);
		         }		      		         
		        break;
		         
		  case 2:
			iu.zhuce();break; 
			case 3:
	    	    System.out.println("请输入用户名:");
	        String manager=iput.next();
	        System.out.println("请输入密码:");
	        String key=iput.next(); 
	        if(manager.equals(MANAGER)&&key.equals(KEY)){
	        	 System.out.println("登入系统成功!");
	        	 System.out.println("*************************************************************************");
		         System.out.println("|                                1.图书信息                             |");
		         System.out.println("|                                2.读者信息                             |");    

		         System.out.println("|                                3.借阅信息                             |");
		         System.out.println("|                                4.修改读者信息                         |");
		         System.out.println("|                                5.删除读者信息                         |");
		         System.out.println("|                                6.查找图书信息                         |");

		         System.out.println("|                                0.退出系统                             |");	 
		         System.out.println("*************************************************************************");
		         System.out.println("请输入一个数字:");
		         int p=iput.nextInt();
		         switch(p){
		               case 1:
			    	   iu.doSelectAllBook();break;
			    	   
		               case 2:
		            	   iu.doSelectAllStudent();break;
		            	   
		               case 3:
		            	   iu.doSelectAllBorrow();break;
		            	   
		               case 4:
		            	   int id2=input.getInt("请输入学号:", "格式不对,请重新输入!");
		            	   String name2=input.getString("请修改人员姓名:");
		            	   String phone2=input.getString("请修改联系方式:");
		            	   String department2=input.getString("请修改院系:");
		            	   String b_id2=input.getString("请修改读书证号:");
		            	   user=new User(id2,name2,phone2,department2,b_id2);
		            	   if(iu.doUpdate(id2, user)){
		            		   System.out.println("修改成功!");
		            	   }else{
		            		   System.out.println("修改失败!");
		            	   }break;
		            	   

		               case 5:
		            	   int id3=input.getInt("请输入要删除学生的学号:", "格式不对,请重新输入!");            	   
		            		   if(iu.doDelete(id3)){
		            			   System.out.println("删除成功!");
		            			   
		            		   }else{
		            			   System.out.println("删除失败!");
		            		   }break;
		            	   
		               case 6:
		            	   int id4=input.getInt("请输入要查找的图书编号:", "格式不对,请重新输入!");
		                   iu.doFindBook(id4);
		                 break;
		                 
		          
		               case 0:
		            	   System.out.println("谢谢使用!");
		            	   System.exit(0);
		               break;
		               
		               default:
		            	   System.out.println("请选择正确的操作!");
		         }
	        	 break;
	        }
	        	 else{
	        		 System.out.println("账号或密码不正确!");
	        	 }	        	
	       }
		  
		 
		
	     
             
	       }	  
	}

主方法

package driver;

import java.sql.SQLException;
import java.text.ParseException;

import view.Menu;

public class BookManager {

    public static void main(String[] args) throws ParseException, SQLException {
        new Menu();
    }

}

数据库为SQL server2008

欢迎各位大神前来指导!!!!!!!!!!!!

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值