2001用户登录实现使用servlet,mysql,ajax,gson实现曾删改查

最新推荐文章于 2021-02-13 15:14:40 发布

Hoetears

最新推荐文章于 2021-02-13 15:14:40 发布

阅读量706

点赞数

分类专栏：数据库文章标签： mysql json javascript ajax

本文链接：https://blog.csdn.net/weixin_43917151/article/details/104966072

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

本文介绍了如何利用Servlet、MySQL数据库、Ajax和Gson技术实现用户登录、注册、主页面及增删查改功能。详细讲述了各个部分的实现，包括Html页面、配置文件、Servlet处理、实体类的设计以及数据访问层的实现。重点指出在查询后关闭流、Ajax参数类型、页面间参数传递和web.xml配置等方面需要注意的问题。

摘要由CSDN通过智能技术生成

项目目录
在这里插入图片描述
一、编写Html
1.注册页面

<!DOCTYPE html>
<html>
	<head>
		<meta charset="utf-8">
		<title>注册页面</title>
		<style>	
			#top{
				width: 800px;
				height: 400px;
				margin: auto;
				border: #0000FF solid 1px;
				text-align: center;
			}
			div>div{
				
			}
			label{
				display: inline-block;
				width: 80px;
				text-align: right;				
			}
			input{
				
				height: 20px;
				margin-top: 4px;
			}
			button{
				margin-top: 4px;
				margin-right: 4px;
			}
			#code{
				width: 100px;
			}
			#code_span{
				display: inline-block;
				width: 70px;
			}			
				
		</style>
		<script src="js/jquery-3.4.1.js"></script>
	</head>
	<body>
		<div id="top">
			<h3>用户注册</h3>
			<div id="">
				<label>用户名：</label><input type="username" 

name="username" id="name" />
			</div>
			<div id="utip"></div>
			<div id="">
				<label>密码：</label><input type="password" 

name="password" id="pass" />
			</div>
			<div id="ptip"></div>
			<div id="">
				<label>确认密码：</label><input type="password" 

name="repassword" id="repass" />
			</div>
			<div id="rtip"></div>
			<div>
				<label>验证码：</label><input type="text" name="codeDemo" 

id="code"/><span id="code_span"></span>
			</div>
			<div id="ctip"></div>
			<button type="button">注册</button><button type="button">登录

</button>			
		</div>
	
		
	</body>
