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上的按鈕實現對地圖數據的入庫操作