一个较完整的selenium脚本——对jdbc的使用

package tianjs_ctp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.concurrent.TimeUnit;

import org.openqa.selenium.By;
import org.openqa.selenium.JavascriptExecutor;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.openqa.selenium.support.ui.ExpectedCondition;
import org.openqa.selenium.support.ui.WebDriverWait;

public class CbpScript {
	public static void main(String[] args) throws Exception {
		/**
		 * 数据库的操作
		 */
		Connection con = null;
		PreparedStatement pre = null;
		ResultSet result = null;
		
		String className = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@ip:1521:orcltest05";
		String dbUser = "tianjs_test_jj";
		String dbPwd = "tianjs_test_jj_new";
		String sql = "select * from (select * from cs_batch_buy_b c order by c.crt_dt desc) a where rownum <= 1";
		String seq_no = null;
		String sqlSta = "select *from cs_batch_buy_cmd_l  where seq_no = '" + seq_no +"'";
	
		System.setProperty("webdriver.firefox.bin", "D:\\Program Files (x86)\\Mozilla Firefox\\firefox.exe");
		WebDriver driver = new FirefoxDriver();
		/**
		 * 登录cbp
		 */
		driver.get("http://URL/");
		driver.findElement(By.xpath(".//input[@name='loginSysBtn']"));
		String logButtonXpath = ".//input[@name='loginSysBtn']";
		String yzmYm = driver.findElement(By.id("imgVerify")).getAttribute("src");
		String yzm = yzmYm.substring(yzmYm.indexOf("=") + 1, yzmYm.lastIndexOf('.'));
		LoginPage login = new LoginPage(driver, "username", "password", logButtonXpath, ".//input[@id='checkmark']");
		driver.manage().timeouts().implicitlyWait(3, TimeUnit.SECONDS);
		login.login("zhuoyi", "123456", yzm);
		
		/**
		 * 加薪宝批量认购功能校验
		 */

		//driver.findElement(By.xpath(".//*[text()='加薪宝批量认购']")).click();
		driver.findElement(By.xpath(".//*[text()='名单导入']")).click();;
		driver.manage().timeouts().implicitlyWait(3, TimeUnit.SECONDS);
		driver.switchTo().frame(driver.findElement(By.xpath(".//div[@id='mainTab']/div[2]/div[2]/div/iframe")));
		//处理日期元素
		JavascriptExecutor js = (JavascriptExecutor)driver;
		js.executeScript("arguments[0].removeAttribute('readonly');", driver.findElement(By.xpath(".//*[@id='editForm']//td[2]/span/input[1]")));
		
		driver.findElement(By.xpath(".//*[@id='editForm']//td[2]/span/input[1]")).sendKeys("2017-03-19");//日期考虑用POI做成Excel读取形式
		driver.findElement(By.xpath(".//*[@id='editForm']//td[2]/span/input[1]")).click();
		driver.findElement(By.xpath(".//*[@id='editForm']//td[2]/span/input[1]")).click();

		
		driver.findElement(By.xpath("/html/body/div[1]/div[2]/div/div/div/form/table/tbody/tr[2]/td[2]/span/span/a")).click();;
		driver.manage().timeouts().implicitlyWait(2, TimeUnit.SECONDS);
		
		//选择公司
		
		driver.findElement(By.xpath(".//*[@id='_easyui_combobox_i1_1']")).click();
		System.out.println("company is selected or not:" + driver.findElement(By.xpath(".//*[text()='天安金交所']")).isSelected());
		//选择产品
		driver.findElement(By.xpath(".//*[@id='editForm']/table/tbody/tr[4]/td[2]/span/span/a")).click();
		driver.findElement(By.id("_easyui_combobox_i2_1")).click();
		
		
		System.out.println("bdID is selected or not:" + driver.findElement(By.id("bdId")).isSelected());
		 
		//上传文件
		driver.findElement(By.xpath(".//form[@id='editForm']//tr[5]//div[@id='multipleupload']")).click();
		Runtime run = Runtime.getRuntime();
		driver.manage().timeouts().implicitlyWait(2, TimeUnit.SECONDS);
		run.exec("D:\\shangchuan\\upload.exe" + " " + "firefox" + " " + "D:\\shangchuan\\加薪宝认购名单模板.xlsx");
		Thread.sleep(1000);
		driver.findElement(By.xpath(".//*[@id='editForm']/table/tbody/tr[6]/td/a")).click();
		
		
		
		
		/**
		 * 漏写了对弹窗的处理，执行会出现问题，添加上，弹窗非alert，为div快，按一般元素处理
		*/
//		driver.switchTo().alert().accept();
		
//		new WebDriverWait(driver, 100).until(new ExpectedCondition<WebElement>() {
//			@Override
//			public WebElement apply(WebDriver d) {
//				// TODO Auto-generated method stub
//				return d.findElement(By.xpath("html/body/div[7]/div[2]/div[4]/a[1]"));
//			}
//		});
		
		
		
		
		//这部分是上传文件那里，去掉注释可以使用
		driver.findElement(By.xpath("html/body/div[7]/div[2]/div[4]/a[1]")).click();
		if (driver.findElement(By.xpath(".//*[text()='上传文件明细']")).isDisplayed()) {
			System.out.println("文件上传成功");
		}
		
		
		
		
		
		/**
		 * 切换到批量认购指令申领页，先校验上传成功的批次存在，JDBC对比
		 */
		//从iframe切换到父级
		driver.switchTo().parentFrame();
		driver.findElement(By.xpath(".//*[text()='批量认购指令申领']")).click();
		driver.manage().timeouts().implicitlyWait(1, TimeUnit.SECONDS);
		driver.switchTo().frame(driver.findElement(By.xpath(".//div[@id='mainTab']//div[2]//div[2]//iframe")));
		//数据库查询记录select
		con = DriverManager.getConnection(url, dbUser, dbPwd);//获取连接
		System.out.println("连接成功");
		pre = con.prepareStatement(sql);//实例化预编译语句
		result = pre.executeQuery();
		Thread.sleep(1000);
	
		while (result.next()) {
			//当结果集不为空时
			if (driver.findElement(By.xpath(".//*[text()='" + result.getString("batch_no") +"']")).isDisplayed()) {
				System.out.println("查询出的数据为：" + result.getString("batch_no") + " " + "状态为:" + result.getString("sta"));
				seq_no = result.getString("batch_no");
				System.out.println("导入成功");
			}
		}
		//进行批量认购申领动作
		driver.findElement(By.xpath(".//*[text()='" + result.getString("batch_no") +"']")).click();
		
		new WebDriverWait(driver, 100).until(new ExpectedCondition<WebElement>() {
			@Override
			public WebElement apply(WebDriver d) {
				// TODO Auto-generated method stub
				return d.findElement(By.xpath(".//*[text()='确定要处理该产品吗？']"));
			}
		});
		
		driver.findElement(By.xpath("html/body/div[1]/ul/li[2]/a/span")).click();
		while(driver.findElement(By.xpath(".//*[text()='确定要处理该产品吗？']")).isDisplayed() && driver.findElement(By.xpath(".//*[text()='系统提示']")).isDisplayed()){
			System.out.println("弹框显示！");
			driver.findElement(By.xpath(".//*[text()='确定']")).click();
			if (driver.findElement(By.xpath(".//*[text()='操作成功!']']")).isDisplayed()) {
				System.out.println("提示操作成功弹框显示！");
				driver.findElement(By.xpath(".//*[text()='确定']")).click();
			}else{
				//处理失败
				System.out.println("处理失败，查看原因！！！！！");
			}
		}
		
		pre = con.prepareStatement(sqlSta);//实例化预编译语句
		result = pre.executeQuery();
		System.out.println("批次处理状态转换为:" + result.getString("sta"));
		while ("SL".equals(result.getString("sta"))) {
			System.out.println("申领成功，进行下一步批量处理确认！！！");
		}
		
		/**
		 * 批量认购确认功能页操作
		 */
		driver.switchTo().parentFrame();
		driver.findElement(By.xpath("/html/body/div[3]/div/div/div[2]/div[2]/div/iframe")).click();
		driver.manage().timeouts().implicitlyWait(2, TimeUnit.SECONDS);
		driver.switchTo().frame(driver.findElement(By.xpath(".//div[@id='mainTab']/div[2]/div[3]/div/iframe")));
		Thread.sleep(100);
		if (driver.findElement(By.xpath(".//text()='" + seq_no + "'")).isDisplayed()) {
			System.out.println("已申领的批次在确认页面显示，上面的流程正确执行");
		}
		
		driver.findElement(By.xpath(".//text()='" + seq_no + "'")).click();
		driver.findElement(By.xpath("html/body/div[1]/ul/li[2]/a/span")).click();//点击“批量认购指令确认”按钮
		//处理弹窗
		
		new WebDriverWait(driver, 100).until(new ExpectedCondition<WebElement>() {
			@Override
			public WebElement apply(WebDriver d) {
				// TODO Auto-generated method stub
				return d.findElement(By.xpath(".//*[text()='确定要处理该产品吗？']"));
			}
		});
		
		while(driver.findElement(By.xpath(".//*[text()='确定要处理该产品吗？']")).isDisplayed() && driver.findElement(By.xpath(".//*[text()='系统提示']")).isDisplayed()){
			System.out.println("弹框显示！");
			driver.findElement(By.xpath(".//*[text()='确定']")).click();
			if (driver.findElement(By.xpath(".//*[text()='操作成功!']']")).isDisplayed()) {
				System.out.println("提示操作成功弹框显示！");
				driver.findElement(By.xpath(".//*[text()='确定']")).click();
			}else{
				//处理失败
				System.out.println("处理失败，查看原因！！！！！");
			}
		}
		//查看该批次的状态，确定是否确认成功
		result = pre.executeQuery();
		System.out.println("批次处理状态转换为:" + result.getString("sta"));
		
		
		//数据库使用完要关闭
		if (result != null) {
			result.close();
		}
		if (pre != null) {
			pre.close();
		}
		if (con != null) {
			con.close();
		}
		System.out.println("数据库连接已关闭！");
		
		
		
	}
}