Connect to database via JDBC

Though we have numerous methods [and sophisticated too] to connect to a database with java, if you are looking for a simple snippet to just connect, run a query, and get the result, this below code will suffice.
Also, if your purpose is just to query and get results, just having the right jar would be enough, no need for the database client/driver installation.

--------------------------------------
//function to connect to database using JDBC/JConnect
public Connection getJDBCConnection(){

System.out.println("Getting DB Connection properties");
Properties properties = getPropertyValues("test1.properties");

String dbDriver = properties.getProperty("database.driver");
String dbServer = properties.getProperty("database.server");
String dbServerPort = properties.getProperty("database.port");
String dbName = properties.getProperty("database.name");
String dbUsername = properties.getProperty("database.username");
String dbPassword = properties.getProperty("database.password");

String dbURL = "jdbc:sybase:Tds:" + dbServer + ":" + dbServerPort + "/" + dbName;

//Initializing the connection object
Connection connection= null;

try{
//Register the database driver
Class.forName(dbDriver);

//Open a connection
System.out.println("Connecting to database - " + dbURL);
connection = DriverManager.getConnection(dbURL, dbUsername, dbPassword);
System.out.println("DB connection successful");

}catch(SQLException se){
se.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}

return connection;
}
--------------------------------------

//function to run query and get the resultset back
public ResultSet runDBQuery(Connection connection, String sqlToRun){

Statement statement = null;
ResultSet resultSet = null;

try{

statement = connection.createStatement();

System.out.println("Executing SQL Query - " + sqlToRun);
resultSet = statement.executeQuery(sqlToRun);

}catch(SQLException se){
se.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(connection.isClosed() == false)
connection.close();
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try

return resultSet;
}
--------------------------------------

Now to use all of these...
--------------------------------------
String sqlToRun = "SELECT EmployeeID FROM Employee Where EmployeeID = 245145";
try{
ResultSet rs = commonLib.runDBQuery(commonLib.getJDBCConnection(),sqlToRun);

while(rs.next()){
//Retrieve by column name
int id = rs.getInt("EmployeeID");
//String last = rs.getString("last");

System.out.println("EmployeeID: " + id);
}
rs.close();
}
catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}
--------------------------------------
Output will be...
EmployeeID: 245145


The jdbc properties are as follows:

#This is the standard driver class for connecting to Sybase database using JConnect jar
database.driver=com.sybase.jdbc4.jdbc.SybDriver
database.server=abc.xyz.net
database.port=5500
database.name=emp_comp
database.username=autobot
database.password=autobot1

Read properties file in Java

Function to get all the properties from a given file, and return the properties object

