业务需求:查询所有的新闻并显示新闻类型
逻辑分析:news表左连接 topic表,每个topic_id对应一个topic_name,一对一关系
表:
create table news
(
news_id int auto_increment
primary key,
topic_id int null,
news_title varchar(200) not null,
news_author varchar(50) not null,
create_time datetime default CURRENT_TIMESTAMP null,
news_content varchar(1000) null,
news_summary varchar(500) not null
)
collate = utf8mb4_unicode_ci;
create table topic
(
topic_id int auto_increment primary key,
topic_name varchar(50) not null
) collate = utf8mb4_unicode_ci;
实体:
@TableName(value ="topic")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Topic implements Serializable {
@TableId(type = IdType.AUTO)
private Integer topicId;
private String topicName;
@TableField(exist = false)
private static final long serialVersionUID = 1L;
}
@TableName(value ="news")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class News implements Serializable {
@TableId(type = IdType.AUTO)
private Integer newsId;
private Integer topicId;
private String newsTitle;
private String newsAuthor;
private Date createTime;
private String newsContent;
private String newsSummary;
@TableField(exist = false)
private static final long serialVersionUID = 1L;
}
DTO:
@Data
@AllArgsConstructor
@NoArgsConstructor
public class NewsDTO extends News{
private Topic topic;
@Override
public String toString() {
return "NewsDTO{" +super.toString()+
"topic=" + topic +
'}';
}
}
Mapper:
@Mapper
public interface NewsMapper extends BaseMapper<News> {
int insertSelective(News news);
IPage<NewsDTO> findAllDTO(Page<NewsDTO> page);
}
Mapper.xml:
<select id="findAllDTO" resultMap="findDTO">
select *
from news n
join topic t on n.topic_id = t.topic_id
</select>
<resultMap id="findDTO" type="com.ac.springbootnews.DTO.NewsDTO" autoMapping="true" extends="BaseResultMap">
<association property="topic"javaType="com.ac.springbootnews.domain.Topic"
autoMapping="true">
</association>
</resultMap><resultMap id="BaseResultMap" type="com.ac.springbootnews.domain.News">
<id property="newsId" column="news_id" jdbcType="INTEGER"/>
<result property="topicId" column="topic_id" jdbcType="INTEGER"/>
<result property="newsTitle" column="news_title" jdbcType="VARCHAR"/>
<result property="newsAuthor" column="news_author" jdbcType="VARCHAR"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
<result property="newsContent" column="news_content" jdbcType="VARCHAR"/>
<result property="newsSummary" column="news_summary" jdbcType="VARCHAR"/>
</resultMap>
Service:
public interface NewsService extends IService<News> {
IPage<NewsDTO> getAllDTO(Page<NewsDTO> page);
}
ServiceImpl
@Service
@Transactional
public class NewsServiceImpl extends ServiceImpl<NewsMapper, News>
implements NewsService {
final
NewsMapper newsMapper;
public NewsServiceImpl(NewsMapper newsMapper) {
this.newsMapper = newsMapper;
}
@Override
public IPage<NewsDTO> getAllDTO(Page<NewsDTO> page) {
return newsMapper.findAllDTO(page);
}
}
Controller:
@GetMapping("/{page}")
@Transactional(readOnly = true)
public Result getNewsInfo(@PathVariable int page) {
Page pages = new Page(page, 15);
IPage allDTO = newsService.getAllDTO(pages);
return Result.success(allDTO);
}
测试结果: