jsp操作MySQL实现查询/插入/删除功能示例

2020-07-27 21:36:53

字体：大中小

来源：转载

供稿：网友

直接贴代码吧：
首先，index_test.jsp页面的代码如下：

复制代码代码如下:

 
<%@ page language="java" pageEncoding="utf-8"%> 
<%@ page contentType="text/html;charset=utf-8"%>&nbsp; 
<%&nbsp; 
&nbsp; &nbsp;request.setCharacterEncoding("UTF-8");&nbsp; 
&nbsp; &nbsp;response.setCharacterEncoding("UTF-8");&nbsp; 
&nbsp; &nbsp;response.setContentType("text/html;charset=utf-8");&nbsp; 
%> 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3c.org/TR/1999/REC-html401-19991224/loose.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head> 
<title>人员操作</title> 
<META http-equiv=Content-Type content="text/html; charset=utf-8"> 
<style> 
#popDivAdd,#popDivDelete { 
position: absolute; 
visibility: hidden; 
overflow: hidden; 
border: 2px solid #AEBBCA; 
background-color: #EEF1F8; 
cursor: move; 
padding: 1px; 
} 

#popTitle { 
background: #9DACBF; 
height: 20px; 
line-height: 20px; 
padding: 1px; 
} 

#popForm { 
padding: 2px; 
} 

.title_left { 
font-weight: bold; 
padding-left: 5px; 
float: left; 
} 

.title_right { 
float: right; 
} 

#popTitle .title_right a { 
color: #000; 
text-decoration: none; 
} 

#popTitle .title_right a:hover { 
text-decoration: underline; 
color: #FF0000; 
} 
</style> 
<script> 
function showPopup(type) {//弹出层 
//var objDiv = null;//objDiv是目标div 
if(type == 'add'){ 
objDiv = document.getElementById("popDivAdd"); 
} 
else 
if(type = 'delete'){ 
objDiv = document.getElementById("popDivDelete"); 
} 
objDiv.style.top = "50px";//设置弹出层距离上边界的距离 
objDiv.style.left = "200px";//设置弹出层距离左边界的距离 
objDiv.style.width = "300px";//设置弹出层的宽度 
objDiv.style.height = "220px";//设置弹出层的高度 
//objDiv.style.display = "block"; 
objDiv.style.visibility = "visible"; 
} 
function hidePopup(type) {//关闭层 
//var objDiv = null;//objDiv是目标div 
if(type == 'add'){ 
objDiv = document.getElementById("popDivAdd"); 
} 
else&nbsp; 
if(type = 'delete'){ 
objDiv = document.getElementById("popDivDelete"); 
} 
objDiv.style.visibility = "hidden"; 
} 
</script> 
</head> 
<body> 
<!-- 表单div --> 
<form action="insert_test.jsp" method="post"> 
<p> 
&nbsp;ID :<input type="text" name="id" value="0" /></br>&nbsp; 
姓名:<input type="text" name="name" value="" /></br> 
性别:<input type="text" name="sex" value="" /></br> 
年龄:<input type="text" name="age" value="" /></br> 
</p>&nbsp; 
&nbsp;&nbsp;&nbsp; <input type="submit" value="提交" />&nbsp;&nbsp; 
&nbsp;&nbsp; <input type="reset" value="重置" />&nbsp;&nbsp; 
&nbsp;&nbsp; <input type="reset" value="取消" onclick="hidePopup('add')" /> 
</form> 
</div> 
</div> 
<div id="popDivDelete"><!-- 删除人员的div --> 
<div id="popTitle"><!-- 标题div --> 
<span class="title_left">删除人员</span> <span class="title_right"> 
<a href="#" onclick="hidePopup('delete');">关闭</a> </span> 
</div> 
<div id="popForm"><!-- 表单div --> 
<form action="delete_test.jsp" method="post"> 
<p> 
&nbsp;ID :<input type="text" name="id" value="" /> </br>&nbsp; 
姓名:<input type="text" name="name" value="" /> </br> 
</p>&nbsp; 
&nbsp;&nbsp;&nbsp; <input type="submit" value="提交" />&nbsp;&nbsp; 
&nbsp;&nbsp; <input type="reset" value="重置" />&nbsp;&nbsp; 
&nbsp;&nbsp; <input type="reset" value="取消" onclick="hidePopup('delete')" /> 
</form> 
</div> 
</div> 
<p> 
<input name="" type="button" onclick="location.href = 'query_test.jsp'" value="查询" />&nbsp;&nbsp; 
<input name="" type="button" onclick="showPopup('add')" value="添加" />&nbsp;&nbsp; 
<input name="" type="button" onclick="showPopup('delete')" value="删除" /> 
</p> 

