One-to-Many Relationship: Server-Side CRUD with Pagination

@Service
@Slf4j
public class BookServiceImpl implements BookService {
    @Autowired
    private BookMapper bookMapper;

    @Override
    public PageResult<BookModel> searchBooks(SearchCriteria criteria) {
        PageHelper.startPage(criteria.getPage(), criteria.getPageSize());
        List<BookModel> results = bookMapper.findAll(criteria);
        PageInfo<BookModel> pageInfo = new PageInfo<>(results);
        return new PageResult<>(pageInfo.getTotal(), pageInfo.getList());
    }

    // Insert operation for one-to-many
    @Override
    @Transactional(rollbackFor = {Exception.class})
    public void create(BookModel book) {
        // Save parent entity
        book.setCreatedAt(LocalDate.now());
        bookMapper.insert(book);

        // Save child entities
        List<StudentModel> students = book.getStudents();
        if (!CollectionUtils.isEmpty(students)) {
            students.forEach(s -> s.setBookId(book.getId()));
            bookMapper.batchInsertStudents(students);
        }
    }

    // Delete operation for one-to-many
    @Override
    @Transactional(rollbackFor = {Exception.class})
    public void remove(List<Integer> ids) {
        bookMapper.deleteStudentsByBookIds(ids);
        bookMapper.deleteBooks(ids);
    }

    // Fetch with associated children
    @Override
    public BookModel fetchBookWithStudents(Integer bookId) {
        return bookMapper.findBookWithStudents(bookId);
    }

    // Update operation for one-to-many
    @Override
    @Transactional(rollbackFor = {Exception.class})
    public void modify(BookModel book) {
        // Update parent
        book.setCreatedAt(LocalDate.now());
        bookMapper.updateById(book);

        // Remove existing children
        bookMapper.deleteStudentsByBookIds(Arrays.asList(book.getId()));

        // Re-insert children
        List<StudentModel> students = book.getStudents();
        if (!CollectionUtils.isEmpty(students)) {
            students.forEach(s -> s.setBookId(book.getId()));
            bookMapper.batchInsertStudents(students);
        }
    }
}

Data Transformation: Flat List to Nested Structure

@Service
public class StatisticsService implements StatsInterface {
    @Autowired
    private StudentMapper studentMapper;

    @Override
    public GenderReportDto getGenderBreakdown() {
        List<Map<String, Object>> rawData = studentMapper.countByGender();
        // [{gender:"Male", count:1},{gender:"Female", count:2}]
        List<Object> genders = rawData.stream()
                .map(m -> m.get("gender"))
                .collect(Collectors.toList());
        List<Object> counts = rawData.stream()
                .map(m -> m.get("count"))
                .collect(Collectors.toList());
        return new GenderReportDto(genders, counts);
    }

    @Override
    public List<Map<String, Object>> getGenderBreakdownRaw() {
        return studentMapper.countByGender();
    }
}

One-to-Many Relationship: MyBatis XML Mapping

