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?

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

    Professor Answered on December 12, 2014.
    Add Comment
  • Your Answer

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