程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 編程語言 >> 網頁編程 >> JSP編程 >> 關於JSP >> jsp操作MySQL實現查詢/插入/刪除功能示例

jsp操作MySQL實現查詢/插入/刪除功能示例

日期:2017/1/11 9:54:30      編輯:關於JSP
直接貼代碼吧:
首先,index_test.jsp頁面的代碼如下:
復制代碼 代碼如下:
<%@ page language="java" 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"); 
%>
<!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 
if(type = 'delete'){
objDiv = document.getElementById("popDivDelete");
}
objDiv.style.visibility = "hidden";
}
</script>
</head>
<body>
<!-- 表單div -->
<form action="insert_test.jsp" method="post">
<p>
 ID :<input type="text" name="id" value="0" /></br> 
姓名:<input type="text" name="name" value="" /></br>
性別:<input type="text" name="sex" value="" /></br>
年齡:<input type="text" name="age" value="" /></br>
</p> 
    <input type="submit" value="提交" />  
   <input type="reset" value="重置" />  
   <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>
 ID :<input type="text" name="id" value="" /> </br> 
姓名:<input type="text" name="name" value="" /> </br>
</p> 
    <input type="submit" value="提交" />  
   <input type="reset" value="重置" />  
   <input type="reset" value="取消" onclick="hidePopup('delete')" />
</form>
</div>
</div>
<p>
<input name="" type="button" onclick="location.href = 'query_test.jsp'" value="查詢" />  
<input name="" type="button" onclick="showPopup('add')" value="添加" />  
<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="返回" />  
</body>
</html>

delete_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"); //從表單獲得
try 

/** 連接數據庫參數 **/
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{ 
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();
}   
try { 
stmt.execute(delete_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="返回" />
</body>
</html>

在浏覽器直接訪問index_test.jsp頁面,點擊刪除按鈕,填入ID或者姓名之中的任何一個並提交即可進行刪除:
 
執行刪除前(下左圖)、後(下右圖)數據庫的內容顯示為:
 

總的來說,查詢、插入與刪除的功能都實現了。

現在發現的問題是:本來是實現的是彈出層的位置是可以移動的,但現在移動不了了,具體原因在於鼠標拖動部分的第一句代碼:var objDiv = document.getElementById("popDiv");即指定拖動對象時,沒有指定好。正在找方法解決。有誰有比較好的方法,請幫下忙吧,多謝了!
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved