jpa + querydsl + sqlite 페이징 처리 방법

JPA는 기본적으로 sqlite를 지원하지 않기 때문에 SQLDialect를 생성해야 합니다.

구글에 검색해보면 보통 아래 코드가 나옵니다.

import java.sql.Types;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.dialect.function.VarArgsSQLFunction;
import org.hibernate.dialect.identity.IdentityColumnSupport;
import org.hibernate.type.StringType;

public class SQLDialect extends Dialect {

  public SQLDialect() {
    registerColumnType(Types.BIT, "integer");
    registerColumnType(Types.TINYINT, "tinyint");
    registerColumnType(Types.SMALLINT, "smallint");
    registerColumnType(Types.INTEGER, "integer");
    registerColumnType(Types.BIGINT, "bigint");
    registerColumnType(Types.FLOAT, "float");
    registerColumnType(Types.REAL, "real");
    registerColumnType(Types.DOUBLE, "double");
    registerColumnType(Types.NUMERIC, "numeric");
    registerColumnType(Types.DECIMAL, "decimal");
    registerColumnType(Types.CHAR, "char");
    registerColumnType(Types.VARCHAR, "varchar");
    registerColumnType(Types.LONGVARCHAR, "longvarchar");
    registerColumnType(Types.DATE, "date");
    registerColumnType(Types.TIME, "time");
    registerColumnType(Types.TIMESTAMP, "timestamp");
    registerColumnType(Types.BINARY, "blob");
    registerColumnType(Types.VARBINARY, "blob");
    registerColumnType(Types.LONGVARBINARY, "blob");
    // registerColumnType(Types.NULL, "null");
    registerColumnType(Types.BLOB, "blob");
    registerColumnType(Types.CLOB, "clob");
    registerColumnType(Types.BOOLEAN, "integer");

    registerFunction("concat", 
      new VarArgsSQLFunction(StringType.INSTANCE, "", "||", ""));
    registerFunction("mod", 
      new SQLFunctionTemplate(StringType.INSTANCE, "?1 % ?2"));
    registerFunction("substr", 
      new StandardSQLFunction("substr", StringType.INSTANCE));
    registerFunction("substring", 
      new StandardSQLFunction("substr", StringType.INSTANCE));
  }

  public boolean supportsIdentityColumns() {
    return true;
  }

  public boolean hasDataTypeInIdentityColumn() {
    return false; // As specify in NHibernate dialect
  }

  @Override
  public IdentityColumnSupport getIdentityColumnSupport() {
    return new SQLiteIdentityColumnSupport();
  }

  public String getIdentityColumnString() {
    // return "integer primary key autoincrement";
    return "integer";
  }

  public String getIdentitySelectString() {
    return "select last_insert_rowid()";
  }

  public boolean supportsLimit() {
    return true;
  }

  protected String getLimitString(String query, boolean hasOffset) {
    return new StringBuffer(query.length() + 20).append(query)
        .append(hasOffset ? " limit ? offset ?" : " limit ?")
        .toString();
  }

  public boolean supportsTemporaryTables() {
    return true;
  }

  public String getCreateTemporaryTableString() {
    return "create temporary table if not exists";
  }

  public boolean dropTemporaryTableAfterUse() {
    return false;
  }

  public boolean supportsCurrentTimestampSelection() {
    return true;
  }

  public boolean isCurrentTimestampSelectStringCallable() {
    return false;
  }

  public String getCurrentTimestampSelectString() {
    return "select current_timestamp";
  }

  public boolean supportsUnionAll() {
    return true;
  }

  public boolean hasAlterTable() {
    return false; // As specify in NHibernate dialect
  }

  public boolean dropConstraints() {
    return false;
  }

  public String getAddColumnString() {
    return "add column";
  }

  public String getForUpdateString() {
    return "";
  }

  public boolean supportsOuterJoinForUpdate() {
    return false;
  }

  public boolean supportsIfExistsBeforeTableName() {
    return true;
  }

  public boolean supportsCascadeDelete() {
    return false;
  }

  @Override
  public String getDropForeignKeyString() {
    return "";
  }

  @Override
  public String getAddForeignKeyConstraintString(String cn,
      String[] fk, String t, String[] pk, boolean rpk) {
    return "";
  }

  @Override
  public String getAddPrimaryKeyConstraintString(String constraintName) {
    return "";
  }
}

 

 

이다음 querydsl에 다음과 같이 코드를 작성했습니다.

@RequiredArgsConstructor
@Slf4j
public class UserGroupRepositoryImpl implements UserGroupRepositoryCustom {
  private final JPAQueryFactory factory;
  private final QueryUtil queryUtil;
  
  @Override
  public PageImpl<?> selectList(Pageable pageable, Map<String, Object> param) {
    JPAQuery<UserGroup> query = factory.selectFrom(userGroup)
        .offset(pageable.getOffset())
        .limit(pageable.getPageSize())
        ;

    List<UserGroup> list = query.fetch();

    return new PageImpl<>(list, pageable, NumberUtil.parseLong(param.get("totalCount")));
  }
}

 

그다음 parameter 값을 다음과 같이 호출합니다.

• page: 5
• size: 1

 

이 경우 다음과 같이 query가 실행됩니다.

limit 4 offset 1

 

즉, page번호가 개수에 들어가 버려서 정상적으로 페이징 처리가 되지 않습니다.

이때 해결 방법은 SQLDialect의 getLimitString method를 다음과 같이 수정하면 됩니다.

protected String getLimitString(String query, boolean hasOffset) {
  return new StringBuffer(query.length() + 20).append(query)
    .append(hasOffset ? " limit ?, ?" : " limit ?")
    .toString();
}