spark 大型项目实战(十九):用户访问session分析(十九) -- 聚合结果写入mysql

文章地址：http://www.haha174.top/article/details/253596
项目源码：https://github.com/haha174/spark-session.git
前文中一直在介绍如何进行session 过滤聚合，本篇介绍一下如何将过滤以后的结果写入mysql.
首先需要创建一个model 类便于进行数据操作

public class SessionAggrStat {
	private long taskid;
	private long session_count;
	private double visit_length_1s_3s_ratio;
	private double visit_length_4s_6s_ratio;
	private double visit_length_7s_9s_ratio;
	private double visit_length_10s_30s_ratio;
	private double visit_length_30s_60s_ratio;
	private double visit_length_1m_3m_ratio;
	private double visit_length_3m_10m_ratio;
	private double visit_length_10m_30m_ratio;
	private double visit_length_30m_ratio;
	
	private double step_length_1_3_ratio;
	private double step_length_4_6_ratio;
	private double step_length_7_9_ratio;
	private double step_length_10_30_ratio;
	private double step_length_30_60_ratio;
	private double step_length_60_ratio;
	public long getTaskid() {
		return taskid;
	}
	public void setTaskid(long taskid) {
		this.taskid = taskid;
	}
	public long getSession_count() {
		return session_count;
	}
	public void setSession_count(long session_count) {
		this.session_count = session_count;
	}
	public double getVisit_length_1s_3s_ratio() {
		return visit_length_1s_3s_ratio;
	}
	public void setVisit_length_1s_3s_ratio(double visit_length_1s_3s_ratio) {
		this.visit_length_1s_3s_ratio = visit_length_1s_3s_ratio;
	}
	public double getVisit_length_4s_6s_ratio() {
		return visit_length_4s_6s_ratio;
	}
	public void setVisit_length_4s_6s_ratio(double visit_length_4s_6s_ratio) {
		this.visit_length_4s_6s_ratio = visit_length_4s_6s_ratio;
	}
	public double getVisit_length_7s_9s_ratio() {
		return visit_length_7s_9s_ratio;
	}
	public void setVisit_length_7s_9s_ratio(double visit_length_7s_9s_ratio) {
		this.visit_length_7s_9s_ratio = visit_length_7s_9s_ratio;
	}
	public double getVisit_length_10s_30s_ratio() {
		return visit_length_10s_30s_ratio;
	}
	public void setVisit_length_10s_30s_ratio(double visit_length_10s_30s_ratio) {
		this.visit_length_10s_30s_ratio = visit_length_10s_30s_ratio;
	}
	public double getVisit_length_30s_60s_ratio() {
		return visit_length_30s_60s_ratio;
	}
	public void setVisit_length_30s_60s_ratio(double visit_length_30s_60s_ratio) {
		this.visit_length_30s_60s_ratio = visit_length_30s_60s_ratio;
	}
	public double getVisit_length_1m_3m_ratio() {
		return visit_length_1m_3m_ratio;
	}
	public void setVisit_length_1m_3m_ratio(double visit_length_1m_3m_ratio) {
		this.visit_length_1m_3m_ratio = visit_length_1m_3m_ratio;
	}
	public double getVisit_length_3m_10m_ratio() {
		return visit_length_3m_10m_ratio;
	}
	public void setVisit_length_3m_10m_ratio(double visit_length_3m_10m_ratio) {
		this.visit_length_3m_10m_ratio = visit_length_3m_10m_ratio;
	}
	public double getVisit_length_10m_30m_ratio() {
		return visit_length_10m_30m_ratio;
	}
	public void setVisit_length_10m_30m_ratio(double visit_length_10m_30m_ratio) {
		this.visit_length_10m_30m_ratio = visit_length_10m_30m_ratio;
	}

	
	
