2.SSM整合_多表_一对一或多对一的增删改查

一对一和多对一配置一样,这里就放到一起。

1.配置文件跟上一章一样,这里就不多写了,主要是Mapper映射文件

接口

public interface NewsMapper {

	public void addNew(News news);

	public void delNew(String id);

	public void updateNew(News news);

	//模糊查询
public List<News> selectNew(String name); //查询所有
public List<News> selectAll(); //根据id查询
public News selectById(String id); //获取分类的名称
public List<String> getCategoryName();
}

映射文件

<mapper namespace="com.demo1.mapper.NewsMapper">
<!-- 添加操作和单表添加差不多,唯一的区别就是外键字段 -->
<insert id="addNew" parameterType="News" >
insert into t_news(content, title, author, createtime, category_id)
value(#{content},#{title},#{author},#{createtime},#{category.id})
</insert> <delete id="delNew" parameterType="String">
delete from t_news where id = #{id}
</delete> <!-- 修改操作和单表修改差不多,唯一的区别就是外键字段 -->
<update id="updateNew" parameterType="News">
update t_news
set
content = #{content},
title = #{title},
author = #{author},
createtime = #{createtime},
category_id = #{category.id}
where
id = #{id}
</update>
<!-- 多对一(配置方式和一对一一样)多表查询 -->
<!-- 查询有两种方式, -->
<!--方式一嵌套结果: 就是把所有的字段都映射,一条SQL连表查询,其中外键使用 <association>标签映射-->
<resultMap type="News" id="newsResultMap">
<!-- property 表示bean中的属性; column 表示表中的列别名 -->
<id column="Id" property="id"/>
<!-- property 表示bean中的属性; column 表示表中的列别名 -->
<result column="Content" property="content"/>
<!-- property 表示bean中的属性; column 表示表中的列别名 -->
<result column="Title" property="title"/>
<!-- property 表示bean中的属性; column 表示表中的列别名 -->
<result column="Author" property="author"/>
<!-- javaType的属性值设置为String是为了显示成:2018-12-5 12:30:10这样 -->
<result column="Createtime" jdbcType="TIMESTAMP" property="createtime" javaType="String"/>
<!-- 映射外键字段,该标签的column属性值为表外键字段列名,而非列别名 -->
<association column="category_id" property="category" jdbcType="INTEGER" javaType="Category">
<!-- property 表示bean中的属性; column 表示表中的列别名 -->
<id column="Categoryid" property="id"/>
<!-- property 表示bean中的属性; column 表示表中的列别名 -->
<result column="Name" property="name"/>
<!-- 不管SQL语句中有没有查询某字段,如果别名同名,mySQL会自动在别名后加上序号,从1开始。
就会导致同时同名的字段会映射同一个数据,
比如:这里的column和上面的column中同名了,那这里的property属性值会和上面的property属性值一样
-->
<!-- <result column="Createtime" jdbcType="TIMESTAMP" property="createtime" javaType="String"/> -->
</association>
</resultMap>
<!-- 根据name模糊查询,左链接查询,若外键无值,则字段为空 -->
<select id="selectNew" parameterType="String" resultMap="newsResultMap">
select t1.id Id, t1.content Content, t1.title Title, t1.author Author, t1.createtime Createtime,
t2.id Categoryid, t2.name Name
from t_news t1
left join t_category t2
on t1.category_id = t2.id
where t1.title like "%"#{name}"%"
</select>
<!-- 查询所有 -->
<select id="selectAll" resultMap="newsResultMap">
<!-- 连表查询:若外键无值,则整条记录去掉 -->
<!-- select t1.id, t1.content, t1.title, t1.author, t1.createtime, t2.name from t_news t1, t_category t2 where t1.category_id = t2.id -->
<!-- 左链接查询,若外键无值,则字段为空 -->
select t1.id Id, t1.content Content, t1.title Title, t1.author Author, t1.createtime Createtime,
t2.id Categoryid, t2.name Name
from t_news t1
left join t_category t2
on t1.category_id = t2.id
</select> <!-- 方式二:嵌套查询,使用两条SQL执行查询,两条SQL单独查询 -->
<resultMap type="News" id="newsResultMap2">
<!-- property 表示bean中的属性; column 表示表中的列别名 -->
<id column="Id" property="id"/>
<!-- property 表示bean中的属性; column 表示表中的列别名 -->
<result column="Content" property="content"/>
<!-- property 表示bean中的属性; column 表示表中的列别名 -->
<result column="Title" property="title"/>
<!-- property 表示bean中的属性; column 表示表中的列别名 -->
<result column="Author" property="author"/>
<!-- javaType的属性值设置为String是为了显示成:2018-12-5 12:30:10这样 -->
<result column="Createtime" jdbcType="TIMESTAMP" property="createtime" javaType="String"/>
<!-- 映射外键字段,该标签的column属性值为表外键字段列名,而非列别名,其值为ID为selectById的select标签查询出的category_id字段 -->
<association column="category_id" property="category" jdbcType="INTEGER" javaType="Category" select="getCategory">
</association>
</resultMap>
<select id="getCategory" parameterType="int" resultType="Category">
select id, name
from t_category
where id = #{id}
</select>
<!-- 根据id查询 :修改时调用
查询字段:t_news表:id,content,title,author,createtime
t_category表:id,name
-->
<select id="selectById" parameterType="String" resultMap="newsResultMap2">
select t1.id Id, t1.content Content, t1.title Title, t1.author Author, t1.createtime Createtime, t1.category_id category_id
from t_news t1
where t1.id = #{id}
</select> <!-- 查询分类的name -->
<select id="getCategoryName" resultType="Category">
select id, name
from t_category
</select>
</mapper>

实体类

public class News {

	private Integer id;
private String content;
private String title;
private String createtime;
private Category category;//外键
private String author;
//省略getter和setter
}

接口

public interface CategoryMapper {

	public void addCategory(Category category);

	public void delCategory(String id);

	public void updateCategory(Category cateory);

	//根据name模糊查询
public List<Category> selectCategory(String name); //查询所有
public List<Category> selectAll(); //根据id查询
public Category selectById(String id); //查询分类下的所有信息
public List<News> getNewsWithCate(String id);
}

映射文件

<mapper namespace="com.demo1.mapper.CategoryMapper">
<!-- 添加 -->
<insert id="addCategory" parameterType="Category">
insert into t_category(name, createtime)
value(#{name}, #{createtime});
</insert> <!-- 删除 -->
<delete id="delCategory" parameterType="String">
delete from t_category
where id = #{id}
</delete> <!-- 更新 -->
<update id="updateCategory" parameterType="Category">
update t_category
set name = #{name}, createtime = #{createtime}
where id = #{id}
</update> <!-- 查询:一对多,多表查询 -->
<!-- 方式一:嵌套结果,一条SQL多表连表查询,所有的字段都做映射 -->
<resultMap type="Category" id="categoryResultMap">
<id column="Id" property="id"/>
<result column="Name" property="name"/>
<result column="Createtime" jdbcType="TIMESTAMP" property="createtime" javaType="String"/>
<!-- 没有外键字段 -->
<collection property="news" javaType="ArrayList" ofType="News">
<id column="NewId" property="id"/>
<result column="Title" property="title"/>
<result column="Author" property="author"/>
</collection>
</resultMap> <!-- 根据name模糊查询 -->
<select id="selectCategory" parameterType="String" resultMap="categoryResultMap">
select t1.id Id, t1.name Name, t1.createtime Createtime,
t2.id NewId, t2.title Title, t2.author Author
from t_category t1
left join t_news t2
on t1.id = t2.category_id
where t1.name like "%"#{name}"%"
</select>
<!-- 查询所有 -->
<select id="selectAll" resultMap="categoryResultMap">
select t1.id Id, t1.name Name, t1.createtime Createtime,
t2.id NewId, t2.title Title, t2.author Author
from t_category t1
left join t_news t2
on t1.id = t2.category_id
</select> <!-- 方式二:嵌套查询,两条SQL,单独查询 -->
<resultMap type="Category" id="categoryResultMap2">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="createtime" jdbcType="TIMESTAMP" property="createtime" javaType="String"/>
<!-- 这个column属性不是外键,是分类的主键,其值为id为selectById的select标签查询出的id字段 -->
<collection column="id" property="news" javaType="ArrayList" ofType="News" select="getNews">
</collection>
</resultMap>
<select id="getNews" parameterType="int" resultType="News">
select id, title, author
from t_news
where category_id = #{id}
</select>
<!-- 根据id查询 -->
<select id="selectById" parameterType="String" resultMap="categoryResultMap2">
select id, name ,createtime
from t_category
where id = #{id}
</select> <!-- 查询分类下的所有信息 -->
<select id="getNewsWithCate" resultMap="categoryResultMap2">
select *
from t_news
where category_id = #{id}
</select>
</mapper>

实体类

public class Category {

	private Integer id;
private String name;
private String createtime;
//一对多关联
private List<News> news;
//省略getter和setter
上一篇:php 判断数组是否为空的几种方法


下一篇:无法解决 equal to 运算中 "Chinese_PRC_CI_AS" 和 "SQL_Latin1_General_CP1_CI_AS" 之间的排序规则冲突。