Tuesday, October 4, 2022

[FIXED] How to avoid hardcoded string in sendKeys and pass a value from SQL SENTENCE (JAVA & SELENIUM)

October 04, 2022 java, selenium, selenium-chromedriver, selenium-webdriver

Issue

I want to execute a SQL SENTENCE (SQL SERVER database), copy the value and place it inside the sendKeys function, i was thinking about doing something like this:

Step 1) Execute the query

Step 2) Copy the value from the SQL SENTENCE and place it inside the "sendKeys" functions:

My CODE looks like this:

package first;

import org.openqa.selenium.By;
import org.openqa.selenium.Keys;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.support.ui.FluentWait;
import org.openqa.selenium.support.ui.Wait;

public class myCodes {
    public static void main(String[] args) throws InterruptedException{
    System.setProperty("webdriver.chrome.driver","C:\\Users\\Steven\\Desktop\\Folder\\chromedriver.exe");

    WebDriver driver = new ChromeDriver();
    driver.get("https://testingserver/backend");
    driver.findElement(By.id("username")).sendKeys("admin");
    driver.findElement(By.id("login")).sendKeys("randomPassw0rd@");
    driver.findElement(By.xpath("//*[@id=\"login-form\"]/fieldset/div[3]/div[2]/button")).click();
    Thread.sleep(4000);
    driver.findElement(By.xpath("//*[@id=\"menu-bbb-backend-stores\"]/a")).click();
    Thread.sleep(4000);
    driver.findElement(By.xpath("//*[@id=\"menu-bbb-backend-stores\"]/div/ul/li[2]/ul/li[2]/div/ul/li[3]/a")).click();
    driver.findElement(By.xpath("//*[@id=\"attributeGrid_filter_frontend_label\"]")).sendKeys("group");
    Thread.sleep(3000);
    driver.findElement(By.xpath("//*[@id=\"attributeGrid_filter_frontend_label\"]")).sendKeys(Keys.ENTER);
    Thread.sleep(3000);
    driver.findElement(By.xpath("//*[@id=\"attributeGrid_table\"]/tbody/tr/td[1]")).click();
    //driver.findElement(By.cssSelector(".col-attr-code col-attribute_code")).click(); 
    //Thread.sleep(6000);
    driver.findElement(By.xpath("//*[@id=\"manage-options-panel\"]/table/tbody/tr/td[5]/input")).sendKeys("122");
    driver.findElement(By.xpath("//*[@id=\"save\"]/a")).click();
    Thread.sleep(3000);
    
    
    }

}

I need to avoid the harcoded value and pass the one get from the SQL SENTENCE:

driver.findElement(By.xpath("//*[@id=\"attributeGrid_filter_frontend_label\"]")).sendKeys("group");

i don't want to pass "group" as a harcoded value but fetch it from this SQL sentence:

SELECT value
from test
where claim = 45;

And also:

driver.findElement(By.xpath("//*[@id=\"manage-options-panel\"]/table/tbody/tr/td[5]/input")).sendKeys("122");

i don't want to pass "122" as a harcoded value but fetch it from this SQL sentence:

SELECT value
from test32
where claim = 34;

What would it be the best way to achieve that?

EDIT #1:

package first;

import org.openqa.selenium.By;
import org.openqa.selenium.Keys;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.support.ui.FluentWait;
import org.openqa.selenium.support.ui.Wait;
import org.apache.commons.dbutils.DbUtils;
import java.sql.DriverManager;

public class myCodes {
    static Connection conn = null;
    static QueryRunner run = new QueryRunner();
    static boolean keepConnection = false;

