通过Servlet让Extjs GridPanel 显示数据库数据
文章分类:Web前端
GridPanel上显示数据,可以使用Struts,也可以使用Servlet的方法,两者的用法相近,所以,给出后者的实现方法:
这里先列出HTML代码:
- <%@ page language="java" import="java.util.*" pageEncoding="GB18030"%>
- <%
- String path = request.getContextPath();
- String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
- %>
- <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
- <html>
- <head>
- <link rel="stylesheet" type="text/css" href="../resources/css/ext-all.css" />
- <script type="text/javascript" src="../adapter/ext/ext-base.js"></script>
- <script type="text/javascript" src="../js/ext-all-debug.js"></script>
- <link rel="stylesheet" type="text/css" href="../css/grid-examples.css" />
- <link rel="stylesheet" type="text/css" href="../css/examples.css" />
- <link rel="stylesheet" type="text/css" href="css/styles.css" />
- <title>后台登陆页面</title>
- </head>
- <body>
- <script type="text/javascript" src="../css/examples.js"></script>
- <script type="text/javascript">
- Ext.onReady(function(){
- var win;
- var button = Ext.get('show-btn');
- button.on('click', function(){
- // create the window on the first click and reuse on subsequent clicks
- if(!win){
- win = new Ext.Window({
- applyTo:'hello-win',
- layout:'fit',
- width:500,
- height:500,
- closeAction:'hide',
- plain: true,
- items: new Ext.TabPanel({
- applyTo: 'hello-tabs',
- autoTabs:true,
- activeTab:0,
- deferredRender:false,
- border:false,
- items : [ {
- title : '添加操作',
- html : '<iframe width=100% height=100% src=address_add.jsp />'
- } ]
- })
- });
- }
- win.show(this);
- });
- //下面是做列表(原来是通过HttpProxy和Servlet来实现JS与数据库数据的交互)
- var store = new Ext.data.JsonStore({
- root: 'bugs',
- totalProperty: 'totalCount',
- idProperty: 'threadid',
- remoteSort: true,
- fields: ['id','name','sex','mobile','email','qq','company','address','postcode'],
- proxy: new Ext.data.HttpProxy({
- //url:'http://localhost:8080/ExtjsPaging/Bug',
- url:'http://localhost:8080/Jstudio_v1_0029_extjs_jsp/DataServlet',
- method:'GET'
- })
- });
- store.setDefaultSort('id', 'desc');
- var grid = new Ext.grid.GridPanel({
- width:"100%",
- height:550,
- title:'通讯录列表',
- store: store,
- trackMouseOver:false,
- disableSelection:false,
- loadMask: true,
- // grid columns
- columns:[{
- header: "序号",
- dataIndex: 'id',
- width: 70,
- align: 'center',
- // renderer: renderTopic,
- sortable: true
- },{
- header: "姓名",
- dataIndex: 'name',
- width: 100,
- align: 'center',
- //hidden: true,
- sortable: true
- },{
- header: "性别",
- dataIndex: 'sex',
- width: 70,
- align: 'center',
- //sortable: true
- },{
- //id: 'mobile',
- header: "手机号码",
- dataIndex: 'mobile',
- width: 200,
- align: 'center',
- //renderer: renderLast,
- // sortable: true
- },{
- header: "电子邮件",
- dataIndex: 'email',
- width: 200,
- align: 'center',
- // sortable: true
- },{
- header: "QQ号码",
- dataIndex: 'qq',
- width: 200,
- align: 'center',
- // sortable: true
- },{
- header: "公司名称",
- dataIndex: 'company',
- width: 200,
- align: 'center',
- // sortable: true
- },{
- header: "地址",
- dataIndex: 'address',
- width: 300,
- align: 'center',
- // sortable: true
- },{
- header: "邮编",
- dataIndex: 'postcode',
- width: 100,
- align: 'center',
- // sortable: true
- }
- ],
- // customize view config
- viewConfig: {
- forceFit:true,
- enableRowBody:true,
- showPreview:false,
- getRowClass : function(record, rowIndex, p, store){
- if(this.showPreview){
- p.body = '<p>'+record.data.excerpt+'</p>';
- return 'x-grid3-row-expanded';
- }
- return 'x-grid3-row-collapsed';
- }
- },
- // paging bar on the bottom
- bbar: new Ext.PagingToolbar({
- pageSize: 25,
- store: store,
- displayInfo: true,
- displayMsg: 'Displaying topics {0} - {1} of {2}',
- emptyMsg: "No topics to display",
- items:[
- '-', {
- pressed: true,
- enableToggle:true,
- text: 'Show Preview',
- cls: 'x-btn-text-icon details',
- toggleHandler: function(btn, pressed){
- var view = grid.getView();
- view.showPreview = pressed;
- view.refresh();
- }
- }]
- })
- });
- // render it
- grid.render('topic-grid');
- // trigger the data store load
- store.load({params:{start:0, limit:25}});
- });
- </script>
- <input type="button" id="show-btn" value="添加联系人" /><br /><br />
- <div id="hello-win" class="x-hidden">
- <div class="x-window-header">Hello Dialog</div>
- <div id="hello-tabs"></div>
- </div>
- <div id="topic-grid"></div>
- </body>
- </html>
<%@ page language="java" import="java.util.*" pageEncoding="GB18030"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<link rel="stylesheet" type="text/css" href="../resources/css/ext-all.css" />
<script type="text/javascript" src="../adapter/ext/ext-base.js"></script>
<script type="text/javascript" src="../js/ext-all-debug.js"></script>
<link rel="stylesheet" type="text/css" href="../css/grid-examples.css" />
<link rel="stylesheet" type="text/css" href="../css/examples.css" />
<link rel="stylesheet" type="text/css" href="css/styles.css" />
<title>后台登陆页面</title>
</head>
<body>
<script type="text/javascript" src="../css/examples.js"></script>
<script type="text/javascript">
Ext.onReady(function(){
var win;
var button = Ext.get('show-btn');
button.on('click', function(){
// create the window on the first click and reuse on subsequent clicks
if(!win){
win = new Ext.Window({
applyTo:'hello-win',
layout:'fit',
width:500,
height:500,
closeAction:'hide',
plain: true,
items: new Ext.TabPanel({
applyTo: 'hello-tabs',
autoTabs:true,
activeTab:0,
deferredRender:false,
border:false,
items : [ {
title : '添加操作',
html : '<iframe width=100% height=100% src=address_add.jsp />'
} ]
})
});
}
win.show(this);
});
//下面是做列表(原来是通过HttpProxy和Servlet来实现JS与数据库数据的交互)
var store = new Ext.data.JsonStore({
root: 'bugs',
totalProperty: 'totalCount',
idProperty: 'threadid',
remoteSort: true,
fields: ['id','name','sex','mobile','email','qq','company','address','postcode'],
proxy: new Ext.data.HttpProxy({
//url:'http://localhost:8080/ExtjsPaging/Bug',
url:'http://localhost:8080/Jstudio_v1_0029_extjs_jsp/DataServlet',
method:'GET'
})
});
store.setDefaultSort('id', 'desc');
var grid = new Ext.grid.GridPanel({
width:"100%",
height:550,
title:'通讯录列表',
store: store,
trackMouseOver:false,
disableSelection:false,
loadMask: true,
// grid columns
columns:[{
header: "序号",
dataIndex: 'id',
width: 70,
align: 'center',
// renderer: renderTopic,
sortable: true
},{
header: "姓名",
dataIndex: 'name',
width: 100,
align: 'center',
//hidden: true,
sortable: true
},{
header: "性别",
dataIndex: 'sex',
width: 70,
align: 'center',
//sortable: true
},{
//id: 'mobile',
header: "手机号码",
dataIndex: 'mobile',
width: 200,
align: 'center',
//renderer: renderLast,
// sortable: true
},{
header: "电子邮件",
dataIndex: 'email',
width: 200,
align: 'center',
// sortable: true
},{
header: "QQ号码",
dataIndex: 'qq',
width: 200,
align: 'center',
// sortable: true
},{
header: "公司名称",
dataIndex: 'company',
width: 200,
align: 'center',
// sortable: true
},{
header: "地址",
dataIndex: 'address',
width: 300,
align: 'center',
// sortable: true
},{
header: "邮编",
dataIndex: 'postcode',
width: 100,
align: 'center',
// sortable: true
}
],
// customize view config
viewConfig: {
forceFit:true,
enableRowBody:true,
showPreview:false,
getRowClass : function(record, rowIndex, p, store){
if(this.showPreview){
p.body = '<p>'+record.data.excerpt+'</p>';
return 'x-grid3-row-expanded';
}
return 'x-grid3-row-collapsed';
}
},
// paging bar on the bottom
bbar: new Ext.PagingToolbar({
pageSize: 25,
store: store,
displayInfo: true,
displayMsg: 'Displaying topics {0} - {1} of {2}',
emptyMsg: "No topics to display",
items:[
'-', {
pressed: true,
enableToggle:true,
text: 'Show Preview',
cls: 'x-btn-text-icon details',
toggleHandler: function(btn, pressed){
var view = grid.getView();
view.showPreview = pressed;
view.refresh();
}
}]
})
});
// render it
grid.render('topic-grid');
// trigger the data store load
store.load({params:{start:0, limit:25}});
});
</script>
<input type="button" id="show-btn" value="添加联系人" /><br /><br />
<div id="hello-win" class="x-hidden">
<div class="x-window-header">Hello Dialog</div>
<div id="hello-tabs"></div>
</div>
<div id="topic-grid"></div>
</body>
</html>
然后给出实现Servlet类
- package com.lee.servlet;
- import java.io.IOException;
- import java.io.PrintWriter;
- import java.util.Hashtable;
- import java.util.Iterator;
- import java.util.List;
- import javax.servlet.ServletException;
- import javax.servlet.http.HttpServlet;
- import javax.servlet.http.HttpServletRequest;
- import javax.servlet.http.HttpServletResponse;
- import com.lee.dao.ServletDao;
- public class DataServlet extends HttpServlet {
- @Override
- protected void doGet(HttpServletRequest request, HttpServletResponse response)
- throws ServletException, IOException {
- String sql = "select * from address";
- ServletDao sd = new ServletDao();
- List<Hashtable<String, String>> list2 = sd.selectData(sql);
- Iterator<Hashtable<String, String>> it = list2.iterator();
- StringBuilder sb = new StringBuilder();
- sb.append("{totalCount:14,bugs:[");
- while(it.hasNext()) {
- Hashtable<String, String> hash2 = it.next();
- //String name2 = hash2.get("qq");
- sb.append("{");
- sb.append("id:" + hash2.get("id"));
- sb.append(",name:" + "\'" + hash2.get("name") + "\'");
- sb.append(",sex:" + "\'" + hash2.get("sex") + "\'");
- sb.append(",mobile:" + "\'" + hash2.get("mobile") + "\'");
- sb.append(",email:" + "\'" + hash2.get("email") + "\'");
- sb.append(",qq:" + "\'" + hash2.get("qq") + "\'");
- sb.append(",company:" + "\'" + hash2.get("company") + "\'");
- sb.append(",address:" + "\'" + hash2.get("address") + "\'");
- sb.append(",postcode:" + "\'" + hash2.get("postcode") + "\'");
- sb.append("},");
- }
- String json = sb.substring(0, sb.length() - 1);
- json += "]}";
- response.setContentType("text/html");
- response.setCharacterEncoding("UTF-8");
- PrintWriter out = response.getWriter();
- out.println(json);
- out.close();
- }
- @Override
- protected void doPost(HttpServletRequest request, HttpServletResponse response)
- throws ServletException, IOException {
- doGet(request, response);
- }
- }
package com.lee.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.lee.dao.ServletDao;
public class DataServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String sql = "select * from address";
ServletDao sd = new ServletDao();
List<Hashtable<String, String>> list2 = sd.selectData(sql);
Iterator<Hashtable<String, String>> it = list2.iterator();
StringBuilder sb = new StringBuilder();
sb.append("{totalCount:14,bugs:[");
while(it.hasNext()) {
Hashtable<String, String> hash2 = it.next();
//String name2 = hash2.get("qq");
sb.append("{");
sb.append("id:" + hash2.get("id"));
sb.append(",name:" + "\'" + hash2.get("name") + "\'");
sb.append(",sex:" + "\'" + hash2.get("sex") + "\'");
sb.append(",mobile:" + "\'" + hash2.get("mobile") + "\'");
sb.append(",email:" + "\'" + hash2.get("email") + "\'");
sb.append(",qq:" + "\'" + hash2.get("qq") + "\'");
sb.append(",company:" + "\'" + hash2.get("company") + "\'");
sb.append(",address:" + "\'" + hash2.get("address") + "\'");
sb.append(",postcode:" + "\'" + hash2.get("postcode") + "\'");
sb.append("},");
}
String json = sb.substring(0, sb.length() - 1);
json += "]}";
response.setContentType("text/html");
response.setCharacterEncoding("UTF-8");
PrintWriter out = response.getWriter();
out.println(json);
out.close();
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
再是 dao层:(内是相关的测试)
- package com.lee.dao;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.Statement;
- import java.util.ArrayList;
- import java.util.Hashtable;
- import java.util.Iterator;
- import java.util.List;
- import java.sql.ResultSetMetaData;
- public class ServletDao {
- public List selectData(String sql) {
- //List<Object> list = new ArrayList<Object>();
- List<Hashtable<String, String>> list = new ArrayList<Hashtable<String,String>>();
- try {
- Class.forName("com.mysql.jdbc.Driver");
- String url = "jdbc:mysql://localhost:3306/ssh_ext_demo";
- Connection conn = DriverManager.getConnection(url, "root", "123");
- Statement stmt = conn.createStatement();
- ResultSet rs = stmt.executeQuery(sql);
- ResultSetMetaData rsmd = rs.getMetaData();
- while(rs.next()) {
- Hashtable<String, String> hash =new Hashtable<String, String>();
- for(int i =1;i<=rsmd.getColumnCount();i++){
- String field = (String)rsmd.getColumnName(i);
- String value = (String)rs.getString(i);
- if(value == null)
- value="";
- hash.put(field, value);
- }
- list.add(hash);
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- return list;
- }
- public static void main(String[] args) {
- String s = "select * from address";
- ServletDao sd = new ServletDao();
- List<Hashtable<String, String>> list2 = sd.selectData(s);
- Iterator<Hashtable<String, String>> it = list2.iterator();
- StringBuilder sb = new StringBuilder();
- sb.append("{totalCount:14,bugs:[");
- while(it.hasNext()) {
- Hashtable<String, String> hash2 = it.next();
- //String name2 = hash2.get("qq");
- sb.append("{");
- sb.append("id:" + hash2.get("id"));
- sb.append(",name:" + "\'" + hash2.get("name") + "\'");
- sb.append(",sex:" + "\'" + hash2.get("sex") + "\'");
- sb.append(",mobile:" + "\'" + hash2.get("mobile") + "\'");
- sb.append(",email:" + "\'" + hash2.get("email") + "\'");
- sb.append(",qq:" + "\'" + hash2.get("qq") + "\'");
- sb.append(",company:" + "\'" + hash2.get("company") + "\'");
- sb.append(",address:" + "\'" + hash2.get("address") + "\'");
- sb.append(",postcode:" + "\'" + hash2.get("postcode") + "\'");
- sb.append("},");
- }
- String json = sb.substring(0, sb.length() - 1);
- json += "]}";
- System.out.println(json);
- }
- }
package com.lee.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.sql.ResultSetMetaData;
public class ServletDao {
public List selectData(String sql) {
//List<Object> list = new ArrayList<Object>();
List<Hashtable<String, String>> list = new ArrayList<Hashtable<String,String>>();
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jd