【有手就会】Eclipse+MySQL+jsp实现对图书的增删改查(代码简单易理解)

     阅读:22

【说明】

使用IDE是Eclipse,数据库用的是MySQL5.7.23

全程只需要建立这几个jsp文件(如图),没有java文件和servlet,所以也不需要建package

9e53c57c350e455eb773d87f6afd7ec0.png

因为是入门级的表单开发,代码为了容易看懂,用的都是最简单简洁的啦,页面很简陋吼~如图所示:

watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6L655ZCD546J5a2Q54On6L655omT5Luj56CB,size_20,color_FFFFFF,t_70,g_se,x_16

watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6L655ZCD546J5a2Q54On6L655omT5Luj56CB,size_20,color_FFFFFF,t_70,g_se,x_16

watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6L655ZCD546J5a2Q54On6L655omT5Luj56CB,size_20,color_FFFFFF,t_70,g_se,x_16

 

 

 

【详细步骤】

第一步:在mysql建立数据库,表格,并插入一些原始数据

我是用命令行窗口进行的,如果觉得麻烦,可以去载一个Navicat,可以对数据库进行可视化管理,新用户有14天的试用期,当然网上也有破解版。

命令行窗口代码:

create database db2;
use db2;
create table books(id int(10) primary key,name varchar(20),author varchar(20),price float(7,2) );

watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6L655ZCD546J5a2Q54On6L655omT5Luj56CB,size_8,color_FFFFFF,t_70,g_se,x_16

插入一些数据:

insert into books(id,name,author,price)values('1001','西游记','吴承恩','20');

watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6L655ZCD546J5a2Q54On6L655omT5Luj56CB,size_11,color_FFFFFF,t_70,g_se,x_16

查看表内已有数据:

select* from books;

:我这里特地插入了中文字段,如果你插入失败,或者是插入后显示乱码了,那么说明你mysql的字符集需要修改!

修改方法:如果是macos系统,请移步我的另一篇博客,对于windows系统,首先退出mysql,然后找到你的my.ini文件,这个文件在哪可以参考我的:

watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6L655ZCD546J5a2Q54On6L655omT5Luj56CB,size_15,color_FFFFFF,t_70,g_se,x_16

打开这个文件,做如下修改(即找到[client][mysql][mysqld]对应修改):

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

[mysqld]

character-set-server=utf8

修改保存后再次启动mysql就不会出现乱码问题了,这里还需要注意一下,修改后你再去查看刚才之前的表格可能还是乱码,那么请再创建一个新的表格,就是正常的啦。


 

第二步:导包——将数据库连接驱动导入lib文件夹

驱动程序下载链接(实在不懂就去看我最早写的一篇博客)MySQL :: MySQL Community Downloadshttps://dev.mysql.com/downloads/

 下载后解压得到后缀名是jar的驱动程序

将驱动导入lib文件夹(lib文件夹在你这个web项目的某一个角落,自己找)

watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6L655ZCD546J5a2Q54On6L655omT5Luj56CB,size_6,color_FFFFFF,t_70,g_se,x_16

 第三步:建立jsp文件,一共是6个

需要注意一下:你创建项目后这三个包导入了吗?没有的话必须要去导入一下哦,不然后果就是报错!中间那个如果没用上servlet可以不导。

33562d6915b74329a61e1a2dd59c984b.png

 导入方式:1.右键项目,选择build path

 watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6L655ZCD546J5a2Q54On6L655omT5Luj56CB,size_11,color_FFFFFF,t_70,g_se,x_16

2.选择libraries,然后选第二项

watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6L655ZCD546J5a2Q54On6L655omT5Luj56CB,size_16,color_FFFFFF,t_70,g_se,x_16

3.在你tomcat安装包里找到lib文件夹,找到这三个文件,选择并导入。

watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6L655ZCD546J5a2Q54On6L655omT5Luj56CB,size_12,color_FFFFFF,t_70,g_se,x_16

 导入完成后在项目中可以看到啦

watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6L655ZCD546J5a2Q54On6L655omT5Luj56CB,size_5,color_FFFFFF,t_70,g_se,x_16

 进入正题!建立jsp文件!直接甩代码了

需要注意的就是数据库连接那里的数据库密码要改成你自己的哈

watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6L655ZCD546J5a2Q54On6L655omT5Luj56CB,size_17,color_FFFFFF,t_70,g_se,x_16

如果你没有按我上面说的建数据库和建表,那么数据库名和表名你也要修改,图片里圈起来的那些,每个文件都要改,要改挺多的……

watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6L655ZCD546J5a2Q54On6L655omT5Luj56CB,size_18,color_FFFFFF,t_70,g_se,x_16

1.index.jsp 

<%@ page language="java" import="java.util.*" import="java.sql.*" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>首页</title>
</head>
<body>
<form action="result.jsp" method="post">
<table align="center" border="1">
<tr><th colspan="2"><input type="text" name="name" placeholder="请输入书名"><button type="submit">查找</button></th>
<th colspan="3"><a href="add.jsp">添加</a></th></tr>
<tr><th>编号</th><th>书名</th><th>作者</th><th>价格</th><th>操作</th></tr>
<%
try{
	Class.forName("com.mysql.jdbc.Driver");
	Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/db2","root","改成你的!");
	Statement stmt=con.createStatement();
	String sql="select * from books";
	ResultSet rs=stmt.executeQuery(sql);
	while(rs.next()){
		int id=rs.getInt(1);
		out.println("<tr><td>"+id+"</td><td>"+rs.getString(2)+"</td><td>"+rs.getString(3)+"</td><td>"+rs.getString(4)+"</td><td><a href='edit.jsp?id="+id+"'>修改</a>&nbsp;<a href='delete.jsp?id="+id+"'>删除</a></td></tr>");
	}
	rs.close();
	stmt.close();
	con.close();
}catch(Exception e){
	System.out.print(e);
}
%>
</table>
</form>
</body>
</html>

2.add.jsp  

<%@ page language="java" import="java.util.*" import="java.sql.*" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加书籍</title>
</head>
<body>
<%request.setCharacterEncoding("utf-8");%>
<form action="add.jsp" method="post">
<table align="center" border="1">
<caption>添加图书</caption>
<tr>
<th>编号:</th>
<td><input name="id" type="text" required="required"></td>
</tr>
<tr>
<th>书名:</th>
<td><input name="name" type="text"></td>
</tr>
<tr>
<th>作者:</th>
<td><input name="author" type="text"></td>
</tr>
<tr>
<th>价格:</th>
<td><input name="price" type="text"></td>
</tr>
<tr>
<td></td>
<th>
	<input type="submit" name="submit" value="添加">
	<input type="reset" value="重置">
</th>
</tr>
</table>
</form>
<%
String submit=request.getParameter("submit");
if(!"".equals(submit)&&submit!=null){
	String id=request.getParameter("id");
	String name=request.getParameter("name");
	String author=request.getParameter("author");
	String price=request.getParameter("price");
	try{
		Class.forName("com.mysql.jdbc.Driver");
		Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/db2","root","改成你的!");
		Statement stmt=con.createStatement();
		String sql="insert into books(id,name,author,price)values('"+id+"','"+name+"','"+author+"','"+price+"')";
		int i=stmt.executeUpdate(sql);
		if(i==1){
			out.print("<script>alert('添加成功,单击确定返回主页面!');</script>");
			response.setHeader("refresh", "1;url=index.jsp");
		}else{
			out.println("<script>alert('添加失败,单击确定返回');</script>");
			response.setHeader("refresh","1;url=add.jsp");
		}
		con.close();
		stmt.close();
	}catch(Exception e){
		out.println("<script>alert('添加失败,请重试');</script>");
		response.setHeader("refresh","1;url=add.jsp");
	}
}
%>
</body>
</html>

 

 3.update.jsp 

<%@ page language="java" import="java.sql.*" import="java.util.*" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title></title>
</head>
<body>
<%
	request.setCharacterEncoding("utf-8");
	Class.forName("com.mysql.jdbc.Driver");
	Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/db2","root","改成你的!");
	Statement stmt=con.createStatement();
	String id=request.getParameter("id");
	String name=request.getParameter("name");
	String author=request.getParameter("author");
	String price=request.getParameter("price");
	String sql="update books set id='"+id+"',name='"+name+"',author='"+author+"',price='"+price+"'where id="+id;
	int i=stmt.executeUpdate(sql);
	if(i==1){
		out.print("<script>alert('修改成功');</script>");
		response.setHeader("refresh","1;url=index.jsp");
	}else{
		out.print("<script>alert('修改失败');</script>");
		response.setHeader("refresh","1;url=index.jsp");
	}
	stmt.close();
	con.close();