    public static void main(String[] args) throws InterruptedException{
    System.setProperty("webdriver.chrome.driver","C:\\Users\\Steven\\Desktop\\Folder\\chromedriver.exe");
    
      public static void createConn(String url, String driver, String usr, String pwd) throws SQLException, IOException {

        if (conn == null || conn.isClosed()) {
            DbUtils.loadDriver(driver);
            conn = DriverManager.getConnection(url, usr, pwd);
            conn.setAutoCommit(false);
        }
    }
    
    

/**
 * returns the row as map
 * @param sql input sql string
 * @param params any additional parameters
 */
public static Map<String, Object> getQueryResultInMap(String url, String driver, String usr, String pwd,String sql, Object... params) throws SQLException, IOException {

    try {
        createConn(url,driver,usr,pwd);
        if (params == null) {
            return run.query(conn, sql, new MapHandler());
        } else {
            return run.query(conn, sql, new MapHandler(), params);
        }
    } catch (SQLException se) {
        se.printStackTrace();
        return null;
    } finally {
        closeConn();
    }
}

    public static void closeConn() throws SQLException {
        if (!keepConnection) {
            DbUtils.closeQuietly(conn);
        }
    }
    
    



String sqlQuery= "SELECT value from test32 where claim = 34";
Map<String,Object>resultSet= getQueryResultInMap(String url, String driver, String usr, String pwd,String sqlQuery,null);
    

    WebDriver driver = new ChromeDriver();
    driver.get("https://testingserver/backend");
    driver.findElement(By.id("username")).sendKeys("admin");
    driver.findElement(By.id("login")).sendKeys("randomPassw0rd@");
    driver.findElement(By.xpath("//*[@id=\"login-form\"]/fieldset/div[3]/div[2]/button")).click();
    Thread.sleep(4000);
    driver.findElement(By.xpath("//*[@id=\"menu-bbb-backend-stores\"]/a")).click();
    Thread.sleep(4000);
    driver.findElement(By.xpath("//*[@id=\"menu-bbb-backend-stores\"]/div/ul/li[2]/ul/li[2]/div/ul/li[3]/a")).click();
    driver.findElement(By.xpath("//*[@id=\"attributeGrid_filter_frontend_label\"]")).sendKeys(resultSet.get("approverRelation"));
    Thread.sleep(3000);
    driver.findElement(By.xpath("//*[@id=\"attributeGrid_filter_frontend_label\"]")).sendKeys(Keys.ENTER);
    Thread.sleep(3000);
    driver.findElement(By.xpath("//*[@id=\"attributeGrid_table\"]/tbody/tr/td[1]")).click();
    //driver.findElement(By.cssSelector(".col-attr-code col-attribute_code")).click(); 
    Thread.sleep(3000);
    
    
    }

}

EDIT #2:

Suppose that my SQL QUERY thrown 3 rows after executing this:

SELECT value
from test
where claim = 45;

Expected result:

I want to loop depending on the amount of rows thrown by the SQL QUERY and assign the value

In first loop, I need to set the parameter approverRelation within the value from row #1 and column "value 2"

In the second loop, I need to set the parameter approverRelation within the value from row #2 and column "value 2"

In the THIRD loop, I need to set the parameter approverRelation within the value from row #3 and column "value 2"

How can i do that?

EDIT #3 (within the loop):

package first;

import org.openqa.selenium.By;
import org.openqa.selenium.Keys;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.support.ui.FluentWait;
import org.openqa.selenium.support.ui.Wait;
import org.apache.commons.dbutils.DbUtils;
import java.sql.DriverManager;

public class myCodes {
    static Connection conn = null;
    static QueryRunner run = new QueryRunner();
    static boolean keepConnection = false;

