1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.apache.ibatis.jdbc;
17
18 import static org.apache.ibatis.jdbc.SelectBuilder.*;
19 import static org.junit.jupiter.api.Assertions.assertEquals;
20
21 import org.junit.jupiter.api.Test;
22
23 class SelectBuilderTest {
24
25 @Test
26 void shouldProduceExpectedSimpleSelectStatement() {
27 String expected =
28 "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME\n" +
29 "FROM PERSON P\n" +
30 "WHERE (P.ID like #id# AND P.FIRST_NAME like #firstName# AND P.LAST_NAME like #lastName#)\n" +
31 "ORDER BY P.LAST_NAME";
32 assertEquals(expected, example2("a", "b", "c"));
33 }
34
35 @Test
36 void shouldProduceExpectedSimpleSelectStatementMissingFirstParam() {
37 String expected =
38 "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME\n" +
39 "FROM PERSON P\n" +
40 "WHERE (P.FIRST_NAME like #firstName# AND P.LAST_NAME like #lastName#)\n" +
41 "ORDER BY P.LAST_NAME";
42 assertEquals(expected, example2(null, "b", "c"));
43 }
44
45 @Test
46 void shouldProduceExpectedSimpleSelectStatementMissingFirstTwoParams() {
47 String expected =
48 "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME\n" +
49 "FROM PERSON P\n" +
50 "WHERE (P.LAST_NAME like #lastName#)\n" +
51 "ORDER BY P.LAST_NAME";
52 assertEquals(expected, example2(null, null, "c"));
53 }
54
55 @Test
56 void shouldProduceExpectedSimpleSelectStatementMissingAllParams() {
57 String expected =
58 "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME\n" +
59 "FROM PERSON P\n" +
60 "ORDER BY P.LAST_NAME";
61 assertEquals(expected, example2(null, null, null));
62 }
63
64 @Test
65 void shouldProduceExpectedComplexSelectStatement() {
66 String expected =
67 "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME, P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON\n" +
68 "FROM PERSON P, ACCOUNT A\n" +
69 "INNER JOIN DEPARTMENT D on D.ID = P.DEPARTMENT_ID\n" +
70 "INNER JOIN COMPANY C on D.COMPANY_ID = C.ID\n" +
71 "WHERE (P.ID = A.ID AND P.FIRST_NAME like ?) \n" +
72 "OR (P.LAST_NAME like ?)\n" +
73 "GROUP BY P.ID\n" +
74 "HAVING (P.LAST_NAME like ?) \n" +
75 "OR (P.FIRST_NAME like ?)\n" +
76 "ORDER BY P.ID, P.FULL_NAME";
77 assertEquals(expected, example1());
78 }
79
80 private static String example1() {
81 SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
82 SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
83 FROM("PERSON P");
84 FROM("ACCOUNT A");
85 INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
86 INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
87 WHERE("P.ID = A.ID");
88 WHERE("P.FIRST_NAME like ?");
89 OR();
90 WHERE("P.LAST_NAME like ?");
91 GROUP_BY("P.ID");
92 HAVING("P.LAST_NAME like ?");
93 OR();
94 HAVING("P.FIRST_NAME like ?");
95 ORDER_BY("P.ID");
96 ORDER_BY("P.FULL_NAME");
97 return SQL();
98 }
99
100 private static String example2(String id, String firstName, String lastName) {
101 SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME");
102 FROM("PERSON P");
103 if (id != null) {
104 WHERE("P.ID like #id#");
105 }
106 if (firstName != null) {
107 WHERE("P.FIRST_NAME like #firstName#");
108 }
109 if (lastName != null) {
110 WHERE("P.LAST_NAME like #lastName#");
111 }
112 ORDER_BY("P.LAST_NAME");
113 return SQL();
114 }
115
116
117 }