Java Database Connectivity

  • This is very important for report testing or if there are more data on webpage and we need to compare that data with database data.

  • The report cannot be tested with excel sheet as there are many combinations for data also sorting feature for webpages. If we want to compare with excel sheet we need to maintain lots of excel sheets. That is very difficult.

  • What generally QA person who is working on a report or any table testing is.

  • Ask a developer to give query which he has used for generating reports for various combinations.

  • Uses same query for testing results on a webpage, for this we need to know how to fetch data from database through Java.

  • Below are some basic steps we need to perform.

  • Let’s say we have table employee. Create this table for learning this concept.

    CREATE TABLE employee (
    `ename` VARCHAR(45) NOT NULL,
    `ephone` VARCHAR(45) NOT NULL, PRIMARY KEY (`eid`)
  • Insert some data for testing using the below given query:
    insert into employee values (1,’javabykiran’,’8888809416’);
    insert into employee values (2,’javabykiran’,’8888809416’);

  • sql insert command example

  • List all rows for the specified columns
    mysql> SELECT name, price FROM products;

  • Start writing code using main method because at a later stage this code can be used for comparing the actual and expected results.

  • Data which we are fetching from the database will give the actual and expected result while the data available on the webpage is actual data.

  • First, we need to add the jar file for MySQL driver in our project. We download it from the MySQL website as shown in the below image.

  • mysql connector jar

    mysql connector jar file

  • Once we download it, we will add it into our project as shown in the above image. Project structure will be as shown in the below image:

  • mysql connector jar file

  • This program prints data from the database.

  • This result set data needs to be converted into array list so that we can compare it with web table data.

  • This proves very helpful in testing data in bulk.

  • Now we will write complete example for getting arraylist from one method and then iterating it over all elements.

  • Creating and Deleting a Database:

    • mysql> DROP DATABASE southwind;

    • mysql> CREATE DATABASE IF NOT EXISTS southwind;

    • mysql> DROP DATABASE IF EXISTS southwind;

  • Show Create Database:
    mysql> SHOW CREATE DATABASE southwind \G

  • Creating and Deleting a Table:

    • Show the current (default) database
      mysql> SELECT DATABASE();

    • Show all the tables in the current database
      mysql> SHOW TABLES;

  • Create the table “products”:

    mysql> CREATE TABLE IF NOT EXISTS products (
        productCode CHAR(3) NOT NULL DEFAULT ‘’,
        name VARCHAR(30) NOT NULL DEFAULT ‘’, 
        price DECIMAL(7,2) NOT NULL DEFAULT 99999.99,
        PRIMARY KEY (productID)

  • Show all tables to confirm that “products” table has been created:
    mysql> SHOW TABLES;

  • Describe the fields (columns) of the “products” table:
    mysql> DESCRIBE products;

  • Show CREATE TABLE statement used by MySQL to create this table:
    mysql> SHOW CREATE TABLE products \G

  • Inserting Rows
    Insert a row with all the column values:

    mysql> INSERT INTO products VALUES (1001, ‘PEN’, ‘Pen Red’, 5000, 1.23);

  • Insert multiple rows in one command
    Inserting NULL to the auto_increment column results in max_value + 1:
    mysql> INSERT INTO products VALUES (NULL, ‘PEN’, ‘Pen Blue’, 8000, 1.25), (NULL, ‘PEN’, ‘Pen Black’, 2000, 1.25);

  • Insert value to selected columns
    Missing value for the auto_increment column also results in max_value + 1:
    mysql> INSERT INTO products (productCode, name, quantity, price)
    VALUES (‘PEC’, ‘Pencil 2B’, 10000, 0.48), (‘PEC’, ‘Pencil 2H’, 8000, 0.49);

  • Missing columns get their default values:
    mysql> INSERT INTO products (productCode, name) VALUES (‘PEC’, ‘Pencil HB’);

  • 2nd column (productCode) is defined to be NOT NULL:
    mysql> INSERT INTO products values (NULL, NULL, NULL, NULL, NULL);

  • show table:
    mysql> SELECT * FROM products;

  • Remove the specific row:
    mysql> DELETE FROM products WHERE productID = 1006;