SpringBoot与MySQL整合实践-优快云博客

本文介绍如何使用SpringBoot框架结合MySQL数据库实现实习生信息管理系统的搭建过程，包括环境配置、对象映射、增删改查操作及Thymeleaf模板引擎的使用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

题目三、

使用Springboot + Mysql，将实习生的简要信息存储在数据库中，用http协议对存储在数据库中的这些信息完成增删改查操作；
也可以做一个简单的网页界面来演示问题1的操作。

一、准备工作，建立springboot-mysql工程

1、http://start.spring.io/

A、Artifact中输入springboot-mysql

B、勾选Web下的web

C、勾选SQL下的JPA MYSQL

2、IDEA打开工程springboot-mysql，结构如下图：

3.在包com.example下建立web文件夹

4.HelloController

package com.example.springbootmysql.controller;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class HelloController {

    protected static Logger logger=LoggerFactory.getLogger(HelloController.class);

    @RequestMapping("/")
    public String helloworld(){
        logger.debug("访问hello");
        return "Hello world!";
    }

    @RequestMapping("/hello/{name}")
    public String helloName(@PathVariable String name){
        logger.debug("访问helloName,Name={}",name);
        return "Hello "+name;
    }
}

logback.xml，位于main/resources下。

<configuration>
    <!-- %m输出的信息,%p日志级别,%t线程名,%d日期,%c类的全名,,,, -->
    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>%d %p (%file:%line\)- %m%n</pattern>
            <charset>GBK</charset>
        </encoder>
    </appender>
    <appender name="baselog"
              class="ch.qos.logback.core.rolling.RollingFileAppender">
        <File>log/base.log</File>
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>log/base.log.%d.%i</fileNamePattern>
            <timeBasedFileNamingAndTriggeringPolicy  class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
                <!-- or whenever the file size reaches 64 MB -->
                <maxFileSize>64 MB</maxFileSize>
            </timeBasedFileNamingAndTriggeringPolicy>
        </rollingPolicy>
        <encoder>
            <pattern>
                %d %p (%file:%line\)- %m%n
            </pattern>
            <charset>UTF-8</charset> <!-- 此处设置字符集 -->
        </encoder>
    </appender>
    <root level="info">
        <appender-ref ref="STDOUT" />
    </root>
    <logger name="com.example" level="DEBUG">
        <appender-ref ref="baselog" />
    </logger>
</configuration>

5.main类注解

package com.example.springbootmysql;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;

@SpringBootApplication(exclude={DataSourceAutoConfiguration.class})
public class SpringbootMysqlApplication {

   public static void main(String[] args) {
      SpringApplication.run(SpringbootMysqlApplication.class, args);
   }
}

6.启动工程，通过浏览器查看正确性

http://localhost:8080/

http://localhost:8080/hello/springboot

二、构建业务对象及访问库

1.com.example建立domain，domain下建立类Student

package com.example.springbootmysql.domain;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

/**
 * Created by deli-zk on 2018/7/19.
 */


@Entity
public class Student {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private Integer jobNumber;

    private String name;

    private String sex;

    private Long phoneNumber;

    private String job;

    public static Long number=0L;

    public Student(Long id, Integer jobNumber, String name, String sex, Long phoneNumber, String job){
        this.id = id;
        this.jobNumber = jobNumber;
        this.name = name;
        this.sex = sex;
        this.phoneNumber = phoneNumber;
        this.job = job;
    }

