文档文库

手机版

投诉建议

首页 > jqGrid连接数据库

jqGrid连接数据库

发布时间：2015-05-13 12:57:58 来源：文档文库

小中大

字号：

手机查看

JqGrid学习笔记

1、jqGrid 用来显示网格数据的jQuery插件，通过使用jqGrid可以实现前端页面与后台数据的ajax异步通信。

2、准备文件

在项目的目录下建立相应的文件夹（js和css文件）

3、Css引入文件：

4、Js引入文件：

5、js和body中的代码：

$(function() {

pageInit();

});

//查询函数

function gridReload() {

var nm_mask = jQuery("#item_nm").val() || "";

var cd_mask = jQuery("#search_cd").val() || "";

jQuery("#list9").jqGrid(

'setGridParam',

{

url : "/jqgrid7/JSONData?nm_mask=" + nm_mask + "&cd_mask="

+ cd_mask,

page : 1

}).trigger("reloadGrid");

}

function pageInit() {

jQuery("#list9").jqGrid(

{

url : '/jqgrid7/JSONData?nd=' + new Date().getTime(),

datatype : "json",

colNames : [ 'InvNo', 'Client', 'Date', 'Amount', 'Tax',

'Total', 'Notes' ],

colModel : [

{name : 'InvNo',index : 'InvNo',width : 55},

{name : 'Client',index : 'Client',width : 100},

{name : 'Date',index : 'Date',width : 90},

{name : 'Amount',index : 'Amount',width : 80,align : "right"},

{name : 'tax',index : 'tax',width : 80,align : "right"},

{name : 'Total',index : 'Total',width : 80,align : "right"},

{name : 'Notes',index : 'Notes',width : 150,sortable : false

} ],

rowNum : 8,

rowList : [ 5, 10, 20, 30 ],

pager : '#pager9',

sortname : 'Amount',

recordpos : 'left',

viewrecords : true,

sortorder : "desc",

multiselect :true,

caption : "Multi Select Example",

});

jQuery("#m1").click(function() {

var s;

s = jQuery("#list9").jqGrid('getGridParam', 'selarrow');

alert(s);

});

jQuery("#m1s").click(function() {

jQuery("#list9").jqGrid('setSelection', "13");

});

}

script>

head>

<body>

This is my JSP page.

<br>

<div>

Inv No<br /> <input type="text" id="search_cd" />

div>

<div>

Name<br> <input type="text" id="item_nm"

/>

<button onclick="gridReload()" id="submitButton"

style="margin-left: 20px; color:blue;" />

查找

button>

div>

.........

<br />

<table id="list9">table>

<br />

<a href="javascript:void(0)" id="m1">Get Selected id'sa>

<br />

<a href="javascript:void(0)" id="m1s">Select(Unselect) row 13a>

···代码省略···

body>

6、连接数据库并且经过sql语句过滤进行分页查询后台代码：

package web;

import java.io.IOException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

/**

* Servlet implementation class JSONData

*/

public class JSONData extends HttpServlet {

private static final long serialVersionUID = 1L;

/**

* @see HttpServlet#HttpServlet()

*/

public JSONData() {

super();

}

@Override

protected void doGet(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

doPost(req,resp);

}

/**

* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)

*/

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

String page=request.getParameter("page");//当前页数

String rows=request.getParameter("rows");//取得每页显示的行数

String cd_mask = request.getParameter("cd_mask") ;

String nm_mask = request.getParameter("nm_mask") ;

String driver="com.mysql.jdbc.Driver";

String url="jdbc:mysql://localhost:3306/jd1109db2";

String user="root";

String password="root";

String sql=null;

String sql2=null;

Connection conn=null;

Statement statement=null;

ResultSet rs=null;

ResultSet rs2=null;

System.out.println(cd_mask+"__"+nm_mask);

try

{

int sum=0;

Class.forName(driver);

conn = DriverManager.getConnection(url,

user,password);

statement = conn.createStatement();

int pages = Integer.parseInt(page);

int rows2 = Integer.parseInt(rows);

int k=(pages-1)*rows2;

if(cd_mask!=null && !"".equals(cd_mask) && nm_mask!=null && !"".equals(nm_mask)){

sql = "select * from shafei2 where InvNo='" +

cd_mask +

"' and Client='" +

nm_mask +

"' limit "+k+"," +rows2;

sql2="select count(*) as sum from shafei2 where InvNo='" +

cd_mask +

"' and Client='" +

nm_mask +

"' limit "+k+"," +rows2;

}else if(cd_mask!=null && !"".equals(cd_mask))

{

sql="select * from shafei2 where InvNo='"+cd_mask+"' limit "+k+"," +rows2;

sql2="select count(*) as sum from shafei2 where InvNo='"+cd_mask+"' limit "+k+"," +rows2;

}else if(nm_mask!=null && !"".equals(nm_mask))

{

sql="select * from shafei2 where Client='"+nm_mask+"' limit "+k+"," +rows2;

sql2="select count(*) as sum from shafei2 where Client='"+nm_mask+"' limit "+k+"," +rows2;

}else

{

sql = "select * from shafei2 limit "+k+"," +rows2;

sql2="select count(*) as sum from shafei2 limit "+k+"," +rows2;

}

System.out.println(sql);

rs=statement.executeQuery(sql);

if(!conn.isClosed())

{

System.out.println("Succeeded connecting to the Database!");

}

String InvNo=null;

String Date=null;

String Client=null;

String Amount=null;

String Total=null;

String Notes=null;

String tax=null;

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);

ResultSet rset=stmt.executeQuery(sql2);

int totalRecord=0;

while(rset.next())

{

totalRecord=rset.getInt("sum");

}

System.out.println("一共有"+totalRecord+"条记录,"+"每页显示"+rows+"行！");

int totalPage = totalRecord%Integer.parseInt(rows) == 0 ?

totalRecord/Integer.parseInt(rows) : totalRecord/Integer.parseInt(rows)+1; // 计算总页数

String json1 = "{\"total\":"+totalPage+",\"page\": "+page+", \"records\": "+totalRecord+",\"rows\": [";

String json="";

int i=1;

while(rs.next())

{

InvNo = rs.getString("InvNo");

Date = rs.getString("Date");

Client = rs.getString("Client");

Amount = rs.getString("Amount");

tax = rs.getString("tax");

Total = rs.getString("Total");

Notes = rs.getString("Notes");

try {

int index = (Integer.parseInt(page)-1)*Integer.parseInt(rows); //开始记录数

int pageSize = Integer.parseInt(rows);

//System.out.println(json);

if(i!=1){

json +=",{\"InvNo\":\""+InvNo+"\",\"Date\":\""+Date+"\",\"Client\":\""+Client+"\",\"Amount\":\""+Amount+"\",\"tax\":\""+tax+"\",\"Total\":\""+Total+"\",\"Notes\":\""+Notes+"\"}";

}else{

json ="{\"InvNo\":\""+InvNo+"\",\"Date\":\""+Date+"\",\"Client\":\""+Client+"\",\"Amount\":\""+Amount+"\",\"tax\":\""+tax+"\",\"Total\":\""+Total+"\",\"Notes\":\""+Notes+"\"}";

}

i++;

}//tr y结束

catch (Exception ex)

{

System.out.println("Exception In getWriter:"+ex.getMessage());

}

}//while(rs.next());

json1 =json1+json+"]}";

//System.out.println("第一页:");

System.out.println(json1);

response.getWriter().write(json1); // 将JSON数据返回页面