Jpa mssql Dialect SQLServer2012 Offset절 사용

SQLServer2012 버전부터 offset fetch절을 지원합니다.

 

JpaRepository를 상속받아 Page<T> findAll(Pageable var1); 메소드 호출시 Offset Limit절을 사용하지 않고 Jpa 자체 쿼리를 사용하는 이슈가 있어서 디버깅을 통해 OrderBy가 작성되지 않아 Offset절이 사용되지 않는 것을 확인했습니다.

 

SQLServer2012LimitHandler.processSql() 메소드에서 order by절 유무를 통해 페이징 처리 쿼리절의 방법이 나뉩니다.

 

	public String processSql(String sql, RowSelection selection) {
		if ( hasOrderBy( sql ) ) {
			if ( !LimitHelper.useLimit( this, selection ) ) {
				return sql;
			}
			return applyOffsetFetch( selection, sql, getInsertPosition( sql ) );
		}
		return super.processSql( sql, selection );
	}

-order by절이 있을경우 쿼리 마지막에 offset절을 작성합니다.

private String applyOffsetFetch(RowSelection selection, String sql, int position) {
		usedOffsetFetch = true;

		StringBuilder sb = new StringBuilder();
		sb.append( sql.substring( 0, position ) );
		sb.append( getOffsetFetch( selection ) );
		if ( position > sql.length() ) {
			sb.append( sql.substring( position - 1 ) );
		}

		return sb.toString();
	}
    
    private String getOffsetFetch(RowSelection selection) {
		if ( !LimitHelper.hasFirstRow( selection ) ) {
			return " offset 0 rows fetch next ? rows only";
		}
		return " offset ? rows fetch next ? rows only";
	}

 

-order by 절이 없을 경우 super.processSql() 메소드에서 마지막에 부분에 offset이 아닌 페이징을 처리할 수 있는 쿼리를 작성합니다.

super.processSql(String sql, RowSelection selection) {
    ...
    ...
      sb.insert( offset, !isCTE ? "WITH query AS (" : ", query AS (" );
      sb.append( ") SELECT " ).append( selectClause ).append( " FROM query " );
      sb.append( "WHERE __hibernate_row_nr__ >= ? AND __hibernate_row_nr__ < ?" );
}

Offset 사용 규칙.

- OrderBy절이 필요합니다.

- Offset 절은 필수 항목으로 Fetch절 단독 사용할 수 없습니다.

- TOP 절은 LIMIT/OFFSET에서 허용되지 않습니다.

 

조회메소드 호출시 offset절을 사용하는 해결 방법.

방법 1)

Pageable 객체 생성시 아래 static 메소드로 생성을 하게 되면 findAll 호출시 Offset절을 사용할 수 있습니다.

    public static PageRequest of(int page, int size, Sort sort) {
        return new PageRequest(page, size, sort);
    }

    public static PageRequest of(int page, int size, Direction direction, String... properties) {
        return of(page, size, Sort.by(direction, properties));
    }

방법 2)

JpaRepository를 상속받은 클래스에 Orderby를 포함한 메소드를 작성하면 Pageable객체에 정렬 데이터가 존재하지 않아도 Offset절을 사용할 수 있습니다.

Page<Dummy> findAllByOrderByRegDateDesc(Pageable p);