jsp操作MySQL实现查询/插入/删除功能示例-巨人网络通讯

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

直接贴代码吧：
首先，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"); 
} 
elsenbsp; 
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=rootpassword=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"); //从表单获得 
trynbsp; 
{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");即指定拖动对象时，没有指定好。正在找方法解决。有谁有比较好的方法，请帮下忙吧，多谢了！

您可能感兴趣的文章: