关于Servlet在web中的配置就不详细介绍了,这里就是介绍Servlet结合JDBC以及JSP操作数据库,关于环境的搭建前面几篇博客都详细的介绍了
一:通过JDBC项数据库添加数据
创建图书信息tb_books表,结构如图所示
创建名称为Book 的类
package com.book.web3;
public class Book {
private int id;
private String name;
private double price;
private int bookCount;
private String author;
public int getId(){
return id;
}
public void setId(int id){
this.id = id;
}
public String getName(){
return name;
}
public void setName(String name){
this.name = name;
}
public double getPrice(){
return price;
}
public void setPrice(double price){
this.price = price;
}
public int getBookCount(){
return bookCount;
}
public void setBookCount(int bookCount){
this.bookCount = bookCount;
}
public String getAuthor(){
return author;
}
public void setAuthor(String author){
this.author = author;
}
}
创建jdbc.jsp页面,用于添加数据的表单页面,该表单提交到jdbcresult.jsp页面处理
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<body>
<form action="jdbcresult.jsp" method="post">
<table align="center" border="1" width="50%" cellpadding="6">
<tr>
<th colspan="2" align="center" >添加图书信息</th><%--th加粗, cellpadding和android padding含义一样--%>
</tr>
<tr>
<td align="center" >图书编号:</td>
<td align="left" ><input type="text" name="id"></td>
</tr>
<tr>
<td align="center" >图书名称:</td>
<td align="left" ><input type="text" name="name"></td>
</tr>
<tr>
<td align="center" >价 格:</td>
<td align="left" ><input type="text" name="price"></td>
</tr>
<tr>
<td align="center" >数 量:</td>
<td align="left" ><input type="text" name="bookCount"></td>
</tr>
<tr>
<td align="center" >作 者:</td>
<td align="left" ><input type="text" name="author"></td>
</tr>
<tr>
<th colspan="2" align="center" ><input type="submit" name="submit" value="添加"></th>
</tr>
</table>
</form>
</body>
</html>
创建jdbcresult.jsp页面,该页面通过JDBC提交的图书信息添加到数据库
<%@page import="java.sql.PreparedStatement"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="java.sql.Connection" %>
<%@page import="java.sql.DriverManager" %>
<% request.setCharacterEncoding("UTF-8"); %>
<jsp:useBean id="book" class="com.book.web3.Book"></jsp:useBean>
<jsp:setProperty property="*" name="book"/>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<body>
<%
try{
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/student";
String userName = "roots"; // 用户名
String userPwd = "123456"; // 密码
Connection conn = DriverManager.getConnection(url, userName, userPwd);
String sql = "insert into tb_books(id,name,price,bookCount, author)values(?,?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, book.getId());
ps.setString(2, book.getName());
ps.setDouble(3, book.getPrice());
ps.setInt(4, book.getBookCount());
ps.setString(5, book.getAuthor());
int row = ps.executeUpdate();
if(row > 0){
out.print("成功添加了 " + row + "条数据! ");
}
ps.close();
conn.close();
}catch(Exception e){
out.print("图书信息添加失败 " + e.toString());
}
%>
</body>
</html>
在jdbcresult.jsp页面中,首先通过<jsp:useBean>实例化JavaBean的对象Book,并通过<jsp:setProperty>对Book对象中的属性赋值,<jsp:setProperty>标签中property属性的值可以设置为' * ',作用是将与表单中同名的属性值赋值给JavaBean对象中的同名属性,使用这种方式就不必对JavaBean中的属性一一进行赋值,减少代码量
结果展示:
二:查询数据
使用JDBC查询数据操作后,需要通过ResultSet对象来装载查询结果集。
创建名称为 JdbcFindServlet的Servlet对象,用于查询图书信息
package com.book.web3;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class JdbcFindServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* 构造函数
*/
public JdbcFindServlet()
{
super();
}
/**
* 初始化
*/
public void init() throws ServletException
{}
/**
* doGet()方法
*/
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
doPost(request, response);
}
/**
* doPost()方法
*/
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/student";
String userName = "roots"; // 用户名
String userPwd = "123456"; // 密码
Connection conn = DriverManager.getConnection(url, userName, userPwd);
Statement stmt = conn.createStatement();
String sql = "select * from tb_books";
ResultSet rs = stmt.executeQuery(sql);
List<Book> list = new ArrayList<Book>();
while (rs.next()) {
Book book = new Book();
book.setId(rs.getInt("id"));
book.setName(rs.getString("name"));
book.setPrice(rs.getDouble("price"));
book.setBookCount(rs.getInt("bookCount"));
book.setAuthor(rs.getString("author"));
list.add(book);
}
request.setAttribute("list", list);
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
// TODO: handle exception
}
request.getRequestDispatcher("jdbcfindservlet.jsp").forward(request, response);
}
/**
* 销毁
*/
public void destroy()
{
super.destroy();
}
}
JdbcFindServlet对象在web.xml中的配置如下
<servlet>
<servlet-name>JdbcFindServlet</servlet-name>
<servlet-class>com.book.web3.JdbcFindServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>JdbcFindServlet</servlet-name>
<url-pattern>/findServlet.do</url-pattern> <!-- 根据不同的url来调用不同的servlet来进行处理。 -->
</servlet-mapping>
创建jdbcfindservlet.jsp页面,用于显示查询信息,下面用的脚本处理的相关点击事件
<%@page import="java.util.ArrayList"%>
<%@page import="java.util.List"%>
<%@page import="com.book.web3.Book"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<script type="text/javascript">
function check(form) {
with (form) {
if (bookCount.value == "") {
alert("请输入更新数量!");
return false;
}
if (isNaN(bookCount.value)) {
alert("格式错误!");
return false;
}
return true;
}
}
</script>
</head>
<body>
<table id="table1" align="center" border="1" width="50%" cellpadding="6">
<tr>
<th colspan="6" align="center" >查询到的图书信息</th><%--th加粗, cellpadding和android padding含义一样--%>
</tr>
<tr>
<th align="center" >ID</th>
<th align="center" >图书名称</th>
<th align="center" >价格</th>
<th align="center" >数量</th>
<th align="center" >作者</th>
<th align="center" >修改数量</th>
</tr>
<%
//获取图书信息集合
List<Book> list = (List<Book>)request.getAttribute("list");
if(list == null || list.size() < 1){
out.print("没有数据 ");
}else{
for(Book book:list){
%>
<tr align="center">
<td> <%= book.getId() %></td>
<td> <%= book.getName() %></td>
<td> <%= book.getPrice() %></td>
<td> <%= book.getBookCount() %></td>
<td> <%= book.getAuthor() %></td>
<td>
<form action="updateServlet.do" method="post" οnsubmit="return check(this);">
<input type="hidden" name="id" value="<%=book.getId()%>">
<input type="text" name="bookCount" size="3">
<input type="submit" value="修改数量">
</form>
</td>
</tr>
<%
}
}
%>
</table>
</body>
</html>
创建程序入口界面,index.jsp页面
<a href="findServlet.do">查看所有图书</a> //和web.xml配置下<url-pattern>一致
运行结果:
三:修改数据
在上面的查询界面中已经预留了修改的页面,修改的表单中含有两个属性id与bookCount,因此指定id作为修改条件
创建修改图书信息名称为JdbcUpdateServlet请求的Servlet对象
package com.book.web3;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class JdbcUpdateServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* 构造函数
*/
public JdbcUpdateServlet()
{
super();
}
/**
* 初始化
*/
public void init() throws ServletException
{}
/**
* doGet()方法
*/
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
doPost(request, response);
}
/**
* doPost()方法
*/
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
int id = Integer.valueOf(request.getParameter("id"));
int bookCount = Integer.valueOf(request.getParameter("bookCount"));
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/student";
String userName = "roots"; // 用户名
String userPwd = "123456"; // 密码
Connection conn = DriverManager.getConnection(url, userName, userPwd);
String sql = "update tb_books set bookCount=? where id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, bookCount);
ps.setInt(2, id);
ps.executeUpdate();
ps.close();
conn.close();
} catch (Exception e) {
// TODO: handle exception
}
}
/**
* 销毁
*/
public void destroy()
{
super.destroy();
}
}
web.xml配置
<servlet>
<servlet-name>JdbcUpdateServlet</servlet-name>
<servlet-class>com.book.web3.JdbcUpdateServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>JdbcUpdateServlet</servlet-name>
<url-pattern>/updateServlet.do</url-pattern> <!--根据不同的url来调用不同的servlet来进行处理。 -->
</servlet-mapping>
操作结果:
修改前
修改后
后面的删除就不介绍了,差不多一样的