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:
1 2 |
CREATE DATABASE testDB; USE testDB; |
We also need to create the table (“CUSTOMERS”) and insert some values to that table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Komal', 22, 'MP', 4500.00 ); |
Re-check your values with this SQL statement: SELECT * FROM CUSTOMERS;
This is the full SQL statement that you can use:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
CREATE DATABASE testDB; USE testDB; CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Komal', 22, 'MP', 4500.00 ); SELECT * FROM CUSTOMERS; |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 |
package tcLogin; import java.sql.Connection; import java.sql.Statement; import org.testng.annotations.*; import java.sql.ResultSet; import java.sql.DriverManager; public class database { // Config information static Connection con = null; private static Statement stmt; public static String DB_URL = "jdbc:mysql://localhost:3306/testdb"; public static String DB_USER = "root"; public static String DB_PASSWORD = ""; // SQL queries String queryShowCustomers = "SELECT * FROM CUSTOMERS"; String queryInsertCustomer = "INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, 'Sang', 01, 'AB', 2000.00)"; String queryUpdateCustomer = "UPDATE Customers SET ADDRESS = 'VN', SALARY= '10000' WHERE ID = 6"; String queryVerifySalaryWithID = "SELECT SALARY FROM CUSTOMERS WHERE ID = 6"; @BeforeTest public void setUp() throws Exception { try { // Make the database connection String dbClass = "com.mysql.jdbc.Driver"; Class.forName(dbClass).newInstance(); // Get connection to DB Connection con = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD); // Statement object to send the SQL statement to the Database stmt = con.createStatement(); } catch (Exception e) { e.printStackTrace(); } } @Test(enabled = false) public void testqueryShowCustomers() { try { // Get the contents of table from DB ResultSet res = stmt.executeQuery(queryShowCustomers); // Print the all result while (res.next()) { String cusID = res.getString(1); String cusName = res.getString(2); String cusAge = res.getString(3); String cusAddress = res.getString(4); String cusSalary = res.getString(5); System.out.println(cusID + "\t" + cusName + "\t" + cusAge + "\t" + cusAddress + "\t" + cusSalary); } } catch (Exception e) { e.printStackTrace(); } } @Test(enabled = false) public void queryUpdateCustomer() { try { // Update data value stmt.executeUpdate(queryUpdateCustomer); } catch (Exception e) { e.printStackTrace(); } } @Test(enabled = false) public void queryInsertCustomers() { try { // Insert data value stmt.executeUpdate(queryInsertCustomer); } catch (Exception e) { e.printStackTrace(); } } @Test(enabled = false) public void testqueryShowCustomersWithID() { try { // Get the contents of table from DB ResultSet res = stmt.executeQuery(queryVerifySalaryWithID); // Print the all result while (res.next()) { String cusSalary = res.getString(1); if (cusSalary.equals("4500.00")) { System.out.println("Pass"); } System.out.println("Fail, the actual value is " + cusSalary); } } catch (Exception e) { e.printStackTrace(); } } @AfterTest public void tearDown() throws Exception { // Close DB connection if (con != null) { con.close(); } } } |
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.
1 2 3 4 5 6 |
// Config information static Connection con = null; private static Statement stmt; public static String DB_URL = "jdbc:mysql://localhost:3306/testdb"; public static String DB_USER = "root"; public static String DB_PASSWORD = ""; |
Pre-define the SQL statement that you need to use, the java code will call these statements to work with database.
1 2 3 4 5 |
// SQL queries String queryShowCustomers = "SELECT * FROM CUSTOMERS"; String queryInsertCustomer = "INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, 'Sang', 01, 'AB', 2000.00)"; String queryUpdateCustomer = "UPDATE Customers SET ADDRESS = 'VN', SALARY= '10000' WHERE ID = 6"; String queryVerifySalaryWithID = "SELECT SALARY FROM CUSTOMERS WHERE ID = 6"; |
Create the database connection with the below code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
@BeforeTest public void setUp() throws Exception { try { // Make the database connection String dbClass = "com.mysql.jdbc.Driver"; Class.forName(dbClass).newInstance(); // Get connection to DB Connection con = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD); // Statement object to send the SQL statement to the Database stmt = con.createStatement(); } catch (Exception e) { e.printStackTrace(); } } |
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”)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
@Test(enabled = true) public void testqueryShowCustomers() { try { // Get the contents of table from DB ResultSet res = stmt.executeQuery(queryShowCustomers); // Print the all result while (res.next()) { String cusID = res.getString(1); String cusName = res.getString(2); String cusAge = res.getString(3); String cusAddress = res.getString(4); String cusSalary = res.getString(5); System.out.println(cusID + "\t" + cusName + "\t" + cusAge + "\t" + cusAddress + "\t" + cusSalary); } } catch (Exception e) { e.printStackTrace(); } } |
# Executing queryUpdateCustomer (“UPDATE Customers SET ADDRESS = ‘VN’, SALARY= ‘10000’ WHERE ID = 6”)
1 2 3 4 5 6 7 8 9 10 11 |
@Test(enabled = true) public void queryUpdateCustomer() { try { // Update data value stmt.executeUpdate(queryUpdateCustomer); } catch (Exception e) { e.printStackTrace(); } } |
# Executing queryInsertCustomer (“INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, ‘Sang’, 01, ‘AB’, 2000.00)”)
1 2 3 4 5 6 7 8 9 10 |
@Test(enabled = true) public void queryInsertCustomers() { try { // Insert data value stmt.executeUpdate(queryInsertCustomer); } catch (Exception e) { e.printStackTrace(); } } |
# Executing queryVerifySalaryWithID (“SELECT SALARY FROM CUSTOMERS WHERE ID = 6”)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
@Test(enabled = true) public void testqueryShowCustomersWithID() { try { // Get the contents of table from DB ResultSet res = stmt.executeQuery(queryVerifySalaryWithID); // Print the all result while (res.next()) { String cusSalary = res.getString(1); if (cusSalary.equals("4500.00")) { System.out.println("Pass"); } System.out.println("Fail, the actual value is " + cusSalary); } } catch (Exception e) { e.printStackTrace(); } } |
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).
Recent Comments