</html>	
<script>
	var flag = false;
	$().ready(function(){
		//生成验证码
		rand();
		//点击验证码更新
		$("#code_span").click(function(){
			rand();
		})
		//用户名检测
		$("#name").on("blur",ckName);
		//密码检测
		$("#pass").on("blur",ckPass);
		//检测确认密码
		$("#repass").on("blur",ckRepass);
		//检测验证码
		$("#code").on("blur",ckCode);
		
		//点击事件
			//注册，ajax查看用户名是否有相同			
				$("#name").on("keyup blur",function(){
					if(ckName()){
						ckreName();
					}
				})
					
								
			
			//注册成功，跳转到登录页面
				
		 		$("button:contains('注册')").click(function(){
		 			if(flag&&ckName()&&ckPass()&&ckRepass()&&ckCode()){						
						registerUser();
					}
			}) 
			
			//点击进入登录界面
			$("button:contains('登录')").click(function(){
				location.href = "login.html";
			})
			console.log($("#name").val());
		
	})
	//注册用户
 	function registerUser(){
		$.ajax({
			type:"post",
			url:"UserServlet",
			data:"state=registerUser&username="+$("#name").val()+"&password="+$("#pass").val(),
			dataType:"text",
			success:function(data){				
				if("添加成功"===data){
					location.href = "login.html";
				}else{
					alert("注册失败");
				}
			}
		})
	} 
	
	
	
	//查询是否有该用户名
	function ckreName(){
		
		$.ajax({
			type:"post",
			dataType:"text",
			url:"UserServlet",
			data:"state=selectOne&userName="+$("#name").val(),
			success:function(data){
				console.log(data);
				if(data==="用户名存在"){
					$("#name").css("border","1px solid red");
					$("#utip").text("该用户名存在").show().css("color","red");					
					flag = false;
				}else{
					$("#name").removeAttr("style");
					$("#utip").hide();
					flag = true;
				}
			}
		})
		
	}
	
	//用户名检测方法
	function ckName(){
		let str = $("#name").val();
		let reg = /^[a-zA-Z]\w{5,29}$/;		
		if($.trim(str)===""){
			$("#name").css("border","1px solid red");
			$("#utip").text("用户名不能为空").show().css("color","red");
		}else if(!reg.test(str)){
			$("#name").css("border","1px solid red");
			$("#utip").text("输入的用户名格式不正确：6-20位 字母，数字，下划线，首位字母").show().css("color","red");
		}else{
			$("#name").removeAttr("style");
			$("#utip").hide();
			return true;
		}
		
	}
	//密码检测方法
	function ckPass(){
		let str = $("#pass").val();
		let reg = /^[A-Z]\w{5,19}$/;		
		if($.trim(str)==""){
			$("#pass").css("border","1px red solid");
			$("#ptip").text("密码不能为空").show().css("color","red");
		}else if(!reg.test(str)){
			$("#pass").css("border","1px red solid" );
			$("#ptip").text("输入的用户名格式不正确：6-20位 字母，数字，下划线，首位字母大写").show().css("color","red");			
		}else{			
			$("#pass").removeAttr("style");
			$("#ptip").hide();
			return true;
		}
	}
	//再检测密码的方法
	function ckRepass(){
		let str1 = $("#pass").val();
		let str2 = $("#repass").val();
		if($.trim(str2)===""){
		$("#repass").css("border","1px red solid");
			$("#rtip").text("密码不能为空").show().css("color","red");
		}else if($.trim(str1)!=$.trim(str2)){
			$("#repass").css("border","1px red solid" );
			$("#rtip").text("密码输入与上次不同").show().css("color","red");	

		
		}else{
			$("#repass").removeAttr("style");
			$("#rtip").hide();
			return true;
		}
	}
	//检测验证码
	function ckCode(){
		console.log(4);
		let str1 = $("#code").val();
		let str2 = $("#code_span").text();
		if($.trim(str1)===""){
			$("#code").css("border","1px solid red");
			$("#ctip").text("验证码不能为空").show().css("color","red");
		}else if($.trim(str1)!=str2){
			$("#code").css("border","1px solid red");
			$("#ctip").text("验证码不正确").show().css("color","red");
		}else{
			$("#code").removeAttr("style");
			$("#ctip").hide();
			return true;
		}
	}
	
	
	//生成随机验证码
	function rand(){
		let ran ="";
		for(let i = 0; i < 4; i++){
			let num = Math.random()*74+48;
			if((num>57&&num<65)||(num>90&&num<97)){
				i--;
			}else{				
				ran+=String.fromCharCode(num);
			}
		}
		$("#code_span").text(ran);
	}
	//
	
</script>

2.登录页面

<!DOCTYPE html>
<html>
	<head>
		<meta charset="utf-8">
		<title>登录页面</title>
		<script src="js/jquery-3.4.1.js"></script>
		<style type="text/css">
			body>div{
				width: 600px;
				height: 18.75rem;
				border: 1px #0077FF solid;
				margin: auto;
				text-align: center;
			}
			label{
				display: inline-block;
				width: 80px;
				text-align: right;
			}
			div>div{
				margin-top: 4px;
			}
			#code{
				width: 120px;
			}
			#code_span{
				display: inline-block;
				width: 50px;
			}
			button{
				margin-right: 0.25rem;
			}
		</style>
	</head>
	<body>		
		<div id="top">
			<h3>登录界面</h3>
			<div>
				<label>用户名：</label><input type="username" id="name" />
			</div>
			<div id="utip"></div>
			<div>
				<label>密码：</label><input type="password" id="pass" />
			</div>
			<div id="ptip"></div>
			<div>
				<label>验证码：</label><input type="text" id="code" /><span id="code_span"></span>
			</div>
			<div id="ctip"></div>
			<button>登录</button><button>注册</button>
		</div>		
	</body>
