springboot学习-spring-boot-data-jdbc分页/排序/多表查询的例子

上次使用的是JdbcTemplate实现的，是比较老的方式，重新用spring boot data jdbc和jdbc client 实现一遍。也比较一下这几种的编码差异。数据库方面JAVA给了太多选择，反而不好选了。

上次就试图直接用：

public interface UserRepository extends CrudRepository<User, Long> {
 
    @Query("SELECT u.username, p.address, p.phoneNumber " +
           "FROM users u JOIN profiles p ON u.profileId = p.id " +
           "ORDER BY p.id")
    Page<UserProfileDTO> findUsersWithProfiles(Pageable pageable);

直接就报错了，才改为了jdbcTemplate.

这次改了：

@Repository
public interface AuthorBookRepository extends CrudRepository<Author, Integer> {

    @Query("SELECT a.id AS author_id, a.name AS author_name, b.id AS book_id, b.title AS book_title " +
            "FROM Author a JOIN Book b ON a.id = b.author_id " +
            "ORDER BY a.id " +
            "LIMIT :limit OFFSET :offset")
    List<AuthorBook> findAllAuthorsWithBooks(int limit, int offset);
}

注意有个坑:SQL语法错误引起的，特别是在ORDER BY和LIMIT子句中使用了占位符?。在SQL中，ORDER BY子句不能使用占位符来指定列名和排序方向。我们需要在代码中动态构建SQL语句来解决这个问题。--这里就直接写，不用占位符了。

service:

@Service
public class AuthorBookService {
    @Autowired
    private AuthorBookRepository authorBookRepository;

    public List<AuthorBook> getAuthorsWithBooks(int page, int size) {
        int offset = page * size;
        return authorBookRepository.findAllAuthorsWithBooks(size, offset);
    }
}

controller

@GetMapping("/authors-books")
    public ModelAndView getAuWithBooks(@RequestParam(defaultValue = "1") int page,
                                       @RequestParam(defaultValue = "3") int size) {
        List<AuthorBook> authorsWithBooks = authorBookService.getAuthorsWithBooks(page, size);
        ModelAndView modelAndView = new ModelAndView("authors-books");
        modelAndView.addObject("authorBooks", authorsWithBooks);
        modelAndView.addObject("page", page);
        modelAndView.addObject("size", size);
        return modelAndView;
    }

验证通过，这个方法很好。

JDBC Client应该也行，没有试过。

package dev.zzz.repository;

import dev.zzz.model.dto.AuthorBook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class AuthorBookService {
    @Autowired
    private AuthorBookRepository authorBookRepository;

    private final JdbcClient jdbcClient;

    public AuthorBookService(JdbcClient jdbcClient) {
        this.jdbcClient = jdbcClient;
    }


    public List<AuthorBook> getAuthorsWithBooks(int page, int size) {
        int offset = page * size;
        return authorBookRepository.findAllAuthorsWithBooks(size, offset);
    }

    public Page<AuthorBook> getAuthors(Pageable pageable) {
        int limit = pageable.getPageSize();
        long offset = pageable.getOffset();

        String baseSql="SELECT a.id AS author_id, a.name AS author_name, b.id AS book_id, b.title AS book_title FROM Author a JOIN Book b ON a.id = b.author_id ";
        String orderByClause = " ORDER BY a.id";
        String sql = baseSql + orderByClause + " LIMIT " + limit + " OFFSET " + offset;
        List<AuthorBook> authorBooks = jdbcClient.sql(sql)
                .query(AuthorBook.class)
                .list();

        String countQuery = "SELECT count(*) FROM  Author a JOIN Book b ON a.id = b.author_id";
        Long total = jdbcClient.sql(countQuery).query(Long.class).single();

        return new PageImpl<>(authorBooks, pageable, total);
    }


}

相应的controller:

package dev.zzz.controller;

import dev.zzz.model.dto.AuthorBook;
import dev.zzz.repository.AuthorBookService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;

@Controller
public class AuthorController {
    @Autowired
    private AuthorBookService authorBookService;

    @GetMapping("/authors-books2")
    public String getAuthos(@RequestParam(defaultValue = "0") int page,
                            @RequestParam(defaultValue = "10") int size,
                            Model model) {
        Pageable pageable = PageRequest.of(page, size);
        Page<AuthorBook> authorsWithBooks = authorBookService.getAuthors(pageable);
        model.addAttribute("authorBooks",authorsWithBooks.getContent());
        model.addAttribute("page", page);
        model.addAttribute("size", size);
        return "authors-books";
    }

}