Thursday, January 3, 2013

Tutorial Connecting to DataBase using Selenium WebDriver

Web Driver cannot directly connect to Database. You can only interact with your Browser using Web Driver. For this we use JDBC("Java Database Connectivity").The JDBC API is a Java API for accessing virtually any kind of tabular data.The value of the JDBC API is that an application can access virtually any data source and run on any platform with a Java Virtual Machine.

In simplest terms, a JDBC technology-based driver ("JDBC driver") makes it possible to do three things:

1.Establish a connection with a data source
2.Send queries and update statements to the data source
3.Process the results

1.Establish a connection with a data source
The traditional way to establish a connection with a database is to call the method
DriverManager.getConnection(URL,  "username", "password" )
URL :   jdbc:<subprotocol>:<subname>
<subprotocol>-the name of the driver or the name of a database connectivity mechanism
<subname> - The point of a subname is to give enough information to locate the data source .(Includes IP address , Port number and exact name of DataSource)

For connecting to MYSQL URL will be
jdbc:mysql://localhost:3306/hoale

2.Send queries and update statements to the data source
A Statement object is used to send SQL statements to a database over the created connection in Step 1.
Statement-created by the Connection.createStatement methods. A Statement object is used for sending SQL statements with no parameters.
PreparedStatement-created by the Connection.prepareStatement methods. A PreparedStatement object is used for precompiled SQL statements. These can take one or more parameters as input arguments (IN parameters).
CallableStatement-created by the Connection.prepareCall methods. CallableStatement objects are used to execute SQL stored procedures
In Short
createStatement methods-for a simple SQL statement (no parameters)
prepareStatement methods-for an SQL statement that is executed frequently
prepareCall methods-for a call to a stored procedure

3.Process the results
A ResultSet is a Java object that contains the results of executing an SQL query.We will have separate post on it.The JDBC API provides three interfaces for sending SQL statements to the database

Here is the code for it.

String url1 ="jdbc:mysql://localhost:3306/hoale";
           // Load Microsoft SQL Server JDBC driver
           String dbClass = "com.mysql.jdbc.Driver";
           Class.forName(dbClass).newInstance();
           //Get connection to DB
           Connection con = DriverManager.getConnection(url1, "root", "");
           //Create Statement
           Statement stmt = (Statement) con.createStatement();
           // method which returns the requested information as rows of data
           ResultSet result = (ResultSet) stmt.executeQuery("select * from employee");

The below example illustrates how to use/connect MySQL with Selenium using Java. 

Prerequisites:

-      mysql-connector-java-5.1.0-bin.jar  or above versions
-      Create table Employee in Navicat
-      Reading a old http://howtesting.blogspot.com/2013/01/creating-html5-page.html create a sample webform
This is example for connection DataBase(mysql) using Selenium WebDriver
package com;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.testng.annotations.AfterTest;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;

import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;
import com.thoughtworks.selenium.SeleneseTestBase;

public class ConnectDB extends SeleneseTestBase{
     WebDriver driver;
     String url ="";
     @BeforeTest
public void setUp() throws Exception{
     driver = new FirefoxDriver();
     url = "file:///D:/ECLIPSE/workspace_eclipseclassic/ConnectDB/src/com/modules/HTML5Demo.html";
     driver.get(url);
}
     @Test
     public void CreateDB() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
           //Prepare connection
           String url1 ="jdbc:mysql://localhost:3306/hoale";
           // Load Microsoft SQL Server JDBC driver
           String dbClass = "com.mysql.jdbc.Driver";
           Class.forName(dbClass).newInstance();
           //Get connection to DB
           Connection con = DriverManager.getConnection(url1, "root", "");
           //Create Statement
           Statement stmt = (Statement) con.createStatement();
           // method which returns the requested information as rows of data
           ResultSet result = (ResultSet) stmt.executeQuery("select * from employee");
           if(result.next())
           {
                String id = result.getString("ID");
                String info = result.getString("Info");
                driver.getCurrentUrl();
                WebElement a = driver.findElement(By.id("txtID"));
                a.sendKeys(id);
                WebElement b = driver.findElement(By.id("txtInfo"));
                b.sendKeys(info);
                WebElement btnclick = driver.findElement(By.id("btnclick"));
                btnclick.click();
                System.out.print("Passed");
           }
     }
    
     @AfterTest
public void tearDown(){
     driver.close();
}
}

