Java Mybatis框架多表操作與注解開發(fā)詳解分析

更新時(shí)間：2021年10月27日 14:41:25 作者：龍弟-idea

MyBatis 是一款優(yōu)秀的持久層框架，它支持自定義 SQL、存儲過程以及高級映射。MyBatis 免除了幾乎所有的 JDBC 代碼以及設(shè)置參數(shù)和獲取結(jié)果集的工作。MyBatis 可以通過簡單的 XML 或注解來配置和映射原始類型、接口和 Java POJO為數(shù)據(jù)庫中的記錄

一對一查詢

一對一查詢的模型

用戶表和訂單表的關(guān)系為，一個(gè)用戶有多個(gè)訂單，一個(gè)訂單只從屬于一個(gè)用戶。

一對一查詢的需求:查詢一個(gè)訂單，與此同時(shí)查詢出該訂單所屬的用戶

一對一查詢的語句

對應(yīng)的sql語句: select * from orders o,user u where o.uid=u.id;查詢的結(jié)果如下:

創(chuàng)建Order和User實(shí)體

創(chuàng)建OrderMapper接口

public interface OrderMapper {
 
    //查詢?nèi)康姆椒?
    public List<Order> findAll();
}

配置OrderMapper.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.longdi.mapper.OrderMapper">
 
    <resultMap id="orderMap" type="order">
        <!--手動(dòng)指定字段與實(shí)體屬性的映射關(guān)系
            column: 數(shù)據(jù)表的字段名稱
            property：實(shí)體的屬性名稱
        -->
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        <!--<result column="uid" property="user.id"></result>
        <result column="username" property="user.username"></result>
        <result column="password" property="user.password"></result>
        <result column="birthday" property="user.birthday"></result>-->
 
        <!--
            property: 當(dāng)前實(shí)體(order)中的屬性名稱(private User user)
            javaType: 當(dāng)前實(shí)體(order)中的屬性的類型(User)
        -->
        <association property="user" javaType="user">
            <id column="uid" property="id"></id>
            <result column="username" property="username"></result>
            <result column="password" property="password"></result>
            <result column="birthday" property="birthday"></result>
        </association>
 
    </resultMap>
 
    <select id="findAll" resultMap="orderMap">
         SELECT *,o.id oid FROM orders o,USER u WHERE o.uid=u.id
    </select>
 
</mapper>

配置UserMapper.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.longdi.mapper.UserMapper">
 
 
    <resultMap id="userMap" type="user">
        <id column="uid" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
        <!--配置集合信息
            property:集合名稱
            ofType：當(dāng)前集合中的數(shù)據(jù)類型
        -->
        <collection property="orderList" ofType="order">
            <!--封裝order的數(shù)據(jù)-->
            <id column="oid" property="id"></id>
            <result column="ordertime" property="ordertime"></result>
            <result column="total" property="total"></result>
        </collection>
    </resultMap>
 
    <select id="findAll" resultMap="userMap">
        SELECT *,o.id oid FROM USER u,orders o WHERE u.id=o.uid
    </select>
 
    <!--#############################################################################-->
 
    <resultMap id="userRoleMap" type="user">
        <!--user的信息-->
        <id column="userId" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
        <!--user內(nèi)部的roleList信息-->
        <collection property="roleList" ofType="role">
            <id column="roleId" property="id"></id>
            <result column="roleName" property="roleName"></result>
            <result column="roleDesc" property="roleDesc"></result>
        </collection>
    </resultMap>
 
    <select id="findUserAndRoleAll" resultMap="userRoleMap">
        SELECT * FROM USER u,sys_user_role ur,sys_role r WHERE u.id=ur.userId AND ur.roleId=r.id
    </select>
 
</mapper>

配置sqlMapConfig.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標(biāo)簽加載外部properties文件-->
    <properties resource="jdbc.properties"></properties>
 
    <!--自定義別名-->
    <typeAliases>
        <typeAlias type="com.longdi.domain.User" alias="user"></typeAlias>
        <typeAlias type="com.longdi.domain.Order" alias="order"></typeAlias>
        <typeAlias type="com.longdi.domain.Role" alias="role"></typeAlias>
    </typeAliases>
 
    <!--數(shù)據(jù)源環(huán)境-->
    <environments default="developement">
        <environment id="developement">
            <transactionManager type="JDBC"></transactionManager>
            <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>
    </environments>
    
 
    <!--加載映射文件-->
    <mappers>
        <mapper resource="com/longdi/mapper/UserMapper.xml"></mapper>
        <mapper resource="com/longdi/mapper/OrderMapper.xml"></mapper>
    </mappers>
</configuration>

測試代碼

    @Test
    public void test1() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
 
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
        List<Order> orderList = mapper.findAll();
        for (Order order : orderList) {
            System.out.println(order);
        }
        sqlSession.close();
    }

一對多查詢

一對多查詢模型

用戶表和訂單表的關(guān)系為，一個(gè)用戶有多個(gè)訂單，一個(gè)訂單只從屬于一個(gè)用戶一對多查詢的需求:查詢一個(gè)用戶，與此同時(shí)查詢出該用戶具有的訂單

對應(yīng)的sql語句: select *,o.id oid from user u left join orders o on u.id=o.uid;查詢的結(jié)果如下:

SELECT *,o.id oid FROM USER u, orders o WHERE u.id=o.uid

修改User實(shí)體

創(chuàng)建UserMapper接口

配置UserMapper.xml
測試結(jié)果

多對多查詢

多對多查詢的模型
用戶表和角色表的關(guān)系為，一個(gè)用戶有多個(gè)角色，一個(gè)角色被多個(gè)用戶使用多對多查詢的需求:查詢用戶同時(shí)查詢出該用戶的所有角色

多對多查詢的語句

對應(yīng)的sql語句: select u.*,r.*,r.id rid from user u left join user_role ur on u.id=ur.user_idinner join role r on ur.role_id=r.id;
查詢的結(jié)果如下:

創(chuàng)建Role實(shí)體，修改User實(shí)體

添加UserMapper接口方法

配置UserMapper.xml（上面）
測試代碼

總結(jié)：

MyBatis多表配置方式:

一對一配置:使用<resultMap>做配置
一對多配置:使用<resultMap>+<collection>做配置

多對多配置:使用<resultMap>+<collection>做配置

Mybatis的注解開發(fā)

Mybatis的常用注解

這幾年來注解開發(fā)越來越流行，Mybatis也可以使用注解開發(fā)方式，這樣我們就可以減少編寫Mapper映射文件了。我們先圍繞一些基本的CRUD來學(xué)習(xí)，再學(xué)習(xí)復(fù)雜映射多表操作。
@Insert:實(shí)現(xiàn)新增
@Update:實(shí)現(xiàn)更新@Delete:實(shí)現(xiàn)刪除@Select:實(shí)現(xiàn)查詢
@Result:實(shí)現(xiàn)結(jié)果集封裝
@Results:可以與@Result一起使用，封裝多個(gè)結(jié)果集@One:實(shí)現(xiàn)一對一結(jié)果集封裝
@Many:實(shí)現(xiàn)一對多結(jié)果集封裝

Mybatis的增刪查改

    private UserMapper mapper;
 
    @Before
    public void before() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        mapper = sqlSession.getMapper(UserMapper.class);
    }

   @Test
    public void testSave(){
        User user = new User();
        user.setUsername("tom");
        user.setPassword("abc");
        mapper.save(user);
    }
 
    @Test
    public void testUpdate(){
        User user = new User();
        user.setId(18);
        user.setUsername("lucy");
        user.setPassword("123");
        mapper.update(user);
    }
 
    @Test
    public void testDelete(){
        mapper.delete(18);
    }
 
    @Test
    public void testFindById(){
        User user = mapper.findById(2);
        System.out.println(user);
    }
 
    @Test
    public void testFindAll(){
        List<User> all = mapper.findAll();
        for (User user : all) {
            System.out.println(user);
        }
    }

修改MyBatis的核心配置文件，我們使用了注解替代的映射文件，所以我們只需要加載使用了注解的Mapper接口即可

    <!--加載映射關(guān)系-->
    <mappers>
        <!--掃描使用注解的類-->
        <package name="com.longdi.mapper.UserMapper"></package>
    </mappers>

或者指定掃描包含映射關(guān)系的接口所在的包也可以

    <!--加載映射關(guān)系-->
    <mappers>
        <!--指定接口所在的包-->
        <package name="com.longdi.mapper"></package>
    </mappers>

UserMapper:

public interface UserMapper {
 
    @Insert("insert into user values(#{id},#{username},#{password},#{birthday})")
    public void save(User user);
 
    @Update("update user set username=#{username},password=#{password} where id=#{id}")
    public void update(User user);
 
    @Delete("delete from user where id=#{id}")
    public void delete(int id);
 
    @Select("select * from user where id=#{id}")
    public User findById(int id);
 
    @Select("select * from user")
    public List<User> findAll();
 
    @Select("select * from user")
    @Results({
            @Result(id=true ,column = "id",property = "id"),
            @Result(column = "username",property = "username"),
            @Result(column = "password",property = "password"),
            @Result(
                    property = "orderList",
                    column = "id",
                    javaType = List.class,
                    many = @Many(select = "com.longdi.mapper.OrderMapper.findByUid")
            )
    })
    public List<User> findUserAndOrderAll();
 
 
    @Select("SELECT * FROM USER")
    @Results({
            @Result(id = true,column = "id",property = "id"),
            @Result(column = "username",property = "username"),
            @Result(column = "password",property = "password"),
            @Result(
                    property = "roleList",
                    column = "id",
                    javaType = List.class,
                    many = @Many(select = "com.longdi.mapper.RoleMapper.findByUid")
            )
    })
    public List<User> findUserAndRoleAll();
 
 
}