mybatis/mybatis-dynamic-sql

Add support for sql-server top x syntax

FrancisS opened this issue · 9 comments

FrancisS commented

There appears to be no support for SQL Server "SELECT TOP x" syntax. Support for MySql "LIMIT x" and oracle "FETCH FIRST x ROWS ONLY" does exist. Unless I am missing something it would be nice to add support for SQL server syntax.

FrancisS commented

Sorry I mangled that comment. I mean to request SQL Server style "SELECT TOP x" support and was mentioning that MySql and Oracle syntax was supported.

jeffgbutler commented

Ah...

It's been a long time since I did anything with SQL Server so I'm not familiar with TOP. From the docs, it looks like something you could do with a function you could write - see this as an example: https://github.com/mybatis/mybatis-dynamic-sql/blob/master/src/main/java/org/mybatis/dynamic/sql/select/function/Lower.java

If you want the values of TOP to be passed as prepared statement parameters, then you'll need to wait until there is a fix for #655 - which should come soon.

FrancisS commented

I followed that pattern and this seems to work. However its only valid to use with the first column in the select list so its a little bit awkward.

public class Top<T> extends AbstractUniTypeFunction<T, Top<T>> {
    private final int number;

    private Top(BindableColumn<T> column, int number) {
        super(column);
        this.number = number;
    }

    @Override
    public String renderWithTableAlias(TableAliasCalculator tableAliasCalculator) {
        return String.format("TOP %s ", number) + column.renderWithTableAlias(tableAliasCalculator);
    }

    @Override
    protected Top<T> copy() {
        return new Top<>(column, number);
    }
    public static <T> Top<T> of(BindableColumn<T> column, int number) {
        return new Top<>(column, number);
    }

}
jeffgbutler commented

Glad to know it worked. I'm not sure what you mean about it being awkward - is that because of something in this library, or because of a SQL Server restriction?

FrancisS commented

Only because its possible to use the function in a way that will produce invalid SQL. Its syntactically possible to use it with any column but its only semantically valid if used for the first one.

jeffgbutler commented

That makes sense. Not sure there's anything we can do about that. I suppose you could create a little convenience method that would make it a bit more clear:

static QueryExpressionDSL.FromGatherer<SelectModel> select(Top<?> top,
        BindableColumn<?>...bindableColumns) {
    List<BindableColumn<?>> list = new ArrayList<>();
    list.add(top);
    list.addAll(Arrays.asList(bindableColumns));

    return SqlBuilder.select(list);
}