Database testing with Java

Automated from the UI is good, but it’s better if we can verify and comparing the value on the web UI with the actual value from the database, that helps us to make sure it’s correct and integrity. This article will show you how to read and verify database values with Java code.

I will assume that you have successfully installed XAMPP so we can create the new database, table for testing this code.

1. Database preparation

Go to phpMyAdmin (http://localhost/phpmyadmin/) and create the new database (“TESTDB”) with the below SQL statement:

We also need to create the table (“CUSTOMERS”) and insert some values to that table.

Re-check your values with this SQL statement: SELECT * FROM CUSTOMERS;

This is the full SQL statement that you can use:

 

The results

 

2. Working with Java code

First, we need to download the “mysql-connector-java” jar at https://dev.mysql.com/downloads/connector/j/5.1.html

Select and download “Platform Independent (Architecture Independent), ZIP Archive”. When the website asks for login, you just need to click on “No thanks, just start my download” if you just want to download it without login.

Unzip this file and import “mysql-connector-java-5.1.43-bin.jar” to your project, the file version should be difference based on the time you download, so do not worry if you see the other number of jar version.

Ok, that’s good for now, we need to create a new Class and use this code, here’s my full code and explanation below:

 

Code Explanation:

This is the config information, it is including your database url with dbname, user and password for accessing the data. The configuration should be the same if you are using XAMPP like me.

Pre-define the SQL statement that you need to use, the java code will call these statements to work with database.

Create the database connection with the below code.

 

Note: I have set the @Test(enabled = false) to disable the test and run the single method, so if you want to execute you have to turn on by @Test(enabled = true), by this example I’m using TestNG.

# Executing the queryShowCustomers (“SELECT * FROM CUSTOMERS”)

 

# Executing queryUpdateCustomer (“UPDATE Customers SET ADDRESS = ‘VN’, SALARY= ‘10000’ WHERE ID = 6”)

 

# Executing queryInsertCustomer (“INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, ‘Sang’, 01, ‘AB’, 2000.00)”)

 

# Executing queryVerifySalaryWithID (“SELECT SALARY FROM CUSTOMERS WHERE ID = 6”)

 

 

With this example code, you can verify and read the values from database and comparing with the UI to make sure it’s the same and consistent (as we often do with manual testing). I’m willing to support if you have any difficulties or questions on this. Just let me know at the below comment box or via email (I have put the email on my blog profile).

An introduction to jComparison v1.0

Automated visual testing is really challenge. Comparing layouts day by day is also challenge and take time. It’s the reason why we want to develop a tool to support our automation script and saves time to compare the layout.

This tool is free and going to open the source as we want to share our work to the community and improving it with your supports.

Download here

The Idea:
We have created 2 folders to store all images of two times of the regression test by date: Regression_Results_20170719 & Regression_Results_20170720
We will compare all layout of these folders to detect the inconsistent or changed layout.
If the layouts are the same after 2 times executing, it should be Passed.
If there is any difference between 2 times of executing, it should be Failed and show us the differences.

How to use?
It’s quite simple.
1. All you need to do is including the script to take a screenshot as the below file name format – so after your automation script finished, it will creating image for you to compare.

Image name format: LayoutName_yyyy-mm-dd-hh-mm-ss.png
Example: Home_2017-07-19-11-22-09.png

2. At the jComparison app

  • Select the first image folder
  • Select the second image folder
  • Click on Compare button => Show the results as below
  • Click on the row of difference status to check the mismatch with highlight color
  • Click on Export TXT or CSV to export the results to file. The file of report will store at the same folder/place of the jar file
  • There are 2 modes:
    Strictly: Comparing exactly the same, this mode will using exact color code for comparision. This mean a little bit darker color will be list as different.
    Simple: This mode will not using exact color code for comparision. This mean a little bit darker color might not be list as different.

 

The main layout of jComparison

Exported results as CSV and TXT

Highlight the mismatch spot

Note:

  • To apply visual testing by using this tool, we have to make sure the screen/browser size is the same.
  • The test data should be the same, we just test the layout – not the data.
  • Test the tool carefully with the small number of images, before you apply it to your project, to make sure it works as expected.
  • Give us your feedback ([email protected]) based on your real issues, when we have your feedback, we have an idea for improving it.