jqGrid连接数据库

JqGrid学习笔记

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

2、准备文件

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

3、Css引入文件：

href="css/jqueryUI.css">

href="css/ui.jqgrid.css">

4、Js引入文件：

5、js和body中的代码：

This is my JSP page.

查找:

Inv No

Name

/>

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

.........

Get Selected

id's

Select(Unselect) row 13

···代码省略···

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.CONC

UR_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\":\""+Cli

ent+"\",\"Amount\":\""+Amount+"\",\"tax\":\""+tax+"\",\"Total\":\""+T otal+"\",\"Notes\":\""+Notes+"\"}";

}else{

json

="{\"InvNo\":\""+InvNo+"\",\"Date\":\""+Date+"\",\"Client\":\""+Clien t+"\",\"Amount\":\""+Amount+"\",\"tax\":\""+tax+"\",\"Total\":\""+Tot al+"\",\"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数据返回页面

}//try结束

catch(Exception ex)

{

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

}

}

}