The purpose of this page is to show the different concepts specific to AssertJ-DB.
For the examples below, suppose that the database contains these three tables :
ID | NAME | FIRSTNAME | SURNAME | BIRTHDATE | SIZE |
---|---|---|---|---|---|
1 | 'Hewson' | 'Paul David' | 'Bono' | 05-10-60 | 1.75 |
2 | 'Evans' | 'David Howell' | 'The Edge' | 08-08-61 | 1.77 |
3 | 'Clayton' | 'Adam' | 03-13-60 | 1.78 | |
4 | 'Mullen' | 'Larry' | 10-31-61 | 1.70 |
ID | RELEASE | TITLE | NUMBEROFSONGS | DURATION | LIVE |
---|---|---|---|---|---|
1 | 10-20-80 | 'Boy' | 12 | 42:17 | |
2 | 10-12-81 | 'October' | 11 | 41:08 | |
3 | 02-28-83 | 'War' | 10 | 42:07 | |
4 | 11-07-83 | 'Under a Blood Red Sky' | 8 | 33:25 | true |
5 | 10-01-84 | 'The Unforgettable Fire' | 10 | 42:42 | |
6 | 06-10-85 | 'Wide Awake in America' | 4 | 20:30 | true |
7 | 03-09-87 | 'The Joshua Tree' | 11 | 50:11 | |
8 | 10-10-88 | 'Rattle and Hum' | 17 | 72:27 | |
9 | 11-18-91 | 'Achtung Baby' | 12 | 55:23 | |
10 | 07-06-93 | 'Zooropa' | 10 | 51:15 | |
11 | 03-03-97 | 'Pop' | 12 | 60:08 | |
12 | 10-30-00 | 'All That You Can't Leave Behind' | 11 | 49:23 | |
13 | 11-22-04 | 'How to Dismantle an Atomic Bomb' | 11 | 49:08 | |
14 | 03-02-09 | 'No Line on the Horizon' | 11 | 53:44 | |
15 | 09-09-14 | 'Songs of Innocence' | 11 | 48:11 |
ID | NAME |
---|---|
1 | 'U2' |
2 | 'Coldplay' |
To make assertions on a database, it is necessary to connect. For that, either to use a DataSource
or a Source
.
A DataSource is the classic Java way to get a Connection to a database.
A Source is a way to connect if you have not access to a DataSource or if you do not want to use one. It allows you to pass the necessary connection information as constructor parameters. Below is an example of using a Source to connect to H2 in memory database :
Source source = new Source("jdbc:h2:mem:test", "sa", "");
Since 1.1.0
A DataSourceWithLetterCase is a DataSource which allows to indicate the LetterCase for the tables, columns and primary keys.
DataSource ds = new DataSourceWithLetterCase(dataSource,
tableLetterCase,
columnLetterCase,
pkLetterCase);
For more information, see the paragraph on LetterCase.
Since 1.1.0
A SourceWithLetterCase is a Source which allows to indicate the LetterCase for the tables, columns and primary keys.
Source s = new SourceWithLetterCase("jdbc:h2:mem:test", "sa", "",
tableLetterCase,
columnLetterCase,
pkLetterCase);
For more information, see the paragraph on LetterCase.
Here the elements on which it is possible to make assertions.
Note that, there are only 3 root elements : Table, Request and Changes.
That means that the other elements are components or sub components of a root element.
A root element is an element on which the assertion start (in practice, the parameter of a assertThat(...)
method).
A Table represents
a table in the database.
A Table needs a way to connect to the database (either a DataSource or a Source) and a name (the two mandatory constructor parameters).
// Get a DataSource
DataSource dataSource = ...
// Declare the "members" table by using a DataSource
Table table1 = new Table(dateSource, "members");
// Declare the "members" table by using a Source
Table table2 = new Table(source, "members");
The two Tables above
(table1
and table2
) are equivalent.
ID | NAME | FIRSTNAME | SURNAME | BIRTHDATE | SIZE |
---|---|---|---|---|---|
1 | 'Hewson' | 'Paul David' | 'Bono' | 05-10-60 | 1.75 |
2 | 'Evans' | 'David Howell' | 'The Edge' | 08-08-61 | 1.77 |
3 | 'Clayton' | 'Adam' | 03-13-60 | 1.78 | |
4 | 'Mullen' | 'Larry' | 10-31-61 | 1.70 |
For a Table, it is possible to choose the columns to include and to exclude in the assertions.
// Get the data of the "id" and "name" columns of the "members" table
Table table3 = new Table(source, "members", new String[] { "id", "name" }, null);
// Get the data of the "members" table but not of the "birthdate" column
Table table4 = new Table(source, "members", null, new String[] { "birthdate" });
// Get the data of the "name" column of the "members" table (because "id" is included and excluded)
Table table5 = new Table(source, "members", new String[] { "id", "name" }, new String[] { "id" });
ID | NAME |
---|---|
1 | 'Hewson' |
2 | 'Evans' |
3 | 'Clayton' |
4 | 'Mullen' |
ID | NAME | FIRSTNAME | SURNAME | SIZE |
---|---|---|---|---|
1 | 'Hewson' | 'Paul David' | 'Bono' | 1.75 |
2 | 'Evans' | 'David Howell' | 'The Edge' | 1.77 |
3 | 'Clayton' | 'Adam' | 1.78 | |
4 | 'Mullen' | 'Larry' | 1.70 |
NAME |
---|
'Hewson' |
'Evans' |
'Clayton' |
'Mullen' |
Since version 1.2.0, there are the possibility to indicate delimiters (start delimiter and end delimiter) and Order
.
The delimiters are usefull when the table name or column name is a reserved word or contains special characters (like space or '%').
Order
allows to choose the order of the Row
.
// The line code below throws SQLException because "group" is SQL reserved word
Table table6 = new Table(source, "group");
// Get the data of the "group" table by using "`" delimiter
// That generates a request
Table table7 = new Table(source, "group", '`', '`');
// Get the data from "members" table and order on "name" column in ascending order
Table table8 = new Table(source, "members", new Order[] {
Order.asc("name")
});
ID | NAME |
---|---|
1 | 'U2' |
2 | 'Colplay' |
ID | NAME | FIRSTNAME | SURNAME | BIRTHDATE | SIZE |
---|---|---|---|---|---|
3 | 'Clayton' | 'Adam' | 03-13-60 | 1.78 | |
2 | 'Evans' | 'David Howell' | 'The Edge' | 08-08-61 | 1.77 |
1 | 'Hewson' | 'Paul David' | 'Bono' | 05-10-60 | 1.75 |
4 | 'Mullen' | 'Larry' | 10-31-61 | 1.70 |
A Request represents a SQL request on the database.
Like a Table,
a Request
needs a way to connect to the database (either a DataSource
or a Source).
// Get a DataSource
DataSource dataSource = ...
// Declare a request which gets the name and the firstname of the corresponding members
// by using a Source
Request request1 = new Request(source,
"select name, firstname from members where id = 2 or id = 3");
// Declare a request which gets the name and the firstname of the corresponding members
// by using the DataSource
Request request2 = new Request(dataSource,
"select name, firstname from members where id = 2 or id = 3");
The two Requests above
(request1
and request2
) are equivalent.
NAME | FIRSTNAME | SURNAME |
---|---|---|
'Evans' | 'David Howell' | 'The Edge' |
'Clayton' | 'Adam' |
For a Request, it is possible to use a simple SQL request or a SQL request with one or many parameters.
// Declare a request which gets the name and the firstname of the members
// and use "%e%" as a parameter
Request request3 = new Request(dataSource,
"select name, firstname from members " +
"where name like ?;",
"%e%");
// Declare a request which gets the name and the firstname of the members
// and use "%e%" and "%Paul%" as parameters
Request request4 = new Request(dataSource,
"select name, firstname from members " +
"where name like ? and firstname like ?;",
"%e%",
"%Paul%");
NAME | FIRSTNAME | SURNAME |
---|---|---|
'Hewson' | 'Paul David' | 'Bono' |
'Evans' | 'David Howell' | 'The Edge' |
'Mullen' | 'Larry' |
NAME | FIRSTNAME | SURNAME |
---|---|---|
'Hewson' | 'Paul David' | 'Bono' |
The Changes
are the differences of states in database between a start point
and a end point
.
Assume that there are these SQL statements between the start point and the end point.
DELETE FROM ALBUMS WHERE ID = 15;
INSERT INTO MEMBERS(ID, NAME, FIRSTNAME) VALUES(5, 'McGuiness', 'Paul');
UPDATE MEMBERS SET SURNAME = 'Bono Vox' WHERE ID = 1;
UPDATE ALBUMS SET NAME = 'Rattle & Hum', LIVE = true WHERE ID = 8;
// Get a DataSource
DataSource dataSource = ...
// The changes can be on a DataSource or on a Source
Changes changes1 = new Changes(dataSource);
Changes changes2 = new Changes(source);
// The changes can also be on a Table or on a Request
Changes changes3 = new Changes(table4);
Changes changes4 = new Changes(request3);
Changes changes5 = new Changes(request4);
// The names of the columns used for the primary key are found in the metadata for a table
// but for a request it can be important to set the primary key
Changes changes6 = new Changes(request4).setPksName("name");
The two Changes above
(changes1
and changes2
) are equivalent.
The two Changes above
(changes4
and changes5
) are also equivalent.
The changes are ordered :
As indicated above, the primary key is used to order the changes.
But more important, the primary key is used to determinate which rows at the same with modifications.
In Representation of "changes4" or "changes5" the modification of first row of the table become a creation and deletion.
Creation | "MEMBERS" table | 5 as PK |
|
|||||||||||||||||||||
Modification | "ALBUMS" table | 8 as PK |
|
|||||||||||||||||||||
Modification | "MEMBERS" table | 1 as PK |
|
|||||||||||||||||||||
Deletion | "ALBUMS" table | 15 as PK |
|
Creation | "MEMBERS" table | 5 as PK |
|
||||||||||||||||||
Modification | "MEMBERS" table | 1 as PK |
|
Creation | No PK |
|
|||||||||||||
Creation | No PK |
|
|||||||||||||
Deletion | No PK |
|
Creation | 'McGuiness' as PK |
|
|||||||||||||
Modification | 'Hewson' as PK |
|
A Change is an element of the Changes.
Below framed in red the first Change of "changes3".
Creation | "MEMBERS" table | 5 as PK |
|
||||||||||||||||||
Modification | "MEMBERS" table | 1 as PK |
|
A Row can represent a row of a Table , of a Request or of a Change.
Below framed in red the third Row of "table4".
ID | NAME | FIRSTNAME | SURNAME | SIZE |
---|---|---|---|---|
1 | 'Hewson' | 'Paul David' | 'Bono' | 1.75 |
2 | 'Evans' | 'David Howell' | 'The Edge' | 1.77 |
3 | 'Clayton' | 'Adam' | 1.78 | |
4 | 'Mullen' | 'Larry' | 1.70 |
Below framed in red the second Row of "request3".
NAME | FIRSTNAME | SURNAME |
---|---|---|
'Hewson' | 'Paul David' | 'Bono' |
'Evans' | 'David Howell' | 'The Edge' |
'Mullen' | 'Larry' |
Below framed in red the Row at end point of the second Change of "changes3".
Creation | "MEMBERS" table | 5 as PK |
|
||||||||||||||||||
Modification | "MEMBERS" table | 1 as PK |
|
A Column can represent a column of a Table , of a Request or of a Change.
Below framed in red the second Column of "table4".
ID | NAME | FIRSTNAME | SURNAME | SIZE |
---|---|---|---|---|
1 | 'Hewson' | 'Paul David' | 'Bono' | 1.75 |
2 | 'Evans' | 'David Howell' | 'The Edge' | 1.77 |
3 | 'Clayton' | 'Adam' | 1.78 | |
4 | 'Mullen' | 'Larry' | 1.70 |
Below framed in red the second Column of "request3".
NAME | FIRSTNAME | SURNAME |
---|---|---|
'Hewson' | 'Paul David' | 'Bono' |
'Evans' | 'David Howell' | 'The Edge' |
'Mullen' | 'Larry' |
Below framed in red the fourth Column of the second Change of "changes3".
Creation | "MEMBERS" table | 5 as PK |
|
||||||||||||||||||
Modification | "MEMBERS" table | 1 as PK |
|
A value can be in a Row or in a Column.
Below framed in red (depending of the path) :
ID | NAME | FIRSTNAME | SURNAME | SIZE |
---|---|---|---|---|
1 | 'Hewson' | 'Paul David' | 'Bono' | 1.75 |
2 | 'Evans' | 'David Howell' | 'The Edge' | 1.77 |
3 | 'Clayton' | 'Adam' | 1.78 | |
4 | 'Mullen' | 'Larry' | 1.70 |
Below framed in red (depending of the path) :
NAME | FIRSTNAME | SURNAME |
---|---|---|
'Hewson' | 'Paul David' | 'Bono' |
'Evans' | 'David Howell' | 'The Edge' |
'Mullen' | 'Larry' |
Below framed in red (depending of the path) :
Creation | "MEMBERS" table | 5 as PK |
|
||||||||||||||||||
Modification | "MEMBERS" table | 1 as PK |
|
As see above there are three root elements of database, but only Table
and Request are data elements.
All the possible types of data are contained in
the DataType enumeration.
The type of the data can be :
The change can be a creation, a modification or a deletion.
All the possible types of change are contained in
the ChangeType enumeration.
The type of the change depends of operation on database :
CREATION
for a insert
sql requestMODIFICATION
for a update
sql requestDELETION
for a delete
sql requestThe value can be a date, a boolean or a text for example.
All the possible types of value are contained in
the ValueType enumeration.
The type of the value depends of class of the object given by java.sql
when the data are got from database :
BYTES
for a array of bytes (byte[]
)BOOLEAN
for a java.lang.Boolean
TEXT
for a java.lang.String
DATE
for a java.sql.Date
TIME
for a java.sql.Time
DATE_TIME
for a java.sql.Timestamp
UUID
for a java.util.UUID
(since 1.1.0)NUMBER
for a java.lang.Byte
,
java.lang.Short
,
java.lang.Integer
,
java.lang.Long
,
java.lang.Double
,
java.lang.Float
or
java.math.BigDecimal
NOT_IDENTIFIED
for other cases (for example when the value is null
)Since 1.2.0
The order can be a ascending or descending.
All the possible types of order are contained in
the Table.Order.OrderType enumeration.
The type of the order can be :
The navigation offers the ability to chain assertions at different levels and instructions to go inside the sub-elements and return to root element. There are examples of the navigation in NavigationExamples.java
The assertThat(...)
static method of org.assertj.db.api.Assertions
allows to create a root assertion on a Table.
import static org.assertj.db.api.Assertions.assertThat;
assertThat(table)...
or on a Request.
import static org.assertj.db.api.Assertions.assertThat;
assertThat(request)...
From these root assertions, it is possible to navigate to the sub elements and return to the root element as in the picture below.
More details on this concept in feature highlight.
The assertThat(...)
static method of org.assertj.db.api.Assertions
allows to create a root assertion on Changes.
import static org.assertj.db.api.Assertions.assertThat;
assertThat(changes)...
From this root assertion, it is possible to navigate to the sub elements and return to the root element as in the picture below.
More details on this concept in feature highlight.
AssertJ-DB is compatible with Java 7, but we need a system to simply compare data in a database with date, time and date/time like LocalDate, LocalTime and LocalDateTime of Java 8.
So The DateValue, TimeValue and DateTimeValue classes are simpler but contains this kind of informations.
There is 4 kinds of static methods to instantiate these values :
of
which receives the informations as int
parameters
DateValue dateValue = DateValue.of(2007, 12, 23);
// With hours and minutes only
TimeValue timeValue1 = TimeValue.of(9, 1);
// With seconds additional
TimeValue timeValue2 = TimeValue.of(9, 1, 6);
// With nanoseconds additional
TimeValue timeValue3 = TimeValue.of(9, 1, 6, 3);
// With date only (so hour is midnight)
DateTimeValue dateTimeValue1 = DateTimeValue.of(dateValue);
// With date and time
DateTimeValue dateTimeValue2 = DateTimeValue.of(dateValue, timeValue1);
from
which receives the equivalent from java.sql
package
(java.sql.Date
,
java.sql.Time
and
java.sql.Timestamp
)
or a java.util.Calendar
(since version 1.1.0)
Date date = Date.valueOf("2007-12-23");
DateValue dateValue = DateValue.from(date);
Time time = Time.valueOf("09:01:06");
TimeValue timeValue = TimeValue.from(time);
Timestamp timestamp = Timestamp.valueOf("2007-12-23 09:01:06.000000003");
DateTimeValue dateTimeValue = DateTimeValue.from(timestamp);
// Since 1.1.0
Calendar calendar = Calendar.getInstance();
DateValue dateValueFromCal = DateValue.from(calendar);
TimeValue timeValueFromCal = TimeValue.from(calendar);
DateTimeValue dateTimeValueFromCal = DateTimeValue.from(calendar);
parse
which receives a String
to represent the value (this method can throw a
ParseException
)
DateValue dateValue = DateValue.parse("2007-12-23");
// With hours and minutes only
TimeValue timeValue1 = TimeValue.parse("09:01");
// With seconds additional
TimeValue timeValue2 = TimeValue.parse("09:01:06");
// With nanoseconds additional
TimeValue timeValue3 = TimeValue.parse("09:01:06.000000003");
// With date only (so hour is midnight)
DateTimeValue dateTimeValue1 = DateTimeValue.parse("2007-12-23");
// With date and time (hours and minutes only)
DateTimeValue dateTimeValue2 = DateTimeValue.parse("2007-12-23T09:01");
// With date and time (seconds additional)
DateTimeValue dateTimeValue2 = DateTimeValue.parse("2007-12-23T09:01:06");
// With date and time (nanoseconds additional)
DateTimeValue dateTimeValue2 = DateTimeValue.parse("2007-12-23T09:01:06.000000003");
now
(since version 1.1.0) which create an instance corresponding to the current moment.
DateValue dateValue = DateValue.now(); // The current date
TimeValue timeValue = TimeValue.now(); // The current time
DateTimeValue dateTimeValue = DateTimeValue.now(); // The current date/time
All these static methods (except for now
method) have equivalent constructors.
In assertj, it is possible to add a description with the methods of the Descriptable interface. This description is used in the error message if the assertion fails.
Due to the navigation, it is more complicated in asserj-db to know on which element an error is thrown. So to help the tester, there are default descriptions.
For example :"members table"
for an assertion on a table
"'select * from actor' request"
for an assertion on a request
"'select id, name, firstname, bi...' request"
for an assertion on a request with more text
"Row at index 0 of members table"
for an assertion on a row of a table
"Column at index 0 (column name : ID) of 'select * from members' request"
for an assertion on a column of a request
"Value at index 0 of Column at index 0 (column name : ID) of 'select * from members' request"
for an assertion on a value of a column of a request
"Value at index 0 (column name : ID) of Row at index 0 of 'select * from members' request"
for an assertion on a value of a row of a request
"Value at index 0 (column name : ID) of Row at end point of Change at index 0 (on table : MEMBERS and with primary key : [4]) of Changes on tables of 'sa/jdbc:h2:mem:test' source"
for an assertion on a value of the row at end point of a change on a table
This default description can be replaced by the choice of the tester by using the methods of Descriptable.
Since 1.1.0
Databases have different letter cases for the name of the elements. For example, the name of the table can be upper case either the name is inputed in upper case or not. So this concept (and feature too) is here to manage these shades.
It is possible to declare a LetterCase with a DataSource with LetterCase or with a Source with LetterCase.
The concept of LetterCase is composed of CaseConversion and CaseComparison.
The CaseConversion is used when getting a name with letter case from database : a table name, a column name or a primary key name.
There are three conversions modes : UPPER
which converts to upper case ("Name"
becomes "NAME"
),
LOWER which converts to lower case ("Name"
becomes "name"
)
and NO which keeps the case ("Name"
remains "Name"
).
Each name (table, column and primary key) got from the database is converted using a CaseConversion.
The CaseComparison is used when comparing something with letter case from database or with a parameter.
There are two comparison modes : IGNORE which compares String
s by ignoring the case ("Name"
is considered equal to "NAME"
)
and STRICT which compares String
s strictly ("Name"
is considered different from "NAME"
).
During navigation (e.g. from table to column) and assertion (e.g. on column name), the name are compared using a CaseComparison.
A LetterCase is created with the getLetterCase static method which has a CaseConversion and a CaseComparison as parameters.
LetterCase letterCase = LetterCase.getLetterCase(CaseConversions.NO, CaseComparisons.IGNORE)
In AssertJ-DB, there are three different uses of a LetterCase : the table name, the column name and the primary key name. That is the reason why the DataSourceWithLetterCase and the SourceWithLetterCase constructors have three LetterCase parameters.
The LetterCase on the tables is used :
The LetterCase on the columns is used :
The LetterCase on the primary keys is used :
The different LetterCase are explictly indicated for DataSourceWithLetterCase and SourceWithLetterCase. But for DataSource and Source, there are LetterCase too but there are implicit :
In this example, The uses of Source and SourceWithLetterCase are equivalent :
Source source = new Source("jdbc:h2:mem:test", "sa", "");
Table table = new Table(source, "members");
LetterCase tableLetterCase = LetterCase.getLetterCase(CaseConversions.NO, CaseComparisons.IGNORE);
LetterCase columnLetterCase = LetterCase.getLetterCase(CaseConversions.UPPER, CaseComparisons.IGNORE);
LetterCase pkLetterCase = LetterCase.getLetterCase(CaseConversions.UPPER, CaseComparisons.IGNORE);
Source sourceWithLC = new SourceWithLetterCase("jdbc:h2:mem:test", "sa", "",
tableLetterCase,
columnLetterCase,
pkLetterCase);
Table tableWithLC = new Table(sourceWithLC, "members");
And in this example, the uses of DataSource and DataSourceWithLetterCase are equivalent :
DataSource dataSource = .....
Table table = new Table(dataSource, "members");
LetterCase tableLetterCase = LetterCase.getLetterCase(CaseConversions.NO, CaseComparisons.IGNORE);
LetterCase columnLetterCase = LetterCase.getLetterCase(CaseConversions.UPPER, CaseComparisons.IGNORE);
LetterCase pkLetterCase = LetterCase.getLetterCase(CaseConversions.UPPER, CaseComparisons.IGNORE);
DataSource dataSourceWithLC = new DataSourceWithLetterCase(dataSource,
tableLetterCase,
columnLetterCase,
pkLetterCase);
Table tableWithLC = new Table(dataSourceWithLC, "members");
Note that the letter case is extensible because the getLetterCase static method's parameters are instances of the CaseConversion and the CaseComparison interfaces. So this is not limited to the implementations in the corresponding enumerations.
Since 1.1.0
It can be interesting to view the values on which an assertion is made (for example for debugging). The output allows that. This is a simple example :
import static org.assertj.db.output.Outputs.output;
Table table = new Table(dataSource, "members");
// Output the content of the table in the console
output(table).toConsole();
These lines give the result below :
[MEMBERS table] |-----------|---------|-----------|-----------|--------------|-----------|-----------|-----------| | | | * | | | | | | | | PRIMARY | ID | NAME | FIRSTNAME | SURNAME | BIRTHDATE | SIZE | | | KEY | (NUMBER) | (TEXT) | (TEXT) | (TEXT) | (DATE) | (NUMBER) | | | | Index : 0 | Index : 1 | Index : 2 | Index : 3 | Index : 4 | Index : 5 | |-----------|---------|-----------|-----------|--------------|-----------|-----------|-----------| | Index : 0 | 1 | 1 | Hewson | Paul David | Bono | 05-10-60 | 1.75 | | Index : 1 | 2 | 2 | Evans | David Howell | The Edge | 08-08-61 | 1.77 | | Index : 2 | 3 | 3 | Clayton | Adam | | 03-13-60 | 1.78 | | Index : 4 | 4 | 4 | Mullen | Larry | | 10-31-61 | 1.70 | |-----------|---------|-----------|-----------|--------------|-----------|-----------|-----------|
In the example above, the output is in plain text in the console. It is possible to change the type of the output and the destination.
There are two outputs already implemented :
// Change the output of the table to be HTML
output(table).withType(OutputType.HTML).....;
Note that the type of output is extensible because the withType(Output outputType) method's parameter is an instance of the Output interface. So this is not limited to the implementations in the OutputType enum.
The destination is the way to print the display. There are three destinations :
Note that with this last method the possibilities of destination are really flexible.
These three methods are fluent. In this short example, the output is a plain text representation in the console and a html output in a file :
// Display the content of the table with plain text in the console
// and with HTML output in the file
output(table).toConsole().withType(OutputType.HTML).toFile("test.html");