Hibernate Grouping query - 나중에 코드 수정해서 공개…

CreateQuery와 도메인

-- Table: topic_trend

– DROP TABLE topic_trend;

CREATE TABLE topic_trend ( topic_trend_id bigint NOT NULL, analysis_date timestamp without time zone, analysis_period timestamp without time zone, rising double precision, steady double precision, term character varying(255), type_contents character varying(200) NOT NULL, type_period character varying(20), CONSTRAINT topic_trend_pkey PRIMARY KEY (topic_trend_id) ) WITH ( OIDS=FALSE ); ALTER TABLE topic_trend OWNER TO vital;

– Index: ix_topic_trend

– DROP INDEX ix_topic_trend;

CREATE INDEX ix_topic_trend ON topic_trend USING btree (type_contents COLLATE pg_catalog.”default”, analysis_date);

Hibernate Domani

package com.kt.vital.domain.model.statistics;

import com.google.common.base.Objects; import kr.debop4j.data.model.AnnotatedEntityBase; import lombok.Getter; import lombok.Setter; import org.hibernate.annotations.DynamicInsert; import org.hibernate.annotations.DynamicUpdate; import org.hibernate.annotations.Index; import org.hibernate.annotations.Type; import org.hibernate.validator.constraints.NotEmpty; import org.joda.time.DateTime;

import javax.persistence.*;

/** * 토픽 추세 정보 * 토픽 분석 * * @author archmagece@gmail.com * @since 2013.05.13 * */ @Entity @Table(name = “TopicTrend”) //@Table(name = “TopicTrend”, uniqueConstraints = {@UniqueConstraint(name=”topic_trend_unique”, columnNames = {“typeContents”, “analysisDate”})}) @org.hibernate.annotations.Table(appliesTo = “TOPIC_TREND”, indexes = { @Index(name = “ix_topic_trend”, columnNames = { “typeContents”, “analysisDate” }) }) //@Table(name=”TopicTrend”) @DynamicInsert @DynamicUpdate @Getter @Setter public class TopicTrend extends AnnotatedEntityBase {

private static final long serialVersionUID = -4845644155814369186L;

//protected TopicTrend() {}

//public TopicTrend(String typePeriod, String typeContents, DateTime analysisPeriod, DateTime analysisDate, String term, double steady, double rising) {}
public TopicTrend() {}

/\*\*
 \* TopicTrend Id
 \*/
@Id
@GeneratedValue(strategy = GenerationType.AUTO, generator = "topic\_trend\_id\_seq")
@SequenceGenerator(name="topic\_trend\_id\_seq", sequenceName = "topic\_trend\_id\_seq")
@Column(name = "TopicTrendId")
private Long id;

/\*\*
 \* 분석주기
 \*/ //    @NotEmpty
@Column(name = "TypePeriod", nullable = true, length = 20)
private String typePeriod;

/\*\*
 \* 컨텐츠(컬렉션)
 \*/
@NotEmpty
@Column(name = "TypeContents", nullable = true, length = 200)
private String typeContents;

/\*\*
 \* 분석 시작일
 \*/ //    @NotEmpty
@Type(type = "kr.debop4j.data.hibernate.usertype.JodaDateTimeUserType")
@Column(name = "AnalysisPeriod", nullable = true)
private DateTime analysisPeriod;

/\*\*
 \* 분석일
 \*/ //    @NotEmpty
@Type(type = "kr.debop4j.data.hibernate.usertype.JodaDateTimeUserType")
@Column(name = "AnalysisDate", nullable = true)
private DateTime analysisDate;

/\*\*
 \* 용어
 \*/ //    @NotEmpty
@Column(name = "Term", nullable = true)
private String term;

/\*\*
 \* 종합
 \*/
@Column(nullable = true)
private double steady;

/\*\*
 \* 급상승
 \*/
@Column(nullable = true)
private double rising;