    public Student(Integer jobNumber, String name, String sex, Long phoneNumber, String job){
        super();
        this.jobNumber = jobNumber;
        this.name = name;
        this.sex = sex;
        this.phoneNumber = phoneNumber;
        this.job = job;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Integer getJobNumber() {
        return jobNumber;
    }

    public void setJobNumber(Integer jobNumber) {
        this.jobNumber = jobNumber;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Long getPhoneNumber() {
        return phoneNumber;
    }

    public void setPhoneNumber(Long phoneNumber) {
        this.phoneNumber = phoneNumber;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }
}

2.com.example下建立repository，建立StudentRepository

package com.example.springbootmysql.repository;


import com.example.springbootmysql.domain.Student;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface StudentRepository extends JpaRepository<Student,Long> {

    List<Student> findByJobNumber(Integer jobNumber);

    List<Student> findByName(String name);

    List<Student> findBySex(String sex);

    List<Student> findByPhoneNumber(Long phoneNumber);

    List<Student> findByJob(String job);


    @Query("select s from Student s where s.name=:name or s.phoneNumber=:phoneNumber or s.job=:job")
    List<Student> withNameOrPhoneNumberOrJobQuery(@Param("name") String name, @Param("phoneNumber") Long phoneNumber, @Param("job") String job);

    Student findAllById(Long id);

}

3.DataController

package com.example.springbootmysql.controller;

import com.example.springbootmysql.domain.Student;
import com.example.springbootmysql.repository.StudentRepository;


import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.view.RedirectView;

import java.util.List;

/**
 * Created by deli-zk on 2018/7/19.
 */
@RestController
public class DataController {

    protected static Logger logger = LoggerFactory.getLogger(DataController.class);

    @Autowired
    StudentRepository studentRepository;

    @RequestMapping(value = "/save",method = RequestMethod.GET)
    public Student save(Integer jobNumber, String name, String sex, Long phoneNumber, String job) {
        logger.info("save 开始");
        Student s = studentRepository.save(new Student(null, jobNumber, name, sex, phoneNumber, job));
        logger.info("save 结束");
        Student.number ++;
        return s;
    }

    @RequestMapping(value = "/deleteById/{id}", method = RequestMethod.GET)
    public void deleteById(@PathVariable("id") Long id) {
        logger.info("deleteById 开始");
        studentRepository.deleteById(id);
        logger.info("deleteById 结束");
    }

    @RequestMapping(value = "/delete", method = RequestMethod.GET)
    public ModelAndView delete(@RequestParam(value = "id") Long id,
                               @RequestParam(value = "jobNumber") Integer jobNumber,
                               @RequestParam(value = "name") String name,
                               @RequestParam(value = "sex") String sex,
                               @RequestParam(value = "phoneNumber") Long phoneNumber,
                               @RequestParam(value = "job") String job) {
        logger.info("delete 开始");
        Student s= new Student(id, jobNumber, name, sex, phoneNumber, job);
        Student s1 = new Student(null, jobNumber, name, sex, phoneNumber, job);
        s1.setId(id);
        s1.setJobNumber(studentRepository.findAllById(id).getJobNumber());
        s1.setSex(studentRepository.findAllById(id).getSex());
        s1.setPhoneNumber(studentRepository.findAllById(id).getPhoneNumber());
        s1.setJob(studentRepository.findAllById(id).getJob());
        if (s.getJobNumber().equals(s1.getJobNumber()) && s.getName().equals(s1.getName()) && s.getSex().equals(s1.getSex()) && s.getPhoneNumber().equals(s1.getPhoneNumber()) && s.getJob().equals(s1.getJob())) {
            studentRepository.delete(s);
            return new ModelAndView(new RedirectView("viewAll"));
        } else {
            return new ModelAndView(new RedirectView("error"));
            //return "输入信息有误！";
        }
    }

    @RequestMapping(value = "/update", method = RequestMethod.GET)
    public Student update(@RequestParam("id") Long id,
                          @RequestParam(value = "jobNumber", required = false) Integer jobNumber,
                          @RequestParam(value = "name", required = false) String name,
                          @RequestParam(value = "sex", required = false) String sex,
                          @RequestParam(value = "phoneNumber", required = false) Long phoneNumber,
                          @RequestParam(value = "job", required = false) String job) {
        Student s = new Student(null, jobNumber, name, sex, phoneNumber, job);
        if (studentRepository.findAllById(id) != null) {
            s.setId(id);
            if (jobNumber == null){
                s.setJobNumber(studentRepository.findAllById(id).getJobNumber());
            } else {
                s.setJobNumber(jobNumber);
            }
            if (name == null) {
                s.setName(studentRepository.findAllById(id).getName());
            } else {
                s.setName(name);
            }
            if (sex == null) {
                s.setSex(studentRepository.findAllById(id).getSex());
            } else {
                s.setName(name);
            }
            if (phoneNumber == null) {
                s.setPhoneNumber(studentRepository.findAllById(id).getPhoneNumber());
            } else {
                s.setPhoneNumber(phoneNumber);
            }
            if (job == null) {
                s.setJob(studentRepository.findAllById(id).getJob());
            } else {
                s.setJob(job);
            }
            return studentRepository.save(s);
        }
        return s;
    }

    @RequestMapping("/find")
    public List<Student> find(
            @RequestParam(required = false) String name,
            @RequestParam(required = false) Long phoneNumber,
            @RequestParam(required = false) String job){
        logger.info("q4 开始");
        logger.info("q4接收参数age={},name={},address={}", name, phoneNumber, job);
        return studentRepository.withNameOrPhoneNumberOrJobQuery(name, phoneNumber, job);
    }
    @RequestMapping("/findAllById")
    public Student findAllById(
            @RequestParam(required = false) Long id){

        return studentRepository.findAllById(id);
    }

    @RequestMapping("/sort")
    public List<Student> sort() {
        logger.debug("sort 开始");
        List<Student> students = studentRepository.findAll(new Sort(Sort.Direction.ASC, "jobNumber"));
        return students;
    }


    @RequestMapping("/page")
    public Page<Student> page(int page, int size) {
        logger.info("page 开始");
        logger.info("page接收参数page={},size={}", page, size);
        PageRequest pageable = PageRequest.of(page, size, new Sort(Sort.Direction.ASC, "jobNumber"));
        Page<Student> students = studentRepository.findAll(pageable);
        return students;
    }

    @RequestMapping("/pageNext")
    public Page<Student> pageNext(int pageNumber){
        PageRequest pageRequest = PageRequest.of(1, 3, new Sort(Sort.Direction.ASC, "jobNumber"));
        if(pageNumber==1){
            return studentRepository.findAll(pageRequest);
        }else if(pageNumber==2){
            return studentRepository.findAll(pageRequest.next());
        }else if(pageNumber==3){
            return studentRepository.findAll(pageRequest.next().next());
        }
        return null;
    }

}

4.HelloThymeleafController

package com.example.springbootmysql.controller;

import com.example.springbootmysql.domain.Student;
import com.example.springbootmysql.repository.StudentRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;

@Controller
public class HelloThymeleafController {

    private Logger logger = LoggerFactory.getLogger(HelloThymeleafController.class);

    final
    StudentRepository studentRepository;

    @Autowired
    public HelloThymeleafController(StudentRepository studentRepository) {
        this.studentRepository = studentRepository;
    }

    @RequestMapping(value = "/helloThymeleaf",method = RequestMethod.GET)
    public String helloThymeleaf(Model model){
        model.addAttribute("name","Dear");
        model.addAttribute("today","2018-6-29");
        return "helloThymeleaf";
    }

    @RequestMapping(value = "/view",method = RequestMethod.GET)
    public String view(@RequestParam("id")Long id, Model model){
        model.addAttribute("id",id);
        model.addAttribute("name",studentRepository.findAllById(id).getJobNumber());
        model.addAttribute("name",studentRepository.findAllById(id).getName());
        model.addAttribute("age",studentRepository.findAllById(id).getSex());
        model.addAttribute("address",studentRepository.findAllById(id).getPhoneNumber());
        model.addAttribute("address",studentRepository.findAllById(id).getJob());
        return "view";
    }

    @RequestMapping(value = "/viewAll",method = RequestMethod.GET)
    public String viewAll(Model model){
        for(Long i=1L;i<= Student.number;i++){
            if(studentRepository.findAllById(i)!=null) {
                model.addAttribute("id" + i, studentRepository.findAllById(i).getId());
                model.addAttribute("name",studentRepository.findAllById(i).getJobNumber());
                model.addAttribute("name",studentRepository.findAllById(i).getName());
                model.addAttribute("age",studentRepository.findAllById(i).getSex());
                model.addAttribute("address",studentRepository.findAllById(i).getPhoneNumber());
                model.addAttribute("address",studentRepository.findAllById(i).getJob());
            }
        }
        return "viewAll";
    }
}

5.服务器配置与thymeleaf配置：

server.port=8033

spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=true
spring.datasource.username=root
spring.datasource.password=123qwe
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.jpa.open-in-view=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jackson.serialization.indent-output=true

#thymeleaf start
spring.thymeleaf.mode=HTML5
spring.thymeleaf.encoding=UTF-8
spring.thymeleaf.content-type=text/html
#关闭缓存
spring.thymeleaf.cache=false
#thymeleaf end

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `Id` INT(11) NOT NULL AUTO_INCREMENT,
  `job_number` INT(11) DEFAULT NULL ,
  `name` varchar(255) DEFAULT NULL,
  `sex` VARCHAR(255) DEFAULT NULL ,
  `phone_number`  Long DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Id`)
)
  ENGINE=InnoDB DEFAULT CHARSET=utf8;

6.修改启动类注解

将注解@SpringBootApplication(exclude={DataSourceAutoConfiguration.class})去掉exclude。

2018.7.19

题目三、

一、准备工作，建立springboot-mysql工程