java映射关系写法 映射类对应关系

myBatis中类与类关系映射数据查询

在一个类中通常会依赖其他类作为自己的私有变量，反映在数据库的表格中就是主外键关联

employee表

department表

如果想要查询一个Employee对象，则需要关联表department进行查询，具体的方法有以下几种：

1.关联查询

关联查询就是将要查询的表格左连接后进行统一查询
配合resultMap标签进行查询数据和类变量之间的映射

查询语句：

select em.id,name,de.id `de.id`,de.department_name
        from private.employee em
        left join department de
        on de.id=em.department_id
        where em.id = #{id}

resultMap标签：

<resultMap id="employeeMap" type="employee">
        <id property="id" column="id"></id>
        <result column="de.id" property="dept.id" ></result>
        <result column="department_name" property="dept.departmentName" ></result>
    </resultMap>

说明：

完整employeeMapper.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.example.dao.EmployeeDao">
    <resultMap id="employeeMap" type="employee">
        <id property="id" column="id"></id>
        <result property="dept.id" column="de.id"></result>
        <result property="dept.departmentName" column="department_name"></result>
    </resultMap>
    <select id="getById" resultMap="employeeMap">
        select em.id,name,de.id `de.id`,de.department_name
        from private.employee em
        left join department de
        on de.id=em.department_id
        where em.id = #{id}
    </select>

</mapper>

mybatis-conf.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="properties/jdbc-conf.properties"></properties>
    <!--设置数据库与实类变量的匹配匹配-->
    <settings>
   		 <!--类变量名称和表字段自动匹配-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!--value值表示对应的日志框架-->
        <setting name="logImpl" value="LOG4J"></setting>
    </settings>
    <typeAliases>
   		 <!--将一个包下所有类在配置时都可以简写成首字母小写格式-->
        <package name="com.example.domain"></package>
    </typeAliases>
    <environments default="aa">
        <environment id="aa">
            <!--transactionManager表示事务管理，type表示类型，JDBC表示进行事务管理，MANAGED表示不进行事务管理-->
            <transactionManager type="JDBC"></transactionManager>
            <!--dataSource表示数据源配置,type表示使用连接池或者不使用连接池-->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driverClassName}"></property>
                <property name="url" value="${jdbc.url}"></property>
                <property name="username" value="${jdbc.username}"></property>
                <property name="password" value="${jdbc.password}"></property>

            </dataSource>
        </environment>
    </environments>

    <!--将单独的映射关系配置到全局配置文件中-->
    <mappers>
        <mapper resource="myBatis/departmentMapper.xml"></mapper>
        <mapper resource="myBatis/employeeMapper.xml"></mapper>
    </mappers>
</configuration>

测试类代码：

public class MyTest {
    private static SqlSessionFactory build;
    private static SqlSession sqlSession;
    @BeforeClass
    public static void beforeClass() throws IOException {
        String resource = "myBatis/mybatis-conf.xml";
        InputStream resourceAsStream = Resources.getResourceAsStream(resource);
        build = new SqlSessionFactoryBuilder().build(resourceAsStream);
    }
    @Before
    public void before(){
        sqlSession = build.openSession();
    }
    @After
    public void after(){
        sqlSession.close();
    }

    @Test
    public void test2() {
        EmployeeDao mapper = sqlSession.getMapper(EmployeeDao.class);
        Employee employee = mapper.getById(1);
        System.out.println(employee);
    }
}

显示打印结果：

作为对比，未进行匹配的查询结果：

2.进行子查询

先进行employeeMapper的查询，将查询到的employee.department_id作为二次查询的数据
具体代码：
mybatis-conf.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="properties/jdbc-conf.properties"></properties>
    <!--设置数据库与实类变量的匹配匹配-->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!--value值表示对应的日志框架-->
        <setting name="logImpl" value="LOG4J"></setting>
    </settings>
    <typeAliases>
        <package name="com.example.domain"></package>
        <!--<typeAlias type="com.example.domain.Book" alias="book"></typeAlias>-->
    </typeAliases>
    <environments default="aa">
        <environment id="aa">
            <!--transactionManager表示事务管理，type表示类型，JDBC表示进行事务管理，MANAGED表示不进行事务管理-->
            <transactionManager type="JDBC"></transactionManager>
            <!--dataSource表示数据源配置,type表示使用连接池或者不使用连接池-->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driverClassName}"></property>
                <property name="url" value="${jdbc.url}"></property>
                <property name="username" value="${jdbc.username}"></property>
                <property name="password" value="${jdbc.password}"></property>

            </dataSource>
        </environment>
    </environments>

    <!--将单独的映射关系配置到全局配置文件中-->
    <mappers>
        <mapper resource="myBatis/departmentMapper.xml"></mapper>
        <mapper resource="myBatis/employeeMapper.xml"></mapper>
    </mappers>
