Quick Contact

    Database Connection

    The connectivity to an application database is required by Selenium Webdriver for testing web applications and perform many operations such as information retrieval, submission and validation. For example the database connection is required to verify that the UI data is exact copy of Database table. The reasons to connect to a database are as:

    • To get test data: If we automate the database, we can directly fetch the test data from database and then work on them in test automation script
    • To verify result: In automation we can verify the front end result with backend entry in the database
    • To delete test data created: In automation it is good practice to delete the test data created, using database automation, we directly fire the delete query to delete the test data created
    • To update certain data: As per the need of test script, the test data can be updated using update query

    The selenium don’t provide facility to directly connect to database but instead use database driver APIs to connect to database. One such database driver is Java Database Connectivity (JDBC) which provides APIs to connect and interact with Database. Other such platform independent driver is ODBC. Since the connectivity to a database is through database drivers, the selenium webdriver can connect to various database such as MSSQL, MYSQL, ORACLE, SYBASE.

    Database connectivity

    The steps to connect to a database using database drivers are as below. The detail of each step will discussed in later sections of this chapter.

    1. Load and Registering the Driver: This step requires loading the database driver into the selenium script such as JDBC, ODBC.
    2. Establishing Connection: The traditional way to establish a connection with a database is to call the driver registered with the project. For example, JDBC provides DriverManager class to connect to database as seen below:

      DriverManager.getConnection(URL, “username”, “password” )

      Here,

      URL syntax is :

      jdbc:<subprotocol>:<subname>

      Where,

      <subprotocol> is the name of the driver or the name of a database connectivity mechanism.

      <subname> is the point of a subname is to give enough information to locate the data source .It may includes IP address , Port number and exact name of DataSource.

      Example of URL is

      jdbc:mysql://localhost:3306/TestMySQL
    3. Creating Statement Object: A Statement object is used to send SQL statements to a database over the created connection.
    4. Execute the Statement: The SQL statements are executed and results are stored in a ResultSet object. This Java object contains the results of executing an SQL query.
    5. 5. Closing the connection: The connection to database are closed using the methods provide the database driver. For example, JDBC provide close() method to close the database connection made by getConnection() method.

    Example: Below program provide an insight into a JAVA program which connects to a mysql database using a JDBC driver.

    import java.sql.Connection; 
    import java.sql.DriverManager; 
    import java.sql.ResultSet; 
    import java.sql.SQLException; 
    import java.sql.Statement;
    public class connectingToMySQLDBExample { 
    private static String userName; 
    private static String password; 
    private static String dbURL;
    private static Connection connection; 
    public static void main(String[] args) {
    try {
    userName = "username"; 
    password = "password";
    dbURL = "jdbc:mysql://artoftesting.com/testDB";
    try {
    
    }
    
    }
    try {
     
    Class.forName("com.mysql.jdbc.Driver");
    
    
    catch (ClassNotFoundException e) { 
    System.out.println("MySQL JDBC driver not found."); 
    e.printStackTrace();
    
    
    
    connection = DriverManager.getConnection(dbURL, userName, password);
    Statement st = connection.createStatement();
    String sqlStr = "select * from testTable"; 
    ResultSet rs = st.executeQuery(sqlStr); 
    while (rs.next()) {
    System.out.println(rs.getString("name")); 
    
    }
    Connection.close();
    } catch (SQLException e) {
    System.out.println("Connection to MySQL db failed");
    e.printStackTrace();
    }
    } catch (Exception e) { e.printStackTrace();
    }
    }
    }
    
    

    The output of the program depends upon the connectivity to the database and the value of the name column in the database table ‘testTable’. Hence the output is not explicitly written based on the assumption.

    Code Explanation:

    The first bold line of the code establishes the connection to the database. Here it is trying to connect to a mysql database using JDBC driver, The JDBC driver is loaded in the second bold statement. The third and fourth bold statement create statement object. The fifth statement executes the statement while the last bold statement closes a database connection.

    Note: The same example may be referred in rets of sections of the chapter.

    Database Testing in Selenium Using MySql Server

    In rest of chapter you will see use of JDBC driver in examples to connect to databases. The JDBC will be explained in later part of this chapter however a brief about JDBC is written in order to explain against the context of this section.

    The Java Database Connectivity (JDBC) API provides universal data access from the Java programming language. Using the JDBC API, you can access virtually any data source, from relational databases to spreadsheets and flat files. It lets the user connect and interact with the database and fetch the data based on the queries you use in the automation script. In other words JDBC Driver facilitates:

    1. Establishing a Database connection
    2. Sending SQL Queries to the Database
    3. Processing the results

    JDBC requires drivers for database it connect to. You need to download the MySQL driver for JDBC from the MySql site. This MySQL driver contains the required class files and Java source files to be used by JDBC driver. The download link is: (https://dev.mysql.com/downloads/connector/j/)

    Once you download the zip file ‘mysql-connector-java-5.1.39.zip’, unzip to see the jar inside which is actual driver file. The further steps to add this driver in Eclipse are as below:

    1. Right-click on the selenium package and navigate as ‘Build Path’ > ‘Configure Build Path’.
    2. Click on the ‘Libraries’ tab and click on the ‘Add External JARs’ button. Select the path to the unzipped ‘mysql-connector-java-5.1.39.jar’ file. Click on the Open button.
    3. The build path set up will show up as below

    Now, that MySQL driver is added in the project, next step is to make a sample program to connect to MySql Database using JDBC.

    package seleniumpackage; import java.sql.Connection; 
    import java.sql.Statement;
    import org.openqa.selenium.WebDriver;
    import org.openqa.selenium.firefox.FirefoxDriver; 
    import java.sql.ResultSet;
    import java.sql.DriverManager; 
    import java.sql.SQLException;
    public class SeleniumJDBCConnection {
    public static final String QUERY = "select * from WEB_TESTING;";
    public static void main(String[] args) throws 
    ClassNotFoundException, SQLException {
    String baseWebUrl = "";
    String expectedWebsiteTitle = "";
    //Load MySQL JDBC driver
     
    Class.forName("com.mysql.jdbc.Driver");
    
    //Create Connection to DB 
    Connection con =
    DriverManager.getConnection("jdbc:mysql://localhost:3306/webtesting"
    , "MySQLDatabase","softwaretestingclass");
    Statement stmt = con.createStatement(); 
    //Create Statement Object.
    ResultSet rs= stmt.executeQuery(QUERY); 
    // Execute the SQL Query. Store results in ResultSet.
    while (rs.next()){
    // While Loop iterates through all data present in a table. 
    baseWebUrl = rs.getString(1);
    expectedWebsiteTitle = rs.getString(2);
    }
    con.close();
    System.out.println("baseWebUrl: "+baseWebUrl); 
    System.out.println("expectedWebsiteTitle: "+expectedWebsiteTitle); 
    WebDriver driver = new FirefoxDriver();
    String actualWebsiteTitle = ""; 
    driver.get(baseWebUrl); 
    actualWebsiteTitle = driver.getTitle();
    if (actualWebsiteTitle.contentEquals(expectedWebsiteTitle)){ 
    System.out.println("Test has Passed!");
    } else {
    System.out.println("Test has Failed!");
    }
    driver.close();
    }
    }
    
    Output:
    baseWebURL: https://www.facebook.com/ expectedWebsiteTitle: Facebook – Log In or Sign Up Test has passed!

    Code Explanation:

    First the JDBC driver for MYSQL is loaded by importing import java.sql.DriverManager and making reference to class “com.mysql.jdbc.Driver”. For registering the Driver we have loaded the Driver class using forName() method. The forName() is the static factory method which is present in predefined class called “Class”. This method loads the class which is mentioned as parameter, here it is com.mysql.jdbc.Driver. Internally this Driver class will register the driver by using static method called registerDriver(). The database connection is made in below line of code by calling all static method called getConnection() present in DriverManager Class.

    Connection con = DriverManager.getConnection(“jdbc:mysql://localhost:3306/webtesting”, “MySQLDatabase”,”softwaretestingclass”);

    This DriveManager class requires URL, username, and password. Once the database connection is established, SQL queries can be executed via JDBC connection. Here, we have created a SQL to select a record (here URL and title) from the database. With the help of Statement interface and ‘createStatement’ method of Connection interface which is then executed with the help of
    ‘executeQuery’ method. This method returns a result set that contains URL and title.

    Configuring SQL

    The various SQL statements which can be used to interact with the Database are listed below. Please note that SQL statements are universal statements which can be used to interact with any kind of database. The syntax of SQL statements remains same everywhere. Below list provides a basic overview of making SQL statements.

    • Creating Statement Object:

      For creating statement object we need to call a method called createStatement() which is present in Connection Interface. This method returns Statement object and it is no argument method.

      Example:

      con.createStatement();
    • Executing Queries:

      For executing queries there are different methods present in Statement Interface for selecting and updating records.

      • Selecting records: for executing select queries, we call a method called executeQuery(). This method takes string as parameter and returns ResultSet object.
        Resultset rs= st.executeQuery("Select * from Employee");

        *We will read about ResultSet object in later part of this chapter.

      • Updating records: To update records in a table we use a method called executeUpdate().This method takes string as parameter and returns integer value as how many records have been updated.

        Example:

        int result = st.executeUpdate("update Employee set EmpName='abc' where EmpId=2");
    • Closing the Connection:Once execution of all statements were completed we need to close all the connections made to the Database by using method called close() present in Connection interface.

      Example:

      con.close();
    JDBC Driver

    Java Database Connectivity (JDBC) is a Java API to interact with the data sources. Here word interaction implies to the connection and retrieving and updating data in data sources. The data sources can be anything kind of tabular data such as relational database, spreadsheets and flat files. JDBC provides APIs to connect with the data sources. The APIs uses the drivers to connect to different type of data sources such as EXCEL, MySQL, MSSQL, ORACLE. You are required to download drivers for each type of data sources and include in your project to have the APIs interact with selected data sources. The pictorial representation of JDBC driver is :

    JDBC APIs

    The JDBC APIs provides universal data access from the Java programming language. Using the JDBC API, you can access various data source, from relational databases to spreadsheets and flat files. The JDBC API consists of a set of classes and interfaces written in the Java programming language. The JDBC API is comprised of two packages, java.sql and javax.sql.

    JDBC API using database drivers provides below functionality to a developer.

    1. APIs to connect to the data source.
    2. APIs to execute queries in form of SELECT, INSERT and UPDATE statements.
    3. APIs to retrieve the result received from the data source.
    JDBC drivers

    JDBC Driver is a software component that enables JAVA application to interact with the data source. JDBC drivers are client-side adapters that convert requests from Java programs to a protocol that the data source can understand. The data sources can be anything kind of tabular data such as relational database, spreadsheets and flat files. There are four types of JDBC drivers:

      • JDBC-ODBC bridge driver: The JDBC-ODBC bridge driver uses Open Database Connectivity (ODBC) driver to connect to the database. The JDBC-ODBC bridge driver converts JDBC method calls into the ODBC function calls. Note that ODBC is also an API for accessing DBMS. Before JDBC, ODBC API was the database API to connect and execute query with the database. But, ODBC API uses ODBC driver which is written in C language and thus is platform dependent and unsecured. Due to this reason this type of driver is not used and is unsupported Oracle. The advantages and disadvantages of JDBC-ODBC bridge driver.

    Advantages:

        • It is easy to use.
        • It can easily connect to any database.

    Disadvantages:

        • It offers low performance as JDBC method call is converted into the ODBC function calls.
        • The ODBC driver needs to be installed on the client machine.

    The pictorial representation is:

      • Native-API driver: The Native API driver uses the client-side libraries of the database. This driver need local installation of native database binary code and configured to work. The driver then converts JDBC method calls into native calls of the database API. For example, in order to use MYSQL the driver would require MYSQL binaries installation locally to have APIs available to use by the driver. The advantages and disadvantages Native-API driver are:

    Advantages:

        • It give higher performance than JDBC-ODBC bridge driver.

    Disadvantages:

        • The Native database needs to be installed on the each client machine.
        • The Vendor client library needs to be installed on client machine.

    The pictorial representation is:

      • Network Protocol driver: The Network Protocol driver uses middleware (application server) that converts JDBC calls directly or indirectly into the vendor-specific database protocol. Thus the calls are passed through the network to the middle-tier server. The middle-tier then translates the request to the database. This driver is most flexible JDBC solution as they do not require any native binary code on the client. The advantages and disadvantages Network Protocol driver are:

    Advantages:

        • No client side library is required because of application server that can perform many tasks like auditing, load balancing, logging etc.

    Disadvantages:

        • Network support is required on client machine.
        • Requires database-specific coding to be done in the middle tier.
        • Maintenance of Network Protocol driver becomes costly because it requires database-specific coding to be done in the middle tier.

    The pictorial representation is:

      • Thin driver: The thin driver converts JDBC calls directly into the vendor-specific database protocol. It uses JAVA networking libraries to communicate directly with the database server and thus require no client installation or configuration. Since the protocols are vendor- specific and proprietary, DBMS vendors are generally the only companies providing this driver. The advantages and disadvantages Thin driver are:

    Advantages:

        • Better performance than all other drivers.
        • No software is required at client side or server side.

    Disadvantages:

        • Drivers depend on the Database.

    The pictorial representation is:

    JDBC API Classes and Interfaces

    The Java API interacts with the database after procuring a data source connection and executing SQL statements in case of database. This API connects the Java programming languages with various databases with the help of their database drivers. Following are the classes and interfaces that are provided by the JDBC API.

    • Driver Manager: This class returns a database connection object after accepting DB connection URL, username and password.
    • Driver: This is database specific driver that helps in creating JDBC connection with the database.
    • Connection: It is an interface that is able to provide information describing its table, its supported SQL grammar, its stored procedures, the capabilities of this connection.
    • Statement: It is an interface to the pre-compiled object that is used to efficiently execute SQL statements into the database.
    • ResultSet: It is an interface to the object that maintains a cursor pointing to its current row of data.
    • SQLException: It is an exception class that defines the various SQL exceptions. It is mandatory to catch SQL exception through this class when you execute any SQL statement via JDBC connection.
    Understanding Connection Interface

    A connection interface provides a standard abstraction to access the session established with a database server. This interface represents a session between a JAVA application and a database. All the SQL statements which are executed, the results are returned within the context of a Connection object. Connection interface is mainly used to create java.sql.Statement, java.sql.PreparedStatement and java.sql.CallableStatement objects. You can also use it to retrieve the metadata of a database like name of the database product, name of the JDBC driver, major and minor version of the database. A connection object can be obtained by using the getConnection() method of the DriverManager class. The syntax of getConnection() method is

    Connection con=DriverManager.getConnection(url,username,password);

    The commonly used methods of Connection Interface are:

    • public Statement createStatement(): It creates a statement object that can be used to execute SQL queries.
    • public Statement createStatement(int resultSetType,int resultSetConcurrency): It creates a Statement object that will generate ResultSet objects with the given type and concurrency.
    • public void setAutoCommit(boolean status): It is used to set the commit status.By default it is true.
    • public void commit(): It saves the changes made since the previous commit/rollback permanent.
    • public void rollback(): It drops all changes made since the previous commit/rollback.
    • public void close(): It closes the connection and Releases a JDBC resources immediately.

    It is required explicitly to close all the connections associated with connection object with database made during the program using the close() method. The syntax of close() method is:

    conn.close();
    Statement Interface

    The Statement interface provides a standard abstraction to execute SQL statements and return the results using the ResultSet objects. It provides methods to execute queries with the database.

    Type of Statements

    The JDBC API provides three different interfaces to execute the different types of SQL queries

    • Statement: This type is used to execute normal SQL queries.
    • PreparedStatement: This type is used to execute dynamic or parameterized SQL queries.
    • CallableStatement: This type is used to execute the stored procedures.
    Statement Interface

    Statement interface is used to execute normal SQL queries. You can’t pass the parameters to SQL query at run time using this interface. A Statement object contains a single ResultSet object at a time. The execute method of a Statement implicitly close its current ResultSet if it is already open. Statement object can be create by using the createStatement() method of the Connection interface. Lets take one small example to understand createStatement().

    Statement st = con.createStatement();

    The above statement defines ‘st’ object of type Statement. The ‘con’ is a connection object which establishes connection to the required data source. At this time only object is created. This object need to be executed along with SQL statement. You can see below example as how the Statement object is executed and result assigned to ResultSet.

    ResultSet rs = st.executeQuery("select * from employeeList"); while (rs.next()) {
    System.out.println(rs.getString("empname"));
    }
    

    In above example, it can be seen that ‘st’ object has a method as executeQuery() which has SQL statement as an argument. This result set after the execution is assigned to the ResultSet object ‘rs’. The The ResultSet provided methods to iterate through the result set and perform required actions.

    Type of execute methods

    The Statement object has three types of execute methods as below:

    • boolean execute (String SQL): Returns a boolean value of true if a ResultSet object can be retrieved; otherwise, it returns false. This method to execute SQL DDL statements or when it need to use truly dynamic SQL.
    • int executeUpdate (String SQL): Returns the number of rows affected by the execution of the SQL statement. This method is used to execute SQL statements to get a number of rows affected.
    • ResultSet executeQuery (String SQL): Returns a ResultSet object. This method is used when it expect to get a result set, with a SELECT statement.

    Just as a Connection object can be closed, the Statement object should also be closed by using the close() method. The syntax of close() method is same as that explained in connection interface section of this chapter.

    Prepared Statement Interface.

    PreparedStatement is used to execute dynamic or parameterized SQL queries. PreparedStatement extends Statement interface. PreparedStatements are pre-compiled and hence their execution is much faster than that of Statements. In PreparedStatement, the SQL is passed in the prepareStatement() method while creating the PreparedStatement and leave the execute method empty. However, the SQL statement can be overridden which is passed in prepareStatement() by

    passing another SQL in the executeQuery() method. Let’s take one small example to understand
    PreparedStatement().

    PreparedStatement st=con.prepareStatement("insert into employeeList values ('Heartin',2)");
    st.executeUpdate();
    

    The above statement defines ‘st’ object of type PreparedStatement. The ‘con’ is a connection object which establishes connection to the required data source. At this time not only object is created but the SQL statement is also written that need to be exectuted. In next line the ‘st’ object is executed using executeQuery() method.

    PreparedStatement also has a set of methods such as setString(), setInt(), which can be used to parameterize a PreparedStatement. Lets see below example:

    PreparedStatement ps1=con.prepareStatement("insert into employeeList values (?,?)");
    ps1.setString(1, "Heartin4"); 
    ps1.setInt(2, 7); 
    ps1.executeUpdate();
    

    In this example, the SQL Insert query requires two values which are passed to the SQL using setString() and setInt() methods. Also note that set methods should be called before calling the executeUpdate() method.

    Just as you close a Statement object, you should also close the PreparedStatement object with the
    close() method.

    ResultSet Interface

    By now you know that the SQL SELECT statements read data from a database query and return the data in an object. This object is called as ‘ResultSet’. The java.sql.ResultSet interface represents the ResultSet of a database query. This ResultSet maintains data in row and column format. In order to access row and columns the object maintains a cursor that points to the current row in the ResultSet. This cursor is movable forward and backwards based on the properties of the ResultSet.

    Type of ResultSet

    There are three types of Result set:

    1. ResultSet.TYPE_FORWARD_ONLY: The cursor can only move forward in the result set. This is default ResultSet type.
    2. ResultSet.TYPE_SCROLL_INSENSITIVE: The cursor can scroll forward and backward, and the result set is not sensitive to changes made by others to the database that occur after the result set was created.
    3. ResultSet.TYPE_SCROLL_SENSITIVE: The cursor can scroll forward and backward, and the result set is sensitive to changes made by others to the database that occur after the result set was created.

    Example:

    Statement stmt = 
    con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
    ResultSet.CONCUR_UPDATABLE);
    Methods of ResultSet

    The methods of the ResultSet interface can be defined into three categories:

    • Navigational methods: These methods are used to move the cursor around.
    • Get methods: These methods are used to view the data in the columns of the current row being pointed by the cursor.
    • Update methods: These methods are used to update the data in the columns of the current row. The updates can then be updated in the underlying database as well

    Based on above categories, JDBC provides the following connection methods to create statements with desired ResultSet:

    • createStatement(int RSType, int RSConcurrency);
    • prepareStatement(String SQL, int RSType, int RSConcurrency);
    • prepareCall(String sql, int RSType, int RSConcurrency);

    Here, the first argument indicates the type of a ResultSet object and the second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable.

    Firing Queries Using Java on Eclipse

    The steps of Firing Queries using Java on Eclipse are as below:

    • Open Eclipse IDE and Select Database Perspective (Windows >> Open Perspective >> Other).
    • Create Connection Profile: Here profile can be create for connecting to DB.

      Method 1: Right click on “Database Connections” and click New.

      Method 2: Click on “New Connection Profile” button in “Data Source Explorer” view.

    • Select DB to connect from popup window. And provide connection profile name (JBT in this case).
    • Click Next. In next screen provide DB connection details.
    • Once provide it can check if connection to DB is successful or not by clicking “Test
      Connection” button. If everything is fine it should show Ping Succeed popup window. If not then check if database is responding and is available to connect.
    • After providing the connection related details click Finish. If everything is fine then a new Connection profile named JBT will be created in Data Source Explorer view
    • To execute a query “SQL Scrapbook” is used. To open SQL scrapbook click scrapbook button on top.
    • It will open new SQL scrap book, from there it can execute sql query. To view the result of the query fired it will require “SQL Results” view which can be opened from Windows >> Show View >> Other.
    • Execution plan of the query can also be checked via Eclipse IDE. To view Execution plan
      “Execution Plan” view needs to be opened. Eclipse IDE can show execution plan in two ways.
    Summary
    • The selenium don’t provide facility to directly connect to database but instead use database driver APIs to connect to database
    • The Java Database Connectivity (JDBC) API provides universal data access from the Java programming language
    • JDBC Driver is a software component that enables JAVA application to interact with the data source
    • A connection interface provides a standard abstraction to access the session established with a database server
    • The Statement interface provides a standard abstraction to execute SQL statements and return the results using the ResultSet objects

    Copyright 1999- Ducat Creative, All rights reserved.