问题:对一关系的查询
一个训练计划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("----------------------------------");
}