Servlet实例总结-基于Java、SQLserver、Tomcat

题目及要求:

使用Java Bean+Servlet+Jsp实现下列目标

任务一：在数据库中建立表格T_BOOK(BID, BNAME, BAUTHER, BPRICE, BAMOUNT, BPUBDATE)，并向表中添加一些记录，T_BOOK数据表结构如下：

表1-2 T_BOOK数据表

序号	列名	含义	类型	备注
1	BID	书号	VARCHAR(20)	“B0001”
2	BNAME	书名	VARCHAR(50)
3	BAUTHER	作者	VARCHAR(50)
4	BTYPE	类型	VARCHAR(50)	计算机、文学
5	BPRICE	价格	INT
6	BAMOUNT	数量	INT
7	BPUBDATE	出版日期	DATE	2000-1-1

任务二：编写程序，在JSP页面中提供表单，用户可填写图书数据，向T_BOOK表中添加一条记录。
任务三：编写程序，在JSP页面提供按书名模糊查询功能，用户输入书名部分或全部内容，查询的结果显示在JSP页面上。
任务四：编写程序，在JSP页面中提供按价格区间查询功能，用户可填写两个价格，查询出两个价格之间的图书结果显示在JSP页面上。
任务五：编写程序，在JSP页面中提供按书号更新书籍数量功能，用户输入书号、数量，可更新数据表中的记录。

实现:

任务1：使用图形化建立数据库及建表，如下图：

在这里插入图片描述

任务2：增加记录，使用post传值，设置简易窗口即可，注意SQL语句的正确。

代码如下：

DbBean.java

package org.etspace.abc.jdbc;
import java.sql.*;
public class DbBean {
	private Statement stmt=null;
	private Connection conn=null;
	ResultSet rs=null;
	//构造函数
	public DbBean(){}
	//打开连接
	public void openConnection() throws Exception
	{
		try
		{
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
			String url="jdbc:sqlserver://localhost:1433;databaseName=book";
			String user="sa";
			String password="wsy0910";
			conn=DriverManager.getConnection(url, user, password);
		}
		catch(ClassNotFoundException e)
		{
			System.err.println("openConn:"+e.getMessage());
		}
		catch(SQLException e)
		{
			System.err.println("openConn:"+e.getMessage());
		}
	}
	//执行查询类的SQL语句
	public ResultSet executeQuery(String sql)
	{
		rs=null;
		try
		{
			stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			rs=stmt.executeQuery(sql);
		}
		catch(SQLException e)
		{
			System.err.println("executeQuery:"+e.getMessage());
		}
		return rs;
	}
	//执行更新类的SQL语句
	public int executeUpdate(String sql)
	{
		int n = 0;
		try{
			stmt=conn.createStatement();
			n=stmt.executeUpdate(sql);
		}catch(Exception e)
		{
			System.out.print(e.toString());
		} 
		return n;
	}	
	//关闭连接
	public void closeConnection()
	{
		try
		{
			if (rs!=null)
				rs.close();
		}
		catch(SQLException e)
		{
			System.err.println("closeRs:"+e.getMessage());
		}
		try
		{
			if (stmt!=null)
				stmt.close();
		}
		catch(SQLException e)
		{
			System.err.println("closeStmt:"+e.getMessage());
		}
		try
		{
			if (conn!=null)
				conn.close();
		}
		catch(SQLException e)
		{
			System.err.println("closeConn:"+e.getMessage());
		}
	}
}

tszjServlet.java

package org.etspace.abc.servlet;
import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.etspace.abc.jdbc.DbBean;
@SuppressWarnings("serial")
public class tszjServlet extends HttpServlet {
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doPost(request,response);
	}
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		request.setCharacterEncoding("gb2312");
		response.setCharacterEncoding("gb2312");
		response.setContentType("text/html;charset=gb2312");
		PrintWriter out = response.getWriter();
		try {
			String BID =  request.getParameter("BID");
			String BNAME =  request.getParameter("BNAME");
			String BAUTHER =  request.getParameter("BAUTHER");
			String BTYPE =  request.getParameter("BTYPE");
			String BPRICE =  request.getParameter("BPRICE");
			String BAMOUNT =  request.getParameter("BAMOUNT");
			String BPUBDATE =  request.getParameter("BPUBDATE");	
			String sql = "insert into T_BOOK(BID,BNAME,BAUTHER,BTYPE,BPRICE,BAMOUNT,BPUBDATE)";
			sql=sql+" values('"+BID+"','"+BNAME+"','"+BAUTHER+"','"+BTYPE+"','"+BPRICE+"',"+BAMOUNT+",'"+BPUBDATE+"')";
			//out.print(sql);
			DbBean myDbBean=new DbBean();
			myDbBean.openConnection();
	        int n = myDbBean.executeUpdate(sql);
	        if (n>0){
	       		out.print("图书记录增加成功！");
	        }
	       	else {
	       		out.print("图书记录增加失败！");
	       	} 
			myDbBean.closeConnection();
		} 
		catch (Exception e) {
			out.print(e.toString());			
		}
	}
}

