introduction
In actual project development, tree structure data query is a very common requirement. For example, organizational structure, menu management, region selection and other scenarios all require processing of tree data. This article will explain in detail how to use MyBatis to implement the query of three-level tree data, from database design to specific code implementation, helping everyone master the core points of tree data processing.
Database Design
First, we need to design a suitable database table structure to store tree data. Here is a typical tree table structure:
CREATE TABLE `sys_area` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key ID', `parent_id` bigint(20) DEFAULT NULL COMMENT 'Parent ID', `name` varchar(50) NOT NULL COMMENT 'Region Name', `level` int(11) NOT NULL COMMENT 'Level(1:province 2:City 3:District and County)', `sort` int(11) DEFAULT 0 COMMENT 'Sorting number', `status` tinyint(4) DEFAULT 1 COMMENT 'state(0:Disabled 1:Enable)', `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time', `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time', PRIMARY KEY (`id`), KEY `idx_parent_id` (`parent_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Region Table';
Solid design
Next, we need to create the corresponding entity class. To support tree structure, we need to add a children attribute to store child nodes:
@Data
public class Area implements Serializable {
private static final long serialVersionUID = 1L;
// Primary key ID private Long id;
// Parent ID private Long parentId;
// Region name private String name;
// Level private Integer level;
// Sorting number private Integer sort;
// state private Integer status;
// Creation time private Date createTime;
// Update time private Date updateTime;
// List of child nodes private List<Area> children;
}
Mapper interface design
Create the AreaMapper interface and define the necessary query methods:
@Mapper
public interface AreaMapper {
/**
* Query all regional data
* @return Region List */
List<Area> selectAllAreas();
/**
* Query child region based on parent ID
* @param parentId ParentID
* @return Subregion List */
List<Area> selectAreasByParentId(Long parentId);
/**
* Query the region at the specified level
* @param level
* @return Region List */
List<Area> selectAreasByLevel(Integer level);
}
XML mapping file implementation
Create a file in the resources directory:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/dtd/">
<mapper namespace="">
<!-- Basic column -->
<sql >
id, parent_id, name, level, sort, status, create_time, update_time
</sql>
<!-- Query all regions -->
<select resultType="">
SELECT <include ref/> FROM sys_area
WHERE status = 1
ORDER BY sort ASC, id ASC
</select>
<!-- According to the parentIDQuery sub-regions -->
<select resultType="">
SELECT <include ref/> FROM sys_area
WHERE status = 1
AND parent_id = #{parentId}
ORDER BY sort ASC, id ASC
</select>
<!-- Query the region at the specified level -->
<select resultType="">
SELECT <include ref/> FROM sys_area
WHERE status = 1
AND level = #{level}
ORDER BY sort ASC, id ASC
</select>
</mapper>
Service layer implementation
Create a Service interface and its implementation class:
@Service
@Slf4j
public class AreaServiceImpl implements AreaService {
@Autowired
private AreaMapper areaMapper;
@Override
public List<Area> buildAreaTree() {
// Query all regional data List<Area> allAreas = ();
// Build a tree structure return buildTree(allAreas);
}
/**
* Build a tree structure
* @param areas Region list
* @return List of regions of tree structure
*/
private List<Area> buildTree(List<Area> areas) {
List<Area> trees = new ArrayList<>();
// Get all root nodes (province) ().filter(area -> () == 1) .forEach(province -> {
// Set up the province's child nodes (city) List<Area> cities = getChildren(areas, ());
(cities);
// Set up the city's child nodes (districts and counties) (city -> { List<Area> districts = getChildren(areas, ());
(districts);
});
(province);
});
return trees;
}
/**
* Get child nodes
* @param areas All regions list
* @param parentId ParentID
* @return child node list
*/
private List<Area> getChildren(List<Area> areas, Long parentId) {
return ().filter(area -> ((), parentId))
.collect(());
}
}
Controller layer implementation
Finally, create a Controller to handle the front-end request:
@RestController@RequestMapping("/api/areas")
public class AreaController {
@Autowired
private AreaService areaService;
/**
* Get regional tree data */
@GetMapping("/tree")
public ResponseResult<List<Area>> getAreaTree() {
try {
List<Area> trees = ();
return (trees);
} catch (Exception e) {
("Failed to obtain regional tree data", e);
return ("Failed to obtain regional tree data");
}
}
}
Performance optimization suggestions
- Cache Optimization
- Consider using Redis to cache tree data, because the regional data changes less frequently
- A reasonable cache expiration time can be set, such as 24 hours
@Service
public class AreaServiceImpl implements AreaService {
@Autowired
private RedisTemplate<String, List<Area>> redisTemplate;
private static final String AREA_TREE_KEY = "AREA:TREE";
private static final long CACHE_TIMEOUT = 24;
// Hour @Override
public List<Area> buildAreaTree() {
// Get it from the cache first List<Area> cacheTree = ().get(AREA_TREE_KEY);
if (cacheTree != null) {
return cacheTree;
}
// Cache misses, query the database and build the tree List<Area> trees = buildTreeFromDb();
// Put it in cache ().set(AREA_TREE_KEY, trees, CACHE_TIMEOUT, ); return trees;
}
}
2. SQL Optimization
- Add index appropriately: parent_id, level, status and other fields
- Consider using batch queries instead of loop queries
3. Memory optimization
- Pay attention to closing it in time when using the Stream API
- Set the initial capacity of the collection reasonably
- Release object references that are no longer used in time
Summarize
Through the explanation of this article, we have implemented a complete three-level tree data query function. Key points include:
- Reasonable database table design, including necessary fields and indexes
- Clear solid design, supporting tree structure
- Writing MyBatis mapping file to implement basic data query
- Implementation of tree construction algorithm of service layer
- Performance optimization and cache usage
This is the article about the example code for MyBatis implementing third-level tree query. For more related contents of MyBatis third-level tree query, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!