借鉴狂神springboot项目
第一步 设计数据表
- department
create table department( id int, departmentName varchar(10) )
- employee
create table employee ( id int auto_increment primary key, last_name varchar(20) not null, email varchar(20) not null, gender int not null, department_id int references department(id), birth datetime not null );
- 初始数据
insert into department(id, departmentName) values (101,'教学部'), (102,'市场部'), (103,'教研部'), (104,'运营部'), (105,'后勤部')
insert into employee(last_name, email, gender, department_id, birth) values ('AA','A123456@qq.com',0,101,'2019-1-1 00:00:00'), ('BB','B123456@qq.com',1,102,'2019-1-1 00:00:00'), ('CC','C123456@qq.com',0,103,'2019-1-1 00:00:00'), ('DD','D123456@qq.com',1,104,'2019-1-1 00:00:00'), ('EE','E123456@qq.com',0,105,'2019-1-1 00:00:00')
第二步 建Springboot项目
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.3</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.kuang</groupId> <artifactId>springboot-05-project</artifactId> <version>0.0.1-SNAPSHOT</version> <name>springboot-05-project</name> <description>Demo project for Spring Boot</description> <properties> <java.version>17</java.version> </properties> <dependencies> <!-- druid --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.21</version> </dependency> <!-- log4j --> <!-- https://mvnrepository.com/artifact/log4j/log4j --> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency> <!-- mybatis --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.1</version> </dependency> <!-- mysql --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <scope>runtime</scope> <optional>true</optional> </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> <!--thymeleaf--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </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>
application.properties
# 关闭模板引擎的缓存 spring.thymeleaf.cache=false # 我们的配置文件放在的真实位置 spring.messages.basename=i18n.login #json格式化全局配置 spring.jackson.time-zone=GMT+8 spring.jackson.date-format=yyyy-MM-dd spring.jackson.default-property-inclusion=NON_NULL # 日期格式化 spring.mvc.date-format=yyyy-MM-dd # 整合mybatis # 驼峰 mybatis.configuration.mapUnderscoreToCamelCase=true #mybatis.type-aliases-package=com.kuang.pojo #mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
application.yml
spring: datasource: username: root password: root # serverTimezone=UTC解决时区的报错 url: jdbc:mysql://localhost:3306/kuang_boot?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8 driver-class-name: com.mysql.cj.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource #Spring Boot 默认是不注入这些属性值的,需要自己绑定 #druid 数据源专有配置 initialSize: 5 minIdle: 5 maxActive: 20 maxWait: 60000 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 300000 validationQuery: SELECT 1 FROM DUAL testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: true #配置监控统计拦截的filters,stat:监控统计、log4j:日志记录、wall:防御sql注入 #如果允许时报错 java.lang.ClassNotFoundException: org.apache.log4j.Priority #则导入 log4j 依赖即可,Maven 地址:https://mvnrepository.com/artifact/log4j/log4j filters: stat,wall,log4j maxPoolPreparedStatementPerConnectionSize: 20 useGlobalDataSourceStat: true connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
测试1
@Autowired DataSource dataSource; @Test void contextLoads() throws SQLException { //查看默认数据源 System.out.println(dataSource.getClass()); //获得数据库连接 Connection connection = dataSource.getConnection(); System.out.println(connection); //关闭 connection.close(); }
pojo
- Department类
package com.kuang.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; @AllArgsConstructor @NoArgsConstructor @Data public class Department { private Integer id; private String departmentName; }
- Employee类
package com.kuang.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import java.util.Date; @NoArgsConstructor @Data public class Employee { private Integer id; private String lastName; private String email; private Integer gender;//0 女 1男 private Integer departmentId; private Date birth; public Employee(String lastName, String email, Integer gender, Integer departmentId,Date birth) { this.lastName = lastName; this.email = email; this.gender = gender; this.departmentId=departmentId; this.birth=birth; } }
mapper
- DepartmentMapper
package com.kuang.mapper; import com.kuang.pojo.Department; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Select; import org.springframework.stereotype.Repository; import java.util.Collection; @Mapper @Repository public interface DepartmentMapper { //获得所有部门信息 @Select("select * from department") Collection<Department> getDepartments(); //通过id得到部门 @Select("select * from department where id=#{id}") Department getDepartmentById(Integer id); }
- EmployeeMapper
package com.kuang.mapper; import com.kuang.pojo.Employee; import org.apache.ibatis.annotations.*; import org.springframework.stereotype.Repository; import java.util.List; @Mapper @Repository public interface EmployeeMapper { //增加一个员工 @Insert("insert into employee(last_name, email, gender, department_id,birth) value (#{lastName},#{email},#{gender},#{departmentId},#{birth})") void save(Employee employee); //查询全部员工信息 @Select("select * from employee") List<Employee> getAll(); //通过id得到员工 @Select(" select * from employee where id = #{id}") Employee getEmployeeById(Integer id); @Update("update employee set last_name=#{lastName},email=#{email},gender=#{gender},department_id=#{departmentId},birth=#{birth} where id=#{id}") int updateEmp(Employee employee); //删除员工id @Delete("delete from employee where id=#{id}") void delete(Integer id); }