使用SpringMVC框架实现的小型Web系统——myLibrary图书借阅系统

2024-02-18 23:39:58

最近在学习SpringMVC框架，为了练习，写了一个小型的web项目，使用的是典型的三层结构，控制层Controller，数据库Dao，数据库用的是Mysql，前端用的是jsp。

项目的结构和功能分解如下

使用SpringMVC框架实现的小型Web系统——myLibrary图书借阅系统

 

 

 使用SpringMVC框架实现的小型Web系统——myLibrary图书借阅系统

 

 

 

 

 

 

 

 

其中*1处需要用户输入用户名和密码，

使用SpringMVC框架实现的小型Web系统——myLibrary图书借阅系统

 

 

 

username<input type="text" name="username"><br/>
password<input type="password" name="password"><br/>

然后跳转到Controller中的CheckLogin中检验

 

查询使用SpringMVC框架实现的小型Web系统——myLibrary图书借阅系统

 

CheckLogin.java中的关键代码为

@Controller
@SessionAttributes("student")
public class CheckLogin{
    @RequestMapping(method=RequestMethod.POST,value="check")//必须将@RequestMapping("")写在方法前，类前可写可不写
    public ModelAndView check(@RequestParam("username") String username,@RequestParam("password") String password) {
        Student student = new Student();
        student.setName(username);
        student.setPassword(password);
        int count=Login.isLogin(student);
        if(count>0) {
            ModelAndView modelAndView = new ModelAndView("success");
            modelAndView.addObject("student", student);
            return modelAndView;
        }else {
            ModelAndView modelAndView = new ModelAndView("login");
            return modelAndView;
        }
    }
    }

login.java数据库代码为

public class Login {
    private static final String url="jdbc:mysql://localhost:3306/test";
    private static final String user="root";
    private static final String password="1234";
    public static int isLogin(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int count=-1;
        try {
            
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(url, user, password);
            String sql = "select count(*) from student where name=? and password=?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, student.getName());
            preparedStatement.setString(2, student.getPassword());
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()) {
                count=resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        if(connection!=null) {
            try {
                connection.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(preparedStatement!=null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return count;
    }

 

*2，登录之后借阅图书，需要先根据书名查询图书，

使用SpringMVC框架实现的小型Web系统——myLibrary图书借阅系统

 

 

 

使用SpringMVC框架实现的小型Web系统——myLibrary图书借阅系统

 

使用SpringMVC框架实现的小型Web系统——myLibrary图书借阅系统

 

 

 QueryBook.java代码为，前端传来bookname

@Controller
public class QueryBook {
    @RequestMapping(method=RequestMethod.POST,value="queryBook")
    public ModelAndView queryBook(@RequestParam("bookname") String bookname) {
        Book book=null;
        book=QBook.getBookByName(bookname);
        ModelAndView modelAndView = new ModelAndView("queryBook");
        if(book!=null) {
            modelAndView.addObject("book", book);
            return modelAndView;
        }else {
            return modelAndView;
        }
        //return "a";
    }
}

连接数据库查询

public class QBook {
    private static final String url="jdbc:mysql://localhost:3306/test";
    private static final String user="root";
    private static final String password="1234";
    public static Book getBookByName(String bookname1) {
        Book book=new Book();
        Connection connection = null;
        PreparedStatement prepareStatement = null;
        ResultSet resultSet = null;
        String sql = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(url, user, password);
            sql="select id,bookname,count from book where bookname=?";
            prepareStatement = connection.prepareStatement(sql);
            prepareStatement.setString(1, bookname1);
            //prepareStatement.setString(2, bookname2);
            resultSet = prepareStatement.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            while(resultSet.next()) {
                book.setId(resultSet.getInt("id"));
                book.setName(resultSet.getString("bookname"));
                book.setCount(resultSet.getInt("count"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        if(connection!=null) {
            try {
                connection.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(prepareStatement!=null) {
            try {
                prepareStatement.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return book;
    }

查询结果

使用SpringMVC框架实现的小型Web系统——myLibrary图书借阅系统

 

 

 *3:点击借阅后，数据库中名字为Java的书数目减一，借阅列表中增加一条记录，通调用Dao层UpdateBook.java和UpdateList.java中的方法修改。

使用SpringMVC框架实现的小型Web系统——myLibrary图书借阅系统

 

 

 BorrowBook.java

@Controller
public class BorrowBook {
@RequestMapping(method=RequestMethod.POST,value="borrowBook")
public ModelAndView borrowBook(@RequestParam("bookname") String bookname,HttpServletRequest request) {
    HttpSession session = request.getSession();
    Student student=(Student) session.getAttribute("student");
    String name = student.getName();
    UpdateList.addBook(name,bookname);
    int count=UpdateBook.reduceBookByName(bookname);
    if(count>0) {
        ModelAndView modelAndView = new ModelAndView("borrowDone");
        return modelAndView;
    }else {
        ModelAndView modelAndView = new ModelAndView("borrowFail");
        return modelAndView;
    }
}
}

Dao层UpdateBook.java和UpdateList.java中的方法

public static int reduceBookByName(String bookname) {
   Book book=new Book();
   Connection connection = null;
   PreparedStatement prepareStatement = null;
   ResultSet resultSet = null;
   String sql = null;
   int count=0;
   try {
    Class.forName("com.mysql.jdbc.Driver");
    connection = DriverManager.getConnection(url, user, password);
    sql="select count from book where bookname=?";
    prepareStatement = connection.prepareStatement(sql);
    prepareStatement.setString(1, bookname);
    resultSet = prepareStatement.executeQuery();
    if(resultSet.next()) {
     int has=resultSet.getInt(1);
     if(has>0) {
      try {
       Class.forName("com.mysql.jdbc.Driver");
       connection = DriverManager.getConnection(url, user, password);
       sql="update book set count=count-1 where bookname=?";
       prepareStatement = connection.prepareStatement(sql);
       prepareStatement.setString(1, bookname);
       count= prepareStatement.executeUpdate();
       //System.out.print(count);
      } catch (SQLException e) {
       e.printStackTrace();
      } catch (ClassNotFoundException e) {
       e.printStackTrace();
      }
     }else {
      
      //System.out.print(count);
      return count;
     }
    }
   } catch (SQLException e) {
    e.printStackTrace();
   } catch (ClassNotFoundException e) {
    e.printStackTrace();
   }
   
   
   if(connection!=null) {
    try {
     connection.close();
    } catch (SQLException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    }
   }
   if(prepareStatement!=null) {
    try {
     prepareStatement.close();
    } catch (SQLException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    }
   }
   //System.out.print(count);
   return count;
  }
public static int reduceBook(int returnBookId) {
  String sql=null;
  Connection connection = null;
  PreparedStatement preparedStatement = null;
  int i = 0;
  try {
   Class.forName("com.mysql.jdbc.Driver");
   connection = DriverManager.getConnection(url, user, password);
   sql="delete from borrowlist where id=?";
   preparedStatement = connection.prepareStatement(sql);
   preparedStatement.setInt(1, returnBookId);
   i = preparedStatement.executeUpdate();
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }

*4:

归还图书功能需要先查询已经借阅的图书列表,因为登录时已经将username放在session中，所以从Session中取出，根据用户名查询

 

 使用SpringMVC框架实现的小型Web系统——myLibrary图书借阅系统

 

 QureyBookList.java的代码为

@Controller

public class QueryBookList {
@RequestMapping("queryBookList")
public static ModelAndView queryBookList(HttpSession httpSession) {
    Student student = (Student) httpSession.getAttribute("student");
    String name = student.getName();
    ArrayList<Book> arrayList = new ArrayList<Book>();
    arrayList=QBookList.getBookByStudent(name);
    ModelAndView modelAndView = new ModelAndView("borrowList");
    if(arrayList!=null) {
        modelAndView.addObject("arrayList", arrayList);//放到request域中
        return modelAndView;
    }else {
        return modelAndView;
    }
}
}

数据库连接QBookList.java

public class QBookList {
    private static final String url="jdbc:mysql://localhost:3306/test";
    private static final String user="root";
    private static final String password="1234";
    public static ArrayList<Book> getBookByStudent(String name) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Book> arrayList = new ArrayList<Book>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(url, user, password);
            String sql = null;
            sql="select id,bookname from borrowlist where name=?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            resultSet = preparedStatement.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            while(resultSet.next()) {
                Book book=new Book();
                book.setId(resultSet.getInt(1));
                book.setName(resultSet.getString(2));
                arrayList.add(book);
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if(connection!=null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(preparedStatement!=null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return arrayList;
    }

前端展示：

<h2 style="text-align: center"><font color="blue"><%Book book=(Book)request.getAttribute("book");%>
图书编号:<%=book.getId()%><br>
图书名字:<%=book.getName()%><br>
可借阅:<%=book.getCount()%>本<br></font></h2><br/>
<h2 style="text-align: center"><font color="blue">
<form action="borrowBook" method="post">
<input type="text" name="bookname" value=<%=book.getName()%>>
<input type="submit" value="borrow">
</form>
</font></h2><br/>

 

 使用SpringMVC框架实现的小型Web系统——myLibrary图书借阅系统

 

 使用SpringMVC框架实现的小型Web系统——myLibrary图书借阅系统

 

 

*5输入上图中的归还图书名字和编号，跳转到Controller中的ReturnBook.java，然后调用Dao层的UpdateBook.Java和UpdateList.java来更新数据库中的余书库存以及借阅列表

使用SpringMVC框架实现的小型Web系统——myLibrary图书借阅系统

 

代码如下ReturnBook.java

@Controller
public class ReturnBook {
    @RequestMapping(method=RequestMethod.POST,value="returnBook")
    public ModelAndView returnBook(@RequestParam("returnBookName") String returnBookName,@RequestParam("returnBookId") int returnBookId) {
        UpdateBook.addBookByName(returnBookName);
        int i = UpdateList.reduceBook(returnBookId);
        if(i>0){
            ModelAndView modelAndView = new ModelAndView("returnDone");
            return modelAndView;
        }else {
            ModelAndView modelAndView = new ModelAndView("returnFail");
            return modelAndView;
        }
        
    }
}

UpdateBook.Java和UpdateList.java

public static int addBookByName(String bookname) {
            Book book=new Book();
            Connection connection = null;
            PreparedStatement prepareStatement = null;
            ResultSet resultSet = null;
            String sql = null;
            int count=0;
            try {
                Class.forName("com.mysql.jdbc.Driver");
                connection = DriverManager.getConnection(url, user, password);
                sql="select count from book where bookname=?";
                prepareStatement = connection.prepareStatement(sql);
                prepareStatement.setString(1, bookname);
                resultSet = prepareStatement.executeQuery();
                if(resultSet.next()) {
                    int has=resultSet.getInt(1);
                    if(has>0) {
                        try {
                            Class.forName("com.mysql.jdbc.Driver");
                            connection = DriverManager.getConnection(url, user, password);
                            sql="update book set count=count+1 where bookname=?";
                            prepareStatement = connection.prepareStatement(sql);
                            prepareStatement.setString(1, bookname);
                            count= prepareStatement.executeUpdate();
                            //System.out.print(count);
                        } catch (SQLException e) {
                            e.printStackTrace();
                        } catch (ClassNotFoundException e) {
                            e.printStackTrace();
                        }
                    }else {
                        
                        //System.out.print(count);
                        return count;
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            
            
public static int addBook(String name,String bookname) {
        String sql=null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int i = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(url, user, password);
            sql="insert into borrowlist (name,bookname) values (?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            preparedStatement.setString(2, bookname);
            i = preparedStatement.executeUpdate();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

 

上一篇：python mysql curros.executemany 批量添加


下一篇：mysql python pymysql模块 增删改查 插入数据 介绍 commit() execute() executemany() 函数