Convert Tree Structure from database to JSON Object in java?

First of all the JSON you have provided is not a valid JSON. So that I have added an parent root node when generating the JSON.

If you had a root node defined in your dataset there will be small change to your structure as you cannot maintain parent_Id = child_id relationship as the current data set.So there will be a modification for the solution also.

First you need to map your data into some parent-child supported data type. I have created Node.java for that purpose. Introduced the addChild method to add children one by one.

import java.util.ArrayList;
import java.util.List;

public class Node
{
    private String nodeName;
    private java.util.List<Node> children = new ArrayList<Node>();

    public Node( String nodeName )
    {
        this.nodeName = nodeName;
    }

    public List<Node> getChildren()
    {
        return children;
    }

    public void setChildren( List<Node> children )
    {
        this.children = children;
    }

    public String getNodeName()
    {
        return nodeName;
    }

    public void setNodeName( String nodeName )
    {
        this.nodeName = nodeName;
    }

    public void addChild( Node node )
    {
        this.children.add( node );
    }
}

And for your original data type, I created Mapping.java

public class Mapping
{
    private int parentId;
    private int childId;
    private String ItemName;

    public Mapping( int parentId, int childId, String itemName )
    {
        this.parentId = parentId;
        this.childId = childId;
        ItemName = itemName;
    }

    public int getParentId()
    {
        return parentId;
    }

    public void setParentId( int parentId )
    {
        this.parentId = parentId;
    }

    public int getChildId()
    {
        return childId;
    }

    public void setChildId( int childId )
    {
        this.childId = childId;
    }

    public String getItemName()
    {
        return ItemName;
    }

    public void setItemName( String itemName )
    {
        ItemName = itemName;
    }
}

Now loading and data and populating the Node objects and getting the final Json is done here. I am using an object reference map here because we cannot guarantee the order of mappings in your database. Since children are assigned to the object reference of the parent, after completing the assignment, we will have our parent-child structure. The two loops are also used for the same reason. We need to ensure map is having all nodes before starting to build the structure.

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ParentChild
{
    public static void main( String[] args )
    {
        List<Mapping> list = new ArrayList<Mapping>();
        list.add( new Mapping( 1, 1, "Country" ) );
        list.add( new Mapping( 1, 2, "Australia" ) );
        list.add( new Mapping( 2, 3, "Victoria" ) );
        list.add( new Mapping( 2, 4, "Queensland" ) );
        list.add( new Mapping( 1, 5, "Canada" ) );
        list.add( new Mapping( 5, 6, "British Columbia" ) );
        list.add( new Mapping( 6, 7, "Vancouver" ) );
        list.add( new Mapping( 8, 8, "Songs" ) );
        list.add( new Mapping( 8, 9, "Song1" ) );
        list.add( new Mapping( 8, 10, "Song2" ) );
        list.add( new Mapping( 10, 11, "lyrics 1st" ) );
        list.add( new Mapping( 10, 12, "lyrics 2nd" ) );
        list.add( new Mapping( 13, 13, "Germany" ) );
        list.add( new Mapping( 14, 14, "England" ) );
        list.add( new Mapping( 14, 15, "London" ) );

        Map<Integer, Node> map = new HashMap<Integer, Node>();

        map.put( -1, new Node( "root" ) ); // give index -1 for the root

        for( Mapping mapping : list )  // keep a map of nodes by child id
        {
            map.put( mapping.getChildId(), new Node( mapping.getItemName() ) );
        }

        for( Mapping mapping : list )
        {
            if( mapping.getParentId() == mapping.getChildId() )
            {
                map.get( -1 ).addChild( map.get( mapping.getChildId() ) ); // add to the root
            }
            else
            {
                Node node = map.get( mapping.getParentId() );
                Node childNode = map.get( mapping.getChildId() );
                node.addChild( childNode ); // add to the relevant parent
            }
        }

        StringBuilder json = new StringBuilder();
        writeJson( map.get( -1 ), json ); // root node is enough
        System.out.println( json );
    }

    private static void writeJson( Node node, StringBuilder json )
    {
        if( node.getChildren().isEmpty() ) // no children. return just the node name
        {
            json.append( "\"" ).append( node.getNodeName() ).append( "\"" );
        }
        else
        {
            json.append( "{\"" ).append( node.getNodeName() ).append( "\": [" );

            List<Node> children = node.getChildren();
            for( int i = 0; i < children.size(); i++ )
            {
                Node child = children.get( i );
                writeJson( child, json ); // call recursively
                if( i != children.size() - 1 ) // skip , for the last child
                {
                    json.append( "," );
                }
            }
            json.append( "]}" );
        }
    }
}

I have used a recursive method to build the json.

The resulting JSON

{
   "root":[
      {
         "Country":[
            {
               "Australia":[
                  "Victoria",
                  "Queensland"
               ]
            },
            {
               "Canada":[
                  {
                     "British Columbia":[
                        "Vancouver"
                     ]
                  }
               ]
            }
         ]
      },
      {
         "Songs":[
            "Song1",
            {
               "Song2":[
                  "lyrics 1st",
                  "lyrics 2nd"
               ]
            }
         ]
      },
      "Germany",
      {
         "England":[
            "London"
         ]
      }
   ]
}

Hope it helps.