//The properties files are all supposed be placed under this folder - "C:\\Automation\\resources\\", hence, only the filename is passed along
public Properties getPropertyValues(String propFileName){

String propFilePath = "C:\\Automation\\resources\\" + propFileName;
System.out.println("Reading properties from - " + propFilePath);

//initializing the objects
Properties properties = new Properties();
InputStream inputStream = null;

try {

inputStream = new FileInputStream(propFilePath);

// load a properties file
properties.load(inputStream);

}catch (FileNotFoundException f) {
f.printStackTrace();
} catch (IOException ex) {
ex.printStackTrace();
} finally {
if (inputStream != null) {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}

//This will return the properties object which can then be used to get all the properties from a give file.
return properties;
}

Export all the Environment Variables to a file

Steps to Export all your Environment Variables to a file. This will be helpful in corporate environments, where we dont have admin rights to browse/update Environment variables

1. Open cmd prompt.
2. Type in C:> set >> allenvvar.txt

The file will be created in the C drive

Voila!

BAT to launch applications with specific Environment Variables

Often you end up in situations, where after installing some software, your previous ones stop working; or the new ones dont work as they should as previous ones are interfering. One of the reasons for this issue could be the environment variables being set and used by such software. And this always happens when you have QTP [along with java add-in] installed, which causes other java-based software to just not launch/start altogether.
To overcome this issue of same environment variables being used by different programs, we can create a .BAT file to temporarily set and launch/start the applications with those environment variables.

This below code will help launch DBArtisan [database client] by setting up the environment variables required for it, some of which have been hijacked by QTP.
We can write similar stuff for any application we want.

-----------------------------------------------
Set SYBASE=C:\Progra~2\Syb155

Set SYBASE_OCS=OCS-15_0

Set INCLUDE=C:\Progra~2\Syb155\OCS-15_0\include;%INCLUDE%

Set LIB=C:\Progra~2\Syb155\OCS-15_0\lib;%LIB%

Set PATH=C:\Progra~2\Syb155\OCS-15_0\bin;C:\Progra~2\Syb155\OCS-15_0\dll;C:\Progra~2\Syb155\OCS-15_0\lib3p;%PATH%

Set PATH=C:\Progra~2\Syb155\DataAccess\OLEDB\dll;%PATH%

Set PATH=C:\Progra~2\Syb155\DataAccess\ODBC\dll;%PATH%

Set LIB=C:\Progra~2\Syb155\DataAccess\ADONET\dll;%LIB%

Set PATH=C:\Progra~2\Syb155\DataAccess\ADONET\dll;%PATH%


Start C:\Progra~2\Enbarcadero\dbart901_11364_ion.exe

-----------------------------------------------

Wait and Sync in Selenium

There are broadly 3 different options to choose from...

Implicit Wait - 

• An implicit wait is to tell WebDriver to poll the DOM for a certain amount of time when trying to find an element or elements if they are not immediately available. The default setting is 0. Once set, the implicit wait is set for the life of the WebDriver object instance, ie., as long as the browser is opened, all the elements will take the amount of time set as implicit wait to load.
• Implicit wait is defined at the browser level, and not at an individual element level, hence, Implicit waits will be useful only when entire page loads. (i.e., It won't wait for dynamically loading ajax elements). The implicit wait only waits for an element to appear in the DOM, so it returns immediately, but when you try to interact with the element you get a NoSuchElementException.
• There can be instances when a particular element takes more than a minute to load. In such a case if we set a huge time for Implicit wait, then the browser may wait for that amount of time for every element. To avoid that situation you can simply put a separate time on the required element only. By following this your browser implicit wait time would be short for every element and it would be large for specific element- and this can be achieved via Explicit Wait.

Syntax -

driver.manage.timeouts().implicitlyWait(10,TimeUnit.SECONDS);

WebDriverWait \ Explicit Wait - 

• An explicit wait is a code you define to wait for a certain condition to occur before proceeding further in the code. 
• Explicit waits are intelligent waits that are confined to a particular web element. Instead of instantly looking for an element, we can tell Webdriver to wait for a certain amount of time before we continue with the next step.
• The worst case of this is Thread.sleep(), which sets the condition to an exact time period to wait. WebDriverWait in combination with ExpectedCondition is a more preferred mode of implementation.

Syntax -

WebDriverWait wait = new WebDriverWait(driver,10);

wait.until(ExpectedConditons.elementToBeClickable(By.xpath("locator"));

FluentWait -

FluentWait uses a Polling Technique to wait i.e, it will keep on Polling every fixed interval for a particular element. But the important feature with FluentWait is that it uses Generics, which means we dont have to pass an instance of the WebDriver - we can directly use the WebElement, of By, or anything else.

Let’s say you have an element which sometime appears in just 1 second and some time it takes minutes to appear. In that case it is better to use fluent wait, as this will try to find element again and again until it find it or until the final timer runs out.

Advantages of FluentWait over WebDriverWait - 

• You dont have to pass the driver instance in FluentWait. This can be very helpful if you are using the PageObject Pattern for your Framework.
• You have more control over the wait time with the polling option - because with WebDriverWait, selenium would wait for the entire duration specified [lets say 20s] but with FluentWait we have the option of specifying the wait as well as the polling interval, hence, the actual wait time can be reduced with 5s wait and 4X polling, instead of directly waiting for 20s.

So its advantageous to use FluentWait for all the sync operations, as it gives fine-tuned control.

This is a function that demonstrates the use of FluentWait, and as you can see, we did not have to pass/use an instance of the WebDriver while creating this function.

------------------------------------------------------------
import org.openqa.selenium.support.ui.FluentWait;

//The FluentWait class uses the Function to implement .until
//This Function uses the following package [com.google.common.base.Function]
//There are many packages available for "Function", but only this has to be used,
// otherwise we get all sorts of weird errors
//Also, in the above function, we must use this class 'org.openqa.selenium.NoSuchElementException' instead of the default [import java.util.NoSuchElementException] suggested by IDE because if we use the java.util, then the FluentWait will fail with NoSuchElementException!! since, we did not use the correct package - stupid ide!
//So we should use the full package name anywhere we have to use the class declaration, otherwise, if we copy the code, and it asks us for the package, it might just add the default one again.

public Boolean checkElementIsDisplayedFluentWait(WebElement webElement) {

System.out.println("running checkElementIsDisplayedFluentWait");

Boolean displayStatus = false;

FluentWait<WebElement> fluentWait = new FluentWait<WebElement>(webElement);
fluentWait.ignoring(NoSuchElementException.class);
fluentWait.withTimeout(10, TimeUnit.SECONDS);
fluentWait.pollingEvery(5, TimeUnit.SECONDS);

displayStatus = fluentWait.until(new Function<WebElement, Boolean>() {
public Boolean apply(WebElement webElement) {
return webElement.isDisplayed();
}
});

System.out.println(displayStatus.booleanValue());

return displayStatus;

}

And it can be used as follows -
This will check if the element is displayed, and then capture its displayed text.
if(commonLib.checkElementIsDisplayedFluentWait(webElement)){
System.out.println(webElement.getText());
}
------------------------------------------------------------


Need to explore the option to check for conditions/properties using the FluentWait like in the WebDriveWait

Also, all of the above examples will work when there is a change in the DOM, but there can be situations where the page sends requests for data without changing the DOM. In such cases, we may opt to check for asynchronous events using JQuery or JScript.

Stay tuned for more, as this is still wip...