    public static void main(String[] args) throws InterruptedException{
    System.setProperty("webdriver.chrome.driver","C:\\Users\\Steven\\Desktop\\Folder\\chromedriver.exe");
    
      public static void createConn(String url, String driver, String usr, String pwd) throws SQLException, IOException {

        if (conn == null || conn.isClosed()) {
            DbUtils.loadDriver(driver);
            conn = DriverManager.getConnection(url, usr, pwd);
            conn.setAutoCommit(false);
        }
    }
    
    

/**
 * returns the row as map
 * @param sql input sql string
 * @param params any additional parameters
 */
public static Map<String, Object> getQueryResultInMap(String url, String driver, String usr, String pwd,String sql, Object... params) throws SQLException, IOException {

    try {
        createConn(url,driver,usr,pwd);
        if (params == null) {
            return run.query(conn, sql, new MapHandler());
        } else {
            return run.query(conn, sql, new MapHandler(), params);
        }
    } catch (SQLException se) {
        se.printStackTrace();
        return null;
    } finally {
        closeConn();
    }
}

    public static void closeConn() throws SQLException {
        if (!keepConnection) {
            DbUtils.closeQuietly(conn);
        }
    }
    
    



String sqlQuery= "SELECT value from test where claim = ?"; 
List<Map<String, Object>>resultSet=getResultInMapList(String url, String driver, String usr, String pwd,sqlQuery, "45");
    
    for (Map<String, Object> rows: resultSet) { for (Map.Entry<String, Object> row: rows.entrySet()) 
{
    WebDriver driver = new ChromeDriver();
    driver.get("https://testingserver/backend");
    driver.findElement(By.id("username")).sendKeys("admin");
    driver.findElement(By.id("login")).sendKeys("randomPassw0rd@");
    driver.findElement(By.xpath("//*[@id=\"login-form\"]/fieldset/div[3]/div[2]/button")).click();
    Thread.sleep(4000);
    driver.findElement(By.xpath("//*[@id=\"menu-bbb-backend-stores\"]/a")).click();
    Thread.sleep(4000);
    driver.findElement(By.xpath("//*[@id=\"menu-bbb-backend-stores\"]/div/ul/li[2]/ul/li[2]/div/ul/li[3]/a")).click();
    driver.findElement(By.xpath("//*[@id=\"attributeGrid_filter_frontend_label\"]")).sendKeys(resultSet.get("approverRelation"));
    Thread.sleep(3000);
    driver.findElement(By.xpath("//*[@id=\"attributeGrid_filter_frontend_label\"]")).sendKeys(Keys.ENTER);
    Thread.sleep(3000);
    driver.findElement(By.xpath("//*[@id=\"attributeGrid_table\"]/tbody/tr/td[1]")).click();
    //driver.findElement(By.cssSelector(".col-attr-code col-attribute_code")).click(); 
    Thread.sleep(3000);
    }
    
    }

}

ERROR:

package first;

import org.openqa.selenium.By;
import org.openqa.selenium.Keys;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.support.ui.FluentWait;
import org.openqa.selenium.support.ui.Wait;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;

import java.sql.Connection;
import java.sql.DriverManager;

public class myCodes{
    static Connection conn = null;
    static QueryRunner run = new QueryRunner();
    static boolean keepConnection = false;
    String urlString="jdbc:sqlserver://FR3DSSD.NNBD.local:1833;databaseName=database3";
    String usernameString="admin";
    String password="ferer";
    String driverr="com.microsoft.sqlserver.jdbc.SQLServerDriver";
    Connection connection=null;
    
    
    public static void main(String[] args) throws InterruptedException{
    System.setProperty("webdriver.chrome.driver","C:\\Users\\Steven\\Desktop\\SELENIUM\\chromedriver.exe");
    
    public static void createConn(String urlString, String driverr, String usernameString, String password) throws SQLException, IOException {

        if (conn == null || conn.isClosed()) {
            DbUtils.loadDriver(driver);
            conn = DriverManager.getConnection(url, usr, pwd);
            conn.setAutoCommit(false);
        }
    }

EDIT #4:

package first;

import org.openqa.selenium.By;
import org.openqa.selenium.Keys;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.support.ui.FluentWait;
import org.openqa.selenium.support.ui.Wait;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Map;

public class assign_Code {
    static Connection conn = null;
    static QueryRunner run = new QueryRunner();
    static boolean keepConnection = false;
    String urlString="jdbc:sqlserver://server43.ffgr.local:1633;databaseName=test";
    String usernameString="admin";
    String password="admin";
    String driverr="com.microsoft.sqlserver.jdbc.SQLServerDriver";
    Connection connection=null;
    String sqlQuery= "SELECT value from test32 where claim = 34";
    
    public static void createConn(String urlString, String driverr, String usernameString, String password) throws SQLException, IOException {

        if (conn == null || conn.isClosed()) {
            String driver = null;
            DbUtils.loadDriver(driver);
            conn = DriverManager.getConnection(urlString, usernameString, driverr);
            conn.setAutoCommit(false);
        }
    }
    
    public static Map<String, Object> getQueryResultInMap(String urlString, String driverr, String usernameString, String password,String sqlQuery, Object... params) throws SQLException, IOException {

        try {
            createConn(urlString,driverr,usernameString,password);
            if (params == null) {
                return run.query(conn, sqlQuery, new MapHandler());
            } else {
                return run.query(conn, sqlQuery, new MapHandler(), params);
            }
        } catch (SQLException se) {
            se.printStackTrace();
            return null;
        } finally {
            conn.close();
        }
    }
    
    
    public static void main(String[] args) throws InterruptedException, SQLException, IOException{
    System.setProperty("webdriver.chrome.driver","C:\\Users\\Maxi\\Steven\\SELENIUM\\chromedriver.exe");
    
    String urlString="jdbc:sqlserver://server43.ffgr.local:1633;databaseName=test";
    String usernameString="admin";
    String password="admin";
    String driverr="com.microsoft.sqlserver.jdbc.SQLServerDriver";
    String sqlQuery= "SELECT value from test32 where claim = 34";
    
    
    Map<String,Object>resultSet= getQueryResultInMap(urlString, driverr, usernameString, password, sqlQuery, null);

    WebDriver driver = new ChromeDriver();
    driver.get("https://stage.test.com/backend");
    driver.findElement(By.id("username")).sendKeys("admin");
    driver.findElement(By.id("login")).sendKeys("admin");
    driver.findElement(By.xpath("//*[@id=\"login-form\"]/fieldset/div[3]/div[2]/button")).click();
    Thread.sleep(4000);
    driver.findElement(By.xpath("//*[@id=\"menu-magento-backend-stores\"]/a")).click();
    Thread.sleep(4000);
    driver.findElement(By.xpath("//*[@id=\"menu-magento-backend-stores\"]/div/ul/li[2]/ul/li[2]/div/ul/li[3]/a")).click();
    driver.findElement(By.xpath("//*[@id=\"attributeGrid_filter_frontend_label\"]")).sendKeys("grupo");
    Thread.sleep(3000);
    driver.findElement(By.xpath("//*[@id=\"attributeGrid_filter_frontend_label\"]")).sendKeys(Keys.ENTER);
    Thread.sleep(3000);
    driver.findElement(By.xpath("//*[@id=\"attributeGrid_table\"]/tbody/tr/td[1]")).click();
    //driver.findElement(By.cssSelector(".col-attr-code col-attribute_code")).click(); 
    //Thread.sleep(6000);
    driver.findElement(By.id("add_new_option_button")).click();
    driver.findElement(By.xpath("//*[@id=\"manage-options-panel\"]/table/tbody/tr[117]/td[3]/input")).sendKeys(resultSet.get("approverEmail"));

    driver.findElement(By.xpath("//*[@id=\"save\"]/a")).click();
    Thread.sleep(3000);
    
    /////////////////////////////////////////////////////////////////////////////
    //driver.findElement(By.className("col-attr-code col-attribute_code")).click();
    //Thread.sleep(6000);
    //driver.findElement(By.By.xpath("//*[@id="attributeGrid_table"]/tbody/tr/td[1]")).click();
    //driver.findElement(By.className("col-label col-frontend_label")).click();
    //driver.findElement(By.className("col-label col-frontend_label")).click();
    //*[@id="id_V0AMpxmF4824s1tKFpEC9p9ZYU4BNXVA"]
    //System.out.println(driver.getTitle());
    //driver.quit();
    
    }

}

from:

Map<String,Object>resultSet= getQueryResultInMap(urlString, driverr, usernameString, password, sqlQuery, null);

i am having this:

Type null of the last argument to method getQueryResultInMap(String, String, String, String, String, Object...) doesn't exactly match the vararg parameter type. Cast to Object[] to confirm the non-varargs invocation, or pass individual arguments of type Object for a varargs invocation.

and from

driver.findElement(By.xpath("//*[@id=\"manage-options-panel\"]/table/tbody/tr[117]/td[3]/input")).sendKeys(resultSet.get("approverEmail"));

i am having this:

The method sendKeys(CharSequence...) in the type WebElement is not applicable for the arguments (Object)

EDIT looping:

package first;

import org.openqa.selenium.By;
import org.openqa.selenium.Keys;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.support.ui.FluentWait;
import org.openqa.selenium.support.ui.Wait;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class assign_code{
    static Connection conn = null;
    static QueryRunner run = new QueryRunner();
    static boolean keepConnection = false;
    
    public static void createConn(String urlString, String driverr, String usernameString, String password) throws SQLException, IOException {

        if (conn == null || conn.isClosed()) {
            String driver = null;
            DbUtils.loadDriver(driver);
            conn = DriverManager.getConnection(urlString, usernameString, password);
            conn.setAutoCommit(false);
        }
    }
    
    public static List<Map<String, Object>> getResultInMapList(String urlString, String driverr, String usernameString, String password,String sqlQuery, Object... params) throws
    SQLException, IOException {

        try {
            createConn(urlString,driverr,usernameString,password);
            if (params == null) {
            return run.query(conn, sqlQuery, new MapListHandler());
      } else {
          return run.query(conn, sqlQuery, new MapListHandler(), params);
    }
     } catch (SQLException se) {
    se.printStackTrace();
         return null;
     } finally {
    closeConn();
     }
   }
    
    
    public static void closeConn() throws SQLException {
        if (!keepConnection) {
            DbUtils.closeQuietly(conn);
        }
    }
    
    
    public static void main(String[] args) throws InterruptedException, SQLException, IOException{
    System.setProperty("webdriver.chrome.driver","C:\\Users\\Steven\\Desktop\\SELENIUM\\chromedriver.exe");
    
    String urlString="jdbc:sqlserver://GERTER5404.btqw.local:1433;databaseName=Test";
    String usernameString="admin";
    String password="admin";
    String driverr="com.microsoft.sqlserver.jdbc.SQLServerDriver";
    String sqlQuery= "select APPROVERID from orders.Approvals where ITEMID = ?";
    
    Map<String,Object>resultSet= getResultInMapList(urlString, driverr, usernameString, password, sqlQuery, "45");

    WebDriver driver = new ChromeDriver();
    driver.get("https://stage.com/backend");
    driver.findElement(By.id("username")).sendKeys("admin");
    driver.findElement(By.id("login")).sendKeys("12345678");
    driver.findElement(By.id("add_new_option_button")).click();
    driver.findElement(By.xpath("//*[@id=\"manage-options-panel\"]/table/tbody/tr[117]/td[3]/input")).sendKeys(resultSet.get("APPROVERID").toString());

    driver.findElement(By.xpath("//*[@id=\"save\"]/a")).click();
    Thread.sleep(3000);

    
    }
}

Solution

To resolve this you need to follow few steps:

Create a JDBC connection to your DB
Query your Table and store it in form of a List if multiple records returned or map if single record returned.

3.Iterate through each element of List or Map pass the value via send keys one after another.

Step 1: Add below dependecy in your POM.xml

<dependency>
    <groupId>commons-dbutils</groupId>
    <artifactId>commons-dbutils</artifactId>
    <version>1.6</version>
</dependency>

For Creating DB connection:

import org.apache.commons.dbutils.DbUtils;
import java.sql.DriverManager;

  public static void createConn(String url, String driver, String usr, String pwd) throws SQLException, IOException {

        if (conn == null || conn.isClosed()) {
            DbUtils.loadDriver(driver);
            conn = DriverManager.getConnection(url, usr, pwd);
            conn.setAutoCommit(false);
        }
    }

Step 2: Get Query result and store it in a List/Map:

a. get query result in list when multiple records are returned:

static Connection conn = null;
static QueryRunner run = new QueryRunner();
static boolean keepConnection = false;

/**
     * returns the rows as list object array
     * @param sql input sql string
     * @param params any additional parameters
     */
    public static List<Object[]> getQueryResultInArrayList(String url, String driver, String usr, String pwd,String sql, Object...params) throws SQLException, IOException{

        try {
            createConn(url,driver,usr,pwd);
            if (params == null) {
                return run.query(conn, sql, new ArrayListHandler());
            } else {
                return run.query(conn, sql, new ArrayListHandler(), params);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConn();
        }
    }

    public static void closeConn() throws SQLException {
        if (!keepConnection) {
            DbUtils.closeQuietly(conn);
        }
    }

b. store single record as map:

/**
     * returns the row as map
     * @param sql input sql string
     * @param params any additional parameters
     */
    public static Map<String, Object> getQueryResultInMap(String url, String driver, String usr, String pwd,String sql, Object... params) throws SQLException, IOException {

        try {
            createConn(url,driver,usr,pwd);
            if (params == null) {
                return run.query(conn, sql, new MapHandler());
            } else {
                return run.query(conn, sql, new MapHandler(), params);
            }
        } catch (SQLException se) {
            se.printStackTrace();
            return null;
        } finally {
            closeConn();
        }
    }

c. For multiple row and columns as result

/**
     * returns the rows as list of map
     * @param sql input sql string
     * @param params any additional parameters
     */
    public static List<Map<String, Object>> getResultInMapList(String url, String driver, String usr, String pwd,String sql, Object... params) throws
            SQLException, IOException {

        try {
            createConn(url,driver,usr,pwd);
            if (params == null) {
                return run.query(conn, sql, new MapListHandler());
            } else {
                return run.query(conn, sql, new MapListHandler(), params);
            }
        } catch (SQLException se) {
            se.printStackTrace();
            return null;
        } finally {
            closeConn();
        }
    }

Step 3: Implementation:

 String sqlQuery= "SELECT value from test32 where claim = 34";
Map<String,Object>resultSet= getQueryResultInMap(String url, String driver, String usr, String pwd,String sqlQuery,null);

 String sqlQuery= "SELECT value from test32 where claim = ?";
    Map<String,Object>resultSet= getQueryResultInMap(String url, String driver, String usr, String pwd,String sqlQuery,"34");

Where Key store the column name and value stores the value

Please refer https://www.demo2s.com/java/apache-commons-queryrunner-execute-string-sql-object-params.html

Answered By - Sonali Das
Answer Checked By - Candace Johnson (JavaFixing Volunteer)

This Answer collected from stackoverflow and tested by JavaFixing community admins, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Tuesday, October 4, 2022

[FIXED] How to avoid hardcoded string in sendKeys and pass a value from SQL SENTENCE (JAVA & SELENIUM)

Issue

Solution

Popular Posts

Labels