@Override
protected Objects.ToStringHelper buildStringHelper() {
    return super.buildStringHelper()
            .add("id", id)
            .add("typePeriod", typePeriod)
            .add("typeContents", typeContents)
            .add("analysisPeriod", analysisPeriod)
            .add("analysisDate", analysisDate)
            .add("term", term)
            .add("steady", steady)
            .add("rising", rising);
} }

원하는 SelectQuery

select date_part(‘day’, this_.analysis_date) as time_group, sum(this_.RISING) as y1_, this_.TERM as y2_ from TOPIC_TREND this_ group by time_group, this_.TERM order by time_group asc, y1_ desc limit ?

요 쿼리를 만드는 방법…

projectionList.add(Projections.alias(Projections.sqlGroupProjection( “date_part(‘day’,{alias}.analysis_date) as time_group, term”, “time_group, term”, new String[]{“time_group, term”}, new Type[] {DoubleType.INSTANCE, StringType.INSTANCE} ),”time_group”));

에러메세지 org.hibernate.QueryException: not a single-length projection: time_group 그룹을 두개씩 써놓으면 안된다는 메세지….처리해준다.

private int pageSize=10; public List dataByTimeSection(TIME_SECTION timeSection, int page) { // int wordPerPage = 10; // int endWord = wordPerPage*page; // int startWord = endWord - 9;

    session = UnitOfWorks.getCurrentSession();

    DetachedCriteria dc = DetachedCriteria.forClass(TopicTrend.class);
    criteria = session.createCriteria(TopicTrend.class);
    ProjectionList projectionList = Projections.projectionList();

    switch (timeSection){
        case HOUR:
            projectionList.add(Projections.sqlGroupProjection(
                    "date\_part('hour',{alias}.analysis\_date) as time\_group",
                    "time\_group",
                    new String\[\]{"time\_group"},
                    new Type\[\] {DoubleType.INSTANCE}
            ), "time\_group");
            break;
        case DAY:
            projectionList.add(Projections.alias(Projections.sqlGroupProjection(
                    "date\_part('day',{alias}.analysis\_date) as time\_group",
                    "time\_group",
                    new String\[\]{"time\_group, term"},
                    new Type\[\] {DoubleType.INSTANCE, StringType.INSTANCE}
            ),"time\_group"));
            break;
        case WEEK:
            projectionList.add(Projections.sqlGroupProjection(
                    "date\_part('week',{alias}.analysis\_date) as time\_group",
                    "time\_group",
                    new String\[\]{"time\_group"},
                    new Type\[\] {DoubleType.INSTANCE}
            ),"time\_group"));
            break;
        case MONTH:
            projectionList.add(Projections.sqlGroupProjection(
                    "date\_part('month',{alias}.analysis\_date) as time\_group",
                    "time\_group",
                    new String\[\]{"time\_group"},
                    new Type\[\] {DoubleType.INSTANCE}
            ),"time\_group"));
            break;
        default:
            //freak //                projectionList.add(Projections.sqlGroupProjection( //                        "sum(rising) as cnt, date\_part('month', {alias}.analysis\_date) as time\_group, term", //                        "date\_part('month', {alias}.analysis\_date), term", //                        new String\[\]{"cnt", "time\_group", "term"}, //                        new Type\[\] {DoubleType.INSTANCE, DoubleType.INSTANCE, StringType.INSTANCE} //                ));
            break;
    }

    criteria.setProjection(
            projectionList
                    .add(Projections.alias(
                            Projections.sum("rising"),"cnt")
                    )
                    .add(Projections.groupProperty("term")
                    )
    )
    .addOrder(Order.asc("time\_group"))
    .addOrder(Order.desc("cnt"));

    criteria.setFirstResult((page-1)\*pageSize);
    criteria.setMaxResults(page \* pageSize);

    return criteria.list();

}

public void testDataByTimeSectionUsingDay() throws Exception { List<Object[]> list = keywordRankingService.dataByTimeSection(KeywordRankingService.TIME_SECTION.DAY, 1); for( Object[] objects : list){ System.out.println(objects); for(Object obj : objects){ // System.out.println(obj instanceof Double); // System.out.println(obj instanceof String); System.out.print(obj); } } System.out.println(list); }