</html>
<script>
	$().ready(function(){		
		//生成验证码
		rand();
		//点击验证码
		$("#code_span").click(function(){
			rand();
		})
		//验证用户名
		$("#name").on("blur",ckName);
		//验证密码
		$("#pass").on("blur",ckPass);
		//验证验证码
		$("#code").on("blur",ckCode); 		
		//ajax验证跳转主页，传用户id
		
		$("button:contains('登录')").click(function(){
			if(ckCode()&&ckPass()&&ckName()){
				userLogin();
			}
			
		})
		//跳转注册
		$("button:contains('注册')").click(function(){
			location.href="register.html";
		})
	})
	
	//ajax验证跳转主页，传用户id
	function userLogin(){
		
		$.ajax({
			type:"post",
			url:"UserServlet",
			data:"state=userLogin&userName="+$("#name").val()+"&password="+$("#pass").val(),
			dataType:"json",
			success:function(data){	
				console.log(data);
				if(data!=null){
					//注意此处的传值，若是一个User对象则直接data。userId
					location.href="main.html?userId="+data[0].userId+"&userName="+data[0].username;				
					alert("登录成功");
					$("#ctip").hide();
				}else{
					$("#ctip").text("用户名或密码错误").show().css("color","red");
				} 
			}
		})
	}
	
	
	
	//验证用户名方法
	function ckName(){
		let str = $("#name").val();
		if($.trim(str)===""){
			$("#name").css("border", "1px solid red");
			$("#utip").text("用户名不能为空").show().css("color","red");
		}else{
			$("#name").removeAttr("style");
			$("#utip").hide();
			return true;
		}
	}
	
	//验证密码方法
	function ckPass(){
		let str = $("#pass").val();
		if($.trim(str)===""){
			$("#pass").css("border", "1px solid red");
			$("#ptip").text("密码不能为空").show().css("color","red");
		}else{
			$("#pass").removeAttr("style");
			$("#ptip").hide();
			return true;
		}
	}	
	//验证验证码方法
	function ckCode(){
		let str = $("#code").val();
		let str2 = $("#code_span").text();		
		if($.trim(str)===""){
			$("#code").css("border", "1px solid red");
			$("#ctip").text("验证不能为空").show().css("color","red");
		}else if($.trim(str)!=str2){
			$("#code").css("border", "1px solid red");
			$("#ctip").text("验证错误").show().css("color","red");			
		}else{
			$("#code").removeAttr("style");
			$("#ctip").hide();
			return true;
		}
	}
	//生成随机验证码
	function rand(){		
		let str ="";
		for(let i = 0; i < 4; i++){
			let num = Math.random()*74+48;
			if((num>57&&num<65)||num>90&&num<97){
				i--;
			}else{				
				str+=String.fromCharCode(num);
			}
		}
		$("#code_span").text(str);
	}
</script>

3.主页面

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>图书馆</title>
<script src="js/jquery-3.4.1.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.bootcss.com/twitter-bootstrap/4.4.1/css/bootstrap-grid.css"/>
<style>
	div{
		max-width:80%;
		min-width:60%;
		margin:auto;
	}
	table{
		width:100%;
		text-align: center;
	}
	div>div:last-child{
		text-align: right;
	}
	h2{
		text-align: center;
	}
</style>

</head>
<body><div>
		<div id="ti">欢迎用户：<span id="user"></span></div>
		<div><h2>个人已借书籍</h2></div>
		<div id="">		
			<table border="1px" align="center"  class="tab1"  width="800px">
				<tr>
					<td><input type="checkbox" /></td>
					<td>id</td>
					<td>书名</td>
					<td>作者</td>
					<td>价格</td>
					<td>发行时间</td>
					<td>借出状态</td>
					<td>操作</td>				
				</tr>			
			</table>		
		</div>
		<div id="">
			<button id="allBook">显示所有书籍</button>
			<button id="myBook">个人书籍</button>
			<button>添加书籍</button>
			<button>删除选中书籍</button>
		</div>
			
	</div>
	
	

