ssm mysql多表查询_3.SSM整合_多表_一对多的增删改查

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

一

接口

public interface CategoryMapper {

public void addCategory(Category category);

public void delCategory(String id);

public void updateCategory(Category cateory);

//根据name模糊查询

public List selectCategory(String name);

//查询所有

public List selectAll();

//根据id查询

public Category selectById(String id);

//查询分类下的所有信息

public List getNewsWithCate(String id);

}

映射文件

insert into t_category(name, createtime)

value(#{name}, #{createtime});

delete from t_category

where id = #{id}

update t_category

set name = #{name}, createtime = #{createtime}

where id = #{id}

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 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 id, title, author

from t_news

where category_id = #{id}

select id, name ,createtime

from t_category

where id = #{id}

select *

from t_news

where category_id = #{id}

实体类

public class Category {

private Integer id;

private String name;

private String createtime;

//一对多关联

private List news;

//省略getter和setter

多

接口

public interface NewsMapper {

public void addNew(News news);

public void delNew(String id);

public void updateNew(News news);

//模糊查询

public List selectNew(String name);

//查询所有

public List selectAll();

//根据id查询

public News selectById(String id);

//获取分类的名称

public List getCategoryName();

}

映射文件

insert into t_news(content, title, author, createtime, category_id)

value(#{content},#{title},#{author},#{createtime},#{category.id})

delete from t_news where id = #{id}

update t_news

set

content = #{content},

title = #{title},

author = #{author},

createtime = #{createtime},

category_id = #{category.id}

where

id = #{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

where t1.title like "%"#{name}"%"

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 id, name

from t_category

where id = #{id}

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 id, name

from t_category

实体类

public class News {

private Integer id;

private String content;

private String title;

private String createtime;

private Category category;//外键

private String author;

//省略getter和setter

}