EASYUI之DataGrid的使用（java）

首先建立了一个html文件，这里对EASYUI使用的是JS加载方式，这里我把JS的代码放到单独的一个文件夹中，也可以把JS的代码和html放在一起。下面是JS文件中的代码。

这里我用Servlet来处理数据，也可以用struts2更为方便。

$(function(){
$("#box").datagrid({
title : "用户列表",
width : 850,
striped: true,
//height: 200,
autoRowHeight : true,
rownumbers : true,
collapsible : true,
pageNumber : 1,
pagination : true,
pageSize : 5,
pageList : [ 5, 10, 15, 20 ],
loadMsg: "信息加载中",
url : "servlet/SearchServlet",
fitColumns : false,
columns :  [[
{field : 'EMPNO',title : '员工号', width: 100, align: 'center'},
{field : 'ENAME',title : '姓名', width: 100, align: 'center'},
{field : 'JOB',title : '职务', width: 100, align: 'center'},
{field : 'MGR',title : '经理', width: 100, align: 'center'},
{field : 'HIREDATE',title : '日期', width: 120, align: 'center'},
{field : 'SAL',title : '薪水', width: 100, align: 'center'},
{field : 'COMM',title : '日用', width: 100, align: 'center'},
{field : 'DEPTNO',title : '公寓号', width: 100, align: 'center'}
]],

});

});

下面是连接数据库的代码，后面会反复用到。

package com.DB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;


public class DB {

public static Connection getConnection(){
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@202.113.76.131:1521:orcl", "scott", "zhengD3064301");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}

public static void close(PreparedStatement pstmt){
if(pstmt != null){
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

public static void close(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

public static void close(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

}

由于DataGrid是用JSON来传递数据的，就需要设置特殊的类来完成JSON的转换。一个是DBRecord ，一个是DBTable 如下：

package com.DB;


import java.io.Serializable;
import java.util.HashMap;


public class DBRecord extends HashMap<String, String> implements Serializable {
private static final long serialVersionUID = 1L;


public DBRecord(){
super();
}
}

package com.DB;


import java.io.Serializable;
import java.util.ArrayList;


public class DBTable extends ArrayList<DBRecord> implements Serializable {


private static final long serialVersionUID = 1L;


public DBTable(){
super();
}
}

现在就把前期的准备工作做完了，开始写Servlet来处理数据rows和page是固定的参数，这里会用到方法getAllData(strRows, strPage);getDataSetInfoByCon(strSql, rowCount, page);getRowCount(String strSql);

public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String result = "";
String strRows = request.getParameter("rows");
String strPage = request.getParameter("page");
if(strRows == null){
strRows = "0";
}
if(strPage == null){
strPage = "0";
}
result = BaseDao.getInstance().getAllData(strRows, strPage);
response.setCharacterEncoding("UTF-8");
PrintWriter out = response.getWriter();
out.write(result);
out.flush();
out.close();
    }

public String getAllData(String strRows,String strPage){
int rowCount = 0;
int page = 0;
String result;
DBTable data = new DBTable();
Map<String, Object> mapresult = new HashMap<String, Object>();
String strSql = "select * from EMP order by empno";
if(strRows != ""){
rowCount = Integer.parseInt(strRows);
}
if(strPage != ""){
page = Integer.parseInt(strPage);
}
data = getDataSetInfoByCon(strSql, rowCount, page);
rowCount = getRowCount("select * from EMP");
mapresult.put("total", rowCount);
mapresult.put("rows",data);
JSONObject jo = JSONObject.fromObject(mapresult);
result = jo.toString();

return result;
}

public DBTable getDataSetInfoByCon(String strSql , int rowCount , int page){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
ResultSetMetaData rsmd = null;

DBTable dbt = new DBTable();
int columnCount = 0;
try {
conn = DB.getConnection();
pstmt = conn.prepareStatement(strSql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs = pstmt.executeQuery();
rsmd = rs.getMetaData();
columnCount = rsmd.getColumnCount();
if(rowCount > 0){
pstmt.setMaxRows(page * rowCount);
}
if(rowCount > 0 && page > 1){
rs.absolute((page - 1) * rowCount);
}
while(rs.next()){
DBRecord dbr = new DBRecord();
for(int i = 1; i <= columnCount; i++){
dbr.put(rsmd.getColumnName(i), rs.getString(i));
}
dbt.add(dbr);
}
DB.close(rs);
DB.close(pstmt);
DB.close(conn);
} catch (SQLException e) {
e.printStackTrace();
}
return dbt;
}

public int getRowCount(String strSql){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
int count = 0;
try {
conn = DB.getConnection();
pstmt = conn.prepareStatement(strSql);
rs = pstmt.executeQuery();
/*rs.last();
count = rs.getRow();*/
while(rs.next()){
count ++;
}
DB.close(rs);
DB.close(pstmt);
DB.close(conn);
} catch (SQLException e) {
e.printStackTrace();
}

return count;
}