jsp+servlet+jdbc实现对数据库的增删改查

2024-03-05 18:26:03 299

一、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

}


}

源代码下载链接

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。

jsp+servlet+jdbc实现对数据库的增删改查

热门推荐

随机推荐