Dynamic Category SubCategory Tree dengan spring boot + mysql

Ada kalanya kita menemukan issue dimana kita dituntut membuat category dan subcategory secara dynamic, dan ini lumayan tricky jika blum mengetahui data structure. di case ini kita akan menggungakan binary tree untuk mengsolve this problem.

Requirement:

1. Text Editor or IDE
2. JDK8
3. MySQL or MariaDB

How to:
1. buat database di MySQL disini saya berinama test
2. buat table di MySQL

CREATE TABLE `category` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `parent_id` bigint(20) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `update_dated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `category_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `category` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4;

3. insert category example into table category

insert  into `category`(`id`,`parent_id`,`name`,`created_date`,`update_dated`) values (4,NULL,'Komputer','2020-07-26 07:51:15','2020-07-26 07:51:15'),(5,NULL,'Elektronik','2020-07-26 07:51:17','2020-07-26 07:51:17'),(6,NULL,'Fashion','2020-07-26 07:51:17','2020-07-26 07:51:17'),(8,4,'Laptop','2020-07-26 07:53:49','2020-07-26 07:53:49'),(9,4,'Dekstop','2020-07-26 07:53:49','2020-07-26 07:53:49'),(10,8,'Asus','2020-07-26 07:54:18','2020-07-26 07:54:18'),(11,8,'Acer','2020-07-26 07:54:18','2020-07-26 07:54:18'),(12,10,'Asus ROG XXX',NULL,NULL),(13,10,'Asus K46C',NULL,NULL),(14,11,'Acer J20',NULL,NULL),(15,6,'Pria',NULL,NULL),(16,6,'Wanita',NULL,NULL),(17,6,'Anak - Anak',NULL,NULL),(19,15,'Baju',NULL,NULL),(20,15,'Celana',NULL,NULL),(21,15,'Daleman',NULL,NULL),(22,15,'Sepatu',NULL,NULL),(23,15,'Sendal',NULL,NULL);

4. buat spring boot project di https://start.spring.io/

dan structure foldernya akan seperti ini.

├── java
│   └── com
│       └── fluex404
│           └── DynamicTree
│               ├── api
│               │   └── CategoryAPI.java
│               ├── controller
│               │   └── CategoryController.java
│               ├── DynamicTreeApplication.java
│               └── service
│                   └── CategoryService.java
└── resources
    ├── application.properties
    ├── static
    │   ├── css
    │   │   └── bstreeview.min.css
    │   └── js
    │       └── bstreeview.min.js
    └── templates
        └── index.html

5. config database di application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/test?zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.hibernate.ddl-auto=none

spring.jpa.generate-ddl=false
spring.jpa.hibernate.use-new-id-generator-mappings=false

#spring.jpa.show-sql=true

#fix 1054, SQLSTATE 42S22 Unknown column in 'field list'
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
spring.jpa.open-in-view=false

6. buat package baru service dan tambahkan CategoryService.java

package com.fluex404.DynamicTree.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.ui.ModelMap;

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

@Component
public class CategoryService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<ModelMap> getCategories(){
        List<ModelMap> list = new ArrayList<>();

        categoryTree(null, list);

        return list;
    }
    private void categoryTree(Long parentId, List<ModelMap> list){
        String sql = "";
        if(parentId == null)
            sql = "SELECT c.`id`, c.`parent_id`, c.`name` FROM `category` c WHERE c.`parent_id` IS NULL ";
        else
            sql = "SELECT c.`id`, c.`parent_id`, c.`name` FROM `category` c WHERE c.`parent_id` ="+parentId;

        jdbcTemplate.query(sql, rs -> {
            ModelMap m = new ModelMap();

//            m.put("id", rs.getLong("id"));
            m.put("text", rs.getString("name"));
            m.put("icon", "fa fa-list-alt");

            List<ModelMap> nodes = new ArrayList<>();
            categoryTree(rs.getLong("id"), nodes);

            m.put("nodes", nodes);

            list.add(m);
        });
    }
}

7. buat package api dan tambahkan CategoryAPI.java

package com.fluex404.DynamicTree.api;

import com.fluex404.DynamicTree.service.CategoryService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/api/category")
public class CategoryAPI {

    @Autowired
    private CategoryService service;

    @GetMapping
    public List<ModelMap> getCategories(){
        return service.getCategories();
    }
}

dan coba run spring boot project

dan coba check api dengan postman atau software http client lainnya. dan bisa kalian liat response jsonnya akan seperti ini.

8. sekarang buat frontendnya untuk mengconsume api ini disini saya akan mengconsume menggunakan ajax dari jQuery dan menggunakan tree list bootstrap plugin untuk menampilkan category secara tree.
9. buat folder css dan js di resources/static folder dan tambahkan liblary tree list bootstrap seperti css dan js nya. kamu bisa download liblarynya disini.

10. buat file index.html di dalam folder resources/templates

<!doctype html>
<html>
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootswatch/4.4.1/flatly/bootstrap.min.css">
    <link href="https://stackpath.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"
          integrity="sha384-wvfXpqpZZVQGK6TAh5PVlGOfQNHSoD2xbE+QkPxCAFlNEevoEH3Sl0sibVcOQVnN" crossorigin="anonymous">
    <link href="/css/bstreeview.min.css" rel="stylesheet">
    <link href="https://www.jqueryscript.net/css/jquerysctipttop.css" rel="stylesheet" type="text/css">
    <style>
        body { background: #fafafa; font-family: 'Roboto Condensed'; }
        .container { margin: 150px auto; }
    </style>
    <title>Bootstrap Tree List Plugin Example</title>
</head>

<body>
<div class="container">
    <div class="content">
        <div class="row">
            <div class="col-md-12">
                <h1>Dynamic Category SubCategory Tree Example</h1>
            </div>
        </div>
        <div class="col-md-4 pt-5">
            <div id="tree"></div>
        </div>
    </div>
</div>
</div>
<script src="https://code.jquery.com/jquery-3.4.1.min.js"
        integrity="sha256-CSXorXvZcTkaix6Yvo6HppcZGetbYMGWSFlBw8HfCJo=" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js"
        integrity="sha384-wfSDF2E50Y2D1uUdj0O3uMBJnjuUD4Ih7YwaYd1iqfktj0Uod8GCExl3Og8ifwB6"
        crossorigin="anonymous"></script>

<script src="js/bstreeview.min.js">
</script>
<script>
    $(document).ready(function(){
        $.ajax({
            url: 'http://localhost:8080/api/category',
            // data: {},
            dataType: 'json',
            success: function(data) {
                console.log(data);


                $('#tree').bstreeview({ data: JSON.stringify(data)});
            },
            error: function(err){
              console.log(err);
            },
            type: 'GET'
        });
    });

</script>
</body>
</html>

11. buat package controller dan tambahkan CategoryController.java

package com.fluex404.DynamicTree.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;

@Controller
public class CategoryController {
    @GetMapping("/")
    public String createIndex(){
        return "index";
    }
}

setelah semuanya selesai silahkan run spring boot project kamu dan masuk dan go to http://localhost:8080/

source code: https://github.com/fluex404/DynamicTree