I need to loop depending on the number of rows thrown by a given SQL SENTENCE and assign the value of the column in every iteration
This is my CODE:
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);
}
}
Suppose that my SQL QUERY thrown 3 rows after executing this:
SQL QUERY:
select APPROVERID from orders.Approvals where ITEMID = 45;
Image:
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 APPROVERID within the value from row #1 and column #4
In the second loop, I need to set the parameter APPROVERID within the value from row #2 and column #4
In the THIRD loop, I need to set the parameter APPROVERID within the value from row #3 and column #4
I did this:
A function called "getResultInMapList":
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();
}
}
Invoking the method:
String sqlQuery= "select APPROVERID from orders.WebOrderItem where itemid = ?";
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())
{ driver.findElement(By.xpath("//*[@id=\"attributeGrid_filter_frontend_label\"]")).ssendKeys(resultSet.get("sku").toString()); }
}
I am having this message:
What am i doing wrong?
Am i looping alright?
EDIT #1:
I am having this error after using this sentence:
List<Map<String, Object>>resultSet= getResultInMapList(urlString, driverr, usernameString, password, sqlQuery, "45");
The method get(int) in the type List<Map<String,Object>> is not applicable for the arguments (String)
CodePudding user response:
You have Declared
List<Map<String, Object>> getResultInMapList(String urlString, String driverr, String usernameString, String password,String sqlQuery, Object... params)`
and inside main method you are calling
Map<String,Object>resultSet= getResultInMapList(urlString, driverr, usernameString, password, sqlQuery, "45")`
which is never declared. Hence the error
CodePudding user response:
You're getting that error because your method "getResultInMapList" is returning a List of Map (List<Map<String,Object>>) and you're trying to place those results into a single map Map<String, Object> instead in a List of Map.
I'm looking at the image at the end of your question, and in there, instead of:
Map<String, Object>resultSet=getResultInMapList(String url, String driver, String usr, String pwd,sqlQuery, "45");
You need to write:
List<Map<String, Object>>resultSet=getResultInMapList(String url, String driver, String usr, String pwd,sqlQuery, "45");
And that will fix your error.