Introduction to MyBatis Dynamic SQL

1. Introduction

MyBatis Dynamic SQL is a library for generating SQL statements in a typesafe way. It helps us ensure that our SQL syntax and parameter bindings are valid. In addition, the SQL generated is based on class definitions representing our database, so we can leverage the compiler to help ensure our SQL is correct.

In this tutorial, we’re going to have a look at MyBatis Dynamic SQL. We’ll see what it is, what we can do with it, and how to use it.

2. Dependencies

Before using MyBatis Dynamic SQL, we need to include the latest version in our build, which is 1.5.2 at the time of writing.

If we’re using Maven, we can include this dependency in our pom.xml file:

<dependency>
    <groupId>org.mybatis.dynamic-sql</groupId>
    <artifactId>mybatis-dynamic-sql</artifactId>
    <version>1.5.2</version>
</dependency>

At this point, we’re ready to start using it in our application.

3. Database Objects

MyBatis Dynamic SQL ensures our SQL generation is typesafe by using types that represent the structure of our database. This includes classes that we write in our application to represent the database tables and columns we’re working with.

All of these database objects are simple classes that extend the SqlTable class:

public class User extends SqlTable {

    public User() {
        super("users");
    }
}

The constructor provides the name of the table within the database. In this case, we represent a table called users in the database.

We can then add column definitions to our table. These are done as fields in our class, generated with the column helper:

public class User extends AliasableSqlTable<User> {
    public final SqlColumn<Integer> userId = column("user_id");
    public final SqlColumn<String> userName = column("username");
}

Making these fields public allows us to reference them from outside the class, which we need to do to generate SQL.

4. Generating SQL

Once we’ve built our database objects, we need to be able to generate SQL. MyBatis Dynamic SQL starts all query building using a static method from the SqlBuilder class – for example, SqlBuilder.countFrom(). This determines the type of query we’re going to build, and returns suitable types for us to use:

CountDSL<SelectModel> count = SqlBuilder.countFrom(user);

Here we’re providing an instance of our database object to the countFrom() method. This indicates the table that we’re going to work with.

We can further chain the result of this with appropriate methods, as we’ll see later. Ultimately, we call a build() method that returns a model class for our SQL statement:

SelectModel model = SqlBuilder.countFrom(user)
  .build();

From here, we can then call a render() method that will actually render the SQL statement:

SelectStatementProvider sqlStatement = SqlBuilder.select(user.allColumns())
  .from(user)
  .build()
  .render(RenderingStrategies.SPRING_NAMED_PARAMETER);

This method takes a rendering strategy to use. Two standard options are available to us – one for working with MyBatis3 and one for working with Spring.

The statement provider that’s returned from the render() call then gives us access to the actual SQL and to the bind parameters we need to call the SQL:

String sql = sqlStatement.getSelectStatement();
Map<String, Object> binds = sqlStatement.getParameters();

We can then provide these to our database call to actually perform our query.

5. Select Statements

Now that we know how to generate our SQL and bind parameters with our database objects, we’re ready to start building some statements.

Select statements are some of the more complex we can build, since they have various parts and ways they can work.

All select statements start with one of a few static methods:

• select() – starts building a normal SELECT statement to return a set of columns.
• selectDistinct() – starts building a SELECT DISTINCT statement to return a set of columns.
• countFrom() – starts building a SELECT COUNT(*) statement to return a count from a table.
• countColumn() – starts building a SELECT COUNT(column) statement to count non-null values in the specified column.
• countDistinctColumn() – starts building a SELECT COUNT(DISTINCT column) statement to count distinct non-null values in the specified column.

With the exception of countFrom(), each of these then needs to be provided with the table to select from:

User user = new User();
SelectStatementProvider sql = SqlBuilder.select(user.allColumns())
  .from(user)
  .build()
  .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
// SELECT * FROM users

We also need to specify which columns we want to return. This can be any of the column definitions we created earlier, or the special value allColumns().

We can also use some special aggregate values for our selected columns. The SqlBuilder class provides access to methods such as count(), max(), sum(), and so on. We can use these exactly the same as any other column definition, and the result will be exactly as expected.

