首页 > 代码库 > MyBatis系列:(7)一对多映射

MyBatis系列:(7)一对多映射


0、准备SQL语句(mysql)

CREATE TABLE Cars(
    cid INT(5) PRIMARY KEY,
    cname VARCHAR(10)
);

CREATE TABLE Wheels(
    wid INT(5) PRIMARY KEY,
    wname VARCHAR(10),
    wcar_id INT(5),
    CONSTRAINT wheels_fk FOREIGN KEY(wcar_id) REFERENCES Cars(cid)
);

INSERT INTO Cars(cid,cname) VALUES(1,"BMW");
INSERT INTO Wheels(wid,wname,wcar_id) VALUES(1,‘第1个轮子‘,1);
INSERT INTO Wheels(wid,wname,wcar_id) VALUES(2,‘第2个轮子‘,1);
INSERT INTO Wheels(wid,wname,wcar_id) VALUES(3,‘第3个轮子‘,1);
INSERT INTO Wheels(wid,wname,wcar_id) VALUES(4,‘第4个轮子‘,1);

SELECT * FROM Cars;
SELECT * FROM Wheels;

SELECT c.cid,c.cname,w.wid,w.wname 
FROM cars c INNER JOIN wheels w ON c.cid=w.wcar_id


1、entity类

Car.java

package com.rk.entity;

import java.util.ArrayList;
import java.util.List;

public class Car {
    private Integer id;
    private String name;
    private List<Wheel> wheels = new ArrayList<Wheel>();
    
    public Car(){}

    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 List<Wheel> getWheels() {
        return wheels;
    }

    public void setWheels(List<Wheel> wheels) {
        this.wheels = wheels;
    }

    @Override
    public String toString() {
        return "Car [id=" + id + ", name=" + name + ", wheels=" + wheels + "]";
    }
    
}

Wheel.java

package com.rk.entity;

public class Wheel {
    private Integer id;
    private String name;
    private Car car;
    public Wheel(){}
    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 Car getCar() {
        return car;
    }
    public void setCar(Car car) {
        this.car = car;
    }
    @Override
    public String toString() {
        return "Wheel [id=" + id + ", name=" + name + "]";
    }
    
}


2、mybatis的映射文件

CarMapper.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="carNamespace">
    <resultMap type="com.rk.entity.Car" id="carMap">
        <id property="id" column="cid"/>
        <result property="name" column="cname"/>
        <collection property="wheels" resultMap="wheelNamespace.wheelMap"></collection>
    </resultMap>
    <select id="findCarByWheelId" parameterType="int" resultMap="carMap">
        select c.cid,c.cname
        from cars c inner join wheels w on c.cid=w.wcar_id
        where w.wid=#{wheelId}
    </select>

</mapper>

WheelMapper.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="wheelNamespace">
    <resultMap type="com.rk.entity.Wheel" id="wheelMap">
        <id property="id" column="wid"/>
        <result property="name" column="wname"/>
    </resultMap>
    <select id="findWheelsByCarName" parameterType="string" resultMap="wheelMap">
        select w.wid,w.wname
        from cars c inner join wheels w on c.cid=w.wcar_id
        and c.cname=#{carName}
    </select>
</mapper>


3、mybatis的主配置文件(引入映射文件)

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>
    <properties resource="db.properties"></properties>
    <typeAliases>
        <typeAlias type="com.rk.entity.Emp" alias="emp"/>
    </typeAliases>
    <environments default="mysql_developement">
        <environment id="mysql_developement">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="http://www.mamicode.com/${mysql.driver}"/>
                <property name="url" value="http://www.mamicode.com/${mysql.url}"/>
                <property name="username" value="http://www.mamicode.com/${mysql.username}"/>
                <property name="password" value="http://www.mamicode.com/${mysql.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/rk/entity/CarMapper.xml"/>
        <mapper resource="com/rk/entity/WheelMapper.xml"/>
    </mappers>
</configuration>


db.properties

mysql.driver=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://127.0.0.1:3306/testdb
mysql.username=root
mysql.password=root


4、MyBatisUtils.java

package com.rk.utils;

import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatisUtils {
    private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();
    private static SqlSessionFactory sqlSessionFactory;
    static{
        try {
            Reader reader = Resources.getResourceAsReader("mybatis.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }
    
    private MyBatisUtils(){}
    public static SqlSession getSqlSession(){
        SqlSession sqlSession = threadLocal.get();
        if(sqlSession == null){
            sqlSession = sqlSessionFactory.openSession();
            threadLocal.set(sqlSession);
        }
        return sqlSession;
    }
    
    public static void closeSqlSession(){
        SqlSession sqlSession = threadLocal.get();
        if(sqlSession != null){
            sqlSession.close();
            threadLocal.remove();
        }
    }
    
    public static void main(String[] args) {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        Connection conn = sqlSession.getConnection();
        System.out.println(conn);
    }
}


5、CarWheelDao.java

package com.rk.dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.rk.entity.Car;
import com.rk.entity.Wheel;
import com.rk.utils.MyBatisUtils;

public class CarWheelDao {

    public Car findCarByWheelId(int wheelId){
        SqlSession sqlSession = null;
        try{
            sqlSession = MyBatisUtils.getSqlSession();
            Car car = sqlSession.selectOne("carNamespace.findCarByWheelId", wheelId);
            return car;
        }
        catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw new RuntimeException(e);
        }
        finally{
            MyBatisUtils.closeSqlSession();
        }
    } 
    
    public List<Wheel> findWheelsByCarName(String carName){
        SqlSession sqlSession = null;
        try{
            sqlSession = MyBatisUtils.getSqlSession();
            List<Wheel> list = sqlSession.selectList("wheelNamespace.findWheelsByCarName", carName);
            return list;
        }
        catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw new RuntimeException(e);
        }
        finally{
            MyBatisUtils.closeSqlSession();
        }
    }
    

    
    public static void main(String[] args) {
        CarWheelDao dao = new CarWheelDao();
        Car car = dao.findCarByWheelId(2);
        System.out.println(car);
        
        List<Wheel> wheels = dao.findWheelsByCarName("BMW");
        if(wheels != null && wheels.size()>0){
            for(Wheel wheel : wheels){
                System.out.println(wheel);
            }
        }
    }
}



MyBatis系列:(7)一对多映射