VueCli+SpringBoot（后端）三

项目搭建

技术

SpringBoot、mybatis、lombok、MySQL、easyexcel、maven

目录

1、所需依赖pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.7</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.jc</groupId>
    <artifactId>MusicTest</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>MusicTest</name>
    <description>MusicTest</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</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.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.1</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

2、application.properties配置信息

server.port=1313

spring.datasource.url=jdbc:mysql:///music?serverTimeZone=GMT%2B8&CharacterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=root

mybatis.mapper-locations=classpath:/mapper/*.xml

logging.level.com.jc=debug

3、pojo

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Message implements Serializable {
    private static final long serialVersionUID = -4948906497251043325L;
    private Integer id;
    @ExcelProperty(value = "歌曲",index = 0)
    private String song;
    @ExcelProperty(value = "歌手",index = 1)
    private String name;
    @ExcelProperty(value = "性别",index = 2)
    private String sex;
    @ExcelProperty(value = "创建时间",index = 3)
    private String createTime;
}

4、mapper

@Mapper
public interface MessageMapper {

    List<Message> selectAll(@Param("pageSize") Integer pageSize,@Param("size") Integer size);

    int insertMusic(@Param("message") Message message);

    int deleteMusic(Integer id);

    int updateMusic(@Param("message") Message message);

    List<Message> selectCondition(@Param("message") Message message,@Param("pageSize") Integer pageSize,@Param("size") Integer size);

    int selectCount();

    List<Message> downloadExcel(@Param("name") String name);

    int insertExcel(@Param("message") Message message);

//    List<Message> findAll();

}

5、mapper.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.jc.mapper.MessageMapper">
    <resultMap id="Message" type="com.jc.pojo.Message">
        <id property="id" column="id"></id>
        <result property="song" column="song"></result>
        <result property="name" column="name"></result>
        <result property="sex" column="sex"></result>
        <result property="createTime" column="createtime"></result>
    </resultMap>
    <select id="selectAll" resultMap="Message">
        select id,song,name,sex,createtime from message limit #{pageSize},#{size}
    </select>
    <insert id="insertMusic">
        insert into message
        (song,name,sex,createtime)
        values
        (#{message.song},#{message.name},#{message.sex},#{message.createTime})
    </insert>
    <delete id="deleteMusic">
        delete from message where id=#{id};
    </delete>
    <update id="updateMusic">
        update message set
        song=#{message.song},name=#{message.name},sex=#{message.sex}
        where id=#{message.id}
    </update>
    <select id="selectCondition" resultMap="Message">
        select id,song,name,sex,createtime from message where name like concat('%',#{message.name},'%') limit #{pageSize},#{size}
    </select>
    <select id="selectCount" resultType="Integer">
        select count(*) from message;
    </select>
    <select id="downloadExcel" resultMap="Message">
        select id,song,name,sex,createtime from message
        <where>
            <if test="name != null and name !='' ">
                name like concat('%',#{name},'%')
            </if>
<!--            <if test="pageSize != null and pageSize !='' and size !=null and size != '' ">-->
<!--                limit #{pageSize},#{size}-->
<!--            </if>-->
        </where>
    </select>
    <insert id="insertExcel">
        insert into message (song,name,sex,createtime)
        values
        (#{message.song},#{message.name},#{message.sex},#{message.createTime})
    </insert>
<!--    <select id="findAll" resultMap="Message">-->
<!--        select * from message-->
<!--    </select>-->
</mapper>

6、service

public interface MessageService {
    List<Message> selectAll(Integer pageSize,Integer size);
    int insertMusic(Message message);
    int deleteMusic(Integer id);
    int updateMusic(Message message);
    List<Message> selectCondition(Message message,Integer pageSize,Integer size);
    int selectCount();
    void downloadExcel(HttpServletResponse response,String name);
    String insertExcel(Message message);
//    void findAll(HttpServletResponse response);
}

7、serviceImpl

@Service
public class MessageServiceImpl implements MessageService {

    @Autowired
    private MessageMapper messageMapper;

    @Override
    public List<Message> selectAll(Integer pageSize, Integer size) {
        return messageMapper.selectAll(pageSize,size);
    }

    @Override
    public int insertMusic(Message message) {
        return messageMapper.insertMusic(message);
    }

    @Override
    public int deleteMusic(Integer id) {
        return messageMapper.deleteMusic(id);
    }

    @Override
    public int updateMusic(Message message) {
        return messageMapper.updateMusic(message);
    }

    @Override
    public List<Message> selectCondition(Message message,Integer pageSize,Integer size) {
        return messageMapper.selectCondition(message,pageSize,size);
    }

    @Override
    public int selectCount() {
        return messageMapper.selectCount();
    }

    @Override
    public void downloadExcel(HttpServletResponse response,String name) {
        try {
            String filename = URLEncoder.encode("MusicExcel", "utf-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition","attachment;filename=" + filename + ".xlsx");
            EasyExcel.write(response.getOutputStream(),Message.class).sheet().doWrite(data(name));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public String insertExcel(Message message) {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat();
        simpleDateFormat.applyPattern("yyyy-MM-dd HH:mm:ss");
        Date date = new Date();
        message.setCreateTime(simpleDateFormat.format(date));
        if (message.getSex().equals("男")){
            message.setSex("1");
        }else if (message.getSex().equals("女")){
            message.setSex("2");
        }else {
            return "数据存储失败，请重试！";
        }
        int i = messageMapper.insertExcel(message);
        if (i>=1){
            return "数据存储成功";
        }else {
            return "数据存储失败,请重试！";
        }

    }

//    @Override
//    public void findAll(HttpServletResponse response) {
//        try {
//            String filename = URLEncoder.encode("MusicExcel", "utf-8");
//            response.setContentType("application/vnd.ms-excel");
//            response.setCharacterEncoding("utf-8");
//            response.setHeader("Content-Disposition","attachment;filename=" + filename + ".xlsx");
//            EasyExcel.write(response.getOutputStream(),Message.class).sheet().doWrite(data());
//        } catch (UnsupportedEncodingException e) {
//            e.printStackTrace();
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//    }
//    private List<Message> data() {
//        return messageMapper.findAll();
//    }

    private List<Message> data(String name) {
        List<Message> messages = messageMapper.downloadExcel(name);
        for (Message message:messages) {
            if (message.getSex().equals("1")){
                message.setSex("男");
            }else if (message.getSex().equals("2")){
                message.setSex("女");
            }
        }
        return messages;
    }

}

8、excelListener

public class MessageListenerExcel extends AnalysisEventListener<Message> {

    private MessageService messageService;

    public MessageListenerExcel(MessageService messageService){
        this.messageService=messageService;
    }

    @Override
    public void invoke(Message message, AnalysisContext analysisContext) {
        saveMessage(message);
    }

    private void saveMessage(Message message) {
        messageService.insertExcel(message);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("数据存储成功");
    }
}

9、controller

@RestController
public class MessageController {

    @Autowired
    private MessageService messageService;

    @CrossOrigin
    @GetMapping("selectAll/{pageSize}/{size}")
    public List<Message> selectAll(@PathVariable Integer pageSize,@PathVariable Integer size){
        pageSize=(pageSize-1)*size;
        return messageService.selectAll(pageSize,size);
    }

    @CrossOrigin
    @PostMapping("insertMessage")
    public String insertMessage(@RequestBody Message message){
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat();
        simpleDateFormat.applyPattern("yyyy-MM-dd HH:mm:ss");
        Date date = new Date();
        message.setCreateTime(simpleDateFormat.format(date));
        if (message.getSex().equals("男")){
            message.setSex("1");
        }else if (message.getSex().equals("女")){
            message.setSex("2");
        }else {
            return "数据错误，请重试！";
        }
        int i = messageService.insertMusic(message);
        if (i>=1){
            return "数据添加成功";
        }else {
            return "数据添加失败，请重试！";
        }
    }

    @CrossOrigin
    @DeleteMapping("deleteMessage")
    public String deleteMessage(@RequestBody Message message){
        int i = messageService.deleteMusic(message.getId());
        if (i>=1){
            return "数据删除成功";
        }else {
            return "数据删除失败，请重试!";
        }
    }

    @CrossOrigin
    @PostMapping("updateMessage")
    public String updateMessage(@RequestBody Message message){
        if(message.getSex().equals("男")){
            message.setSex("1");
        }else if (message.getSex().equals("女")){
            message.setSex("2");
        }
        int i = messageService.updateMusic(message);
        if (i>=1){
            return "数据修改成功";
        }else {
            return "数据修改失败，请重试！";
        }
    }

    @CrossOrigin
    @PostMapping("selectCondition/{pageSize}/{size}")
    public List<Message> selectCondition(@RequestBody Message message,@PathVariable Integer pageSize,@PathVariable Integer size){
        pageSize=(pageSize-1)*size;
        return messageService.selectCondition(message,pageSize,size);
    }

    @CrossOrigin
    @GetMapping("selectCount")
    public Integer selectCount(){
        return messageService.selectCount();
    }

    @CrossOrigin
    @GetMapping("downloadExcel/{name}")
    public void downloadExcel(HttpServletResponse response,@PathVariable String name){
        messageService.downloadExcel(response,name);
    }

    @CrossOrigin
    @PostMapping("uploadExcel")
    public String uploadExcel(MultipartFile file){
        try {
            EasyExcel.read(file.getInputStream(),Message.class,new MessageListenerExcel(messageService)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return "导入成功";
    }

//    @CrossOrigin
//    @GetMapping("findAll")
//    public void findAll(HttpServletResponse response){
//        messageService.findAll(response);
//    }


}