数据库表的行转列，并用struts2在页面显示二维数组

一、需求：目前在做一集中监控系统的项目，其中有个需求就是agent采集上来的针对每个属性的数据都是分多行存储的（如文件系统属性，有盘符、空间大小，使用量、空闲量等多行记录），现在要把这么多行的记录转化成一行记录，这行记录每一列数据对应先前的每一行数据。
二、建表（使用oracle10g）
CREATE TABLE T_PROPERTY_STATUS
(
PROPERTYSTATUS_ID INTEGER NOT NULL,
OBJECTPROPERTY_ID INTEGER,
PROPERTYSTATUSNAME VARCHAR2(64 BYTE),
PROPERTY_STATUS_VALUE VARCHAR2(1024 BYTE),
PROPERTY_STATUS INTEGER,
IFMONITOR INTEGER,
IFNOTIFY INTEGER,
IFRESTORE INTEGER,
PERFORMDATE DATE,
UNITE_INDEX INTEGER
)
表中数据：

[img]/upload/attachment/140430/7f0d8e3f-8604-364e-902a-50909dc7d9e7.jpg[/img]

需要在页面中显示成的记录形式：

[img]/upload/attachment/140433/219f4d05-1e23-3b4e-ae60-c45e56c19d64.jpg[/img]

三、采用SSH框架实现
1. 创建对应原始表的实体类(PropertyStatus.java)：
package bscms3.monitorobjmng.domain;

import java.util.Date;

public class PropertyStatus implements java.io.Serializable{
private Integer propertyStatusId;
private Integer objectPropertyId;
private String propertyStatusName;
private String propertyStatusValue;
private Integer propertyStatus;
private Integer ifMonitor;
private Integer ifNotify;
private Integer ifRestore;
private Date performDate;
private Integer uniteIndex;
public Integer getPropertyStatusId() {
return propertyStatusId;
}
public void setPropertyStatusId(Integer propertyStatusId) {
this.propertyStatusId = propertyStatusId;
}
public Integer getObjectPropertyId() {
return objectPropertyId;
}
public void setObjectPropertyId(Integer objectPropertyId) {
this.objectPropertyId = objectPropertyId;
}
public String getPropertyStatusName() {
return propertyStatusName;
}
public void setPropertyStatusName(String propertyStatusName) {
this.propertyStatusName = propertyStatusName;
}
public String getPropertyStatusValue() {
return propertyStatusValue;
}
public void setPropertyStatusValue(String propertyStatusValue) {
this.propertyStatusValue = propertyStatusValue;
}
public Integer getPropertyStatus() {
return propertyStatus;
}
public void setPropertyStatus(Integer propertyStatus) {
this.propertyStatus = propertyStatus;
}
public Integer getIfMonitor() {
return ifMonitor;
}
public void setIfMonitor(Integer ifMonitor) {
this.ifMonitor = ifMonitor;
}
public Integer getIfNotify() {
return ifNotify;
}
public void setIfNotify(Integer ifNotify) {
this.ifNotify = ifNotify;
}
public Integer getIfRestore() {
return ifRestore;
}
public void setIfRestore(Integer ifRestore) {
this.ifRestore = ifRestore;
}
public Date getPerformDate() {
return performDate;
}
public void setPerformDate(Date performDate) {
this.performDate = performDate;
}
public Integer getUniteIndex() {
return uniteIndex;
}
public void setUniteIndex(Integer uniteIndex) {
this.uniteIndex = uniteIndex;
}

}

2. 对应该实体类的映射文件(PropertyStatus.hbm.xml)：
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
<class name="bscms3.monitorobjmng.domain.PropertyStatus" table="t_property_status" catalog="bscms">
<id name="propertyStatusId" type="java.lang.Integer">
<column name="propertystatus_id" />
<generator class="sequence" >
<param name="sequence">PUBLIC_SEQ</param>
</generator>
</id>
<property name="objectPropertyId" type="java.lang.Integer">
<column name="objectproperty_id" />
</property>
<property name="propertyStatusName" type="java.lang.String">
<column name="propertystatusname" length="64" />
</property>
<property name="propertyStatusValue" type="java.lang.String">
<column name="property_status_value" length="1024" />
</property>
<property name="propertyStatus" type="java.lang.Integer">
<column name="property_status"/>
</property>
<property name="ifMonitor" type="java.lang.Integer">
<column name="ifmonitor"/>
</property>
<property name="ifNotify" type="java.lang.Integer">
<column name="ifnotify"/>
</property>
<property name="ifRestore" type="java.lang.Integer">
<column name="ifrestore"/>
</property>
<property name="performDate" type="java.util.Date">
<column name="performdate"/>
</property>
<property name="uniteIndex" type="java.lang.Integer">
<column name="unite_index"/>
</property>
</class>
</hibernate-mapping>

