时间：2023-10-06 16:06:01 | 来源：网站运营
时间：2023-10-06 16:06:01 来源：网站运营
Java web实验报告：JDBC访问数据库：注：本实验代码要根据实际做适当修改才能运行
在线咨询
create database bookstoreGOUse bookstoreCREATE TABLE books ( bookid character(5) PRIMARY KEY, --书号 title varchar(80), --书名author character varying(20), --作者 publisher character varying (40), --出版社 price real --价格);GoINSERT INTO books VALUES ('204','Head First Servlets & JSP', 'Bryan Basham', '中国电力出版社',98.00);INSERT INTO books VALUES ('201', 'Servlets 与JSP 核心教程', 'Hall Marty','清华大学出版社',45);INSERT INTO books VALUES ('202', 'Tomcat与Java Web 开发技术祥解', '孙卫琴', '机械工业出版社',45);INSERT INTO books VALUES ('203', 'JSP 应用开发技术', '柳永坡','人民邮电出版社',52);INSERT INTO books VALUES ('205', 'J2EE 1.4 编程指南', 'Spielman Sue','电子工业出版社',68);注意：需要将数据库的JDBC驱动程序安装到应用程序的WEB-INF/lib目录中<%@ page contentType="text/html; charset=gb2312" %><%@ page import="java.sql.*"%><html><head> <title>Database Access Test</title></head><body><%try {Class.forName("org.postgresql.Driver");String dburl = "jdbc:postgresql://localhost:5432/bookstore";Connection conn = DriverManager.getConnection(dburl, "bookstore", "bookstore"); Statement stmt = conn.createStatement();String sql = "SELECT * FROM books" ;ResultSet rs = stmt.executeQuery(sql);out.println("<table border=1>");out.println("<tr><td>书号</td><td>书名</td><td>作者</td><td>价格</td></tr>");while (rs.next()){out.println("<tr><td>"+ rs.getString(1)+"</td><td>"+ rs.getString(2)+"</td><td>"+ rs.getString(3)+"</td><td>"+ rs.getString(5)+"</td></tr>");}out.println("</table>");rs.close();stmt.close();conn.close();}catch (Exception e) { out.println(e.getMessage());}%></body></html>通过数据源访问数据库<?xml version="1.0" encoding="UTF-8"?><Context reloadable="true"><Resourceauth="Container"driverClassName="net.sourceforge.jtds.jdbc.Driver"maxActive="10" maxIdle="2" maxWait="5000"name="jdbc/bookDS"username="sa"password="123"type="javax.sql.DataSource"url="jdbc:jtds:sqlserver://localhost:1433/;DatabaseName=bookstore"validationQuery="select 1"/></Context>【步骤2】使用下面的JSP页面displayBooks.jsp访问数据库<%@ page contentType="text/html; charset=gb2312" %><%@ page import="java.sql.*, javax.sql.*,javax.naming.*"%><html><head> <title>DataSource Test</title></head><body><%try {Context context = new InitialContext();DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/bookDS");Connection conn = ds.getConnection();Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("SELECT * FROM books");out.println("<table border=1>");out.println("<tr><td>书号</td><td>书名</td><td>作者</td><td>价格</td></tr>");while (rs.next()){out.println("<tr><td>"+ rs.getString(1)+"</td><td>"+ rs.getString(2)+"</td><td>"+ rs.getString(3)+"</td><td>"+ rs.getString(5)+"</td></tr>");}out.println("</table>");rs.close();stmt.close();conn.close();}catch (Exception e) { out.println(e.getMessage());}%></body></html>综合应用。本实验采用MVC设计模式，通过数据源和DAO对象访问数据库。其中JavaBeans实现模型，访问数据库，Servlet实现控制器，JSP页面实现视图。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 author, String title, 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; public BookDAO(){ try{ if(context == null){ context = new InitialContext(); } dataSource = (DataSource)context.lookup("java:comp/env/jdbc/bookDS"); }catch(NamingException e2){ } } // 根据书号查询图书信息 public BookBean searchBook(String bookid){ Connection conn = null; PreparedStatement pstmt = null; ResultSet rst = null; BookBean book = new BookBean(); try{ conn = dataSource.getConnection(); pstmt = conn.prepareStatement("SELECT * FROM books 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{ conn.close(); }catch(SQLException se){ } } } // 插入一本图书记录 public boolean insertBook(BookBean book){ Connection conn = null; PreparedStatement pstmt = null; try{ conn = dataSource.getConnection(); pstmt = conn.prepareStatement( "INSERT INTO books VALUES(?,?,?,?,?)"); pstmt.setString(1,book.getBookid()); pstmt.setString(2,book.getTitle()); pstmt.setString(3,book.getAuthor()); pstmt.setString(4,book.getPublisher()); pstmt.setFloat(3,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="bookquery.do" 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 = "bookinsert.do" 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="request"/><html><body> 书号：<jsp:getProperty name="book" property="bookid"/> 书名：<jsp:getProperty name="book" property="title"/> 作者：<jsp:getProperty name="book" property="author"/> 出版社：<jsp:getProperty name="book" property="publisher"/> 价格：<jsp:getProperty name="book" property="price"/></body></html>【步骤6】错误页面errorPage.jsp代码如下：<%@ page contentType="text/html;charset=gb2312"%><html><body> 对不起，您查的图书不存在！</body></html>【步骤7】下面的Servlet实现从请求参数获得书号，然后从数据库中查找该书，最后根据查询结果将请求转发到显示页面(display.jsp)或错误页面(errorPage.jsp)。package com.control;import java.io.*;import java.sql.*;import javax.servlet.*;import javax.servlet.http.*;import com.beans.BookBean;import com.beans.BookDAO;public class BookQueryServlet extends HttpServlet{ public void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException{ 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页面。package com.control;import java.io.*;import java.sql.*;import javax.servlet.*;import javax.servlet.http.*;import com.beans.BookBean;import com.beans.BookDAO;public class BookInsertServlet extends HttpServlet{ public void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException{ 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); }}【步骤9】在DD文件中部署Servlet，将下列代码添加到DD文件的适当位置 <servlet> <servlet-name>bookQuery</servlet-name> <servlet-class>com.control.BookQueryServlet</servlet-class> </servlet><servlet> <servlet-name>bookInsert</servlet-name> <servlet-class>com.control.BookInsertServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>bookQuery</servlet-name> <url-pattern>/bookquery.do</url-pattern> </servlet-mapping> <servlet-mapping> <servlet-name>bookInsert</servlet-name> <url-pattern>/bookinsert.do</url-pattern> </servlet-mapping>运行bookQuery.jsp，进行查询操作：关键词：访问,数据,报告,实验
