DynamicSqlSourceTest.java
/*
* Copyright 2009-2025 the original author or authors.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* https://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.apache.ibatis.builder.xml.dynamic;
import static org.junit.jupiter.api.Assertions.assertEquals;
import java.io.IOException;
import java.io.Reader;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Stream;
import org.apache.ibatis.BaseDataTest;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.scripting.xmltags.ChooseSqlNode;
import org.apache.ibatis.scripting.xmltags.DynamicSqlSource;
import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;
import org.apache.ibatis.scripting.xmltags.IfSqlNode;
import org.apache.ibatis.scripting.xmltags.MixedSqlNode;
import org.apache.ibatis.scripting.xmltags.SetSqlNode;
import org.apache.ibatis.scripting.xmltags.SqlNode;
import org.apache.ibatis.scripting.xmltags.StaticTextSqlNode;
import org.apache.ibatis.scripting.xmltags.TextSqlNode;
import org.apache.ibatis.scripting.xmltags.WhereSqlNode;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.params.ParameterizedTest;
import org.junit.jupiter.params.provider.Arguments;
import org.junit.jupiter.params.provider.MethodSource;
class DynamicSqlSourceTest extends BaseDataTest {
@Test
void shouldDemonstrateSimpleExpectedTextWithNoLoopsOrConditionals() throws Exception {
final String expected = "SELECT * FROM BLOG";
final MixedSqlNode sqlNode = mixedContents(new TextSqlNode(expected));
DynamicSqlSource source = createDynamicSqlSource(sqlNode);
BoundSql boundSql = source.getBoundSql(null);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldDemonstrateMultipartExpectedTextWithNoLoopsOrConditionals() throws Exception {
final String expected = "SELECT * FROM BLOG WHERE ID = ?";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("SELECT * FROM BLOG"),
new TextSqlNode("WHERE ID = ?"));
BoundSql boundSql = source.getBoundSql(null);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldConditionallyIncludeWhere() throws Exception {
final String expected = "SELECT * FROM BLOG WHERE ID = ?";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("SELECT * FROM BLOG"),
new IfSqlNode(mixedContents(new TextSqlNode("WHERE ID = ?")), "true"));
BoundSql boundSql = source.getBoundSql(null);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldConditionallyExcludeWhere() throws Exception {
final String expected = "SELECT * FROM BLOG";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("SELECT * FROM BLOG"),
new IfSqlNode(mixedContents(new TextSqlNode("WHERE ID = ?")), "false"));
BoundSql boundSql = source.getBoundSql(null);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldConditionallyDefault() throws Exception {
final String expected = "SELECT * FROM BLOG WHERE CATEGORY = 'DEFAULT'";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("SELECT * FROM BLOG"),
new ChooseSqlNode(new ArrayList<SqlNode>() {
private static final long serialVersionUID = 1L;
{
add(new IfSqlNode(mixedContents(new TextSqlNode("WHERE CATEGORY = ?")), "false"));
add(new IfSqlNode(mixedContents(new TextSqlNode("WHERE CATEGORY = 'NONE'")), "false"));
}
}, mixedContents(new TextSqlNode("WHERE CATEGORY = 'DEFAULT'"))));
BoundSql boundSql = source.getBoundSql(null);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldConditionallyChooseFirst() throws Exception {
final String expected = "SELECT * FROM BLOG WHERE CATEGORY = ?";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("SELECT * FROM BLOG"),
new ChooseSqlNode(new ArrayList<SqlNode>() {
private static final long serialVersionUID = 1L;
{
add(new IfSqlNode(mixedContents(new TextSqlNode("WHERE CATEGORY = ?")), "true"));
add(new IfSqlNode(mixedContents(new TextSqlNode("WHERE CATEGORY = 'NONE'")), "false"));
}
}, mixedContents(new TextSqlNode("WHERE CATEGORY = 'DEFAULT'"))));
BoundSql boundSql = source.getBoundSql(null);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldConditionallyChooseSecond() throws Exception {
final String expected = "SELECT * FROM BLOG WHERE CATEGORY = 'NONE'";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("SELECT * FROM BLOG"),
new ChooseSqlNode(new ArrayList<SqlNode>() {
private static final long serialVersionUID = 1L;
{
add(new IfSqlNode(mixedContents(new TextSqlNode("WHERE CATEGORY = ?")), "false"));
add(new IfSqlNode(mixedContents(new TextSqlNode("WHERE CATEGORY = 'NONE'")), "true"));
}
}, mixedContents(new TextSqlNode("WHERE CATEGORY = 'DEFAULT'"))));
BoundSql boundSql = source.getBoundSql(null);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldTrimWHEREInsteadOfANDForFirstCondition() throws Exception {
final String expected = "SELECT * FROM BLOG WHERE ID = ?";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("SELECT * FROM BLOG"),
new WhereSqlNode(new Configuration(),
mixedContents(new IfSqlNode(mixedContents(new TextSqlNode(" and ID = ? ")), "true"),
new IfSqlNode(mixedContents(new TextSqlNode(" or NAME = ? ")), "false"))));
BoundSql boundSql = source.getBoundSql(null);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldTrimWHEREANDWithLFForFirstCondition() throws Exception {
final String expected = "SELECT * FROM BLOG WHERE \n ID = ?";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("SELECT * FROM BLOG"),
new WhereSqlNode(new Configuration(),
mixedContents(new IfSqlNode(mixedContents(new TextSqlNode(" and\n ID = ? ")), "true"))));
BoundSql boundSql = source.getBoundSql(null);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldTrimWHEREANDWithCRLFForFirstCondition() throws Exception {
final String expected = "SELECT * FROM BLOG WHERE \r\n ID = ?";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("SELECT * FROM BLOG"),
new WhereSqlNode(new Configuration(),
mixedContents(new IfSqlNode(mixedContents(new TextSqlNode(" and\r\n ID = ? ")), "true"))));
BoundSql boundSql = source.getBoundSql(null);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldTrimWHEREANDWithTABForFirstCondition() throws Exception {
final String expected = "SELECT * FROM BLOG WHERE \t ID = ?";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("SELECT * FROM BLOG"),
new WhereSqlNode(new Configuration(),
mixedContents(new IfSqlNode(mixedContents(new TextSqlNode(" and\t ID = ? ")), "true"))));
BoundSql boundSql = source.getBoundSql(null);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldTrimWHEREORWithLFForFirstCondition() throws Exception {
final String expected = "SELECT * FROM BLOG WHERE \n ID = ?";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("SELECT * FROM BLOG"), new WhereSqlNode(
new Configuration(), mixedContents(new IfSqlNode(mixedContents(new TextSqlNode(" or\n ID = ? ")), "true"))));
BoundSql boundSql = source.getBoundSql(null);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldTrimWHEREORWithCRLFForFirstCondition() throws Exception {
final String expected = "SELECT * FROM BLOG WHERE \r\n ID = ?";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("SELECT * FROM BLOG"),
new WhereSqlNode(new Configuration(),
mixedContents(new IfSqlNode(mixedContents(new TextSqlNode(" or\r\n ID = ? ")), "true"))));
BoundSql boundSql = source.getBoundSql(null);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldTrimWHEREORWithTABForFirstCondition() throws Exception {
final String expected = "SELECT * FROM BLOG WHERE \t ID = ?";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("SELECT * FROM BLOG"), new WhereSqlNode(
new Configuration(), mixedContents(new IfSqlNode(mixedContents(new TextSqlNode(" or\t ID = ? ")), "true"))));
BoundSql boundSql = source.getBoundSql(null);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldTrimWHEREInsteadOfORForSecondCondition() throws Exception {
final String expected = "SELECT * FROM BLOG WHERE NAME = ?";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("SELECT * FROM BLOG"),
new WhereSqlNode(new Configuration(),
mixedContents(new IfSqlNode(mixedContents(new TextSqlNode(" and ID = ? ")), "false"),
new IfSqlNode(mixedContents(new TextSqlNode(" or NAME = ? ")), "true"))));
BoundSql boundSql = source.getBoundSql(null);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldTrimWHEREInsteadOfANDForBothConditions() throws Exception {
final String expected = "SELECT * FROM BLOG WHERE ID = ? OR NAME = ?";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("SELECT * FROM BLOG"),
new WhereSqlNode(new Configuration(),
mixedContents(new IfSqlNode(mixedContents(new TextSqlNode(" and ID = ? ")), "true"),
new IfSqlNode(mixedContents(new TextSqlNode("OR NAME = ? ")), "true"))));
BoundSql boundSql = source.getBoundSql(null);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldTrimNoWhereClause() throws Exception {
final String expected = "SELECT * FROM BLOG";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("SELECT * FROM BLOG"),
new WhereSqlNode(new Configuration(),
mixedContents(new IfSqlNode(mixedContents(new TextSqlNode(" and ID = ? ")), "false"),
new IfSqlNode(mixedContents(new TextSqlNode("OR NAME = ? ")), "false"))));
BoundSql boundSql = source.getBoundSql(null);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldTrimSETInsteadOfCOMMAForBothConditions() throws Exception {
final String expected = "UPDATE BLOG SET ID = ?, NAME = ?";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("UPDATE BLOG"),
new SetSqlNode(new Configuration(),
mixedContents(new IfSqlNode(mixedContents(new TextSqlNode(" ID = ?, ")), "true"),
new IfSqlNode(mixedContents(new TextSqlNode(" NAME = ?, ")), "true"))));
BoundSql boundSql = source.getBoundSql(null);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldTrimCommaAfterSET() throws Exception {
final String expected = "UPDATE BLOG SET NAME = ?";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("UPDATE BLOG"),
new SetSqlNode(new Configuration(),
mixedContents(new IfSqlNode(mixedContents(new TextSqlNode("ID = ?")), "false"),
new IfSqlNode(mixedContents(new TextSqlNode(", NAME = ?")), "true"))));
BoundSql boundSql = source.getBoundSql(null);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldTrimNoSetClause() throws Exception {
final String expected = "UPDATE BLOG";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("UPDATE BLOG"),
new SetSqlNode(new Configuration(),
mixedContents(new IfSqlNode(mixedContents(new TextSqlNode(" , ID = ? ")), "false"),
new IfSqlNode(mixedContents(new TextSqlNode(", NAME = ? ")), "false"))));
BoundSql boundSql = source.getBoundSql(null);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldIterateOnceForEachItemInCollection() throws Exception {
final HashMap<String, String[]> parameterObject = new HashMap<>() {
private static final long serialVersionUID = 1L;
{
put("array", new String[] { "one", "two", "three" });
}
};
final String expected = "SELECT * FROM BLOG WHERE ID in ( one = ? AND two = ? AND three = ? )";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("SELECT * FROM BLOG WHERE ID in"),
new ForEachSqlNode(new Configuration(), mixedContents(new TextSqlNode("${item} = #{item}")), "array", "index",
"item", "(", ")", "AND"));
BoundSql boundSql = source.getBoundSql(parameterObject);
assertEquals(expected, boundSql.getSql());
assertEquals(3, boundSql.getParameterMappings().size());
assertEquals("item", boundSql.getParameterMappings().get(0).getProperty());
assertEquals("item", boundSql.getParameterMappings().get(1).getProperty());
assertEquals("item", boundSql.getParameterMappings().get(2).getProperty());
}
@Test
void shouldHandleOgnlExpression() throws Exception {
final HashMap<String, String> parameterObject = new HashMap<>() {
private static final long serialVersionUID = 1L;
{
put("name", "Steve");
}
};
final String expected = "Expression test: 3 / yes.";
DynamicSqlSource source = createDynamicSqlSource(
new TextSqlNode("Expression test: ${name.indexOf('v')} / ${name in {'Bob', 'Steve'\\} ? 'yes' : 'no'}."));
BoundSql boundSql = source.getBoundSql(parameterObject);
assertEquals(expected, boundSql.getSql());
}
@Test
void shouldSkipForEachWhenCollectionIsEmpty() throws Exception {
final HashMap<String, Integer[]> parameterObject = new HashMap<>() {
private static final long serialVersionUID = 1L;
{
put("array", new Integer[] {});
}
};
final String expected = "SELECT * FROM BLOG";
DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("SELECT * FROM BLOG"),
new ForEachSqlNode(new Configuration(), mixedContents(new TextSqlNode("#{item}")), "array", null, "item",
"WHERE id in (", ")", ","));
BoundSql boundSql = source.getBoundSql(parameterObject);
assertEquals(expected, boundSql.getSql());
assertEquals(0, boundSql.getParameterMappings().size());
}
@Test
void shouldPerformStrictMatchOnForEachVariableSubstitution() throws Exception {
final Map<String, Object> param = new HashMap<>();
final Map<String, String> uuu = new HashMap<>();
uuu.put("u", "xyz");
List<Bean> uuuu = new ArrayList<>();
uuuu.add(new Bean("bean id"));
param.put("uuu", uuu);
param.put("uuuu", uuuu);
DynamicSqlSource source = createDynamicSqlSource(
new TextSqlNode("INSERT INTO BLOG (ID, NAME, NOTE, COMMENT) VALUES"),
new ForEachSqlNode(new Configuration(),
mixedContents(
new TextSqlNode("#{uuu.u}, #{u.id}, #{ u,typeHandler=org.apache.ibatis.type.StringTypeHandler},"
+ " #{u:VARCHAR,typeHandler=org.apache.ibatis.type.StringTypeHandler}")),
"uuuu", "uu", "u", "(", ")", ","));
BoundSql boundSql = source.getBoundSql(param);
assertEquals(4, boundSql.getParameterMappings().size());
assertEquals("uuu.u", boundSql.getParameterMappings().get(0).getProperty());
assertEquals("u.id", boundSql.getParameterMappings().get(1).getProperty());
assertEquals("u", boundSql.getParameterMappings().get(2).getProperty());
assertEquals("u", boundSql.getParameterMappings().get(3).getProperty());
}
@Test
void cornerCase_ForeachComesFirst() throws Exception {
final Map<String, Object> param = new HashMap<>();
List<Bean> beans = new ArrayList<>();
beans.add(new Bean("bean id 1"));
beans.add(new Bean("bean id 2"));
param.put("beans", beans);
DynamicSqlSource source = createDynamicSqlSource(new ForEachSqlNode(new Configuration(),
mixedContents(new TextSqlNode("#{b.id}")), "beans", false, null, "b", "(", ")", ","));
BoundSql boundSql = source.getBoundSql(param);
assertEquals(2, boundSql.getParameterMappings().size());
assertEquals("b.id", boundSql.getParameterMappings().get(0).getProperty());
assertEquals("b.id", boundSql.getParameterMappings().get(1).getProperty());
}
private DynamicSqlSource createDynamicSqlSource(SqlNode... contents) throws IOException, SQLException {
createBlogDataSource();
final String resource = "org/apache/ibatis/builder/MapperConfig.xml";
final Reader reader = Resources.getResourceAsReader(resource);
SqlSessionFactory sqlMapper = new SqlSessionFactoryBuilder().build(reader);
Configuration configuration = sqlMapper.getConfiguration();
MixedSqlNode sqlNode = mixedContents(contents);
return new DynamicSqlSource(configuration, sqlNode);
}
private MixedSqlNode mixedContents(SqlNode... contents) {
return new MixedSqlNode(Arrays.asList(contents));
}
@Test
void shouldMapNullStringsToEmptyStrings() {
final String expected = "id=${id}";
final MixedSqlNode sqlNode = mixedContents(new TextSqlNode(expected));
final DynamicSqlSource source = new DynamicSqlSource(new Configuration(), sqlNode);
String sql = source.getBoundSql(new Bean(null)).getSql();
Assertions.assertEquals("id=", sql);
}
public static class Bean {
public String id;
Bean(String property) {
this.id = property;
}
public String getId() {
return id;
}
public void setId(String property) {
this.id = property;
}
}
@MethodSource
@ParameterizedTest
void testShrinkWhitespacesInSql(SqlNode input, boolean shrinkWhitespaces, String expected) {
Configuration config = new Configuration();
config.setShrinkWhitespacesInSql(shrinkWhitespaces);
String actual = new DynamicSqlSource(config, input).getBoundSql(null).getSql();
assertEquals(expected, actual);
}
static Stream<Arguments> testShrinkWhitespacesInSql() {
return Stream.of(
Arguments.arguments(
new StaticTextSqlNode("\t\n\n SELECT * \n FROM user\n \t WHERE user_id = 1\n\t "), false,
"SELECT * \n FROM user\n \t WHERE user_id = 1"),
Arguments.arguments(new StaticTextSqlNode("\t\n\n SELECT * \n FROM user\n \t WHERE user_id = 1\n\t"), true,
"SELECT * FROM user WHERE user_id = 1"));
}
}