Vue + Spring Boot 项目实战（六）笔记
前言
这一篇到了数据库的设计以及后端的增删改查，@Evan-Nightly大佬用的是JPA，我这边寻思着用过一点mybatis，所以就直接换成mybatis了，于是这一换就是一下午，从注解换成xml再换成注解再换回来。之所以这么做是因为想了一下午没想通这个多表操作，俺寻思为什么不直接查询cid，而要弄多表查询。最后索性不管了，写出来再说。
建议不想折腾的直接选择JPA，跟原来教程完事。
数据库架构
book中有一个指向category的外键
项目配置
在application.properties文件中加入下面两行：
mybatis.type-aliases-package=com.sk.wjbackend.pojo
mybatis.mapper-locations=classpath:mapper/*Mapper.xml
mapper-locations后面一定要有classpath。
此时目录结构：
后端代码
pojo
为了减少代码量，后面的pojo全部用lombok简化。
新建两个pojo，Book和Category。
@Data
public class Book {
private Integer id;
private String cover;
private String title;
private String author;
private String date;
private String press;
private String abs;
private Category category;
}
@Data
public class Category {
private Integer id;
private String name;
}
dao
pojo写好后开始写dao，dao文件夹中创建两个类，BookMapper和CategoryMapper。
根据教程后面的api测试写出来对应的方法。
- BookMapper:
@Repository
public interface BookMapper {
public abstract void insert(Book book);
public abstract void update(Book book);
public abstract void delete(@Param("id") Integer id);
public abstract List<Book> selectAll();
public abstract List<Book> selectByCategory(Category category);
public abstract List<Book> selectByNameOrAuthor(@Param("key1") String key1,@Param("key2") String key2);
}
- BookMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sk.wjbackend.dao.BookMapper">
<resultMap id="book" type="Book">
<id column="id" property="id"/>
<result column="cover" property="cover"/>
<result column="title" property="title"/>
<result column="author" property="author"/>
<result column="date" property="date"/>
<result column="press" property="press"/>
<result column="abs" property="abs"/>
<association property="category" column="cid" javaType="Category">
<id column="cid" property="id"/>
<result column="cname" property="name"/>
</association>
</resultMap>
<insert id="insert" parameterType="Book">
insert into
book
values
(null,#{cover},#{title},#{author},#{date},#{press},#{abs},#{category.id})
</insert>
<update id="update" parameterType="Book">
update
book
set
cover=#{cover},title=#{title},author=#{author},date=#{date},press=#{press},abs=#{abs},cid=#{category.id}
where
id=#{id}
</update>
<delete id="delete" parameterType="int">
delete from book where id=#{id}
</delete>
<select id="selectAll" resultMap="book">
select
b.id,b.cover,b.title,b.author,b.date,b.press,b.abs,c.id cid,c.name cname
from
book b left join category c on c.id = b.cid
</select>
<select id="selectByCategory" resultMap="book" parameterType="Category">
select
b.id,b.cover,b.title,b.author,b.date,b.press,b.abs,c.id cid,c.name cname
from
book b,category c
where
b.cid=#{id}
and
c.id=#{id}
</select>
<select id="selectByNameOrAuthor" resultMap="book">
select
*
from
book
where
name like '%#{key1}%'
or
author like '%#{key2}%'
</select>
</mapper>
中间遇到了两个问题，一个是报错：Error creating bean with name 'xxx': Unsatisfiehd dependency expressed through field 'service'，原因是result里多加了个jdbctype，导致无法创建bean，因为jdbctype是指数据库中的type，正确应该是javatype，但是不加也可以用。
还有一个报错是因为xml少复制了前面两行
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
selectByCategory这部分的sql语句是：
select
b.id,b.cover,b.title,b.author,b.date,b.press,b.abs,c.id cid,c.name cname
from
book b,category c
where
b.cid=#{id}
and
c.id=#{id}
之所以这么写，而不是直接查询cid获取某一分类下的书，是因为要通过多表查询来同时得到book的信息和所属category的信息。
- CategoryMapper
@Repository
public interface CategoryMapper {
public abstract List<Category> selectAll();
public abstract Category selectById(Integer id);
}
- CategoryMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sk.wjbackend.dao.CategoryMapper">
<select id="selectAll" resultType="Category">
select
*
from
category
</select>
<select id="selectById" resultType="Category">
select
*
from
category
where
id=#{id}
</select>
</mapper>
Category的就很简单了，其他的方法暂时用不到，等用到了再写。
service
@Service
public class BookService {
@Autowired
BookMapper bookMapper;
@Autowired
CategoryService categoryService;
public List<Book> selectAll() {
return bookMapper.selectAll();
}
public void insert(Book book) {
bookMapper.insert(book);
}
public void update(Book book){
bookMapper.update(book);
}
public void delete(int id) {
bookMapper.delete(id);
}
public List<Book> selectByCategory(int cid) {
Category category = categoryService.get(cid);
return bookMapper.selectByCategory(category);
}
}
为了统一代码风格，方法名字都改下名字，毕竟mybatis没有addOrUpdate方法。
controller
@RestController
@CrossOrigin(origins = "*")
public class LibraryController {
@Autowired
BookService bookService;
@GetMapping("/api/books")
public List<Book> selectAll() throws Exception {
return bookService.selectAll();
}
@PostMapping("/api/books_insert")
public Book insert(@RequestBody Book book) throws Exception {
bookService.insert(book);
return book;
}
@PostMapping("/api/books_update")
public Book update(@RequestBody Book book) throws Exception {
bookService.update(book);
return book;
}
@PostMapping("/api/delete")
public void delete(@RequestBody Book book) throws Exception {
bookService.delete(book.getId());
}
@GetMapping("/api/categories/{cid}/books")
public List<Book> selectByCategory(@PathVariable("cid") int cid) throws Exception {
if (0 != cid) {
return bookService.selectByCategory(cid);
} else {
return selectAll();
}
}
}
- @RestController
Spring4之后新加入的注解，原来返回json需要@ResponseBody和@Controller配合。即@RestController是@ResponseBody和@Controller的组合注解。
需要注意的是不能有两个相同的PostMapping或者GetMapping，否则会报错：nvocation of init method failed; nested exception is java.lang.IllegalStateException: Ambiguous mapping. Cannot map 'libraryController' method
方法名改好之后就可以直接测试了
- /api/books
- api/categories/1/books
大功告成。