association标签
实体类
/**
*书籍
*/
@Data
public class Book {
private String id;
private String name;
private String author;
private Double price;
//出版社
private Publisher pub;//一本书对应一个出版社
}
/**
*出版社
*/
@Data
public class Publisher {
private String id;
private String name;
private String phone;
private String address;
}
XML
关联查询
<!–配置关联实体类–>
<resultMap id="bookResultMap" type="com.entity.Book">
<!–主键属性–>
<id property="id" column="id" jdbcType="VARCHAR"></id>
<!–普通属性–>
<result property="name" column="name" jdbcType="VARCHAR"></result>
<result property="author" column="author" jdbcType="VARCHAR"></result>
<result property="price" column="price" jdbcType="VARCHAR"></result>
<!–一对一映射–>
<association property="pub" javaType="com.entity.Publisher">
<id property="id" column="id" jdbcType="VARCHAR"></id>
<result property="name" column="name" jdbcType="VARCHAR"></result>
<result property="phone" column="phone" jdbcType="VARCHAR"></result>
<result property="address" column="address" jdbcType="VARCHAR"></result>
</association>
</resultMap>
<!–关联查询–>
<select id="selectAllBook" resultMap="bookResultMap">
SELECT * FROM book e
left JOIN publisher d ON e.publisher_id = d.id
</select>
嵌套查询
<resultMap id="bookResultMap" type="com.entity.Book">
<!–主键属性–>
<id property="id" column="id" jdbcType="VARCHAR"></id>
<!–普通属性–>
<result property="name" column="name" jdbcType="VARCHAR"></result>
<result property="author" column="author" jdbcType="VARCHAR"></result>
<result property="price" column="price" jdbcType="VARCHAR"></result>
<association column="publisher_id" property="pub"
javaType="com.entity.Publisher" select="selectPublisher"></association>
</resultMap>
<!–书籍查询–>
<select id="selectAllBook" resultMap="bookResultMap">
select * from book
</select>
<!–出版社映射Map–>
<resultMap id="publisherResultMap" type="com.entity.Publisher">
<id property="id" column="id" jdbcType="VARCHAR"></id>
<result property="name" column="name" jdbcType="VARCHAR"></result>
<result property="phone" column="phone" jdbcType="VARCHAR"></result>
<result property="address" column="address" jdbcType="VARCHAR"></result>
</resultMap>
<!–嵌套查询–>
<select id="selectPublisher" resultMap="publisherResultMap">
SELECT * FROM publisher d
WHERE d.id = #{publisher_id}
</select>
collection标签
<collection>和<association>标签属性基本相同，就多了一个ofType属性。
实体类
/**
*出版社
*/
@Data
public class Publisher {
private String id;
private String name;
private String phone;
private String address;
// 书籍列表
List<Book> bookList;//一个出版社对应多本书
}
/**
*书籍
*/
@Data
public class Book {
private String id;
private String name;
private String author;
private Double price;
}
XML
关联查询
<resultMap id="publisherResultMap" type="com.entity.Publisher">
<id property="id" column="id" jdbcType="VARCHAR"></id>
<result property="name" column="name" jdbcType="VARCHAR"></result>
<result property="phone" column="phone" jdbcType="VARCHAR"></result>
<result property="address" column="address" jdbcType="VARCHAR"></result>
<collection property="bookList" ofType="com.entity.Book">
<id property="id" column="id" jdbcType="VARCHAR"></id>
<result property="name" column="name" jdbcType="VARCHAR"></result>
<result property="author" column="author" jdbcType="VARCHAR"></result>
<result property="price" column="price" jdbcType="VARCHAR"></result>
</collection>
</resultMap>
<select id="selectAllPublisher" resultMap="publisherResultMap">
SELECT * FROM publisher d
left JOIN book e ON e.publisher_id = d.id
</select>
嵌套查询
<resultMap id="publisherResultMap" type="com.entity.Publisher">
<id property="id" column="id" jdbcType="VARCHAR"></id>
<result property="name" column="name" jdbcType="VARCHAR"></result>
<result property="phone" column="phone" jdbcType="VARCHAR"></result>
<result property="address" column="address" jdbcType="VARCHAR"></result>
<collection column="id" property="bookList"
javaType="java.util.ArrayList" ofType="com.entity.Book"
select="selectBookList"/>
</resultMap>
<select id="selectAllPublisher" resultMap="publisherResultMap">
SELECT * FROM publisher d
</select>
<resultMap id="bookResultMap" type="com.worldly.config.entity.Employee">
<id property="id" column="id" jdbcType="VARCHAR"></id>
<result property="name" column="name" jdbcType="VARCHAR"></result>
<result property="author" column="author" jdbcType="VARCHAR"></result>
<result property="price" column="price" jdbcType="VARCHAR"></result>
</resultMap>
<select id="selectBookList" resultMap="bookResultMap">
SELECT * FROM book e
WHERE e.publisher_id = #{id}
</select>
多条件查询
修改collection标签的column属性，{参数名1=列名1,参数名2=列名2}
/**
*出版社
*/
@Data
public class Publisher {
private String id;
private String name;
private String phone;
private String address;
// 新增—状态
private String status;
// 书籍列表
List<Book> bookList;//一个出版社对应多本书
}
/**
*书籍
*/
@Data
public class Book {
private String id;
private String name;
private String author;
private Double price;
// 新增—状态
private String status;
}
<!–修改collection标签的column属性–>
<collection column="{publisherId=id,status=status}" property="bookList"
javaType="java.util.ArrayList" ofType="com.entity.Book"
select="selectBookList"/>
<select id="selectEmpBydepId" resultMap="empResultMap">
SELECT * FROM book e
WHERE e.publisher_id = #{publisherId} AND e.status=#{status}
</select>
jdbc Type与java Type对照表
转自“https://blog.csdn.net/csdnzhang365/article/details/129747492”