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());
}
}
