oracle表之间的关联

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引入配置文件-->
    <properties resource="db.properties"></properties>

    <settings>
        <!--是否使用缓存 开发中禁用-->
        <setting name="cacheEnabled" value="false"/>
        <!--下划线和驼峰式命名法的匹配  -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!--使用启用懒加载机制  true启用-->
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="multipleResultSetsEnabled" value="true"/>
        <setting name="useColumnLabel" value="true"/>
        <setting name="useGeneratedKeys" value="true"/>
        <setting name="autoMappingBehavior" value="PARTIAL"/>
        <setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
        <setting name="defaultExecutorType" value="SIMPLE"/>
        <setting name="defaultStatementTimeout" value="25"/>
        <setting name="defaultFetchSize" value="100"/>
        <setting name="safeRowBoundsEnabled" value="false"/>
        <setting name="localCacheScope" value="SESSION"/>
        <setting name="jdbcTypeForNull" value="OTHER"/>
        <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
    </settings>
    <typeAliases>
        <!--给类名起别名-->
        <!--<typeAlias type="com.teng.domain.User" alias="User"></typeAlias>-->
        <!--给包中所有的类名起别名 默认名字为类名-->
        <package name="com.teng.domain"></package>
    </typeAliases>
    <!--配置mybatis数据库坏境 default指定当前使用的默认坏境-->
    <environments default="product">
        <!--id给当前连接环境起名 不能重复-->
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driverClassName}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>

        <!--id给当前连接环境起名 不能重复-->
        <environment id="product">
            <transactionManager type="jdbc" />
            <dataSource type="POOLED">
                <property name="driver" value="oracle.jdbc.driver.OracleDriver" />
                <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:orcl" />
                <property name="username" value="jtf" />
                <property name="password" value="123456" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--引入mapper映射文件的位置-->
        <mapper resource="Mapper/UserMapper.xml"/>
        <mapper resource="com.teng.Mapper/UserMapper.xml"/>
        <mapper resource="com.teng.Mapper/StudentMapper.xml"/>
        <mapper resource="com.teng.Mapper/StuMapper.xml"/>
        <mapper resource="com.teng.Mapper/TeacherMapper.xml"/>
    </mappers>
</configuration>

log4j2.xml

<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="trace">
    <Appenders>
        <Console name="Console" target="SYSTEM_OUT">
            <PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n"/>
        </Console>
    </Appenders>
    <Loggers>
        <Root level="trace">
            <AppenderRef ref="Console"/>
        </Root>
        <Logger name="com.qy.mapper" level="trace" additivity="false">
            <AppenderRef ref="Console"/>
        </Logger>
    </Loggers>
</Configuration>

实体类 Stu

package com.teng.domain;

public class Stu {

    private Integer id;
    private String name;
    private Integer teacherId;
    private String className;
    private Teacher teacher;

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }

    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 Integer getTeacherId() {
        return teacherId;
    }

    public void setTeacherId(Integer teacherId) {
        this.teacherId = teacherId;
    }

    public String getClassName() {
        return className;
    }

    public void setClassName(String className) {
        this.className = className;
    }
}

Teacher

package com.teng.domain;

import java.util.List;

public class Teacher {

    private Integer id;
    private String name;
    private String className;
    private List<Stu> stus;

    public List<Stu> getStus() {
        return stus;
    }

    public void setStus(List<Stu> stus) {
        this.stus = stus;
    }

    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 String getClassName() {
        return className;
    }

    public void setClassName(String className) {
        this.className = className;
    }
}

接口 stuMapper

package com.teng.mapper;
import com.teng.domain.Stu;
import org.apache.ibatis.annotations.Param;
import java.util.HashMap;
import java.util.List;

public interface StuMapper {
    public List<HashMap<String,Object>> getStu();
    public List<Stu> getStu1();
 
}

TeacherMapper

