1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 package org.dbunit.database;
23
24 import static org.assertj.core.api.Assertions.assertThat;
25 import static org.junit.jupiter.api.Assertions.assertThrows;
26
27 import org.dbunit.DatabaseEnvironment;
28 import org.dbunit.dataset.AbstractDataSetTest;
29 import org.dbunit.dataset.IDataSet;
30 import org.dbunit.dataset.ITable;
31 import org.dbunit.dataset.ITableIterator;
32 import org.dbunit.dataset.ITableMetaData;
33 import org.dbunit.dataset.NoSuchColumnException;
34 import org.dbunit.operation.DatabaseOperation;
35 import org.junit.jupiter.api.AfterEach;
36 import org.junit.jupiter.api.BeforeEach;
37 import org.junit.jupiter.api.Test;
38
39
40
41
42
43
44 class QueryDataSetIT extends AbstractDataSetTest
45 {
46 private IDatabaseConnection _connection;
47
48
49
50
51 @BeforeEach
52 protected void setUpConnection() throws Exception
53 {
54
55 final DatabaseEnvironment env = DatabaseEnvironment.getInstance();
56 _connection = env.getConnection();
57
58 DatabaseOperation.CLEAN_INSERT.execute(_connection,
59 env.getInitDataSet());
60 }
61
62 @AfterEach
63 protected void tearDown() throws Exception
64 {
65 _connection = null;
66 }
67
68
69
70
71 @Override
72 protected IDataSet createDataSet() throws Exception
73 {
74 final String[] names = getExpectedNames();
75
76 final QueryDataSet dataSet = new QueryDataSet(_connection);
77 for (int i = 0; i < names.length; i++)
78 {
79 final String name = names[i];
80 final String query = "select * from " + name;
81 dataSet.addTable(name, query);
82
83
84
85
86 }
87 return dataSet;
88 }
89
90 @Override
91 protected IDataSet createDuplicateDataSet() throws Exception
92 {
93 final QueryDataSet dataSet = new QueryDataSet(_connection);
94 final String[] names = getExpectedDuplicateNames();
95
96
97 final String queryOneRow = "select * from only_pk_table";
98 dataSet.addTable(names[0], queryOneRow);
99
100
101 final String queryNoRow = "select * from empty_table";
102 dataSet.addTable(names[1], queryNoRow);
103
104
105 final String queryTwoRow =
106 "select * from pk_table where PK0=0 or PK0=1";
107 dataSet.addTable(names[2], queryTwoRow);
108
109 return dataSet;
110 }
111
112 @Override
113 protected IDataSet createMultipleCaseDuplicateDataSet() throws Exception
114 {
115 final QueryDataSet dataSet = new QueryDataSet(_connection);
116 final String[] names = getExpectedDuplicateNames();
117
118
119 final String queryOneRow = "select * from only_pk_table";
120 dataSet.addTable(names[0], queryOneRow);
121
122
123 final String queryNoRow = "select * from empty_table";
124 dataSet.addTable(names[1], queryNoRow);
125
126
127 final String queryTwoRow =
128 "select * from pk_table where PK0=0 or PK0=1";
129 dataSet.addTable(names[2].toLowerCase(), queryTwoRow);
130
131
132
133
134 return dataSet;
135 }
136
137
138
139
140 @Test
141 void testGetSelectPartialData_withWhereClause_returnsFilteredRows() throws Exception
142 {
143
144 final QueryDataSet ptds = new QueryDataSet(_connection);
145 ptds.addTable("PK_TABLE",
146 "SELECT PK0, PK1 FROM PK_TABLE where PK0 = 0");
147
148 final ITable table = ptds.getTable("PK_TABLE");
149 assertThat(table.getValue(0, "PK0")).hasToString("0");
150 assertThat(new String(table.getRowCount() + "")).hasSize(1);
151
152 }
153
154 @Test
155 void testGetAllColumnsWithStar_withStarQuery_returnsAllColumns() throws Exception
156 {
157
158 final QueryDataSet ptds = new QueryDataSet(_connection);
159 ptds.addTable("PK_TABLE", "SELECT * FROM PK_TABLE where PK0 = 0");
160
161 final ITable table = ptds.getTable("PK_TABLE");
162 assertThat(table.getValue(0, "PK0")).hasToString("0");
163 assertThat(new String(table.getRowCount() + "")).hasSize(1);
164
165 }
166
167 @Test
168 void testGetAllRowsSingleColumn_withSingleColumnQuery_returnsAllRows() throws Exception
169 {
170
171 final QueryDataSet ptds = new QueryDataSet(_connection);
172 ptds.addTable("PK_TABLE", "SELECT PK0 FROM PK_TABLE ORDER BY PK0");
173
174 final ITable table = ptds.getTable("PK_TABLE");
175 assertThat(table.getValue(0, "PK0")).hasToString("0");
176 assertThat(new String(table.getRowCount() + "")).isEqualTo("3");
177 }
178
179 @Test
180 void testOnlySpecifiedColumnsReturned_withSpecificColumnQuery_throwsForUnqueriedColumn() throws Exception
181 {
182
183 final QueryDataSet ptds = new QueryDataSet(_connection);
184 ptds.addTable("PK_TABLE", "SELECT PK0 FROM PK_TABLE ORDER BY PK0 ASC");
185
186 final ITable table = ptds.getTable("PK_TABLE");
187 assertThat(table.getValue(0, "PK0")).hasToString("0");
188
189 final NoSuchColumnException nsce = assertThrows(
190 NoSuchColumnException.class,
191 () -> table.getValue(0, "PK1").toString(),
192 "Should not have reached here, we should have thrown a NoSuchColumnException");
193 final String errorMsg =
194 "org.dbunit.dataset.NoSuchColumnException: PK_TABLE.PK1";
195 assertThat(nsce).as("Find text:" + errorMsg).asString()
196 .contains(errorMsg);
197 }
198
199 @Test
200 void testGetSelectPartialData2_withColumnAndRowFilter_returnsFilteredResult() throws Exception
201 {
202
203 final QueryDataSet ptds = new QueryDataSet(_connection);
204 ptds.addTable("SECOND_TABLE",
205 "SELECT * FROM SECOND_TABLE where COLUMN0='row 0 col 0'");
206
207 final ITable table = ptds.getTable("SECOND_TABLE");
208 assertThat(table.getValue(0, "COLUMN0")).hasToString("row 0 col 0");
209 assertThat(table.getValue(0, "COLUMN3")).hasToString("row 0 col 3");
210 assertThat(new String(table.getRowCount() + "")).hasSize(1);
211
212 }
213
214 @Test
215 void testCombinedWhere_withMultipleWhereConditions_returnsMatchingRow() throws Exception
216 {
217
218 final QueryDataSet ptds = new QueryDataSet(_connection);
219 ptds.addTable("SECOND_TABLE",
220 "SELECT COLUMN0, COLUMN3 FROM SECOND_TABLE where COLUMN0='row 0 col 0' and COLUMN2='row 0 col 2'");
221
222 final ITable table = ptds.getTable("SECOND_TABLE");
223 assertThat(table.getValue(0, "COLUMN0")).hasToString("row 0 col 0");
224 assertThat(table.getValue(0, "COLUMN3")).hasToString("row 0 col 3");
225 assertThat(new String(table.getRowCount() + "")).hasSize(1);
226
227 }
228
229 @Test
230 void testMultipleTables_withTwoQueriedTables_returnsCorrectDataForEach() throws Exception
231 {
232 ITable table = null;
233
234 final QueryDataSet ptds = new QueryDataSet(_connection);
235 ptds.addTable("SECOND_TABLE",
236 "SELECT * from SECOND_TABLE where COLUMN0='row 0 col 0' and COLUMN2='row 0 col 2'");
237 ptds.addTable("PK_TABLE", "SELECT * FROM PK_TABLE where PK0 = 0");
238
239 table = ptds.getTable("SECOND_TABLE");
240 assertThat(table.getValue(0, "COLUMN0")).hasToString("row 0 col 0");
241 assertThat(table.getValue(0, "COLUMN3")).hasToString("row 0 col 3");
242 assertThat(new String(table.getRowCount() + "")).hasSize(1);
243
244 table = ptds.getTable("PK_TABLE");
245 assertThat(table.getValue(0, "PK0").toString()).isEqualTo("0");
246 assertThat(new String(table.getRowCount() + "")).isEqualTo("1");
247
248 }
249
250 @Test
251 void testMultipleTablesWithMissingWhere_withNullQuery_addsTableWithoutFilter() throws Exception
252 {
253 final QueryDataSet ptds = new QueryDataSet(_connection);
254 ptds.addTable("SECOND_TABLE",
255 "SELECT * from SECOND_TABLE where COLUMN0='row 0 col 0' and COLUMN2='row 0 col 2'");
256 ptds.addTable("PK_TABLE", null);
257 }
258
259 @Test
260 void testIteratorGetTableMetaData_calledBeforeGetTable_returnsConsistentDataAndMetaData()
261 throws Exception
262 {
263 final QueryDataSet ptds = new QueryDataSet(_connection);
264 ptds.addTable("PK_TABLE", "SELECT * FROM PK_TABLE where PK0 = 0");
265
266 final ITableIterator iterator = ptds.iterator();
267 assertThat(iterator.next()).as("PK_TABLE entry should be present.").isTrue();
268
269
270
271
272 final ITableMetaData metaData = iterator.getTableMetaData();
273 assertThat(metaData.getColumnIndex("PK0")).as("PK0 column index.").isZero();
274
275 final ITable table = iterator.getTable();
276 assertThat(table.getValue(0, "PK0")).as("PK0 value.").hasToString("0");
277 assertThat(table.getRowCount()).as("row count.").isEqualTo(1);
278 }
279 }