mybatis一对多关联

简单的例子(商品与用户关联)

用户表

商品表

分别生成用户、商品实体类, 及dao层配置

mybatis.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>

    <!--设置别名-->
    <typeAliases>
        <!--
            type: 数据类型
            alias: 别名
        -->
        <typeAlias type="com.java.entity.User" alias="User"></typeAlias>
        <typeAlias type="com.java.entity.Goods" alias="Goods"></typeAlias>
    </typeAliases>

    <!--配置数据库连接环境-->
    <!--可以配置多个-->
    <environments default="mysql">

        <!--详细配置-->
        <environment id="mysql">
            <!--配置事务-->
            <transactionManager type="JDBC"></transactionManager>
            <!--配置数据源-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"></property>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/my_database"></property>
                <property name="username" value="root"></property>
                <property name="password" value="123456"></property>
            </dataSource>
        </environment>

    </environments>

    <!--关联局部配置文件-->
    <mappers>
        <mapper resource="com/java/dao/User/UserDao.xml"></mapper>
        <mapper resource="com/java/dao/Goods/GoodsDao.xml"></mapper>
    </mappers>

</configuration>

User.java(实体类)

/**
 * 用户
 */
public class User {
    private Long id;
    private String username;
    private String password;
    private Integer role_id;
    private Role role;
    private List<Goods> goods_list;

	/**
	 set、get方法、有(无)参构造函数、toString
	 */
}

UserDao.java(dao层)

package com.java.dao.User;

import com.java.entity.User;
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.Before;
import org.junit.Test;

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

public class UserDao {

    private SqlSession sqlSession = null;

    @Before
    public void init() throws IOException {
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory build = sqlSessionFactoryBuilder.build(resourceAsStream);
        sqlSession = build.openSession();
    }

    @Test
    public void selectUserDetails() {
        List<User> userList = sqlSession.selectList("com.java.dao.User.UserDao.selectUserDetails");
        for(User user : userList) {
            System.out.println(user);
        }
    }
}

UserDao.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: 命名空间, 其值为某一个dao层类的具体路径
-->
<mapper namespace="com.java.dao.User.UserDao">

    <!--用户关联角色及商品查询-->
    <resultMap id="user_role" type="User">
        <result property="id" javaType="Long" column="id"></result>
        <result property="username" javaType="String" column="username"></result>
        <result property="password" javaType="String" column="password"></result>
        <result property="role_id" javaType="Integer" column="role_id"></result>
        <association property="role" javaType="Role">
            <result property="id" javaType="Integer" column="role_id"></result>
            <result property="name" javaType="String" column="role_name"></result>
            <result property="desc" javaType="String" column="role_desc"></result>
            <result property="authority" javaType="String" column="role_authority"></result>
        </association>

        <!--一对多关系-->
        <collection property="goods_list" javaType="List" ofType="Goods">
            <result property="id" column="goods_id"></result>
            <result property="name" column="goods_name"></result>
            <result property="desc" column="goods_desc"></result>
        </collection>
    </resultMap>
    <select id="selectUserDetails" resultMap="user_role" parameterType="User">
        SELECT u.*, r.name as role_name, r.desc as role_desc, r.authority as role_authority, g.id as goods_id, g.name as goods_name, g.desc as goods_desc FROM users u INNER JOIN role r ON u.role_id = r.id LEFT JOIN goods g ON u.id = g.user_id;
    </select>
</mapper>

Goods.java(实体类)

public class Goods {
    private Integer id;
    private String name;
    private String desc;
    private Integer user_id;
    /**
     	set、get方法、有(无)参构造函数、toString
     */
}

GoodsDao.java(dao层)

package com.java.dao.Goods;

import com.java.entity.Goods;
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.Before;
import org.junit.Test;

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

public class GoodsDao {
    private SqlSession sqlSession = null;

    @Before
    public void init() throws IOException {
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory build = sqlSessionFactoryBuilder.build(resourceAsStream);
        sqlSession = build.openSession();
    }

    @Test
    public void getAll() {
        List<Goods> goodsList = sqlSession.selectList("com.java.dao.Goods.GoodsDao.getAll");
        System.out.println(goodsList);
    }

    @Test
    public void pushGoods() {
        Goods goods = new Goods(null, "蘑菇炖鸡面", "蘑菇炖鸡面", null);
        int insert = sqlSession.insert("com.java.dao.Goods.GoodsDao.pushGoods", goods);
        sqlSession.commit();
        System.out.println(insert);
    }

    @Test
    public void updateGoods() {
        Goods goods = new Goods(1, "酸菜牛肉面", "酸菜牛肉面", null);
        int update = sqlSession.update("com.java.dao.Goods.GoodsDao.updateGoods", goods);
        sqlSession.commit();
        System.out.println(update);
    }

    @Test
    public void deleteGoods() {
        HashMap<String, Object> map = new HashMap<String, Object>();
        map.put("delete", "(3,4,5)");
        int delete = sqlSession.delete("com.java.dao.Goods.GoodsDao.deleteGoods", map);
        sqlSession.commit();
        System.out.println(delete);
    }
}

UserDao.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: 命名空间, 其值为某一个dao层类的具体路径
-->
<mapper namespace="com.java.dao.Goods.GoodsDao">
    <select id="getAll" resultType="Goods" parameterType="Goods">
         SELECT * FROM goods;
    </select>

    <insert id="pushGoods" parameterType="Goods">
        INSERT INTO goods (`name`, `desc`) VALUES (#{name}, #{desc});
    </insert>

    <update id="updateGoods" parameterType="Goods">
        UPDATE goods
        <set>
            <if test="name != null">
                `name` = #{name},
            </if>
            <if test="desc != null">
                `desc` = #{desc},
            </if>
            <if test="user_id != null">
                `user_id` = #{user_id},
            </if>
            `id`=#{id}
        </set>
        WHERE id = #{id};
    </update>

    <delete id="deleteGoods" parameterType="Map">
        DELETE FROM goods WHERE id in ${delete};
    </delete>
</mapper>