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 {@Autowiredprivate 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 {@Autowiredprivate 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 {@Autowiredprivate 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";}}