There're plenty of approaches we use for processing expected data to avoid hardcoding and tests cases' support simplification. In this article we'll look at a way of providing test data via DB.
As you may know, TestNG has internal mechanism for working with user data, called DataProvider (DP). It's pretty straightforward for common cases. But due to its static nature, we can face with different issues while tests' scaling. Parallel execution can deal with static context only if you manually take care about your objects' thread safety.
The other question is: what's the easiest way of pulling DB data using DP mechanism? Common approach assumes creating model with relative DAO / DAOImpl. It's an obvious task for pure developers, but could be very hard for automation engineers, who mostly don't have enough technical skills. Even if they had. The next question would be: how to ask DP pulling data independently from entity type, its schema or unknown rows amount? What if we need to use several entities within 1 test? What if we even need to use different DB? Quite enough questions to take a beer and completely forget about this idea...
If you're still here, I'll show how to create a generic thread-safe approach for pulling DB data and easily use your models within tests.
Let's start with preparation. You'll need 2 sample MySQL data sources: AUTOMATION and PRODUCTION with the following structure.
Fill in created tables with some sample data:
So now we have 2 DB with 5 tables in total. I guess you have some questions regarding IMPORT_DATA table purpose, but I'll explain it a little bit later.
Let's continue with configuration. Assuming that we're going to scale our tests, it's better to rely on one of existing specialized libraries to be confident with data thread-safety. So the first thing you need to read about is Hibernate ORM.
Now add the following dependencies into you pom.xml to start working with Hibernate:
To operate with DB data in Java code, first we need to create appropriate tables' representations. BaseEntity will be an abstract superclass for all the entities.
To avoid further ID duplication (as it's present in all tables), it will be enough to define this field only once on a parent class level.
Now let's create all the entities using JPA annotations:

Try to create appropriate entities for PRODUCTION database. It's important to specify both getters and setters for all the fields.
Well, we are ready to map newly created entities with real DB tables in Hibernate config files. As we have 2 data sources, we need to create xml for each:
Don't forget to change user credentials. As you can see, there're 3 mappings pointing to newly created entity classes. Create the same config for PRODUCTION data source by yourself. Now Hibernate can make a relation between entities and real DB tables.
To load config file and start using DB entities, we can create a simple utility class for sessions management:
As you can see, we establish connections with both DB, and it will be done before tests execution. To recognize them, we can use schema name as a key.
I've already mentioned, that we're looking for generic solution, so let's create a generic DAO interface with its implementation.
As we'll use only SELECT queries, generic DAO should be quite enough. You can ignore commit / rollback for common cases. It was left for some tricky moments. And here's some common actions implementation:
We're passing entity and schema as a constructor parameters to allow getting fields in a generic way.
That's pretty much related to DB configuration. So now we're ready for creating generic DP.
Let's assume that we can specify any amount of entities we want to use within particular test. But how do we pass them all into DP? Custom annotations will help.
If we used a single DB, 1 common annotation with entity class as a parameter could be quite enough. But in case of multiple data sources, we face with the following questions:
So ideally, we need to define annotation that will also contain a schema name besides entity. But how could these entities be merged together then? Thanks to Java 8 developers and their array of annotations improvements.
Repeatable option allows us to use multiple Entity annotations for a single test case. As you can also see, there were added 2 more parameters: invocationCount and ids. Both are optional, but can be very important. If we specify several entities with different sizes, which size should be chosen by DP as a base one? To handle such situation there was added a min size lookup logic. This minimum should be used to iterate retrieved data.
Now let's look at DP implementation:
As you can see, we're looping through entities annotations and creating generic DAO using entity class name and schema. Next goal is to analyze optional parameters and choose a valid records' extraction strategy. IDs array has a top priority. If it's not specified, we retrieve everything. Depending on computed min size, we prepare an output container. To store transitional results (retrieved records), we use simple DataSet objects list.
Let's look at how simple and flexible is now using generic DP in tests:
And here are results of above tests execution in 3 parallel threads:
As you can see, we can specify as many entities we want, independently from data source. It could be even DB of different types, e.g. MySQL and Oracle. You can also use the same entities with different ids.
The last thing I want to cover is IMPORT_DATA table example, that is used in a third test. As you may notice, this table differs from others. It consists of pure foreign keys. It's a little bit tricky moment. If we need lots of entities within a single test, it's much easier to create a composite table that refers to particular records from other tables, instead of N separate entities setting via annotations. Just drill into ImportData entity again to see how Hibernate can easily retrieve Users and Files records by their foreign keys.
That's pretty much it. You can find full source code, as always, on GitHub. Take your time and happy codding!
As you may know, TestNG has internal mechanism for working with user data, called DataProvider (DP). It's pretty straightforward for common cases. But due to its static nature, we can face with different issues while tests' scaling. Parallel execution can deal with static context only if you manually take care about your objects' thread safety.
The other question is: what's the easiest way of pulling DB data using DP mechanism? Common approach assumes creating model with relative DAO / DAOImpl. It's an obvious task for pure developers, but could be very hard for automation engineers, who mostly don't have enough technical skills. Even if they had. The next question would be: how to ask DP pulling data independently from entity type, its schema or unknown rows amount? What if we need to use several entities within 1 test? What if we even need to use different DB? Quite enough questions to take a beer and completely forget about this idea...
If you're still here, I'll show how to create a generic thread-safe approach for pulling DB data and easily use your models within tests.
Let's start with preparation. You'll need 2 sample MySQL data sources: AUTOMATION and PRODUCTION with the following structure.
Fill in created tables with some sample data:
So now we have 2 DB with 5 tables in total. I guess you have some questions regarding IMPORT_DATA table purpose, but I'll explain it a little bit later.
Let's continue with configuration. Assuming that we're going to scale our tests, it's better to rely on one of existing specialized libraries to be confident with data thread-safety. So the first thing you need to read about is Hibernate ORM.
Now add the following dependencies into you pom.xml to start working with Hibernate:
To operate with DB data in Java code, first we need to create appropriate tables' representations. BaseEntity will be an abstract superclass for all the entities.
To avoid further ID duplication (as it's present in all tables), it will be enough to define this field only once on a parent class level.
Now let's create all the entities using JPA annotations:
Try to create appropriate entities for PRODUCTION database. It's important to specify both getters and setters for all the fields.
Well, we are ready to map newly created entities with real DB tables in Hibernate config files. As we have 2 data sources, we need to create xml for each:
Don't forget to change user credentials. As you can see, there're 3 mappings pointing to newly created entity classes. Create the same config for PRODUCTION data source by yourself. Now Hibernate can make a relation between entities and real DB tables.
To load config file and start using DB entities, we can create a simple utility class for sessions management:
As you can see, we establish connections with both DB, and it will be done before tests execution. To recognize them, we can use schema name as a key.
I've already mentioned, that we're looking for generic solution, so let's create a generic DAO interface with its implementation.
As we'll use only SELECT queries, generic DAO should be quite enough. You can ignore commit / rollback for common cases. It was left for some tricky moments. And here's some common actions implementation:
We're passing entity and schema as a constructor parameters to allow getting fields in a generic way.
That's pretty much related to DB configuration. So now we're ready for creating generic DP.
Let's assume that we can specify any amount of entities we want to use within particular test. But how do we pass them all into DP? Custom annotations will help.
If we used a single DB, 1 common annotation with entity class as a parameter could be quite enough. But in case of multiple data sources, we face with the following questions:
- How to define a list of entities?
- How to identify schema for each specified entity?
So ideally, we need to define annotation that will also contain a schema name besides entity. But how could these entities be merged together then? Thanks to Java 8 developers and their array of annotations improvements.
Repeatable option allows us to use multiple Entity annotations for a single test case. As you can also see, there were added 2 more parameters: invocationCount and ids. Both are optional, but can be very important. If we specify several entities with different sizes, which size should be chosen by DP as a base one? To handle such situation there was added a min size lookup logic. This minimum should be used to iterate retrieved data.
Now let's look at DP implementation:
As you can see, we're looping through entities annotations and creating generic DAO using entity class name and schema. Next goal is to analyze optional parameters and choose a valid records' extraction strategy. IDs array has a top priority. If it's not specified, we retrieve everything. Depending on computed min size, we prepare an output container. To store transitional results (retrieved records), we use simple DataSet objects list.
Let's look at how simple and flexible is now using generic DP in tests:
And here are results of above tests execution in 3 parallel threads:
As you can see, we can specify as many entities we want, independently from data source. It could be even DB of different types, e.g. MySQL and Oracle. You can also use the same entities with different ids.
The last thing I want to cover is IMPORT_DATA table example, that is used in a third test. As you may notice, this table differs from others. It consists of pure foreign keys. It's a little bit tricky moment. If we need lots of entities within a single test, it's much easier to create a composite table that refers to particular records from other tables, instead of N separate entities setting via annotations. Just drill into ImportData entity again to see how Hibernate can easily retrieve Users and Files records by their foreign keys.
That's pretty much it. You can find full source code, as always, on GitHub. Take your time and happy codding!
