在本教程中，我们从 Derby 数据库中检索数据并将其显示在 jsGrid 组件中。数据以 JSON 格式从 Derby 发送到 jsGrid。本教程的源代码可在作者 Github?存储库中找到。
jsGrid是一个基于 jQuery 的轻量级客户端数据网格控件。它支持基本的网格操作，如插入、编辑、过滤、删除、排序和分页。该jsGrid组件允许自定义其外观及其子组件。
jQuery是一个快速、小型且功能丰富的 JavaScript 库。它使 HTML 文档的遍历和操作、事件处理、动画和 Ajax 变得更加简单，其易于使用的 API 可以跨多种浏览器工作。
Apache Derby是一个完全用 Java 实现的开源关系数据库。Derby 占地面积小，易于部署和安装。它有两种模式：嵌入式和客户端/服务器。它也称为 Java DB。
JSON（JavaScript Object Notation）是一种轻量级的数据交换格式。人类很容易读写，机器也很容易解析和生成。JSON 的官方 Internet 媒体类型是application/json.?JSON 文件扩展名为.json.
在我们的应用程序中，我们将使用以下四种 HTTP 方法：
- GET — 读取资源
- POST — 创建一个新资源
- PUT — 修改资源
- DELETE — 删除资源
这些 HTTP 动词将调用相应的 Java servlet 方法。
cars.sql
CREATE TABLE CARS(ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1), NAME VARCHAR(30), PRICE INT);
INSERT INTO CARS(NAME, PRICE) VALUES('Audi', 52642);
INSERT INTO CARS(NAME, PRICE) VALUES('Mercedes', 57127);
INSERT INTO CARS(NAME, PRICE) VALUES('Skoda', 9000);
INSERT INTO CARS(NAME, PRICE) VALUES('Volvo', 29000);
INSERT INTO CARS(NAME, PRICE) VALUES('Bentley', 350000);
INSERT INTO CARS(NAME, PRICE) VALUES('Citroen', 21000);
INSERT INTO CARS(NAME, PRICE) VALUES('Hummer', 41400);
INSERT INTO CARS(NAME, PRICE) VALUES('Volkswagen', 21600);在示例中，我们使用名称为testdb的Derby数据库中的CARS数据库表。
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.example</groupId>
<artifactId>JsGridEx</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>war</packaging>
<name>JsGridEx</name>
<properties>
<endorsed.dir>${project.build.directory}/endorsed</endorsed.dir>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>javax</groupId>
<artifactId>javaee-web-api</artifactId>
<version>7.0</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.apache.derby</groupId>
<artifactId>derbyclient</artifactId>
<version>10.12.1.1</version>
</dependency>
<dependency>
<groupId>com.googlecode.json-simple</groupId>
<artifactId>json-simple</artifactId>
<version>1.1.1</version>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>19.0</version>
</dependency>
<dependency>
<groupId>org.apache.derby</groupId>
<artifactId>derbyoptionaltools</artifactId>
<version>10.12.1.1</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.1</version>
<configuration>
<source>1.7</source>
<target>1.7</target>
<compilerArguments>
<endorseddirs>${endorsed.dir}</endorseddirs>
</compilerArguments>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-war-plugin</artifactId>
<version>2.3</version>
<configuration>
<failOnMissingWebXml>false</failOnMissingWebXml>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-dependency-plugin</artifactId>
<version>2.6</version>
<executions>
<execution>
<phase>validate</phase>
<goals>
<goal>copy</goal>
</goals>
<configuration>
<outputDirectory>${endorsed.dir}</outputDirectory>
<silent>true</silent>
<artifactItems>
<artifactItem>
<groupId>javax</groupId>
<artifactId>javaee-endorsed-api</artifactId>
<version>7.0</version>
<type>jar</type>
</artifactItem>
</artifactItems>
</configuration>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
这些是我们项目中使用的依赖项。这javaee-web-api?是一组用于创建 Java Web 应用程序的 JAR。这derbyclient?是 Derby 的数据库驱动程序。该json-simple库用于处理 JSON 数据。该guava项目中使用了 的辅助方法。derbyoptionaltools包含一个将数据库结果集转换为 JSON 格式的辅助方法?。
index.html
<!DOCTYPE html>
<html>
<head>
<title>jsGrid example</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta name="viewport" content="width=device-width">
<link href="css/style.css" rel="stylesheet">
<link href="http://js-grid.com/css/jsgrid.min.css" rel="stylesheet">
<link href="http://js-grid.com/css/jsgrid-theme.min.css" rel="stylesheet">
</head>
<body>
<div id="jsGrid"></div>
<script src="http://code.jquery.com/jquery-1.11.3.min.js"></script>
<script src="http://js-grid.com/js/jsgrid.min.js"></script>
<script src="js/sample.js"></script>
</body>
</html>在index.html文件中，我们包含jQuery和jsGrid库。
<div id="jsGrid"></div>
jsGrid包含在 div 标签中?。
style.css
html {
height: 100%;
}
body {
height: 100%;
font-family: Verdana, Georgia;
}为了以完整尺寸显示 jsGrid 组件，我们需要设置<body>and<html>标记的高度。
sample.js
$(function () {
$.ajax({
type: "GET",
url: "/JsGridEx/ManageCars"
}).done(function () {
$("#jsGrid").jsGrid({
height: "60%",
width: "50%",
inserting: true,
editing: true,
sorting: true,
paging: true,
autoload: true,
pageSize: 10,
controller: {
loadData: function (filter) {
return $.ajax({
type: "GET",
url: "/JsGridEx/ManageCars",
data: filter
});
},
insertItem: function (item) {
return $.ajax({
type: "POST",
url: "/JsGridEx/ManageCars",
data: item
});
},
updateItem: function (item) {
return $.ajax({
type: "PUT",
url: "/JsGridEx/ManageCars",
data: item
});
},
deleteItem: function (item) {
return $.ajax({
type: "DELETE",
url: "/JsGridEx/ManageCars",
data: item
});
}
},
fields: [
{name: "NAME", title: "Name", type: "text", width: 60},
{name: "PRICE", title: "Price", type: "text", width: 50},
{type: "control"}
]
});
});
});在sample.js文件中，我们创建和配置jsGrid组件。
inserting: true,
editing: true,
sorting: true,
paging: true,我们的jsGrid组件可以插入、编辑和排序数据，还支持分页。
loadData: function (filter) {
return $.ajax({
type: "GET",
url: "/JsGridEx/ManageCars",
data: filter
});
}该函数在servlet?loadData()上发出一个 HTTP GET 方法 。ManageCars
insertItem: function (item) {
return $.ajax({
type: "POST",
url: "/JsGridEx/ManageCars",
data: item
});
}ManageCars当我们插入一个新项目时，会在servlet?上发出一个 HTTP POST 方法。
updateItem: function (item) {
return $.ajax({
type: "PUT",
url: "/JsGridEx/ManageCars",
data: item
});
}ManageCars更新项目会在servlet?上生成 HTTP PUT 方法 。
deleteItem: function (item) {
return $.ajax({
type: "DELETE",
url: "/JsGridEx/ManageCars",
data: item
});
}ManageCars删除项目会在servlet?上生成 HTTP DELETE 方法 。
fields: [
{name: "NAME", title: "Name", type: "text", width: 60},
{name: "PRICE", title: "Price", type: "text", width: 50},
{type: "control"}
]该jsGrid组件具有三个两列：NAME 和 PRICE。它们必须与 JSON 中返回的键完全匹配。
com/zetcode/ManageCars.java
package com.zetcode.web;
import com.zetcode.service.CarService;
import com.zetcode.util.Utils;
import java.io.IOException;
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 org.json.simple.JSONArray;
@WebServlet(name = "ManageCars", urlPatterns = {"/ManageCars"})
public class ManageCars extends HttpServlet {
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("application/json");
response.setCharacterEncoding("UTF-8");
JSONArray ar = CarService.getCarsJSON();
response.getWriter().write(ar.toJSONString());
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException {
String name = request.getParameter("NAME");
int price = Integer.valueOf(request.getParameter("PRICE"));
CarService.insertCar(name, price);
getServletContext().log("Car " + name + " inserted");
}
@Override
protected void doPut(HttpServletRequest request, HttpServletResponse response)
throws ServletException {
Map<String, String> dataMap = Utils.getParameterMap(request);
String carName = dataMap.get("NAME");
int carPrice = Integer.valueOf(dataMap.get("PRICE"));
CarService.updateCar(carName, carPrice);
getServletContext().log("Car " + carName + " updated" + carPrice);
}
@Override
protected void doDelete(HttpServletRequest request, HttpServletResponse response)
throws ServletException {
Map<String, String> dataMap = Utils.getParameterMap(request);
String carName = dataMap.get("NAME");
CarService.deleteCar(carName);
getServletContext().log("Car:" + carName + " deleted");
}
}
这ManageCars是一个Java servlet，它包含HTTP GET、POST、PUT 和DELETE 方法的相应方法。
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("application/json");
response.setCharacterEncoding("UTF-8");
JSONArray ar = CarService.getCarsJSON();
response.getWriter().write(ar.toJSONString());
}该doGet()方法被调用以响应 HTTP GET 方法。它调用该CarService's?getCarsJSON()方法，该方法返回 CARS 表中的所有汽车。数据以 JSON 格式发送回客户端。
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException {
String name = request.getParameter("NAME");
int price = Integer.valueOf(request.getParameter("PRICE"));
CarService.insertCar(name, price);
getServletContext().log("Car " + name + " inserted");
}在该doPost()方法中，我们从请求中检索 NAME 和 PRICE 参数，并使用该方法将它们插入到数据库中CarService.insertCar()?。当doPost()我们收到一个 HTTP POST 方法时调用该方法，期望添加一个新资源。
@Override
protected void doPut(HttpServletRequest request, HttpServletResponse response)
throws ServletException {
Map<String, String> dataMap = Utils.getParameterMap(request);
String carName = dataMap.get("NAME");
int carPrice = Integer.valueOf(dataMap.get("PRICE"));
CarService.updateCar(carName, carPrice);
getServletContext().log("Car " + carName + " updated" + carPrice);
}在该doPut()方法中，我们从请求中检索 NAME 和 PRICE 参数，并使用该方法将它们插入到数据库中CarService.updateCar()?。当doPut()我们接收到 HTTP PUT 方法时调用该方法，期望修改资源。
@Override
protected void doDelete(HttpServletRequest request, HttpServletResponse response)
throws ServletException {
Map<String, String> dataMap = Utils.getParameterMap(request);
String carName = dataMap.get("NAME");
CarService.deleteCar(carName);
getServletContext().log("Car:" + carName + " deleted");
}在该doDelete()方法中，我们从请求中检索 NAME 参数并使用该方法删除汽车CarService.deleteCar()?。当doDelete()我们收到一个 HTTP DELETE 方法时调用该方法，期望删除一个资源。
com/zetcode/CarService.java
package com.zetcode.service;
import com.zetcode.util.Utils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.DataSource;
import org.apache.derby.optional.api.SimpleJsonUtils;
import org.json.simple.JSONArray;
public class CarService {
private static final Logger LOG = Logger.getLogger(CarService.class.getName());
private static JSONArray jarray;
public static void updateCar(String name, int price) {
Connection con = null;
PreparedStatement pst = null;
try {
DataSource ds = Utils.getDataSource();
con = ds.getConnection();
pst = con.prepareStatement("UPDATE CARS SET NAME=?, PRICE=? WHERE NAME=?");
pst.setString(1, name);
pst.setInt(2, price);
pst.setString(3, name);
pst.executeUpdate();
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(CarService.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
} finally {
try {
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
LOG.log(Level.WARNING, ex.getMessage(), ex);
}
}
}
public static void deleteCar(String name) {
Connection con = null;
PreparedStatement pst = null;
try {
DataSource ds = Utils.getDataSource();
con = ds.getConnection();
pst = con.prepareStatement("DELETE FROM CARS WHERE Name=?");
pst.setString(1, name);
pst.executeUpdate();
} catch (SQLException ex) {
LOG.log(Level.SEVERE, ex.getMessage(), ex);
} finally {
try {
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
LOG.log(Level.WARNING, ex.getMessage(), ex);
}
}
}
public static void insertCar(String name, int price) {
Connection con = null;
PreparedStatement pst = null;
try {
DataSource ds = Utils.getDataSource();
con = ds.getConnection();
pst = con.prepareStatement("INSERT INTO CARS(NAME, PRICE) "
+ "VALUES(?, ?)");
pst.setString(1, name);
pst.setInt(2, price);
pst.executeUpdate();
} catch (SQLException ex) {
LOG.log(Level.SEVERE, ex.getMessage(), ex);
} finally {
try {
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
LOG.log(Level.WARNING, ex.getMessage(), ex);
}
}
}
public static JSONArray getCarsJSON() {
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
DataSource ds = Utils.getDataSource();
con = ds.getConnection();
pst = con.prepareStatement("SELECT NAME, PRICE FROM Cars");
rs = pst.executeQuery();
jarray = SimpleJsonUtils.toJSON(rs);
} catch (SQLException ex) {
LOG.log(Level.SEVERE, ex.getMessage(), ex);
} finally {
try {
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
LOG.log(Level.WARNING, ex.getMessage(), ex);
}
}
return jarray;
}
}
CarService包含用于数据检索和修改?的方法。我们使用标准的 JDBC 代码。Java 数据库连接 (JDBC) 是 Java 编程语言的应用程序编程接口 (API)，它定义了客户端如何访问数据库。
DataSource ds = Utils.getDataSource();
con = ds.getConnection();
pst = con.prepareStatement("DELETE FROM CARS WHERE Name=?");
pst.setString(1, name);
pst.executeUpdate();在这里，我们创建一个数据源并建立一个到 Derby 数据库的新连接。我们执行 DELETE SQL 语句。
DataSource ds = Utils.getDataSource();
con = ds.getConnection();
pst = con.prepareStatement("INSERT INTO CARS(NAME, PRICE) "
+ "VALUES(?, ?)");
pst.setString(1, name);
pst.setInt(2, price);
pst.executeUpdate();这是用于插入新车的 JDBC 代码。
DataSource ds = Utils.getDataSource();
con = ds.getConnection();
pst = con.prepareStatement("SELECT NAME, PRICE FROM Cars");
rs = pst.executeQuery();
jarray = SimpleJsonUtils.toJSON(rs);在该getCarsJSON()方法中，我们得到一个结果集并使用SimpleJsonUtils.toJSON().?该方法是derbyoptionaltools.
com/zetcode/Utils.java
package com.zetcode.util;
import com.google.common.base.Splitter;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.http.HttpServletRequest;
import javax.sql.DataSource;
import org.apache.derby.jdbc.ClientDataSource;
public class Utils {
public static DataSource getDataSource() {
ClientDataSource ds = new ClientDataSource();
ds.setDatabaseName("testdb");
ds.setUser("app");
ds.setPassword("app");
ds.setServerName("localhost");
ds.setPortNumber(1527);
return ds;
}
public static Map<String, String> getParameterMap(HttpServletRequest request) {
BufferedReader br = null;
Map<String, String> dataMap = null;
try {
InputStreamReader reader = new InputStreamReader(
request.getInputStream());
br = new BufferedReader(reader);
String data = br.readLine();
dataMap = Splitter.on('&')
.trimResults()
.withKeyValueSeparator(
Splitter.on('=')
.limit(2)
.trimResults())
.split(data);
return dataMap;
} catch (IOException ex) {
Logger.getLogger(Utils.class.getName()).log(Level.SEVERE, null, ex);
} finally {
if (br != null) {
try {
br.close();
} catch (IOException ex) {
Logger.getLogger(Utils.class.getName()).log(Level.WARNING, null, ex);
}
}
}
return dataMap;
}
}
是Utils一个辅助类，它包含两个方法：getDataSource()?和getParameterMap()。
public static DataSource getDataSource() {
ClientDataSource ds = new ClientDataSource();
ds.setDatabaseName("testdb");
ds.setUser("app");
ds.setPassword("app");
ds.setServerName("localhost");
ds.setPortNumber(1527);
return ds;
}创建并getDataSource()返回一个 Derby 数据源。
InputStreamReader reader = new InputStreamReader(
request.getInputStream());
br = new BufferedReader(reader);
String data = br.readLine();
dataMap = Splitter.on('&')
.trimResults()
.withKeyValueSeparator(
Splitter.on('=')
.limit(2)
.trimResults())
.split(data);
return dataMap;与doGet()和doPost()方法不同doPut()， 和doDelete()不能用该?getParameter()方法检索请求参数。我们必须从流中获取它们。为此，我们使用 Guava 的Splitter类。参数被解析并在映射中返回。
图：jsGrid 组件
在本教程中，我们使用了 jsGrid 组件。我们已经用从 Derby 数据库中检索到的数据填充了组件。数据以 JSON 格式从数据库发送。
?