101 comments:

  1. Hello ,
    I Would like to automate one scenario where . User performed approve action on data ,approved data get reflected in URL(Application) in form of Excel sheet , we want to automate those action which approved by the user in application where respective excel sheet is generated , it should execute automatically when approve action is perform con u please help me on this

    waiting for your reply

    ReplyDelete
  2. Good.. Keep posting more...

    Visit selenium easy for complete webdriver tutorials with sample frameworks and examples
    https://seleniumeasy.com

    ReplyDelete
  3. Hi Hoa,

    I see that you use Selenium WebDriver and MySQL. However, if you'd like to benefit from convenience of Selenium IDE, you may be interested in SeLite. It enhances Selenese and it helps the tests to use SQLite.

    See https://code.google.com/p/selite/wiki/ProjectHome

    ReplyDelete
  4. can i use SQL server 2012 ? some reason it's not working for SQL server 2012?

    ReplyDelete
  5. hi,I see that you utilize Selenium WebDriver and MySQL. In any case, on the off chance that you'd like to profit by comfort of Selenium IDE, you may be keen on SeLite. It improves Selenese and it helps the tests to utilize SQLite.

    informatica training in chennai | hadoop training in chennai

    ReplyDelete
  6. Help me to retrieve data from as400/database maching,,!
    Is it possible to retrive data from that ...kindly help m ewith that ..

    Thanks,
    Sudheer Chittireddy

    ReplyDelete
  7. This information is impressive; I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic..

    Perl training in chennai
    Python training in chennai

    ReplyDelete
  8. Interesting and worth able content is discussed here. The fact about current technology is explicitly stated over here. I do agree on your thoughts on how the influencers are taking advantage over emerging technology. Thanks for sharing this in here. Keep bloging like this.

    Hadoop Training Chennai | Hadoop Training in Chennai | hadoop training in Chennai

    ReplyDelete
  9. Really awesome blog. Your blog is really useful for me. Thanks for sharing this informative blog. Keep update your blog.
    SAS Training In Chennai

    ReplyDelete


  10. Dear Admin,

    Nice topic. Very useful. But am getting error like “com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘ DB name . Table Name’ doesn’t exist.

    Kindly assit Please help. ASAP

    ReplyDelete
  11. • Its really helpful for me. Suppose if anyone want to find real time coaching center.recently i did some blog may visit..tibco training in chennai

    ReplyDelete
  12. Quite a useful post, I learned some new points here. Thanks admin please keep posting updates regularly to enlighten our knowledge.
    PHP Training in Chennai | PHP Course in Chennai

    ReplyDelete
  13. This comment has been removed by the author.

    ReplyDelete
  14. This blog is having the general information. Got a creative work and this is very different one.We have to develop our creativity mind.This blog helps for this. Thank you for this blog. This is very interesting and useful.
    SQL Server Training in Chennai

    ReplyDelete
  15. How can i write webtable into mysql database directly using selenium.

    ReplyDelete
  16. I believe there are many more pleasurable opportunities ahead for individuals that looked at your site.
    Best Java Training Institute Chennai


    ReplyDelete
  17. The young boys ended up stimulated to read through them and now have unquestionably been having fun with these things.
    Dotnet developer
    dotnet training in bangalore

    ReplyDelete
  18. This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.I like it and help me to development very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.

    Hadoop Training in Chennai

    Hadoop Training in Bangalore

    Big data training in tambaram

    Big data training in Sholinganallur

    Big data training in annanagar

    Big data training in Velachery

    Big data training in Marathahalli

    ReplyDelete
  19. This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.I like it and help me to development very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.


    java training in chennai | java training in bangalore

    java online training | java training in pune

    selenium training in chennai

    selenium training in bangalore

    ReplyDelete
  20. The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.
    Devops Training in pune|Devops training in tambaram|Devops training in velachery|Devops training in annanagar
    DevOps online Training

    ReplyDelete
  21. The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.
    Devops Training in pune|Devops training in tambaram|Devops training in velachery|Devops training in annanagar
    DevOps online Training

    ReplyDelete
  22. This comment has been removed by the author.

    ReplyDelete
  23. From your discussion I have understood that which will be better for me and which is easy to use. Really, I have liked your brilliant discussion. I will comThis is great helping material for every one visitor. You have done a great responsible person. i want to say thanks owner of this blog.
    python training in chennai
    python training in chennai
    python training in Bangalore

    ReplyDelete
  24. Appreciating the persistence you put into your blog and detailed information you provide
    python online training
    python training course in chennai
    python training in jayanagar

    ReplyDelete
  25. This looks absolutely perfect. All these tiny details are made with lot of background knowledge. I like it a lot. 
    Python training in pune
    AWS Training in chennai
    Python course in chennai

    ReplyDelete
  26. Nice post. By reading your blog, i get inspired and this provides some useful information. Thank you for posting this exclusive post for our vision. 
    Devops Training in pune

    ReplyDelete
  27. Good Post, I am a big believer in posting comments on sites to let the blog writers know that they ve added something advantageous to the world wide web.
    DevOps online Training
    Best Devops Training institute in Chennai

    ReplyDelete
  28. Well somehow I got to read lots of articles on your blog. It’s amazing how interesting it is for me to visit you very often.
    python training in chennai | python training in chennai | python training in bangalore

    ReplyDelete
  29. I prefer to study this kind of material. Nicely written information in this post, the quality of content is fine and the conclusion is lovely. Things are very open and intensely clear explanation of issues
    Java training in Bangalore |Java training in Rajaji nagar | Java training in Bangalore | Java training in Kalyan nagar

    Java training in Bangalore | Java training in Kalyan nagar | Java training in Bangalore | Java training in Jaya nagar

    ReplyDelete
  30. The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.

    angularjs interview questions and answers

    angularjs Training in bangalore

    angularjs Training in bangalore

    angularjs online Training

    angularjs Training in marathahalli

    ReplyDelete
  31. In the beginning, I would like to thank you much about this great post. Its very useful and helpful for anyone looking for tips. I like your writing style and I hope you will keep doing this good working.
    aws training in bangalore
    AWS Training
    Aws Certification in Chennai
    best aws training in bangalore
    Best AWS Training in Chennai
    AWS Training in Chennai

    ReplyDelete
  32. This comment has been removed by the author.

    ReplyDelete
  33. Wonderful bloggers like yourself who would positively reply encouraged me to be more open and engaging in commenting.So know it's helpful.
    Data Science training in Chennai
    Data science online training

    ReplyDelete
  34. Thank you for benefiting from time to focus on this kind of, I feel firmly about it and also really like comprehending far more with this particular subject matter. In case doable, when you get know-how, is it possible to thoughts modernizing your site together with far more details? It’s extremely useful to me.
    Data Science Training in Chennai
    Data Science course in anna nagar
    Data Science course in chennai
    Data science course in Bangalore
    Data Science course in marathahalli

    ReplyDelete
  35. A good blog for the people who really needs information about this. Good work keep it up.

    Guest posting sites
    Technology

    ReplyDelete
  36. Great post, informative and helpful post and you are obviously very knowledgeable in this field. Very useful and solid content. Thanks for sharing

    Data Science

    ReplyDelete
  37. Thanks for sharing this information. I really like your blog post very much. You have really shared a informative and interesting blog post with people..
    machine learning course malaysia

    ReplyDelete
  38. I have to search sites with relevant information on given topic and provide them to teacher our opinion and the article.
    data science course

    ReplyDelete

  39. This is an awesome blog. Really very informative and creative contents. This concept is a good way to enhance the knowledge. Thanks for sharing.
    ExcelR business analytics course

    ReplyDelete
  40. Wonderful thanks for sharing an amazing idea. keep it...

    Softgen Infotech is a leading training institute for all kind of the Best Python Training in Bangalore with real-time experienced trainers with 100% Placement Assistance.

    ReplyDelete
  41. Really awesome blog!!! I really enjoyed reading this article. Thanks for sharing valuable information.
    Data Science Course
    Data Science Course in Marathahalli

    ReplyDelete
  42. Your Website is very good, Your Website impressed us a lot, We have liked your website very much.
    We have also created a website of Android App that you can see it.

    http://damodapk.com/

    ReplyDelete
  43. Your Website is very good, Your Website impressed us a lot, We have liked your website very much.
    We have also created a website of Android App that you can see it.

    http://damodapk.com/

    ReplyDelete
  44. Your Website is very good, Your Website impressed us a lot, We have liked your website very much.
    We have also created a website of Android App that you can see it.

    http://infotodaypk.com/

    ReplyDelete
  45. Always so interesting to visit your site.What a great info, thank you for sharing. this will help me so much in my learning
    data science certification

    ReplyDelete
  46. This comment has been removed by the author.

    ReplyDelete
  47. This comment has been removed by the author.

    ReplyDelete
  48. This comment has been removed by the author.

    ReplyDelete
  49. Great post but I was wondering if you could write a little more on this subject? I’d be very thankful if you could elaborate a little bit further. Thanks in advance!I enjoyed to visiting your blog. You have done a really nice job.

    Data Science Training In Chennai

    Data Science Online Training In Chennai

    Data Science Training In Bangalore

    Data Science Training In Hyderabad

    Data Science Training In Coimbatore

    Data Science Training

    Data Science Online Training

    ReplyDelete
  50. This comment has been removed by the author.

    ReplyDelete
  51. Very interesting blog. Many blogs I see these days do not really provide anything that attracts others, but believe me the way you interact is literally awesome. I will instantly grab your rss feed to stay informed of any updates you make and as well take the advantage to share some latest information about

    CREDIT CARD HACK SOFTWARE which many are not yet informed, of the recent technology.

    Thank so much for the great job.

    ReplyDelete
  52. A good blog always comes-up with new and exciting information and while reading I have feel that this blog is really have all those quality that qualify a blog to be a one.
    data scientist course in hyderabad

    ReplyDelete
  53. Excellent work done by you once again here. This is just the reason why I’ve always liked your work. You have amazing writing skills and you display them in every article. Keep it going!
    data scientist training and placement

    ReplyDelete
  54. Login Your tradeatf Account To Read The Latest News About The Platform.s

    ReplyDelete
  55. The Original Forex Trading System: exness login Is The Original Forex Trading System. It Is 100% Automated And Provides An Easy-to-follow Trading System. You Get Access To Real-time Signals, Proven Methods, And A Money-back Guarantee.

    ReplyDelete
  56. Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. A debt of gratitude is in order for sharing. best institute for cloud computing in hyderabad

    ReplyDelete
  57. XM REVIEW If You Are A Beginner, Check Out Our Guide On How To Open An Account With XM. They Offer Copy Trading Where You Can Copy The Trades Of Successful Traders.

    ReplyDelete
  58. There is obviously a lot to know about this. I think you made some good points in Features also. Keep working, great job!
    artificial intelligence training in aurangabad

    ReplyDelete
  59. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. Great workk
    cyber security course malaysia

    ReplyDelete
  60. Really an awesome blog and informative content. Keep sharing more blogs with us. If you want to learn a data science course, follow the below link.
    Data Science Certification in Hyderabad

    ReplyDelete
  61. This post is very simple to read and appreciate without leaving any details out. Great work!
    business analytics training in hyderabad

    ReplyDelete
  62. Data Science is a booming field with ample job opportunities. Start your preparation today with 360DigiTMG and become a Data Scientist in the right way.

    Data Science Course in Jaipur

    ReplyDelete
  63. Data Scientist is the top job in the market, as it has promising career growth and high salary packages. Start your preparation with the best Data Science training Institute 360DigiTMG today and become a successful Data Scientist.
    Business Analytics Course in Jodhpur

    ReplyDelete
  64. Data Scientist is the top job in the market, as it has promising career growth and high salary packages. Start your preparation with the best Data Science training Institute 360DigiTMG today and become a successful Data Scientist.

    Best Data Science Training institute in Bangalore

    ReplyDelete
  65. Thanks for sharing the article!
    jewellery erp software
    Jewellery erp software

    ReplyDelete
  66. fantastic instruction JDBC and Selenium WebDriver together provide a potent combo for data-driven testing. It's simple to follow along thanks to your clear explanations and code samples. I appreciate you providing this useful information!

    Data Analytics Courses in India

    ReplyDelete
  67. Hello Blogger,
    This post is a valuable resource for Selenium WebDriver users looking to connect to a database. It provides a clear and detailed explanation of the process, along with code examples for connecting to a MySQL database. The step-by-step guide and prerequisites make it easy for readers to follow along and implement this functionality in their Selenium tests. Great job in simplifying a complex task!
    Data Analytics Courses in Nashik

    ReplyDelete
  68. Thank you so much for this detailed tutorial on connecting the database using selenium webdriver. It was very helpful. You explained every single point in such a conscise way, It was so easy for me to understand this topic.
    Visit - Data Analytics Courses in Delhi

    ReplyDelete
  69. The ability to connect to a database using Selenium opens up a world of possibilities for data-driven testing and automation.
    Digital marketing courses in illinois

    ReplyDelete
  70. Thank you for sharing amazing Tutorial on Connecting to DataBase using Selenium WebDriver.
    Investment banking training Programs

    ReplyDelete