1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 package org.dbunit.operation;
22
23 import static org.assertj.core.api.Assertions.assertThat;
24
25 import java.io.Reader;
26 import java.io.StringReader;
27 import java.nio.charset.StandardCharsets;
28 import java.nio.file.Files;
29 import java.sql.SQLException;
30
31 import org.dbunit.AbstractDatabaseIT;
32 import org.dbunit.Assertion;
33 import org.dbunit.DatabaseEnvironment;
34 import org.dbunit.TestFeature;
35 import org.dbunit.database.DatabaseConfig;
36 import org.dbunit.dataset.Column;
37 import org.dbunit.dataset.CompositeDataSet;
38 import org.dbunit.dataset.DataSetUtils;
39 import org.dbunit.dataset.DefaultDataSet;
40 import org.dbunit.dataset.DefaultTable;
41 import org.dbunit.dataset.ForwardOnlyDataSet;
42 import org.dbunit.dataset.IDataSet;
43 import org.dbunit.dataset.ITable;
44 import org.dbunit.dataset.LowerCaseDataSet;
45 import org.dbunit.dataset.SortedTable;
46 import org.dbunit.dataset.datatype.DataType;
47 import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
48 import org.dbunit.dataset.xml.XmlDataSet;
49 import org.dbunit.testutil.TestUtils;
50 import org.junit.jupiter.api.Test;
51 import org.junit.jupiter.api.condition.EnabledIfSystemProperty;
52
53
54
55
56
57
58 public class InsertOperationIT extends AbstractDatabaseIT
59 {
60
61 @Test
62 void testExecute_withClobData_insertsClobSuccessfully() throws Exception
63 {
64
65 final DatabaseEnvironment environment =
66 DatabaseEnvironment.getInstance();
67 if (environment.support(TestFeature.CLOB))
68 {
69 final String tableName = "CLOB_TABLE";
70
71 final Reader in =
72 Files.newBufferedReader(TestUtils.getFile("xml/clobInsertTest.xml").toPath(), StandardCharsets.UTF_8);
73 final IDataSet xmlDataSet = new FlatXmlDataSetBuilder().build(in);
74
75 assertThat(_connection.getRowCount(tableName)).as("count before")
76 .isEqualTo(0);
77
78 DatabaseOperation.INSERT.execute(_connection, xmlDataSet);
79
80 final ITable tableAfter =
81 _connection.createDataSet().getTable(tableName);
82 assertThat(tableAfter.getRowCount()).as("count after").isEqualTo(3);
83 Assertion.assertEquals(xmlDataSet.getTable(tableName), tableAfter);
84 }
85 }
86
87 @Test
88 void testExecute_withBlobData_insertsBlobSuccessfully() throws Exception
89 {
90
91 final DatabaseEnvironment environment =
92 DatabaseEnvironment.getInstance();
93 if (environment.support(TestFeature.BLOB))
94 {
95 final String tableName = "BLOB_TABLE";
96
97 final Reader in =
98 Files.newBufferedReader(TestUtils.getFile("xml/blobInsertTest.xml").toPath(), StandardCharsets.UTF_8);
99 final IDataSet xmlDataSet = new FlatXmlDataSetBuilder().build(in);
100
101 assertThat(_connection.getRowCount(tableName)).as("count before")
102 .isEqualTo(0);
103
104 DatabaseOperation.INSERT.execute(_connection, xmlDataSet);
105
106 final ITable tableAfter =
107 _connection.createDataSet().getTable(tableName);
108 assertThat(tableAfter.getRowCount()).as("count after").isEqualTo(3);
109 Assertion.assertEquals(xmlDataSet.getTable(tableName), tableAfter);
110 }
111 }
112
113 @Test
114 void testExecute_withSdoGeometryData_insertsSdoGeometrySuccessfully() throws Exception
115 {
116
117 final DatabaseEnvironment environment =
118 DatabaseEnvironment.getInstance();
119 if (environment.support(TestFeature.SDO_GEOMETRY))
120 {
121 final String tableName = "SDO_GEOMETRY_TABLE";
122
123 final Reader in = Files.newBufferedReader(
124 TestUtils.getFile("xml/sdoGeometryInsertTest.xml").toPath(), StandardCharsets.UTF_8);
125 final IDataSet xmlDataSet = new FlatXmlDataSetBuilder().build(in);
126
127 assertThat(_connection.getRowCount(tableName)).as("count before")
128 .isEqualTo(0);
129
130 DatabaseOperation.INSERT.execute(_connection, xmlDataSet);
131
132 final ITable tableAfter =
133 _connection.createDataSet().getTable(tableName);
134 assertThat(tableAfter.getRowCount()).as("count after").isEqualTo(1);
135 Assertion.assertEquals(xmlDataSet.getTable(tableName), tableAfter);
136 }
137 }
138
139 @Test
140 void testExecute_withXmlTypeData_insertsXmlTypeSuccessfully() throws Exception
141 {
142
143 final DatabaseEnvironment environment =
144 DatabaseEnvironment.getInstance();
145 if (environment.support(TestFeature.XML_TYPE))
146 {
147 final String tableName = "XML_TYPE_TABLE";
148
149 final Reader in = Files.newBufferedReader(
150 TestUtils.getFile("xml/xmlTypeInsertTest.xml").toPath(), StandardCharsets.UTF_8);
151 final IDataSet xmlDataSet = new FlatXmlDataSetBuilder().build(in);
152
153 assertThat(_connection.getRowCount(tableName)).as("count before")
154 .isEqualTo(0);
155
156 DatabaseOperation.INSERT.execute(_connection, xmlDataSet);
157
158 final ITable tableAfter =
159 _connection.createDataSet().getTable(tableName);
160 assertThat(tableAfter.getRowCount()).as("count after").isEqualTo(3);
161 Assertion.assertEquals(xmlDataSet.getTable(tableName), tableAfter);
162 }
163 }
164
165 @Test
166 void testExecute_withMissingColumns_nullsUnspecifiedColumns() throws Exception
167 {
168 final Reader in = TestUtils.getFileReader("xml/missingColumnTest.xml");
169 final IDataSet xmlDataSet = new XmlDataSet(in);
170
171 final ITable[] tablesBefore =
172 DataSetUtils.getTables(_connection.createDataSet());
173 DatabaseOperation.INSERT.execute(_connection, xmlDataSet);
174 final ITable[] tablesAfter =
175 DataSetUtils.getTables(_connection.createDataSet());
176
177
178 for (int i = 0; i < tablesBefore.length; i++)
179 {
180 final ITable table = tablesBefore[i];
181 final String tableName = table.getTableMetaData().getTableName();
182 if (tableName.startsWith("EMPTY"))
183 {
184 assertThat(table.getRowCount()).as(tableName + " before")
185 .isZero();
186 }
187 }
188
189
190 for (int i = 0; i < tablesAfter.length; i++)
191 {
192 final ITable databaseTable = tablesAfter[i];
193 final String tableName =
194 databaseTable.getTableMetaData().getTableName();
195
196 if (tableName.startsWith("EMPTY"))
197 {
198 final Column[] columns =
199 databaseTable.getTableMetaData().getColumns();
200 final ITable xmlTable = xmlDataSet.getTable(tableName);
201
202
203 assertThat(databaseTable.getRowCount()).as("row count")
204 .isEqualTo(xmlTable.getRowCount());
205
206
207 for (int j = 0; j < databaseTable.getRowCount(); j++)
208 {
209
210 final Object expected =
211 xmlTable.getValue(j, columns[0].getColumnName());
212 final Object actual = databaseTable.getValue(j,
213 columns[0].getColumnName());
214
215 assertThat(actual)
216 .as(tableName + "." + columns[0].getColumnName())
217 .isEqualTo(expected);
218
219
220
221 for (int k = 1; k < columns.length; k++)
222 {
223 final String columnName = columns[k].getColumnName();
224 assertThat(databaseTable.getValue(j, columnName))
225 .as(tableName + "." + columnName).isNull();
226 }
227 }
228 }
229 }
230
231 }
232
233 @Test
234 void testExecute_withXmlDataSet_insertsAllRows() throws Exception
235 {
236 final Reader in =
237 TestUtils.getFileReader("xml/insertOperationTest.xml");
238 final IDataSet dataSet = new XmlDataSet(in);
239
240 testExecute(dataSet);
241 }
242
243 @Test
244 void testExecute_withLowerCaseDataSet_insertsAllRows() throws Exception
245 {
246 final Reader in =
247 TestUtils.getFileReader("xml/insertOperationTest.xml");
248 final IDataSet dataSet = new XmlDataSet(in);
249
250 testExecute(new LowerCaseDataSet(dataSet));
251 }
252
253 @Test
254 void testExecute_withForwardOnlyDataSet_insertsAllRows() throws Exception
255 {
256 final Reader in =
257 TestUtils.getFileReader("xml/insertOperationTest.xml");
258 final IDataSet dataSet = new XmlDataSet(in);
259
260 testExecute(new ForwardOnlyDataSet(dataSet));
261 }
262
263 @Test
264 void testExecute_batchedStatements_allRowsInserted() throws Exception
265 {
266 final Reader in = TestUtils.getFileReader("xml/insertOperationTest.xml");
267 final IDataSet dataSet = new XmlDataSet(in);
268
269 _connection.getConfig().setFeature(DatabaseConfig.FEATURE_BATCHED_STATEMENTS, true);
270
271 final ITable[] tablesBefore = DataSetUtils.getTables(_connection.createDataSet());
272 DatabaseOperation.INSERT.execute(_connection, dataSet);
273 final ITable[] tablesAfter = DataSetUtils.getTables(_connection.createDataSet());
274
275 assertThat(tablesAfter).as("table count.").hasSameSizeAs(tablesBefore);
276 for (final ITable table : tablesAfter)
277 {
278 final String name = table.getTableMetaData().getTableName();
279 if (name.startsWith("EMPTY"))
280 {
281 final SortedTable expectedTable =
282 new SortedTable(dataSet.getTable(name),
283 dataSet.getTable(name).getTableMetaData());
284 final SortedTable actualTable = new SortedTable(table);
285 Assertion.assertEquals(expectedTable, actualTable);
286 }
287 }
288 }
289
290 @Test
291 void testExecute_nullValues_insertedAsNull() throws Exception
292 {
293 final String tableName = "EMPTY_TABLE";
294 final Column[] columns = new Column[]{
295 new Column("COLUMN0", DataType.VARCHAR),
296 new Column("COLUMN1", DataType.VARCHAR),
297 new Column("COLUMN2", DataType.VARCHAR),
298 new Column("COLUMN3", DataType.VARCHAR)
299 };
300 final DefaultTable table = new DefaultTable(tableName, columns);
301 table.addRow(new Object[]{"notNull", null, null, null});
302 final IDataSet dataSet = new DefaultDataSet(table);
303
304 assertThat(_connection.getRowCount(tableName)).as("count before.").isZero();
305
306 DatabaseOperation.INSERT.execute(_connection, dataSet);
307
308 final ITable actual = _connection.createDataSet().getTable(tableName);
309 assertThat(actual.getRowCount()).as("count after.").isEqualTo(1);
310 assertThat(actual.getValue(0, "COLUMN0")).as("COLUMN0.").isEqualTo("notNull");
311 assertThat(actual.getValue(0, "COLUMN1")).as("COLUMN1.").isNull();
312 assertThat(actual.getValue(0, "COLUMN2")).as("COLUMN2.").isNull();
313 }
314
315 @Test
316 void testExecute_emptyStringWithAllowEmptyFields_insertedAsEmptyString() throws Exception
317 {
318 final String tableName = "EMPTY_TABLE";
319 final Column[] columns = new Column[]{
320 new Column("COLUMN0", DataType.VARCHAR),
321 new Column("COLUMN1", DataType.VARCHAR),
322 new Column("COLUMN2", DataType.VARCHAR),
323 new Column("COLUMN3", DataType.VARCHAR)
324 };
325 final DefaultTable table = new DefaultTable(tableName, columns);
326 table.addRow(new Object[]{"hasValue", "", "", ""});
327 final IDataSet dataSet = new DefaultDataSet(table);
328
329 _connection.getConfig().setFeature(DatabaseConfig.FEATURE_ALLOW_EMPTY_FIELDS, true);
330
331 assertThat(_connection.getRowCount(tableName)).as("count before.").isZero();
332
333 DatabaseOperation.INSERT.execute(_connection, dataSet);
334
335 final ITable actual = _connection.createDataSet().getTable(tableName);
336 assertThat(actual.getRowCount()).as("count after.").isEqualTo(1);
337 assertThat(actual.getValue(0, "COLUMN0")).as("COLUMN0.").isEqualTo("hasValue");
338 }
339
340 @Test
341 void testExecute_withCompositeDataSetFromTwoFlatXmlDataSetsAndOneMissingAnOptionalColumn_insertsSuccessfully()
342 throws Exception
343 {
344
345
346 final String tableName = "EMPTY_TABLE";
347 final IDataSet dataSetWithOptionalColumn =
348 new FlatXmlDataSetBuilder().build(new StringReader("<dataset><"
349 + tableName
350 + " COLUMN0=\"row1\" COLUMN1=\"optionalValue\"/></dataset>"));
351 final IDataSet dataSetWithoutOptionalColumn =
352 new FlatXmlDataSetBuilder().build(new StringReader(
353 "<dataset><" + tableName + " COLUMN0=\"row2\"/></dataset>"));
354 final IDataSet dataSet = new CompositeDataSet(dataSetWithOptionalColumn,
355 dataSetWithoutOptionalColumn);
356
357 assertThat(_connection.getRowCount(tableName)).as("count before.")
358 .isZero();
359
360 DatabaseOperation.INSERT.execute(_connection, dataSet);
361
362 final ITable actual = _connection.createDataSet().getTable(tableName);
363 assertThat(actual.getRowCount()).as("count after.").isEqualTo(2);
364
365
366
367 Object column1ForRow1 = null;
368 Object column1ForRow2 = null;
369 for (int i = 0; i < actual.getRowCount(); i++)
370 {
371 final Object column0Value = actual.getValue(i, "COLUMN0");
372 if ("row1".equals(column0Value))
373 {
374 column1ForRow1 = actual.getValue(i, "COLUMN1");
375 } else if ("row2".equals(column0Value))
376 {
377 column1ForRow2 = actual.getValue(i, "COLUMN1");
378 }
379 }
380
381 assertThat(column1ForRow1).as("row1 COLUMN1.").isEqualTo("optionalValue");
382 assertThat(column1ForRow2)
383 .as("row2 COLUMN1 - the second dataset never declared this"
384 + " column, so it is omitted from the insert and the"
385 + " database's own NULL applies.")
386 .isNull();
387 }
388
389 @Test
390 @EnabledIfSystemProperty(named = "dbunit.profile", matches = "hsqldb")
391 void testExecute_withDefaultValueNotNullColumnTurningNull_appliesDefaultOnSecondRow()
392 throws Exception
393 {
394
395
396 final String tableName = "DEFAULT_VALUE_TABLE";
397 final Column[] columns = {new Column("ID", DataType.INTEGER),
398 new Column("STATUS", DataType.VARCHAR,
399 DataType.VARCHAR.toString(), Column.NO_NULLS,
400 "'PENDING'")};
401 final DefaultTable table = new DefaultTable(tableName, columns);
402 table.addRow(new Object[] {"1", "ACTIVE"});
403 table.addRow(new Object[] {"2", null});
404 final IDataSet dataSet = new DefaultDataSet(table);
405
406 DatabaseOperation.CLEAN_INSERT.execute(_connection, dataSet);
407
408 final ITable actual = _connection.createDataSet().getTable(tableName);
409 assertThat(actual.getRowCount()).as("row count.").isEqualTo(2);
410 assertThat(actual.getValue(0, "STATUS")).as("row 1 STATUS.")
411 .isEqualTo("ACTIVE");
412 assertThat(actual.getValue(1, "STATUS"))
413 .as("row 2 STATUS - database default must apply since"
414 + " equalsIgnoreMapping now regenerates the"
415 + " statement instead of reusing row 1's, which"
416 + " would otherwise bind NULL into this NOT NULL"
417 + " column.")
418 .isEqualTo("PENDING");
419 }
420
421 private void testExecute(final IDataSet dataSet)
422 throws Exception, SQLException
423 {
424 final ITable[] tablesBefore =
425 DataSetUtils.getTables(_connection.createDataSet());
426 DatabaseOperation.INSERT.execute(_connection, dataSet);
427 final ITable[] tablesAfter =
428 DataSetUtils.getTables(_connection.createDataSet());
429
430 assertThat(tablesAfter).as("table count").hasSameSizeAs(tablesBefore);
431 for (int i = 0; i < tablesBefore.length; i++)
432 {
433 final ITable table = tablesBefore[i];
434 final String name = table.getTableMetaData().getTableName();
435
436 if (name.startsWith("EMPTY"))
437 {
438 assertThat(table.getRowCount()).as(name + "before").isZero();
439 }
440 }
441
442 for (int i = 0; i < tablesAfter.length; i++)
443 {
444 final ITable table = tablesAfter[i];
445 final String name = table.getTableMetaData().getTableName();
446
447 if (name.startsWith("EMPTY"))
448 {
449 if (dataSet instanceof ForwardOnlyDataSet)
450 {
451 assertThat(table.getRowCount()).as(name).isPositive();
452 } else
453 {
454 final SortedTable expectedTable =
455 new SortedTable(dataSet.getTable(name),
456 dataSet.getTable(name).getTableMetaData());
457 final SortedTable actualTable = new SortedTable(table);
458 Assertion.assertEquals(expectedTable, actualTable);
459 }
460 }
461 }
462 }
463 }