Import data from excel sheet to application’s database (sql)

Answered

I want to populate data in my application’s database using webdriver.

Is it possible?

som Train Asked on December 12, 2014 in Selenium WebDriver.
Add Comment
1 Answer(s)
Best answer

Hi SOm,

It is possible but webdriver got nothing to do with it. You write your program in Java or in other language and with that you would be able to connect your data base and perform sql queries for fetch and update data. I will soon write a tutorial on this, till the time I am sharing the whole code, see if you can make use of it :

DB UTIL


package utilities;
import java.io.BufferedReader;

import java.io.File;

import java.io.FileReader;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;
import config.Constant;
public class DbUtils {

// This is to create a DB connection

public static Connection createConnection()

{

Connection conn = null;

try

{

Class.forName("oracle.jdbc.driver.OracleDriver");

conn = DriverManager.getConnection("jdbc:oracle:thin:@jupiter:1521:DBNAME", "USERNAME", "PASSSWORD");

}

catch(ClassNotFoundException e)

{

System.err.println("Could not find Oracle Driver!!!");

}

catch(SQLException e)

{

System.err.println("Could not get the database connection!!!");

}

return conn;

}
//Here you will pass a ArrayList of Queries which you will get from the createQueries function

public static void loadDataToDB(Connection conn, ArrayList<String> listOfQueries) throws SQLException

{

Statement stmt = null ;

try{
for (String query : listOfQueries){

stmt = conn.createStatement();

stmt.executeUpdate(query);

}

}catch(SQLException sqle){

}
finally{

stmt.close();

}

}
//This will read your text file containing SQL statements and put it in a Array and return it

public static ArrayList<String> createQueries(String path)

{

String queryLine = new String();

StringBuffer sBuffer = new StringBuffer();

ArrayList<String> listOfQueries = new ArrayList<String>();
try

{

FileReader fr = new FileReader(new File(path));

BufferedReader br = new BufferedReader(fr);
// read the SQL file line by line

while ((queryLine = br.readLine()) != null)

{

// ignore comments beginning with #

int indexOfCommentSign = queryLine.indexOf('#');

if (indexOfCommentSign != -1)

{

if (queryLine.startsWith("#"))

{

queryLine = new String("");

}

else

queryLine = new String(queryLine.substring(0,indexOfCommentSign - 1));

}

// ignore comments beginning with --

indexOfCommentSign = queryLine.indexOf("--");

if (indexOfCommentSign != -1)

{

if (queryLine.startsWith("--"))

{

queryLine = new String("");

}

else

queryLine = new String(queryLine.substring(0,indexOfCommentSign - 1));

}

// ignore comments surrounded by /* */

indexOfCommentSign = queryLine.indexOf("/*");

if (indexOfCommentSign != -1)

{

if (queryLine.startsWith("#"))

{

queryLine = new String("");

}

else

queryLine = new String(queryLine.substring(0,indexOfCommentSign - 1));
sBuffer.append(queryLine + " ");

// ignore all characters within the comment

do

{

queryLine = br.readLine();

}

while (queryLine != null && !queryLine.contains("*/"));

indexOfCommentSign = queryLine.indexOf("*/");

if (indexOfCommentSign != -1)

{

if (queryLine.endsWith("*/"))

{

queryLine = new String("");

}

else

queryLine = new String(queryLine.substring(indexOfCommentSign + 2,queryLine.length() - 1));

}

}
// the + " " is necessary, because otherwise the content before

// and after a line break are concatenated

// like e.g. a.xyz FROM becomes a.xyzFROM otherwise and can not

// be executed

if (queryLine != null)

sBuffer.append(queryLine + " ");

}

br.close();
// here is our splitter ! We use ";" as a delimiter for each request

String[] splittedQueries = sBuffer.toString().split(";");
// filter out empty statements

for (int i = 0; i < splittedQueries.length; i++)

{

if (!splittedQueries[i].trim().equals("")

&& !splittedQueries[i].trim().equals("\t"))

{

listOfQueries.add(new String(splittedQueries[i]));

}

}

}

catch (Exception e)

{

System.out.println("*** Error : " + e.toString());

System.out.println("*** ");

System.out.println("*** Error : ");

e.printStackTrace();

System.out.println("################################################");

System.out.println(sBuffer.toString());

}

return listOfQueries;

}
}

TEST CASE


package testCases;
import java.sql.Connection;

import java.util.ArrayList;
import org.testng.annotations.Test;

import utilities.DbUtils;

import utilities.ExcelUtils;
public class RefreshData {

private Connection conn = null;

private ArrayList<String> listOfQueries = null;

@Test

public void f() throws Exception {

conn = DbUtils.createConnection();

listOfQueries = DbUtils.createQueries(PATH_SQL_TEXT_FILE + "SQL_TEXT_FILE_NAME" + ".sql");

DbUtils.loadDataToDB(conn, listOfQueries);

}

}

}

Regards,
Lakshay Sharma

xLov3rDns Professor Answered on December 12, 2014.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.