Home  >  Forum  >  Spring
Post New Query

How to fetch child locations and sub child locations by parent location?



Asked on June 21, 2018
Hello,

I want to fetch child and sub child locations by parent location.

For example, I have location structure as follows:

      

     -HTL

          -India

             -Karnataka

                -Banglore

                   Jaynagar

             -Maharashtra

                 Puna


Example 1

I want to fetch child locations of India – means it should fetch sub children of its children.

It should fetch Karnataka, Maharashtra with their children.

Example 2

If I want to fetch children of Karnataka location means it should fetch banglore and also jaynagar.

In my database table, I have one field has_children which will be set to 1 if it has child location. If location does not have any child location then the has_children will be set to 0.

I have written the following query to fetch the child locations:

@Override

       public List<Location> getChildLocations(Long pk_location_id) {

             Query query = em.createQuery("SELECT loc FROM Location loc where loc.deleted=0 and " + "loc.parent_location_id="+pk_location_id);

             return (List<Location>) query.getResultList();

       }

But for this query I am getting only the main children of the location. For example, if I passed the pk_location_id of India, then I am getting only the children of India which are Karnataka and Maharashtra. But I want to fetch the sub children of these locations also.

If I passed one location id means it should check the has_children field of the children location also if that field is set to 1 means again it should fetch the children of those sub location also and so on.


My DB table structure is as follows:

CREATE TABLE `location` (

  `pk_location_id` bigint(20) NOT NULL,

  `created_date` datetime DEFAULT NULL,

  `deleted` int(11) DEFAULT NULL,

  `has_children` bit(1) DEFAULT NULL,

  `location_city` varchar(50) DEFAULT NULL,

  `location_country` varchar(50) DEFAULT NULL,

  `location_description` varchar(255) DEFAULT NULL,

  `location_fax` varchar(50) DEFAULT NULL,

  `location_phone` varchar(20) DEFAULT NULL,

  `location_postal_code` varchar(20) DEFAULT NULL,

  `location_roles_id` bigint(20) DEFAULT NULL,

  `location_state` varchar(50) DEFAULT NULL,

  `location_street_address` varchar(50) DEFAULT NULL,

  `locationcode` varchar(20) DEFAULT NULL,

  `locationname` varchar(50) NOT NULL,

  `locationpath` varchar(100) DEFAULT NULL,

  `modified_date` datetime DEFAULT NULL,

  `parent_location_id` bigint(20) DEFAULT NULL,

  `tenant_id` varchar(50) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;



--

-- Dumping data for table `location`

--


INSERT INTO `location` (`pk_location_id`, `created_date`, `deleted`, `has_children`, `location_city`, `location_country`, `location_description`, `location_fax`, `location_phone`, `location_postal_code`, `location_roles_id`, `location_state`, `location_street_address`, `locationcode`, `locationname`, `locationpath`, `modified_date`, `parent_location_id`, `tenant_id`) VALUES

(1, NULL, 0, b'1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '001', 'HTL', NULL, NULL, 0, NULL),

(2, NULL, 0, b'1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'India', NULL, NULL, 1, NULL),

(3, NULL, 0, b'1', NULL, NULL, NULL, '8765', NULL, NULL, NULL, NULL, NULL, '4444', 'Karnataka', NULL, NULL, 2, NULL),

(4, NULL, 0, b'1', NULL, NULL, NULL, '876', '9876543211', NULL, NULL, NULL, NULL, 'MA112', 'Maharashtra', NULL, NULL, 2, NULL),

(5, NULL, 0, b'1', NULL, NULL, NULL, '3333', '444444444', NULL, NULL, NULL, NULL, 'ba2222', 'banglore', NULL, NULL, 3, NULL),

(6, NULL, 0, b'0', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'ja111', 'jay nagar', '/banglore/Karnataka/India/HTL', NULL, 5, NULL),

(7, NULL, 0, b'0', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'pu111', 'puna', '', NULL, 4, NULL);


How to implement this using spring boot?


Thanks & Regards,

Shilpa Kulkarni







Replied on June 21, 2018
Your search is iterative. You need to call multiple query. 
So you should create stored procedure in database and call it using Spring.


Write Answer







©2019 concretepage.com | Privacy Policy | Contact Us