如何在Selenium Java中从SQL Server取数填充sendKeys，避免硬编码？

问题 #

我需要从SQL Server数据库的两条不同SQL语句中取值，传入Selenium的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);
    
    
    }

}

需要替换两处硬编码：

1. 将driver.findElement(By.xpath("//*[@id=\"attributeGrid_filter_frontend_label\"]")).sendKeys("group");中的值替换为执行SQL：

SELECT value
from test
where claim = 45;

的结果；
2. 将driver.findElement(By.xpath("//*[@id=\"manage-options-panel\"]/table/tbody/tr/td[5]/input")).sendKeys("122");中的值替换为执行SQL：

SELECT value
from test32
where claim = 34;

的结果。

最优实现方案 #

1. 引入SQL Server JDBC驱动 #

如果用Maven，在pom.xml中添加依赖：

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>12.4.2.jre11</version> <!-- 按需选择对应JDK版本的驱动 -->
</dependency>

手动导入的话，下载对应版本的JDBC jar包添加到项目依赖即可。

2. 封装数据库查询工具类 #

单独写工具类统一处理数据库连接和查询，避免业务代码混乱：

package first;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class DBUtil {
    // 实际项目中建议用配置文件/环境变量读取，不要硬编码敏感信息
    private static final String DB_URL = "jdbc:sqlserver://your-db-server:1433;databaseName=your-db-name;encrypt=true;trustServerCertificate=true";
    private static final String DB_USER = "db-username";
    private static final String DB_PWD = "db-password";

    // 执行单值查询的通用方法
    public static String getSingleValue(String sql) {
        String result = null;
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PWD);
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            if (rs.next()) {
                result = rs.getString("value");
            }
        } catch (Exception e) {
            e.printStackTrace();
            // 实际项目建议用日志框架记录异常
        }
        return result;
    }
}

3. 修改原Selenium代码调用查询方法 #

在业务代码中调用工具类方法，替换硬编码值：

package first;

import org.openqa.selenium.By;
import org.openqa.selenium.Keys;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;

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

            // 从数据库获取过滤值
            String filterValue = DBUtil.getSingleValue("SELECT value FROM test WHERE claim = 45");
            if (filterValue != null) {
                driver.findElement(By.xpath("//*[@id=\"attributeGrid_filter_frontend_label\"]")).sendKeys(filterValue);
            } else {
                throw new RuntimeException("未查询到过滤字段对应的value");
            }

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

            // 从数据库获取输入值
            String inputValue = DBUtil.getSingleValue("SELECT value FROM test32 WHERE claim = 34");
            if (inputValue != null) {
                driver.findElement(By.xpath("//*[@id=\"manage-options-panel\"]/table/tbody/tr/td[5]/input")).sendKeys(inputValue);
            } else {
                throw new RuntimeException("未查询到输入字段对应的value");
            }

            driver.findElement(By.xpath("//*[@id=\"save\"]/a")).click();
            Thread.sleep(3000);
        } finally {
            // 确保浏览器关闭
            if (driver != null) {
                driver.quit();
            }
        }
    }
}

4. 额外优化建议 #

• 替换Thread.sleep为Selenium的显式等待（WebDriverWait），避免固定等待时间导致的不稳定：

  WebDriverWait wait = new WebDriverWait(driver, Duration.ofSeconds(10));
  wait.until(ExpectedConditions.elementToBeClickable(By.xpath("your-xpath"))).click();
  

• 把SQL语句也放到配置文件中，进一步解耦；
• 完善异常处理逻辑，比如数据库查询失败时的降级处理；
• 用日志框架（如SLF4J+Logback）替代printStackTrace，方便排查问题。

内容的提问来源于stack exchange，提问作者JustToKnow