1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 package org.dbunit.util;
22
23 import static org.assertj.core.api.Assertions.assertThat;
24 import static org.assertj.core.api.Assertions.assertThatNullPointerException;
25
26 import java.io.ByteArrayOutputStream;
27 import java.io.PrintStream;
28 import java.sql.Connection;
29 import java.sql.DatabaseMetaData;
30 import java.sql.ResultSet;
31 import java.sql.Statement;
32
33 import org.dbunit.AbstractDatabaseIT;
34 import org.dbunit.database.DatabaseConfig;
35 import org.dbunit.dataset.Column;
36 import org.dbunit.dataset.datatype.IDataTypeFactory;
37 import org.junit.jupiter.api.Assumptions;
38 import org.junit.jupiter.api.Test;
39
40
41
42
43
44
45
46
47
48
49 class SQLHelperIT extends AbstractDatabaseIT
50 {
51
52
53
54
55 @Test
56 void testGetPrimaryKeyColumn_withPkTable_returnsPrimaryKeyColumnName()
57 throws Exception
58 {
59 final Connection conn = _connection.getConnection();
60 final String pk =
61 SQLHelper.getPrimaryKeyColumn(conn, convertString("PK_TABLE"));
62 assertThat(pk).as("primary key column name.")
63 .isEqualToIgnoringCase("PK0");
64 }
65
66
67
68
69
70 @Test
71 void testClose_withBothNull_doesNotThrow() throws Exception
72 {
73 SQLHelper.close((ResultSet) null, (Statement) null);
74 }
75
76 @Test
77 void testClose_withBothNonNull_closesResultSetAndStatement() throws Exception
78 {
79 final Connection conn = _connection.getConnection();
80 final Statement stmt = conn.createStatement();
81 final ResultSet rs =
82 stmt.executeQuery("SELECT COUNT(*) FROM TEST_TABLE");
83 assertThat(rs.isClosed()).as("rs open before close.").isFalse();
84 assertThat(stmt.isClosed()).as("stmt open before close.").isFalse();
85
86 SQLHelper.close(rs, stmt);
87
88 assertThat(rs.isClosed()).as("rs closed after close.").isTrue();
89 assertThat(stmt.isClosed()).as("stmt closed after close.").isTrue();
90 }
91
92 @Test
93 void testClose_withNullResultSetAndNonNullStatement_closesStatement()
94 throws Exception
95 {
96 final Connection conn = _connection.getConnection();
97 final Statement stmt = conn.createStatement();
98 assertThat(stmt.isClosed()).as("stmt open before close.").isFalse();
99
100 SQLHelper.close((ResultSet) null, stmt);
101
102 assertThat(stmt.isClosed()).as("stmt closed after close.").isTrue();
103 }
104
105 @Test
106 void testClose_withNonNullResultSetAndNullStatement_closesResultSet()
107 throws Exception
108 {
109 final Connection conn = _connection.getConnection();
110 final Statement stmt = conn.createStatement();
111 final ResultSet rs =
112 stmt.executeQuery("SELECT COUNT(*) FROM TEST_TABLE");
113 assertThat(rs.isClosed()).as("rs open before close.").isFalse();
114
115 SQLHelper.close(rs, (Statement) null);
116 stmt.close();
117
118 assertThat(rs.isClosed()).as("rs closed after close.").isTrue();
119 }
120
121
122
123
124
125 @Test
126 void testCloseStatement_withNullStatement_doesNotThrow() throws Exception
127 {
128 SQLHelper.close((Statement) null);
129 }
130
131 @Test
132 void testCloseStatement_withOpenStatement_closesStatement() throws Exception
133 {
134 final Connection conn = _connection.getConnection();
135 final Statement stmt = conn.createStatement();
136 assertThat(stmt.isClosed()).as("stmt open before close.").isFalse();
137
138 SQLHelper.close(stmt);
139
140 assertThat(stmt.isClosed()).as("stmt closed after close.").isTrue();
141 }
142
143
144
145
146
147 @Test
148 void testCloseResultSet_withNullResultSet_doesNotThrow() throws Exception
149 {
150 SQLHelper.close((ResultSet) null);
151 }
152
153 @Test
154 void testCloseResultSet_withOpenResultSet_closesResultSet() throws Exception
155 {
156 final Connection conn = _connection.getConnection();
157 final Statement stmt = conn.createStatement();
158 final ResultSet rs =
159 stmt.executeQuery("SELECT COUNT(*) FROM TEST_TABLE");
160 assertThat(rs.isClosed()).as("rs open before close.").isFalse();
161
162 SQLHelper.close(rs);
163 stmt.close();
164
165 assertThat(rs.isClosed()).as("rs closed after close.").isTrue();
166 }
167
168
169
170
171
172 @Test
173 void testSchemaExists_withExistingSchema_returnsTrue() throws Exception
174 {
175 final Connection conn = _connection.getConnection();
176 final String schema = getEnvironment().getProfile().getSchema();
177 Assumptions.assumeTrue(schema != null && !schema.isEmpty(),
178 "Skip for databases that use no schema (e.g., MySQL).");
179 assertThat(SQLHelper.schemaExists(conn, schema))
180 .as("existing schema returns true.").isTrue();
181 }
182
183 @Test
184 void testSchemaExists_withNonExistentSchema_returnsFalse() throws Exception
185 {
186 final Connection conn = _connection.getConnection();
187 assertThat(SQLHelper.schemaExists(conn, "NONEXISTENT_SCHEMA_XYZ_99"))
188 .as("non-existent schema returns false.").isFalse();
189 }
190
191 @Test
192 void testSchemaExists_withNullSchema_throwsNullPointerException()
193 throws Exception
194 {
195 final Connection conn = _connection.getConnection();
196 assertThatNullPointerException()
197 .isThrownBy(() -> SQLHelper.schemaExists(conn, null))
198 .withMessageContaining("schema");
199 }
200
201
202
203
204
205 @Test
206 void testTableExists_withExistingTable_returnsTrue() throws Exception
207 {
208 final Connection conn = _connection.getConnection();
209 final DatabaseMetaData metaData = conn.getMetaData();
210 final String schema = getEnvironment().getProfile().getSchema();
211 assertThat(SQLHelper.tableExists(metaData, schema,
212 convertString("TEST_TABLE")))
213 .as("existing table returns true.").isTrue();
214 }
215
216 @Test
217 void testTableExists_withNonExistentTable_returnsFalse() throws Exception
218 {
219 final Connection conn = _connection.getConnection();
220 final DatabaseMetaData metaData = conn.getMetaData();
221 final String schema = getEnvironment().getProfile().getSchema();
222 assertThat(SQLHelper.tableExists(metaData, schema,
223 "NONEXISTENT_TABLE_XYZ_99"))
224 .as("non-existent table returns false.").isFalse();
225 }
226
227
228
229
230
231 @Test
232 void testPrintAllTables_withLiveDatabase_outputContainsTestTable()
233 throws Exception
234 {
235 final Connection conn = _connection.getConnection();
236 final DatabaseMetaData metaData = conn.getMetaData();
237 final ByteArrayOutputStream baos = new ByteArrayOutputStream();
238 final PrintStream ps = new PrintStream(baos);
239
240 SQLHelper.printAllTables(metaData, ps);
241
242 final String output = baos.toString();
243 assertThat(output).as("output contains TEST_TABLE.")
244 .containsIgnoringCase("TEST_TABLE");
245 }
246
247
248
249
250
251 @Test
252 void testGetDatabaseInfo_withLiveDatabase_returnsNonNullInfoWithProductName()
253 throws Exception
254 {
255 final Connection conn = _connection.getConnection();
256 final DatabaseMetaData metaData = conn.getMetaData();
257 final String productName = metaData.getDatabaseProductName();
258
259 final String info = SQLHelper.getDatabaseInfo(metaData);
260
261 assertThat(info).as("info is not null.").isNotNull();
262 assertThat(info).as("info contains product name.").contains(productName);
263 assertThat(info).as("info contains driver name label.")
264 .contains("jdbc driver name");
265 }
266
267
268
269
270
271 @Test
272 void testPrintDatabaseInfo_withLiveDatabase_writesNonEmptyOutput()
273 throws Exception
274 {
275 final Connection conn = _connection.getConnection();
276 final DatabaseMetaData metaData = conn.getMetaData();
277 final ByteArrayOutputStream baos = new ByteArrayOutputStream();
278 final PrintStream ps = new PrintStream(baos);
279
280 SQLHelper.printDatabaseInfo(metaData, ps);
281
282 assertThat(baos.toString()).as("output is not empty.").isNotEmpty();
283 }
284
285
286
287
288
289 @Test
290 void testIsSybaseDb_withTestDatabase_returnsFalse() throws Exception
291 {
292 final Connection conn = _connection.getConnection();
293 final DatabaseMetaData metaData = conn.getMetaData();
294 assertThat(SQLHelper.isSybaseDb(metaData))
295 .as("test database is not Sybase.").isFalse();
296 }
297
298
299
300
301
302 @Test
303 void testCreateColumn_withValidColumnResultSet_returnsNonNullColumn()
304 throws Exception
305 {
306 final Connection conn = _connection.getConnection();
307 final DatabaseMetaData metaData = conn.getMetaData();
308 final String schema = getEnvironment().getProfile().getSchema();
309 final IDataTypeFactory dataTypeFactory =
310 (IDataTypeFactory) _connection.getConfig()
311 .getProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY);
312
313 final ResultSet rs = metaData.getColumns(null, schema,
314 convertString("TEST_TABLE"), null);
315 try
316 {
317 assertThat(rs.next()).as("at least one column in TEST_TABLE.")
318 .isTrue();
319 final Column column =
320 SQLHelper.createColumn(rs, dataTypeFactory, true);
321 assertThat(column).as("column created.").isNotNull();
322 assertThat(column.getColumnName())
323 .as("column name not empty.").isNotEmpty();
324 } finally
325 {
326 rs.close();
327 }
328 }
329
330
331
332
333
334 @Test
335 void testMatches_withMatchingSchemaAndTable_returnsTrue() throws Exception
336 {
337 final Connection conn = _connection.getConnection();
338 final DatabaseMetaData metaData = conn.getMetaData();
339 final String schema = getEnvironment().getProfile().getSchema();
340 final String tableName = convertString("TEST_TABLE");
341
342 final ResultSet rs =
343 metaData.getColumns(null, schema, tableName, null);
344 try
345 {
346 assertThat(rs.next())
347 .as("at least one row in columns ResultSet.").isTrue();
348 assertThat(SQLHelper.matches(rs, schema, tableName, false))
349 .as("matches with correct schema and table.").isTrue();
350 } finally
351 {
352 rs.close();
353 }
354 }
355
356 @Test
357 void testMatches_withNullSchemaAndNullTable_returnsTrue() throws Exception
358 {
359 final Connection conn = _connection.getConnection();
360 final DatabaseMetaData metaData = conn.getMetaData();
361 final String schema = getEnvironment().getProfile().getSchema();
362 final String tableName = convertString("TEST_TABLE");
363
364 final ResultSet rs =
365 metaData.getColumns(null, schema, tableName, null);
366 try
367 {
368 assertThat(rs.next())
369 .as("at least one row in columns ResultSet.").isTrue();
370 assertThat(SQLHelper.matches(rs, null, null, true))
371 .as("null schema and table act as wildcards.").isTrue();
372 } finally
373 {
374 rs.close();
375 }
376 }
377
378
379
380
381
382 @Test
383 void testMatchesFull_withNullCatalogSchemaTableAndMatchingColumn_returnsTrue()
384 throws Exception
385 {
386 final Connection conn = _connection.getConnection();
387 final DatabaseMetaData metaData = conn.getMetaData();
388 final String schema = getEnvironment().getProfile().getSchema();
389 final String tableName = convertString("TEST_TABLE");
390
391 final ResultSet rs =
392 metaData.getColumns(null, schema, tableName, null);
393 try
394 {
395 assertThat(rs.next())
396 .as("at least one row in columns ResultSet.").isTrue();
397 final String actualColumnName = rs.getString(4);
398 assertThat(
399 SQLHelper.matches(rs, null, null, null, actualColumnName,
400 false))
401 .as("full match with concrete column name.").isTrue();
402 } finally
403 {
404 rs.close();
405 }
406 }
407
408
409
410
411
412 @Test
413 void testAreEqualIgnoreNull_withNullValue1_returnsTrue()
414 {
415 assertThat(SQLHelper.areEqualIgnoreNull(null, "anything", true))
416 .as("null v1 always matches.").isTrue();
417 }
418
419 @Test
420 void testAreEqualIgnoreNull_withEmptyValue1_returnsTrue()
421 {
422 assertThat(SQLHelper.areEqualIgnoreNull("", "anything", true))
423 .as("empty v1 always matches.").isTrue();
424 }
425
426 @Test
427 void testAreEqualIgnoreNull_withEqualValuesCaseSensitive_returnsTrue()
428 {
429 assertThat(SQLHelper.areEqualIgnoreNull("ABC", "ABC", true))
430 .as("equal values case-sensitive.").isTrue();
431 }
432
433 @Test
434 void testAreEqualIgnoreNull_withDifferentCaseCaseSensitive_returnsFalse()
435 {
436 assertThat(SQLHelper.areEqualIgnoreNull("ABC", "abc", true))
437 .as("different case with case-sensitive comparison.").isFalse();
438 }
439
440 @Test
441 void testAreEqualIgnoreNull_withDifferentCaseCaseInsensitive_returnsTrue()
442 {
443 assertThat(SQLHelper.areEqualIgnoreNull("ABC", "abc", false))
444 .as("different case with case-insensitive comparison.").isTrue();
445 }
446
447 @Test
448 void testAreEqualIgnoreNull_withDifferentValues_returnsFalse()
449 {
450 assertThat(SQLHelper.areEqualIgnoreNull("ABC", "XYZ", true))
451 .as("different values return false.").isFalse();
452 }
453
454
455
456
457
458 @Test
459 void testCorrectCase_withConnection_returnsIdentifierInDatabaseCase()
460 throws Exception
461 {
462 final Connection conn = _connection.getConnection();
463 final String corrected = SQLHelper.correctCase("test_table", conn);
464 assertThat(corrected).as("corrected identifier matches DB-stored case.")
465 .isEqualTo(convertString("test_table"));
466 }
467
468
469
470
471
472 @Test
473 void testCorrectCase_withDatabaseMetaData_returnsIdentifierInDatabaseCase()
474 throws Exception
475 {
476 final Connection conn = _connection.getConnection();
477 final DatabaseMetaData metaData = conn.getMetaData();
478 final String corrected = SQLHelper.correctCase("test_table", metaData);
479 assertThat(corrected).as("corrected identifier matches DB-stored case.")
480 .isEqualTo(convertString("test_table"));
481 }
482
483 @Test
484 void testCorrectCase_withNullIdentifier_throwsNullPointerException()
485 throws Exception
486 {
487 final Connection conn = _connection.getConnection();
488 final DatabaseMetaData metaData = conn.getMetaData();
489 assertThatNullPointerException()
490 .isThrownBy(() -> SQLHelper.correctCase(null, metaData))
491 .withMessageContaining("databaseIdentifier");
492 }
493 }