Concepts

For the examples below, suppose that the database contains these three tables :

MEMBERS
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

ALBUMS
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

GROUP
ID	NAME
1	'U2'
2	'Coldplay'

Connection to the database

To make assertions on a database, it is necessary to connect. The concept of AssertDbConnection represent how AssertJ-DB can retrieve data and schema information from database.

It’s also with a AssertDbConnection that you can instantiate the following element : Table, Request, Changes.

AssertDbConnection

A AssertDbConnection is created with the factory AssertDbConnectionFactory.

There are 2 way to begin the AssertDbConnectionFactory, with a DataSource ( the classic Java way to get a Connection to a database ) or with JDBC connection information.

Below is an example of using a DataSource to connect to H2 in memory database :

AssertDbConnection connection = AssertDbConnectionFactory.of("jdbc:h2:mem:test", "sa", "").create();

And using a DataSource to connect :

AssertDbConnection connection = AssertDbConnectionFactory.of(dataSource).create();

LetterCase setup

AssertDbConnectionFactory provide the capacity to indicate the LetterCase to use for the tables, columns and primary keys.

AssertDbConnection connection = AssertDbConnectionFactory
    .of(dataSource)
    .letterCase(tableLetterCase, columnLetterCase, pkLetterCase)
    .create();

For more information, see the paragraph on LetterCase.

Schema retrieval mode

Since 3.0.0

For many assertions, AssertJ-DB require to discover database schema metadata ( list of tables, columns, … ). When the schema contains many tables this operation can slow down the tests executions.

To avoid that and when the database schema is not updated during the test session, you can use the option SchemaMetaDataMode of AssertDbConnectionFactory to keep in memory the schema.

Available mode are :

DYNAMIC ( default ): Retrieve schema metadata each time is required.
STATIC : Retrieve schema metadata only once and keep in memory for all duration of connection.

Below is an example of using the STATIC mode for a connection :

AssertDbConnection connection = AssertDbConnectionFactory.of(dataSource)
    .schemaMetaDataMode(SchemaMetaDataMode.STATIC)
    .create();

Elements of the database

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).

Table

A Table represents a table in the database.

A Table can be constructed with the builder method table(String name) from AssertDbConnection.

// Prepare the connection
AssertDbConnection connection = ...
// Declare the "members" table by using a AssertDbConnection table builder
Table table1 = connection.table("members").build();

For more information about table construction, see the Table.Builder javadoc.

Representation of "table1"
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 table2 = connection.table("members").columnsToCheck(new String[] { "id", "name" }).build();
// Get the data of the "members" table but not of the "birthdate" column
Table table3 = connection.table("members").columnsToExclude(new String[] { "birthdate" }).build();
// Get the data of the "name" column of the "members" table (because "id" is included and excluded)
Table table4 = connection.table("members").columnsToCheck(new String[] { "id", "name" }).columnsToExclude(new String[] { "id" }).build();

Representation of "table2"
ID	NAME
1	'Hewson'
2	'Evans'
3	'Clayton'
4	'Mullen'

Representation of "table3"
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

Representation of "table4"
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 useful 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 table5 = connection.table("group").build();
// Get the data of the "group" table by using "`" delimiter
// That generates a request
Table table6 = connection.table("group").delimiters('`', '`').build();

// Get the data from "members" table and order on "name" column in ascending order
Table table7 = connection.table("members").columnsToOrder(new Order[] { Order.asc("name") }).build();

Representation of "table6"
ID	NAME
1	'U2'
2	'Colplay'

Representation of "table7"
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

Request

A Request represents a SQL request on the database.

Like a Table, a Request can be constructed with the builder method request(String sql) from AssertDbConnection.

// Prepare the connection
AssertDbConnection connection = ...
// Declare a request which gets the name and the firstname of the corresponding members
Request request1 = connection.request("select name, firstname from members where id = 2 or id = 3").build();

For more information about request construction, see the Request.Builder javadoc.

Representation of "request1"
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 request2 = connection.request(
                               "select name, firstname from members " +
                               "where name like ?;")
                               .parameters("%e%")
                               .build();
// Declare a request which gets the name and the firstname of the members
// and use "%e%" and "%Paul%" as parameters
Request request3 = connection.request(
                               "select name, firstname from members " +
                               "where name like ? and firstname like ?;")
                               .parameters("%e%", "%Paul%")
                               .build();

Representation of "request2"
NAME	FIRSTNAME	SURNAME
'Hewson'	'Paul David'	'Bono'
'Evans'	'David Howell'	'The Edge'
'Mullen'	'Larry'

Representation of "request3"
NAME	FIRSTNAME	SURNAME
'Hewson'	'Paul David'	'Bono'

Changes

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;

// Prepare the connection
AssertDbConnection connection = ...
// The changes can be on a DataSource or on a Source
Changes changes1 = connection.changes().build();
// The changes can also be on a Table or on a Request
Changes changes2 = connection.changes().table(table3).build();
Changes changes3 = connection.changes().request(request2).build();
// 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 changes4 = connection.changes().request("select name, firstname from members", r -> r.pksName("name")).build();