5.1. Where Clauses

By default, our select statements will just return everything. We can add WHERE clauses to filter the responses, though. These start using the where() method after the table has been specified:

SelectStatementProvider sql = SqlBuilder.select(user.allColumns())
  .from(user)
  .where(user.userName, SqlBuilder.isEqualTo("baeldung"))
  .build()
  .render(RenderingStrategies.SPRING_NAMED_PARAMETER);

Here, the where() method takes the column to compare and the comparison to use. The SquBuilder class has static methods for all the comparisons we want to make. There are also alternative versions that can be used for more complex conditions, such as for EXISTS clauses with sub-selects.

MyBatis Dynamic SQL uses generics here to ensure comparisons are typesafe. The types we can use for the comparisons must be suitable for the type of column we’re comparing. Here, the userName column is a SqlColumn<String>, so the isEqualTo() method must also take a string.

When generating this SQL, we now get a bind value. The SQL generated from the above would be:

SELECT * FROM users WHERE username = :p1

and the SelectStatementProvider.getParameters() method returns a map containing the entry p1 with our value.

We can also build more complex WHERE clauses using and() and or():

SelectStatementProvider sql = SqlBuilder.select(user.allColumns())
  .from(user)
  .where(user.userName, SqlBuilder.isEqualTo("baeldung"))
  .or(user.userId, SqlBuilder.isGreaterThan(5))
  .build()
  .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
// SELECT * FROM users WHERE username = :p1 OR user_id > :p2

With this, we can build statements that are as complex as needed whilst still being typesafe and correctly balanced.

5.2. Joins

So far, we’ve seen how to write SQL statements that reference a single table, but we often need to join multiple tables. MyBatis Dynamic SQL allows us to do this using the join() method when we’re building the SQL:

SelectStatementProvider sql = SqlBuilder.select(post.allColumns())
  .from(post)
  .join(user).on(user.userId, SqlBuilder.equalTo(post.posterId))
  .build()
  .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
// SELECT posts.* FROM posts JOIN users ON users.user_id = posts.poster_id

Here, the join() method takes the database object for the table we’re joining to. We then need to use the on() method to indicate how the join works. Note that this uses equalTo instead of isEqualTo. This is because it’s a different type of condition and therefore needs different Java types for type safety.

We also have a few different join methods that we can use, depending on exactly what we’re trying to achieve:

• join() – this builds a standard JOIN … ON … statement.
• leftJoin() – this builds a LEFT JOIN … ON … statement.
• rightJoin() – this builds a LEFT JOIN … ON … statement.
• fullJoin() – this builds a FULL JOIN … ON … statement.

All of these work in the same way, with the only difference being the type of join that’s generated.

6. Other Statements

In addition to SELECT statements, we can also build other SQL statements.

DELETE statements are the most similar to what we’ve already seen. We start these using the SqlBuilder.deleteFrom() method and then provide a suitable WHERE clause:

DeleteStatementProvider sql = SqlBuilder.deleteFrom(user)
  .where(user.userId, isEqualTo(1))
  .build()
  .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
// DELETE FROM users WHERE user_id = :p1

UPDATE statements are similar, though they also have support for setting the values of fields as part of the statement too:

UpdateStatementProvider sql = SqlBuilder.update(user)
  .set(user.userName).equalTo("Baeldung")
  .where(user.userId, isEqualTo(1))
  .build()
  .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
// UPDATE users SET username = :p1 WHERE user_id = :p2

Finally, INSERT statements require us to provide the new values but don’t support a WHERE clause:

GeneralInsertStatementProvider sql = SqlBuilder.insertInto(user)
  .set(user.userId).toValue(2)
  .set(user.userName).toValue("Baeldung")
  .build()
  .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
// INSERT INTO users(user_id, username) VALUES (:p1, :p2)

All of these ultimately return an appropriate statement provider instance that we can then use to get the SQL and bind parameters from to execute our statement against the database.

8. Summary

This was a quick introduction to MyBatis Dynamic SQL. There’s a lot more that we can do with this library. Next time we need to generate SQL statements safely, why not give it a try?

As usual, all of the examples from this article are available over on GitHub.