Home > database >  How to avoid hardcoded string in sendKeys and pass a value from SQL SENTENCE (JAVA & SELENIUM)
How to avoid hardcoded string in sendKeys and pass a value from SQL SENTENCE (JAVA & SELENIUM)

Time：10-03

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:

enter image description here

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" enter image description here

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

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

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)

CodePudding user response：

To resolve this you need to follow few steps:

  1. Create a JDBC connection to your DB
  2. 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);

or

 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

Page link：https//www.codepudding.com/database/563942.html

Prev:Trying to locate an element in a webpage but getting NoSuchElementException
Next:@layout/activity_main does not contain a declaration with id tvDescription
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding