mysql 基础(三)- springboot项目连接mysql
一、通过Idea脚手架搭建springboot项目
1、在idea工具中安装插件 Alibaba Cloud Toolkit
2、创建新项目
下一步
选择springWeb
完成
3、得到项目
启动项目
启动成功
访问页面
二、集成springboot 与 mybits 集成
该例子仅仅是进行一个简单的集成
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>
<groupId>com.example</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<spring-boot.version>2.6.13</spring-boot.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- mybatis 依赖 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.0</version>
</dependency>
<!-- mysql 连接类 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- druid 数据库连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.9</version>
</dependency>
<!-- lombok 插件 用于简化实体代码 -->
<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>org.hibernate.javax.persistence</groupId>
<artifactId>hibernate-jpa-2.1-api</artifactId>
<version>1.0.0.Final</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>
</build>
</project>
2、按照图片创造目录结构
3、各个文件内容
1、StudentController
package com.example.demo.demos.mybatis.controller;
import com.example.demo.demos.mybatis.domain.Student;
import com.example.demo.demos.mybatis.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
@RequestMapping("/Student")
public class StudentController {
@Autowired
private StudentService studentService;
@PostMapping("")
public int add(@RequestBody Student student){
return studentService.add(student);
}
@PutMapping("/{id}")
public int updateStudent(@PathVariable("id") Integer id, @RequestParam(value = "name", required = true) String name,
@RequestParam(value = "age", required = true) Integer age){
return studentService.update(name,age,id);
}
@DeleteMapping("/{id}")
public void deleteStudent(@PathVariable("id") Integer id){
studentService.delete(id);
}
@GetMapping("/{id}")
public Student findStudentById(@PathVariable("id") Integer id){
return studentService.findStudentById(id);
}
@GetMapping("/list")
public List<Student> findStudentList(){
return studentService.findStudentList();
}
}
2、studentMapper
package com.example.demo.demos.mybatis.dao;
import java.util.List;
import com.example.demo.demos.mybatis.domain.Student;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
@Mapper
public interface StudentMapper {
@Insert("insert into student(name, age) values(#{name}, #{age})")
int add(Student student);
@Update("update student set name = #{name}, age = #{age} where id = #{id}")
int update(@Param("name") String name, @Param("age") Integer age, @Param("id") Integer id);
@Delete("delete from student where id = #{id}")
int delete(int id);
@Select("select id, name as name, age as age from student where id = #{id}")
Student findStudentById(@Param("id") Integer id);
@Select("select id, name as name, age as age from student")
List<Student> findStudentList();
}
3、student
package com.example.demo.demos.mybatis.domain;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
@Id
@GeneratedValue
private Integer id;
private String name;
private Integer age;
}
注解:
@Data:可以自动为类生成常用的方法,包括 getter、setter、equals、hashCode 和 toString 等
@NoArgsConstructor: 自动生成无参数构造函数。
@AllArgsConstructor: 自动生成全参数构造函数。
@Id :标识实体类中的主键字段
@GeneratedValue:为一个实体生成一个唯一标识的主键、提供主键的生成策略
4、studentServiceImpl
package com.example.demo.demos.mybatis.service.impl;
import java.util.List;
import com.example.demo.demos.mybatis.dao.StudentMapper;
import com.example.demo.demos.mybatis.domain.Student;
import com.example.demo.demos.mybatis.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class StudentServiceImpl implements StudentService {
@Autowired
private StudentMapper studentMapper;
/**
* 添加 Student
* @param name
* @param age
* @return
*/
@Override
public int add(Student student) {
return studentMapper.add(student);
}
/**
* 更新 Student
* @param name
* @param age
* @param id
* @return
*/
@Override
public int update(String name, Integer age, Integer id) {
return studentMapper.update(name,age,id);
}
/**
* 删除 Student
* @param id
* @return
*/
@Override
public int delete(Integer id) {
return studentMapper.delete(id);
}
/**
* 根据 id 查询 Student
* @param id
* @return
*/
@Override
public Student findStudentById(Integer id) {
return studentMapper.findStudentById(id);
}
/**
* 查询所有的 Student
* @return
*/
@Override
public List<Student> findStudentList() {
return studentMapper.findStudentList();
}
}
5、StudentService
package com.example.demo.demos.mybatis.service;
import com.example.demo.demos.mybatis.domain.Student;
import java.util.List;
public interface StudentService {
int add(Student student);
int update(String name, Integer age, Integer id);
int delete(Integer id);
Student findStudentById(Integer id);
List<Student> findStudentList();
}
有爆红的地方引入相应的包即可
6、配置文件 application.properties
# 应用服务 WEB 访问端口
server.port=8082
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
4、在navicat中操作数据库
1、新建test数据库
2、点击”新建查询“新建表,并插入数据
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` double DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `student` VALUES ('1', 'aaa', '21');
INSERT INTO `student` VALUES ('2', 'bbb', '22');
INSERT INTO `student` VALUES ('3', 'ccc', '23');
三、演示
Controller 文件中 对应的GetMapping,DeleteMapping,PutMapping、PostMapping 请求需要postman进行相应的测试
这里演示GetMapping