当前位置：百木园 > 野生技术 > 正文

Mybatis的级联查询，分步查询，一对一，一对多和多对一

2022-07-05 分类：野生技术阅读(183) 评论(0)

配置和代码目录

***util配置

***log4j配置 --可以打印入日志，也可以使用系统自带的STDOUT_LOGGING个人喜欢log4j

***mybatis-config.xml的配置

***jdbc.properties的大概配置

1、studentMapper.xml

2、banjiMapper.xml

3、studentMapper.java、banjiMapper.java

4、banjiMapperTest.java

5、studentMapperTest.java

***util配置

package com.xiaomin.util;

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 {
    private static String path=\"mybatis-config.xml\";
    private static SqlSessionFactory sqlSessionFactory;
    private static InputStream inputStream;;

    static {
        try {
            inputStream = Resources.getResourceAsStream(path);
            sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static SqlSession getSqlSession(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        return sqlSession;
    }
}

***log4j配置 --可以打印入日志，也可以使用系统自带的STDOUT_LOGGING个人喜欢log4j

#????DEBUG????????console?file???????console?file?????????
log4j.rootLogger=DEBUG, console, file

#??????????
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.Target=System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=[%c]-%m%n

#?????????
log4j.appender.file=org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/xiaomin.log
log4j.appender.file.MaxFileSize=100KB
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]-%m%n

#??????
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.preparedStatement=DEBUG

***mybatis-config.xml的配置

<?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\"></properties>
    <settings>
        <setting name=\"logImpl\" value=\"LOG4J\"/>
    </settings>
<!--取别名，偷懒可用-->
    <typeAliases>
        <typeAlias type=\"com.xiaomin.pojo.student\" alias=\"student\"/>
        <typeAlias type=\"com.xiaomin.pojo.banji\" alias=\"banji\"/>
<!--        <typeAlias type=\"com.xiaomin.mapper.studentMapper\" alias=\"studentMapper\"/>-->
<!--        <typeAlias type=\"com.xiaomin.mapper.banjiMapper\" alias=\"banjiMapper\"/>-->
    </typeAliases>

    <environments default=\"development\">
        <environment id=\"development\">
            <transactionManager type=\"JDBC\"/>
            <dataSource type=\"POOLED\">
                <property name=\"driver\" value=\"${driver}\"/>
                <property name=\"url\" value=\"${url}\"/>
                <property name=\"username\" value=\"${user}\"/>
                <property name=\"password\" value=\"${password}\"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource=\"studentMapper.xml\"/>
        <mapper resource=\"banjiMapper.xml\"/>
    </mappers>
</configuration>

***jdbc.properties的大概配置

user=你自己的用户名
password=你自己的密码
driver=com.mysql.cj.jdbc.Driver    --8.x以上的connect就会有cj，旧版本没有cj
url=jdbc:mysql://localhost:3306/你自己的数据库

initsize = 5
maxsize = 50

1、studentMapper.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.xiaomin.mapper.studentMapper\">
    <resultMap id=\"studentMap\" type=\"student\">
        <id column=\"stu_num\" property=\"stuNum\"/>
        <result column=\"stu_name\" property=\"stuName\"/>
        <result column=\"stu_sex\" property=\"stuSex\"/>
        <result column=\"stu_age\" property=\"stuAge\"/>
        <result column=\"stu_banjiId\" property=\"stuBanjiId\"/>
        <result column=\"stu_banjiId\" property=\"b.banjiId\"/>
        <collection property=\"b\" ofType=\"banji\">
            <id column=\"banjiId\" property=\"banjiId\"/>
            <result column=\"banji_xueYuan\" property=\"banjiXueYuan\"/>
            <result column=\"banji_zhuanYe\" property=\"banjiZhuanYe\"/>
            <result column=\"banji_order\" property=\"banjiOrder\"/>
        </collection>
    </resultMap>
    <select id=\"getAllStudentByNum\" resultMap=\"studentMap\">
        select * from homework01_student st,homework01_banji ba
        where st.stu_banjiId=ba.banjiId and stu_num=#{stuNum}
    </select>

    <resultMap id=\"studentMap2\" type=\"student\">
        <id column=\"stu_num\" property=\"stuNum\"/>
        <result column=\"stu_name\" property=\"stuName\"/>
        <result column=\"stu_sex\" property=\"stuSex\"/>
        <result column=\"stu_age\" property=\"stuAge\"/>
        <result column=\"stu_banjiId\" property=\"stuBanjiId\"/>
        <result column=\"banjiId\" property=\"b.banjiId\"/>
        <result column=\"banji_xueYuan\" property=\"b.banjiXueYuan\"/>
        <result column=\"banji_zhuanYe\" property=\"b.banjiZhuanYe\"/>
        <result column=\"banji_order\" property=\"b.banjiOrder\"/>
    </resultMap>
    <select id=\"getStudentByNum\" resultMap=\"studentMap2\">
        select * from homework01_student st,homework01_banji ba
        where st.stu_banjiId=ba.banjiId and stu_num=#{stuNum}
    </select>

    <select id=\"getStudent\" resultMap=\"studentbanji\">
        select * from homework01_student
    </select>
    <resultMap id=\"studentbanji\" type=\"student\">
        <id column=\"stu_num\" property=\"stuNum\"/>
        <result column=\"stu_name\" property=\"stuName\"/>
        <result column=\"stu_sex\" property=\"stuSex\"/>
        <result column=\"stu_age\" property=\"stuAge\"/>
        <result column=\"stu_banjiId\" property=\"stuBanjiId\"/>
        <!--复杂的属性，需单独处理：对象： association 集合： collection
        javaType:指定属性类型
        集合中的泛型信息，使用ofType获取-->

<!--        这个行不通，不知道为啥，哔站_狂神讲的mybatis 20集 多对一 -->
<!--        <association property=\"b\" column=\"stu_banjiId\" javaType=\"banji\" select=\"getBanji\"/>-->
        <association property=\"b\" column=\"stu_banjiId\" javaType=\"banji\" select=\"com.xiaomin.mapper.banjiMapper.getBanji\"/>
    </resultMap>
<!--    <select id=\"getBanji\" resultType=\"banji\">-->
<!--        select * from homework01_banji where banjiId=#{banjiId}-->
<!--    </select>-->

<!--    按照结果嵌套处理-->
    <select id=\"getStudent2\" resultMap=\"studentbanji2\">
        select * from homework01_student s,homework01_banji b where banjiId=stu_banjiId
    </select>
    <resultMap id=\"studentbanji2\" type=\"student\">
        <id column=\"stu_num\" property=\"stuNum\"/>
        <result column=\"stu_name\" property=\"stuName\"/>
        <result column=\"stu_sex\" property=\"stuSex\"/>
        <result column=\"stu_age\" property=\"stuAge\"/>
        <result column=\"stu_banjiId\" property=\"stuBanjiId\"/>
        <association property=\"b\" column=\"stu_banjiId\" javaType=\"banji\">
            <id column=\"banjiId\" property=\"banjiId\"/>
            <result column=\"banji_xueYuan\" property=\"banjiXueYuan\"/>
            <result column=\"banji_zhuanYe\" property=\"banjiZhuanYe\"/>
            <result column=\"banji_order\" property=\"banjiOrder\"/>
        </association>
    </resultMap>

    <resultMap id=\"banjistudent\" type=\"student\">
        <id column=\"stu_num\" property=\"stuNum\"/>
        <result column=\"stu_name\" property=\"stuName\"/>
        <result column=\"stu_sex\" property=\"stuSex\"/>
        <result column=\"stu_age\" property=\"stuAge\"/>
        <result column=\"stu_banjiId\" property=\"stuBanjiId\"/>
    </resultMap>
    <select id=\"getStudentByTeacherId\" resultMap=\"banjistudent\">
        select * from homework01_student where  stu_banjiId=#{bid}
    </select>

</mapper>

2、banjiMapper.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.xiaomin.mapper.banjiMapper\">
    <resultMap id=\"studentbanji\" type=\"banji\">
        <id column=\"banjiId\" property=\"banjiId\"/>
        <result column=\"banji_xueYuan\" property=\"banjiXueYuan\"/>
        <result column=\"banji_zhuanYe\" property=\"banjiZhuanYe\"/>
        <result column=\"banji_order\" property=\"banjiOrder\"/>
    </resultMap>
    <select id=\"getBanji\" resultMap=\"studentbanji\">
        select * from homework01_banji where banjiId=#{banjiId}
    </select>

    <resultMap id=\"banjistudent\" type=\"banji\">
        <id column=\"banjiId\" property=\"banjiId\"/>
        <result column=\"banji_xueYuan\" property=\"banjiXueYuan\"/>
        <result column=\"banji_zhuanYe\" property=\"banjiZhuanYe\"/>
        <result column=\"banji_order\" property=\"banjiOrder\"/>
        <collection property=\"s\" ofType=\"student\">
            <id column=\"stu_num\" property=\"stuNum\"/>
            <result column=\"stu_name\" property=\"stuName\"/>
            <result column=\"stu_sex\" property=\"stuSex\"/>
            <result column=\"stu_age\" property=\"stuAge\"/>
            <result column=\"stu_banjiId\" property=\"stuBanjiId\"/>
        </collection>
    </resultMap>
    <select id=\"getBanji2\" resultMap=\"banjistudent\">
        select * from homework01_banji b,homework01_student s where s.stu_banjiId=b.banjiId and banjiId=#{bid}
    </select>

<!--    按照查询嵌套处理-->
    <resultMap id=\"banjistudent2\" type=\"banji\">
        <id column=\"banjiId\" property=\"banjiId\"/>
        <result column=\"banji_xueYuan\" property=\"banjiXueYuan\"/>
        <result column=\"banji_zhuanYe\" property=\"banjiZhuanYe\"/>
        <result column=\"banji_order\" property=\"banjiOrder\"/>
        <collection property=\"s\" javaType=\"ArrayList\" ofType=\"student\"
                    select=\"com.xiaomin.mapper.studentMapper.getStudentByTeacherId\"
                    column=\"banjiId\"/>
    </resultMap>
    <select id=\"getBanji3\" resultMap=\"banjistudent2\">
        select * from homework01_banji where banjiId=#{bid}
    </select>
</mapper>

3、studentMapper.java、banjiMapper.java

package com.xiaomin.mapper;

import com.xiaomin.pojo.student;

import java.util.List;

public interface studentMapper {
    List<student> getStudent();
    List<student> getStudent2();
    student getStudentByTeacherId();
    List<student> getAllStudentByNum(int stuNum);
    student getStudentByNum(int stuNum);

}

package com.xiaomin.mapper;

import com.xiaomin.pojo.banji;
import org.apache.ibatis.annotations.Param;

import java.util.List;


public interface banjiMapper {
    banji getBanji(int banjiId);
    List<banji> getBanji2(@Param(\"bid\") int banjiId);
    List<banji> getBanji3(@Param(\"bid\") int banjiId);
}

4、banjiMapperTest.java

package com.xiaomin.mapper;

import com.xiaomin.pojo.banji;
import com.xiaomin.util.mybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.junit.jupiter.api.Test;

import java.util.List;

/**
 * @author 解你忧
 * @date 2022/03/25 23:14
 * @product_namae mybatis-software2
 * @product_namae IntelliJ IDEA
 */
public class banjiMapperTest {
    SqlSession sqlSession=mybatisUtil.getSqlSession();
    banjiMapper mapper=sqlSession.getMapper(banjiMapper.class);

    Logger logger = Logger.getLogger(studentMapperTest.class);
    @Test
    public void getBanji(){
        List<banji> banjiList = mapper.getBanji2(20010202);
        for (banji banji : banjiList) {
            System.out.println(banji);
        }
    }
    @Test
    public void getBanji2(){
        List<banji> banjiList = mapper.getBanji3(20010202);
        for (banji banji : banjiList) {
            System.out.println(banji);
        }
    }
}

5、studentMapperTest.java

package com.xiaomin.mapper;

import com.xiaomin.pojo.student;
import com.xiaomin.util.mybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.junit.jupiter.api.Test;

import java.util.List;

/**
 * @author 解你忧
 * @date 2022/03/22/23:14
 */
public class studentMapperTest {
    SqlSession sqlSession= mybatisUtil.getSqlSession();
    studentMapper mapper = sqlSession.getMapper(studentMapper.class);

    Logger logger = Logger.getLogger(studentMapperTest.class);
    @Test
    public void getAllStudentByNum(){
        List<student> studentAndBanjiInfo = mapper.getAllStudentByNum(2032002);
        for (student student : studentAndBanjiInfo) {
            System.out.println(student);
        }
        sqlSession.close();
    }
    @Test
    public void getStudentByNum(){
        student linmin = mapper.getStudentByNum(2032002);
        System.out.println(linmin);
        sqlSession.close();
    }
    @Test
    public void getStudent(){
        List<student> studentList = mapper.getStudent();
        for (student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }
    @Test
    public void getStudent2(){
        List<student> studentList = mapper.getStudent2();
        for (student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }
}

本文来自博客园，作者：解你忧，转载请注明原文链接：https://www.cnblogs.com/jieniyou/p/16417727.html

来源：https://www.cnblogs.com/jieniyou/p/16417727.html
本站部分图文来源于网络，如有侵权请联系删除。

未经允许不得转载：百木园 » Mybatis的级联查询，分步查询，一对一，一对多和多对一

标签：mysql

相关推荐

暂无文章