jsp操作MySQL实现查询/插入/删除功能示例
程序员文章站
2023-11-27 16:30:28
直接贴代码吧： 首先，index_test.jsp页面的代码如下： 复制代码 代码如下: <%@ page language="java" pageencoding=...
直接贴代码吧：
首先，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");即指定拖动对象时，没有指定好。正在找方法解决。有谁有比较好的方法，请帮下忙吧，多谢了！
首先，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");即指定拖动对象时，没有指定好。正在找方法解决。有谁有比较好的方法，请帮下忙吧，多谢了！
推荐阅读
-
jsp操作MySQL实现查询/插入/删除功能示例
-
jsp连接MySQL操作GIS地图数据实现添加point的功能代码
-
jsp连接MySQL实现插入insert操作功能示例
-
jsp操作MySQL实现查询/插入/删除功能示例
-
ThinkPHP框架实现用户信息查询更新及删除功能示例
-
荐 JAVA实现对已有数据库的插入、删除、更改、查询操作详细解释
-
运用Servlet+JSP+MySQL+JDBCTempleat+Duird+BeanUtilS+tomcat技术。实现用户信息的增删改查操作(添加功能及界面实现)。
-
PHP实现数组向任意位置插入,删除,替换数据操作示例
-
PHP中使用jQuery+Ajax实现分页查询多功能操作(示例讲解)
-
PHP操作redis实现的分页列表，新增，删除功能封装类与用法示例