tszj.jsp

<%@ page contentType="text/html;charset=gb2312" language="java" %>
<%@ page import="java.sql.*"%>
<jsp:useBean id="myDbBean" scope="page" class="org.etspace.abc.jdbc.DbBean"></jsp:useBean>
<%request.setCharacterEncoding("gb2312"); %>
<script language="JavaScript">
function check(theForm)
{
  if (theForm.BID.value.length != 5)
  {
    alert("书号必须为5位！");
    theForm.BID.focus();
    return (false);
  }  
  if (theForm.BNAME.value == "")
  {
    alert("请输入书名！");
    theForm.BNAME.focus();
    return (false);
  }
  if (theForm.BPUBDATE.value == "")
  {
    alert("请输入出版日期！");
    theForm.BPUBDATE.focus();
    return (false);
  }
  if (theForm.BAMOUNT.value == "")
  {
    alert("请输入数量！");
    theForm.BAMOUNT.focus();
    return (false);
  }
  if (theForm.BPRICE.value == "")
  {
    alert("请输入价格！");
    theForm.BPRICE.focus();
    return (false);
  }
  if (theForm.BAUTHER.value == "")
  {
    alert("请输入作者！");
    theForm.BAUTHER.focus();
    return (false);
  }
  if (theForm.BTYPE.value == "")
  {
    alert("请输入类型！");
    theForm.BTYPE.focus();
    return (false);
  }  
  return (true);
}
</script>
<html>
<head><title>图书增加</title></head>
<body>
<div align="center">
  <P>图书增加</P>
<form id="form1" name="form1" method="post" action="tszjServlet" onSubmit="return check(this)">
	<table border="1">
	<tr><td>书号</td><td><input name="BID" type="text" id="BID" /></td></tr>
	<tr><td>书名</td><td><input name="BNAME" type="text" id="BNAME" /></td></tr>
	<tr><td>作者</td><td><input name="BAUTHER" type="text" id="BAUTHER" /></td></tr>
	<tr><td>类型</td><td>
	<input type="radio" name="BTYPE" value="计算机" checked="checked" />计算机 
	<input type="radio" name="BTYPE" value="文学" />文学 
	</td></tr>
<%
		String sql = "select * from BID order by T_BOOK";
		myDbBean.openConnection();
		ResultSet rs=myDbBean.executeQuery(sql);
%>
	</td></tr>
	<tr><td>价格</td><td><input name="BPRICE" type="text" id="BPRICE" /></td></tr>
	<tr><td>数量</td><td><input name="BAMOUNT" type="text" id="BAMOUNT" /></td></tr>
	<tr><td>出版日期</td><td><input name="BPUBDATE" type="text" id="BPUBDATE" /></td></tr>
	</table>
	<br>
  	<input name="submit" type="submit"  value="确定" />
  	<input name="reset" type="reset" value="重置" />
</form>
</div>
</body>
</html>

截图如下：

在这里插入图片描述

任务3：模糊查询，设置一个实体类Book，注意SQL语句的准确即可，此题我用直接输出，可用List列表进行输出。

代码如下：

Book.java

package org.etspace.abc.bean;

public class Book {
	
	public String BID;
	public String BNAME;
	public String BAUTHER;
	public String BTYPE;
	public int BPRICE;
	public int BAMOUNT;
	public String BPUBDATE;
	
	public String getBID(){
		return BID;
	}
	public void setBID(String BID){
		this.BID = BID;
	}
	
	public String getBNAME(){
		return BNAME;
	}
	public void setBNAME(String BNAME){
		this.BNAME = BNAME;
	}
	
	public String getBAUTHER(){
		return BAUTHER;
	}
	public void setBAUTHER(String BAUTHER){
		this.BAUTHER = BAUTHER;
	}
	
	public String getBTYPE(){
		return BTYPE;
	}
	public void setBTYPE(String BTYPE){
		this.BTYPE = BTYPE;
	}
	
	public int getBPRICE(){
		return BPRICE;
	}
	public void setBPRICE(int BPRICE){
		this.BPRICE = BPRICE;
	}
	
	public int getBAMOUNT(){
		return BAMOUNT;
	}
	public void setBAMOUNT(int BAMOUNT){
		this.BAMOUNT = BAMOUNT;
	}
	
	public String getBPUBDATE(){
		return BPUBDATE;
	}
	public void setBPUBDATE(String BPUBDATE){
		this.BPUBDATE = BPUBDATE;
	}

}

mhcxServlet.java

