Extending the SELECT Capabilities

The SELECT support is the most complex part of the library, and also the part of the library that is most likely to be extended. There are two main interfaces involved with extending the SELECT support. Picking which interface to implement is dependent on how you want to use your extension.

See the following sections for examples.

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;
    }
}

Writing Custom Functions

Relational database vendors provide hundreds of functions in their SQL dialects to aid with queries and offload processing to the database servers. This library does not try to implement every function that exists. This library also does not provide any abstraction over the different functions on different databases. For example, bitwise operator support is non-standard, and it would be difficult to provide a function in this library that worked on every database. So we take the approach of supplying examples for a few very common functions, and making it relatively easy to write your own functions.

The supplied functions are all in the org.mybatis.dynamic.sql.select.function package. They are all implemented as BindableColumn - meaning they can appear in a select list or a where clause.

We provide some base classes that you can easily extend to write functions of your own. Those classes are as follows:

Note: the base classes are all in the org.mybatis.dynamic.sql.select.function package.

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);
   }
}

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);
   }
}

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));
    }
}

Writing Custom Rendering Strategies

A RenderingStrategy is used to format the parameter placeholders in generated SQL. The library ships with two built-in rendering strategies:

1. A strategy that is suitable for MyBatis3. This strategy generates placeholders in the format required by MyBatis3 (for example #{foo,jdbcType=INTEGER}).
2. A strategy that is suitable for the Spring NamedParameterJDBCTemplate. This strategy generates placeholders in the format required by Spring (for example :foo).

You can write a custom rendering strategy if you want to use the library with some other framework. For example, if you wanted to use the library to generate SQL that could be prepared directly by JDBC, you could write a rendering strategy that simply uses the question mark (?) for all parameters.

import org.mybatis.dynamic.sql.BindableColumn;
import org.mybatis.dynamic.sql.render.RenderingStrategy;

public class PlainJDBCRenderingStrategy extends RenderingStrategy {
    @Override
    public String getFormattedJdbcPlaceholder(BindableColumn<?> column, String prefix, String parameterName) {
        return "?";
    }

    @Override
    public String getFormattedJdbcPlaceholder(String prefix, String parameterName) {
        return "?";
    }
}

The library will pass the following parameters to the getFormattedJdbcPlaceholder method:

1. column - the column definition for the current placeholder
2. prefix - For INSERT statements the value will be “record”, for all other statements (including inserts with selects) the value will be “parameters”
3. parameterName - this will be the unique name for the parameter. For INSERT statements, the name will be the property of the inserted record mapped to this parameter. For all other statements (including inserts with selects) a unique name will be generated by the library. That unique name will also be used to place the value of the parameter into the parameters Map.

Writing Custom Renderers

SQL rendering is accomplished by classes that are decoupled from the SQL model classes. All the model classes have a render method that calls the built-in default renderers, but this is completely optional, and you do not need to use it. You can write your own rendering support if you are dissatisfied with the SQL produced by the default renderers.

Writing a custom renderer is quite complex. If you want to undertake that task, we suggest that you take the time to understand how the default renderers work first. Feel free to ask questions about this topic on the MyBatis mailing list.