For more information about changes construction, see the Changes.Builder javadoc.

The changes are ordered :

First by the type of the change : creation, modification and after deletion
After if it is a change on a table by the name of the table
To finish by the values of the primary key and if there are no primary key by the values of the row (for a modification)

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 "changes3" the modification of first row of the table become a creation and deletion.

Creation

"MEMBERS" table

5 as PK

NAME

FIRSTNAME

SURNAME

BIRTHDATE

SIZE

At start point

At end point

'McGuiness'

'Paul'

Modification

"ALBUMS" table

8 as PK

	ID	RELEASE	TITLE	NUMBEROFSONGS	DURATION	LIVE
At start point	8	10-10-88	'Rattle and Hum'	17	72:27
At end point	8	10-10-88	'Rattle & Hum'	17	72:27	true

Modification

"MEMBERS" table

1 as PK

	ID	NAME	FIRSTNAME	SURNAME	BIRTHDATE	SIZE
At start point	1	'Hewson'	'Paul David'	'Bono'	05-10-60	1.75
At end point	1	'Hewson'	'Paul David'	'Bono Vox'	05-10-60	1.75

Deletion

"ALBUMS" table

15 as PK

	ID	RELEASE	TITLE	NUMBEROFSONGS	DURATION	LIVE
At start point	15	09-09-14	'Songs of Innocence'	11	48:11
At end point

Creation

"MEMBERS" table

5 as PK

NAME

FIRSTNAME

SURNAME

SIZE

At start point

At end point

'McGuiness'

'Paul'

Modification

"MEMBERS" table

1 as PK

NAME

FIRSTNAME

SURNAME

SIZE

At start point

'Hewson'

'Paul David'

'Bono'

1.75

At end point

'Hewson'

'Paul David'

'Bono Vox'

1.75

Representation of "changes3"

Creation

No PK

NAME

FIRSTNAME

SURNAME

At start point

At end point

'Hewson'

'Paul David'

'Bono Vox'

Creation

No PK

NAME

FIRSTNAME

SURNAME

At start point

At end point

'McGuiness'

'Paul'

Deletion

No PK

NAME

FIRSTNAME

SURNAME

At start point

At end point

'Hewson'

'Paul David'

'Bono Vox'

Creation

'McGuiness' as PK

NAME

FIRSTNAME

SURNAME

At start point

At end point

'McGuiness'

'Paul'

Modification

'Hewson' as PK

NAME

FIRSTNAME

SURNAME

At start point

'Hewson'

'Paul David'

'Bono'

At end point

'Hewson'

'Paul David'

'Bono Vox'

Change

A Change is an element of the Changes.

Below framed in red the first Change of "changes2".

Creation

"MEMBERS" table

5 as PK

NAME

FIRSTNAME

SURNAME

SIZE

At start point

At end point

'McGuiness'

'Paul'

Modification

"MEMBERS" table

1 as PK

NAME

FIRSTNAME

SURNAME

SIZE

At start point

'Hewson'

'Paul David'

'Bono'

1.75

At end point

'Hewson'

'Paul David'

'Bono Vox'

1.75

Row

A Row can represent a row of a Table , of a Request or of a Change.

Below framed in red the third Row of "table3".

Representation of "table3"
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 "request2".

Representation of "request2"
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

NAME

FIRSTNAME

SURNAME

SIZE

At start point

At end point

'McGuiness'

'Paul'

Modification

"MEMBERS" table

1 as PK

NAME

FIRSTNAME

SURNAME

SIZE

At start point

'Hewson'

'Paul David'

'Bono'

1.75

At end point

'Hewson'

'Paul David'

'Bono Vox'

1.75

Column

A Column can represent a column of a Table , of a Request or of a Change.

Below framed in red the second Column of "table3".

Representation of "table3"
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 "request2".

Representation of "request2"
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

NAME

FIRSTNAME

SURNAME

SIZE

At start point

At end point

'McGuiness'

'Paul'

Modification

"MEMBERS" table

1 as PK

NAME

FIRSTNAME

SURNAME

SIZE

At start point

'Hewson'

'Paul David'

'Bono'

1.75

At end point

'Hewson'

'Paul David'

'Bono Vox'

1.75

Value

A value can be in a Row or in a Column.

Below framed in red (depending of the path) :

the second value of the third Row of "table3"
the third value of the second Column of "table3"

Representation of "table3"
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) :

the second value of the second Row of "request2"
the second value of the second Column of "request2"

Representation of "request2"
NAME	FIRSTNAME	SURNAME
'Hewson'	'Paul David'	'Bono'
'Evans'	'David Howell'	'The Edge'
'Mullen'	'Larry'

Below framed in red (depending of the path) :

the fourth value of the Row at end point of the second Change of "changes2"
the value at end point of the fourth Column of the second Change of "changes2"

Creation

"MEMBERS" table

5 as PK

NAME

FIRSTNAME

SURNAME

SIZE

At start point

At end point

'McGuiness'

'Paul'

Modification

"MEMBERS" table

1 as PK

NAME

FIRSTNAME

SURNAME

SIZE