<?xml version="1.0" encoding="UTF-8" ?>
<mapper namespace="com.example.mapper.BookMapper">
    <!-- Update parent entity -->
    <update id="updateById">
        UPDATE book_catalog
        <set>
            <if test="bookName != null and bookName != ''">
                book_name = #{bookName},
            </if>
            <if test="createdAt != null">
                created_at = #{createdAt}
            </if>
        </set>
        WHERE id = #{id}
    </update>

    <!-- Conditional search with joins -->
    <select id="findAll" resultType="com.example.model.StudentModel">
        SELECT s.*, b.book_name 
        FROM student s 
        LEFT JOIN book_catalog b ON s.book_id = b.id
        <where>
            <if test="name != null and name != ''">
                s.name LIKE CONCAT('%', #{name}, '%')
            </if>
            <if test="age != null">
                AND s.age > #{age}
            </if>
            <if test="createdAfter != null">
                AND s.created_time > #{createdAfter}
            </if>
        </where>
    </select>

    <!-- Insert parent -->
    <insert id="insert" parameterType="com.example.model.BookModel" 
            keyProperty="id" useGeneratedKeys="true">
        INSERT INTO book_catalog(book_name, created_at) 
        VALUES(#{bookName}, #{createdAt})
    </insert>

    <!-- Batch insert children -->
    <insert id="batchInsertStudents" parameterType="com.example.model.StudentModel">
        INSERT INTO student(name, age, gender, created_time, book_id) VALUES
        <foreach collection="list" item="student" separator=",">
            (#{student.name}, #{student.age}, #{student.gender}, 
             #{student.createdTime}, #{student.bookId})
        </foreach>
    </insert>

    <!-- Batch delete parents -->
    <delete id="deleteBooks" parameterType="java.lang.Integer">
        DELETE FROM book_catalog WHERE id IN
        <foreach collection="list" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>

    <!-- Batch delete children -->
    <delete id="deleteStudentsByBookIds" parameterType="java.lang.Integer">
        DELETE FROM student WHERE book_id IN
        <foreach collection="list" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>

    <!-- Result map for fetching with children -->
    <resultMap id="bookWithStudentsMap" type="com.example.model.BookModel">
        <id column="id" property="id" />
        <result column="catalog_name" property="bookName" />
        <collection property="students" ofType="com.example.model.StudentModel">
            <id column="student_id" property="id" />
            <result column="student_name" property="name" />
            <result column="student_age" property="age" />
            <result column="student_gender" property="gender" />
            <result column="student_created" property="createdTime" />
            <result column="student_book_id" property="bookId" />
        </collection>
    </resultMap>

    <select id="findBookWithStudents" resultMap="bookWithStudentsMap">
        SELECT
            b.id,
            s.id AS student_id,
            s.name AS student_name,
            s.created_time AS student_created,
            s.age AS student_age,
            s.gender AS student_gender,
            s.book_id AS student_book_id,
            b.book_name AS catalog_name
        FROM student s
        LEFT JOIN book_catalog b ON s.book_id = b.id
        WHERE b.id = #{id}
    </select>
</mapper>

SQL CASE and IF Expressions

<!-- Gender statistics using CASE -->
<select id="countByGender" resultType="java.util.Map">
    SELECT
        (CASE
            WHEN gender = 1 THEN 'Female'
            WHEN gender = 2 THEN 'Male'
            ELSE 'Unknown' END) AS gender,
        COUNT(*) AS count
    FROM student 
    GROUP BY gender
    ORDER BY count
</select>

<!-- Gender statistics using IF -->
<select id="countByGenderIf" resultType="java.util.Map">
    SELECT
        IF(gender = 1, 'Male Employees', 'Female Employees') AS gender,
        COUNT(*) AS count
    FROM student 
    GROUP BY gender
    ORDER BY count
</select>

Configuration Management

application.yml (Base Configuration)

wechat:
  appId: 123
  token: 456
  secret: 789
  port: ${server.port}

spring:
  profiles:
    active: online

application-online.yml (Environment-Specific)

server:
  port: 8082

wechat:
  url: 127.0.0.1/online

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/testdata?useSSL=false&serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: 100100100
    hikari:
      connection-timeout: 30000
      maximum-pool-size: 30
      minimum-idle: 10
      max-lifetime: 6000

mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: classpath*:**/mapper/*.xml
  type-aliases-package: com.example.demo.model

Accessing Configuration Values

@PropertySource("custom.properties")
@ConfigurationProperties(prefix = "mychat")
@Component
public class CustomConfig {
    private String appId;
    private String token;
    private String secret;

    public String getAppId() {
        return appId;
    }

    public void setAppId(String appId) {
        this.appId = appId;
    }

    public String getToken() {
        return token;
    }

    public void setToken(String token) {
        this.token = token;
    }

    public String getSecret() {
        return secret;
    }

    public void setSecret(String secret) {
        this.secret = secret;
    }
}

// Inject using @Value
@Value("${wechat.port}")
private String port;

// Inject custom configuration bean
@Resource
private CustomConfig customConfig;

@GetMapping("/getConfig")
public CustomConfig getConfig() {
    return customConfig;
}

MyBatis-Plus Integration

Maven Dependencies

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.12</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.6</version>
</dependency>

MyBatis-Plus Configuration

server:
  port: 8089

spring:
  datasource:
    druid:
      url: jdbc:mysql://localhost:3306/testdata
      username: root
      password: 100100100
      driver-class-name: com.mysql.cj.jdbc.Driver

mybatis-plus:
  type-aliases-package: com.example.demo.model
  mapper-locations: classpath:/mapper/**.xml
  configuration:
    map-underscore-to-camel-case: false
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      id-type: auto

Common Annotations

Use @TableField("`order`") when entity field names conflict with SQL reserved words.

Query Wrappers

@Autowired
private BookMapper bookMapper;

@Test
void selectDemo() {
    QueryWrapper<BookModel> wrapper = new QueryWrapper<BookModel>()
        .select("id", "book_name")
        .ge("id", 20)
        .like("book_name", "Tutorial");
    bookMapper.selectList(wrapper);
}

@Test
void updateDemo() {
    BookModel book = new BookModel();
    book.setBookName("Updated Title");
    UpdateWrapper<BookModel> wrapper = new UpdateWrapper<BookModel>()
        .eq("id", 34);
    bookMapper.update(book, wrapper);
}

@Test
void setSqlDemo() {
    UpdateWrapper<BookModel> wrapper = new UpdateWrapper<BookModel>()
        .setSql("book_name = 'New Title'")
        .in("id", Arrays.asList(40, 41, 42));
    bookMapper.update(null, wrapper);
}

@Test
void lambdaQueryDemo() {
    LambdaQueryWrapper<BookModel> wrapper = new LambdaQueryWrapper<BookModel>()
        .select(BookModel::getId, BookModel::getBookName)
        .ge(BookModel::getId, 20);
    bookMapper.selectList(wrapper);
}

Service Layer Setup

Mapper Interface:

@Mapper
public interface BookMapper extends BaseMapper<BookModel> {
}

Service Interface:

public interface IBookService extends IService<BookModel> {
}

Service Implementation:

@Service
public class BookServiceImpl extends ServiceImpl<BookMapper, BookModel> 
    implements IBookService {
}

REST Controller:

@RestController
@RequiredArgsConstructor
@RequestMapping("/book")
public class BookController {
    private final BookService bookService;

    @PostMapping
    public void create(@RequestBody BookModel book) {
        bookService.save(book);
    }

    @DeleteMapping("/{id}")
    public void delete(@PathVariable Long id) {
        bookService.removeById(id);
    }

    @GetMapping("/{id}")
    public BookModel getById(@PathVariable Long id) {
        return bookService.getById(id);
    }

    @GetMapping
    public List<BookModel> getMultiple(@RequestParam("ids") List<Long> ids) {
        return bookService.listByIds(ids);
    }
}

MyBatis-Plus CRUD Operations

@Service
public class BookServiceImpl extends ServiceImpl<BookMapper, BookModel> 
    implements IBookService {

    @Autowired
    private StudentService studentService;

    @Override
    public PageResult<BookModel> searchPaged(QueryModel query) {
        Page<BookModel> page = Page.of(query.getPageNum(), query.getPageSize());
        String bookName = query.getBookName();
        Page<BookModel> result = lambdaQuery()
            .like(bookName != null, BookModel::getBookName, bookName)
            .page(page);
        PageResult<BookModel> response = new PageResult<>();
        response.setTotal(result.getTotal());
        response.setPages(result.getPages());
        response.setList(result.getRecords());
        return response;
    }

    @Override
    public BookModel getWithStudents(Long bookId) {
        return baseMapper.selectBookWithStudents(bookId);
    }

    @Override
    @Transactional(rollbackFor = {Exception.class})
    public void create(BookModel book) {
        save(book);
        List<StudentModel> students = book.getStudents();
        if (!CollectionUtils.isEmpty(students)) {
            students.forEach(s -> s.setBookId(book.getId()));
            studentService.saveBatch(students);
        }
    }

    @Override
    @Transactional(rollbackFor = {Exception.class})
    public void remove(Long id) {
        BookModel book = getById(id);
        if (book == null) {
            throw new RuntimeException("Entity not found");
        }
        QueryWrapper<StudentModel> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("book_id", id);
        studentService.remove(queryWrapper);
        removeById(id);
    }

    @Override
    @Transactional(rollbackFor = {Exception.class})
    public void modify(BookModel book) {
        updateById(book);
        List<StudentModel> students = book.getStudents();
        if (!CollectionUtils.isEmpty(students)) {
            QueryWrapper<StudentModel> deleteWrapper = new QueryWrapper<>();
            deleteWrapper.eq("book_id", book.getId());
            studentService.remove(deleteWrapper);
            students.forEach(s -> s.setBookId(book.getId()));
            studentService.saveBatch(students);
        }
    }
}