How to fetch value from database which is in json format using spring?




Asked on November 07, 2018
Hello,
I have to fetch particular value based on condition from database which is in json format. I have written query as follows:

public String getChildLocation(String pk_location_id){
ObjectMapper mapper = new ObjectMapper(); 
JsonNode node = mapper.createObjectNode();
ArrayNode objNode = mapper.createArrayNode();
try {
Query query = em.createQuery("Select js.tree_node FROM JsonTreeStructure js where js.id=:id").setParameter("id",Long.parseLong(pk_location_id));
String result = query.getSingleResult().toString();
System.out.println("result:"+result);

node =mapper.readTree(result);  
objNode = (ArrayNode) node.path("children");
return "objNode"+objNode;
}catch(Exception e){
System.out.println("EXCeption:"+e.getMessage());
return e.getMessage();
}
//return "....";
}

In url if I pass id as 1 (http://localhost:8088/microservice/getChildLocation/1)
I am getting following result:
objNode[{"text":"India","value":2,"disabled":true,"children":[]},{"text":"Exceego","value":3,"children":[{"text":"hubli1","value":4,"children":[]}]}]

But I want to fetch the children of value 3
(http://localhost:8088/microservice/getChildLocation/3), if I pass 3 in the path I am getting exception as follows :
No entity found for query.

Can anyone please provide solution for this.

Thanks & Regards
Shilpa Kulkarni



Replied on November 07, 2018
Instead of passing id , I want to pass tree_node value in the following query :
Query query = em.createQuery("Select js.tree_node FROM JsonTreeStructure js where js.id=:id").setParameter("id",Long.parseLong(pk_location_id));

Because the domain is as follows:

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "jsonTree")
public class JsonTreeStructure {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@Column(name="location_tree",columnDefinition="LONGTEXT")
private String tree_node;

public Long getId() {
return id;
}


public void setId(Long id) {
this.id = id;
}


public String getTree_node() {
return tree_node;
}


public void setTree_node(String tree_node) {
this.tree_node = tree_node;
}

}


So, in the query I want to pass the tree_node value as 3. How to write query for that?


Write Answer










©2024 concretepage.com | Privacy Policy | Contact Us