easyui04---datagrid&&数据查询

一、datagrid

首先这是一个基本的表格代码

<table id="tt" class="easyui-treegrid" style="width:600px;height:400px"   
        data-options="url:'get_data.php',idField:'id',treeField:'name'">   
    <thead>   
        <tr>   
            <th data-options="field:'name',width:180">Task Name</th>   
            <th data-options="field:'persons',width:60,align:'right'">Persons</th>   
            <th data-options="field:'begin',width:80">Begin Date</th>   
            <th data-options="field:'end',width:80">End Date</th>   
        </tr>   
    </thead>   
</table>  

这是一个基本的对应easyui的JSON格式的表格

{"total":28,"rows":[
	{"productid":"FI-SW-01","productname":"Koi","unitcost":10.00,"status":"P","listprice":36.50,"attr1":"Large","itemid":"EST-1"},
	{"productid":"K9-DL-01","productname":"Dalmation","unitcost":12.00,"status":"P","listprice":18.50,"attr1":"Spotted Adult Female","itemid":"EST-10"},
	{"productid":"RP-SN-01","productname":"Rattlesnake","unitcost":12.00,"status":"P","listprice":38.50,"attr1":"Venomless","itemid":"EST-11"},
	{"productid":"RP-SN-01","productname":"Rattlesnake","unitcost":12.00,"status":"P","listprice":26.50,"attr1":"Rattleless","itemid":"EST-12"},
	{"productid":"RP-LI-02","productname":"Iguana","unitcost":12.00,"status":"P","listprice":35.50,"attr1":"Green Adult","itemid":"EST-13"},
	{"productid":"FL-DSH-01","productname":"Manx","unitcost":12.00,"status":"P","listprice":158.50,"attr1":"Tailless","itemid":"EST-14"},
	{"productid":"FL-DSH-01","productname":"Manx","unitcost":12.00,"status":"P","listprice":83.50,"attr1":"With tail","itemid":"EST-15"},
	{"productid":"FL-DLH-02","productname":"Persian","unitcost":12.00,"status":"P","listprice":23.50,"attr1":"Adult Female","itemid":"EST-16"},
	{"productid":"FL-DLH-02","productname":"Persian","unitcost":12.00,"status":"P","listprice":89.50,"attr1":"Adult Male","itemid":"EST-17"},
	{"productid":"AV-CB-01","productname":"Amazon Parrot","unitcost":92.00,"status":"P","listprice":63.50,"attr1":"Adult Male","itemid":"EST-18"}
]}

但现在这是定死的数据，如果我们想把他这个JSON代码变活，就需要从数据库中拿到对应的值

这边我以图书管理为例子，首先需要一个数据表

create table tb_book (
  bid number not null,
  bname varchar2(50) not null,
  bprice float not null,
  btype varchar2(40) not null,
  primary key(bid)
)

-- 测试数据
insert into TB_BOOK (bid, bname, bprice, btype)
values (1, '西游记', 180, '名著');
insert into TB_BOOK (bid, bname, bprice, btype)
values (2, '红楼梦1', 110.08, '名著');
insert into TB_BOOK (bid, bname, bprice, btype)
values (3, '倚天屠龙记', 150.16, '武侠');
insert into TB_BOOK (bid, bname, bprice, btype)
values (4, '聊斋志异1', 100.12, '玄幻');
insert into TB_BOOK (bid, bname, bprice, btype)
values (5, '永生', 110.11, '玄幻');
insert into TB_BOOK (bid, bname, bprice, btype)
values (6, '武动乾坤', 90.89, '玄幻');
insert into TB_BOOK (bid, bname, bprice, btype)
values (7, '完美世界1', 100, '玄幻');
insert into TB_BOOK (bid, bname, bprice, btype)
values (8, '万域之王', 56.5, '玄幻');
insert into TB_BOOK (bid, bname, bprice, btype)
values (9, '遮天1', 130.9, '玄幻');
insert into TB_BOOK (bid, bname, bprice, btype)
values (10, '凡人修仙传1', 200, '修仙');
insert into TB_BOOK (bid, bname, bprice, btype)
values (11, '倚天屠龙记', 150.16, '武侠');
insert into TB_BOOK (bid, bname, bprice, btype)
values (12, '斗破苍穹', 115.07, '玄幻');
insert into TB_BOOK (bid, bname, bprice, btype)
values (13, '超级兵王1', 145, '言情');
insert into TB_BOOK (bid, bname, bprice, btype)
values (14, '武极天下', 45.55, '玄幻');
insert into TB_BOOK (bid, bname, bprice, btype)
values (15, '聊斋志异', 100.12, '玄幻');
insert into TB_BOOK (bid, bname, bprice, btype)
values (16, '永生1', 110.11, '玄幻');
insert into TB_BOOK (bid, bname, bprice, btype)
values (17, '武动乾坤', 90.89, '玄幻');
insert into TB_BOOK (bid, bname, bprice, btype)
values (18, '完美世界', 100, '玄幻');
insert into TB_BOOK (bid, bname, bprice, btype)
values (19, '万域之王', 56.5, '玄幻');
insert into TB_BOOK (bid, bname, bprice, btype)
values (20, 'Java', 1000, '修仙');
insert into TB_BOOK (bid, bname, bprice, btype)
values (21, '娃哈哈', 100, '玄幻');
insert into TB_BOOK (bid, bname, bprice, btype)
values (22, '呼啸山庄1', 123, '哈哈');
insert into TB_BOOK (bid, bname, bprice, btype)
values (23, '平凡的世界', 123, '哈哈');
insert into TB_BOOK (bid, bname, bprice, btype)
values (24, '大红底1', 12, '哈哈');
insert into TB_BOOK (bid, bname, bprice, btype)
values (25, '屌丝的逆袭1', 34.67, '哈哈');
insert into TB_BOOK (bid, bname, bprice, btype)
values (26, '嗨害嗨1', 22.3, '哈哈');
commit;

这是数据表的脚本，其次这边我们实现一个无刷新的分页，这就需要两个Dao方法了

package com.zking.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.zking.entity.Book;
import com.zking.util.DBHelper;

public class BookDao implements IBookDao {

	//三兄弟
	private Connection con=null;
	private PreparedStatement ps=null;
	private ResultSet rs=null;
	
	@Override
	public List<Book> getAll(String rows, String str, int pageIndex, int pageSize) {
		List<Book> ls=new ArrayList<Book>();
		int a=(pageIndex-1)*pageSize+1;
		int b=pageIndex*pageSize;
		try {
			con=DBHelper.getCon();
			String sql="select * from (select a.*,rownum rid from tb_book a where "+rows+" like '%"+str+"%' )b where b.rid between ? and ? ";
			ps=con.prepareStatement(sql);
			ps.setInt(1, a);
			ps.setInt(2, b);
			rs=ps.executeQuery();
			while(rs.next()) {
				Book bb=new Book(rs.getInt(1), rs.getString(2), rs.getDouble(3), rs.getString(4));
				ls.add(bb);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBHelper.myClose(con, ps, rs);
		}
		return ls;
	}

	@Override
	public int getMax(String str) {
		int n=0;
		try {
			con=DBHelper.getCon();
			String sql="select count(*) from "+str;
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()) {
				n=rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBHelper.myClose(con, ps, rs);
		}
		return n;
	}

}

一个是拿到对象集合也就可以对应我们上面基本JSON格式的rows,还有一个结束拿到总行数，对应JSON格式的total，写完方法后我们就可以去搭建前台页面了

二、数据查询 

在body里面写入

<table id="dg"></table>  

在script里面写入我们导入数据的方法

$('#dg').datagrid({    
    url:'datagrid_data.json',    
    columns:[[    
        {field:'code',title:'代码',width:100},    
        {field:'name',title:'名称',width:100},    
        {field:'price',title:'价格',width:100,align:'right'}    
    ]]    
});  

这里要注意的是id一定要对上，其次就是field后的属性名需要跟实力类保持一致

这里我们为了后期工作，所以我们给表格设置一些限制

            fitColumns:true,//让其自适应宽度
		    pagination:true,//显示分页工具
		    toolbar:'#myTools',//记载工具栏
		    singleSelect:true,//只允许选择一行
		    pageList:[5,10,15,20,30,40],//设置页码数
		    pageSize:5,//设置默认页码数
		    loadMsg:"正在加载中.....",

这些代码写入URL下面，这里的URL路径要放我们等下从数据库拿到数据的servlet的路径

写完这些之后我们就可以写模糊查询的一些下拉框，文本框

<div style="margin:20px;text-align: center;">
		<!-- 模糊查询 -->
		<!-- 下拉框 -->
		<select class="easyui-combobox" id="dept" style="width:200px;">   
    		<option value="bname">名称</option>    
    		<option value="btype">类型</option>   
		</select>  
		<!-- 文本框 -->
		<input class="easyui-textbox" id="str" style="width:300px"> 
		<a id="btn"	 class="easyui-linkbutton" data-options="iconCls:'icon-search'">查看</a>
	</div>
	

然后我们需要给查询按钮添加点击事件，写一个方法把我们想要传的值传入过去

//给查询添加点击事件
		$("#btn").click(function() {
			$('#myTab').datagrid('load',{
				//传值
				name:$("#dept").val(),
				str:$("#str").val()
			});
		})

接着我们就差一个servlet的邦值界面

package com.zking.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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;
import com.zking.biz.BookBiz;
import com.zking.biz.IBookBiz;
import com.zking.entity.Book;

@WebServlet("/BookServlet")
public class BookServlet extends HttpServlet {
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//三个编码方式
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("utf-8");
		response.setContentType("text/html; charset=UTF-8");
				
		//拿到out
		PrintWriter out = response.getWriter();
		
		int pageIndex=1;
		int pageSize=10;
		
		//接收前台传过来的参数 page rows name str
		String pid=request.getParameter("page");
		if(pid!=null) {
			pageIndex=Integer.parseInt(pid);
		}
		String size=request.getParameter("rows");
		if(pid!=null) {
			pageSize=Integer.parseInt(size);
		}
		//下拉框
		String bname=request.getParameter("name");
		if(bname==null) {
			bname="bname";
		}
		//文本框
		String str=request.getParameter("str");
		if(str==null) {
			str="";
		}
				
		//调用biz层 
		IBookBiz ibb=new BookBiz();
		//拿到总行数
		int zhs = ibb.getMax(" tb_book where '"+bname+"' like '%"+str+"%' ");
		
		List<Book> ls = ibb.getAll(bname,str, pageIndex, pageSize);
		//拿到对象集合
		
		//map集合
		Map<String, Object> m=new HashMap<>();
		m.put("total", zhs);
		m.put("rows", ls);
		
		String mm = JSON.toJSONString(m);
		
		out.write(mm);
		out.flush();
		out.close();
		
		
	}

}

这里的servlet的路径对应上后记得前台的url路径也要发生改变，否则就出不来效果