1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.mybatis.dynamic.sql.mybatis3;
17
18 import static org.assertj.core.api.Assertions.assertThat;
19 import static org.junit.jupiter.api.Assertions.assertAll;
20 import static org.mybatis.dynamic.sql.SqlBuilder.*;
21
22 import java.sql.JDBCType;
23 import java.util.Date;
24 import java.util.Map;
25
26 import org.junit.jupiter.api.Test;
27 import org.mybatis.dynamic.sql.SqlColumn;
28 import org.mybatis.dynamic.sql.SqlTable;
29 import org.mybatis.dynamic.sql.render.RenderingStrategies;
30 import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
31
32 class SelectStatementTest {
33 static final SqlTable table = SqlTable.of("foo");
34 static final SqlColumn<Date> column1 = table.column("column1", JDBCType.DATE);
35 static final SqlColumn<Integer> column2 = table.column("column2", JDBCType.INTEGER);
36
37 @Test
38 void testSimpleCriteriaWithoutAlias() {
39 Date d = new Date();
40
41 SelectStatementProvider selectStatement = select(column1, column2)
42 .from(table, "a")
43 .where(column1, isEqualTo(d))
44 .or(column2, isEqualTo(4))
45 .and(column2, isLessThan(3))
46 .build()
47 .render(RenderingStrategies.MYBATIS3);
48
49 assertThat(selectStatement.getSelectStatement()).isEqualTo(
50 "select a.column1, a.column2 from foo a where a.column1 = #{parameters.p1,jdbcType=DATE} or a.column2 = #{parameters.p2,jdbcType=INTEGER} and a.column2 < #{parameters.p3,jdbcType=INTEGER}");
51
52 Map<String, Object> parameters = selectStatement.getParameters();
53
54 assertAll(
55 () -> assertThat(parameters).containsEntry("p1", d),
56 () -> assertThat(parameters).containsEntry("p2", 4),
57 () -> assertThat(parameters).containsEntry("p3", 3)
58 );
59 }
60
61 @Test
62 void testComplexCriteriaWithoutAlias() {
63 Date d = new Date();
64
65 SelectStatementProvider selectStatement = select(column1, column2)
66 .from(table, "a")
67 .where(column1, isEqualTo(d))
68 .or(column2, isEqualTo(4))
69 .and(column2, isLessThan(3))
70 .or(column2, isEqualTo(4), and(column2, isEqualTo(6)))
71 .and(column2, isLessThan(3), or(column1, isEqualTo(d)))
72 .build()
73 .render(RenderingStrategies.MYBATIS3);
74
75
76 String expected = "select a.column1, a.column2 "
77 + "from foo a "
78 + "where a.column1 = #{parameters.p1,jdbcType=DATE}"
79 + " or a.column2 = #{parameters.p2,jdbcType=INTEGER}"
80 + " and a.column2 < #{parameters.p3,jdbcType=INTEGER}"
81 + " or (a.column2 = #{parameters.p4,jdbcType=INTEGER} and a.column2 = #{parameters.p5,jdbcType=INTEGER})"
82 + " and (a.column2 < #{parameters.p6,jdbcType=INTEGER} or a.column1 = #{parameters.p7,jdbcType=DATE})";
83
84 Map<String, Object> parameters = selectStatement.getParameters();
85
86 assertAll(
87 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
88 () -> assertThat(parameters).containsEntry("p1", d),
89 () -> assertThat(parameters).containsEntry("p2", 4),
90 () -> assertThat(parameters).containsEntry("p3", 3),
91 () -> assertThat(parameters).containsEntry("p4", 4),
92 () -> assertThat(parameters).containsEntry("p5", 6),
93 () -> assertThat(parameters).containsEntry("p6", 3),
94 () -> assertThat(parameters).containsEntry("p7", d)
95 );
96 }
97
98 @Test
99 void testSimpleCriteriaWithAlias() {
100 Date d = new Date();
101
102 SelectStatementProvider selectStatement = select(column1, column2)
103 .from(table, "a")
104 .where(column1, isEqualTo(d))
105 .or(column2, isEqualTo(4))
106 .and(column2, isLessThan(3))
107 .build()
108 .render(RenderingStrategies.MYBATIS3);
109
110 Map<String, Object> parameters = selectStatement.getParameters();
111
112 assertAll(
113 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(
114 "select a.column1, a.column2 from foo a where a.column1 = #{parameters.p1,jdbcType=DATE} or a.column2 = #{parameters.p2,jdbcType=INTEGER} and a.column2 < #{parameters.p3,jdbcType=INTEGER}"),
115 () -> assertThat(parameters).containsEntry("p1", d),
116 () -> assertThat(parameters).containsEntry("p2", 4),
117 () -> assertThat(parameters).containsEntry("p3", 3)
118 );
119 }
120
121 @Test
122 void testComplexCriteriaWithAlias() {
123 Date d = new Date();
124
125 SelectStatementProvider selectStatement = select(column1, column2)
126 .from(table, "a")
127 .where(column1, isEqualTo(d))
128 .or(column2, isEqualTo(4))
129 .and(column2, isLessThan(3))
130 .or(column2, isEqualTo(4), and(column2, isEqualTo(6), or(column2, isEqualTo(7))))
131 .and(column2, isLessThan(3), or(column1, isEqualTo(d), and(column2, isEqualTo(88))))
132 .build()
133 .render(RenderingStrategies.MYBATIS3);
134
135
136 String expected = "select a.column1, a.column2 "
137 + "from foo a "
138 + "where a.column1 = #{parameters.p1,jdbcType=DATE}"
139 + " or a.column2 = #{parameters.p2,jdbcType=INTEGER}"
140 + " and a.column2 < #{parameters.p3,jdbcType=INTEGER}"
141 + " or (a.column2 = #{parameters.p4,jdbcType=INTEGER} and (a.column2 = #{parameters.p5,jdbcType=INTEGER} or a.column2 = #{parameters.p6,jdbcType=INTEGER}))"
142 + " and (a.column2 < #{parameters.p7,jdbcType=INTEGER} or (a.column1 = #{parameters.p8,jdbcType=DATE} and a.column2 = #{parameters.p9,jdbcType=INTEGER}))";
143
144 Map<String, Object> parameters = selectStatement.getParameters();
145
146 assertAll(
147 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
148 () -> assertThat(parameters).containsEntry("p1", d),
149 () -> assertThat(parameters).containsEntry("p2", 4),
150 () -> assertThat(parameters).containsEntry("p3", 3),
151 () -> assertThat(parameters).containsEntry("p4", 4),
152 () -> assertThat(parameters).containsEntry("p5", 6),
153 () -> assertThat(parameters).containsEntry("p6", 7),
154 () -> assertThat(parameters).containsEntry("p7", 3),
155 () -> assertThat(parameters).containsEntry("p8", d),
156 () -> assertThat(parameters).containsEntry("p9", 88)
157 );
158 }
159 }