gitsilence 的个人博客

数据库备份文件

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `newslabel`
-- ----------------------------
DROP TABLE IF EXISTS `newslabel`;
CREATE TABLE `newslabel` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `pid` int(5) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of newslabel
-- ----------------------------
INSERT INTO `newslabel` VALUES ('1', '体育新闻', '0');
INSERT INTO `newslabel` VALUES ('2', '娱乐新闻', '0');
INSERT INTO `newslabel` VALUES ('3', 'NBA', '1');
INSERT INTO `newslabel` VALUES ('4', 'CBA', '1');
INSERT INTO `newslabel` VALUES ('5', '火箭', '3');
INSERT INTO `newslabel` VALUES ('6', '湖人', '3');
INSERT INTO `newslabel` VALUES ('7', '青岛金星', '4');
INSERT INTO `newslabel` VALUES ('8', '浙江广厦', '4');
INSERT INTO `newslabel` VALUES ('9', '港台明星', '2');
INSERT INTO `newslabel` VALUES ('10', '内地影视', '2');

父栏目-> 子栏目 -> 孙栏目 -> ...

数据库查询思路:

首先查询父栏目 例如: 体育新闻,

select * from newslabel where id = 1

找到体育新闻, 然后用体育新闻的id查询

select * from newslabel where pid = #{id}

取结果集的id再次作为条件查询

select * from neswslabel where pid = #{id}

反复这样, 直到再也不到结果 结束

创建maven项目

• pom依赖

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.6</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.47</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/log4j/log4j -->
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
    </dependency>


  </dependencies>

• 数据库连接配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--注册属性文件-->
    <properties resource="jdbc.properties"/>
    <!--定义别名-->
    <typeAliases>
        <!--给指定类型定义别名-->
        <!--<typeAlias type="com.abc.beans.Student" alias="Student"/>-->
        <!--给指定包中所有实体类指定别名，别名默认就是该类的简单类名-->
        <package name="com.abc.beans"/>
    </typeAliases>

    <!--可以配置多个environment-->
    <environments default="mySql">
        <environment id="mySql">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
        <!--<environment id="Orcale">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql:///test"/>
                <property name="username" value="root"/>
                <property name="password" value="12345678"/>
            </dataSource>
        </environment>-->
    </environments>
    <!--注册映射文件-->
    <mappers>
        <!--resource配置映射文件-->
        <!--<mapper resource="com/abc/dao/IStudentDao.xml"/>-->
        <!--url可以注册本地磁盘上的映射文件（不在项目路径下的文件）-->
       <!-- <mapper url="file:///e:/mapper.xml"/>-->

        <!--
        使用下面的注册方式需要满足以下三点要求：
	        1) 映射文件要与Dao接口在同一个包下
	        2) 映射文件名要与Dao接口的简单类名相同
	        3) 映射文件的<mapper/>标签的namespace属性值为Dao接口的全限定性类名
        满足以上三个条件，那么这里的class属性值就可以填写Dao接口的全限定性类名
        -->
        <!--<mapper class="com.abc.dao.IStudentDao"/>-->

        <!--
            使用下面的注册方式需要满足以下四点要求：
	        1) 映射文件要与Dao接口在同一个包下
	        2) 映射文件名要与Dao接口的简单类名相同
	        3) 映射文件的<mapper/>标签的namespace属性值为Dao接口的全限定性类名
	        4) 使用动态Mapper
            满足以上四个条件，那么这里的name属性值就可以填写Dao接口所在的包名
        -->
        <package name="com.abc.dao"/>

    </mappers>
</configuration>

• 日志显示配置文件log4j.properties

##define an appender named console
log4j.appender.console=org.apache.log4j.ConsoleAppender
#The Target value is System.out or System.err
log4j.appender.console.Target=System.out
#set the layout type of the apperder
log4j.appender.console.layout=org.apache.log4j.PatternLayout
#set the layout format pattern
log4j.appender.console.layout.ConversionPattern=[%-5p] %m%n

##define a logger
#log4j.rootLogger=TRACE,console
log4j.logger.com.abc.dao.INewslabelDao=debug,console

• 实体类Newslabel.java

package com.abc.beans;

import jdk.nashorn.internal.objects.annotations.Setter;

import java.util.Set;

/**
 * company: www.abc.com
 * Author: Administrator
 * Create Data: 2019/9/25 0025
 */

