java-树形结构数据表查询
该文采用SpringBoot框架进行测试,测试树形结构数据查询,可以提供些许学习参考。
实验结果
先看实验结果,如果符合预期,可以参考这篇文章。
实验结果:
- 数据库:
- 跑出的结果
- 查询全部的
- 查询某一个类型id的子目录的
- 查询全部的
实验步骤:
导入数据表
DROP TABLE IF EXISTS `t_objecttype`;
CREATE TABLE `t_objecttype` (
`object_type_num` int(11) NOT NULL AUTO_INCREMENT,
`object_type_id` int(11) NOT NULL,
`object_type_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`object_father_type_id` int(11) NULL DEFAULT 0,
PRIMARY KEY (`object_type_num`, `object_type_id`) USING BTREE,
INDEX `file_type_id`(`object_type_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 36 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t_objecttype
-- ----------------------------
INSERT INTO `t_objecttype` VALUES (31, 10000, '服装', 0);
INSERT INTO `t_objecttype` VALUES (32, 10001, '布料', 10000);
INSERT INTO `t_objecttype` VALUES (33, 20000, '电脑', 0);
INSERT INTO `t_objecttype` VALUES (34, 20001, '连想', 20000);
INSERT INTO `t_objecttype` VALUES (35, 20002, '平果', 20000);
SET FOREIGN_KEY_CHECKS = 1;
实体类
@Data
@ToString
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class TObjecttype {
private static final long serialVersionUID = 1L;
@TableId(value = "object_type_num",type = IdType.AUTO)
private Integer objectTypeNum;
private Integer objectTypeId;
private String objectTypeName;
private Integer objectFatherTypeId;
@TableField(exist = false)
private List<TObjecttype> childNode= new ArrayList<>();
@TableField(exist = false)
private List<TObjecttype> fatherNode = new ArrayList<>();
}
Mapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxx.mapper.TObjectTypeMapper">
<resultMap id="BaseResultMap" type="com.xxx.entity.TObjecttype">
<result column="object_type_num" property="objectTypeNum"/>
<result column="object_type_id" property="objectTypeId"/>
<result column="object_type_name" property="objectTypeName"/>
<result column="object_father_type_id" property="objectFatherTypeId"/>
</resultMap>
<resultMap id="ObjectNodeTreeResult" extends="BaseResultMap" type="com.xxx.entity.TObjecttype">
<collection property="childNode" column="object_type_id" ofType="com.gdpi.entity.TObjecttype"
javaType="java.util.ArrayList" select="nextNoteTree">
</collection>
</resultMap>
<sql id="Base_Colum_List">
object_type_num,
object_type_id,
object_type_name,
object_father_type_id
</sql>
<select id="nextNoteTree" resultMap="ObjectNodeTreeResult" parameterType="com.xxx.entity.TObjecttype">
select
<include refid="Base_Colum_List"></include>
from t_objecttype
where object_father_type_id = #{object_type_id}
</select>
<select id="noteTree" resultMap="ObjectNodeTreeResult">
select
<include refid="Base_Colum_List"></include>
from t_objecttype
where object_father_type_id = "0"
</select>
</mapper>
上述代码解释:
- resultMap: 映射实体类(column是数据库字段,property是实体类字段),id取名,后边select的resultMap就是这个名字,extends继承某个resultMap映射的结果会先映射到父类resultMap,然后在是子类的resultMap,降低了耦合度提高了resultMap复用性。
- select:内附查询语句,id为Mapper中的某方法,方法必须和Mapper中的方法一致,resultMap查询的结果映射位置,上边resultMap的id
- sql:将sql语句抽象出去,这样可以复用
- collection (上述代码的核心):
- property:实体类字段
- column:数据库字段(注意这里的字段,因为后边有个select查询,这个字段还会进行复用,在 #{object_type_id}还会用到,形成一个递归查询,结束条件为查询结果为空)
- ofType :指定集合是什么实体类类型
- javaType:用于指定整个集合或关联对象的 Java 类型
- select:再次查询,里边的值是某个写好的select 的id,然后他会将你刚刚查询到的字段再次丢进去查询,形成递归查询,结束条件是数据为空
Mapper
public interface TObjectTypeMapper extends BaseMapper<TObjecttype> {
public List<TObjecttype> noteTree();
// 获取树形结构数据
//提供一个objectid 然后得到该id下所有的子目录
public List<TObjecttype> nextNoteTree(Map map);
}
ServiceAndServiceImpl
public interface TObjectTypeService {
List allObjectType(Map map);
}
@Service
public class TObjectTypeImpl extends ServiceImpl<TObjectTypeMapper, TObjecttype> implements TObjectTypeService {
@Autowired
TObjectTypeMapper tObjectTypeMapper;
@Override
public List allObjectType(Map map) {
QueryWrapper<TObjecttype> objectQueryWrapper = new QueryWrapper<>();
objectQueryWrapper.eq("object_type_id",10000);
return this.list(objectQueryWrapper);
}
}
测试
@Autowired
TObjectTypeMapper tObjectTypeMapper;
@Autowired
TObjectTypeService tObjectTypeService;
@Test
void testAllObjectType(){
List list = tObjectTypeMapper.noteTree();
System.out.println(list);
}
@Test
void testAllObjectType2(){
Map<Object, Object> map = new HashMap<>();
map.put("object_type_id",20000);
List list = tObjectTypeMapper.nextNoteTree(map);
System.out.println(list);
//打印结果为
//[TObjecttype(objectTypeNum=34, objectTypeId=20001, objectTypeName=连想, objectFatherTypeId=20000, childNode=[], fatherNode=[]), TObjecttype(objectTypeNum=35, objectTypeId=20002, objectTypeName=平果, objectFatherTypeId=20000, childNode=[], fatherNode=[])]
}
注意:
- 这里是mybatisplus,在配置文件中是mybatisplus而不是mybatis
application.yml配置类中
mybatis-plus:
configuration:
map-underscore-to-camel-case: true
# log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
type-aliases-package: com.xxx.entity
mapper-locations: "classpath:/mapper/*.xml"
- 根据子类查询父类只需要微调调换下位置
<resultMap id="FileNodeTreeResult2" extends="BaseResultMap" type="com.xxx.entity.TFiletype">
<collection property="fatherNode" column="file_father_type_id" ofType="com.xxx.entity.TFiletype"
javaType="java.util.ArrayList" select="nextFatherNoteTree">
</collection>
</resultMap>
<select id="nextFatherNoteTree" resultMap="FileNodeTreeResult2" parameterType="com.xxx.TFiletype">
select
<include refid="Base_Colum_List"></include>
from t_filetype
where file_type_id = #{file_type_father_id}
</select>