SDET- QA Automation Techie

Full Stack QA Automation Testing Blog

  • Home
  • Training
    • Online
    • Self-Paced
  • Video Tutorials
  • Interview Skills
    • HR Interview Questions Videos
    • Domain Knowledge
  • Career Guidance
  • Home
  • Software Testing
    • Manual Testing Tutorials
    • Manual Testing Project
    • Manaul Testing FAQS
    • ISTQB
    • AGILE
  • Web Automation Testing
    • Java Programmng
    • Python Programmng
    • Selenium with Java
    • Selenium with Python
    • Robot Framework(Selenium with Python)
    • selenium with Cucumber
    • TestNG+IntelliJ
    • Mobile App Testing(Appium)
    • JMeter
  • API Automation Testing
    • Rest Assured API Testing (BDD)
    • Rest Assured API Testing (Java+ TestNG)
    • Robot Framework(Rest API Testing with Python)
    • Postman
    • SoapUI
    • API Testing(FAQ's)
  • SDET|DevOps
    • Continuos Integration
    • SDET Essentials
    • AWS For Testers
    • Docker
  • SQL
    • Oracle(SQL)
    • MySQL for Testers
    • NoSQL
  • Unix/Linux
    • UNIX TUTORIALS
    • Linux Shell Scripting
  • ETL Testing
    • ETL Data warehouse Tutorial
    • ETL Concepts Tools and Templates
    • ETL Testing FAQ's
    • ETL Testing Videos
  • Big Data Hadoop
  • Video Tutorials
  • ApachePOI Video Tutorials
  • Downloads
    • E-Books for Professionals
    • Resumes
  • Automation Essencials
    • Cloud Technologies
      • Docker For Testers
      • AWS For Testers
      • Sub Child Category 3
    • Java Collections
    • Selenium Locators
    • Frequently Asked Java Programs
    • Frequently Asked Python Programs
    • Protractor
    • Cypress Web Automation

How to Convert Database Results into JSON Files

 How to Convert Database Results into JSON Files   











Pre-Requisite:

Create Mock Table "constomersInfo" in "ClassicModels" Database and insert some data.

CREATE DATABASE ClassicModels;
use ClassicModels;

CREATE TABLE CustomerInfo

(BookName varchar(50),
PurchasedDate date,
Amount int(50),
Location varchar(50));

INSERT INTO CustomerInfo values("selenium",CURRENT_DATE(),350,'Africa');
INSERT INTO CustomerInfo values("Java",CURRENT_DATE(),200,'Africa');
INSERT INTO CustomerInfo values("Python",CURRENT_DATE(),250,'Asia');
INSERT INTO CustomerInfo values("Jmeter",CURRENT_DATE(),150,'Asia');
INSERT INTO CustomerInfo values("C#",CURRENT_DATE(),300,'Asia');


select * from CustomerInfo where purchasedDate=CURDATE() and Location ='Asia';

Step 1: Retrieve Results from Database using JDBC

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

public class ConvertDBResult_JavaObject_JsonFile{

     public static void main(String[] args) throws SQLException {
          // 1) Create a connection
          Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/classicmodels", "root", "root");

          // 2) Create statement/Query
          Statement stmt = con.createStatement();

          String s = "select * from customerinfo  limit 1";

          // 3,4) Execute statement/Query & Store data in resultset
          ResultSet rs = stmt.executeQuery(s);

          CustomerDetails cd=new CustomerDetails();
         
          while (rs.next()) {
              String bookname = rs.getString("BookName");
              String purchasedate = rs.getString("PurchasedDate");
              int amount = rs.getInt("Amount");
              String location = rs.getString("Location");
              System.out.println(bookname + "   " + purchasedate + "      " + amount+"    "+location);
          }

          // 5) close connection
          con.close();

          System.out.println("Query executed.....");
     }

}


Step 2: Converts Data base Results into Java Object (Plain Old Java Object)  public class CustomerDetails {
     String bookname;
     String purchasedate;
     int amount;
     String location;
    
     public String getBookname() {
          return bookname;
     }
     public void setBookname(String bookname) {
          this.bookname = bookname;
     }
     public String getPurchasedate() {
          return purchasedate;
     }
     public void setPurchasedate(String purchasedate) {
          this.purchasedate = purchasedate;
     }
     public int getAmount() {
          return amount;
     }
     public void setAmount(int amount) {
          this.amount = amount;
     }
     public String getLocation() {
          return location;
     }
     public void setLocation(String location) {
          this.location = location;
     }
}

Modify JDBC Program which will use POJO (Plain Old Java Object) Class to retrieve data from Java Object.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ConvertDBResult_JavaObject_JsonFile{

     public static void main(String[] args) throws SQLException {
          // 1) Create a connection
          Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/classicmodels", "root", "root");

          // 2) Create statement/Query
          Statement stmt = con.createStatement();

          String s = "select * from customerinfo  limit 1";

          // 3,4) Execute statement/Query & Store data in resultset
          ResultSet rs = stmt.executeQuery(s);

          CustomerDetails cd=new CustomerDetails();
         
          while (rs.next()) {
              String bookname = rs.getString("BookName");
              String purchasedate = rs.getString("PurchasedDate");
              int amount = rs.getInt("Amount");
              String location = rs.getString("Location");
             
              cd.setBookname(bookname);
              cd.setPurchasedate(purchasedate);
              cd.setAmount(amount);
              cd.setLocation(location);
          }

          // 5) close connection
          con.close();
    
          System.out.println(cd.getLocation());
     }
}

Step 3: Convert Java Objects into JSON Files
            Maven Dependencies: Jackson Core, Jackson Databind, Jackson annotations

Single Java Object à Single JSON File:

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.fasterxml.jackson.core.JsonGenerationException;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.ObjectMapper;

public class ConvertDBResult_JavaObject_JsonFile{

     public static void main(String[] args) throws SQLException, JsonGenerationException, JsonMappingException, IOException {
          // 1) Create a connection
          Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/classicmodels", "root", "root");

          // 2) Create statement/Query
          Statement stmt = con.createStatement();

          String s = "select * from customerinfo  limit 1";

          // 3,4) Execute statement/Query & Store data in resultset
          ResultSet rs = stmt.executeQuery(s);

          CustomerDetails cd=new CustomerDetails();
         
          while (rs.next()) {
              String bookname = rs.getString("BookName");
              String purchasedate = rs.getString("PurchasedDate");
              int amount = rs.getInt("Amount");
              String location = rs.getString("Location");
             
              cd.setBookname(bookname);
              cd.setPurchasedate(purchasedate);
              cd.setAmount(amount);
              cd.setLocation(location);
          }

          // 5) close connection
          con.close();
                  
          // Using Jakson API, Converting Java object into JSON File
          File jsonfile=new File("C:\\Users\\admin\\eclipse-workspace\\sdet\\custinfo.json");
         
          ObjectMapper om=new ObjectMapper();
          om.writeValue(jsonfile, cd);
         
          System.out.println("Done!");
         
     }

}



Multiple Java Objects à Multiple JSON Files:

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.fasterxml.jackson.core.JsonGenerationException;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.ObjectMapper;

public class ConvertDBResults_JavaObjects_JsonFiles {

     public static void main(String[] args) throws SQLException, JsonGenerationException, JsonMappingException, IOException {
          // 1) Create a connection
          Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/classicmodels", "root", "root");

          // 2) Create statement/Query
          Statement stmt = con.createStatement();

          String s = "select * from customerinfo";

          // 3,4) Execute statement/Query & Store data in resultset
          ResultSet rs = stmt.executeQuery(s);

          ArrayList ar=new ArrayList();
         
          while (rs.next()) {
              String bookname = rs.getString("BookName");
              String purchasedate = rs.getString("PurchasedDate");
              int amount = rs.getInt("Amount");
              String location = rs.getString("Location");
             
              CustomerDetails cd=new CustomerDetails();
             
              cd.setBookname(bookname);
              cd.setPurchasedate(purchasedate);
              cd.setAmount(amount);
              cd.setLocation(location);
             
              ar.add(cd); // Adding all the objects to arraylist
          }

          //Using Jakson API, Converting all Java objects into JSON Files
         
          for(int i=0;i
          {
              File jsonfile=new File("C:\\Users\\admin\\eclipse-workspace\\sdet\\custinfo"+i+".json");
             
              ObjectMapper om=new ObjectMapper();
              om.writeValue(jsonfile,ar.get(i));
          }
                  
          System.out.println("Done!");
    
          con.close();
         
     }

}


Multiple Java Objects à Single JSON (Along with JSON Formatting):

package sdet;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

import com.fasterxml.jackson.core.JsonGenerationException;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.google.gson.Gson;

public class ConvertDBResults_JavaObjects_SingleJsonFile {

     public static void main(String[] args) throws SQLException, JsonGenerationException, JsonMappingException, IOException {
          // 1) Create a connection
          Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/classicmodels", "root", "root");

          // 2) Create statement/Query
          Statement stmt = con.createStatement();

          String s = "select * from customerinfo";

          // 3,4) Execute statement/Query & Store data in resultset
          ResultSet rs = stmt.executeQuery(s);

          ArrayList ar=new ArrayList();
         
          while (rs.next()) {
              String bookname = rs.getString("BookName");
              String purchasedate = rs.getString("PurchasedDate");
              int amount = rs.getInt("Amount");
              String location = rs.getString("Location");
             
              CustomerDetails cd=new CustomerDetails();
             
              cd.setBookname(bookname);
              cd.setPurchasedate(purchasedate);
              cd.setAmount(amount);
              cd.setLocation(location);
             
              ar.add(cd); // Adding all the objects to arraylist
          }

          //Using Jakson API, Converting all Java objects into JSON Files
         
          JSONArray jsonr=new JSONArray(); //Required to add JSon string to Json array
         
          for(int i=0;i
          {
              //File jsonfile=new File("C:\\Users\\admin\\eclipse-workspace\\sdet\\custinfo"+i+".json");
              //ObjectMapper om=new ObjectMapper();
              //om.writeValue(jsonfile,ar.get(i));
             
              Gson g=new Gson();
              String JsonString=g.toJson(ar.get(i)); //Converts Java object into JSON String
             
              jsonr.add(JsonString); //Add JSon String to Json Array
          }
         
          JSONObject jo=new JSONObject();
          jo.put("data",jsonr);
         
          System.out.println(jo.toJSONString()); // added escape chars That's not right format
    
          String jsonFormattedString = jo.toJSONString().replace("\\\"", "\""); //removed escape chars
          System.out.println(jsonFormattedString); //still doubles quotes there. It's not exactly in Json format
         
          String finalJSONString=jsonFormattedString.replace("\"{","{").replace("}\"","}"); //Removes double quotes both the sides
          System.out.println(finalJSONString);
         
          System.out.println("Done!");
                  
          con.close();
         
     }

}


Step 4: Convert JSON to Java Object
import java.io.File;
import java.io.IOException;

import com.fasterxml.jackson.core.JsonParseException;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.ObjectMapper;

public class JSONtoJavaObject {

     public static void main(String[] args) throws JsonParseException, JsonMappingException, IOException {
         
          ObjectMapper om=new ObjectMapper();
         
          File jsonfile=new File("C:\\Users\\admin\\eclipse-workspace\\sdet\\custinfo.json");
          CustomerDetails cd=om.readValue(jsonfile, CustomerDetails.class);
         
          System.out.println(cd.getBookname());
          System.out.println(cd.getAmount());
         

     }

}


  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
Email ThisBlogThis!Share to TwitterShare to Facebook
Newer Post Older Post Home
popup

Popular Posts

  • How To Explain Project In Interview Freshers and Experienced
    “ Describe an important project you’ve worked on ” is one of the most common questions you can expect in an interview. The purpose of a...
  • API/Webservices Testing using RestAssured (Part 1)
    Rest Assured : Is an API designed for automating REST services/Rest API's Pre-Requisites Java Free videos: https://www.you...
  • MANUAL TESTING REAL TIME INTERVIEW QUESTIONS & ANSWERS
    1. How will you receive the project requirements? A. The finalized SRS will be placed in a project repository; we will access it fr...

Facebook Page

Pages

  • Home
  • Resumes
  • Job Websites India/UK/US
  • ISTQB
  • Selenium with Java
  • E-Books for Professionals
  • Manual Testing Tutorials
  • Agile Methodology
  • Manual Testing Projects

Live Traffic

YouTube


Blog Visitors

Copyright © SDET- QA Automation Techie | Powered by Blogger
Design by SDET | Blogger Theme by | Distributed By Gooyaabi Templates