Often in automation, our tests need to perform actions on the database. Sometimes we need to set up some test data. Other times we need to query the database in search for test data. And let’s not forget about cleaning up the data we used and no longer require. In this article, I will show how you can use Spring’s JdbcTemplate class for easily working with a MySQL database from your Java automated tests.
💡 You should also check out this cool MySQL Database Addon provided by the community to TestProject’s Addons Library, enabling you to extend your testing capabilities with pre-built automation actions you can incorporate into your recorded and coded tests instantly.
Requirements
Before we can start interacting with the database, there are a few things we need to setup. Namely the dependencies we need to add to our project. In my case, because I am using Maven, the dependencies I need to add to the pom.xml file will look like this:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.23</version>
</dependency>
The first dependency we see here is the one from the Spring package. This is where we will find the JdbcTemplate class we will use for communicating with the DB. This class contains valuable methods for updating or gathering data from the database. The second dependency is required for being able to communicate with the MySQL instance.
❗ Note: These dependencies have the latest available version (as seen in Maven repository). The ‘mysql-connector-java’ version needs to be in sync with the version of the MySQL instance your DB runs on. In my case, I am running a MySQL server with version >8, so my ‘mysql-connector-java’ version is also higher than 8.
Connecting to the database
Once the dependencies are in place, we can set up the connection to our database. We could write the code required for this operation in our test. However, we will surely need this code in other test classes as well. Therefore, this code could be written either in a dedicated class for working with the database, or in a base class your tests are extending. No matter what option you choose, the connection can be created in a method like this one:
public DataSource mysqlDataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://dbURL:portNumber/nameOfDB?useSSL=false");
dataSource.setUsername("username");
dataSource.setPassword("password");
return dataSource;
}
The first thing we need to address here is that this method returns a ‘DataSource’. It will be required when we initialize the JdbcTemplate class we will use in our tests, because it stores the database connection.
Next, in this example, I used the value “com.mysql.cj.jdbc.Driver” for setting the driver class name. This is again required for the connection, and in some cases, with older versions of the MySQL connector dependencies, this should be replaced by “com.mysql.jdbc.Driver”. In case you are using the incorrect one, you will get a relevant warning when attempting to connect to the database.
You will need to provide the location of the database to the ‘setUrl’ method. It is made up of the URL, port, and database name. And of course, you need to provide the username and password for connecting to the database, to the methods ‘setUsername’ and ‘setPassword’.
Now that the connection is set up, we need to initialize the JdbcTemplate class. We can declare a variable of this type in our test class:
private JdbcTemplate jdbcTemplate;
Then, in the @BeforeAll method, we can initialize this variable, by providing the connection we established with the DB:
jdbcTemplate = new JdbcTemplate(nameOfClass.mysqlDataSource());
At this point the setup is done, the connection is up, and we can start updating or querying the database.
Update
In the JdbcTemplate class, we will find a lot of useful methods. One of them, ‘update’, can be used to create tables, update tables, add data to them, or even delete data. There are several variants for this method (with different signatures), but the one I will exemplify here takes a single parameter: a SQL statement as String.
Example
Let’s create two new tables: one named ‘meal’ and one named ‘ingredient’. In the ‘meal’ table we want to store the name of a meal, a category we assign to it (representing whether it is a breakfast, lunch, or dinner meal), and an auto-generated id as primary key. To create the table we will write the following code in a test method:
jdbcTemplate.update("create table meal(\n" +
" meal_id bigint auto_increment primary key,\n" +
" name varchar(50) not null unique,\n" +
" category varchar(50) not null\n" + ");");
When we run the test, the table will be created. Nothing else is needed in order to have the table created. Let’s say we also want to add two meals to this table: Chicken Fajita and Enchilada. We will do this easily, by providing the required SQL statement to the ‘update’ method as follows:
jdbcTemplate.update("insert into meal (name, category) values ('Chicken Fajita', 'lunch');");
jdbcTemplate.update("insert into meal (name, category) values ('Enchilada', 'lunch');");
As you can see, we have one ‘update’ method call per one SQL operation.
Now, let’s also create a table called ‘ingredient’. It will not have any auto-generated primary key. However, it will have a foreign key, corresponding to the ‘meal_id’ from the ‘meal’ table. Each entry in this table represents an ingredient corresponding to a meal from the ‘meal’ table. This foreign key will link the ingredient to the meal. Additionally, the ‘ingredient’ table has columns for ‘name’ of the ingredient, ‘quantity’, and ‘uom’ (unit of measure) for the quantity of the ingredient.
To create this table, then add a foreign key to it, we will again use the ‘update’ method, to which we will provide the relevant SQL statements:
jdbcTemplate.update("create table ingredient(\n" +
" meal_id bigint not null,\n" +
" name varchar(50) not null,\n" +
" quantity bigint not null,\n" +
" uom varchar(50) not null\n" + ");");
jdbcTemplate.update("alter table ingredient add foreign key (meal_id)" +
" references meal(meal_id);\n");
To have more useful data for our following examples, I will also add some data to the ‘ingredient’ table:
jdbcTemplate.update("insert into ingredient (meal_id, name, quantity,"
+ " uom) values ((select meal_id from meal where name = 'Chicken Fajita'), 'chicken', 1, 'kg');\n");
jdbcTemplate.update("insert into ingredient (meal_id, name, quantity, uom) " +
"values ((select meal_id from meal where name = 'Chicken Fajita'), 'red pepper', 1, 'piece');\n");
jdbcTemplate.update("insert into ingredient (meal_id, name, quantity, uom) " +
"values ((select meal_id from meal where name = 'Chicken Fajita'), 'green pepper', 1, 'piece');\n");
jdbcTemplate.update("insert into ingredient (meal_id, name, quantity, uom) " +
"values ((select meal_id from meal where name = 'Chicken Fajita'), 'yellow pepper', 1, 'piece');");
jdbcTemplate.update("insert into ingredient (meal_id, name, quantity," + " uom) " +
"values ((select meal_id from meal where name = " + "'Enchilada'), 'chicken', 1, 'kg');\n");
jdbcTemplate.update("insert into ingredient (meal_id, name, quantity," + " uom) " +
"values ((select meal_id from meal where name = " + "'Enchilada'), 'cheese', 100, 'grams');\n");
jdbcTemplate.update("insert into ingredient (meal_id, name, quantity," + " uom) " +
"values ((select meal_id from meal where name = " + "'Enchilada'), 'tomato', 1, 'piece');\n");
All right. Now we have 2 tables with some data, that we can query. We will use different methods from the JdbcTemplate class to grab results of different types.
queryForObject – retrieve a single value
In case we need to query the database for a single value, we can use the ‘queryForObject’ method. This method also has several variants of usage, but here we will look at an easy one:
jdbcTemplate.queryForObject(String sqlStatement, Class returnType);
When calling this method, we need to specify what return type the query needs to have, as a Class. We could, for instance, retrieve a String value (by specifying String.class), or an integer (by specifying Integer.class).
Example
We need to query the database to retrieve the value of the ‘meal_id’ from the ‘meal’ table for the meal ‘Chicken Fajita’. We need to store this result in an int variable:
int id = jdbcTemplate.queryForObject("select meal_id from meal
where name='Chicken Fajita';", Integer.class);
Here you can see that the return type of the query is specified as Integer.class, hence the result is stored in an int variable. Let’s say that in the test we also want to print the result of this query to the console:
System.out.println("Meal id for Chicken Fajita = " + id);
The result of this print will be:
Meal id for Chicken Fajita = 1
queryForMap – retrieve a row
Now let’s say you want to return an entire row from a table. Or parts of a row. You can do that using the ‘queryForMap’ method, to which you will provide the required SQL statement:
jdbcTemplate.queryForMap(String sqlStatement);
The result of this query can be stored in a variable of type Map. The keys of the map will correspond to each column name a row item belongs to. The value will correspond to the actual value from the row, corresponding to that column.
Example
We want to extract all the details of the meal with id 1 from the ‘meal’ table, store them into a variable, and print the result to the console. This will be done easily:
Map<String, Object> entireRowAsMap = jdbcTemplate.queryForMap("select * from meal where meal_id = 1");
System.out.println("All details of meal with id 1 = " + entireRowAsMap);
As you can see, the variable ‘entireRowAsMap’ represents a Map, whose keys are String, but the values are Objects. This happens because some of the values are Integer, some are String, and of course, all of these types are Objects in Java. The console output for the above code is:
All details of meal with id 1 = {meal_id=1, name=Chicken Fajita, category=lunch}
queryForList – retrieve a column
For when you need to retrieve either all the values or part of the values from a given column, you can use the ‘queryForList’ method. In this version I will exemplify, it requires an SQL statement and a return type for the resulting elements. That means the type of elements you will store in a Java List. For example, if all the elements you retrieve with this query are integers, the return type will be Integer.class. The basic usage for the method is:
jdbcTemplate.queryForList(String sqlStatement, Class returnType);
Example
We want to store, into a Java List, all the distinct ingredient names we have in the ‘ingredient’ table. But we also want to print these values to the console. This can be achieved:
List<String> queryForColumn = jdbcTemplate.queryForList("select " +
"distinct name from ingredient", String.class);
System.out.println("All available ingredients = " + queryForColumn);
Because all the ingredient names are Strings, the return type for the ‘queryForList’ method is String.class. What is printed to the console:
All available ingredients = [chicken, red pepper, green pepper, yellow pepper, cheese, tomato]
queryForList – retrieve a list of rows
Another usage for the ‘queryForList’ method is for retrieving more than one row. To achieve this, the only parameter required when calling this method is the SQL statement that gathers the data. The return type will be a List of Maps, where each Map will have a String key and an Object value. This method looks like this:
jdbcTemplate.queryForList(String sqlStatement);
Example
Select all the values from the ‘meal’ table, store and print them.
List<Map<String, Object>> severalRowsAsListOfMaps = jdbcTemplate.queryForList("select * from meal;");
System.out.println("All available meals = " + severalRowsAsListOfMaps);
Here, the printed output represents a List of Maps:
All available meals = [{meal_id=1, name=Chicken Fajita, category=lunch}, {meal_id=2, name=Enchilada, category=lunch}]
Passing parameters to queries
In some cases, the SQL statements would require a parameter to be passed to them, to replace a hardcoded value from the query. For example, you might want to run the same query for identifying a row in the database based on its id. But you might need the id to be passed to the test via a DataProvider. Hence, on each run of the method, you would have a different id value for running the query.
In this case, for any query you want to run, instead of providing an explicit value, you will provide the ‘?’ character. This will be done inside the SQL statement.
Example
In a test method, we need to find out how many rows exist in the ‘ingredient’ table with a name which is provided as a parameter. The result of this query will be stored to an int variable, and it will be printed to the console. This can be achieved as follows:
Integer howManyUsages = jdbcTemplate.queryForObject("select count(*) "
+ "from ingredient where name=?", Integer.class, ingredientToLookFor);
System.out.println("How many time does the ingredient passed as "
+ "parameter appear in the DB " + " = " + howManyUsages);
The second parameter passed to the ‘queryForObject’ method is the return type for the query, whereas the third parameter is the name of the parameter which will be sent to the query from the DataProvider. For example, if the value of the ‘ingredientToLookFor’ parameter is ‘chicken’, the console output would be:
How many time does the ingredient passed as parameter appear in the DB = 2
Extracting data into a Java Object
Remember my article on using Java Objects to model data extracted from a DB? You can easily use JdbcTemplate to query the database, and extract the result directly into an Object. All you need to complete this task is a Java Object to model the data; a row mapper class that maps a column from the database to an Object property; the query which extracts the data into the Object using the row mapper.
Example
Let’s say we need to model the data corresponding to an ingredient whose name contains the text ‘yellow’ into an Ingredient Object. This means we want the Object to have the same properties that the ingredient has in the table. We want to map each column to a property. Therefore, we will create the Java Object called Ingredient. Its properties will be:
public int meal_id; public String name; public int quantity; public String uom;
It’s a good idea to keep the names of the properties in sync with the names of the database columns. This way you can easily identify each property. Because this is an Object, we will at least need to create the ‘equals’, ‘hashCode’ and ‘toString’ methods. I will skip these for now.
But instead, I will show something else that you need, namely the setter methods for each property. You can easily auto-generate these from IntelliJ using the Alt + Insert shortcut while in the editor. They will be:
public void setMeal_id ( int meal_id){
this.meal_id = meal_id;
}
public void setName (String name){
this.name = name;
}
public void setQuantity ( int quantity){
this.quantity = quantity;
}
public void setUom (String uom){
this.uom = uom;
}
You will use these for mapping the DB data to the Object properties. And that will happen inside the row mapper class we will create next. The entire body of this class is:
public class IngredientRowMapper implements RowMapper<Ingredient> {
@Override
public Ingredient mapRow(ResultSet rs, int rowNum) throws SQLException {
Ingredient ingredient = new Ingredient();
ingredient.setMeal_id(rs.getInt("meal_id"));
ingredient.setName(rs.getString("name"));
ingredient.setQuantity(rs.getInt("quantity"));
ingredient.setUom(rs.getString("uom"));
return ingredient;
}
}
As you can see, this class needs to implement an interface called RowMapper. Because of this, it will need to implement a ‘mapRow’ method. And inside this method is where you will map each Object property to a database column, using the setters. So, for example, for the ‘quantity’ property, the setQuantity method will set the value extracted from an entire row, whose corresponding column name is ‘quantity’.
Having created the IntegerRowMapper class, we can fulfill the task we set out to accomplish by using the ‘queryForObject’ to extract the data corresponding to the ‘yellow’ ingredient:
Ingredient ingredient = jdbcTemplate.queryForObject("select * from "
+ "ingredient where name like '%yellow%'", new IngredientRowMapper());
System.out.println("The ingredient object = " + ingredient);
The result of this query will be displayed, as an Object with its corresponding properties:
The ingredient object = Ingredient{meal_id=1, name='yellow pepper', quantity=1, uom='piece'}
Conclusion
We have seen several ways of working with databases from our automated tests: whether we need to update it or just retrieve data, there are plenty of methods in the JdbcTemplate class to help us. You can choose which one to use based on what you want your SQL statement to return.