</configuration>

departmentMapper.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.example.dao.DepartmentDao">
<select id="getById" resultType="department">
        <![CDATA[
	      select * from department
	      where  id = #{id}
        ]]>
    </select>

</mapper>

employeeMapper.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.example.dao.EmployeeDao">

    <resultMap id="basicMap" type="employee">
        <id column="id" property="id" ></id>
    </resultMap>

    <resultMap id="employeeMap2" type="employee" extends="basicMap">
        <association property="dept" javaType="department" select="com.example.dao.DepartmentDao.getById" column="department_id">
            <id property="id" column="id"></id>
            <result property="departmentName" column="department_name"></result>
        </association>
    </resultMap>

    <select id="getById2" resultMap="employeeMap2">
        select id,name,department_id
        from private.employee em
        where em.id = #{id}
    </select>
</mapper>

测试代码：

public class MyTest {
    private static SqlSessionFactory build;
    private static SqlSession sqlSession;
    @BeforeClass
    public static void beforeClass() throws IOException {
        String resource = "myBatis/mybatis-conf.xml";
        InputStream resourceAsStream = Resources.getResourceAsStream(resource);
        build = new SqlSessionFactoryBuilder().build(resourceAsStream);
    }
    @Before
    public void before(){
        sqlSession = build.openSession();
    }
    @After
    public void after(){
        sqlSession.close();
    }

    @Test
    public void test2() {
        EmployeeDao mapper = sqlSession.getMapper(EmployeeDao.class);
        Employee employee = mapper.getById2(1);
        System.out.println(employee);
    }
}

参数映射关系说明：

最终查询显示结果：

缺点是需要进行两次sql请求，解决方式是进行延迟加载按需进行查询：

fetchType="lazy"

<association property="dept" javaType="department" select="com.example.dao.DepartmentDao.getById" column="department_id" fetchType="lazy">

设置后验证：

@Test
    public void test2() {
        EmployeeDao mapper = sqlSession.getMapper(EmployeeDao.class);
        Employee employee = mapper.getById2(1);
        System.out.println("***********************************");
        System.out.println(employee);
    }

3.通过collection来获取关联的集合

departmentMapper.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.example.dao.DepartmentDao">
    <resultMap id="departmentMap" type="department">
        <id property="id" column="id"></id>
        <collection property="emps" ofType="employee" select="com.example.dao.EmployeeDao.getByDepartId" column="id"></collection>
    </resultMap>
<select id="getById" resultMap="departmentMap">
        <![CDATA[
	      select *
	      from department
	      where  id = #{id}
        ]]>
    </select>
</mapper>

employeeMapper.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.example.dao.EmployeeDao">
    <resultMap id="basicMap" type="employee">
        <id column="id" property="id" ></id>
    </resultMap>

    <resultMap id="employeeMap" type="employee" extends="basicMap">
        <result column="de.id" property="dept.id" ></result>
        <result column="department_name" property="dept.departmentName" ></result>
    </resultMap>
    <select id="getById" resultMap="employeeMap">
        select em.id,name,de.id `de.id`,de.department_name
        from private.employee em
        left join department de
        on de.id=em.department_id
        where em.id = #{id}
    </select>


    <resultMap id="employeeMap2" type="employee" extends="basicMap">
        <association property="dept" javaType="department" select="com.example.dao.DepartmentDao.getById" column="department_id" fetchType="lazy">
            <id property="id" column="id"></id>
            <result property="departmentName" column="department_name"></result>
        </association>
    </resultMap>

    <select id="getByDepartId" resultMap="employeeMap2">
        select *
        from private.employee em
        where em.department_id = #{id}
    </select>
</mapper>

EmployeeDao

public interface EmployeeDao {
    //查询所有
    List<Employee> getAll();
    //通过id查询
    Employee getById(Integer id);

    Employee getById2(Integer id);

    List<Employee> getByDepartId(Integer id);
}

说明：

这样的结果是相对复杂，但可以解决类之间的相互指定显示结果：