<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>数据表格</title>
<%
String pid = request.getParameter("pid");
if(pid == null || pid.trim().length() == 0 )
{
pid = "0";
} %>
<script type="text/javascript" src="js/jquery-easyui-1.4.4/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="js/jquery-easyui-1.4.4/themes/icon.css">
<link rel="stylesheet" type="text/css" href="js/jquery-easyui-1.4.4/themes/default/easyui.css">
<script type="text/javascript" src="js/jquery-easyui-1.4.4/jquery.easyui.min.js"></script>
<script type="text/javascript" src="js/jquery-easyui-1.4.4/locale/easyui-lang-zh_CN.js"></script> <script type="text/javascript">
$(function(){
$('#mydg').datagrid({
title:'地区列表',
//singleSelect:true,
width:600,
height:300,
fitColumns:true,
striped:true,
idField:'id',//设置后可实现跨页选择
pagination:true,//分页工具栏
rownumbers:true,
pageSize:5,
pageList:[5,10,15],
sortName:'id',
remoteSort:false,//定义从服务器对数据进行排序。
sortOrder:'desc',
url:'MembersList?pid=<%=pid%>',
toolbar: [{
iconCls: 'icon-add',
text:'增加地区',
handler: function(){
$("#addform").form('reset');
//清理id
$("#id").val(""); $('#add').dialog({
title:'增加地区'
});
$('#add').dialog('open'); }
},'-',{
iconCls: 'icon-edit',
text:'编辑地区',
handler: function(){
var s = $("#mydg").datagrid('getSelected');
if(s == null)
{
alert('请选择一条数据');
}
else
{
alert(s);
//编辑数据
$("#addform").form('reset');
$('#add').dialog({
title:'编辑地区'
});
//绑定数据
$("#addform").form('load',s); $('#add').dialog('open'); }
//alert('编辑按钮')
}
},'-',{
iconCls: 'icon-remove',
text:'删除地区',
handler: function(){
var s = $("#mydg").datagrid('getSelections');
if(s.length > 0)
{
$.messager.confirm('确认','您确认想要删除记录吗？',function(r){
if (r){ var ids = "";
for(var i = 0; i < s.length; i ++)
{
ids += s[i].id;
if(i != s.length - 1)
{
ids += ",";
}
} //alert('ids=' + ids);
$.get("Deletedg?ids=" + ids,
function(data,status)
{
var data = eval('('+ data + ')');
$.messager.show({title:'信息', msg:data.message});
}); $("#mydg").datagrid('reload'); $("#mydg").datagrid('clearSelections');
}
});
}
else
{
alert('请选择数据');
}
}
}], frozenColumns:[[//冻结列
{field:'',checkbox:true},
{field:'id',width:80,title:'ID'}
]],
columns:[[
{field:'parentid',width:80,title:'父ID'},
{field:'name',width:80,title:'地区名',
formatter: function(value,row,index)//列格式化
{
value = '<a href=datagrid.jsp?pid=' + row.id + '>' + value +'</a>' ;
return value; }
},
{field:'postcode',width:80,title:'邮编'}, ]]
});
//按钮
$('#bt').click(function(){
$("#addform").form('submit');
return false;
}); //表单
$("#addform").form({
url:'AddMem',
onSubmit:function(){
//alert('表单测试');
var isValid = $(this).form('validate');
if(!isValid)
{
$.messager.show({title:'信息',msg:'输入有误'});
return false
}
},
novalidate:false,
success:function(data){ var data = eval('('+ data + ')'); $.messager.show({title:'信息', msg:data.message}); if(data.success == true)
{ $("#add").dialog('close');
$("#mydg").datagrid('reload');
} //alert(data.message);
}
});
});
</script>
</head>
<body>
<!--
<table id="mydatagrid" class="easyui-datagrid" style="width:500px;height:300px;"
data-options="url:'',title:'地区列表',singleSelect:true,collapsible:true">
<thead>
<tr>
<th data-options="field:'id',width:100">id</th>
<th data-options="field:'parentid',width:100">父id</th>
<th data-options="field:'name',width:100">地区名</th>
<th data-options="field:'postcode',width:100">邮编</th>
</tr>
</thead>
</table>
-->
<table id="mydg"></table> <div id="add" class="easyui-dialog" style="width:300px" data-options="title:'添加地区',closed:true">
<form id="addform" method="post">
<table>
<tr>
<td>地区名称:</td>
<td><input name="name" class="easyui-textbox"
data-options="required:true,validType:'length[2,5]'"></td>
</tr>
<tr>
<td>邮政编码:</td>
<td><input name="postcode" class="easyui-numberbox"
data-options="required:false,validType:'length[6,6]'"></td> <input type="hidden" value="" name="parentid">
<input type="hidden" value="" name="id" id="id"> </tr>
<tr>
<td colspan="2" align="center"><a href="#" id="bt" style="width:100px" class="easyui-linkbutton">提交</a></td>
</tr>
</table>
</form>
</div>
</body>
</html>
查询Servlet
package com.hanqi; import java.io.IOException;
import java.util.ArrayList; import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import com.alibaba.fastjson.JSON; /**
* Servlet implementation class jsonMembers
*/
@WebServlet("/jsonMembers")
public class jsonMembers extends HttpServlet {
private static final long serialVersionUID = 1L; /**
* @see HttpServlet#HttpServlet()
*/
public jsonMembers() {
super();
// TODO Auto-generated constructor stub
} /**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//接收地区父id
String pid = request.getParameter("pid"); //如果没有收到,赋初值-1
if(pid == null || pid.trim().length() == 0)
{
pid = "-1";
} try { //实例化集合,接收查询结果
ArrayList<Member> arr = new ArrayList<Member>(); //实例化数据库操作类
AreaDao ad = new AreaDao(); //调用查询方法
arr = ad.selArea(Integer.parseInt(pid)); //初始化向前台输出的json字符串
String str = ""; //如果查询到数据
if(arr != null)
{
//转换成json
str = JSON.toJSON(arr).toString();
} //str = JSON.toJSONString(arr); //输出json
response.getWriter().print(str); }catch (Exception e) { response.getWriter().append(e.getMessage());
} //response.getWriter().append("Served at: ").append(request.getContextPath());
} /**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
} }
增加/修改的Servlet
package com.hanqi; import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; /**
* Servlet implementation class AddMem
*/
@WebServlet("/AddMem")
public class AddMem extends HttpServlet {
private static final long serialVersionUID = 1L; /**
* @see HttpServlet#HttpServlet()
*/
public AddMem() {
super();
// TODO Auto-generated constructor stub
} /**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //接收表单数据
String name = request.getParameter("name");
String postcode = request.getParameter("postcode");
String parentid = request.getParameter("parentid");
//id用来判断是添加还是修改
String id = request.getParameter("id"); //判断数据是否合法
if(name == null || name.trim().length() == 0)
{
response.getWriter().append("{\"success\":false, \"message\":\"名称不能为空\"}");
}
else if(parentid == null || parentid.trim().length() == 0)
{
response.getWriter().append("{\"success\":false, \"message\":\"父id不能为空\"}");
}
else
{
//转换数据类型
int pid = Integer.parseInt(parentid); //实例化实体类
Member mem = new Member(); //向实体类对象添加参数
mem.setName(name);
mem.setPostcode(postcode);
mem.setParentid(pid); //实例化数据库操作类
AreaDao ad = new AreaDao(); try {
//影响行数
int row = -1; //如果收到id,说明是修改请求
if(id != null && id.trim().length() > 0)
{
int iid = Integer.parseInt(id);
//向实体类对象添加id参数
mem.setId(iid);
//调用修改方法
row = ad.updateArea(mem);
//输出成功信息
response.getWriter().append("{\"success\":true, \"message\":\"成功修改"+row +"条数据\"}");
}
else
{
//没有收到id,则添加数据
row = ad.addArea(mem);
//输出成功信息
response.getWriter().append("{\"success\":true, \"message\":\"成功添加"+row +"条数据\"}");
} } catch (Exception e) {
//异常处理,输出错误信息
response.getWriter().append("{\"success\":false, \"message\":\"错误信息:"+ e.getMessage()+"\"}");
} } //response.setHeader("refresh", "1;URL=memList.jsp?pid=" + parentid);
//response.getWriter().append("Served at: ").append(request.getContextPath());
} /**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
} }
删除的Servlet
package com.hanqi; import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; /**
* Servlet implementation class AddMem
*/
@WebServlet("/AddMem")
public class AddMem extends HttpServlet {
private static final long serialVersionUID = 1L; /**
* @see HttpServlet#HttpServlet()
*/
public AddMem() {
super();
// TODO Auto-generated constructor stub
} /**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //接收表单数据
String name = request.getParameter("name");
String postcode = request.getParameter("postcode");
String parentid = request.getParameter("parentid");
//id用来判断是添加还是修改
String id = request.getParameter("id"); //判断数据是否合法
if(name == null || name.trim().length() == 0)
{
response.getWriter().append("{\"success\":false, \"message\":\"名称不能为空\"}");
}
else if(parentid == null || parentid.trim().length() == 0)
{
response.getWriter().append("{\"success\":false, \"message\":\"父id不能为空\"}");
}
else
{
//转换数据类型
int pid = Integer.parseInt(parentid); //实例化实体类
Member mem = new Member(); //向实体类对象添加参数
mem.setName(name);
mem.setPostcode(postcode);
mem.setParentid(pid); //实例化数据库操作类
AreaDao ad = new AreaDao(); try {
//影响行数
int row = -1; //如果收到id,说明是修改请求
if(id != null && id.trim().length() > 0)
{
int iid = Integer.parseInt(id);
//向实体类对象添加id参数
mem.setId(iid);
//调用修改方法
row = ad.updateArea(mem);
//输出成功信息
response.getWriter().append("{\"success\":true, \"message\":\"成功修改"+row +"条数据\"}");
}
else
{
//没有收到id,则添加数据
row = ad.addArea(mem);
//输出成功信息
response.getWriter().append("{\"success\":true, \"message\":\"成功添加"+row +"条数据\"}");
} } catch (Exception e) {
//异常处理,输出错误信息
response.getWriter().append("{\"success\":false, \"message\":\"错误信息:"+ e.getMessage()+"\"}");
} } //response.setHeader("refresh", "1;URL=memList.jsp?pid=" + parentid);
//response.getWriter().append("Served at: ").append(request.getContextPath());
} /**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
} }
数据库操作类
package com.hanqi; import java.sql.*;
import java.util.*; //数据操作类
public class AreaDao { //增
public int addArea(Member mem) throws Exception
{
int row = -1;
Connection conn = DBHelper.getConnection();
PreparedStatement ps = null; if(conn != null)
{
try
{
String sql = "insert into MEMBERS(id, parentid, name, postcode) values(sq_members_id.nextval,?,?,?)"; ps = conn.prepareStatement(sql); ps.setInt(1, mem.getParentid());
ps.setString(2, mem.getName());
ps.setString(3, mem.getPostcode()); row = ps.executeUpdate();
}
catch(Exception e)
{
throw e;
}
finally
{
ps.close();
} }
conn.close();
return row;
} //修改
public int updateArea(Member mem) throws Exception
{
int row = -1;
Connection conn = DBHelper.getConnection();
PreparedStatement ps = null; if(conn != null)
{
try
{
String sql = "update MEMBERS set parentid=?, name=?, postcode=? where id = ?"; ps = conn.prepareStatement(sql); ps.setInt(1, mem.getParentid());
ps.setString(2, mem.getName());
ps.setString(3, mem.getPostcode());
ps.setInt(4, mem.getId()); row = ps.executeUpdate();
}
catch(Exception e)
{
throw e;
}
finally
{
ps.close();
} }
conn.close();
return row;
} //删除
public int delArea(int id) throws Exception
{
int row = -1;
Connection conn = DBHelper.getConnection();
PreparedStatement ps = null; if(conn != null)
{
try
{
String sql = "delete from MEMBERS where id = ? or parentid = ?"; ps = conn.prepareStatement(sql); ps.setInt(1, id);
ps.setInt(2, id); row = ps.executeUpdate();
}
catch(Exception e)
{
throw e;
}
finally
{
ps.close();
} }
conn.close();
return row;
} //递归方法
private int dg(Connection conn, int id, int row) throws Exception
{ //遍历子节点
PreparedStatement ps = null; PreparedStatement ps1 = null; ResultSet rs =null; try{ String sql = "delete from MEMBERS where id = ?"; ps1 = conn.prepareStatement(sql); ps1.setInt(1, id); row = row + ps1.executeUpdate(); sql = "select * from MEMBERS where parentid = ?"; ps = conn.prepareStatement(sql); ps.setInt(1, id); rs = ps.executeQuery(); if(rs != null)
{ while(rs.next())
{
row = dg(conn, rs.getInt("id"),row);//递归调用
}
} } catch(Exception e)
{
e.printStackTrace(); }
finally
{
try
{
rs.close();
ps.close();
ps1.close(); }
catch(Exception e)
{
throw e;
}
}
return row;
} //递归删除
public int delDG(int id) throws Exception
{
int row = 0; Connection conn = DBHelper.getConnection(); if(conn != null)
{
try
{
//设置手动提交
conn.setAutoCommit(false); //递归级联删除 row = dg(conn,id,row); conn.commit();
}
catch(Exception e)
{
//事务回滚
conn.rollback();
throw e;
} } conn.close();
return row;
} //查询
public ArrayList<Member> selArea(int pid) throws Exception
{
ArrayList<Member> arr = null; Member mem = null; Connection conn = DBHelper.getConnection(); PreparedStatement ps = null; ResultSet rs = null; if(conn != null )
{
try
{ String sql = "select * from MEMBERS where parentid = ?"; ps = conn.prepareStatement(sql); ps.setInt(1, pid); rs = ps.executeQuery(); if(rs != null)
{ arr = new ArrayList<Member>(); while(rs.next())
{ mem = new Member(); mem.setId((rs.getInt("id")));
mem.setParentid(rs.getInt("parentid"));
mem.setName(rs.getString("name"));
mem.setPostcode(rs.getString("postcode")); arr.add(mem);
}
} }
catch(Exception e)
{
throw e;
}
finally
{
try
{
ps.close();
rs.close();
conn.close();
}
catch(Exception e)
{
conn.close();
} }
} return arr;
} //单条查询
public Member getList(int id) throws Exception
{
Member m = null; Connection conn = DBHelper.getConnection(); PreparedStatement ps = null; ResultSet rs = null; if(conn != null )
{
try
{ String sql = "select * from members where id = ?"; ps = conn.prepareStatement(sql); ps.setInt(1, id); rs = ps.executeQuery(); if(rs != null && rs.next())
{
m = new Member(); m.setId(rs.getInt("id"));
m.setParentid(rs.getInt("parentid"));
m.setName(rs.getString("name"));
m.setPostcode(rs.getString("postcode")); } }
catch(Exception e)
{
throw e;
}
finally
{
try
{
ps.close();
rs.close();
conn.close();
}
catch(Exception e)
{
conn.close();
} } } return m;
} //分页查询
public ArrayList<Member> selArea(int pid,int page,int rows) throws Exception
{
ArrayList<Member> arr = null; Member mem = null; Connection conn = DBHelper.getConnection(); PreparedStatement ps = null; ResultSet rs = null; if(conn != null )
{
try
{
int max = page * rows;
int min = (page - 1) * rows; String sql = "select * from (select t.*,rownum rn from (select * from MEMBERS where parentid = ? order by id desc) t where rownum <= ?) where rn > ?"; ps = conn.prepareStatement(sql); ps.setInt(1, pid);
ps.setInt(2, max);
ps.setInt(3, min); rs = ps.executeQuery(); if(rs != null)
{ arr = new ArrayList<Member>(); while(rs.next())
{ mem = new Member(); mem.setId((rs.getInt("id")));
mem.setParentid(rs.getInt("parentid"));
mem.setName(rs.getString("name"));
mem.setPostcode(rs.getString("postcode")); arr.add(mem);
}
} }
catch(Exception e)
{
throw e;
}
finally
{
try
{
ps.close();
rs.close();
conn.close();
}
catch(Exception e)
{
conn.close();
} }
} return arr;
} //记录条数查询
public int getcount(int pid) throws Exception
{
int row = -1; Connection conn = DBHelper.getConnection(); PreparedStatement ps = null; ResultSet rs = null; if(conn != null )
{
try
{ String sql = "select count(1) as rn from MEMBERS where parentid = ?"; ps = conn.prepareStatement(sql);
ps.setInt(1, pid);
rs = ps.executeQuery(); if(rs != null && rs.next())
{
row = rs.getInt("rn");
} }
catch(Exception e)
{
throw e;
}
finally
{
try
{
ps.close();
rs.close();
conn.close();
}
catch(Exception e)
{
conn.close();
} }
} return row;
}
}
实体类
package com.hanqi; //实体类
public class Member { //成员变量设为私有
private int id;
private int parentid;
private String name;
private String postcode; //对外开放getter和setter方法
public int getId() {
return id;
} public void setId(int id) {
this.id = id;
} public int getParentid() {
return parentid;
} public void setParentid(int parentid) {
this.parentid = parentid;
} public String getName() {
return name;
} public void setName(String name) {
this.name = name;
} public String getPostcode() {
return postcode;
} public void setPostcode(String postcode) {
this.postcode = postcode;
} }