综合运用javaBean、servlet对数据库的查询、插入等操作

Dear 丶 2024-02-17 21:30 40阅读 0赞

（数据库使用的是SQL server 2008）综合运用javaBean、servlet对数据库的查询、插入等操作，将代码贴处理，以后需要时直接用就行了。

综合应用。本实验采用MVC设计模式，通过数据源和DAO对象访问数据库。其中JavaBeans实现模型，访问数据库，Servlet实现控制器，JSP页面实现视图，分页功能实现。

· 模型包括2个JavaBean：BookBean用于存放图书信息，BookDAO用于访问数据库。

· 控制器包括2个Servlet：BookQueryServlet根据请求参数查询图书信息、BookInsertServlet用来向数据库中插入数据。

· 视图包括4个JSP页面：bookQuery.jsp显示查询页面、bookInsert.jsp显示插入页面、display.jsp显示查询结果页面和errorPage.jsp显示错误页面。

【步骤1】存放图书信息的JavaBeans代码BookBean.java，它也是一个传输对象。

package com.beans;
    
    import java.io.*;
    
    public class BookBean implements Serializable{
        private String bookid = null;
        private String title = null;
        private String author = null;
        private String publisher = null;
        private float price = 0.0F;
    
        public BookBean(){}
        public BookBean(String bookId, String title, String author, String publisher, float price) {
            this.bookid = bookId;
            this.title = title;
            this.author = author;
            this.publisher = publisher;
            this.price = price;
        }
        public String getBookid() { return this.bookid; }
        public String getTitle() { return title; }
        public String getAuthor() { return this.author; }
        public float getPrice() { return price; }
        public String getPublisher () { return publisher; }
        
        public void setBookid(String bookid){ this.bookid=bookid;	}
        public void setTitle(String title){this.title=title;	}
        public void setAuthor(String author){ this. author = author;	}
        public void setPrice(float price){this.price=price;	}
        public void setPublisher (String publisher){ this.publisher = publisher;}
    }

【步骤2】下面的BookDAO是一个简单的JavaBeans，它实现数据库的访问。

package com.beans;
    
    import java.sql.*;
    
    import javax.sql.*;
    import javax.naming.*;
    
    import java.util.ArrayList;
    
    import com.beans.BookBean;
    
    public class BookDAO{
    //   private static InitialContext context= null;
    //   private DataSource dataSource = null;
       
        Connection conn =null;
        PreparedStatement pstmt = null;
        ResultSet rst = null;
       public BookDAO(){
       	try{
       		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();   //jar包
       	    conn = DriverManager.getConnection("jdbc:sqlserver://127.0.0.1:1433;DatabaseName=bookStore","sa","123456"); 
         }catch(Exception e){
         }
       }
       // 根据书号查询图书信息
       public BookBean searchBook(String bookid){  	 
          BookBean  book = new BookBean();
          try{
       	    pstmt = conn.prepareStatement("SELECT * FROM book WHERE bookid=?"); 
       	    pstmt.setString(1,bookid);
       	    rst = pstmt.executeQuery();
       	    if(rst.next()){
       	      book.setBookid(rst.getString("bookid"));
       	      book.setTitle(rst.getString("title"));
              book.setAuthor(rst.getString("author"));
              book.setPublisher(rst.getString("publisher"));
              book.setPrice(rst.getFloat("price"));
       	      return book;
            }else{
              return  null;
            }
       	 }catch(SQLException se){
       	  	return null;
       	 }finally{
       	  	 try{
       	  		 if(conn!=null)
       	  	 	 conn.close();
       	  	 }catch(SQLException se){
       	  	 }
       	 }
       }   
       // 插入一本图书记录
       public boolean insertBook(BookBean book){  	  
       	   try{
       	  	 pstmt = conn.prepareStatement(
       	  	 	 "INSERT INTO book VALUES(?,?,?,?,?)"); 
       	     pstmt.setString(1,book.getBookid());
       	     pstmt.setString(2,book.getTitle());
       	     pstmt.setString(3,book.getAuthor());
       	     pstmt.setString(4,book.getPublisher());
       	     pstmt.setFloat(5,book.getPrice());
       	     pstmt.executeUpdate();
       	     pstmt.close();
       	     return true;
       	   }catch(SQLException se){
       	  	 return false;
       	   }finally{
       	  	  try{
       	  	 	conn.close();
       	  	  }catch(SQLException se){ }
       	   }
       }     
    }

【步骤3】下面的JSP页面bookQuery.jsp实现根据书号查询图书信息

<%@ page contentType="text/html; charset=gb2312" %>
    <html><head> <title>Book Query</title>
    </head>
    <body>
    请输入一个书号:<br>
    <form action="BookQueryServlet" method = "post">
    <input type="text" name="bookid"><br>
    <input type="submit" value="提交">
    </form>
    </body>
    </html>

【步骤4】下面的JSP页面bookInsert.jsp实现向数据库中插入数据

<%@ page contentType="text/html; charset=gb2312" %>
    <html><head> <title>Book Insert</title>
    </head>
    <body>
    <h3>请输入图书信息：</h3>
    <% if(request.getAttribute("result")!=null)
         out.print(request.getAttribute("result"));
    %>
    <form action = "BookInsertServlet" method = "post">
     <table>
      <tr><td>书号</td> <td><input type="text" name="bookid" ></td></tr>
      <tr><td>书名</td><td><input type="text" name="title"></td></tr>      
      <tr><td>作者</td><td><input type="text" name="author" ></td></tr>
      <tr><td>出版社</td><td><input type="text" name="publisher" ></td></tr>
      <tr><td>单价</td><td><input type="text" name="price" ></td></tr>
      <tr><td><input type="submit" value="确定" ></td>
          <td><input type="reset" value="重置" ></td>
      </tr>
    </table>
    </form>
    </body></html>

【步骤5】显示查询结果的JSP页面display.jsp：

<%@ page contentType="text/html;charset=gb2312"%>
    <jsp:useBean id="book" class="com.beans.BookBean" scope="session"/>
    <html><body>
       书号：<jsp:getProperty name="book" property="bookid"/><br><br>
       书名：<jsp:getProperty name="book" property="title"/><br><br>
       作者：<jsp:getProperty name="book" property="author"/><br><br>
       出版社：<jsp:getProperty name="book" property="publisher"/><br><br>
       价格：<jsp:getProperty name="book" property="price"/><br><br>
    </body></html>

【步骤6】错误页面errorPage.jsp代码如下：

<%@ page contentType="text/html;charset=gb2312"%>
    <html><body>
        对不起，您查的图书不存在！
    </body></html>

【步骤7】下面的Servlet实现从请求参数获得书号，然后从数据库中查找该书，最后根据查询结果将请求转发到显示页面(display.jsp)或错误页面(errorPage.jsp)。

BookQueryServlet.java

import java.io.*;
    import java.sql.*;
    import javax.servlet.*;
    import javax.servlet.http.*;
    import com.beans.BookBean;
    import com.beans.BookDAO;
    
    
    	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    		// TODO Auto-generated method stub
    		String bookid = request.getParameter("bookid");
    	    BookDAO bookdao = new BookDAO();
    	    BookBean book = bookdao.searchBook(bookid);
    	    
    	    if(book!=null){
    	      request.getSession().setAttribute("book", book);
    	      RequestDispatcher view = request.getRequestDispatcher("/display.jsp");
    	      view.forward(request, response);
    	    }else{
    	      RequestDispatcher view = request.getRequestDispatcher("/errorPage.jsp");
    	      view.forward(request, response);
    	    }
    
    	}

【步骤8】下面的Servlet实现向数据库插入数据，并将控制请求的转发到bookInsert.jsp页面。

BookInsertServlet.java

import java.io.*;
    import java.sql.*;
    import javax.servlet.*;
    import javax.servlet.http.*;
    import com.beans.BookBean;
    import com.beans.BookDAO;	
    
    
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            // TODO Auto-generated method stub
            request.setCharacterEncoding("gb2312");
            String message = null;  
            BookBean book = new BookBean(
                request.getParameter("bookid"),request.getParameter("title"),
                request.getParameter("author"),request.getParameter("publisher"),
                Float.parseFloat(request.getParameter("price"))
                ); 
            BookDAO bookdao = new BookDAO();
            boolean success = bookdao.insertBook(book);
            if(success){
               message = "成功插入一条记录！";
             }else{ 
               message = "插入记录错误！";
            }   
           request.setAttribute("result",message);
           RequestDispatcher view = request.getRequestDispatcher("/bookInsert.jsp");
           view.forward(request, response);
    
        }

插入：

![Center][]

插入后：

![Center 1][]

查询：

![Center 2][]

查询结果：

![Center 3][]

数据库中的数据：

![Center 4][]

[Center]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/93769d4b767b4e5baadade7afc879377.png
[Center 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/d1c9bba0496a45a29ee5befd15f5a3db.png
[Center 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/7a8e675b0e384e1c85b498b3a98f3689.png
[Center 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/78036dc90c3941a5ab17968a373f421c.png
[Center 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/625c6234faa0413396fd17db89a379c4.png