At start point

'Hewson'

'Paul David'

'Bono'

1.75

At end point

'Hewson'

'Paul David'

'Bono Vox'

1.75

Type

Data Type

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 :

TABLE
REQUEST

Change Type

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 request
MODIFICATION for a update sql request
DELETION for a delete sql request

Value Type

The 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)

Order Type

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 :

ASC for an ascending order
DESC for a descending order

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

With a Table or a Request as root

The assertThat(…) static method of Assertions allows to create a root assertion on a Table.

// From v3.0.2
import static org.assertj.core.api.Assertions.assertThat;

// For versions <= 3.0.1 :
// import static org.assertj.db.api.Assertions.assertThat;

assertThat(table)...

or on a Request.

// From v3.0.2
import static org.assertj.core.api.Assertions.assertThat;

// For versions <= 3.0.1 :
// 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.

                                             origin
                 instantiate+-----------+<-----------=+----------------------+
                 +--------->|On a Column| instantiate |On a Value Of a Column|
                 |          +-----+-----+=----------->+----------------------+
                 :          origin:
+----------------+------+<--------+
|On a Table Or a Request|
+----------------+------+<--------+
                 :          origin:       origin
                 |          +-----+--+<-----------=+-------------------+
                 +--------->|On a Row| instantiate |On a Value Of a Row|
                 instantiate+--------+=----------->+-------------------+

More details on this concept in assertj-db-features-highlight.html#tableorrequestasroot[feature highlight].

With Changes as root

The assertThat(…) static method of Assertions allows to create a root assertion on Changes.

// From v3.0.2
import static org.assertj.core.api.Assertions.assertThat;

// For versions <= 3.0.1 :
// 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.

                                                            origin
                                instantiate+-----------+<-----------=+----------------------+
                                +--------->|On a Column| instantiate |On a Value Of a Column|
                                |          +-----+-----+=----------->+----------------------+
                 origin         :          origin:
+-----------+<=-----------+-----+-----+<---------+
|On Changes | instantiate |On a Change|
+-----------+=----------->+-----+-----+<---------+
                                :          origin:       origin
                                |          +-----+--+<-----------=+-------------------+
                                +--------->|On a Row| instantiate |On a Value Of a Row|
                                instantiate+--------+=----------->+-------------------+

More details on this concept in feature highlight.

DateValue, TimeValue and DateTimeValue

Since 2.0.0, AssertJ-DB baseline is Java 8. The preferred way to compare values with date, time and date/time is to use java.time.LocalDate, java.time.LocalTime, java.time.LocalDateTime directly.

But for the backward compatibility, it’s always possible to use AssertJ-DB DateValue utilities.

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 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 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.

Default description

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 assertj-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.

Letter Case of the database

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 inputted 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 AssertDbConnection with LetterCase.

The concept of LetterCase is composed of CaseConversion and CaseComparison.

CaseConversion

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.

CaseComparison

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.

LetterCase

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 AssertDbConnection have three LetterCase parameters.

The LetterCase on the tables is used :

to convert the table name : when a name is got from the database like for the Table instantiation or for the table with changes found with Changes.
to compare the table name : for the instantiation when the table is search in the database for Table, for navigation (e.g. from changes to a change on a table) or for a assertion (like isOnTable(String name)).

The LetterCase on the columns is used :

to convert the column name : when a column name is got from the database for a table or a request
to compare the column name : for the navigation (e.g. from a table to a column) or for a assertion (like hasColumnName(String columnName)).

The LetterCase on the primary keys is used :

to convert the primary key name : when a primary key name is got from the database for a table
to compare the primary key name : for a assertion (like hasPksNames(String… names)).

The default settings for LetterCase in AssertDbConnection are :

NO conversion and IGNORE comparison for table names
UPPER conversion and IGNORE comparison for the column and primary key name

In this example, the two connections are equivalent :

AssertDbConnection jdbcConnection = AssertDbConnectionFactory.of("jdbc:h2:mem:test", "sa", "").create();
Table table = jdbcConnection.table("members").build();

LetterCase tableLetterCase = LetterCase.getLetterCase(CaseConversions.NO, CaseComparisons.IGNORE);
LetterCase columnLetterCase = LetterCase.getLetterCase(CaseConversions.UPPER, CaseComparisons.IGNORE);
LetterCase pkLetterCase = LetterCase.getLetterCase(CaseConversions.UPPER, CaseComparisons.IGNORE);
AssertDbConnection connectionWithLC = AssertDbConnectionFactory.of("jdbc:h2:mem:test", "sa", "")
                                        .letterCase(tableLetterCase, columnLetterCase, pkLetterCase)
                                        .create();
Table tableWithLC = connectionWithLC.table("members").build();

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.

Output

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 = connection.table("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.

Type of output

There are two outputs already implemented :

PLAIN : shown in the example above (the default output type)
HTML : which represents the result as an HTML document

// 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.

Destination

The destination is the way to print the display. There are three destinations :

the console (with the toConsole() method)
a file (with the toFile(String fileName) method)
a stream (with the toStream(OutputStream outputStream) method)

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");