还没写mvc(。)
一.创建数据库
CREATE DATABASE `springboot`;
USE `springboot`;
创建登录用户数据表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(20) NOT NULL,
`password` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
创建学生信息表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`student_id` int(10) NOT NULL AUTO_INCREMENT,
`student_name` varchar(20) NOT NULL COMMENT '学生姓名',
`student_age` int(5) NOT NULL COMMENT '学生年龄',
`student_gender` varchar(5) NOT NULL COMMENT '学生性别',
`department` varchar(200) NOT NULL COMMENT '院系代号',
`date` date NOT NULL COMMENT '入学日期',
PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
创建院系信息表
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`department_id` varchar(200) NOT NULL,
`department_name` varchar(200) NOT NULL,
PRIMARY KEY (`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
二.环境搭建
1.相关pom依赖
<?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.2.4.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.gxy</groupId>
<artifactId>yeyeqwq</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springboot-crud</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<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.1.1</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>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-test</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
2.建立基本结构和配置框架
如图
3.springboot启动器
package com.gxy;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class Application {
public static void main(String[] args){
SpringApplication.run(Application.class, args);
}
}
4.application.properties配置文件
spring.datasource.username=root
spring.datasource.password=1827
spring.datasource.url=jdbc:mysql://localhost:3306/student?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
mybatis.type-aliases-package=com.gxy.pojo
mybatis.mapper-locations=classpath:mapper/*.xml
5.测试数据库连接
@SpringBootTest
public class Test1 {
@Autowired
DataSource dataSource;
@Test
public void contextLoads() throws SQLException {
System.out.println("数据源:" + dataSource.getClass());
Connection connection = dataSource.getConnection();
System.out.println("连接:" + connection);
System.out.println("连接地址:" + connection.getMetaData().getURL());
connection.close();
}
}
创建pojo实体类
1.创建User实体
package com.gxy.pojo;
import lombok.Data;
@Data
public class User {
private int id;
private String userName;
private String password;
}
2.创建Department实体
package com.gxy.pojo;
import lombok.Data;
@Data
public class Department {
private String departmentId;
private String departmentName;
}
3.创建Student实体
package com.gxy.pojo;
import lombok.Data;
import java.sql.Date;
@Data
public class Student {
private int studentId;
private String studentName;
private int studentAge;
private String studentGender;
private String department;
private Date date;
}
4.创建StudentDTO实体(在dto包下)
package com.gxy.dto;
import lombok.Data;
import java.sql.Date;
@Data
public class StudentDTO {
private int studentId;
private String studentName;
private int studentAge;
private String studentGender;
private String department;
private Date date;
}
Mapper层
文件存放目录:
-
com.gxy.mapper 相关接口
-
resources/mapper 相关mapper.xml
1.编写User的Mapper接口:UserMapper
package com.gxy.mapper;
import com.gxy.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
@Mapper
@Repository
public interface UserMapper {
User selectPasswordByName(String userName, String password);
}
2.编写接口对应的Mapper.xml文件,UserMapper.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.gxy.mapper.UserMapper">
<select id="selectPasswordByName" resultType="User">
select * from user where user_name=#{userName} and password=#{password};
</select>
</mapper>
3.编写Department的Mapper接口:DepaertmentMapper
package com.gxy.mapper;
import com.gxy.pojo.Department;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import java.util.List;
@Mapper
@Repository
public interface DepartmentMapper {
List<Department> selectAllDepartment();
}
4.编写接口对应的Mapper.xml文件:DepaertmentMapper.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.wangqiang.mapper.DepartmentMapper">
<select id="selectAllDepartment" resultType="Department">
select * from department
</select>
</mapper>
5.编写Student的Mapper接口:StudentMapper
package com.gxy.mapper;
import com.gxy.dto.StudentDTO;
import com.gxy.pojo.Student;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import java.util.List;
@Mapper
@Repository
public interface StudentMapper {
//查询所有学生信息
List<StudentDTO> selectAllStudentDTO();
//根据Id查询学生信息
Student selectStudentById(int id);
//添加学生信息
int addStudent(Student student);
//根据Id删除学生信息
int deleteStudent(int id);
int updateStudent(Student student);
}
6.编写对应的Mapper.xml文件:StudentMapper.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.gxy.mapper.StudentMapper">
<resultMap id="StudentDTO" type="com.gxy.dto.StudentDTO">
<!-- 定义主键 ,非常重要。如果是多个字段,则定义多个id -->
<!-- property:主键在pojo中的属性名 -->
<!-- column:主键在数据库中的列名 -->
<id column="student_id" jdbcType="INTEGER" property="studentId"/>
<!-- 定义普通属性 -->
<result column="student_name" jdbcType="VARCHAR" property="studentName"/>
<result column="student_age" jdbcType="INTEGER" property="studentAge"/>
<result column="student_gender" jdbcType="VARCHAR" property="studentGender"/>
<result column="date" jdbcType="DATE" property="date"/>
<result column="department_name" jdbcType="VARCHAR" property="department"/>
</resultMap>
<select id="selectAllStudentDTO" resultMap="StudentDTO">
select s.student_id,s.student_name,s.student_gender,s.student_age,d.department_name,s.date
from student s,department d
where s.department = d.department_id
</select>
<select id="selectStudentById" resultType="Student">
select * from student where student_id = #{id}
</select>
<insert id="addStudent" parameterType="Student">
insert into student (student_name,student_gender,student_age,department,date)
values (#{studentName},#{studentGender},#{studentAge},#{department},#{date})
</insert>
<delete id="deleteStudent" parameterType="int">
delete from student where student_id = #{id}
</delete>
<update id="updateStudent" parameterType="Student">
update student set student_name = #{studentName},student_gender=#{studentGender},
student_age=#{studentAge},department=#{department},date=#{date}
where student_id = #{studentId}
</update>
</mapper>
Service层
1.StudentService接口
package com.wangqiang.service;
import com.wangqiang.dto.EmployeeDTO;
import com.wangqiang.pojo.Employee;
import java.util.List;
public interface EmployeeService {
//查询全部员工信息
List<EmployeeDTO> selectAllEmployeeDTO();
//根据id查询员工信息
Employee selectEmployeeById(int id);
//添加一个员工信息
int addEmployee(Employee employee);
//修改一个员工信息
int updateEmployee(Employee employee);
//根据id删除员工信息
int deleteEmployee(int id);
}
StudentImpl实现类
package com.gxy.service;
import com.gxy.dto.StudentDTO;
import com.gxy.mapper.StudentMapper;
import com.gxy.pojo.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class StudentServiceImpl implements StudentService{
@Autowired
private StudentMapper studentMapper;
@Override
public List<StudentDTO> selectAllStudentDTO() {
return studentMapper.selectAllStudentDTO();
}
@Override
public Student selectStudentById(int id) {
return studentMapper.selectStudentById(id);
}
@Override
public int addStudent(Student student) {
return studentMapper.addStudent(student);
}
@Override
public int updateStudent(Student student) {
return studentMapper.updateStudent(student);
}
@Override
public int deleteStudent(int id) {
return studentMapper.deleteStudent(id);
}
}
3.DepartmentService接口
package com.gxy.service;
import com.gxy.pojo.Department;
import java.util.List;
public interface DepartmentService {
List<Department> selectAllDepartment();
}
4.DepartmentService实现类
package com.gxy.service;
import com.gxy.mapper.DepartmentMapper;
import com.gxy.pojo.Department;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class DepartmentServiceImpl implements DepartmentService{
@Autowired
DepartmentMapper departmentMapper;
@Override
public List<Department> selectAllDepartment() {
return departmentMapper.selectAllDepartment();
}
}
5.UserService接口
package com.gxy.service;
import com.gxy.pojo.User;
public interface UserService {
User selectPasswordByName(String UserName,String password);
}
5.UserService实现类
package com.gxy.service;
import com.gxy.mapper.StudentMapper;
import com.gxy.mapper.UserMapper;
import com.gxy.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class UserServiceImpl implements UserService{
@Autowired
UserMapper userMapper;
@Override
public User selectPasswordByName(String userName, String password) {
return userMapper.selectPasswordByName(userName,password);
}
}
6.测试获取数据情况
package com.gxy;
import com.gxy.dto.StudentDTO;
import com.gxy.pojo.Department;
import com.gxy.pojo.Student;
import com.gxy.pojo.User;
import com.gxy.service.DepartmentService;
import com.gxy.service.StudentService;
import com.gxy.service.UserService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Date;
import java.util.List;
@SpringBootTest
public class Test1 {
@Autowired
DataSource dataSource;
@Test
public void contextLoads() throws SQLException {
System.out.println("数据源:" + dataSource.getClass());
Connection connection = dataSource.getConnection();
System.out.println("连接:" + connection);
System.out.println("连接地址:" + connection.getMetaData().getURL());
connection.close();
}
@Autowired
StudentService studentService;
@Test
public void selectAllTest(){
List<StudentDTO> studentDTOS = studentService.selectAllStudentDTO();
for(StudentDTO student : studentDTOS){
System.out.println(student);
}
}
@Test
public void insertTest(){
Student student = new Student();
student.setStudentAge(21);
student.setStudentName("张三");
Date date = new Date(2020-1-1);
student.setDate(date);
student.setStudentGender("男");
student.setDepartment("2");
studentService.addStudent(student);
selectAllTest();
}
@Test
public void selectByIdTest(){
Student student = new Student();
student = studentService.selectStudentById(2);
System.out.println(student.toString());
}
@Test
public void updateTest(){
Student student = new Student();
student.setStudentName("hhhh");
student.setDepartment("1");
student.setStudentAge(20);
student.setStudentGender("男");
Date date = new Date(2012-1-1);
student.setDate(date);
student.setStudentId(2);
studentService.updateStudent(student);
selectAllTest();
}
@Test
public void deleteTest(){
studentService.deleteStudent(2);
}
@Autowired
UserService userService;
@Test
public void userTest(){
User user = userService.selectPasswordByName("yeye","0529");
System.out.println(user);
}
@Autowired
DepartmentService departmentService;
@Test
public void departmentTest(){
List<Department> departments = departmentService.selectAllDepartment();
for (Department department:departments) {
System.out.println(department);
}
}
}