jsp+servlet+jdbc实现对数据库的增删改查
一、JSP和Servlet的简单介绍
1、Servlet和JSP简介:
Java开发Web应用程序时用到的技术主要有两种,即Servlet和JSP,Servlet是在服务器端执行的Java程序,一个被称为Servlet容器的程序(其实就是服务器)负责执行Java程序,而JSP(JavaServerPage)则是一个页面,由JSP容器负责执行.
2、Servlet和JSP的区别:
Servlet以Java程序为主,输出HTML代码时需要使用out.println函数,也就是说Java中内嵌HTML;而JSP则以HTML页面为主,需要写Java代码时则在页面中直接插入Java代码,即HTML中内嵌Java.
3、MVC模型
MVC模型就是将数据、逻辑处理、用户界面分离的一种方法
1)、M(Model,模型):用于数据处理、逻辑处理
2)、V(View,视图):用于显示用户界面
3)、C(Controller,控制器):根据客户端的请求控制逻辑走向和画面
而在Java中,MVC这三个部分则分别对应于JavaBeans、JSP和Servlet
1)、M=JavaBeans:用于传递数据,拥有与数据相关的逻辑处理
2)、V=JSP:从Model接收数据并生成HTML
3)、C=Servlet:接收HTTP请求并控制Model和View
4、jdbc连接,可参考文章:点击打开链接
二、代码演示,实现了book的添加删除和修改功能
1、环境的配置
myeclipse+tomcat+MySQL
2、Book中bean类
packageexample.bean.book; publicclassBook{ //编号 privateintid; //图书名称 privateStringname; //价格 privatedoubleprice; //数量 privateintbookCount; //作者 privateStringauthor; publicintgetId(){ returnid; } publicvoidsetId(intid){ this.id=id; } publicStringgetName(){ returnname; } publicvoidsetName(Stringname){ this.name=name; } publicdoublegetPrice(){ returnprice; } publicvoidsetPrice(doubleprice){ this.price=price; } publicintgetBookCount(){ returnbookCount; } publicvoidsetBookCount(intbookCount){ this.bookCount=bookCount; } publicStringgetAuthor(){ returnauthor; } publicvoidsetAuthor(Stringauthor){ this.author=author; } }
3、Servlet类
1)、FindServlet.java
packageexample.servlet.book; importjava.io.IOException; importjava.sql.Connection; importjava.sql.DriverManager; importjava.sql.ResultSet; importjava.sql.Statement; importjava.util.ArrayList; importjava.util.List; importjavax.servlet.ServletException; importjavax.servlet.http.HttpServlet; importjavax.servlet.http.HttpServletRequest; importjavax.servlet.http.HttpServletResponse; importexample.bean.book.Book; /** *ServletimplementationclassFindServlet */ publicclassFindServletextendsHttpServlet{ privatestaticfinallongserialVersionUID=1L; /** *@seeHttpServlet#doGet(HttpServletRequestrequest,HttpServletResponse *response) */ protectedvoiddoGet(HttpServletRequestrequest, HttpServletResponseresponse)throwsServletException,IOException{ try{ //加载数据库驱动,注册到驱动管理器 Class.forName("com.mysql.jdbc.Driver"); //数据库连接字符串 Stringurl="jdbc:mysql://localhost:3306/db_book?useUnicode=true&characterEncoding=utf-8"; //数据库用户名 Stringusername="root"; //数据库密码 Stringpassword=""; //创建Connection连接 Connectionconn=DriverManager.getConnection(url,username, password); //添加图书信息的SQL语句 Stringsql="select*fromtb_books"; //获取Statement Statementstatement=conn.createStatement(); ResultSetresultSet=statement.executeQuery(sql); List<Book>list=newArrayList<Book>(); while(resultSet.next()){ Bookbook=newBook(); book.setId(resultSet.getInt("id")); book.setName(resultSet.getString("name")); book.setPrice(resultSet.getDouble("price")); book.setBookCount(resultSet.getInt("bookCount")); book.setAuthor(resultSet.getString("author")); list.add(book); } request.setAttribute("list",list); resultSet.close(); statement.close(); conn.close(); }catch(Exceptione){ e.printStackTrace(); } request.getRequestDispatcher("book_list.jsp") .forward(request,response); } /** *@seeHttpServlet#doPost(HttpServletRequestrequest,HttpServletResponse *response) */ protectedvoiddoPost(HttpServletRequestrequest, HttpServletResponseresponse)throwsServletException,IOException{ //TODOAuto-generatedmethodstub doGet(request,response); } }
2)、UpdateServlet.java类
packageexample.servlet.book; importjava.io.IOException; importjava.sql.Connection; importjava.sql.DriverManager; importjava.sql.PreparedStatement; importjavax.servlet.ServletException; importjavax.servlet.http.HttpServlet; importjavax.servlet.http.HttpServletRequest; importjavax.servlet.http.HttpServletResponse; /** *ServletimplementationclassUpdateServlet */ publicclassUpdateServletextendsHttpServlet{ privatestaticfinallongserialVersionUID=1L; /** *@seeHttpServlet#doGet(HttpServletRequestrequest,HttpServletResponse *response) */ protectedvoiddoGet(HttpServletRequestrequest, HttpServletResponseresponse)throwsServletException,IOException{ intid=Integer.valueOf(request.getParameter("id")); intbookCount=Integer.valueOf(request.getParameter("bookCount")); try{ //加载数据库驱动,注册到驱动管理器 Class.forName("com.mysql.jdbc.Driver"); //数据库连接字符串 Stringurl="jdbc:mysql://localhost:3306/db_book"; //数据库用户名 Stringusername="root"; //数据库密码 Stringpassword=""; //创建Connection连接 Connectionconn=DriverManager.getConnection(url,username, password); //更新SQL语句 Stringsql="updatetb_bookssetbookcount=?whereid=?"; //获取PreparedStatement PreparedStatementps=conn.prepareStatement(sql); //对SQL语句中的第一个参数赋值 ps.setInt(1,bookCount); //对SQL语句中的第二个参数赋值 ps.setInt(2,id); //执行更新操作 ps.executeUpdate(); //关闭PreparedStatement ps.close(); //关闭Connection conn.close(); }catch(Exceptione){ e.printStackTrace(); } //重定向到FindServlet response.sendRedirect("FindServlet"); } /** *@seeHttpServlet#doPost(HttpServletRequestrequest,HttpServletResponse *response) */ protectedvoiddoPost(HttpServletRequestrequest, HttpServletResponseresponse)throwsServletException,IOException{ //TODOAuto-generatedmethodstub doGet(request,response); } }
3)、DeleteServlet类
packageexample.servlet.book; importjava.io.IOException; importjava.sql.Connection; importjavax.servlet.ServletException; importjavax.servlet.http.HttpServlet; importjavax.servlet.http.HttpServletRequest; importjavax.servlet.http.HttpServletResponse; importexample.dao.book.BookJdbcDao; importexample.dao.book.ConnectionFactory; /** *ServletimplementationclassDeleteServlet */ publicclassDeleteServletextendsHttpServlet{ privatestaticfinallongserialVersionUID=1L; /** *@seeHttpServlet#doGet(HttpServletRequestrequest,HttpServletResponse *response) */ protectedvoiddoGet(HttpServletRequestrequest, HttpServletResponseresponse)throwsServletException,IOException{ intid=Integer.valueOf(request.getParameter("id")); try{ ////加载数据库驱动,注册到驱动管理器 //Class.forName("com.mysql.jdbc.Driver"); ////数据库连接字符串 //Stringurl="jdbc:mysql://localhost:3306/db_book"; ////数据库用户名 //Stringusername="root"; ////数据库密码 //Stringpassword=""; ////创建Connection连接 //Connectionconn=DriverManager.getConnection(url,username, //password); ////删除图书信息的SQL语句 //Stringsql="deletefromtb_bookswhereid=?"; ////获取PreparedStatement //PreparedStatementps=conn.prepareStatement(sql); ////对SQL语句中的第一个占位符赋值 //ps.setInt(1,id); ////执行更新操作 //ps.executeUpdate(); ////关闭PreparedStatement //ps.close(); ////关闭Connection //conn.close(); BookJdbcDaobookDao=newBookJdbcDao(); Connectionconn=ConnectionFactory.getInstance().getConnection(); bookDao.delete(conn,id); }catch(Exceptione){ e.printStackTrace(); } //重定向到FindServlet response.sendRedirect("FindServlet"); } /** *@seeHttpServlet#doPost(HttpServletRequestrequest,HttpServletResponse *response) */ protectedvoiddoPost(HttpServletRequestrequest, HttpServletResponseresponse)throwsServletException,IOException{ doGet(request,response); } }
4、servlet访问url映射配置:
由于客户端是通过URL地址访问web服务器中的资源,所以Servlet程序若想被外界访问,必须把servlet程序映射到一个URL地址上,这个工作在web.xml文件中使用<servlet>元素和<servlet-mapping>元素完成,<servlet>元素用于注册Servlet,它包含有两个主要的子元素:<servlet-name>和<servlet-class>,分别用于设置Servlet的注册名称和Servlet的完整类名。
一个<servlet-mapping>元素用于映射一个已注册的Servlet的一个对外访问路径,它包含有两个子元素:<servlet-name>和<url-pattern>,分别用于指定Servlet的注册名称和Servlet的对外访问路径.另外:同一个Servlet可以被映射到多个URL上,即多个<servlet-mapping>元素的<servlet-name>子元素的设置值可以是同一个Servlet的注册名.
eg:上例中的web.xml
<?xmlversion="1.0"encoding="UTF-8"?> <web-appxmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns="http://java.sun.com/xml/ns/javaee"xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"xsi:schemaLocation="http://java.sun.com/xml/ns/javaeehttp://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"id="WebApp_ID"version="2.5"> <display-name>JdbcConnection</display-name> <welcome-file-list> <welcome-file>index.html</welcome-file> <welcome-file>index.htm</welcome-file> <welcome-file>index.jsp</welcome-file> <welcome-file>default.html</welcome-file> <welcome-file>default.htm</welcome-file> <welcome-file>default.jsp</welcome-file> </welcome-file-list> <servlet> <description></description> <display-name>FindServlet</display-name> <servlet-name>FindServlet</servlet-name> <servlet-class>example.servlet.book.FindServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>FindServlet</servlet-name> <url-pattern>/FindServlet</url-pattern> </servlet-mapping> <servlet> <description></description> <display-name>UpdateServlet</display-name> <servlet-name>UpdateServlet</servlet-name> <servlet-class>example.servlet.book.UpdateServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>UpdateServlet</servlet-name> <url-pattern>/UpdateServlet</url-pattern> </servlet-mapping> <servlet> <description></description> <display-name>DeleteServlet</display-name> <servlet-name>DeleteServlet</servlet-name> <servlet-class>example.servlet.book.DeleteServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>DeleteServlet</servlet-name> <url-pattern>/DeleteServlet</url-pattern> </servlet-mapping> </web-app>
5、jsp页面
1)、index.jsp web页面主界面
<%@pageimport="java.sql.SQLException"%> <%@pageimport="java.sql.DriverManager"%> <%@pageimport="java.sql.Connection"%> <%@pagelanguage="java"contentType="text/html;charset=utf-8" pageEncoding="utf-8"%> <!DOCTYPEhtmlPUBLIC"-//W3C//DTDHTML4.01Transitional//EN""http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <metahttp-equiv="Content-Type"content="text/html;charset=utf-8"> <title>添加图书信息</title> <scripttype="text/javascript"> functioncheck(form){ with(form){ if(name.value==""){ alert("图书名称不能为空"); returnfalse; } if(price.value==""){ alert("图书价格不能为空"); returnfalse; } if(author.value==""){ alert("作者不能为空"); returnfalse; } } } </script> </head> <body> <formaction="addbook.jsp"method="post"onsubmit="check(this)"> <tablealign="center"width="450"> <tr> <tdalign="center"colspan="2"> <h2>添加图书信息</h2> <hr> </td> </tr> <tr> <tdalign="right">图书名称:</td> <td><inputtype="text"name="name"></td> </tr> <tr> <tdalign="right">价格:</td> <td><inputtype="text"name="price"></td> </tr> <tr> <tdalign="right">数量:</td> <td><inputtype="text"name="bookCount"/></td> </tr> <tr> <tdalign="right">作者:</td> <td><inputtype="text"name="author"/></td> </tr> <tr> <tdalign="center"colspan="2"><inputtype="submit"value="添加"> </td> </tr> </table> </form> <h2align="center"> <ahref="FindServlet">查询图书信息</a> </h2> </body> </html>
2)、addbook.jsp添加书籍成功或者失败后的显示页面
<%@pagelanguage="java"contentType="text/html;charset=utf-8" pageEncoding="utf-8"%> <!DOCTYPEhtmlPUBLIC"-//W3C//DTDHTML4.01Transitional//EN""http://www.w3.org/TR/html4/loose.dtd"> <%@pageimport="java.sql.Connection"%> <%@pageimport="java.sql.DriverManager"%> <%@pageimport="java.sql.PreparedStatement"%> <html> <head> <metahttp-equiv="Content-Type"content="text/html;charset=utf-8"> <title>添加结果</title> </head> <body> <% request.setCharacterEncoding("utf-8"); %> <jsp:useBeanid="book"class="example.bean.book.Book"></jsp:useBean> <jsp:setPropertyproperty="*"name="book"/> <% try{ //加载数据库驱动,注册到驱动管理器 Class.forName("com.mysql.jdbc.Driver"); //数据库连接字符串 Stringurl="jdbc:mysql://localhost:3306/db_book?useUnicode=true&characterEncoding=utf-8"; //数据库用户名 Stringusername="root"; //数据库密码 Stringpassword=""; //创建Connection连接 Connectionconn=DriverManager.getConnection(url,username, password); //添加图书信息的SQL语句 Stringsql="insertintotb_books(name,price,bookCount,author)values(?,?,?,?)"; //获取PreparedStatement PreparedStatementps=conn.prepareStatement(sql); //对SQL语句中的第1个参数赋值 ps.setString(1,book.getName()); //对SQL语句中的第2个参数赋值 ps.setDouble(2,book.getPrice()); //对SQL语句中的第3个参数赋值 ps.setInt(3,book.getBookCount()); //对SQL语句中的第4个参数赋值 ps.setString(4,book.getAuthor()); //执行更新操作,返回所影响的行数 introw=ps.executeUpdate(); //判断是否更新成功 if(row>0){ //更新成输出信息 out.print("成功添加了"+row+"条数据!"); } //关闭PreparedStatement,释放资源 ps.close(); //关闭Connection,释放资源 conn.close(); }catch(Exceptione){ out.print("图书信息添加失败!"); e.printStackTrace(); } %> <br> <ahref="index.jsp">返回</a> </body> </html>
3)、book_list.jsp查找所有书籍信息的显示页面,以表格方式显示
<!--<%@pageimport="sun.awt.SunHints.Value"%>--> <%@pagelanguage="java"contentType="text/html;charset=utf-8" pageEncoding="utf-8"%> <!DOCTYPEhtmlPUBLIC"-//W3C//DTDHTML4.01Transitional//EN""http://www.w3.org/TR/html4/loose.dtd"> <%@pageimport="java.util.List"%> <%@pageimport="example.bean.book.Book"%> <html> <head> <metahttp-equiv="Content-Type"content="text/html;charset=utf-8"> <title>所有图书信息</title> <styletype="text/css"> td{ font-size:12px; } h2{ margin:0px } </style> <scripttype="text/javascript"> functioncheck(form){ with(form){ if(bookCount.value==""){ alert("请输入更新数量!"); returnfalse; } if(isNaN(bookCount.value)){ alert("格式错误!"); returnfalse; } returntrue; } } </script> </head> <body> <tablealign="center"width="450"border="1"height="180" bordercolor="white"bgcolor="black"cellpadding="1"cellspacing="1"> <trbgcolor="white"> <tdalign="center"colspan="7"> <h2>所有图书信息</h2> </td> </tr> <tralign="center"bgcolor="#e1ffc1"> <td><b>ID</b></td> <td><b>图书名称</b></td> <td><b>价格</b></td> <td><b>数量</b></td> <td><b>作者</b></td> <td><b>修改</b></td> <td><b>删除</b></td> </tr> <% //获取图书信息集合 List<Book>list=(List<Book>)request.getAttribute("list"); //判断集合是否有效 if(list==null||list.size()<1){ out.print("没有数据!"); }else{ //遍历图书集合中的数据 for(Bookbook:list){ %> <tralign="center"bgcolor="white"> <td><%=book.getId()%></td> <td><%=book.getName()%></td> <td><%=book.getPrice()%></td> <td><%=book.getBookCount()%></td> <td><%=book.getAuthor()%></td> <td> <formstyle="align:center;background-color:gray"action="UpdateServlet"method="post" onsubmit="returncheck(this);"> <inputtype="hidden"name="id"value="<%=book.getId()%>"><input type="text"name="bookCount"size="3"> <inputtype="submit"value="修改数量"> </form> </td> <td> <ahref="DeleteServlet?id=<%=book.getId()%>">删除</a> </td> </tr> <% } } %> </table> <h2align="center"> <ahref="index.jsp">返回添加图书信息页面</a> </h2> </body> </html>
6、jdbc简单的封装
1)、ConnectionFactory.java工厂类
packageexample.dao.book; importjava.sql.Connection; importjava.sql.DriverManager; importjava.sql.SQLException; publicclassConnectionFactory{ privateStringdriverClassName="com.mysql.jdbc.Driver"; privateStringurl="jdbc:mysql://localhost:3306/db_book?useUnicode=true&characterEncoding=utf-8"; privateStringuserName="root"; privateStringpassword=""; privatestaticConnectionFactoryconnectionFactory=null; privateConnectionFactory(){ try{ Class.forName(driverClassName); }catch(ClassNotFoundExceptione){ e.printStackTrace(); } } publicConnectiongetConnection()throwsSQLException { returnDriverManager.getConnection(url,userName,password); } publicstaticConnectionFactorygetInstance() { if(null==connectionFactory){ connectionFactory=newConnectionFactory(); } returnconnectionFactory; } }
2)、BookJdbcDao.java数据库操作封装类
packageexample.dao.book; importjava.sql.Connection; importjava.sql.PreparedStatement; importjava.sql.ResultSet; importjava.sql.SQLException; importexample.bean.book.Book; publicclassBookJdbcDao{ privatePreparedStatementptmt=null; privateResultSetrs=null; publicBookJdbcDao(){ } publicvoidfindAll(Connectionconn)throwsSQLException { //todo } publicvoiddelete(Connectionconn,intid)throwsSQLException { Stringsql="deletefromtb_bookswhereid=?"; try{ ptmt=conn.prepareStatement(sql); //对SQL语句中的第一个占位符赋值 ptmt.setInt(1,id); //执行更新操作 ptmt.executeUpdate(); }finally{ if(null!=ptmt){ ptmt.close(); } if(null!=conn){ conn.close(); } } } publicvoidupdate(Connectionconn,intid,intbookcount)throwsSQLException { //todo } }
源代码下载链接
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持毛票票。