整体结构
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.example.demo1.mapper.BookMapper">
<resultMap id="resultMap" type="com.example.demo1.model.Book">
<constructor>
<idArg column="id" jdbcType="INTEGER" javaType="java.lang.Integer"/>
<arg column="name" jdbcType="VARCHAR" javaType="java.lang.String"/>
<arg column="author" jdbcType="VARCHAR" javaType="java.lang.String"/>
<arg column="publish" jdbcType="VARCHAR" javaType="java.lang.String"/>
<arg column="pages" jdbcType="INTEGER" javaType="java.lang.Integer"/>
<arg column="price" jdbcType="DECIMAL" javaType="java.math.BigDecimal"/>
<arg column="bookcaseid" jdbcType="INTEGER" javaType="java.lang.Integer"/>
<arg column="abled" jdbcType="INTEGER" javaType="java.lang.Integer"/>
</constructor>
</resultMap>
<sql id="Base_Column_List">
`id`, `name`, `author`, `publish`, `pages`, `price`, `bookcaseid`, `abled`
</sql>
<sql id="Query_Page">
limit ${pageIndex},${pageSize}
</sql>
<delete id="delete" parameterType="java.lang.Integer">
delete from book where id=#{id}
</delete>
<update id="updatebook" parameterType="com.example.demo1.model.Book">
update book set name=#{name},author=#{author},publish=#{publish},pages=#{pages},price=#{price},bookcaseid=#{bookcaseid},abled=#{abled} where id=#{id}
</update>
<select id="select" resultMap="resultMap" parameterType="com.example.demo1.model.Book">
select
<include refid="Base_Column_List"/>
from book
where abled=1
<if test=" name != null and name != '' ">
and name like concat('%',#{name,jdbcType=VARCHAR},'%')
</if>
</select>
<insert id="insert" parameterType="com.example.demo1.model.Book">
insert into book(name,author,publish,pages,price,bookcaseid,abled)value (#{name},#{author},#{publish},#{pages},#{price},#{bookcaseid},#{abled})
</insert>
</mapper>
BookMapper
```java
package com.example.demo1.mapper;
import com.example.demo1.model.Book;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Component;
import java.util.List;
@Mapper
@Component("BookMapper")
public interface BookMapper {
List<Book> select(Book book);
int delete(Integer id);
int insert(Book book);
int updatebook(Book book);
}
``
实体类Book
(这里用了lombok插件,并且导入了相关的依赖,自动的为我们生成get和set方法)
```java
package com.example.demo1.model;
import lombok.Data;
import java.math.BigDecimal;
@Data
public class Book {
Integer id;
String name;
String author;
String publish;
Integer pages;
BigDecimal price;
Integer bookcaseid;
Integer abled;
public Book(Integer id, String name, String author, String publish, Integer pages, BigDecimal price, Integer bookcaseid, Integer abled) {
this.id = id;
this.name = name;
this.author = author;
this.publish = publish;
this.pages = pages;
this.price = price;
this.bookcaseid = bookcaseid;
this.abled = abled;
}
public Book() {
super();
}
}
service下的BookServiceImpl和BookService
BookServiceImpl
package com.example.demo1.service.impl;
import com.example.demo1.mapper.BookMapper;
import com.example.demo1.model.Book;
import com.example.demo1.service.BookService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class BookServiceImpl implements BookService {
@Autowired
BookMapper bookMapper;
@Override
public List<Book> select(Book book) {
return bookMapper.select(book);
}
@Override
public int delete(Integer id) {
return bookMapper.delete(id);
}
@Override
public int insert(Book book){return bookMapper.insert(book);}
@Override
public int updatebook(Book book) {
return bookMapper.updatebook(book);
}
}
package com.example.demo1.service;
import com.example.demo1.model.Book;
import java.util.List;
public interface BookService {
List<Book> select(Book book);
int delete(Integer id);
int insert(Book book);
int updatebook(Book book);
}
BookController
package com.example.demo1.controller;
import com.example.demo1.model.Book;
import com.example.demo1.service.BookService;
import com.example.demo1.service.impl.BookServiceImpl;
import com.example.demo1.utils.Constants;
import com.example.demo1.utils.JsonResult;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.math.BigDecimal;
import java.util.List;
@RestController
@RequestMapping("/book")
public class BookController {
@Autowired
BookService bookService;
//根据姓名查询
@RequestMapping(value = "/getBookList")
public JsonResult select(@RequestParam(value = "pageIndex", defaultValue = "0") Integer pageIndex,
@RequestParam(value = "pageSize", defaultValue = "10") Integer pageSize,
@RequestParam(value = "name", defaultValue = "") String name){
JsonResult jsonResult;
try{
Book book = new Book();
book.setName(name);
List<Book> list = bookService.select(book);
jsonResult = new JsonResult(Constants.STATUS_SUCCESS, "查询成功", list);
}catch (Exception ex){
ex.printStackTrace();
jsonResult = new JsonResult(Constants.STATUS_ERROR, "查询异常");
}
return jsonResult;
}
//更新图书信息
@RequestMapping(value = "/updatebook")
public JsonResult updatebook(@RequestParam(value = "name") String name,
@RequestParam(value = "author") String author,
@RequestParam(value = "publish") String publish,
@RequestParam(value = "pages") Integer pages,
@RequestParam(value = "price") BigDecimal price,
@RequestParam(value = "bookcaseid") Integer bookcaseid,
@RequestParam(value = "abled") Integer abled,
@RequestParam(value = "id") Integer id){
JsonResult jsonResult;
try{
Book book= new Book();
book.setName(name);
book.setAuthor(author);
book.setPublish(publish);
book.setPages(pages);
book.setPrice(price);
book.setBookcaseid(bookcaseid);
book.setAbled(abled);
book.setId(id);
int i=bookService.updatebook(book);
jsonResult =new JsonResult(Constants.STATUS_SUCCESS,"更新成功");
}catch ( Exception ex){
ex.printStackTrace();
jsonResult=new JsonResult(Constants.STATUS_ERROR,"删除异常");
}
return jsonResult;
}
//插入数据
@RequestMapping(value = "/insertbook")
public JsonResult insert(@RequestParam(value = "name") String name,
@RequestParam(value = "author") String author,
@RequestParam(value = "publish") String publish,
@RequestParam(value = "pages") Integer pages,
@RequestParam(value = "price") BigDecimal price,
@RequestParam(value = "bookcaseid") Integer bookcaseid,
@RequestParam(value = "abled") Integer abled){
JsonResult jsonResult;
try{
Book book= new Book();
book.setName(name);
book.setAuthor(author);
book.setPublish(publish);
book.setPages(pages);
book.setPrice(price);
book.setBookcaseid(bookcaseid);
book.setAbled(abled);
int i= bookService.insert(book);
jsonResult =new JsonResult(Constants.STATUS_SUCCESS,"插入成功");
}catch(Exception ex){
ex.printStackTrace();
jsonResult=new JsonResult(Constants.STATUS_ERROR,"删除异常");
}
return jsonResult;
}
//根据id删除
@RequestMapping(value = "/deletebook")
public JsonResult delete(@RequestParam(value ="id") Integer id){
JsonResult jsonResult;
try{
bookService.delete(id);
// if(i == 0){
// jsonResult = new JsonResult(Constants.STATUS_FAIL, "删除失败");
// return jsonResult;
// }
jsonResult =new JsonResult(Constants.STATUS_SUCCESS,"删除成功");
}catch ( Exception ex){
ex.printStackTrace();
jsonResult=new JsonResult(Constants.STATUS_ERROR,"删除异常");
}
return jsonResult;
}
}
工具类
Constants
package com.example.demo1.utils;
public class Constants {
public static final String STATUS_SUCCESS="200";
public static final String STATUS_NOT_FOUND="404";
public static final String STATUS_FAIL="500";
public static final String STATUS_ERROR="400";
}
JsonResult
package com.example.demo1.utils;
public class JsonResult {
private String status;
private String message;
private Object data;
public String getStatus() {
return this.status;
}
public void setStatus(String status) {
this.status = status;
}
public String getMessage() {
return this.message;
}
public void setMessage(String message) {
this.message = message;
}
public Object getData() {
return this.data;
}
public void setData(Object data) {
this.data = data;
}
public JsonResult() {
super();
}
public JsonResult(String status) {
this.status = status;
}
public JsonResult(String status, String message) {
this.status = status;
this.message = message;
}
public JsonResult(String status, String message, Object data) {
this.status = status;
this.message = message;
this.data = data;
}
}
application.yml
server:
port: 8088
servlet:
context-path: /demo
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/library?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true&serverTimezone=Hongkong
username: root
password: 123456
mybatis:
mapper-locations: classpath*:mapper/*.xml
type-aliases-package: com.example.demo1.model
library.sql
/*
Navicat Premium Data Transfer
Source Server : 本地连接
Source Server Type : MySQL
Source Server Version : 50725
Source Host : localhost:3306
Source Schema : library
Target Server Type : MySQL
Target Server Version : 50725
File Encoding : 65001
Date: 12/04/2020 11:25:09
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`author` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`publish` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`pages` int(10) NULL DEFAULT NULL,
`price` decimal(10, 2) NULL DEFAULT NULL,
`bookcaseid` int(10) NULL DEFAULT NULL,
`abled` int(10) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES (1, '丁帅历险记', '帅帅', '帅帅出版社', 15, 21.50, 30, 4);
INSERT INTO `book` VALUES (2, '追风筝的人', '卡勒德·胡赛尼', '中信出版社', 330, 26.00, 1, 1);
INSERT INTO `book` VALUES (3, '人间失格', '太宰治', '作家出版社', 150, 17.30, 1, 1);
INSERT INTO `book` VALUES (4, '这就是二十四节气', '高春香', '电子工业出版社', 220, 59.00, 3, 1);
INSERT INTO `book` VALUES (5, '白夜行', '东野圭吾', '南海出版公司', 300, 27.30, 4, 1);
INSERT INTO `book` VALUES (6, '摆渡人', '克莱儿·麦克福尔', '百花洲文艺出版社', 225, 22.80, 1, 1);
INSERT INTO `book` VALUES (7, '暖暖心绘本', '米拦弗特毕', '湖南少儿出版社', 168, 131.60, 5, 1);
INSERT INTO `book` VALUES (8, '天才在左疯子在右', '高铭', '北京联合出版公司', 330, 27.50, 6, 1);
INSERT INTO `book` VALUES (10, '活着', '余华', '作家出版社', 100, 100.00, 6, 1);
INSERT INTO `book` VALUES (11, '水浒传', '施耐庵', '三联出版社', 300, 50.00, 1, 1);
INSERT INTO `book` VALUES (12, '三国演义', '罗贯中', '三联出版社', 300, 50.00, 2, 1);
INSERT INTO `book` VALUES (13, '红楼梦', '曹雪芹', '三联出版社', 300, 50.00, 5, 1);
INSERT INTO `book` VALUES (14, '西游记', '吴承恩', '三联出版社', 300, 60.00, 3, 1);
INSERT INTO `book` VALUES (16, '丁帅历险记', '帅帅', '帅帅出版社', 101, 21.50, 30, 4);
INSERT INTO `book` VALUES (17, '丁帅历险记', '帅帅', '帅帅出版社', 101, 21.50, 30, 4);
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '姚明', '25');
INSERT INTO `user` VALUES (2, '丁帅', '123456');
SET FOREIGN_KEY_CHECKS = 1;