Java EE 进阶：MyBatis案例练习

表白墙

首先我们先准备一下数据库的数据

创建一个信息表

DROP TABLE IF EXISTS message_info;CREATE TABLE `message_info` (`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,`from` VARCHAR ( 127 ) NOT NULL,`to` VARCHAR ( 127 ) NOT NULL,`message` VARCHAR ( 256 ) NOT NULL,`delete_flag` TINYINT ( 4 ) DEFAULT 0 COMMENT`create_time` DATETIME DEFAULT now(),`update_time` DATETIME DEFAULT now() ON UPDATE now(),PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

引⼊MyBatis和MySQL驱动依赖 

<dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>3.0.3</version></dependency><dependency><groupId>com.mysql</groupId><artifactId>mysql-connector-j</artifactId><scope>runtime</scope></dependency>

配置yml文件

spring:application:name: springboot-demodatasource:url: jdbc:mysql://127.0.0.1:3306/mybatis_test?characterEncoding=utf8&useSSL=falseusername: rootpassword: 123456driver-class-name: com.mysql.cj.jdbc.Driver
mybatis:configuration:log-impl: org.apache.ibatis.logging.stdout.StdOutImplmap-underscore-to-camel-case: true

先创建实体类

@Data
public class MessageInfo {private Integer id;private String from;private String to;private String message;private Integer deleteFlag;private Date creatTime;private Date updateTime;
}

创建MessageMapper

@Mapper
public interface MessageMapper {@Select("SELECT `id`, `from`, `to`, `message` FROM message_info WHERE delete_flag = 0")List<MessageInfo> findMessage();@Insert("insert into message_info (`from`,`to`,message) values (#{from},#{to},#{message})")Integer addMessage(MessageInfo messageInfo);
}

创建MessageService

@Service
public class MessageService {@Autowiredprivate MessageMapper messageMapper;private MessageInfo messageInfo;public Integer insertMessage(MessageInfo messageInfo) {return messageMapper.addMessage(messageInfo);}public List<MessageInfo> queryMessage() {return messageMapper.findMessage();}
}

 测试

 

 

 

图书管理系统

准备数据库

DROP DATABASE IF EXISTS book_test;CREATE DATABASE book_test DEFAULT CHARACTER SET utf8mb4;-- 
⽤⼾表DROP TABLE IF EXISTS user_info;CREATE TABLE user_info (`id` INT NOT NULL AUTO_INCREMENT,`user_name` VARCHAR ( 128 ) NOT NULL,`password` VARCHAR ( 128 ) NOT NULL,`delete_flag` TINYINT ( 4 ) NULL DEFAULT 0,`create_time` DATETIME DEFAULT now(),`update_time` DATETIME DEFAULT now() ON UPDATE now(),
PRIMARY KEY ( `id` ),UNIQUE INDEX `user_name_UNIQUE` ( `user_name` ASC )) ENGINE = INNODB DEFAULT 
CHARACTER 
SET = utf8mb4 COMMENT = '⽤⼾表';

DROP TABLE IF EXISTS book_info;CREATE TABLE `book_info` (`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,`book_name` VARCHAR ( 127 ) NOT NULL,`author` VARCHAR ( 127 ) NOT NULL,`count` INT ( 11 ) NOT NULL,`price` DECIMAL (7,2 ) NOT NULL,`publish` VARCHAR ( 256 ) NOT NULL,`status` TINYINT ( 4 ) DEFAULT 1 COMMENT '0⽆效, 1正常, 2不允许借阅',`create_time` DATETIME DEFAULT now(),`update_time` DATETIME DEFAULT now() ON UPDATE now(),PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;-- 
初始化数据INSERT INTO user_info ( user_name, PASSWORD ) VALUES ( "admin", "admin" );INSERT INTO user_info ( user_name, PASSWORD ) VALUES ( "zhangsan", "123456" );
INSERT INTO book_info (id, book_name, author, count, price, publish, status, create_time, update_time) VALUES
(6, '解忧杂货店', '东野圭吾', 20, 45.50, '南海出版公司', 1, '2025-03-22 13:00:00', '2025-03-22 13:00:00'),
(7, '百年孤独', '加西亚·马尔克斯', 13, 88.00, '上海译文出版社', 1, '2025-03-22 13:05:00', '2025-03-22 13:05:00'),
(8, '追风筝的人', '卡勒德·胡赛尼', 25, 39.80, '上海人民出版社', 1, '2025-03-22 13:10:00', '2025-03-22 13:10:00'),
(9, '嫌疑人X的献身', '东野圭吾', 18, 42.00, '人民文学出版社', 1, '2025-03-22 13:15:00', '2025-03-22 13:15:00'),
(10, '围城', '钱钟书', 15, 36.00, '人民文学出版社', 1, '2025-03-22 13:20:00', '2025-03-22 13:20:00'),
(11, '牧羊少年奇幻之旅', '保罗·柯艾略', 12, 29.90, '作家出版社', 1, '2025-03-22 13:25:00', '2025-03-22 13:25:00'),
(12, '红楼梦', '曹雪芹', 30, 59.00, '中华书局', 1, '2025-03-22 13:30:00', '2025-03-22 13:30:00'),
(13, '西游记', '吴承恩', 28, 48.00, '人民文学出版社', 1, '2025-03-22 13:35:00', '2025-03-22 13:35:00'),
(14, '水浒传', '施耐庵', 22, 46.50, '人民文学出版社', 1, '2025-03-22 13:40:00', '2025-03-22 13:40:00'),
(15, '三国演义', '罗贯中', 27, 49.00, '人民文学出版社', 1, '2025-03-22 13:45:00', '2025-03-22 13:45:00'),
(16, '小王子', '圣埃克苏佩里', 17, 25.00, '新星出版社', 1, '2025-03-22 13:50:00', '2025-03-22 13:50:00'),
(17, '白夜行', '东野圭吾', 19, 55.00, '南海出版公司', 1, '2025-03-22 13:55:00', '2025-03-22 13:55:00'),
(18, '人类简史', '尤瓦尔·赫拉利', 14, 89.00, '中信出版社', 1, '2025-03-22 14:00:00', '2025-03-22 14:00:00'),
(19, '未来简史', '尤瓦尔·赫拉利', 10, 92.00, '中信出版社', 1, '2025-03-22 14:05:00', '2025-03-22 14:05:00'),
(20, '我们仨', '杨绛', 8, 28.00, '生活·读书·新知三联书店', 1, '2025-03-22 14:10:00', '2025-03-22 14:10:00'),
(21, '霍乱时期的爱情', '加西亚·马尔克斯', 9, 75.00, '上海译文出版社', 1, '2025-03-22 14:15:00', '2025-03-22 14:15:00'),
(22, '目送', '龙应台', 11, 34.50, '广西师范大学出版社', 1, '2025-03-22 14:20:00', '2025-03-22 14:20:00'),
(23, '文化苦旅', '余秋雨', 16, 32.00, '中华书局', 1, '2025-03-22 14:25:00', '2025-03-22 14:25:00'),
(24, '长安的荔枝', '马伯庸', 21, 40.00, '湖南文艺出版社', 1, '2025-03-22 14:30:00', '2025-03-22 14:30:00'),
(25, '解密', '麦家', 13, 37.50, '作家出版社', 1, '2025-03-22 14:35:00', '2025-03-22 14:35:00');

引⼊MyBatis和MySQL驱动依赖

<dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>3.0.3</version></dependency><dependency><groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId><scope>runtime</scope></dependency>

 配置yml文件

spring:datasource:url: jdbc:mysql://127.0.0.1:3306/book_test?characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=trueusername: rootpassword: 123456driver-class-name: com.mysql.cj.jdbc.Drivermybatis:configuration:map-underscore-to-camel-case: truelog-impl: org.apache.ibatis.logging.stdout.StdOutImplmapper-locations: classpath:mapper/**.xml
logging:file:name: spring-book.log

UserInfo

@Data
public class UserInfo {private Integer id;private String userName;private String password;private Integer deleteFlag;private Data createTime;private Data updateTime;}

BookInfo

@Data
public class BookInfo {private Integer Id;private String bookName;private String author;private Integer count;private Integer price;private String publish;private Integer status;private String statusCN;private Date createTime;private Date updateTime;
}

PageResult

@AllArgsConstructor
@NoArgsConstructor
@Data
public class PageResult<T> {private List<T> records;private int total;private PageRequest pageRequest;public PageResult(int total,List<T> records,PageRequest pageRequest) {this.records = records;this.total = total;}
}

PageRequest

@Data
public class PageRequest {private Integer currentPage=1;private Integer pageSize=10;private Integer offset;public Integer getOffset() {return (currentPage-1)*pageSize;}
}

Result

@Data
public class Result<T>  {private ResultCodeEnum code;private String errMes;private T data;public static <T> Result success(T data){Result result=new Result();result.setCode(ResultCodeEnum.SUCCESS);result.setErrMes("");result.setData(data);return result;}public static <T> Result fail(String errMes){Result result=new Result();result.setCode(ResultCodeEnum.UNLOGIN);result.setErrMes(errMes);result.setData(null);return result;}public static <T> Result fail(String errMes,T data){Result result=new Result();result.setCode(ResultCodeEnum.FAIL);result.setErrMes(errMes);result.setData(data);return result;}public static <T> Result unLogin(){Result result=new Result();result.setCode(ResultCodeEnum.UNLOGIN);result.setErrMes("登录失败");return result;}
}

UserController

@RequestMapping("/user")
@RestController
public class userController {@Autowiredprivate UserService userService;@RequestMapping("/login")public boolean login(String name, String password, HttpSession session){if(!StringUtils.hasLength(name) || !StringUtils.hasLength(password)){return false;}UserInfo userInfo=userService.queryPasswordByUsername(name);if (userInfo==null){return false;}if(password.equals(userInfo.getPassword())){session.setAttribute(Constants.SESSION_USER_KEY,userInfo);return true;}return false;}
}

 

BookController

@RestController
@RequestMapping("/book")
public class bookController {private final BookService bookService;public bookController(BookService bookService) {this.bookService = bookService;}@RequestMapping("/getListByPage")public Result<PageResult<BookInfo>> getList(PageRequest pageRequest, HttpSession session){if(session.getAttribute(Constants.SESSION_USER_KEY)==null){return Result.unLogin();}UserInfo userinfo =(UserInfo) session.getAttribute(Constants.SESSION_USER_KEY);if(userinfo==null && userinfo.getId()<=0){return Result.unLogin();}PageResult<BookInfo> bookInfoPageResult = bookService.pageResult(pageRequest);return Result.success(bookInfoPageResult);}@RequestMapping("/addBook")public String addBook(@RequestBody BookInfo bookInfo){if (!StringUtils.hasLength(bookInfo.getBookName()) ||!StringUtils.hasLength(bookInfo.getAuthor()) ||bookInfo.getCount()==null ||bookInfo.getPrice()==null ||!StringUtils.hasLength(bookInfo.getPublish()) ||bookInfo.getStatus()==null){return "输入不合法";}try {bookService.addBook(bookInfo);return "添加成功";} catch (Exception e){return "添加失败";}}@RequestMapping("/queryBookId")public BookInfo queryBookId(@RequestParam("bookId") Integer bookId){return bookService.queryBookId(bookId);}@RequestMapping ("/updateBook")public String updateBook(@RequestBody BookInfo bookInfo){bookService.update(bookInfo);return "更新成功";}@RequestMapping ("/deleteBook")public String deleteBook(@RequestBody Integer bookId){BookInfo bookInfo=new BookInfo();bookInfo.setId(bookId);bookInfo.setStatus(BookStatus.DELETED.getCode());bookService.update(bookInfo);return "删除成功";}@RequestMapping("batchDelete")public boolean batchDelete(@RequestBody List<Integer> ids){bookService.batchDelete(ids);return true;}
}

UserMapper

@Mapper
public interface UserMapper {@Select("select id, user_name,`password`, delete_flag, create_time,update_time from user_info where delete_flag=0 and user_name=#{name}")UserInfo queryPasswordByUsername(String name);
}

BookMapper 

@Mapper
public interface BookMapper {@Insert("insert into book_info (book_name,author,count,price,publish,status) " +"values (#{bookName},#{author},#{count},#{price},#{publish},#{status})")void insertBook(BookInfo bookInfo);@Select("select * from book_info where status!=0 order by id asc limit #{offset},#{pageSize}")List<BookInfo> selectByPage(PageRequest pageRequest);@Select("select count(1) from book_info where status !=0")Integer count();@Select("select * from book_info where status!=0 and id=#{id}")BookInfo selectById(Integer id);Integer updateBook(BookInfo bookInfo);Integer batchDelete(List<Integer> ids);
}

UserService

@Service
public class UserService {@Autowiredprivate UserMapper userMapper;public UserInfo queryPasswordByUsername(String name) {return userMapper.queryPasswordByUsername(name);}
}

BookService

@Service
public class BookService {//bookDao是数据库访问中的一个对象，现在我们对status这个对象进行修改@Autowiredprivate BookMapper bookMapper;public void addBook(BookInfo bookInfo) {bookMapper.insertBook(bookInfo);}public PageResult<BookInfo> pageResult(PageRequest pageRequest){int count=bookMapper.count();List<BookInfo> bookInfos = bookMapper.selectByPage(pageRequest);for (BookInfo bookInfo:bookInfos) {bookInfo.setStatusCN(BookStatus.getCodeByName(bookInfo.getStatus()).getName());}return new PageResult<>(count,bookInfos,pageRequest);}public BookInfo queryBookId(Integer id) {return bookMapper.selectById(id);}public void update(BookInfo bookInfo) {bookMapper.updateBook(bookInfo);}public  Integer batchDelete(List<Integer> ids) {return bookMapper.batchDelete(ids);}
}

BookStatus

@Getter
public enum BookStatus {DELETED(0,"无效"),NORMAL(1,"可借阅"),FORBIDDEN(2,"不可借阅");private Integer code;private String name;BookStatus(Integer code, String name) {this.code = code;this.name = name;}public static BookStatus getCodeByName(Integer code){return switch (code) {case 0 -> DELETED;case 1 -> NORMAL;case 2 -> FORBIDDEN;default -> null;};}@Overridepublic String toString() {return "BookStatus{" +"code=" + code +", name='" + name + '\'' +'}';}
}

ResultCodeEnum 

public enum ResultCodeEnum {UNLOGIN(-1),SUCCESS(200),FAIL(-2);private int code;ResultCodeEnum(int code){this.code=code;}
}

constants

public class Constants {public static final String SESSION_USER_KEY="session_user_info";
}

 

 希望能对大家有所帮助！！！