<script type="text/javascript"> 
/*-------------------------鼠标左键拖动---------------------*/ 
/*--------当不需要实现此功能时，可以将这一部分代码删除------------*/ 
var objDiv = document.getElementById("popDiv"); 
var isIE = document.all ? true : false;//判断浏览器类型 
document.onmousedown = function(evnt) {//当鼠标左键按下后执行此函数 
var evnt = evnt ? evnt : event; 
if (evnt.button == (document.all ? 1 : 0)) { 
mouseD = true;//mouseD为鼠标左键状态标志，为true时表示左键被按下 
} 
} 

objDiv.onmousedown = function(evnt) { 
objDrag = this;//objDrag为拖动的对象 
var evnt = evnt ? evnt : event; 
if (evnt.button == (document.all ? 1 : 0)) { 
mx = evnt.clientX; 
my = evnt.clientY; 
objDiv.style.left = objDiv.offsetLeft + "px"; 
objDiv.style.top = objDiv.offsetTop + "px"; 
if (isIE) { 
objDiv.setCapture(); 
//objDiv.filters.alpha.opacity = 50;//当鼠标按下后透明度改变 
} else { 
window.captureEvents(Event.MOUSEMOVE);//捕获鼠标拖动事件 
//objDiv.style.opacity = 0.5;//当鼠标按下后透明度改变 
} 
} 
} 
document.onmouseup = function() { 
mouseD = false;//左键松开 
objDrag = ""; 
if (isIE) { 
objDiv.releaseCapture(); 
//objDiv.filters.alpha.opacity = 100;//当鼠标左键松开后透明度改变 
} else { 
window.releaseEvents(objDiv.MOUSEMOVE);//释放鼠标拖动事件 
//objDiv.style.opacity = 1;//当鼠标左键松开后透明度改变 
} 
} 

document.onmousemove = function(evnt) { 
var evnt = evnt ? evnt : event; 
if (mouseD == true && objDrag) { 
var mrx = evnt.clientX - mx; 
var mry = evnt.clientY - my; 
objDiv.style.left = parseInt(objDiv.style.left) + mrx + "px"; 
objDiv.style.top = parseInt(objDiv.style.top) + mry + "px"; 
mx = evnt.clientX; 
my = evnt.clientY; 
} 
} 
</script> 
</body> 
</html> 

query_test.jsp页面代码如下：

复制代码代码如下:

 
<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="utf-8"%> 
<%@ page contentType="text/html;charset=utf-8"%> 
<% 
request.setCharacterEncoding("UTF-8"); 
response.setCharacterEncoding("UTF-8"); 
response.setContentType("text/html; charset=utf-8"); 
%> 
<html> 
<body> 
<% 
Connection con=null; 
String url="jdbc:mysql://localhost/html_db?user=root&password=123456"; 
//html_db为数据库名 
Class.forName("org.gjt.mm.mysql.Driver").newInstance();//新建实例 
Connection conn= DriverManager.getConnection(url);//建立连接 
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); 
String sql="select * from person_tb"; 
ResultSet rs=stmt.executeQuery(sql); 
while(rs.next()) {%> 
ID：<%=rs.getString("id")%> </br> 
姓名：<%=rs.getString("name")%></br> 
性别：<%=rs.getString("sex")%></br> 
年龄：<%=rs.getString("age")%></br></br> 
<%}%> 
<%out.print("恭喜你!数据库操作成功！ ");%> 
<% 
rs.close(); 
stmt.close(); 
conn.close(); 
%> 
</br> 
<input name="" type="button" onclick="location.href = 'index_test.jsp'" value="返回" /> 
</body> 
</html> 

insert_test.jsp页面代码如下：

复制代码代码如下:

 
<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="utf-8"%> 
<%@ page contentType="text/html;charset=utf-8"%> 
<% 
request.setCharacterEncoding("UTF-8"); 
response.setCharacterEncoding("UTF-8"); 
response.setContentType("text/html; charset=utf-8"); 
%> 
<htnl> 
<head> 
<title>往数据库插入数据</title> 
</head> 
<body> 
<% 
String id=request.getParameter("id"); //从表单获得 
String name=request.getParameter("name"); //从表单获得 
String sex=request.getParameter("sex"); //从表单获得 
String age=request.getParameter("age"); //从表单获得 

