MyBatis一对多
Mysql对应的表
DROP TABLE IF EXISTS `article`;
CREATE TABLE `article` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`uid` varchar(255) DEFAULT NULL,
`uname` varchar(255) DEFAULT NULL,
`ctime` datetime DEFAULT NULL,
`content` text,
PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
#
# Structure for table "comment"
#
DROP TABLE IF EXISTS `comment`;
CREATE TABLE `comment` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`aid` int(11) DEFAULT NULL,
`uid` int(11) DEFAULT NULL,
`uname` varchar(255) DEFAULT NULL,
`ctime` datetime DEFAULT NULL,
`content` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;
#
# Structure for table "reply"
#
DROP TABLE IF EXISTS `reply`;
CREATE TABLE `reply` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`aid` varchar(255) DEFAULT NULL,
`cid` varchar(255) DEFAULT NULL,
`utm` varchar(255) DEFAULT NULL,
`utmid` int(11) DEFAULT NULL,
`ufm` varchar(255) DEFAULT NULL,
`ufmid` int(11) DEFAULT NULL,
`ctime` datetime DEFAULT NULL,
`content` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
#
# Structure for table "user"
#
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`username` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
Mybatis对应ResultMap
<resultMap id="BaseResultMap" type="com.demo121.blog.model.vo.ArticleVO" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="atitle" property="title" jdbcType="VARCHAR" />
<result column="auid" property="uid" jdbcType="VARCHAR" />
<result column="auname" property="uname" jdbcType="VARCHAR" />
<result column="actime" property="ctime" jdbcType="TIMESTAMP" />
<result column="acontent" property="content" jdbcType="VARCHAR" />
<collection property="comments" ofType="com.demo121.blog.model.vo.CommentVO" column="id">
<id column="cid" property="id"/>
<result column="cuname" property="uname"/>
<result column="cuid" property="uid"/>
<result column="cctime" property="ctime"/>
<result column="ccontent" property="content"/>
<result column="caid" property="aid" />
<collection property="replyVOList" ofType="com.demo121.blog.model.vo.ReplyVO">
<id column="rid" property="id"/>
<result column="raid" property="aid"/>
<result column="rcid" property="cid"/>
<result column="rutm" property="utm"/>
<result column="rutmid" property="utmid"/>
<result column="rufm" property="ufm"/>
<result column="rufmid" property="ufmid"/>
<result column="rctime" property="ctime"/>
<result column="rcontent" property="content"/>
</collection>
</collection>
</resultMap>
<select id="getAll1" parameterType="java.lang.Integer" resultMap="BaseResultMap">
SELECT
a.id as id,
a.title as atitle,
a.uid as auid,
a.uname as auname,
a.ctime as actime,
a.content as acontent,
c.id as cid,
c.uname as cuname,
c.uid as cuid,
c.ctime as cctime,
c.content as ccontent,
c.aid as caid,
r.id as rid,
r.cid as rcid,
r.aid as raid,
r.utm as rutm,
r.utmid as rutmid,
r.ufm as rufm,
r.ufmid as rufmid,
r.ctime as rctime,
r.content as rcontent
FROM
article a
LEFT JOIN comment c ON a.id = c.aid
LEFT JOIN reply r ON r.aid = a.id and r.cid = c.id
where a.id =#{id,jdbcType=INTEGER}
</select>
其中 column 映射的是 sql查询结果字段 ,property对应的是模型层的字段
package com.demo121.blog.model.po;
import java.util.Date;
public class Article {
private Integer id;
private String title;
private String uid;
private String uname;
private Date ctime;
private String content;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title == null ? null : title.trim();
}
public String getUid() {
return uid;
}
public void setUid(String uid) {
this.uid = uid == null ? null : uid.trim();
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname == null ? null : uname.trim();
}
public Date getCtime() {
return ctime;
}
public void setCtime(Date ctime) {
this.ctime = ctime;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content == null ? null : content.trim();
}
}
package com.demo121.blog.model.po;
import java.util.Date;
public class Comment {
private Integer id;
private String uname;
private Integer uid;
private Date ctime;
private String content;
private Integer aid;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname == null ? null : uname.trim();
}
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid == null ? null : uid;
}
public Date getCtime() {
return ctime;
}
public void setCtime(Date ctime) {
this.ctime = ctime == null ? null : ctime;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content == null ? null : content.trim();
}
public Integer getAid() {
return aid;
}
public void setAid(Integer aid) {
this.aid = aid;
}
}
package com.demo121.blog.model.po;
import lombok.Data;
import java.util.Date;
@Data
public class Reply {
private Integer id;
private String aid;
private String cid;
private String utm;
private Integer utmid;
private String ufm;
private Integer ufmid;
private Date ctime;
private String content;
}
package com.demo121.blog.model.po;
public class User {
private Integer id;
private String name;
private String username;
private String password;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name == null ? null : name.trim();
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username == null ? null : username.trim();
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password == null ? null : password.trim();
}
}
package com.demo121.blog.model.vo;
import com.demo121.blog.model.po.Article;
import com.demo121.blog.model.po.Comment;
import java.util.List;
public class ArticleVO extends Article {
private List<CommentVO> comments;
public List<CommentVO> getComments() {
return comments;
}
public void setComments(List<CommentVO> comments) {
this.comments = comments;
}
}
package com.demo121.blog.model.vo;
import com.demo121.blog.model.po.Comment;
import com.demo121.blog.model.po.Reply;
import java.util.List;
public class CommentVO extends Comment {
List<ReplyVO> replyVOList;
public List<ReplyVO> getReplyVOList() {
return replyVOList;
}
public void setReplyVOList(List<ReplyVO> replyVOList) {
this.replyVOList = replyVOList;
}
}
package com.demo121.blog.model.vo;
import com.demo121.blog.model.po.Reply;
public class ReplyVO extends Reply {
}
package com.demo121.blog.web;
import com.demo121.blog.mapper.*;
import com.demo121.blog.model.po.Comment;
import com.demo121.blog.model.po.Reply;
import com.demo121.blog.model.po.User;
import com.demo121.blog.model.vo.ArticleVO;
import com.demo121.blog.service.GetArticleInfo;
import com.demo121.blog.service.UserAuth;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.util.Date;
import java.util.List;
@Slf4j
@Controller
public class WebController {
@Autowired
private UserAuth userAuth;
@Autowired
private GetArticleInfo getArticleInfo;
@Autowired
private ReplyMapper replyMapper;
@Autowired
private CommentMapper commentMapper;
@Autowired
private UserMapper userMapper;
@Autowired
private ArticleInfoMapper articleInfoMapper;
@GetMapping("articles")
public String articleLists(ModelMap map){
map.put("articlesList" ,getArticleInfo.getArticleOrderTime());
return "articlelist";
}
@GetMapping("p/{id}")
public String getAll(@PathVariable("id") Integer id,ModelMap map){
ArticleVO articleVO = articleInfoMapper.getAll1(id);
map.put("articleInfo",articleVO);
return "article";
}
@ResponseBody
@GetMapping("p2/{id}")
public Object getAll1(@PathVariable("id") Integer id,ModelMap map){
return articleInfoMapper.getAll1(id);
}
@PostMapping("comment")
public void comment(HttpServletRequest request,Comment comment,ModelMap map,HttpServletResponse response) throws IOException {
HttpSession session = request.getSession();
User user = (User)session.getAttribute("user");
if(user==null){
response.sendRedirect("login");
return;
}
comment.setUname(user.getName());
comment.setUid(user.getId());
comment.setCtime(new Date());
comment.setUname(user.getName());
commentMapper.insert(comment);
getAll1(comment.getAid(),map);
response.sendRedirect("/p/"+comment.getAid());
}
@GetMapping("login")
public String loginPage(){
return "login";
}
@PostMapping("login")
public String login(HttpServletRequest request,User user,HttpServletResponse response) throws IOException {
HttpSession session = request.getSession();
User user1 = userAuth.login(user);
if(user1 != null)
{
session.setAttribute("user",user1);
response.sendRedirect("/p/1");
return null;
}
else
{
return "error";
}
}
@PostMapping("reply")
public void reply(HttpServletRequest request, Reply reply, HttpServletResponse response) throws IOException {
HttpSession session = request.getSession();
User user = (User)session.getAttribute("user");
reply.setUfm( user.getName());
reply.setUfmid(user.getId());
Integer id = Integer.parseInt(reply.getAid());
log.info(reply.toString());
replyMapper.insert(reply);
response.sendRedirect("/p/"+id);
}
@ResponseBody
@GetMapping("/u/{id}")
public Object getUser(@PathVariable("id") Integer id){
return userMapper.selectByPrimaryKey(id);
}
}
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title></title>
<link rel="stylesheet" href="/css/bootstrap.min.css">
<style>
.hidden{display:hidden;}
</style>
</head>
<body>
<#if articleInfo ??>
<div>
<h1>${articleInfo.title}</h1>
<a href="/u/${articleInfo.uid}"><span>${articleInfo.uname}</span></a>
<h5>时间:${articleInfo.ctime?string("yyyy-MM-dd HH:mm:ss")}</h5>
<div>
<p>${articleInfo.content}</p>
</div>
<div class="container">
<form class="bs-example bs-example-form" role="form" method="POST" action="/comment">
<div class="input-group">
<textarea type="text" class="form-control" autofocus name="content" placeholder="twitterhandle">
</textarea>
<input type="hidden" name="aid" value="${articleInfo.id}">
</div>
<br>
<button type="submit" class="btn btn-success">评论</button>
</form>
</div>
<h1>评论</h1>
<div class="container">
<h2>${articleInfo.comments?size}条评论</h2>
<#list articleInfo.comments?reverse as comment>
<div class="">
<h4><img src="/head.jpg" style="width: 40px ;height: 40;border-radius: 50%;border: 1px #eee solid">${comment.uname}</h4>
${articleInfo.comments?size - comment_index}楼 ${comment.ctime?string("yyyy-MM-dd HH:mm:ss")}
<h4>${comment.content}</h4>
<#if Session.user?exists>
<#if Session.user.id !=comment.uid>
<a href="javascript:void(0)" onclick="show(this)">回复</a>
<div hidden>
<form action="/reply" method="post">
<textarea name="content"></textarea>
<input type="hidden" name="aid" value="${articleInfo.id}">
<input type="hidden" name="cid" value="${comment.id}">
<input type="hidden" name="utm" value="${comment.uname}">
<input type="hidden" name="utmid" value="${comment.uid}">
<button class="btn btn-success" type="submit">回复</button>
</form>
</div>
</#if>
<#else>
<a href="javascript:void(0)" onclick="show(this)">回复</a>
<div hidden>
<form action="/reply" method="post">
<textarea name="content"></textarea>
<input type="hidden" name="aid" value="${articleInfo.id}">
<input type="hidden" name="cid" value="${comment.id}">
<input type="hidden" name="utm" value="${comment.uname}">
<input type="hidden" name="utmid" value="${comment.uid}">
<button class="btn btn-success" type="submit">回复</button>
</form>
</div>
</#if>
</div>
<script>
function show(e){
$(e).next().toggle();
}
</script>
<#list comment.replyVOList as reply >
<div><a href="/u/${reply.ufmid}">${reply.ufm}</a>:<a href="/u/${reply.utmid}">@${reply.utm}</a> ${reply.content}
<a href="javascript:void(0)" onclick="show(this)">回复</a>
<div hidden>
<form action="/reply" method="post">
<textarea name="content"></textarea>
<input type="hidden" name="aid" value="${articleInfo.id}">
<input type="hidden" name="cid" value="${comment.id}">
<input type="hidden" name="utm" value="${reply.utm}">
<input type="hidden" name="utmid" value="${reply.utmid}">
<button class="btn btn-success" type="submit">回复</button>
</form>
</div>
</div>
</#list>
</#list>
</div>
</div>
</#if>
<script type="text/javascript" src="/js/jquery-3.3.1.min.js"></script>
<script type="text/javascript" src="/js/bootstrap.min.js"></script>
</body>
</html>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title></title>
</head>
<body>
<#if articlesList ??>
<#list articlesList as item>
<div>
<h1><a href="/p/${item.id}" >${item.title}</a></h1>
<a href="/u/${item.uid}"><span>${item.uname}</span></a>
<h5>时间:${item.ctime?string("yyyy-MM-dd HH:mm:ss")}</h5>
<div>
<#if item.content?length lt 200>
<p>${item.content?html}</p>
<#else>
<p>${item.content[0..201]?html}...</p>
</#if>
</div>
</div>
</#list>
</#if>
</body>
</html>