</body>
</html>
<script >
	
	$().ready(function(){
		//解析登录传入的值
		let url = location.href;		
		let parameters = url.substr(url.indexOf("?")+1).split("&");
		for(let values of parameters){
			let value = values.split("=");
			if(value[0]==="userId"){
				console.log(value[1]);
				$("#user").prop("userId",value[1]);
			}else if(value[0]==="userName"){
				console.log(value[1]);
				$("#user").text(value[1]);
			}
		}
		//根据id获取书籍
		$.ajax({
			type:"post",			
			dataType:"json",
			url:"BookServlet",
			data:"state=selectMyBook&userId="+$("#user").prop("userId"),
			success:function(data){				
				for(let book of data){					
					 $("table").append(`
							<tr>
								<td><input type="checkbox" /></td>
								<td>${book.bookId}</td>
								<td>${book.bookName}</td>
								<td>${book.bookAuthor}</td>
								<td>${book.bookPrice}</td>
								<td>${book.bookPublishDate}</td>
								<td>${book.userId==0?"未借":"已借"}</td>
								<td><button>归还</button></td>				
							</tr>	
							
							
							`); 
				}
			}
		})
		//添加点击事件，框了才归还
		//归还书籍，删除数据库id
		$("table").on("click","button:contains('归还')",bookBack);//注意button在表里面
		
		//显示全部，查询所有书籍
		$("#allBook").on("click",allBook);
		//显示个人书籍，添加点击事件，刷新
		$("#myBook").click(function(){
			location.reload();
		})
		//借阅书籍
		$("table").on("click","button:contains('借阅')",borrow);
		//添加复选框，让所有都选择
		$($(":checkbox")[0]).click(function(){
			$(":checkbox").prop("checked",$(this).prop("checked"));
		});	
		//添加书籍,跳转到添加书籍页面
		$("button:contains('添加书籍')").click(function(){
			location.href="addbook.html?userId="+$("#user").prop("userId")+"&username="+$("#user").text();
		})
		
		//删除选中书籍
		$("button:contains('删除选中书籍')").on("click", delBook);		
		
	})
	//删除书籍，获取所有选中的chexckbox,排除第一行
	function delBook(){
		//获取所有的选中的tr,不包含第一行和已经借出去的
		let trs = $(":checked").closest("tr").not($("button:contains('归还')").closest("tr")).not($($(":checkbox")[0]).closest("tr"));
		for(let tr of trs){			
			 let bookId = $(tr).children(":eq(1)").text();
			 $.ajax({
				 type:"post",
				 url:"BookServlet",
				 data:"state=delBook&bookId="+bookId,
				 dataType:"text",
				 success:function(data){
					 if(data=="删除成功"){
						 allBook();
			 		}else{
			 			alert(data);
			 		}
					 
				 }
			 });
		}
	}
	
	//借阅书籍
	function borrow(){
		//获取到所有选中的tr
		let bookId = $(this).closest("tr").children(":eq(1)").text();
		let userId = $("#user").prop("userId");	
		console.log(userId);
		console.log(bookId);
		if($(this).closest("tr").find(":checkbox").prop("checked")){
			$.ajax({
				type:"post",
				url:"BookServlet",
				data:"state=borrow&bookId="+bookId+"&userId="+userId,
				dataType:"text",
				success:function(data){
					if(data==="借阅成功"){						
						allBook();
					}else{
						alert(data);						
					}
				}
			})
		}
	}
	
	//所有书籍
	function allBook(){
		$.ajax({
			type:"post",
			url:"BookServlet",
			data:"state=allBook",
			dataType:"json",
			success:function(data){
				//删除个人的书籍
				$("table").children(":gt(0)").remove();
				$("h2").text("所有书籍");
				for(let book of data){
					 $("table").append(`
								<tr>
									<td><input type="checkbox" /></td>
									<td>${book.bookId}</td>
									<td>${book.bookName}</td>
									<td>${book.bookAuthor}</td>
									<td>${book.bookPrice}</td>
									<td>${book.bookPublishDate}</td>
									<td>${book.userId==0?"未借":"已借"}</td>
									<td><button>${book.userId==0?"借阅":"归还"}</button></td>				
								</tr>	
								
								
								`);
				}
			}
		})
	}
	
	//归还
	function bookBack(){
		let id = $(this).closest("tr").children(":eq(1)").text();			
		if($(this).closest("tr").find(":checkbox").prop("checked")){
			$.ajax({
				type:"post",
				url:"BookServlet",
				data:"state=bookBack&bookId="+id,
				success:function(data){
					if(data==1){
						alert("归还成功");
						if($("h2").text()=="所有书籍"){
							allBook();
						}else{
							location.reload();
						}
						
					}else{
						alert("归还失败");
					}
				}
			}) 
		}
	}
	
	
	
