众所周知,SpreadJS是前端控件,对后台的语言没有限定。那么SpreadJS是如何与Java后台进行数据交互的呢,让我们来看一下示例:
本示例完成了一个简单的数据库工具,可以对数据库中所有的表进行增删改查以及导出excel操作。
其中添加与删除须在完成操作后点击保存按钮,支持批量添加与删除。
修改数据在操作完成后进行实时更新。
导出excel使用SpreadJS的前端导出,目前仅支持xlsx格式。
首先,进行环境准备工作:
一,搭建mysql数据库:
mysql下载地址:https://dev.mysql.com/downloads/mysql/
mysql安装步骤:
1.解压安装包
2.配置环境变量
3.修改安装包中的my-default.ini文件
basedir=(mysql所在目录)
datadir=(mysql所在目录\data)
4.这里要说一下,在mysql5.7.6版本之后zip包中不再包括data目录,需要做一下初始化。以下是官网文档原文:
As of MySQL 5.7.6, the Zip Archive no longer includes a data directory. To initialize a MySQL installation by creating the data directory and populating the tables in the mysql system database, initialize MySQL using either --initialize or --initialize-insecure. For additional information, see Section 2.10.1.1, “Initializing the Data Directory Using mysqld”.
所以如果没有data目录请执行以下操作:
将my-default.ini copy至bin目录下,然后执行:mysqld --initialize 会生成一个随机密码,记下来。
5.以管理员身份运行cmd,进入解压目录的bin目录下输入:mysqld -install,看到service successfully installed的提示表明安装成功。
6.输入:net start mysql启动mysql服务,得到服务启动成功的提示表明,服务已经启动。
7.接下来修改root密码的操作,原始密码为之前生成的随机密码
二,安装JDK,JRE
安装步骤可以参考:http://jingyan.baidu.com/article/6dad5075d1dc40a123e36ea3.html
三,搭建JavaWEB工程,本示例使用maven来构建管理工程。
安装步骤可以参考:http://jingyan.baidu.com/article/295430f136e8e00c7e0050b9.html
四,搭建springmvc框架
1.在maven中配置springmvc的相关依赖jar包
以下为本示例的配置的部分代码可供参考:pom.xml:
- <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.spreadjs</groupId>
- <artifactId>demo</artifactId>
- <version>0.0.1-SNAPSHOT</version>
- <packaging>war</packaging>
- <dependencies>
- <dependency>
- <groupId>mysql</groupId>
- <artifactId>mysql-connector-java</artifactId>
- <version>5.1.39</version>
- </dependency>
- <dependency>
- <groupId>javax.servlet</groupId>
- <artifactId>servlet-api</artifactId>
- <version>2.5</version>
- <scope>provided</scope>
- </dependency>
- <dependency>
- <groupId>org.springframework</groupId>
- <artifactId>spring-webmvc</artifactId>
- <version>4.3.7.RELEASE</version>
- </dependency>
- <dependency>
- <groupId>javax.servlet</groupId>
- <artifactId>jstl</artifactId>
- <version>1.2</version>
- </dependency>
-
- <dependency>
- <groupId>com.fasterxml.jackson.core</groupId>
- <artifactId>jackson-core</artifactId>
- <version>2.6.1</version>
- </dependency>
- <dependency>
- <groupId>com.fasterxml.jackson.core</groupId>
- <artifactId>jackson-databind</artifactId>
- <version>2.6.1</version>
- </dependency>
- <dependency>
- <groupId>com.fasterxml.jackson.core</groupId>
- <artifactId>jackson-annotations</artifactId>
- <version>2.6.1</version>
- </dependency>
- <dependency>
- <groupId>org.codehaus.jackson</groupId>
- <artifactId>jackson-mapper-asl</artifactId>
- <version>1.9.13</version>
- </dependency>
- <dependency>
- <groupId>org.json</groupId>
- <artifactId>json</artifactId>
- <version>20140107</version>
- </dependency>
- </dependencies>
- </project>
复制代码
2.配置web.xml文件
web.xml:
- <?xml version="1.0" encoding="UTF-8"?>
- <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" version="2.5">
- <display-name>SpreadJS_Demo</display-name>
- <filter>
- <filter-name>encodingFilter</filter-name>
- <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
- <init-param>
- <param-name>encoding</param-name>
- <param-value>UTF-8</param-value>
- </init-param>
- </filter>
- <filter-mapping>
- <filter-name>encodingFilter</filter-name>
- <url-pattern>/*</url-pattern>
- </filter-mapping>
- <servlet>
- <servlet-name>dispatcher</servlet-name>
- <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
- <init-param>
- <param-name>contextConfigLocation</param-name>
- <param-value>/WEB-INF/dispatcher-servlet.xml</param-value>
- </init-param>
- <load-on-startup>1</load-on-startup>
- </servlet>
- <servlet-mapping>
- <servlet-name>dispatcher</servlet-name>
- <url-pattern>/</url-pattern>
- </servlet-mapping>
- <servlet-mapping>
- <servlet-name>default</servlet-name>
- <url-pattern>*.css</url-pattern>
- </servlet-mapping>
- <servlet-mapping>
- <servlet-name>default</servlet-name>
- <url-pattern>*.gif</url-pattern>
- </servlet-mapping>
- <servlet-mapping>
- <servlet-name>default</servlet-name>
- <url-pattern>*.jpg</url-pattern>
- </servlet-mapping>
- <servlet-mapping>
- <servlet-name>default</servlet-name>
- <url-pattern>*.js</url-pattern>
- </servlet-mapping>
- <servlet-mapping>
- <servlet-name>default</servlet-name>
- <url-pattern>*.png</url-pattern>
- </servlet-mapping>
- <welcome-file-list>
- <welcome-file>index.jsp</welcome-file>
- </welcome-file-list>
- </web-app>
复制代码 3.配置dispatcher-servlet.xml
- <?xml version="1.0" encoding="UTF-8"?>
- <beans xmlns="http://www.springframework.org/schema/beans"
- xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
- xmlns:context="http://www.springframework.org/schema/context"
- xmlns:mvc="http://www.springframework.org/schema/mvc"
- xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
- http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
- http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd">
-
- <context:component-scan base-package="com.spreadjs.*"></context:component-scan>
- <mvc:annotation-driven>
- <mvc:message-converters>
- <bean class="org.springframework.http.converter.StringHttpMessageConverter"></bean>
- <bean class="org.springframework.http.converter.json.MappingJackson2HttpMessageConverter"></bean>
- </mvc:message-converters>
- </mvc:annotation-driven>
-
- <mvc:default-servlet-handler/>
-
- <context:property-placeholder location="classpath:resources.properties" ignore-unresolvable="true"/>
-
- <mvc:resources location="/resources/" mapping="/resources/**" />
-
- <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
- <property name="prefix" value="/WEB-INF/views/"></property>
- <property name="suffix" value = ".jsp"></property>
- </bean>
- </beans>
复制代码
到此,环境准备工作已经基本完成。接下来,是数据库交互部分的代码编写,本示例采用的是基本的JDBC技术来与mysql数据库进行交互。
一,创建负责创建,关闭数据库连接的工具类DataBaseManager
以下是核心代码:
- public Connection getConnection() throws IOException{
- InputStream in = this.getClass().getClassLoader().getResourceAsStream("resources.properties");
- Properties ps = new Properties();
- ps.load(in);
- in.close();
- String url = ps.get("url").toString();
- String name = ps.get("name").toString();
- String user = ps.get("user").toString();
- String password = ps.get("password").toString();
- Connection conn = null;
- try {
- Class.forName(name);
- conn = DriverManager.getConnection(url, user, password);
- } catch (Exception e) {
- e.printStackTrace();
- }
- return conn;
- }
复制代码 本代码用户创建mysql的数据库连接connection。
二,根据业务逻辑编写业务逻辑层。
附一个查询逻辑的代码,其余代码可以见上传附件。
- public Map<String,Object> getDataFromTableName(String tableName) throws Exception{
- Map<String,Object> dataMap = new HashMap<String,Object>();
- List<Map<String,Object>> listMap = new ArrayList<Map<String,Object>>();
- String sql = "select * from "+tableName;
- Connection conn = null;
- PreparedStatement pstmt = null;
- ResultSet rs = null;
- try {
- conn = new DataBaseManager().getConnection();
- pstmt = conn.prepareStatement(sql);
- rs = pstmt.executeQuery();
- ResultSetMetaData rsmd = rs.getMetaData();
- List<Map<String,Object>> columns = new ArrayList<Map<String,Object>>();
- for(int i=1;i<=rsmd.getColumnCount();i++){
- Map<String,Object> columnPerferences = new HashMap<String,Object>();
- columnPerferences.put("columnName", rsmd.getColumnName(i));
- columnPerferences.put("columnType", rsmd.getColumnType(i));
- columns.add(columnPerferences);
- }
- dataMap.put("columnSetting", columns);
- while(rs.next()){
- Map<String,Object> resultMap = new LinkedHashMap<String,Object>();
- for(int j=0;j<columns.size();j++){
- int columnType = Integer.parseInt(columns.get(j).get("columnType").toString());
- String columnName = columns.get(j).get("columnName").toString();
- if (Types.VARCHAR == columnType) {
- resultMap.put(columnName, rs.getObject(columnName)==null?null:rs.getString(columnName));
- } else if (Types.INTEGER == columnType) {
- resultMap.put(columnName, rs.getObject(columnName)==null?null:rs.getInt(columnName));
- } else if (Types.SMALLINT == columnType) {
- resultMap.put(columnName, rs.getObject(columnName)==null?null:rs.getShort(columnName));
- } else {
- resultMap.put(columnName, rs.getObject(columnName)==null?null:rs.getString(columnName));
- }
- }
- listMap.add(resultMap);
- }
- dataMap.put("dataList", listMap);
- } catch (SQLException e) {
- throw new Exception(e.getMessage());
- } finally {
- new DataBaseManager().closeConnection(conn);
- new DataBaseManager().closePstmt(pstmt);
- }
- return dataMap;
- }
复制代码
该方法可以根据表名查出表中的所有字段以及各个字段的值,将其返回成指定的数据结构,便于和前端进行交互。
三,编写前端,运用SpreadJS将数据源展现出来。
附一个查询逻辑的前端代码逻辑,其余代码可以见上传附件。
- var selectedTableName = $("#table_name").find("option:selected").text();
- $.ajax({
- url: "getDataFromTableName",
- type:"post",
- data:{tableName:selectedTableName},
- datatype: "json",
- success: function (data) {
- //var json = JSON.parse(data);
- if(data.isSuccess == 1){
- spread.suspendPaint();
- sheet.clearPendingChanges();
- columnSetting = data.resultData.columnSetting;
- sheet.setDataSource(data.resultData.dataList);
- formatColumn(columnSetting);
- spread.resumePaint();
- }else{
- alert(data.errorMessage);
- }
- },
- error: function (ex) {
- alert(ex);
- }
- });
复制代码 可以看到使用ajax技术向后台发送请求,在将请求返回的结果,用SpreadJS内置的setDataSource()方法来将数据显示出来。
以上只是其中一个简单的业务逻辑,具体详细的逻辑以及实现方式请参看附件代码。
接下来就是将代码部署到服务器上,本示例使用tomcat作为发布服务器。
tomcat的安装与环境配置可以参考:http://jingyan.baidu.com/article/8065f87fcc0f182330249841.html
|
|