package com.teng.mapper;
import com.teng.domain.Teacher;
import java.util.List;
public interface TeacherMapper {
    public List<Teacher> selectAllTeacher();
    public List<Teacher> findAllTeacher();
}

实体类对应的mapper映射文件 多对一关联 StuMapper.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">
<!--namespace 命名空间 唯一的-->
<mapper namespace="com.teng.mapper.StuMapper">

    <resultMap id="stuMap" type="Stu" autoMapping="true">
        <id property="id" column="id"></id>
        <result property="name" column="stuName"></result>
        <result property="className" column="stuClassName"></result>
        <!--多对一关联-->
        <association property="teacher" column="teacher_id" javaType="Teacher" autoMapping="true">
            <id property="id" column="id"></id>
        </association>
    </resultMap>
    <select id="getStu" resultType="HashMap">
        select
            s.id,s.name,s.teacher_id,s.class_name,
            t.name,t.class_name
            from stu s join teacher t
            on s.teacher_id = t.id
    </select>
    <select id="getStu1" resultMap="stuMap">
        select stu.id,stu.name as stuName,stu.teacher_id as teacherId,stu.class_name as stuClassName,
          teacher.name,teacher.class_name
          from stu,teacher
          where stu.teacher_id = teacher.id
    </select>
</mapper>

一对多关联 TeacherMapper.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">
<!--namespace 命名空间 唯一的-->
<mapper namespace="com.teng.mapper.TeacherMapper">
    <!--一对多的第一种写法,一般考虑到性能,不会这样实现-->
    <resultMap id="TeacherMap" type="Teacher" autoMapping="true">
    <collection property="stus" ofType="Stu" column="id" autoMapping="true">
    </collection>
    </resultMap>
    <!--查询所有的老师及各自的所有学生,第一种形式 一一对应-->
    <select id="selectAllTeacher" parameterType="Teacher" resultMap="TeacherMap">
        select t.id,t.name,t.class_name,
        s.id as sid,s.name as sname,s.class_name as className
        from
        teacher t join stu s
        on t.id = s.teacher_id
    </select>
    <resultMap type="Teacher" id="teacherMaps" autoMapping="true">
        <collection property="stus" ofType="Stu" select="getStudents" column="id">
        </collection>
    </resultMap>
    <!-- 查询所有的老师级各自的所有学生,一对多关联-->
    <select id="findAllTeacher" parameterType="Teacher" resultMap="teacherMaps">
		SELECT
			t.id,
			t.name,
			t.class_name
		FROM
			teacher t
	</select>
    <select id="getStudents" parameterType="int" resultType="Student">
		select
			s.id,
			s.name,
			s.class_name as className
		from stu s
		where teacher_id = #{id}
	</select>
</mapper>

测试类

package com.teng;

import com.teng.domain.Stu;
import com.teng.domain.Teacher;
import com.teng.mapper.StuMapper;
import com.teng.mapper.TeacherMapper;
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 org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;

public class test3 {
    static SqlSessionFactory sqlSessionFactory=null;
    static {
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }
    //获取sqlsession
    public SqlSession getSqlSession() throws Exception{
        SqlSession session = sqlSessionFactory.openSession();
        return  session;
    }
    //关闭session
    public void sessionClose(SqlSession session){
        if (session!=null)session.close();
    }

    @Test
    public void selectAllTeacher() throws Exception {
        SqlSession session = getSqlSession();
        TeacherMapper mapper = session.getMapper(TeacherMapper.class);
        List<Teacher> list = mapper.selectAllTeacher();
        List<Teacher> list1 = mapper.findAllTeacher();
        System.out.println(list);
    }

    @Test
    public void getStu() throws Exception{
        SqlSession session = getSqlSession();
        StuMapper mapper = session.getMapper(StuMapper.class);
        List<HashMap<String,Object>> list = mapper.getStu();
        List<Stu> list1 = mapper.getStu1();
        System.out.println(list);
    }
}

结果

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券