</script>

4.增加页面

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加书籍</title>
<style>
	body>div{
		width: 50%;
		margin: auto;
		border:1px solid blue;
	}
	h2{
		text-align: center;
	}
	table{
		margin:auto;	    
		text-align: center;
	    
	}
	input{
		width: 80%;
	}
	td{
	    border: 1px solid black;
	}
	button{
		margin-left:25%;
		text-align: center;
	}
</style>

<script src="js/jquery-3.4.1.js"></script>
<script type="text/javascript">
	$().ready(function(){
		//加载
		let url = location.href;
		let pars = url.substr(url.indexOf("?")+1).split("&");
		for(let par of pars){
			let value = par.split("=");
			if(value[0]=="userId"){				
				$("#user").prop("userId",value[1]);
			}else if(value[0]=="username"){
				$("#user").text(value[1]);
			}
		}
		
		/* $("#user").text(); */
		//添加		
		$("#sub").on("click",addBook);
		$("#main").click(function(){
			location.href="main.html?userId="+$("#user").prop("userId")+"&userName="+$("#user").text();
		})
		//验证书名、作者、价格、日期
		$("#sub").on("click",ckBookName);
		console.log($("[name='bookName']"));	
		
		
		
		
	})
	//检查书名
	function ckBookName(){
		let bookName = $("[name='bookName']").val();
		let bookAuthor = $("[name='bookAuthor']").val();
		let bookPrice =$("[name='bookPrice']").val();
		let bookPublishdate = $("[name='bookPublishdate']").val();
		if(($.trim(bookName)==="")||($.trim(bookAuthor)==="")||($.trim(bookPrice)==="")||($.trim(bookPublishdate)==="")){
			$("td:contains('提示')").next().text("书名，作者，价格，出版日期均不能为空").css("color","red");			
		}else{
			$("td:contains('提示')").next().text("");
			return true;
		}
	}
	
	//添加书籍
	function addBook(){
		console.log(0);
	if(ckBookName()){
		$.ajax({			
			type:"post",
			data:$("#fom").serialize()+"&state=addBook",
			url:"BookServlet",
			datType:"text",
			success:function(data){
				if(data==1){
					alert("添加成功");
					 location.reload(); 
				}else{
					alert("添加失败");
				}
			}
			
			
		})
	}
		
	}
	
	//检查书名

	
</script>

</head>
<body>
	<div>
		<div id="ti">欢迎用户：<span id="user"></span></div>
		<h2>添加书籍</h2>
		<div>
			<form id="fom">
				<table>
					<tr>
						<td>书名</td>
						<td>
							<input type="text" name="bookName"/>
						</td>
					</tr>
					<tr>
						<td>作者</td>
						<td>
							<input type="text" name="bookAuthor"/>
						</td>
					</tr>
					<tr>
						<td>价格</td>
						<td>
							<input type="number" name="bookPrice"/>
						</td>
					</tr>
					<tr>
						<td>出版日期</td>
						<td>
							<input type="date" name="bookPublishdate"/>
						</td>
					</tr>
					<tr>
						<td>提示</td>
						<td>				
						</td>
					</tr>
				</table>
				<button type="button" id ="sub">添加</button>
				<button type="button" id ="main">返回主页</button>
			</form>			
		</div>
	</div>

</body>
</html>

二、配置文件
在这里插入图片描述
三、编写Java
1、Servlet
UserServlet请求与响应，实现用户的登录注册

package com.test.servlet;

import java.io.IOException;
import java.util.List;

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

import com.google.gson.Gson;
import com.test.bean.User;
import com.test.dao.UserDao;

/**
 * Servlet implementation class UserServlet
 */
public class UserServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public UserServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub	
		doPost(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		//获取ajax请求数据
		String state = request.getParameter("state");
		String userId = request.getParameter("userId");
		String userName = request.getParameter("userName");
		String password = request.getParameter("password");
		String str = "";
		System.out.println(userName);
		System.out.println(password);		
		UserDao userDao = new UserDao();		
		response.setContentType("text/html;charset=utf-8");			
		if(state.equals("selectOne")){
			List<User> list = userDao.selectOne(userName);
			
			if(list.size()!=0){
				str = "用户名存在";
			}else{
				str = "用户名不存在";
			}
			
		}else if("registerUser".equals(state)){
			int num = userDao.registerUser(userName,password);
			System.out.println(num);
			if(num==1){
				str = "添加成功";
			}else{
				str = "添加失败";
			}
		}else if("userLogin".equals(state)){
			List<User> list = userDao.userLogin(userName,password);			
			Gson gson = new Gson();	
			System.out.println(list);
			if(list.size()!=0){
				str = gson.toJson(list);
			}else{
				str = null;
			}
			
		}
		
		response.getWriter().print(str);
		
	}

}

