《Spring boot从入门到实战》第八章习题答案

1）创建Spring Boot项目并集成Mybatis。使用XML配置的方式实现完整的学生信息管理模块，包括学生、班级等信息的新增、修改、删除、查询等功能。

答案:

1. application.properties 配置数据库

spring.datasource.test1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.test1.jdbc-url=jdbc:mysql://127.0.0.1:3306/test?useSSL=false&serverTimezone=UTC
spring.datasource.test1.username=root
spring.datasource.test1.password=123456
spring.datasource.test2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.test2.jdbc-url=jdbc:mysql://127.0.0.1:3306/dp_database?useSSL=false&serverTimezone=UTC
spring.datasource.test2.username=root
spring.datasource.test2.password=123456

2. mybatis多数据源配置

DataSource1Config.java

package com.example.demo.config;import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;import javax.sql.DataSource;@Configuration
@MapperScan(basePackages = "com.example.demo.mapper.test1", sqlSessionFactoryRef = "test1SqlSessionFactory")
public class DataSource1Config {@Primary@Bean(name = "test1DataSource")@ConfigurationProperties(prefix = "spring.datasource.test1")public DataSource dataSource() {return DataSourceBuilder.create().build();}@Primary@Bean(name = "test1SqlSessionFactory")public SqlSessionFactory sqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource) throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean();bean.setDataSource(dataSource);bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:/mapper/test1/*.xml"));return bean.getObject();}@Primary@Bean(name = "test1TransactionManager")public DataSourceTransactionManager transactionManager(@Qualifier("test1DataSource") DataSource dataSource) {return new DataSourceTransactionManager(dataSource);}@Primary@Bean(name = "test1SqlSessionTemplate")public SqlSessionTemplate sqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {return new SqlSessionTemplate(sqlSessionFactory);}
}

DataSource2Config.java

package com.example.demo.config;import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;import javax.sql.DataSource;@Configuration
@MapperScan(basePackages = "com.example.demo.mapper.test2", sqlSessionFactoryRef = "test2SqlSessionFactory")
public class DataSource2Config {@Bean(name = "test2DataSource")@ConfigurationProperties(prefix = "spring.datasource.test2")public DataSource dataSource() {return DataSourceBuilder.create().build();}@Bean(name = "test2SqlSessionFactory")public SqlSessionFactory sqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource) throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean();bean.setDataSource(dataSource);bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:/mapper/test2/*.xml"));return bean.getObject();}@Bean(name = "test2TransactionManager")public DataSourceTransactionManager transactionManager(@Qualifier("test2DataSource") DataSource dataSource) {return new DataSourceTransactionManager(dataSource);}@Bean(name = "test2SqlSessionTemplate")public SqlSessionTemplate sqlSessionTemplate(@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {return new SqlSessionTemplate(sqlSessionFactory);}
}

3. 学生实体类 Student.java

package com.example.demo.bean;public class Student {private Long id;private String name;private Integer sex;private Integer age;private Classes classes;public Student() {}public Student(String name, int sex, int age) {this.name = name;this.sex = sex;this.age = age;}@Overridepublic String toString() {return "Student{" +"id=" + id +", name='" + name + '\'' +", sex=" + sex +", age=" + age +'}';}public Classes getClasses() {return classes;}public void setClasses(Classes classes) {this.classes = classes;}public void setAge(Integer age) {this.age = age;}public void setSex(Integer sex) {this.sex = sex;}public Long getId() {return id;}public void setId(Long id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Integer getSex() {return sex;}public void setSex(int sex) {this.sex = sex;}public Integer getAge() {return age;}public void setAge(int age) {this.age = age;}
}

4. StudentOneController 控制器创建 StudentOneController 类，处理学生管理的 HTTP 请求。

package com.example.demo.controller;import com.example.demo.bean.Student;
import com.example.demo.service.StudentOneService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;@Controller
@RequestMapping("/studentsone")
public class StudentOneController {@Autowiredprivate StudentOneService studentOneService;@GetMapping("/")public String getAllStudents(Model model) {model.addAttribute("students", studentOneService.listAllStudent());return "studentsone/list";}@GetMapping("/add")public String showAddForm(Model model) {model.addAttribute("student", new Student());return "studentsone/add";}@PostMapping("/add")public String addStudent(@ModelAttribute Student student) {studentOneService.insertStudent(student);return "redirect:/studentsone/";}@GetMapping("/edit/{id}")public String showEditForm(@PathVariable Long id, Model model) {Student student = studentOneService.getStudentById(id);model.addAttribute("student", student);return "studentsone/edit";}@PostMapping("/edit/{id}")public String updateStudent(@PathVariable Long id, @ModelAttribute Student student) {student.setId(id);studentOneService.updateStudent(student);return "redirect:/studentsone/";}@GetMapping("/delete/{id}")public String deleteStudent(@PathVariable Long id) {studentOneService.deleteStudent(id);return "redirect:/studentsone/";}}

5. StudentOneService 服务类创建 StudentOneService 类，用于业务逻辑处理。

package com.example.demo.service;import com.example.demo.bean.Student;
import com.example.demo.mapper.test1.PrimaryStudentMapper;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;import java.util.List;@Service
public class StudentOneService {private final PrimaryStudentMapper primaryMapper;@Autowiredpublic StudentOneService(@Qualifier("test1SqlSessionTemplate") SqlSessionTemplate test1SqlSessionTemplate){this.primaryMapper = test1SqlSessionTemplate.getMapper(PrimaryStudentMapper.class);}public List<Student> listAllStudent(){List<Student> studentsPrimary = primaryMapper.selectAll();return studentsPrimary;}public Student getStudentById(Long id){Student student = primaryMapper.selectOne(id);return student;}public void insertStudent(Student student){primaryMapper.insert(student);}public void updateStudent(Student student){primaryMapper.update(student);}public void deleteStudent(Long id){primaryMapper.delete(id);}}

6. mybatis PrimaryStudentMapper.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.example.demo.mapper.test1.PrimaryStudentMapper"><select id="selectAll" resultType="com.example.demo.bean.Student">SELECT * FROM student</select><select id="selectOne" parameterType="Long" resultType="com.example.demo.bean.Student">SELECT * FROM student WHERE id = #{id}</select><insert id="insert" parameterType="com.example.demo.bean.Student">INSERT INTO student (name, sex, age) values (#{name}, #{sex}, #{age})</insert><update id="update" parameterType="com.example.demo.bean.Student">UPDATE student SET<if test="name != null">name = #{name},</if><if test="sex != null">sex = #{sex},</if>age = #{age}WHEREid = #{id}</update><delete id="delete" parameterType="Long">DELETE FROM student WHERE id = #{id}</delete><resultMap id="StudentAndClassMap" type="com.example.demo.bean.Student"><id column="id" property="id" jdbcType="BIGINT"/><result column="name" property="name" jdbcType="VARCHAR"/><result column="sex" property="sex" jdbcType="INTEGER"/><result column="age" property="age" jdbcType="INTEGER"/><association property="classes" javaType="com.example.demo.bean.Classes"><id column="id" property="id"/><result column="class_name" property="name" jdbcType="VARCHAR"/><result column="memo" property="memo" jdbcType="VARCHAR"/></association></resultMap><select id="selectStudentAndClass" parameterType="Long" resultMap="StudentAndClassMap">SELECT s.id,s.name,s.sex,s.age,s.class_id,c.name as class_name, c.memoFROM student s LEFT JOIN classes c ON s.class_id = c.id WHERE s.id = #{id}</select><resultMap id="ClassAndStudentMap" type="com.example.demo.bean.Classes"><id property="id" column="id"/><result property="name" column="name"/><result property="memo" column="memo"/><collection property="students" ofType="com.example.demo.bean.Student"><id property="id" column="student_id"/><result property="name" column="student_name"/><result property="age" column="age" /></collection></resultMap><select id="selectClassAndStudent" parameterType="Long" resultMap="ClassAndStudentMap">SELECT c.id,c.name,c.memo,s.id as student_id,s.name as student_name,s.age,s.sex FROM classes cLEFT JOIN student s on s.class_id = c.id WHERE c.id = #{id}</select></mapper>

7. Thymeleaf 页面创建页面模板来展示学生列表、添加、编辑学生等功能。studentsone目录下:

list.html

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head><title>学生信息管理</title>
</head>
<body>
<h1>学生信息</h1>
<table><thead><tr><th>ID</th><th>姓名</th><th>年龄</th><th>性别</th><th>操作</th></tr></thead><tbody><tr th:each="student : ${students}"><td th:text="${student.id}"></td><td th:text="${student.name}"></td><td th:text="${student.age}"></td><td th:text="${student.sex}"></td><td><a th:href="@{/studentsone/edit/{id}(id=${student.id})}">编辑</a> |<a th:href="@{/studentsone/delete/{id}(id=${student.id})}">删除</a></td></tr></tbody>
</table>
<a href="/studentsone/add">添加学生</a>
</body>
</html>

add.html

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head><title>添加学生</title>
</head>
<body>
<h1>添加学生</h1>
<form action="#" th:action="@{/studentsone/add}" th:object="${student}" method="post"><label>姓名:</label><input type="text" th:field="*{name}"/><br/><label>年龄:</label><input type="number" th:field="*{age}"/><br/><label>性别:</label><input type="number" th:field="*{sex}"/><br/><button type="submit">提交</button>
</form>
<a href="/studentsone/">返回</a>
</body>
</html>

edit.html

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head><title>编辑学生</title>
</head>
<body>
<h1>编辑学生</h1>
<form action="#" th:action="@{/studentsone/edit/{id}(id=${student.id})}" th:object="${student}" method="post"><label>姓名:</label><input type="text" th:field="*{name}"/><br/><label>年龄:</label><input type="number" th:field="*{age}"/><br/><label>性别:</label><input type="number" th:field="*{sex}"/><br/><button type="submit">提交</button>
</form>
<a href="/studentsone/">返回</a>
</body>
</html>

8. 测试启动 Spring Boot 应用，访问：

2) 增加学生信息多条件查询功能，查询条件覆盖姓名、性别和年龄等字段。

答案：

要实现多条件查询功能，可以在 PrimaryStudentMapper 和 StudentOneService 中添加一个多条件查询方法。在 XML 配置文件中使用 <where> 标签动态生成 SQL 查询，以根据条件的有无生成相应的查询语句。

以下是详细的实现步骤：

1. 修改 PrimaryStudentMapper 接口

public interface PrimaryStudentMapper {List<Student> selectByConditions(Student student);
}

2. 在 PrimaryStudentMapper 的 XML 中定义 SQL 查询

    <select id="selectByCondition" resultType="com.example.demo.bean.Student">SELECT * FROM student<where><if test="name != null and name != ''">name = #{name}</if><if test="sex != null">AND sex = #{sex}</if><if test="age != null">AND age = #{age}</if></where></select>

3. 修改 StudentOneService 服务层
在 StudentOneService 中增加 selectByConditions 方法，调用 PrimaryStudentMapper 的 selectByConditions 方法。

    public List<Student> findStudentsByCondition(Student student){return primaryMapper.selectByCondition(student);}

4. 控制器StudentOneController调用示例
在控制层可以通过传入包含条件的 Student 对象来调用 findStudentsByConditions 方法。以下是一个示例：

    @GetMapping("/search")public String searchStudents(@RequestParam(required = false) String name,@RequestParam(required = false) Integer sex,@RequestParam(required = false) Integer age,Model model){Student student = new Student();student.setName(name);student.setSex(sex);student.setAge(age);model.addAttribute("students", studentOneService.findStudentsByCondition(student));return "studentsone/list";}

5. 测试多条件查询
访问 /students/search 端点，可以传入不同的查询条件组合进行查询。例如：
查询姓名为 “weiz新增” 的学生：/studentsone/search?name=weiz新增

《Spring boot从入门到实战》第八章习题答案

相关资讯

热文排行

最新新闻

推荐新闻

热搜词