%>
</body>
</html>

 4.result.jsp 

<%@ page language="java" import="java.util.*" import="java.sql.*" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>查找结果</title>
</head>
<body>
<table align="center" border="1">
<tr><th colspan="4">查找结果</th>
<th colspan="1"><a href='index.jsp'>返回</a></th></tr>
<tr><th>编号</th><th>书名</th><th>作者</th><th>价格</th><th>操作</th></tr>
<%
request.setCharacterEncoding("utf-8");
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/db2","root","改成你的!");
Statement stmt=con.createStatement();
String name=request.getParameter("name");
String sql="select * from books where name='" + name +"'";
ResultSet rs=stmt.executeQuery(sql);
int flag=0;
while(rs.next()){
	flag=1;
	int id=rs.getInt(1);
	out.println("<tr><td>"+id+"</td><td>"+rs.getString(2)+"</td><td>"+rs.getString(3)+"</td><td>"+rs.getString(4)+"</td><td><a href='edit.jsp?id="+id+"'>修改</a>&nbsp;<a href='delete.jsp?id="+id+"'>删除</a></td></tr>");
}
if(flag==1){
	out.println("<script>alert('查找完成!');</script>");
}else{
	out.println("<script>alert('没有找到!');</script>");
	response.setHeader("refresh", "1;url=index.jsp");
}
rs.close();
stmt.close();
con.close();
%>
</table>
</body>
</html>

 5.edit.jsp

<%@ page language="java" import="java.util.*" import="java.sql.*" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>修改图书信息</title>
</head>
<body>
<%
try{
	request.setCharacterEncoding("utf-8");
	Class.forName("com.mysql.jdbc.Driver");
	Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/db2","root","改成你的!");
	Statement stmt=con.createStatement();
	String id=request.getParameter("id");
	ResultSet rs=stmt.executeQuery("select * from books where id="+id);
	rs.next();	
%>
<form action="update.jsp" method="post">
<table align="center">
<caption>修改图书信息</caption>
<tr>
<th>编号:</th>
<td><input name="id" type="text" value="<%=rs.getString(1)%>" readonly="readonly"></td>
</tr>
<tr>
<th>书名:</th>
<td><input name="name" type="text" value="<%=rs.getString(2)%>"></td>
</tr>
<tr>
<th>作者:</th>
<td><input name="author" type="text" value="<%=rs.getString(3)%>"></td>
</tr>
<tr>
<th>价格:</th>
<td><input name="price" type="text" value="<%=rs.getString(4)%>">元</td>
</tr>
<tr>
	<th colspan="2">
	<input type="submit" value="修改">
	<input type="reset" value="重置">
	</th>	
</tr>
</table>
</form>
<%
	rs.close();
	con.close();
}catch(Exception e){
	out.println("<script>alert('修改失败,请重试');</script>");
	response.setHeader("refresh","1;url=edit.jsp");
} 
%>
</body>
</html>

  6.delete.jsp

<%@ page language="java" import="java.sql.*" import="java.util.*" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>删除图书</title>
</head>
<body>
<%
	request.setCharacterEncoding("utf-8");
	Class.forName("com.mysql.jdbc.Driver");
	Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/db2","root","改成你的!");
	Statement stmt=con.createStatement();
	String id=request.getParameter("id");
	int i=stmt.executeUpdate("delete from books where id="+id);
	if(i==1){
		out.print("<script>alert('删除成功!');</script>");
		response.setHeader("refresh","1;url=index.jsp");
	}else{
		out.print("<script>alert('删除失败,单击确定后返回首页')</script>");
		response.setHeader("refresh","1;url=index.jsp");
	}
	stmt.close();
	con.close();
%>
</body>
</html>

overover结束了!把这些jsp文件建好后,运行index.jsp就得了,该交代的我也都说清楚了,代码已经是能删则删,简洁的不能再简洁了(就是我懒得写注释😌),对于刚入门的应该琢磨一下就能看懂了吧,有不懂的可以留言吼,掰掰。