docs/extending.html

<!DOCTYPE html>


<!--
 | Generated by Apache Maven Doxia Site Renderer 2.0.0-M18 from src/site/markdown/docs/extending.md at 03 Jun 2024
 | Rendered using Apache Maven Fluido Skin 2.0.0-M9
-->
<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <meta name="generator" content="Apache Maven Doxia Site Renderer 2.0.0-M18" />
    <title>MyBatis Dynamic SQL</title>
    <link rel="stylesheet" href="../css/apache-maven-fluido-2.0.0-M9.min.css" />
    <link rel="stylesheet" href="../css/site.css" />
    <link rel="stylesheet" href="../css/print.css" media="print" />
    <script src="../js/apache-maven-fluido-2.0.0-M9.min.js"></script>
  </head>
  <body>
    <div class="container-fluid container-fluid-top">
      <header>
        <div id="banner">
          <div class="pull-left"><div id="bannerLeft"><h1><a href="../docs/introduction.html">MyBatis Dynamic SQL</a></h1></div></div>
          <div class="pull-right"><div id="bannerRight"><h1><a href="http://www.mybatis.org/" class="externalLink"><img class="imageLink" src="https://mybatis.org/images/mybatis-logo.png" alt="MyBatis logo" /> MyBatis</a></h1></div></div>
          <div class="clear"><hr/></div>
        </div>

        <div id="breadcrumbs">
          <ul class="breadcrumb">
        <li id="publishDate">Last Published: 03 Jun 2024<span class="divider">|</span>
</li>
          <li id="projectVersion">Version: 1.5.2</li>
          </ul>
        </div>
      </header>
      <div class="row-fluid">
        <header id="leftColumn" class="span2">
          <nav class="well sidebar-nav">
  <ul class="nav nav-list">
   <li class="nav-header">User&apos;s Guide</li>
    <li><a href="../docs/introduction.html">Introduction</a></li>
    <li><a href="../docs/CHANGELOG.html">Change Log</a></li>
    <li><a href="../docs/quickStart.html">Quick Start</a></li>
    <li><a href="../docs/exceptions.html">Exceptions thrown by the Library</a></li>
    <li><a href="../docs/configuration.html">Configuration of the Library</a></li>
    <li><a href="../docs/databaseObjects.html">Modeling Database Objects</a></li>
    <li><a href="../docs/whereClauses.html"><span class="icon-chevron-down"></span>WHERE Clause Support</a>
     <ul class="nav nav-list">
      <li><a href="../docs/conditions.html">WHERE Conditions</a></li>
     </ul></li>
    <li><a href="../docs/select.html"><span class="icon-chevron-down"></span>SELECT Statements</a>
     <ul class="nav nav-list">
      <li><a href="../docs/caseExpressions.html">Case Expressions</a></li>
      <li><a href="../docs/complexQueries.html">Complex Queries</a></li>
     </ul></li>
    <li><a href="../docs/delete.html">DELETE Statements</a></li>
    <li><a href="../docs/insert.html">INSERT Statements</a></li>
    <li><a href="../docs/update.html">UPDATE Statements</a></li>
    <li><a href="../docs/subQueries.html">SubQuery Support</a></li>
    <li><a href="../docs/functions.html">Database Functions</a></li>
    <li><a href="../docs/mybatis3.html">MyBatis3 Support</a></li>
    <li><a href="../docs/spring.html">Spring Support</a></li>
    <li><a href="../docs/springBatch.html">Spring Batch Support</a></li>
    <li><a href="../docs/kotlinOverview.html"><span class="icon-chevron-right"></span>Kotlin Support</a></li>
    <li><a href="../docs/howItWorks.html">How it Works</a></li>
    <li class="active"><a>Extending the Library</a></li>
    <li><a href="../docs/codingStandards.html">Coding Standards</a></li>
    <li><a href="../docs/motivation.html">Motivation</a></li>
   <li class="nav-header">Project Documentation</li>
    <li><a href="../project-info.html"><span class="icon-chevron-right"></span>Project Information</a></li>
    <li><a href="../project-reports.html"><span class="icon-chevron-right"></span>Project Reports</a></li>
  </ul>
          </nav>
          <div class="well sidebar-nav">
            <div id="poweredBy">
              <div class="clear"></div>
              <div class="clear"></div>
<a href="https://maven.apache.org/" class="builtBy" target="_blank"><img class="builtBy" alt="Built by Maven" src="../images/logos/maven-feather.png" /></a>
            </div>
          </div>
        </header>
        <main id="bodyColumn" class="span10">
