如何避免sendKeys中的硬编码字符串并从SQL语句传递一个值(Java和Selify)

我想执行一个SQL语句(SQL服务器数据库)，复制值并将其放入sendKeys函数中，我正在考虑这样做：

步骤1)执行查询

步骤2)复制SQL语句中的值，并将其放入sendKeys函数中：

我的代码如下所示：

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

    }

}

我需要避免硬编码值，并传递从SQL语句获得的值：

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

我不想将“group”作为硬编码值传递，而是从下面的SQL语句中获取它：

SELECT value
from test
where claim = 45;

此外，还包括：

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

我不想将“122”作为硬编码值传递，而是从下面的SQL语句中获取它：

SELECT value
from test32
where claim = 34;

实现这一目标的最佳方式是什么？

编辑#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);

    }

}

要解决此问题，您需要执行以下几个步骤：

1.创建到数据库的JDBC连接
1.如果返回多条记录，则查询表并将其以列表的形式存储；如果返回单个记录，则将其Map。

3.遍历List或Map的每个元素，一个接一个地通过Send键传递值。

步骤1：在POM.xml中添加以下依赖项

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

用于创建数据库连接：

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

步骤2：获取查询结果并将其存储在列表/Map中：

A.当返回多条记录时，以列表形式获取查询结果：

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.将单个记录存储为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.作为结果的多行和多列

/**
     * 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 sql, Object... params) throws
            SQLException, IOException {

        try {
            createConn();
            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();
        }
    }

第三步：实施：

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

其中键存储列名，值存储值

请参考https://www.demo2s.com/java/apache-commons-queryrunner-execute-string-sql-object-params.html

如何避免sendKeys中的硬编码字符串并从SQL语句传递一个值(Java和Selify)

1条答案

相关问题

热门标签

最新问答