	public double getVisit_length_30m_ratio() {
		return visit_length_30m_ratio;
	}
	public void setVisit_length_30m_ratio(double visit_length_60m_ratio) {
		this.visit_length_30m_ratio = visit_length_60m_ratio;
	}
	public double getStep_length_1_3_ratio() {
		return step_length_1_3_ratio;
	}
	public void setStep_length_1_3_ratio(double step_length_1_3_ratio) {
		this.step_length_1_3_ratio = step_length_1_3_ratio;
	}
	public double getStep_length_4_6_ratio() {
		return step_length_4_6_ratio;
	}
	public void setStep_length_4_6_ratio(double step_length_4_6_ratio) {
		this.step_length_4_6_ratio = step_length_4_6_ratio;
	}
	public double getStep_length_7_9_ratio() {
		return step_length_7_9_ratio;
	}
	public void setStep_length_7_9_ratio(double step_length_7_9_ratio) {
		this.step_length_7_9_ratio = step_length_7_9_ratio;
	}
	public double getStep_length_10_30_ratio() {
		return step_length_10_30_ratio;
	}
	public void setStep_length_10_30_ratio(double step_length_10_30_ratio) {
		this.step_length_10_30_ratio = step_length_10_30_ratio;
	}
	public double getStep_length_30_60_ratio() {
		return step_length_30_60_ratio;
	}
	public void setStep_length_30_60_ratio(double step_length_30_60_ratio) {
		this.step_length_30_60_ratio = step_length_30_60_ratio;
	}
	public double getStep_length_60_ratio() {
		return step_length_60_ratio;
	}
	public void setStep_length_60_ratio(double step_length_60_ratio) {
		this.step_length_60_ratio = step_length_60_ratio;
	}

下面编写dao类用于插入数据

public class SessionAggrStatDAOImpl implements ISessionAggrStatDAO {
    @Override
    public void insert(SessionAggrStat sessionAggrStat) {
        SessionFactory sessionFactory=SessionFactory.getSessionFactory ();
        String sql = "insert into session_aggr_stat values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        Object[] params = new Object[] {sessionAggrStat.getTaskid(),
                sessionAggrStat.getSession_count(),
                sessionAggrStat.getVisit_length_1s_3s_ratio(),
                sessionAggrStat.getVisit_length_4s_6s_ratio(),
                sessionAggrStat.getVisit_length_7s_9s_ratio(),
                sessionAggrStat.getVisit_length_10s_30s_ratio(),
                sessionAggrStat.getVisit_length_30s_60s_ratio(),
                sessionAggrStat.getVisit_length_1m_3m_ratio(),
                sessionAggrStat.getVisit_length_3m_10m_ratio(),
                sessionAggrStat.getVisit_length_10m_30m_ratio(),
                sessionAggrStat.getVisit_length_30m_ratio(),
                sessionAggrStat.getStep_length_1_3_ratio(),
                sessionAggrStat.getStep_length_4_6_ratio(),
                sessionAggrStat.getStep_length_7_9_ratio(),
                sessionAggrStat.getStep_length_10_30_ratio(),
                sessionAggrStat.getStep_length_30_60_ratio(),
                sessionAggrStat.getStep_length_60_ratio(),};
        sessionFactory.executeUpdate ( sql, params );
    }
}