BookServlet请求与响应，实现增删查改

package com.test.servlet;

import java.io.IOException;
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;

import com.google.gson.Gson;
import com.test.bean.Book;
import com.test.dao.BookDao;

/**
 * Servlet implementation class BookServlet
 */
public class BookServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public BookServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		
		doPost(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		String state = request.getParameter("state");
		response.setContentType("text/html;charset=utf-8");
		BookDao bookDao = new BookDao();
		
		if(state.equals("selectMyBook")){
			selectMyBooks(bookDao,request,response);
		}else if(state.equals("bookBack")){
			updateMyBook(bookDao,request,response);
		}else if(state.equals("allBook")){
			selectAllBook(bookDao,request,response);
		}else if(state.equals("borrow")){
			updateBorrow(bookDao,request,response);
		}else if(state.equals("addBook")){
			updateAddBook(bookDao,request,response);
		}else if(state.equals("delBook")){
			updateDellBook(bookDao,request,response);
		}
	}
	//根据BookId删除书籍
	private void updateDellBook(BookDao bookDao, HttpServletRequest request, HttpServletResponse response) throws IOException {
		// TODO Auto-generated method stub
		String bookId = request.getParameter("bookId");
		System.out.println(bookId);
		int num = bookDao.DelBooks(bookId);
		if(num==1){
			response.getWriter().print("删除成功");
		}else{
			response.getWriter().print("删除失败");
		}
	}

	//添加书籍
	private void updateAddBook(BookDao bookDao, HttpServletRequest request, HttpServletResponse response) throws IOException {
		// TODO Auto-generated method stub
		String bookName = request.getParameter("bookName");
		String bookAuthor = request.getParameter("bookAuthor");
		String bookPrice = request.getParameter("bookPrice");
		String bookPublishdate = request.getParameter("bookPublishdate");
		int num = bookDao.updateAdd(bookName,bookAuthor,bookPrice,bookPublishdate);
		System.out.println(num);
		System.out.println(bookName);
		System.out.println(bookAuthor);
		System.out.println(bookPrice);
		System.out.println(bookPublishdate);		
		response.getWriter().print(num);
		
	}
//借阅书籍
	private void updateBorrow(BookDao bookDao, HttpServletRequest request, HttpServletResponse response) throws IOException {
		// TODO Auto-generated method stub
		System.out.println(request.getParameter("bookId"));
		System.out.println(request.getParameter("userId"));
		int bookId = Integer.parseInt(request.getParameter("bookId"));
		int userId = Integer.parseInt(request.getParameter("userId"));
		System.out.println(bookId);
		System.out.println(userId);
		int num = bookDao.excuteUserId(userId,bookId);
		System.out.println(num);
		if(num==1){
			response.getWriter().print("借阅成功");
		}else{
			response.getWriter().print("借阅失败");
		}
	}

	//查询所有书籍
	private void selectAllBook(BookDao bookDao, HttpServletRequest request, HttpServletResponse response) throws IOException {
		// TODO Auto-generated method stub
		List<Book> list = new ArrayList<>();
		list = bookDao.excuteAllBook();
		Gson gson = new Gson();		
		if(list.size()>0){
			response.getWriter().print(gson.toJson(list));
		}else{
			response.getWriter().print("查询失败");
		}
	}
	//修改书籍
	private void updateMyBook(BookDao bookDao, HttpServletRequest request, HttpServletResponse response) throws IOException {
		// TODO Auto-generated method stub
		String bookId = request.getParameter("bookId");
		int num = bookDao.excuteDelBook(bookId);
		response.getWriter().print(num);
	}

	//通过用户id查询书籍
	private void selectMyBooks(BookDao bookDao, HttpServletRequest request, HttpServletResponse response) throws IOException {
		// TODO Auto-generated method stub
		String userId = request.getParameter("userId");
		List<Book> list = new ArrayList<>();
		Gson gson = new Gson();
		list = bookDao.excuteQueryMyBooks(userId);
		response.getWriter().print(gson.toJson(list));
	}

	

}

