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

naiquan_hu

浏览: 66665 次
性别:
来自: 上海

最近访客更多访客>>

zheyiw

18829905418

sakjf

fanli0476

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

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

博客分类：

struts2

Hibernate Java SQL DAO JSP

一、需求：目前在做一集中监控系统的项目，其中有个需求就是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
)
表中数据：

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

三、采用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">

<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">

<bean id="dataSource"
class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">


<property name="driverClass" value="oracle.jdbc.driver.OracleDriver" />


<property name="jdbcUrl" value="jdbc:oracle:thin:@10.66.7.233:1522:bscms" />

<property name="user" value="bscms" />
<property name="password" value="bscms" />
</bean>

<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">

<property name="dataSource" ref="dataSource" />

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

<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">

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


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


<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 结果如下：