下面将聚合的结果写入mysql

 private static void calculateAndPersistAggrStat(String value) {
        //从Accumulator统计串中获取值
        long session_count = Long.valueOf(StringUtils.getFieldFromConcatString(
                value, "\\|", Constants.SESSION_PROJECT.SESSION_COUNT));

        long visit_length_1s_3s = Long.valueOf(StringUtils.getFieldFromConcatString(
                value, "\\|", Constants.SESSION_PROJECT.TIME_PERIOD_1s_3s));
        long visit_length_4s_6s = Long.valueOf(StringUtils.getFieldFromConcatString(
                value, "\\|", Constants.SESSION_PROJECT.TIME_PERIOD_4s_6s));
        long visit_length_7s_9s = Long.valueOf(StringUtils.getFieldFromConcatString(
                value, "\\|", Constants.SESSION_PROJECT.TIME_PERIOD_7s_9s));
        long visit_length_10s_30s = Long.valueOf(StringUtils.getFieldFromConcatString(
                value, "\\|", Constants.SESSION_PROJECT.TIME_PERIOD_10s_30s));
        long visit_length_30s_60s = Long.valueOf(StringUtils.getFieldFromConcatString(
                value, "\\|", Constants.SESSION_PROJECT.TIME_PERIOD_30s_60s));
        long visit_length_1m_3m = Long.valueOf(StringUtils.getFieldFromConcatString(
                value, "\\|", Constants.SESSION_PROJECT.TIME_PERIOD_1m_3m));
        long visit_length_3m_10m = Long.valueOf(StringUtils.getFieldFromConcatString(
                value, "\\|", Constants.SESSION_PROJECT.TIME_PERIOD_3m_10m));
        long visit_length_10m_30m = Long.valueOf(StringUtils.getFieldFromConcatString(
                value, "\\|", Constants.SESSION_PROJECT.TIME_PERIOD_10m_30m));
        long visit_length_30m = Long.valueOf(StringUtils.getFieldFromConcatString(
                value, "\\|", Constants.SESSION_PROJECT.TIME_PERIOD_30m));

        long step_length_1_3 = Long.valueOf(StringUtils.getFieldFromConcatString(
                value, "\\|", Constants.SESSION_PROJECT.STEP_PERIOD_1_3));
        long step_length_4_6 = Long.valueOf(StringUtils.getFieldFromConcatString(
                value, "\\|", Constants.SESSION_PROJECT.STEP_PERIOD_4_6));
        long step_length_7_9 = Long.valueOf(StringUtils.getFieldFromConcatString(
                value, "\\|", Constants.SESSION_PROJECT.STEP_PERIOD_7_9));
        long step_length_10_30 = Long.valueOf(StringUtils.getFieldFromConcatString(
                value, "\\|", Constants.SESSION_PROJECT.STEP_PERIOD_10_30));
        long step_length_30_60 = Long.valueOf(StringUtils.getFieldFromConcatString(
                value, "\\|", Constants.SESSION_PROJECT.STEP_PERIOD_30_60));
        long step_length_60 = Long.valueOf(StringUtils.getFieldFromConcatString(
                value, "\\|", Constants.SESSION_PROJECT.STEP_PERIOD_60));

        //计算各个访问时长和访问步长的范围
        double visit_length_1s_3s_ratio = NumberUtils.formatDouble(
                (double)visit_length_1s_3s / (double)session_count, 2);
        double visit_length_4s_6s_ratio = NumberUtils.formatDouble(
                (double)visit_length_4s_6s / (double)session_count, 2);
        double visit_length_7s_9s_ratio = NumberUtils.formatDouble(
                (double)visit_length_7s_9s / (double)session_count, 2);
        double visit_length_10s_30s_ratio = NumberUtils.formatDouble(
                (double)visit_length_10s_30s / (double)session_count, 2);
        double visit_length_30s_60s_ratio = NumberUtils.formatDouble(
                (double)visit_length_30s_60s / (double)session_count, 2);
        double visit_length_1m_3m_ratio = NumberUtils.formatDouble(
                (double)visit_length_1m_3m / (double)session_count, 2);
        double visit_length_3m_10m_ratio = NumberUtils.formatDouble(
                (double)visit_length_3m_10m / (double)session_count, 2);
        double visit_length_10m_30m_ratio = NumberUtils.formatDouble(
                (double)visit_length_10m_30m / (double)session_count, 2);
        double visit_length_30m_ratio = NumberUtils.formatDouble(
                (double)visit_length_30m / (double)session_count, 2);

        double step_length_1_3_ratio = NumberUtils.formatDouble(
                (double)step_length_1_3 / (double)session_count, 2);
        double step_length_4_6_ratio = NumberUtils.formatDouble(
                (double)step_length_4_6 / (double)session_count, 2);
        double step_length_7_9_ratio = NumberUtils.formatDouble(
                (double)step_length_7_9 / (double)session_count, 2);
        double step_length_10_30_ratio = NumberUtils.formatDouble(
                (double)step_length_10_30 / (double)session_count, 2);
        double step_length_30_60_ratio = NumberUtils.formatDouble(
                (double)step_length_30_60 / (double)session_count, 2);
        double step_length_60_ratio = NumberUtils.formatDouble(
                (double)step_length_60 / (double)session_count, 2);

        //将访问结果封装成Domain对象
        SessionAggrStat sessionAggrStat = new SessionAggrStat();
        sessionAggrStat.setTaskid(taskid);
        sessionAggrStat.setSession_count(session_count);
        sessionAggrStat.setVisit_length_1s_3s_ratio(visit_length_1s_3s_ratio);
        sessionAggrStat.setVisit_length_4s_6s_ratio(visit_length_4s_6s_ratio);
        sessionAggrStat.setVisit_length_7s_9s_ratio(visit_length_7s_9s_ratio);
        sessionAggrStat.setVisit_length_10s_30s_ratio(visit_length_10s_30s_ratio);
        sessionAggrStat.setVisit_length_30s_60s_ratio(visit_length_30s_60s_ratio);
        sessionAggrStat.setVisit_length_1m_3m_ratio(visit_length_1m_3m_ratio);
        sessionAggrStat.setVisit_length_3m_10m_ratio(visit_length_3m_10m_ratio);
        sessionAggrStat.setVisit_length_10m_30m_ratio(visit_length_10m_30m_ratio);
        sessionAggrStat.setVisit_length_30m_ratio(visit_length_30m_ratio);

        sessionAggrStat.setStep_length_1_3_ratio(step_length_1_3_ratio);
        sessionAggrStat.setStep_length_4_6_ratio(step_length_4_6_ratio);
        sessionAggrStat.setStep_length_7_9_ratio(step_length_7_9_ratio);
        sessionAggrStat.setStep_length_10_30_ratio(step_length_10_30_ratio);
        sessionAggrStat.setStep_length_30_60_ratio(step_length_30_60_ratio);
        sessionAggrStat.setStep_length_60_ratio(step_length_60_ratio);

        //调用对应的DAO插入统计结果
        ISessionAggrStatDAO sessionAggrStatDAO = DAOFactory.getSessionAggrStatDAO();
        sessionAggrStatDAO.insert(sessionAggrStat);
    }

欢迎关注，更多惊喜等着你