3.DAO接口及实现类(PropertyStatusDao.java, PropertyStatusDaoImpl.java)
//PropertyStatusDao.java
package bscms3.monitorobjmng.dao;

import java.util.List;

import bscms3.monitorobjmng.domain.PropertyStatus;

public interface PropertyStatusDao {
public List<PropertyStatus> getAllPropertyStatus(Integer objectPropertyId);
public Integer getRows(Integer objectPropertyId);
public Long getCols(Integer objectPropertyId);
}

//PropertyStatusDaoImpl.java
package bscms3.monitorobjmng.dao.impl;

import java.util.List;

import org.springframework.orm.hibernate3.support.HibernateDaoSupport;


import bscms3.monitorobjmng.dao.PropertyStatusDao;
import bscms3.monitorobjmng.domain.PropertyStatus;

public class PropertyStatusDaoImpl extends HibernateDaoSupport implements PropertyStatusDao{
public List<PropertyStatus> getAllPropertyStatus(Integer objectPropertyId){
String sql=null;
sql="FROM PropertyStatus ps WHERE ps.objectPropertyId="+objectPropertyId+"ORDER BY ps.propertyStatusId";
return this.getHibernateTemplate().find(sql);
}
public Integer getRows(Integer objectPropertyId){
String sql = "select max(uniteIndex) from PropertyStatus where objectPropertyId="+objectPropertyId;
return (Integer)this.getHibernateTemplate().find(sql).get(0)+1;
}
public Long getCols(Integer objectPropertyId){
String sql = "select count(propertyStatusId) from PropertyStatus where objectPropertyId="+objectPropertyId+" and uniteIndex=1";
return (Long)this.getHibernateTemplate().find(sql).get(0);
}
}

4. Service接口及实现类(PropertyStatusService.java, PropertyStatusServiceImpl.java)

//PropertyStatusService.java
package bscms3.monitorobjmng.service;

import java.util.List;

import bscms3.monitorobjmng.domain.PropertyStatus;

public interface PropertyStatusService {
public List<PropertyStatus> getAllPropertyStatus(Integer objectPropertyId);
public Integer getRows(Integer objectPropertyId);
public Long getCols(Integer objectPropertyId);
}

//PropertyStatusServiceImpl.java
package bscms3.monitorobjmng.service.impl;

import java.util.List;

import bscms3.monitorobjmng.dao.PropertyStatusDao;
import bscms3.monitorobjmng.domain.PropertyStatus;
import bscms3.monitorobjmng.service.PropertyStatusService;

public class PropertyStatusServiceImpl implements PropertyStatusService{
PropertyStatusDao propertyStatusDao;
public List<PropertyStatus> getAllPropertyStatus(Integer objectPropertyId){
return propertyStatusDao.getAllPropertyStatus(objectPropertyId);
}
public Integer getRows(Integer objectPropertyId){
return propertyStatusDao.getRows(objectPropertyId);
}
public Long getCols(Integer objectPropertyId){
return propertyStatusDao.getCols(objectPropertyId);
}

public PropertyStatusDao getPropertyStatusDao() {
return propertyStatusDao;
}
public void setPropertyStatusDao(PropertyStatusDao propertyStatusDao) {
this.propertyStatusDao = propertyStatusDao;
}
}

5. Action类 (PropertyStatusAction.java)
//PropertyStatusAction.java
package bscms3.strategymng.action.strategies;

import java.util.List;

import bscms3.monitorobjmng.domain.PropertyStatus;
import bscms3.monitorobjmng.service.PropertyStatusService;

import com.opensymphony.xwork2.ActionSupport;

public class PropertyStatusAction extends ActionSupport{
private Integer objectPropertyId;//由于查的是关于监控对象的某一属性的信息，所以需要上文带来属性ID的值
private PropertyStatusService propertyStatusService;
private String[][] items;//用于存放处理后的数据，第一行存放表头，其余行存对应的数据
public Integer getObjectPropertyId() {
return objectPropertyId;
}
public void setObjectPropertyId(Integer objectPropertyId) {
this.objectPropertyId = objectPropertyId;
}
public PropertyStatusService getPropertyStatusService() {
return propertyStatusService;
}
public void setPropertyStatusService(PropertyStatusService propertyStatusService) {
this.propertyStatusService = propertyStatusService;
}
public String[][] getItems() {
return items;
}
public void setItems(String[][] items) {
this.items = items;
}

public String getData(){
if(this.objectPropertyId!=null){
List<PropertyStatus> propertyStatus=propertyStatusService.getAllPropertyStatus(this.objectPropertyId);
int row=(int)propertyStatusService.getRows(this.objectPropertyId);
long col=(long)propertyStatusService.getCols(this.objectPropertyId);
int col2=(int) col;
String[][] tmp_items=new String[row][col2];
int row_index=1,col_index=0;
for(int i=0; i<propertyStatus.size();i++){
PropertyStatus tmp_propertyStatus=(PropertyStatus)propertyStatus.get(i);

if(tmp_items[0][col_index]==null)
tmp_items[0][col_index]=tmp_propertyStatus.getPropertyStatusName();

tmp_items[row_index][col_index]=tmp_propertyStatus.getPropertyStatusValue();
col_index++;
if(col_index>=col2){
col_index=0;
row_index++;
}


}
this.setItems(tmp_items);
return SUCCESS;
}
return ERROR;
}
}