2、实体类
Book类

package com.test.bean;

public class Book {
	private int bookId;
	private String bookName;
	private String bookAuthor;
	private String bookPrice;
	private String bookPublishDate;
	private int userId;
	public Book() {
		super();
		// TODO Auto-generated constructor stub
	}
	
	public Book(int bookId, String bookName, String bookAuthor, String bookPrice, String bookPublishDate, int userId) {
		super();
		this.bookId = bookId;
		this.bookName = bookName;
		this.bookAuthor = bookAuthor;
		this.bookPrice = bookPrice;
		this.bookPublishDate = bookPublishDate;
		this.userId = userId;
	}
	public int getBookId() {
		return bookId;
	}
	public void setBookId(int bookId) {
		this.bookId = bookId;
	}
	public String getBookName() {
		return bookName;
	}
	public void setBookName(String bookName) {
		this.bookName = bookName;
	}
	public String getBookAuthor() {
		return bookAuthor;
	}
	public void setBookAuthor(String bookAuthor) {
		this.bookAuthor = bookAuthor;
	}
	public String getBookPrice() {
		return bookPrice;
	}
	public void setBookPrice(String bookPrice) {
		this.bookPrice = bookPrice;
	}
	public String getBookPublishDate() {
		return bookPublishDate;
	}
	public void setBookPublishDate(String bookPublishDate) {
		this.bookPublishDate = bookPublishDate;
	}
	public int getUserId() {
		return userId;
	}
	public void setUserId(int userId) {
		this.userId = userId;
	}
	@Override
	public String toString() {
		return "Book [bookId=" + bookId + ", bookName=" + bookName + ", bookAuthor=" + bookAuthor + ", bookPrice="
				+ bookPrice + ", bookPublishDate=" + bookPublishDate + ", userId=" + userId + "]";
	}
	
}

User类

package com.test.bean;

public class User {
	
	private int userId;
	private String username;
	private String password;
	