<section><a id="Extending_MyBatis_Dynamic_SQL"></a>
<h1>Extending MyBatis Dynamic SQL</h1>
<p>The library has been designed for extension from the very beginning.  We do not believe that the library
covers all possible uses, and we wanted to make it possible to add functionality that suits the needs of different
projects.</p>
<p>This page details the main extension points of the library.</p><section><a id="Extending_the_SELECT_Capabilities"></a>
<h2>Extending the SELECT Capabilities</h2>
<p>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.</p>
<table class="table table-striped">
<thead>
<tr class="a">
<th>Interface</th>
<th>Purpose</th></tr></thead><tbody>
<tr class="b">
<td><code>org.mybatis.dynamic.sql.BasicColumn</code></td>
<td>Use this interface if you want to add capabilities to a SELECT list or a GROUP BY expression. For example, using a database function.</td></tr>
<tr class="a">
<td><code>org.mybatis.dynamic.sql.BindableColumn</code></td>
<td>Use this interface if you want to add capabilities to a WHERE clause. For example, creating a custom condition.</td></tr></tbody>
</table>

<p>Rendering is the process of generating an appropriate SQL fragment to implement the function or calculated column.
The library will call a method <code>render(RenderingContext)</code> in your implementation. This method should return an
instance of <code>FragmentAndParameters</code> containing your desired SQL fragment and any bind parameters needed. Bind
parameter markers can be calculated by calling the <code>RenderingContext.calculateParameterInfo()</code> method. That method will
return a properly formatted bind marker for the SQL string, and a matching Map key you should use in your parameter map.
In general, you do not need to worry about adding spacing, commas, etc. before or after your fragment - the library
will properly format the final statement from all the different fragments.</p>
<p>See the following sections for examples.</p><section><a id="Supporting_Calculated_Columns"></a>
<h3>Supporting Calculated Columns</h3>
<p>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 <code>org.mybatis.dynamic.sql.BasicColumn</code> interface.  An example follows:</p>

<pre><code class="language-java">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(&quot;count(*)&quot;); //$NON-NLS-1$
    }

    @Override
    public Optional&lt;String&gt; alias() {
        return Optional.ofNullable(alias);
    }

    @Override
    public CountAll as(String alias) {
        CountAll copy = new CountAll();
        copy.alias = alias;
        return copy;
    }
}
</code></pre>
<p>This class is used to implement the <code>count(*)</code> function in a SELECT list.  There are only three methods to implement:</p>
<ol style="list-style-type: decimal;">

<li><code>render</code> - 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 <code>count(*)</code> expression, a table alias never applies, so we just
return the same value regardless of whether an alias has been specified by the user.</li>
<li><code>as</code> - 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.</li>
<li><code>alias</code> - 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.</li>
</ol></section></section><section><a id="Writing_Custom_Functions"></a>
<h2>Writing Custom Functions</h2>
<p>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.</p>
<p>The supplied functions are all in the <code>org.mybatis.dynamic.sql.select.function</code> package. They are all implemented
as <code>BindableColumn</code> - meaning they can appear in a select list or a where clause.</p>
<p>We provide some base classes that you can easily extend to write functions of your own. Those classes are as follows:</p>
<p>Note: the base classes are all in the <code>org.mybatis.dynamic.sql.select.function</code> package.</p>
<table class="table table-striped">
<thead>
<tr class="a">
<th>Interface</th>
<th>Purpose</th></tr></thead><tbody>
<tr class="b">
<td><code>o.m.d.s.s.f.AbstractTypeConvertingFunction</code></td>
<td>Extend this class if you want to build a function that changes a column data type. For example, using a database function to calculate the Base64 String for a binary field.</td></tr>
<tr class="a">
<td><code>o.m.d.s.s.f.AbstractUniTypeFunction</code></td>
<td>Extend this class if you want to build a function that does not change a column data type. For example UPPER(), LOWER(), etc.</td></tr>
<tr class="b">
<td><code>o.m.d.s.s.f.OperatorFunction</code></td>
<td>Extend this class if you want to build a function the implements an operator. For example column1 + column2.</td></tr></tbody>
</table>
<section><a id="AbstractTypeConvertingFunction_Example"></a>
<h3>AbstractTypeConvertingFunction Example</h3>
<p>The following function uses HSQLDB's <code>TO_BASE64</code> function to calculate the BASE64 string for a binary field. Note that
the function changes the data type from <code>byte[]</code> to <code>String</code>.</p>

<pre><code class="language-java">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&lt;byte[], String, ToBase64&gt; {

   protected ToBase64(BindableColumn&lt;byte[]&gt; column) {
      super(column);
   }

   @Override
   public Optional&lt;JDBCType&gt; jdbcType() {
      return Optional.of(JDBCType.VARCHAR);
   }

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

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

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

   public static ToBase64 toBase64(BindableColumn&lt;byte[]&gt; column) {
      return new ToBase64(column);
   }
}
</code></pre></section><section><a id="AbstractUniTypeFunction_Example"></a>
<h3>AbstractUniTypeFunction Example</h3>
<p>The following function implements the common database <code>UPPER()</code> function.</p>

<pre><code class="language-java">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&lt;String, Upper&gt; {

   private Upper(BindableColumn&lt;String&gt; column) {
      super(column);
   }

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

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

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

   public static Upper of(BindableColumn&lt;String&gt; column) {
      return new Upper(column);
   }
}
</code></pre>
<p>Note that <code>FragmentAndParameters</code> 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:</p>

<pre><code class="language-java">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&lt;String, Upper&gt; {

   private Upper(BindableColumn&lt;String&gt; column) {
      super(column);
   }

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

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

   public static Upper of(BindableColumn&lt;String&gt; column) {
      return new Upper(column);
   }
}
</code></pre></section><section><a id="OperatorFunction_Example"></a>
<h3>OperatorFunction Example</h3>
<p>The following function implements the concatenate operator. Note that the operator can be applied to list of columns of
arbitrary length:</p>

<pre><code class="language-java">public class Concatenate&lt;T&gt; extends OperatorFunction&lt;T&gt; {

    protected Concatenate(BindableColumn&lt;T&gt; firstColumn, BasicColumn secondColumn,
            List&lt;BasicColumn&gt; subsequentColumns) {
        super(&quot;||&quot;, firstColumn, secondColumn, subsequentColumns); //$NON-NLS-1$
    }

    @Override
    protected Concatenate&lt;T&gt; copy() {
        return new Concatenate&lt;&gt;(column, secondColumn, subsequentColumns);
    }

    public static &lt;T&gt; Concatenate&lt;T&gt; concatenate(BindableColumn&lt;T&gt; firstColumn, BasicColumn secondColumn,
            BasicColumn... subsequentColumns) {
        return new Concatenate&lt;&gt;(firstColumn, secondColumn, Arrays.asList(subsequentColumns));
    }
}
</code></pre></section></section><section><a id="Writing_Custom_Rendering_Strategies"></a>
<h2>Writing Custom Rendering Strategies</h2>
<p>A RenderingStrategy is used to format the parameter placeholders in generated SQL. The library ships with two built-in
rendering strategies:</p>
<ol style="list-style-type: decimal;">

<li>A strategy that is suitable for MyBatis3.  This strategy generates placeholders in the format required by MyBatis3
(for example <code>#{foo,jdbcType=INTEGER}</code>).</li>
<li>A strategy that is suitable for the Spring NamedParameterJDBCTemplate. This strategy generates placeholders in the
format required by Spring (for example <code>:foo</code>).</li>
</ol>
<p>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 (<code>?</code>) for all parameters.</p>

<pre><code class="language-java">import org.mybatis.dynamic.sql.BindableColumn;
import org.mybatis.dynamic.sql.render.RenderingStrategy;

public class PlainJDBCRenderingStrategy extends RenderingStrategy {
    @Override
    public String getFormattedJdbcPlaceholder(BindableColumn&lt;?&gt; column, String prefix, String parameterName) {
        return &quot;?&quot;;
    }

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

</code></pre>
<p>The library will pass the following parameters to the <code>getFormattedJdbcPlaceholder</code> method:</p>
<ol style="list-style-type: decimal;">

<li><code>column</code> - the column definition for the current placeholder</li>
<li><code>prefix</code> - For INSERT statements the value will be &#x201c;record&#x201d;, for all other statements (including inserts with
selects) the value will be &#x201c;parameters&#x201d;</li>
<li><code>parameterName</code> - 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.</li>
</ol></section><section><a id="Writing_Custom_Renderers"></a>
<h2>Writing Custom Renderers</h2>
<p>SQL rendering is accomplished by classes that are decoupled from the SQL model classes.  All the model classes have a
<code>render</code> 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.</p>
<p>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.</p></section></section>
        </main>
      </div>
    </div>
    <hr/>
    <footer>
      <div class="container-fluid">
        <div class="row-fluid">
            <p>©      2016–2024
<a href="https://www.mybatis.org/">MyBatis.org</a>
</p>
        </div>
      </div>
    </footer>
<script>
  if(anchors) {
    anchors.add();
  }
</script>
  </body>
</html>