Why we need DB connection
First of all, we need to understand the actual meaning of Database. A Database is a collection where we store the data in the form of schemas and tables. This data can then be accessed by Queries, Stored Procedures and Views.
Then the second question arises “Why do we need a Database“. Let’s take the example of a Library. We know that there are many books in the library where we have some collection of data and each one have its own properties. And you have to save the data in the form of file e.g. text file, excel file, document file etc. Many of the small businesses continue to use Text files or Excel spreadsheets since long time. This setup might work good for few small businesses, because just having data to store does not require a Database. But for big companies this might not prove to be the best option. Here, we need a term called Database.
When do we need to validate or get some value from DB in Testing?
Take a look at the two basic terms of software testing: Blackbox Testing and Whitebox Testing. Both are a part of functional testing. Now think these type of testing with the reference of Database Testin
- Black Box Testing with Database Testing => This type of testing is applied on the application to verify the incoming and outgoing data by executing queries on the Database.
- White Box Testing with Database Testing => This type of testing is applied to validate the internal structure of the Database. Also we check the basic terminology of the Database called ACID (Atomicity, Consistency, Isolation and Durability) with the flow of the application.
Types of DBs we have
There are numerous type of Databases available in the market but mostly we use the ones mentioned below:
- MySQL
- SQL Server
- Oracle
- SQLite
- PostgreSQL
- MariaDB
- MS Access
For further reference: – https://en.wikipedia.org/wiki/Programming_languages_used_in_most_popular_websites
https://en.wikipedia.org/wiki/List_of_relational_Database_management_systems
Basic Idea of Operations about the processing of Database
Here I am mentioning some basic Idea of Operations about the processing of Database and for this purpose I am using MySQL with Java.
-
Create Connection between Java and DB
- Download the jar file of MySQL Connector from here, then unzip the zip file
- Add the reference of this jar to your project (Right click on the project => Build Path => Configure Build Path => Add Library => Select the Jar => OK)
- Now we need to register JDBC driver and create connection between JDBC and MySQL as mentioned below.
- forName(“com.mysql.jdbc.Driver”); [The basic idea behind using Class.forName(“”) is to open a class or in other words invoke it via its name from string format.]
- Connection conn = DriverManager.getConnection(“jdbc:mysql://<Host>:3306/<Database>”, “<userId>”, “<Password>”); [This is the Connection String]
- As for the Database we know that there are 4 types of queries which are majorly used and these queries are known as SQL (Structured Query Language) queries as SELECT, INSERT, UPDATE and DELETE (Brief idea about these is here)
Example of Queries :-
- Select * from <TableName>
- Insert into <TableName> <Column1, Column 2 …..> values<value1, values 2 …..>
- Update <TableName> set <columnName> = <Data> where <column name> = <Data>
- Delete from <TableName> where <column name> = <Data>
-
Execute SQL Query
After creating a connection between JDBC and MySQL we have to create 2 more objects
- Statement {It is used to assure that the program is ready to execute the query e.g Statement stmt = conn.createStatement() } and
- ResultSet {This is the container in which the program stores the Result of query e.g. ResultSet resultSet = stmt.executeQuery(“select * from sampletable”) }
-
Closing DB Connection: –
Closing the connection after getting the results from Database is a must otherwise issues like memory leakage is seen. Also, even though the user logs out, the connection remains active until the application or web server is shut down.
Code: –
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
if (resultSet != null) { try { resultSet.close(); } catch (Exception e) {} } if (stmt != null) { try { stmt.close(); } catch (Exception e) {} } if (conn != null) { try { conn.close(); } catch (Exception e) {} } |
Complete Code : Create Connection + Execute Statement + Close Connection
|
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 |
package com.Database; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.testng.annotations.AfterTest; import org.testng.annotations.BeforeTest; import org.testng.annotations.Test; public class ConnectToMySQL { public static void main(String[] args) throws Exception { // Object of Connection from the Database Connection conn = null; // Object of Statement. It is used to create a Statement to execute the query Statement stmt = null; //Object of ResultSet => 'It maintains a cursor that points to the current row in the result set' ResultSet resultSet = null; Class.forName("com.mysql.jdbc.Driver"); // Open a connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "root", "root"); // Execute a query stmt = conn.createStatement(); resultSet = stmt.executeQuery("select * from sampletable"); while (resultSet .next()) { System.out.println(resultSet .getString(1) + " " + resultSet.getString(2) + " " + resultSet.getString(3) + " " + resultSet.getString(4) + " " + resultSet.getString(5)); } if (resultSet != null) { try { resultSet.close(); } catch (Exception e) { } } if (stmt != null) { try { stmt.close(); } catch (Exception e) { } } if (conn != null) { try { conn.close(); } catch (Exception e) { } } } } |
Example to verify Some Data with Database using Selenium and MySQL
Sometimes we get some Test Case in Software Testing in which you have to verify the UI Data to ensure that this data is exact copy of Database table.
- => Code => “TestNG” + “Selenium” + ”MySQL” + “Chrome Browser”
- => Required Jar (MySQL Jar , Selenium Jar)
- => Browser (Chrome Server)
- => TestNG (AddOn for Eclipse) or (Open Eclipse => Help => Eclipse Marketplace => Type TestNG in the ‘Find Box’ => Press Enter => Install)
- => MySQL Server (Server)
- => Create the Project and Add the reference of above Jars then write the code
Code {How to validate UI DATA with The Database}
|
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 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 |
package com.Database; import static org.testng.Assert.assertEquals; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.HashMap; import java.util.List; import java.util.Map; import org.openqa.selenium.By; import org.openqa.selenium.WebDriver; import org.openqa.selenium.WebElement; import org.openqa.selenium.chrome.ChromeDriver; import org.openqa.selenium.chrome.ChromeOptions; import org.testng.annotations.AfterTest; import org.testng.annotations.BeforeTest; import org.testng.annotations.Test; public class ConnectToMySQL { // Object of Connection from the Database Connection conn = null; // Object of Statement. It is used to create a Statement to execute the query Statement stmt = null; //Object of ResultSet => 'It maintains a cursor that points to the current row in the result set' ResultSet resultSet = null; WebDriver driver; @BeforeTest public void SetUpConnection() throws SQLException, ClassNotFoundException { // Register JDBC driver (JDBC driver name and Database URL) Class.forName("com.mysql.jdbc.Driver"); // Open a connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "root", "root"); System.setProperty("webdriver.chrome.driver", "<Path of Driver>\\chromedriver.exe"); ChromeOptions options = new ChromeOptions(); // Code to disable the popup of saved password Map<String, Object> prefs = new HashMap<String, Object>(); prefs.put("credentials_enable_service", false); prefs.put("password_manager_enabled", false); options.setExperimentalOption("prefs", prefs); driver = new ChromeDriver(options); driver.get("<URL>"); } @Test public void OperationalMethod() { try { // Execute a query stmt = conn.createStatement(); resultSet = stmt.executeQuery("select * from sampletable"); // Get the all row of UI Table List<WebElement> lstTr = driver.findElement(By.id("grdData")).findElements(By.tagName("tr")); // Index for Row int rowCount = 0; // Count of Matched Column int matchColumnCount = 0; // Count of Matched Row int matchRowCount = 0; System.out.println("Row Count => " + lstTr.size()); // Extract the data from Table while (resultSet.next()) { //(rowCount + 1) because first row is a header row , Get all the columns from a particular row List<WebElement> lstTd = lstTr.get(rowCount + 1).findElements(By.tagName("td")); System.out.println("Cloumn Count => " + lstTd.size()); for (int j = 0; j < lstTd.size(); j++) { String uiCell = lstTd.get(j).getText(); System.out.println("UI Cell Data => " + uiCell); /* * (j + 1) in the resultSet => because index is start from 1 * and here loop is starting from 0 */ String dbCell = resultSet.getString(j + 1); System.out.println("DB Cell Data => " + dbCell); // Comparison between both string if (uiCell.trim().equalsIgnoreCase(dbCell.trim())) { matchColumnCount++; } } if (matchColumnCount == lstTd.size()) { matchRowCount++; System.out.println("========ROW MATCHED=========="); } // Set 'matchColumnCount' to 0 for next row matchColumnCount = 0; rowCount++; } assertEquals(matchRowCount, rowCount, "UI Table is the exact copy of Database Table"); } catch (Exception e) { System.out.println(e); } } @AfterTest public void CloseTheConnection() throws SQLException { // Code to close each and all Object related to Database connection if (resultSet != null) { try { resultSet.close(); } catch (Exception e) { } } if (stmt != null) { try { stmt.close(); } catch (Exception e) { } } if (conn != null) { try { conn.close(); } catch (Exception e) { } } driver.quit(); } } |
How to store the data in Database (Insertion)
Here I am writing the code to store the Data into the Database
Steps: –
- Write the code to connect MySQL
- Write the code of Insertion
// the mysql insert statement
String query = ” Insert into sampletable (FirstName, LastName ) values(?,?)”;
// create the mysql insert preparedstatement
PreparedStatement preparedStmt = conn.prepareStatement(query);
preparedStmt.setString(1, “<FirstName>”);
preparedStmt.setString(2, “<LastName>”);
// execute the preparedstatement
preparedStmt.execute();
Explanation :
- Insert Query to insert the value in Database
- PreparedStatement’. It is used to execute parameterized or dynamic SQL queries.
- setString(<Position of ‘?’>,”<data>”). It is used to insert data to a particular column.
Note :- Now the question arises is what is “(?,?)” ? The symbol “?” shows the number of arguments. Suppose you have 2 parameters in the query then you have to write two “?” like “(?,?)”
- execute() to execute the query
Complete Code
|
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 |
private static void InsertToDB() { try { // create a mysql Database connection String myUrl = "jdbc:mysql://localhost:3306/testDB"; Class.forName("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection(myUrl, "root", "root"); // the mysql insert statement String query = " Insert into sampletable (FirstName, LastName , PhoneNo, Company) values(?,?,?,?)"; // create the mysql insert preparedstatement PreparedStatement preparedStmt = conn.prepareStatement(query); preparedStmt.setString(1, "<FirstName>"); preparedStmt.setString(2, "<LastName>"); preparedStmt.setString(3, "<Phone No.>"); preparedStmt.setString(4, "<Company Name>"); // execute the preparedstatement preparedStmt.execute(); conn.close(); } catch (Exception e) { System.out.println("Got an exception!"); System.out.println(e.getMessage()); } } |