try 
{ 
/** 连接数据库参数 **/ 
String driverName = "com.mysql.jdbc.Driver"; //驱动名称 
String DBUser = "root"; //mysql用户名 
String DBPasswd = "123456"; //mysql密码 
String DBName = "html_db"; //数据库名 

String connUrl = "jdbc:mysql://localhost/" + DBName + "?user=" + DBUser + "&password=" + DBPasswd; 
Class.forName(driverName).newInstance(); 
Connection conn = DriverManager.getConnection(connUrl); 
Statement stmt = conn.createStatement(); 
stmt.executeQuery("SET NAMES UTF8"); 
String insert_sql = "insert into person_tb values('" + id + "','" + name + "','" + sex + "','" + age + "')"; 
String query_sql = "select * from person_tb"; 

try { 
stmt.execute(insert_sql); 
}catch(Exception e) { 
e.printStackTrace(); 
} 
try { 
ResultSet rs = stmt.executeQuery(query_sql); 
while(rs.next()) { 
%> 
ID：<%=rs.getString("id")%> </br> 
姓名：<%=rs.getString("name")%> </br> 
性别：<%=rs.getString("sex")%> </br> 
年龄：<%=rs.getString("age")%> </br> </br> 
<% 
} 
}catch(Exception e) { 
e.printStackTrace(); 
} 
//rs.close(); 
stmt.close(); 
conn.close(); 
}catch (Exception e) { 
e.printStackTrace(); 
} 
%> 
</br> 
<input name="" type="button" onclick="location.href = 'index_test.jsp'" value="返回" />&nbsp;&nbsp; 
</body> 
</html> 

delete_test.jsp页面的代码如下：

复制代码代码如下:

 
<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="UTF-8"%> 
<%@ page contentType="text/html;charset=UTF-8"%> 
<%&nbsp; 
&nbsp; &nbsp;request.setCharacterEncoding("UTF-8");&nbsp; 
&nbsp; &nbsp;response.setCharacterEncoding("UTF-8");&nbsp; 
&nbsp; &nbsp;response.setContentType("text/html; charset=UTF-8");&nbsp; 
%> 
<htnl> 
<head> 
<title>删除人员</title> 
</head> 
<body> 
<% 
String id=request.getParameter("id"); //从表单获得&nbsp; 
String name=request.getParameter("name"); //从表单获得 
try&nbsp; 
{&nbsp; 
/** 连接数据库参数 **/ 
String driverName = "com.mysql.jdbc.Driver"; //驱动名称 
String DBUser = "root"; //mysql用户名 
String DBPasswd = "hel610"; //mysql密码 
String DBName = "html_db"; //数据库名 
String connUrl = "jdbc:mysql://localhost/" + DBName + "?user=" + DBUser + "&password=" + DBPasswd; 
String query_sql = "select id,name,sex,age from person_tb"; 
String delete_sql = ""; 
Class.forName(driverName).newInstance(); 
Connection conn = DriverManager.getConnection(connUrl); 
Statement stmt = conn.createStatement(); 
try{&nbsp; 
if(id != null && id != ""){ 
out.println("删除了id=" + id + "的人。</br></br>"); 
delete_sql = "delete from person_tb where id=" + id; 
} 
else if(name != null && name != ""){ 
out.println("删除了name=" + name + "的人。</br></br>"); 
delete_sql = "delete from person_tb where name='" + name + "'"; 
}else{ 
out.println("请至少输入ID、姓名中的一个！</br></br>"); 
} 
}catch(Exception e){ 
e.printStackTrace(); 
} &nbsp;&nbsp; 
try {&nbsp; 
stmt.execute(delete_sql);&nbsp; 
}catch(Exception e){ 
e.printStackTrace(); 
} 
try {&nbsp; 
ResultSet rs = stmt.executeQuery(query_sql); &nbsp;&nbsp; 
while(rs.next()) { 
%> 
ID：<%=rs.getString("id")%> 
</br> 姓名：<%=rs.getString("name")%> 
</br> 性别：<%=rs.getString("sex")%> 
</br> 年龄：<%=rs.getString("age")%> 
</br> 
</br> 
<% 
} &nbsp; &nbsp; 
}catch(Exception e) { 
e.printStackTrace(); 
}&nbsp; 
//rs.close(); 
stmt.close(); 
conn.close();&nbsp; 
}catch (Exception e) { 
e.printStackTrace(); 
}&nbsp; 
%> 
</br> 
<input name="" type="button" onclick="location.href = 'index_test.jsp'" value="返回" /> 
</body> 
</html> 

在浏览器直接访问index_test.jsp页面，点击删除按钮，填入ID或者姓名之中的任何一个并提交即可进行删除：

执行删除前（下左图）、后（下右图）数据库的内容显示为：

总的来说，查询、插入与删除的功能都实现了。

现在发现的问题是：本来是实现的是弹出层的位置是可以移动的，但现在移动不了了，具体原因在于鼠标拖动部分的第一句代码：var objDiv = document.getElementById("popDiv");即指定拖动对象时，没有指定好。正在找方法解决。有谁有比较好的方法，请帮下忙吧，多谢了！

上一篇：GET方法URL中传递中文参数乱码的解决方法

下一篇：点击地图div上的按钮实现对地图数据的入库操作