依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
</dependencies>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>${spring-boot.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>${spring-boot.version}</version>
<configuration>
<mainClass>com.example.demo.DemoApplication</mainClass>
<skip>true</skip>
</configuration>
<executions>
<execution>
<id>repackage</id>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
<resources>
<!-- 扫描src/main/java下所有xx.xml文件 -->
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
<!-- 扫描resources下所有资源-->
<resource>
<directory>src/main/resources</directory>
</resource>
</resources>
</build>
yml
server:
port: 8090
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/systemboot?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
username: root
password: 123456
#邮箱的配置
mail:
host: smtp.qq.com #发送邮件服务器
username: 1613300371@qq.com #QQ邮箱
password: rhilaprdumauejca #客户端授权码 注意会失效,qq邮箱生成
protocol: smtp #发送邮件协议
# properties.mail.smtp.port: 587 #端口号465或587
default-encoding: utf-8
port: 587
rabbitmq:
host: 127.0.0.1
port: 5672
username: guest
password: guest
# MyBatis-plus配置
# 1.xml文件放在java的mapper下xml文件中,路径要写全,并且pom中要加构建扫描src/main/java:扫描java下的所有文件
# mapper-locations: classpath:com/example/demo/demos/web/mapper/xml/*.xml
# 2.xml文件放在resource的mapper下xml文件中,从resource文件名开始写路径,并且pom中要加构建扫描src/main/resources
# 扫描resource下的所有文件
mybatis-plus:
mapper-locations: classpath:/com/example/demo/demos/web/mapper/xml/*.xml
type-aliases-package: com.example.demo.demos.web.entity
controller
@RestController
@RequestMapping("/admin/book")
public class AdminController {
@Autowired
private BookService bookService;
//查询分页
@GetMapping("/pageList")
public Object pageList(@RequestParam(value = "pageNum",required = false, defaultValue = "1") Integer pageNum,
@RequestParam(value = "pageSize",required = false, defaultValue = "3") Integer pageSize,
@RequestBody Book book){
return bookService.selectPage(pageNum,pageSize,book);
}
//新增
@PostMapping("/save")
public boolean createBook(@RequestBody Book book) {
return bookService.createBook(book);
}
//修改
@PostMapping("/update/{id}")
public boolean updateBook(@PathVariable Integer id, @RequestBody Book book) {
book.setId(id);
return bookService.updateBook(book);
}
//删除
@GetMapping("/delete/{id}")
public boolean deleteBook(@PathVariable Integer id) {
return bookService.deleteBook(id);
}
//book作为参数,如果加@RequestBody要用请求体json
@GetMapping("/export")
public void export(Book book, HttpServletResponse response) {
bookService.export(book,response);
}
@PostMapping("/import")
public boolean importExcel(MultipartFile file,String username) {
return bookService.importExcel(file,username);
}
}
@RestController
@RequestMapping("/user/book")
public class UserController {
@Autowired
private BookService bookService;
//查询分页
@GetMapping("/pageList")
public Object pageList(@RequestParam(value = "pageNum",required = false, defaultValue = "1") Integer pageNum,
@RequestParam(value = "pageSize",required = false, defaultValue = "3") Integer pageSize,
@RequestBody Book book,
@RequestParam(value = "username",required = true) String username){
return bookService.userSelectPage(pageNum,pageSize,book,username);
}
//借书/还书
@PostMapping("/rentAndBackBook/{id}")
public boolean rentAndBackBook(@PathVariable Integer id, @RequestBody Book book, @RequestParam String username) {
book.setId(id);
return bookService.rentAndBackBook(book,username);
}
}
config
@Configuration
public class MyBatisPlusConfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor(){
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
//分页插件
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.H2));
//乐观锁插件
// interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
return interceptor;
}
}
entity
@Data
public class Book {
//使用mybatis-plus自带的新增
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
private String name;
//0可借,1可还
private Integer isrent;
@TableLogic(value = "0",delval = "1")
private Integer deleted;
private String username;
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
//返回的日期类型字段数据解析成字符串 xml中查询返回字段create_time更名createTime
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "Asia/Shanghai")
private Date createTime;
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "Asia/Shanghai")
private Date updateTime;
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
//返回的日期类型字段数据解析成字符串 xml中查询返回字段create_time更名createTime
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "Asia/Shanghai")
private Date rentTime;
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "Asia/Shanghai")
private Date backTime;
}
@Data
public class User {
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
private String username;
private String password;
private Integer level;
}
service
public interface BookService extends IService<Book> {
boolean createBook(Book book);
boolean updateBook(Book book);
boolean deleteBook(Integer id);
Page<Book> selectPage(int pageNum, int pageSize,Book book);
//读者查询
Page<Book> userSelectPage(int pageNum, int pageSize,Book book,String username);
//借书还书
boolean rentAndBackBook(Book book,String username);
void export(Book book, HttpServletResponse response);
boolean importExcel(MultipartFile file, String username);
}
public interface UserService extends IService<User> {
}
impl
@Service
public class BookServiceImpl extends ServiceImpl<BookMapper,Book> implements BookService {
@Autowired
private BookMapper bookMapper;
@Override
public boolean createBook(Book book) {
book.setCreateTime(new Date());
book.setUpdateTime(new Date());
book.setIsrent(0);
boolean result = save(book);
return result;
}
@Override
public boolean updateBook(Book book) {
book.setUpdateTime(new Date());
int result = bookMapper.updateById(book);
return result>0;
}
@Override
public boolean deleteBook(Integer id) {
int result = bookMapper.deleteById(id);
return result>0;
}
@Override
public Page<Book> selectPage(int pageNum, int pageSize, Book book) {
QueryWrapper<Book> wrapper = new QueryWrapper<>();
if (ObjectUtils.isNotEmpty(book)) {
if (StringUtils.isNotBlank(book.getName())) {
wrapper.like("name", book.getName());
}
}
wrapper.eq("deleted",0).orderByDesc("create_time");
Page<Book> page = new Page<>(pageNum,pageSize);
Page<Book> pageList = (Page<Book>) bookMapper.selectPage(page,wrapper);
return pageList;
}
@Override
public Page<Book> userSelectPage(int pageNum, int pageSize, Book book, String username) {
QueryWrapper<Book> wrapper = new QueryWrapper<>();
if (ObjectUtils.isNotEmpty(book)) {
if (StringUtils.isNotBlank(book.getName())) {
wrapper.like("name", book.getName());
}
//book.getIsrent()表示查询自己可借可还的书
if (ObjectUtils.isNotEmpty(book.getIsrent()) && book.getIsrent() == 0){
wrapper.eq("isrent",book.getIsrent());
}else {
wrapper.eq("isrent",book.getIsrent())
.eq("username",username);
}
}
wrapper.eq("deleted",0).orderByDesc("create_time");
Page<Book> page = new Page<>(pageNum,pageSize);
Page<Book> pageList = (Page<Book>) bookMapper.selectPage(page,wrapper);
return pageList;
}
/**
* book的Isrent=1借书,否则还书
* @param book
* @return
*/
@Override
public boolean rentAndBackBook(Book book, String username) {
//借书
if (book.getIsrent() == 1){
book.setRentTime(new Date());
book.setUsername(username);
}else{
book.setBackTime(new Date());
book.setUsername("");
}
int result = bookMapper.updateById(book);
return result>0;
}
//导出excel
@Override
public void export(Book book, HttpServletResponse response) {
//目标数据
Page<Book> consultPage = selectPage(1, 1000, book);
List<Book> list = consultPage.getRecords();
//创建一个工作铺
XSSFWorkbook workbook=new XSSFWorkbook();
//创建一个sheet
XSSFSheet sheet = workbook.createSheet("sheet名称");
String[] heads={"序号","书名","是否被借","借书人","创建日期","修改日期","借书日期","还书日期"};
//背景颜色
XSSFColor color = new XSSFColor(new java.awt.Color(0, 0, 255));
//单元格样式
XSSFCellStyle headStyle = getHeadStyleWithBorder(workbook, color, "宋体", (short) 12, true);
//表头行
XSSFRow headRow = sheet.createRow(0);
//首行添加内容
for (int i = 0; i < heads.length; i++) {
//创建headRow首行单元格
XSSFCell cell = headRow.createCell(i);
cell.setCellValue(heads[i]);
cell.setCellStyle(headStyle);
}
//contentStyle内容单元格样式
XSSFCellStyle contentStyle = getContentStyleWithBorder(workbook);
for (int i = 0; i < list.size(); i++) {
//获取对象
Book record = list.get(i);
//创建行,从第二行开始
XSSFRow row = sheet.createRow(i + 1);
//创建单元格设置+样式
//序号
XSSFCell cell0 = row.createCell(0);
cell0.setCellValue(i+1);
cell0.setCellStyle(contentStyle);
//书名
XSSFCell cell1 = row.createCell(1);
cell1.setCellValue(record.getName());
cell1.setCellStyle(contentStyle);
//是否可借 0可借,1不可借
XSSFCell cell2 = row.createCell(2);
cell2.setCellValue(record.getIsrent());
cell2.setCellStyle(contentStyle);
//借书人
XSSFCell cell3 = row.createCell(3);
cell3.setCellValue(record.getUsername());
cell3.setCellStyle(contentStyle);
//创建日期
XSSFCell cell4 = row.createCell(4);
cell4.setCellValue(getDateStr(record.getCreateTime()));
cell4.setCellStyle(contentStyle);
//修改日期
XSSFCell cell5 = row.createCell(5);
cell5.setCellValue(getDateStr(record.getUpdateTime()));
cell5.setCellStyle(contentStyle);
//借书日期
XSSFCell cell6 = row.createCell(6);
if(ObjectUtils.isNotEmpty(record.getRentTime())){
cell6.setCellValue(getDateStr(record.getRentTime()));
}else {
cell6.setCellValue("");
}
cell6.setCellStyle(contentStyle);
//还书日期
XSSFCell cell7 = row.createCell(7);
if(ObjectUtils.isNotEmpty(record.getBackTime())){
cell7.setCellValue(getDateStr(record.getBackTime()));
}else {
cell7.setCellValue("");
}
cell7.setCellStyle(contentStyle);
}
//设置1、3列列宽
sheet.setColumnWidth(1,3000);
sheet.setColumnWidth(2,7000);
exportToWeb(response,workbook,"信息表");
}
//表头单元格类型2
public static XSSFCellStyle getHeadStyleWithBorder(XSSFWorkbook workbook, XSSFColor color,String word,Short wordSize,Boolean isBold) {
XSSFCellStyle headStyle = workbook.createCellStyle();
headStyle.setAlignment(HorizontalAlignment.CENTER);
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headStyle.setWrapText(true);
headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
headStyle.setBorderBottom(BorderStyle.THIN);
headStyle.setBorderLeft(BorderStyle.THIN);
headStyle.setBorderRight(BorderStyle.THIN);
headStyle.setBorderTop(BorderStyle.THIN);
headStyle.setFillForegroundColor(color);
XSSFFont font = workbook.createFont();
font.setFontName(word);
font.setFontHeightInPoints(wordSize);
font.setBold(isBold);
headStyle.setFont(font);
return headStyle;
}
//内容单格类型
public static XSSFCellStyle getContentStyleWithBorder(XSSFWorkbook workbook) {
XSSFCellStyle contentStyle = workbook.createCellStyle();
contentStyle.setAlignment(HorizontalAlignment.CENTER);
contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentStyle.setBorderBottom(BorderStyle.THIN);
contentStyle.setBorderLeft(BorderStyle.THIN);
contentStyle.setBorderRight(BorderStyle.THIN);
contentStyle.setBorderTop(BorderStyle.THIN);
contentStyle.setWrapText(true);
return contentStyle;
}
public String getDateStr(Date date){
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); // 设置日期格式
String dateString = dateFormat.format(date);
return dateString;
}
//内容设置
public void exportToWeb(HttpServletResponse response, XSSFWorkbook workbook, String name){
OutputStream out = null;
try {
response.setContentType("application/x-download");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode(name, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
out = response.getOutputStream();
workbook.write(out);
} catch (IOException e) {
// log.error("导出"+name+"失败", e);
}finally {
IOUtils.closeQuietly(out);
}
}
//导入
@Override
public boolean importExcel(MultipartFile file,String username) {
//获取excel工作铺
Workbook workbook = getWorkbook(file);
//获取第一个sheet
Sheet sheet = workbook.getSheetAt(0);
List<Book> dataList = new ArrayList<>();
//循环sheet的所有行
for (int rowNum=1; rowNum<=sheet.getLastRowNum(); rowNum++) {
//获取第一行,第0行是标题
Row row = sheet.getRow(rowNum);
try {
//获取当前行的的每个格子
//标题
String name = getValue(row.getCell(1));
Book book = new Book();
book.setName(name);
book.setIsrent(0);
book.setDeleted(0);
book.setCreateTime(new Date());
book.setUpdateTime(new Date());
dataList.add(book);
} catch (Exception e){
// LogUtils.error("第" + rowNum + "导入行读取异常", e);
}
}
if (CollectionUtils.isNotEmpty(dataList)){
return saveBatch(dataList);
}
return false;
}
//获取工作铺,传个文件返回Workbook
public static Workbook getWorkbook(MultipartFile file) {
//获取文件名
String fileName = file.getOriginalFilename();
Workbook workbook = null;
InputStream is = null;
try {
//获取文件流
is = file.getInputStream();
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if (fileName.endsWith("xls")) {
//2003
workbook = new HSSFWorkbook(is);
} else {
//2007
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
e.printStackTrace();
}
return workbook;
}
//解决excel类型问题,获得数值
public static String getValue(Cell cell) {
String cellValue = "";
if (null != cell) {
//以下是判断数据类型
switch (cell.getCellType()) {
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) { //判断是否为日期类型
Date date = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
cellValue = formater.format(date);
} else {
DecimalFormat df = new DecimalFormat("####.####");
cellValue = df.format(cell.getNumericCellValue());
}
break;
case STRING:
cellValue = cell.getStringCellValue();
break;
case BOOLEAN:
cellValue = cell.getBooleanCellValue() + "";
break;
case BLANK: //空值
cellValue = "";
break;
case ERROR:
cellValue = "非法字符";
break;
}
}
return cellValue;
}
}
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
}
mapper
@Mapper
public interface BookMapper extends BaseMapper<Book> {
}
@Mapper
public interface UserMapper extends BaseMapper<User> {
}
<?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.demo.demos.web.mapper.BookMapper">
</mapper>
<?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.demo.demos.web.mapper.UserMapper">
</mapper>
sql
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
`id` int(0) NOT NULL AUTO_INCREMENT COMMENT '消息ID',
`name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '书名',
`isrent` tinyint(1) NOT NULL COMMENT '是否被借 0未借,1借',
`deleted` tinyint(1) NULL DEFAULT 0 COMMENT '逻辑删除 1已删除, 0未删除',
`username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '借书人',
`create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
`rent_time` datetime(0) NULL DEFAULT NULL COMMENT '借书时间',
`back_time` datetime(0) NULL DEFAULT NULL COMMENT '归还时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 115 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '消息表' ROW_FORMAT = Compact;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`level` tinyint(1) NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;