6.配置applicationContext.xml
<?xml version="1.0" encoding="GBK"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
<!-- 定义c3p0数据源 -->
<bean id="dataSource"
class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">
<!-- 指定连接数据库驱动 -->
<!-- property name="driverClass" value="com.mysql.jdbc.Driver" / -->
<property name="driverClass" value="oracle.jdbc.driver.OracleDriver" />
<!-- 指定连接数据库url -->

<property name="jdbcUrl" value="jdbc:oracle:thin:@10.66.7.233:1522:bscms" />
<!-- 指定连接数据库用户名，密码为空 -->
<property name="user" value="bscms" />
<property name="password" value="bscms" />
</bean>
<!-- 定义Hibernate的sessionFactory -->
<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<!-- 指定数据源 -->
<property name="dataSource" ref="dataSource" />
<!-- 指定Hibernate映射文件 -->
<property name="mappingResources">
<list>
<value>bscms3/monitorobjmng/domain/PropertyStatus.hbm.xml</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<!-- 指定使用方言 -->
<prop key="hibernate.dialect">
org.hibernate.dialect.OracleDialect
</prop>
<!-- 是否在控制台输出sql语句 -->
<prop key="show_sql">true</prop>
<prop key="hibernate.hbm2ddl.auto">update</prop>
<prop key="hibernate.jdbc.batch_size">20</prop>
<!-- 解决查询中文乱码的问题 -->
<prop key="hibernate.query.factory_class">
org.hibernate.hql.ast.ASTQueryTranslatorFactory
</prop>
</props>
</property>
</bean>
<!-- 定义事务管理 -->
<bean id="transactionManager"
class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory" />
</bean>
<!-- 定义事务管理拦截器 -->
<bean id="transactionInterceptor"
class="org.springframework.transaction.interceptor.TransactionInterceptor">
<property name="transactionManager" ref="transactionManager" />
<property name="transactionAttributes">
<props>
<prop key="get*">PROPAGATION_REQUIRED,readOnly</prop>
<prop key="*">PROPAGATION_REQUIRED</prop>
</props>
</property>
</bean>
<!-- 定义代理自动管理事务 -->
<bean id="ProxyCreator" class="org.springframework.aop.framework.autoproxy.BeanNameAutoProxyCreator">
<!-- 指定需要Spring管理事务的Bean -->
<property name="beanNames">
<list>
</list>
</property>
<!-- 调用事务管理拦截器 -->
<property name="interceptorNames">
<list>
<value>transactionInterceptor</value>
</list>
</property>
</bean>
<!-- 依赖注入 -->

<bean id="propertyStatusAction" class="bscms3.strategymng.action.strategies.PropertyStatusAction">
<property name="propertyStatusService" ref="propertyStatusService" />
</bean>
<!-- service -->

<bean id="propertyStatusService" class="bscms3.monitorobjmng.service.impl.PropertyStatusServiceImpl">
<property name="propertyStatusDao" ref="propertyStatusDao"/>
</bean>
<!-- dao -->

<bean id="propertyStatusDao" class="bscms3.monitorobjmng.dao.impl.PropertyStatusDaoImpl">
<property name="sessionFactory" ref="sessionFactory" />
</bean>

</beans>

7.配置struts.xml
<action name="propertyStatus" class="propertyStatusAction" method="getData">
<result>/jsp/strategymng/propertystatus.jsp</result>
</action>

8.创建：propertystatus.jsp
<%@ page language="java" contentType="text/html; charset=GB2312"%>
<%@ taglib prefix="s" uri="/struts-tags"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>


<title>My JSP 'propertystatus.jsp' starting page</title>


</head>

<body>

<table border="1" id="table11">
<s:iterator id="data1" value="items" status="st1">
<tr>
<s:iterator id="data2" value="items[#st1.index]" status="st2">
<td>
<s:property value="data2"/>
</td>
</s:iterator>
</tr>
</s:iterator>
</table>


</body>
</html>

9.在浏览器中输入：http://localhost:8080/bscms3/propertyStatus.action?objectPropertyId=10054 结果如下：

[img]/upload/attachment/140444/241c7bfb-c067-3d96-b0ca-57b981e1b1ff.jpg[/img]