通过Java编程实现行列转换（转换为固定列）

将上图的数据转换为入如下数据形式：

实际上就是实现行列转换： 

1、定义JavaBean的PO与VO实现

PO:

public class RetentionRatePO {
    private String time;
    private int flag;
    private double value;
    
    public int getFlag() {
        return flag;
    }
    public void setFlag(int flag) {
        this.flag = flag;
    }
    public double getValue() {
        return value;
    }
    public void setValue(double value) {
        this.value = value;
    }
    public String getTime() {
        return time;
    }
    public void setTime(String time) {
        this.time = time;
    }  
}

VO:

public class RetentionRateVO {
    private String time;
    private double rate0;
    private double rate1;
    private double rate2;
    private double rate3;
    private double rate4;
    private double rate5;
    private double rate6;
    private double rate7;
    
    public String getTime() {
        return time;
    }
    
    public double getRate0() {
        return rate0;
    }
    public void setRate0(double rate0) {
        this.rate0 = rate0;
    }
    public void setTime(String time) {
        this.time = time;
    }
    public double getRate1() {
        return rate1;
    }
    public void setRate1(double rate1) {
        this.rate1 = rate1;
    }
    public double getRate2() {
        return rate2;
    }
    public void setRate2(double rate2) {
        this.rate2 = rate2;
    }
    public double getRate3() {
        return rate3;
    }
    public void setRate3(double rate3) {
        this.rate3 = rate3;
    }
    public double getRate4() {
        return rate4;
    }
    public void setRate4(double rate4) {
        this.rate4 = rate4;
    }
    public double getRate5() {
        return rate5;
    }
    public void setRate5(double rate5) {
        this.rate5 = rate5;
    }
    public double getRate6() {
        return rate6;
    }
    public void setRate6(double rate6) {
        this.rate6 = rate6;
    }
    public double getRate7() {
        return rate7;
    }
    public void setRate7(double rate7) {
        this.rate7 = rate7;
    }
    
}

2、springMVC中 dao 中获取PO

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.logging.Logger;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.zfc.b3.bean.CCUCount;
import com.zfc.b3.bean.RetentionRate;
import com.zfc.b3.bean.RetentionRatePO;
import com.zfc.b3.bean.Test;

@Repository
public class RetentionRateDao {
	
	@Autowired
	private JdbcTemplate jdbcTemplate_postgresql;
	
	private static final Logger log = Logger.getAnonymousLogger();
	
	public List<RetentionRatePO> getRetentionRate(String sdate,
			String edate, String searchtype){
		
		String oracle = "" ;
		
		oracle= " select to_char(m,'yyyy-mm-dd') as time ,diff as flag, value  from  "
	
				+" ( "
				+" select m, diff, count(*) as value from "
				+" (select a.user_id, b.m, a.d-b.m as diff from "
				+" (select user_id, date_trunc('day', date)::date as d from (select date AT TIME ZONE 'UTC' as date, user_id from login_record where type_id=0 and date >= date'"+sdate+"' and date <= date'"+edate+"' + 7 ) t where  user_id not in (select user_id from login_record where date < date'"+sdate+"') group by user_id, date_trunc('day', date)) a "
				+" join "
				+" (select user_id, min(date_trunc('day',date))::date as m from (select date AT TIME ZONE 'UTC' as date, user_id from login_record where type_id=0 and date >= date'"+sdate+"' and date <= date'"+edate+"' + 7 ) t where user_id not in (select user_id from login_record where date < date'"+sdate+"') group by user_id) b "
				+" on a.user_id = b.user_id) c "
				+" group by m, diff "
				+" having diff<=7  "
				+" ) t  order by t.m ";

		
		this.log.info(oracle);
		return this.jdbcTemplate_postgresql.query(oracle,
				new RowMapper<RetentionRatePO>() {
					public RetentionRatePO mapRow(ResultSet rs, int index)
							throws SQLException {
						RetentionRatePO ec = new RetentionRatePO();
						ec.setTime(rs.getString("time"));
						ec.setFlag(rs.getInt("flag"));
						ec.setValue(rs.getDouble("value"));
						return ec;
					}
				});
	}
	
	
}

 3、在springMVC的service中转换为VO 

import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.zfc.b3.bean.CCUCount;
import com.zfc.b3.bean.RetentionRate;
import com.zfc.b3.bean.RetentionRatePO;
import com.zfc.b3.bean.RetentionRateVO;
import com.zfc.b3.dao.CCUCountDao;
import com.zfc.b3.dao.RetentionRateDao;

@Service
public class RetentionRateService {
	@Autowired
	private RetentionRateDao rdao;
	
	public List<RetentionRateVO> getRetentionRate(String sdate,
			String edate, String searchtype){
		
		 List<RetentionRatePO> reverse = rdao.getRetentionRate(sdate, edate, searchtype);
		 Set set = new HashSet();
		 Set tmepSet = new HashSet();
    	 List list = new ArrayList();
    	 //首先存放列的标识，需要过滤重复的类，通过set集合过滤重复值
    	 for(RetentionRatePO obj:reverse){
    		 set.add(obj.getTime());
    	 }
    	 Iterator it = set.iterator();
    	 int i = 0;
    	 while(it.hasNext()){
    		 String fg = (String) it.next();
    		 RetentionRateVO rr = new RetentionRateVO();
    		 List ls = new ArrayList();
    		 for(RetentionRatePO o :reverse){
    			    
    	    		if(fg.equals(o.getTime())){
    	    		   rr.setTime(fg);
    	    		   double rate = o.getValue();
    	    		  /* if(o.getFlag()>i){
    	    			   rate =0;
    	    		   }*/
    	    		   switch(o.getFlag()){
    	    		   		case 0: rr.setRate0(rate);ls.add(rate);break;
    	    		   		case 1: rr.setRate1(rate);ls.add(rate);break;
    	    		   		case 2: rr.setRate2(rate);ls.add(rate);break;
    	    		   		case 3: rr.setRate3(rate);ls.add(rate);break;
    	    		   		case 4: rr.setRate4(rate);ls.add(rate);break;
    	    		   		case 5: rr.setRate5(rate);ls.add(rate);break;
    	    		   		case 6: rr.setRate6(rate);ls.add(rate);break;
    	    		   		case 7: rr.setRate7(rate);ls.add(rate);break;
    	    		   }
    	    		  // ls.add(rr);
    	    		   tmepSet.add(rr);
    	    		}
        		   
        	 }
    		 //对象去重
    		 Iterator its = tmepSet.iterator();
  		     while(its.hasNext()){
  		    	RetentionRateVO rvo = (RetentionRateVO) its.next();
  			   if(!list.contains(rvo)){
  				 list.add(rvo);
  			   }
  		     }
  		    
    		i++; 
    	 }
		
		return list;
	}
		
		
}