//以一方为主，关联属性定义到一方
    //作为一方
public class Newslabel {
    private Integer id;
    private String name;

    //关联属性
    private Set<Newslabel> children;

    public Newslabel() {
    }

    public Newslabel(String name, Set<Newslabel> children) {
        this.name = name;
        this.children = children;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Set<Newslabel> getChildren() {
        return children;
    }

    public void setChildren(Set<Newslabel> children) {
        this.children = children;
    }

    @Override
    public String toString() {
        return "Newslabel{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", children=" + children +
                '}';
    }
}

• 查询的接口INewslabelDao.java

package com.abc.dao;

import com.abc.beans.Newslabel;

/**
 * company: www.abc.com
 * Author: Administrator
 * Create Data: 2019/9/25 0025
 */
public interface INewslabelDao {
    Newslabel selectNewslabelById(int id);
}

• 映射文件INewslabelDao.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.abc.dao.INewslabelDao">

    <!--查询栏目及其子孙栏目的信息-->
    <!--多表单独查询-->
    <select id="selectNewslabeByParent" resultMap="newlabelMap">
        select id,name from newslabel where pid = #{id}
    </select>
    <resultMap id="newlabelMap" type="Newslabel">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <collection property="children"
                    ofType="Newslabel"
                    select="selectNewslabeByParent"
                    column="id">

        </collection>
    </resultMap>
    <select id="selectNewslabelById" resultMap="newlabelMap">
        select id,name from newslabel where id = #{id}
    </select>

</mapper>

• 工具类MyBatisUtils.java

package com.abc.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

public class MyBatisUtil {
    static SqlSessionFactory factory = null;
    //SqlSessionFactory是重量级组件，把它定义为单例对象
    public static SqlSession getSqlSession() throws IOException {
        if (factory == null){
            factory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis.xml"));
        }
        return factory.openSession();
    }
}

• 测试类MyTest.java

package com.abc;

import static org.junit.Assert.assertTrue;

import com.abc.beans.Newslabel;
import com.abc.dao.INewslabelDao;
import com.abc.utils.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MyTest
{
    /**
     * Rigorous Test :-)
     */
    SqlSession session = null;
    INewslabelDao newslabelDao = null;
    @Before
    public void before() throws IOException {
        session = MyBatisUtil.getSqlSession();
        newslabelDao = session.getMapper(INewslabelDao.class);
    }
    @After
    public void after(){
        if (session != null){
            session.close();
        }
    }

    //查询栏目及其子孙栏目的信息
    //多表单独查询
    @Test
    public void test01(){
        Newslabel newslabel = newslabelDao.selectNewslabelById(1);
        System.out.println(newslabel);
    }

}

运行结果

只查询子孙栏目

以上代码不变, 变的是映射配置文件

互相嵌套, 直接通过pid查询

• INewslabelDao.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.abc.dao.INewslabelDao">

    <!--查询栏目的子孙栏目的信息-->
    <!--多表单独查询-->
    <select id="selectNewslabelByParent" resultMap="newlabelMap">
        select id,name from newslabel where pid = #{id}
    </select>
    <resultMap id="newlabelMap" type="Newslabel">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <collection property="children"
                    ofType="Newslabel"
                    select="selectNewslabelByParent"
                    column="id">

        </collection>
    </resultMap>

</mapper>

孙栏目 -> 子栏目 -> 父栏目

• 将实体类中的children 换成Newslabel parent
• 映射文件INewslabelDao.xml
  • 因为是父栏目不再是一个集合, 使用association

<?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.abc.dao.INewslabelDao">

    <!--查询栏目及其父辈栏目的信息-->
    <!--多表单独查询-->

    <!--<select id="selectNewslabelByChild" resultMap="newslabelMap">
        select id,name,pid from newslabel where id = #{xx}
    </select>-->
    <resultMap id="newslabelMap" type="Newslabel">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <association property="parent"
                     javaType="Newslabel"
                     select="selectNewslabelById"
                     column="pid"/>
    </resultMap>
    <select id="selectNewslabelById" resultMap="newslabelMap">
        select id,name,pid from newslabel where id=#{xx}
    </select>
</mapper>

数据库查询思路:

首先查询一个孙栏目通过id,

select * from newslabel where id = #{id}

然后找到它pid作为条件, 进行查询

select * from newslabel where id = #{pid}

然后一直这样.....查询

运行结果