package org.etspace.abc.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.etspace.abc.jdbc.DbBean;
@WebServlet("/mhcxServlet")
@SuppressWarnings("serial")
public class mhcxServlet extends HttpServlet {
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doPost(request,response);
	}
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		response.setCharacterEncoding("UTF-8");
		response.setContentType("text/html;charset=UTF-8");
		PrintWriter out = response.getWriter();
		
			String input = request.getParameter("input");
			String sql = "select * from T_BOOK where BNAME like '%"+input+"%'";
			//like实现模糊查询
			DbBean myDbBean=new DbBean();
			try {
				myDbBean.openConnection();
			} catch (Exception e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
	        ResultSet rs = myDbBean.executeQuery(sql);
	        try {    
	        while(rs.next()){
	        	out.println(
	        			"<font color=\'blue\' size=\'2\' face=\'Arial\' >"
	        			+ "书号："+rs.getString("BID")+
	        			"  书名："+rs.getString("BNAME")+
	        			"  作者："+rs.getString("BAUTHER")+
	        			"  类型："+rs.getString("BTYPE")+
	        			"  价格："+rs.getString("BPRICE")+
	        			"  数量："+rs.getString("BAMOUNT")+
	        			"  出版日期："+rs.getString("BPUBDATE")
	        			);//直接打印，并修改字体大小颜色，使适应界面
	        	out.println("<br>");
	        }
	        rs.close();
			myDbBean.closeConnection();
		} 
		catch (Exception e) {
			out.print(e.toString());			
		}
	}
}

mhcx.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>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>模糊查询</title>
</head>
<body>
	<div align="center">
	<p>模糊查询</p>
	<form action="mhcxServlet" method="post">
	<input type="text" name="input"/><br>
	<input type="submit" value="确定"/>
	<input type="reset" value="重置"/>
	</form>
	</div>
</body>
</html>

截图如下：

在这里插入图片描述

任务4：区间查找，设置了一个success的jsp进行跳转输出列表，请注意SQL代码的正确与初始化列表的先后顺序。

代码如下：

jgcxServlet,java

package org.etspace.abc.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.etspace.abc.bean.Book;
import org.etspace.abc.jdbc.DbBean;
@WebServlet("/jgcxServlet")
@SuppressWarnings("serial")
public class jgcxServlet extends HttpServlet {
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doPost(request,response);
	}
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		response.setCharacterEncoding("UTF-8");
		response.setContentType("text/html;charset=UTF-8");
		PrintWriter out = response.getWriter();
		
			int Low = Integer.parseInt(request.getParameter("LowPrice"));
			int High = Integer.parseInt(request.getParameter("HighPrice"));
			
			String sql = "select * from T_BOOK where BPRICE between "+Low+" and "+High;
			//between实现区间查询
			DbBean myDbBean=new DbBean();
			try {
				myDbBean.openConnection();
			} catch (Exception e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
	        ResultSet rs = myDbBean.executeQuery(sql);
	        List<Book> BookList = new ArrayList<Book>();
	        
	        try {    
	        while(rs.next()){
	        	Book book = new Book();
	        	book.setBID(rs.getString("BID"));
	        	book.setBNAME(rs.getString("BNAME"));
	        	book.setBAUTHER(rs.getString("BAUTHER"));
	        	book.setBTYPE(rs.getString("BTYPE"));
	        	book.setBPRICE(rs.getInt("BPRICE"));
	        	book.setBAMOUNT(rs.getInt("BAMOUNT"));
	        	book.setBPUBDATE(rs.getString("BPUBDATE"));
	        	BookList.add(book);
	        }
	        request.setAttribute("BookList", BookList);
	        rs.close();
			myDbBean.closeConnection();
			request.getRequestDispatcher("/jgcxSuccess.jsp").forward(request, response);
		} 
		catch (Exception e) {
			out.print(e.toString());			
		}
	}
}

jgcx.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>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>价格区间查询</title>
</head>
<body>
	<div align="center">
	<p>价格区间查询</p>
	<form action="jgcxServlet" method="post">
	请输入您要查询图书的价格:
	<input type="text" name="LowPrice"/>-
	<input type="text" name="HighPrice"/><br>
	<input type="submit" value="确定"/>
	<input type="reset" value="重置"/>
	</form>
	</div>
</body>
</html>

jgcxSuccess.jsp

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ page import = "java.io.IOException"%>
<%@ page import = "java.io.PrintWriter"%>
<%@ page import = "java.util.ArrayList" %>
<%@ page import = "org.etspace.abc.bean.Book" %>
<%@page import="java.sql.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>查询结果</title>
</head>
<body>
	
	<% ArrayList BookList = (ArrayList)request.getAttribute("BookList");
	 
	 %>
	
	<div align="center">
	<h2>查询结果</h2>
	<table align="center" border = '1'>
        	<tr>
            <th>书号</th>
            <th>书名</th>
            <th>作者</th>
            <th>类型</th>
            <th>价格</th>
            <th>数量</th>
            <th>出版日期</th>
        	</tr>
        	<%
		    	 for(int i = 0; i < BookList.size(); i++){
			    	 Book book = (Book)BookList.get(i);%>
		    	 	<tr>
		    	 	<td><%=book.getBID()%></td>
		    	 	<td><%=book.getBNAME()%></td>
		    	 	<td><%=book.getBAUTHER()%></td>
		    	 	<td><%=book.getBTYPE()%></td>
		    	 	<td><%=book.getBPRICE()%></td>
		    	 	<td><%=book.getBAMOUNT()%></td>
		    	 	<td><%=book.getBPUBDATE()%></td>
		    	 	</tr>
		    	 	<%
		    	 }
	    	 
	    	 %>
	   	
	</table>
	</div>
</body>
</html>

截图如下：

在这里插入图片描述

任务5：修改数据，即确认书号修改数量，保证SQL语句正确，和任务1无异。

代码如下：

tsxgServlet.java

package org.etspace.abc.servlet;
import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.etspace.abc.jdbc.DbBean;
@WebServlet("/tsxgServlet")
@SuppressWarnings("serial")
public class tsxgServlet extends HttpServlet {
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doPost(request,response);
	}
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		request.setCharacterEncoding("gb2312");
		response.setCharacterEncoding("gb2312");
		response.setContentType("text/html;charset=gb2312");
		PrintWriter out = response.getWriter();
		try {
			String BID =  request.getParameter("BID");
			String BAMOUNT =  request.getParameter("BAMOUNT");
			String sql = "update T_BOOK set BAMOUNT = "+BAMOUNT+" where BID = '"+BID+"'";
			//out.print(sql);
			DbBean myDbBean=new DbBean();
			myDbBean.openConnection();
	        int n = myDbBean.executeUpdate(sql);
	        if (n>0){
	       		out.print("图书记录修改成功！");
	        }
	       	else {
	       		out.print("图书记录修改失败！");
	       	} 
			myDbBean.closeConnection();
		} 
		catch (Exception e) {
			out.print(e.toString());			
		}
	}
}

tsxg.jsp

<%@ page contentType="text/html;charset=gb2312" language="java" %>
<%@ page import="java.sql.*"%>
<jsp:useBean id="myDbBean" scope="page" class="org.etspace.abc.jdbc.DbBean"></jsp:useBean>
<%request.setCharacterEncoding("gb2312"); %>
<script language="JavaScript">
function check(theForm)
{
  if (theForm.BID.value.length != 5)
  {
    alert("书号必须为5位！");
    theForm.BID.focus();
    return (false);
  }  
  if (theForm.BNAME.value == "")
  {
    alert("请输入书名！");
    theForm.BNAME.focus();
    return (false);
  }
  if (theForm.BPUBDATE.value == "")
  {
    alert("请输入出版日期！");
    theForm.BPUBDATE.focus();
    return (false);
  }
  if (theForm.BAMOUNT.value == "")
  {
    alert("请输入数量！");
    theForm.BAMOUNT.focus();
    return (false);
  }
  if (theForm.BPRICE.value == "")
  {
    alert("请输入价格！");
    theForm.BPRICE.focus();
    return (false);
  }
  if (theForm.BAUTHER.value == "")
  {
    alert("请输入作者！");
    theForm.BAUTHER.focus();
    return (false);
  }
  if (theForm.BTYPE.value == "")
  {
    alert("请输入类型！");
    theForm.BTYPE.focus();
    return (false);
  }  
  return (true);
}
</script>
<html>
<head><title>图书数量修改</title></head>
<body>
<div align="center">
  <P>图书数量修改</P>
<form id="form1" name="form1" method="post" action="tsxgServlet" onSubmit="return check(this)">
	<table border="1">
	<tr><td>书号</td><td><input name="BID" type="text" id="BID" /></td></tr>
<%
		String sql = "select * from BID order by T_BOOK";
		myDbBean.openConnection();
		ResultSet rs=myDbBean.executeQuery(sql);
%>
	</td></tr>
	<tr><td>数量</td><td><input name="BAMOUNT" type="text" id="BAMOUNT" /></td></tr>
	</table>
	<br>
  	<input name="submit" type="submit"  value="确定" />
  	<input name="reset" type="reset" value="重置" />
</form>
</div>
</body>
</html>

截图如下：

在这里插入图片描述

希望能帮到你学习Servlet技术，以及MVC模型的相关知识，感谢观看！

编程

Servlet

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议，转载请注明出处！

git clone加速问题解决方案上一篇

三角判断下一篇