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 [email protected](enabled = false) to disable the test and run the single method, so if you want to execute you have to turn on [email protected](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).


Leave a Reply

Your email address will not be published. Required fields are marked *

Powered by Sang Bui