案例开发:
利用Datagrid实现一个CRUD
1.DB的实现
①创建数据库stu并切换到数据库stu
create database stu;
use stu;
②创建学生信息表stu
create table stu(
stuId int primary key auto_increment,
stuNum int,
stuName varchar(30),
stuSex varchar(30),
stuAge int,
stuQQ varchar(30)
);
③准备一些记录
insert into stu values(null,1,"tom1","female",18,"12646123");
insert into stu values(null,2,"tom2","female",18,"12646123");
insert into stu values(null,3,"tom3","female",18,"12646123");
insert into stu values(null,4,"tom4","female",18,"12646123");
insert into stu values(null,5,"tom5","female",18,"12646123");
insert into stu values(null,6,"tom6","female",18,"12646123");
insert into stu values(null,7,"tom7","female",18,"12646123");
insert into stu values(null,8,"tom8","female",18,"12646123");
insert into stu values(null,9,"tom9","female",18,"12646123");
insert into stu values(null,10,"tom10","female",18,"12646123");
insert into stu values(null,11,"tom11","female",18,"12646123");
insert into stu values(null,12,"tom12","female",18,"12646123");
2. 2种jar包的加入
数据库jar包
mysql-connector-java-5.1.7-bin.jar
java<-->json 互换需要的jar包
json-lib-2.4-jdk15.jar
3.创建Student实体类
带有以下几个字段，和数据库一一对应
private int stuId;
private int stuNum;
private String stuName;
private String stuSex;
private int stuAge;
private String stuQQ;
4.StuDB 的实现
//连接数据库
public Connection getConn(){
Connection conn=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection("jdbc:mysql:///easyui","root","123456");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
//关闭连接资源
public void closeRes(ResultSet rs,Statement stmt,Connection conn){
try{
if(rs!=null){
rs.close();
rs=null;
}
if(stmt!=null){
stmt.close();
stmt=null;
}
if(conn!=null){
conn.close();
conn=null;
}
}catch(Exception e){
e.printStackTrace();
}
}
//关闭连接资源
public void closeStmt(Statement stmt,Connection conn){
try{
if(stmt!=null){
stmt.close();
stmt=null;
}
if(conn!=null){
conn.close();
conn=null;
}
}catch(Exception e){
e.printStackTrace();
}
}
//查询所有学生信息方法
public List<Student> getAllStu(int page,int pageSize){
List<Student> list = new ArrayList();
Connection conn = null;
ResultSet rs = null;
Statement stmt = null;
try {
conn = getConn();
stmt = conn.createStatement();
String sql = "select * from stu limit "+(page-1)*pageSize+","+pageSize+";";
System.out.println(sql.toString());
rs = stmt.executeQuery(sql);
while(rs.next()){
int stuId = rs.getInt(1);
int stuNum = rs.getInt(2);
String stuName = rs.getString(3);
String stuSex = rs.getString(4);
int stuAge = rs.getInt(5);
String stuQQ = rs.getString(6);
Student stu = new Student(stuId,stuNum,stuName,stuSex,stuAge,stuQQ);
list.add(stu);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
this.closeRes(rs, stmt, conn);
}
return list;
}
//添加学生信息方法
public boolean insertStu(Student stu) {
boolean flag = false;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = this.getConn();
stmt = conn.createStatement();
String sql = "insert into stu values(null,"+stu.getStuNum()+",'"+stu.getStuName()+"','"+stu.getStuSex()+"',"+stu.getstuAge()+",'"+stu.getStuQQ()+"');";
int num = stmt.executeUpdate(sql);
if(num>0) {
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
this.closeStmt(stmt, conn);
}
return flag;
}
//删除学生信息方法
public boolean delStu(int id) {
boolean flag = false;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = this.getConn();
stmt = conn.createStatement();
String sql = "delete from stu where stuId = "+id+";";
int num = stmt.executeUpdate(sql);
if(num>0) {
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
this.closeStmt(stmt, conn);
}
return flag;
}
//更新学生信息方法
public boolean updateStu(int id,Student stu) {
boolean flag = false;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = this.getConn();
stmt = conn.createStatement();
String sql = "update stu set stuNum = "+stu.getStuNum()+", stuName = '"+stu.getStuName()+"',stuSex = '"+stu.getStuSex()+"',stuAge = "+stu.getstuAge()+",stuQQ = '"+stu.getStuQQ()+"' where stuId = "+id+";";
int num = stmt.executeUpdate(sql);
if(num>0) {
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
this.closeStmt(stmt, conn);
}
return flag;
}
//返回所有学生信息总数
public int getCount() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
int num = 0;
try {
conn = this.getConn();
stmt = conn.createStatement();
String sql = "select count(*) from stu where 1=1;";
rs = stmt.executeQuery(sql);
while(rs.next()) {
num = rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
this.closeRes(rs, stmt, conn);
}
return num;
}
}
5.Servlet的实现
通过每次提交增加" flag='各种操作名称' " 的方式来减少servlet的数量
response.setContentType("text/html;charset=utf-8");
response.setCharacterEncoding("utf-8");
request.setCharacterEncoding("utf-8");
PrintWriter out = response.getWriter();
//flag判断是哪个操作，根据flag的值做出不懂的操作
String flag = request.getParameter("flag");
StudentDB db = new StudentDB();
//查询全部学生信息的方法
if(flag.equals(" getAllStu")) {
String page = request.getParameter("page");
String pageSize = request.getParameter("rows");
if(page==null || page.equals("")) {
page = "1";
}
if(pageSize == null || pageSize.equals("")) {
pageSize="10";
}
List<Student> list = db.getAllStu(Integer.parseInt(page), Integer.parseInt(pageSize));
int total = db.getCount();
Map map = new HashMap();
map.put("total", Integer.toString(total));
map.put("rows",list);
JSONObject js = JSONObject.fromObject(map);
System.out.println(js);
out.println(js);
}
//增加学生信息的方法
if(flag.equals(" addStu")) {
String stuNum = request.getParameter("stuNum");
String stuName = request.getParameter("stuName");
String stuSex = request.getParameter("stuSex");
String stuAge = request.getParameter("stuAge");
String stuQQ = request.getParameter("stuQQ");
Student stu = new Student(1,Integer.parseInt(stuNum),stuName,stuSex,Integer.parseInt(stuAge),stuQQ);
boolean f = db.insertStu(stu);
Map map = new HashMap();
if(f) {
map.put("result", "true");
}else {
map.put("msg", "save error");
}
JSONObject js = JSONObject.fromObject(map);
out.println(js);
}
//删除学生信息方法
if(flag.equals("delStu")) {
String id = request.getParameter("id");
boolean f = db.delStu(Integer.parseInt(id));
Map map = new HashMap();
if(f) {
map.put("result", "true");
}else {
map.put("msg", "del error");
}
JSONObject js = JSONObject.fromObject(map);
out.println(js);
}
//更新学生信息方法
if(flag.equals("updateStu")) {
String stuId = request.getParameter("stuId");
String stuNum = request.getParameter("stuNum");
String stuName = request.getParameter("stuName");
String stuSex = request.getParameter("stuSex");
String stuAge = request.getParameter("stuAge");
String stuQQ = request.getParameter("stuQQ");
Student stu = new Student(1,Integer.parseInt(stuNum),stuName,stuSex,Integer.parseInt(stuAge),stuQQ);
boolean f = db.updateStu(Integer.parseInt(stuId), stu);
Map map = new HashMap();
if(f){
map.put("result", "true");
}else{
map.put("msg", "update error");
}
JSONObject js = JSONObject.fromObject(map);
out.println(js);
}
out.flush();
out.close();
}
6.indel.html的实现
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<link rel="stylesheet" type="text/css" href="easyui/themes/default/easyui.css">
<link rel="stylesheet" type="text/css" href="easyui/themes/icon.css">
<script type="text/javascript" src="easyui/jquery.min.js"></script>
<script type="text/javascript" src="easyui/jquery.easyui.min.js"></script>
<!-- 支持中文 -->
<script type="text/javascript" src="easyui/locale/easyui-lang-zh_CN.js"></script>
<title>datagrid2</title>
<script type="text/javascript">
//全局变量，根据是更新还是添加学生信息而变化访问url
var url;
function addStu(){
$("#dlg").dialog({
closed:false
});
url = "stuAction?flag=addStu";
}
function editStu(){
var row = $("#dg").datagrid("getSelected");
if(row){
$("#dlg").dialog({closed:false});
$('#ff').form('load',row);
url="stuAction?flag=updateStu&stuId="+row.stuId;
}else{
$.messager.alert('消息','还未选中要修改的记录！','info');
}
}
function delStu(){
var row = $("#dg").datagrid("getSelected");
if(row){
$.messager.confirm('删除记录','请问确定删除吗？',function(r){
if(r){
$.post(
'stuAction?flag=delStu',
{id:row.stuId},
function(data){
if(data.result){
$("#dg").datagrid("reload");
}
},
"json");
}
});
}else{
$.messager.alert('消息','还未选中要删除的记录！','info');
}
}
function saveStu(){
$('#ff').form('submit', {
//根据是添加还是修改变动的url
url:url,
onSubmit: function(){
//表单验证，返回false会阻止表单提交
},
success:function(data){
var data = eval（"("+data+")");
if(data.result){
$("#dlg").dialog({
closed:true
});
$("#dg").datagrid("reload");
}
}
});
}
</script>
</head>
<body>
<table id="dg" class="easyui-datagrid" style="width:1000px;height:300px"
data-options="url:'stuAction?flag=getAllStu',pagination:true,fitColumns:true,singleSelect:true
,toolbar:'#tb',sortName:'stuNum',sortOrder:'desc'">
<thead>
<tr>
<th data-options="field:'stuId',width:100">编码</th>
<th data-options="field:'stuNum',width:100">学号</th>
<th data-options="field:'stuName',width:100">姓名</th>
<th data-options="field:'stuSex',width:100">性别</th>
<th data-options="field:'stuAge',width:100">年龄</th>
<th data-options="field:'stuQQ',width:100">QQ</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
<div id="tb">
<a class="easyui-linkbutton" data-options="iconCls:'icon-add'" href="#" οnclick="addStu()">新建</a>
<a class="easyui-linkbutton" data-options="iconCls:'icon-edit'" href="#" οnclick="editStu()">编辑</a>
<a class="easyui-linkbutton" data-options="iconCls:'icon-remove'" href="#" οnclick="delStu()">删除</a>
</div>
<div id="dlg" title="添加学员" class="easyui-dialog" data-options="buttons:'#btn',closed:true" style="width:300px;height:200px">
<form id="ff" method="post">
<center>学员信息</center>
学号：<input type="text" name="stuNum"><br/>
姓名：<input type="text" name="stuName"><br/>
性别：<input type="text" name="stuSex"><br/>
年龄：<input type="text" name="stuAge"><br/>
QQ：<input type="text" name="stuQQ"><br/>
</form>
</div>
<div id="btn">
<a class="easyui-linkbutton" data-options="iconCls:'icon-save'" href="#" οnclick="saveStu()">保存</a>
<a class="easyui-linkbutton" data-options="iconCls:'icon-cancel'" href="#" οnclick="javascript:$('#dlg').dialog({closed:true})">取消</a>
</div>
</body>
</html>
