Posted in Spring Boot, Spring JPA

Google Organization Chart Using Spring

Organization Chart

Here I am going to build google organization chart using spring boot framework.

Organization chart can be used to display hierarchical data of an organization, for example, you can display the employees and their managers in hierarchical order.

You can also find the organization hierarchy in your organization also. For example, you can see your manager, your manager’s manager and so on. Probably you can find this hierarchy in the MS Teams or MS Outlook.

Prerequisites

Java 19, Spring Boot 3.1.3/3.1.5, Maven 3.8.5, MySQL 8.1.0, Google Org Chart API

Project Setup

The following pom.xml file can be used for your maven based project:

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.roytuts</groupId>
	<artifactId>spring-google-org-chart</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>19</maven.compiler.source>
		<maven.compiler.target>19</maven.compiler.target>
	</properties>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>3.1.5</version>
	</parent>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>com.mysql</groupId>
			<artifactId>mysql-connector-j</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-thymeleaf</artifactId>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

MySQL Table And Data

The following SQL content is required for this example:

CREATE DATABASE IF NOT EXISTS `roytuts`;
USE `roytuts`;

CREATE TABLE IF NOT EXISTS `emp` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `designation` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `manager` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `emp` (`id`, `name`, `designation`, `manager`) VALUES
	(100, 'Steven', 'CEO', ''),
	(101, 'Neena', 'President', 'Steven'),
	(102, 'Lex', 'Vice President', 'Neena'),
	(103, 'Alexander', 'Senior Manager', 'Lex'),
	(104, 'Bruce', 'Manager', 'Alexander'),
	(105, 'David', 'Manager', 'Alexander'),
	(106, 'Diana', 'Associate', 'Bruce'),
	(107, 'Nancy', 'Associate', 'David'),
	(108, 'Daniel', 'Associate', 'Bruce'),
	(109, 'John', 'Senior Manager', 'Lex'),
	(110, 'Jose', 'Manager', 'John'),
	(111, 'Luis', 'Associate', 'Jose'),
	(112, 'Den', 'Trainee', 'Luis');

Application Config

The following application configuration is written into the application.properties file under src/main/resources folder.

spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost/roytuts
spring.datasource.username=root
spring.datasource.password=root

I have put the database settings as per the spring standard naming conventions, so I do not need to create any datasource instance manually and spring boot will take care of this thing using the auto-configuration feature.

MySQL Table

The table structure and sample data are given below:

CREATE DATABASE IF NOT EXISTS `roytuts`;
USE `roytuts`;

CREATE TABLE IF NOT EXISTS `emp` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `designation` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `manager` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `emp` (`id`, `name`, `designation`, `manager`) VALUES
	(100, 'Steven', 'CEO', ''),
	(101, 'Neena', 'President', 'Steven'),
	(102, 'Lex', 'Vice President', 'Neena'),
	(103, 'Alexander', 'Senior Manager', 'Lex'),
	(104, 'Bruce', 'Manager', 'Alexander'),
	(105, 'David', 'Manager', 'Alexander'),
	(106, 'Diana', 'Associate', 'Bruce'),
	(107, 'Nancy', 'Associate', 'David'),
	(108, 'Daniel', 'Associate', 'Bruce'),
	(109, 'John', 'Senior Manager', 'Lex'),
	(110, 'Jose', 'Manager', 'John'),
	(111, 'Luis', 'Associate', 'Jose'),
	(112, 'Den', 'Trainee', 'Luis');

Entity Class

The following entity class will map the table in database.

@Entity
@Table(name = "emp")
public class Employee {

	@Id
	@Column
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;

	@Column
	private String name;

	@Column
	private String designation;

	@Column
	private String manager;

        //getters and setters
}

DTO Class

The DTO class that corresponds to the above entity class:

public class EmpDto {

	private Integer id;

	private String name;

	private String designation;

	private String manager;

        //getters and setters
}

The DTO class is required to handle request and response for the end users or clients. The entity class should not be actually used for request and response structures in the controller class. The entity class should be hidden from the client interaction code.

Entity and DTO Converter

The following class is required to convert entity class to DTO class:

public final class EmpDtoEntityConverter {

	public static EmpDto getEmpDto(Employee employee) {
		return new EmpDto(employee.getId(), employee.getName(), employee.getDesignation(), employee.getManager());
	}

	public static List<EmpDto> getEmpDtoList(List<Employee> employees) {
		return employees.stream().map(e -> getEmpDto(e)).collect(Collectors.toList());
	}

}

JPA Repository

Spring Data JPA API can be used to get advantage of its built-in methods to query database using minimal code written.

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {

}

Service Class

The service class generally used to process business logic for the application.

@Service
public class EmployeeService {

	@Autowired
	private EmployeeRepository employeeRepository;

	public List<EmpDto> getEmpList() {
		List<Employee> employees = employeeRepository.findAll();

		return EmpDtoEntityConverter.getEmpDtoList(employees);
	}

}

Controller Class

The controller class will handle request and response for the clients:

@Controller
public class EmployeeController {

	@Autowired
	private EmployeeService employeeService;

	@GetMapping("/orgChart")
	public String orgChartData(Model model) {
		List<EmpDto> empDtos = employeeService.getEmpList();

		model.addAttribute("orgChartDataList", empDtos);

		return "index";
	}

}

Spring Boot Main Class

The spring boot main class will deploy the application into the embedded Tomcat server.

@SpringBootApplication
@EntityScan(basePackages = "com.roytuts.spring.google.orgchart.entity")
@EnableJpaRepositories(basePackages = "com.roytuts.spring.google.orgchart.repository")
public class App {

	public static void main(String[] args) {
		SpringApplication.run(App.class, args);
	}

}

Front End Code

I am using Thymeleaf from spring boot framework to display the org chart on User Interface. The index.html file is kept under src/main/resources/templates folder.

<html xmlns:th="http://www.thymeleaf.org">

<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
	<title>Google Org Chart using Spring</title>
	<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
	<script type="text/javascript" th:inline="javascript">
		//Load data from server
		var chartData = /*[[${orgChartDataList}]]*/'';

		//Load chart package
		google.charts.load('current', {'packages': ['orgchart']});

		// Set a callback to run when the Google Visualization API is loaded.
		google.charts.setOnLoadCallback(drawChart);

		// Callback that creates and populates a data table,
		// instantiates the chart, passes in the data and
		// draws it.    
		function drawChart() {
			// Create the data table.        
			var data = new google.visualization.DataTable();
			data.addColumn('string', 'Name');
			data.addColumn('string', 'Manager');
			data.addColumn('string', 'ToolTip');

			/*for(i = 0; i < chartData.length; i++) {
				data.addRow([ chartData[i].name, chartData[i].manager, chartData[i].designation ]);
			}*/

			//Or

			chartData.forEach(c => data.addRow([ c.name, c.manager, c.designation ]));

			// Instantiate and draw the chart
			var chart = new google.visualization.OrgChart(document.getElementById('chart_div'));
			chart.draw(data, {'allowHtml':true});
		}
	</script>
</head>

<body>
	<div style="width: 600px;">
		<div id="chart_div"></div>
	</div>
</body>

</html>

The package ‘orgchart’ is loaded for org chart.

google.charts.load('current', {packages: ['orgchart']});

The visualization’s class name is google.visualization.OrgChart.

var visualization = new google.visualization.OrgChart(container);

A table with three string columns, where each row represents a node in the orgchart. Here are the three columns:

  • Column 0 – The node ID. It should be unique among all nodes, and can include any characters, including spaces. This is shown on the node. You can specify a formatted value to show on the chart instead, but the unformatted value is still used as the ID.
  • Column 1 – [optional] The ID of the parent node. This should be the unformatted value from column 0 of another row. Leave unspecified for a root node.
  • Column 2 – [optional] Tool-tip text to show, when a user hovers over this node.
  • Each node can have zero or one parent node, and zero or more child nodes.

Testing the Organization Chart

Accessing the URL http://localhost:8080/orgChart will display the organization chart as shown below:

google org chart

Source Code

Download

Leave a Reply

Your email address will not be published. Required fields are marked *