我们在实际工作环境中,经常遇到下面的场景:
我们的数据的列根据页面查询条件的不同,是动态变化的。行数也是变换的。
我们最熟悉的莫过于列的数量是固定不变的,行的数量是动态改变的。这种情况,我们只有写一个SQL就能完成一切:select name,age,address from person;
然后把上面SQL的查询结果,写到页面<table>或者拼接成JSON返回给页面就可以了。
然而,我们还会遇到列的数量是动态改变的情况。
这样怎么办?
总体的思路如下:
我们的最终展现页面上的数据一般也是2维表的样子。
<table border=1>
<tr>
<td>name</td>
<td>age</td>
<td>address</td>
</tr>
<tr>
<td>lili</td>
<td>34</td>
<td>Beijing</td>
</tr>
<tr>
<td>zhanglei</td>
<td>30</td>
<td>Shanghai</td>
</tr>
</table>
这张2维表,如果列是动态的,行也是动态的,该如何给这个2维表填充数据呢?
很简单,我们初中学过的数学知识就可以了。一个2维表,其实就是一个X,Y坐标组成的坐标系,这样我们确定了坐标系内的x,y就可以确定每个单元格的数据了。
1、首先,生成Header表头的列表(X坐标的列表)。
2、然后,生成Rows的表体行列表(Y坐标的列表)。
3、然后,生成所有数据的Map,这个Map的Key就是x,y坐标。
5、最后,对Rows的行循环,在这个循环内部嵌入header的循环。根据Map的Key取出循环的每一个单元格的数据,也就是根据X,Y得到Map存储的数值。
这样,一个table就生成了。
在做一些动态的数据table的生成的时候,经常会遇到动态生成标题和数据的时候。
如何动态生成表头和对应的数据呢。
可以查询table的元数据:
这里给出一个spring的元数据的rowmapper,提供参考:如何查询table的元数据。这个类是queryForList(sql)的默认的RowMapper
/*
* Copyright 2002-2012 the original author or authors.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.springframework.jdbc.core;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Map;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.util.LinkedCaseInsensitiveMap;
/**
* {@link RowMapper} implementation that creates a {@code java.util.Map}
* for each row, representing all columns as key-value pairs: one
* entry for each column, with the column name as key.
*
* <p>The Map implementation to use and the key to use for each column
* in the column Map can be customized through overriding
* {@link #createColumnMap} and {@link #getColumnKey}, respectively.
*
* <p><b>Note:</b> By default, ColumnMapRowMapper will try to build a linked Map
* with case-insensitive keys, to preserve column order as well as allow any
* casing to be used for column names. This requires Commons Collections on the
* classpath (which will be autodetected). Else, the fallback is a standard linked
* HashMap, which will still preserve column order but requires the application
* to specify the column names in the same casing as exposed by the driver.
*
* @author Juergen Hoeller
* @since 1.2
* @see JdbcTemplate#queryForList(String)
* @see JdbcTemplate#queryForMap(String)
*/
public class ColumnMapRowMapper implements RowMapper<Map<String, Object>> {
public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
Map<String, Object> mapOfColValues = createColumnMap(columnCount);
for (int i = 1; i <= columnCount; i++) {
String key = getColumnKey(JdbcUtils.lookupColumnName(rsmd, i));
Object obj = getColumnValue(rs, i);
mapOfColValues.put(key, obj);
}
return mapOfColValues;
}
/**
* Create a Map instance to be used as column map.
* <p>By default, a linked case-insensitive Map will be created.
* @param columnCount the column count, to be used as initial
* capacity for the Map
* @return the new Map instance
* @see org.springframework.util.LinkedCaseInsensitiveMap
*/
protected Map<String, Object> createColumnMap(int columnCount) {
return new LinkedCaseInsensitiveMap<Object>(columnCount);
}
/**
* Determine the key to use for the given column in the column Map.
* @param columnName the column name as returned by the ResultSet
* @return the column key to use
* @see java.sql.ResultSetMetaData#getColumnName
*/
protected String getColumnKey(String columnName) {
return columnName;
}
/**
* Retrieve a JDBC object value for the specified column.
* <p>The default implementation uses the {@code getObject} method.
* Additionally, this implementation includes a "hack" to get around Oracle
* returning a non standard object for their TIMESTAMP datatype.
* @param rs is the ResultSet holding the data
* @param index is the column index
* @return the Object returned
* @see org.springframework.jdbc.support.JdbcUtils#getResultSetValue
*/
protected Object getColumnValue(ResultSet rs, int index) throws SQLException {
return JdbcUtils.getResultSetValue(rs, index);
}
}