上次使用的是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应该也行，没有试过。

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.stereotype.Repository;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;import java.util.List;@Repository
public class AuthorRepository {@Autowiredprivate JdbcClient jdbcClient;public List<Author> findAllAuthorsWithBooks(int page, int size, String sortBy, String sortDirection) {Pageable pageable = PageRequest.of(page, size, Sort.by(Sort.Direction.fromString(sortDirection), sortBy));String sql = "SELECT a.id AS author_id, a.name AS author_name, b.id AS book_id, b.title AS book_title " +"FROM Author a LEFT JOIN Book b ON a.id = b.author_id " +"ORDER BY " + pageable.getSort().toString().replace(":", "");return jdbcClient.sql(sql).query((rs, rowNum) -> {Author author = new Author();author.setId(rs.getLong("author_id"));author.setName(rs.getString("author_name"));return author;}).list();}
}