booksssssss

依赖

<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;

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值