Supporting Calculated Columns

A calculated column can be used anywhere in a SELECT statement. If you don't need to use it in a WHERE clause, then it is easier to implement the org.mybatis.dynamic.sql.BasicColumn interface. An example follows:

import org.mybatis.dynamic.sql.BasicColumn;
import org.mybatis.dynamic.sql.render.RenderingContext;
import org.mybatis.dynamic.sql.util.FragmentAndParameters;

public class CountAll implements BasicColumn {

    private String alias;

    @Override
    public FragmentAndParameters render(RenderingContext renderingContext) {
        return FragmentAndParameters.fromFragment("count(*)"); //$NON-NLS-1$
    }

    @Override
    public Optional<String> alias() {
        return Optional.ofNullable(alias);
    }

    @Override
    public CountAll as(String alias) {
        CountAll copy = new CountAll();
        copy.alias = alias;
        return copy;
    }
}

This class is used to implement the count(*) function in a SELECT list. There are only three methods to implement:

1. render - the default renderers will write the value returned from this function into the select list - or the GROUP BY expression. If your item can be altered by a table alias, then here is where you change the return value based on the alias specified by the user. For a count(*) expression, a table alias never applies, so we just return the same value regardless of whether an alias has been specified by the user.
2. as - this method can be called by the user to add an alias to the column expression. In the method you should return a new instance of the object, with the alias passed by the user.
3. alias - this method is called by the default renderer to obtain the column alias for the select list. If there is no alias, then returning Optional.empty() will disable setting a column alias.

AbstractTypeConvertingFunction Example

The following function uses HSQLDB's TO_BASE64 function to calculate the BASE64 string for a binary field. Note that the function changes the data type from byte[] to String.

import java.sql.JDBCType;
import java.util.Optional;

import org.mybatis.dynamic.sql.BindableColumn;
import org.mybatis.dynamic.sql.render.RenderingContext;
import org.mybatis.dynamic.sql.select.function.AbstractTypeConvertingFunction;
import org.mybatis.dynamic.sql.util.FragmentAndParameters;

public class ToBase64 extends AbstractTypeConvertingFunction<byte[], String, ToBase64> {

   protected ToBase64(BindableColumn<byte[]> column) {
      super(column);
   }

   @Override
   public Optional<JDBCType> jdbcType() {
      return Optional.of(JDBCType.VARCHAR);
   }

   @Override
   public FragmentAndParameters render(RenderingContext renderingContext) {
      FragmentAndParameters renderedColumn = column.render(renderingContext);

      return FragmentAndParameters
              .withFragment("TO_BASE64(" + renderedColumn.fragment() + ")") //$NON-NLS-1$ //$NON-NLS-2$
              .withParameters(renderedColumn.parameters())
              .build();
   }

   @Override
   protected ToBase64 copy() {
      return new ToBase64(column);
   }

   public static ToBase64 toBase64(BindableColumn<byte[]> column) {
      return new ToBase64(column);
   }
}

AbstractUniTypeFunction Example

The following function implements the common database UPPER() function.

import org.mybatis.dynamic.sql.render.RenderingContext;
import org.mybatis.dynamic.sql.select.function.AbstractUniTypeFunction;
import org.mybatis.dynamic.sql.util.FragmentAndParameters;

public class Upper extends AbstractUniTypeFunction<String, Upper> {

   private Upper(BindableColumn<String> column) {
      super(column);
   }

   @Override
   public FragmentAndParameters render(RenderingContext renderingContext) {
      FragmentAndParameters renderedColumn = column.render(renderingContext);

      return FragmentAndParameters
              .withFragment("upper(" + renderedColumn.fragment() + ")") //$NON-NLS-1$ //$NON-NLS-2$
              .withParameters(renderedColumn.parameters())
              .build();
   }

   @Override
   protected Upper copy() {
      return new Upper(column);
   }

   public static Upper of(BindableColumn<String> column) {
      return new Upper(column);
   }
}

Note that FragmentAndParameters has a utility method that can simplify the implementation if you do not need to add any new parameters to the resulting fragment. For example, the UPPER function can be simplified as follows:

import org.mybatis.dynamic.sql.render.RenderingContext;
import org.mybatis.dynamic.sql.select.function.AbstractUniTypeFunction;
import org.mybatis.dynamic.sql.util.FragmentAndParameters;

public class Upper extends AbstractUniTypeFunction<String, Upper> {

   private Upper(BindableColumn<String> column) {
      super(column);
   }

   @Override
   public FragmentAndParameters render(RenderingContext renderingContext) {
      return = column.render(renderingContext).mapFragment(f -> "upper(" + f + ")"); //$NON-NLS-1$ //$NON-NLS-2$
   }

   @Override
   protected Upper copy() {
      return new Upper(column);
   }

   public static Upper of(BindableColumn<String> column) {
      return new Upper(column);
   }
}

OperatorFunction Example

The following function implements the concatenate operator. Note that the operator can be applied to list of columns of arbitrary length:

public class Concatenate<T> extends OperatorFunction<T> {

    protected Concatenate(BindableColumn<T> firstColumn, BasicColumn secondColumn,
            List<BasicColumn> subsequentColumns) {
        super("||", firstColumn, secondColumn, subsequentColumns); //$NON-NLS-1$
    }

    @Override
    protected Concatenate<T> copy() {
        return new Concatenate<>(column, secondColumn, subsequentColumns);
    }

    public static <T> Concatenate<T> concatenate(BindableColumn<T> firstColumn, BasicColumn secondColumn,
            BasicColumn... subsequentColumns) {
        return new Concatenate<>(firstColumn, secondColumn, Arrays.asList(subsequentColumns));
    }
}