	public User(int userId, String username, String password) {
		super();
		this.userId = userId;
		this.username = username;
		this.password = password;
	}
	public User() {
		super();
		// TODO Auto-generated constructor stub
	}
	public int getUserId() {
		return userId;
	}
	public void setUserId(int userId) {
		this.userId = userId;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	@Override
	public String toString() {
		return "User [userId=" + userId + ", username=" + username + ", password=" + password + "]";
	}
}

3、数据访问层
BaseDao连接数据库实现，包装增删查改

package com.test.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *	连接数据库，并提供基本的增删改查功能。
 *	url:连接数据库地址
 *		格式：jdbc:mysql://localhost:3306/库名
 *	getConnection(url,"用户名","密码");
 */
public class BaseDao {
	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;
	private void getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/book";
			conn = DriverManager.getConnection(url,"root","123456");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 关闭数据库连接
	 */
	public void close() {

		if (rs!=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (ps!=null) {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}
	
	/**
	 * 更新方法（包含添加，修改，删除功能）
	 * @param sql	sql语句，实际参数在sql中使用？表示
	 * @param objects	按照顺序传入实际参数
	 * @return	返回-1表示更新失败，返回其他参数表示更新成功
	 */
	public int excuteUpdate(String sql,Object...objects) {
		this.getConnection();
		try {
			ps = conn.prepareStatement(sql);
			if (objects!=null) {
				for (int i = 0; i < objects.length; i++) {
					ps.setObject(i+1, objects[i]);
				}
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			this.close();
		}
		return -1;
	}
	/**
	 * 查询方法
	 * @param sql	sql语句，实际参数在sql中使用？表示
	 * @param objects	按照顺序传入实际参数
	 * @return	返回ResultSet类型的集合，如果为null表示查询失败，或查询为空，返回对象表示查询到数据，
	 * 			使用.next()判断ResultSet中的数据，根据属性类型使用 get类型() 方法;
	 */
	public ResultSet excuteQuery(String sql,Object...objects) {
		this.getConnection();
		try {
			ps = conn.prepareStatement(sql);
			if (objects!=null) {
				for (int i = 0; i < objects.length; i++) {
					ps.setObject(i+1, objects[i]);
				}
			}
			return rs = ps.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 查询所有方法
	 * @param sql	sql语句	 * 
	 * @return	返回ResultSet类型的集合，如果为null表示查询失败，或查询为空，返回对象表示查询到数据，
	 * 			使用.next()判断ResultSet中的数据，根据属性类型使用 get类型() 方法;
	 */
	public ResultSet excuteQuery(String sql){
		this.getConnection();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
		}
	
}

UserDao实现BaseDao,执行用户的注册于登录

package com.test.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.test.bean.User;

public class UserDao extends BaseDao {
	//通过用户名，查看是否有相同的用户名
	public List<User> selectOne(String str){
		String sql = "SELECT * FROM user WHERE user_name=?;";
		ResultSet rs = this.excuteQuery(sql,str);
		List<User> list = new ArrayList<>();		
		try {
			while(rs.next()){
				list.add(new User(rs.getInt(1),rs.getString(2),rs.getString(3)));
			
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		return null;
		
	}
	//添加用户
	public int registerUser(String username,String password) {
		String sql = "INSERT INTO user(user_name,user_password) VALUE(?,?);";		
		return this.excuteUpdate(sql, username,password);
	}
	public List<User> userLogin(String userName, String password) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM user WHERE user_name=? and user_password=?;";
		List<User> list = new ArrayList<>();
		ResultSet rs = this.excuteQuery(sql,userName,password);
		try {
			while(rs.next()){
				list.add(new User(rs.getInt(1), rs.getString(2), rs.getString(3)));
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		return null;
	}

	

}

BookDao实现BaseDao，执行增删查改

package com.test.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.test.bean.Book;

public class BookDao extends BaseDao {
	//通过id查询就借阅书籍
	public List<Book> excuteQueryMyBooks(String userId) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM book WHERE user_id=?;";
		List<Book> list = new ArrayList<>();
		ResultSet rs = this.excuteQuery(sql, userId);
		try {
			while(rs.next()){
				list.add(new Book(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getInt(6)));
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		return null;
 	}
	//通过id删除用户id
	public int excuteDelBook(String userId) {
		// TODO Auto-generated method stub
		String sql = "UPDATE book SET user_id=null WHERE book_id=?;";
		return this.excuteUpdate(sql, userId);
	}
	//查询所有书籍
	public List<Book> excuteAllBook() {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM book;";
		ResultSet rs = this.excuteQuery(sql);
		List<Book> list = new ArrayList<>();
		try {
			while(rs.next()){
				list.add(new Book(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getInt(6)));				
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();//注意查询方法关闭流一定要再获取数据后关闭
		}
		return null;
	}
	//通过userId,boookId借阅书籍
	public int excuteUserId(int userId, int bookId) {
		// TODO Auto-generated method stub
		String sql = "UPDATE book SET user_id=? WHERE book_id=?;";
		return this.excuteUpdate(sql, userId,bookId);
	}
	//添加书籍
	public int updateAdd(String bookName, String bookAuthor, String bookPrice, String bookPublishdate) {
		// TODO Auto-generated method stub
		String sql ="INSERT INTO book(book_name,book_author,book_price,book_publishdate) VALUE(?,?,?,?);";
		return this.excuteUpdate(sql, bookName,bookAuthor,bookPrice,bookPublishdate);		
	}
	//通过bookId删除书籍
	public int DelBooks(String bookId) {
		// TODO Auto-generated method stub
		String sql ="DELETE FROM book WHERE book_id=?;";
		
		return this.excuteUpdate(sql, bookId);
	}

}

问题点：
1、在查询时一定要在查询结束后关闭流
2、在ajax请求时返回的参数类型要注意
3、不同网页直接的参数传递问题通过？…&…=
4、在web.XML文件的配置

Hoetears

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
2001用户登录实现使用servlet,mysql,ajax,gson实现曾删改查

项目目录一、编写Html1.注册页面<!DOCTYPE html><html> <head> <meta charset="utf-8"> <title>注册页面</title> <style> #top{ width: 800px; height: 400px; ...
复制链接

扫一扫

专栏目录