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

Question

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);

    
    }
}

Hey @tgdavies pal, thanks for commenting. Could you please tell and complement my code with the best approach to achieve what i need? — JustToKnow, Oct 01 '22 at 00:47
See https://docs.oracle.com/javase/tutorial/jdbc/basics/index.html — tgdavies, Oct 01 '22 at 00:48
@tgdavies i'm not referring to the database connection, i got confused with the other thing about passing the value from the SQL SENTENCE — JustToKnow, Oct 01 '22 at 00:50
You need to pass the value you get from your `ResultSet` as the parameter to `sendKeys`, instead of passing a `String` literal. — tgdavies, Oct 01 '22 at 01:08
@tgdavies I'm having trouble while thinking where the sql sentence should be placed in my code, where to open the database connection and how to set the result from the query into the sendKey function :/. Could you please show me some code? I'm kinda lost — JustToKnow, Oct 01 '22 at 01:16

Sonali Das · Accepted Answer · 2022-10-01T22:53:28.300

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);

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

Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/248579/discussion-on-answer-by-sonali-das-how-to-avoid-hardcoded-string-in-sendkeys-and). — Dharman, Oct 05 '22 at 20:02
Hey pal, can you check this one: https://stackoverflow.com/questions/74392304/not-able-to-pass-the-result-from-a-sql-query-to-a-method-in-java — JustToKnow, Nov 10 '22 at 18:30

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

1 Answers1