ssm中mybatis对一查询的注意点

问题：对一关系的查询

一个训练计划train由一个教练coach开设的。

希望查询一个训练计划train的时候希望带出教练coach的信息，

但是查得到train表，coach表的内容是null。

 

解决问题的几个注意点：

• trainMapper.xml 中对coach的resultMap配置使用association标签

 <association property="trainCoach" javaType="com.fit.domain.Coach" resultMap="com.fit.mapper.CoachMapper.BaseResultMap"/>

如果直接使用result标签配置，只能拿到coachId：

<result column="train_coach" property="trainCoach.coachId" jdbcType="INTEGER"/>

• 在写sql语句的select语句时，如果能用* 就用* ：

   <select id="findByCoachId" resultMap="BaseResultMap" parameterType="com.fit.domain.Train">
          select 
          *
          from train 
          left join coach
          on coach.coach_id = train.train_coach
          where train_coach = #{coachId}
      </select>

  如果使用反向自动生成的代码代替*，就导致coach查询不到

<sql id="Base_Column_List">
        train_id,train_name,train_period,train_price,train_desc,train_img,train_coach
    </sql>

修改完后：

 

数据库表：

domain：

@Data
public class Train {
	//train_id	int
	private Integer trainId;
	//train_name	varchar
	private String trainName;
	//train_period	int
	private Integer trainPeriod;
	//train_price	int
	private Integer trainPrice;
	//train_desc	varchar
	private String trainDesc;
	//train_img	varchar
	private String trainImg;
	//train_coach	int
//	对一：每个课程属于一个教练
	private Coach trainCoach;

}

@Data
public class Coach {
	//coach_id	int
	private Integer coachId;
	//coach_phone	varchar
	private String coachPhone;
	//coach_name	varchar
	private String coachName;
	//coach_psw	varchar
	private String coachPsw;
	//coach_sex	varchar
	private String coachSex;
	//coach_power	int
	private Integer coachPower;
	//coach_img	varchar
	private String coachImg;
	
	//一个教练可能有多门课程
	private List<Train> trainList;
}

mapper（xml），interface就不写了：

<?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.fit.mapper.TrainMapper">
	<resultMap type="com.fit.domain.Train" id="BaseResultMap">
        <id column="train_id" property="trainId" jdbcType="INTEGER"/>
        <result column="train_name" property="trainName" jdbcType="VARCHAR"/>
        <result column="train_period" property="trainPeriod" jdbcType="INTEGER"/>
        <result column="train_price" property="trainPrice" jdbcType="INTEGER"/>
        <result column="train_desc" property="trainDesc" jdbcType="VARCHAR"/>
        <result column="train_img" property="trainImg" jdbcType="VARCHAR"/>
       <!--  <result column="train_coach" property="trainCoach.coachId" jdbcType="INTEGER"/> -->
       <!-- 对一：每个课程属于一个教练 -->
        <association property="trainCoach" javaType="com.fit.domain.Coach"
        	resultMap="com.fit.mapper.CoachMapper.BaseResultMap"/>
    </resultMap>
    
<!--     <sql id="Base_Column_List">
        train_id,train_name,train_period,train_price,train_desc,train_img,train_coach
    </sql> -->
    
    <select id="findByCoachId" resultMap="BaseResultMap" parameterType="com.fit.domain.Train">
        select 
        *
        from train 
        left join coach
        on coach.coach_id = train.train_coach
        where train_coach = #{coachId}
    </select>

</mapper>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.fit.mapper.CoachMapper">

	<resultMap type="com.fit.domain.Coach" id="BaseResultMap">
        <id column="coach_id" property="coachId" jdbcType="INTEGER"/>
        <result column="coach_phone" property="coachPhone" jdbcType="VARCHAR"/>
        <result column="coach_name" property="coachName" jdbcType="VARCHAR"/>
        <result column="coach_psw" property="coachPsw" jdbcType="VARCHAR"/>
        <result column="coach_sex" property="coachSex" jdbcType="VARCHAR"/>
        <result column="coach_power" property="coachPower" jdbcType="INTEGER"/>
        <result column="coach_img" property="coachImg" jdbcType="VARCHAR"/>
    </resultMap>
</mapper>

测试：

@Autowired
	private TrainMapper TrainMapper;
	
	@Test
	public void findByCoachId() {
		List<Train> trainList = this.TrainMapper.findByCoachId(1);
		System.out.println("coachId 为 1 的教练开设的课程训练有：");
		for (Train train : trainList) {
			System.out.println("train名字："+train.getTrainName());
			System.out.println("train课时："+train.getTrainPeriod());
			System.out.println("train价格："+train.getTrainPrice());
			System.out.println("coach教练是："+train.getTrainCoach());
			System.out.println("coach教练姓名是："+train.getTrainCoach().getCoachName());
		}
		System.out.println("----------------------------------");
	}