html网站二维码悬浮怎么做,网站建设维护费合同范本,什么是网站被黑,网站设计 联系最近喜欢上了springboot#xff0c;真是个好的脚手架。今天继续学习分页/排序/多表查询等复杂功能。按步骤记录如下. 按步骤做的发现不可用#xff0c;最终还是用的jdbctemplate解决。这也是一次经验。总计在最后。
1.maven依赖
首先从https://start.spring.io/ 选择需要的…最近喜欢上了springboot真是个好的脚手架。今天继续学习分页/排序/多表查询等复杂功能。按步骤记录如下. 按步骤做的发现不可用最终还是用的jdbctemplate解决。这也是一次经验。总计在最后。
1.maven依赖
首先从https://start.spring.io/ 选择需要的maven依赖 2. 配置好H2数据库
spring.h2.console.enabledtrue -- /h2-console 就可以访问数据库了。spring.datasource.nameuser-profile -- 定义数据库名或者说数据源名称。
spring.datasource.generate-unique-namefalse --trueh会自动生成随机名称false用定义的name
3. 数据库表创建并插入数据
约定创建表的脚本在src/main/resroucs/schema.sql
CREATE TABLE users (id BIGINT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(255) NOT NULL,profile_id BIGINT
);CREATE TABLE profiles (id BIGINT AUTO_INCREMENT PRIMARY KEY,address VARCHAR(255),phone_number VARCHAR(255)
);添加数据是data.sql,要实现分页数据要多一些插入8条数据准备5条分一页。
INSERT INTO profiles (address, phone_number) VALUES (123 Main St, 555-1234);
INSERT INTO users (username, profile_id) VALUES (john_doe, 1);
INSERT INTO profiles (address, phone_number) VALUES (222 qian St, 555-2222);
INSERT INTO users (username, profile_id) VALUES (qianer, 2);
INSERT INTO profiles (address, phone_number) VALUES (333 zhang St, 555-3333);
INSERT INTO users (username, profile_id) VALUES (zhangsan, 3);
INSERT INTO profiles (address, phone_number) VALUES (444 li St, 555-4444);
INSERT INTO users (username, profile_id) VALUES (lisi, 4);
INSERT INTO profiles (address, phone_number) VALUES (555 wang St, 555-5555);
INSERT INTO users (username, profile_id) VALUES (wangwu, 5);
INSERT INTO profiles (address, phone_number) VALUES (666 zhao St, 555-6666);
INSERT INTO users (username, profile_id) VALUES (zhaoliu, 6);
INSERT INTO profiles (address, phone_number) VALUES (777 tian St, 555-7777);
INSERT INTO users (username, profile_id) VALUES (tianqi, 7);
INSERT INTO profiles (address, phone_number) VALUES (888 tian St, 555-8888);
INSERT INTO users (username, profile_id) VALUES (gongba, 8);4. 编写JAVA Entities
用户和档案个一个class
// User.java
import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Table;Table(users)
public class User {Idprivate Long id;private String username;private Long profileId; // Reference to Profile// getters and setters
}// Profile.java
import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Table;Table(profiles)
public class Profile {Idprivate Long id;private String address;private String phoneNumber;// getters and setters
}5. 创建一个DTO类用于jion result
DTO的意思是Data Transfer Object, 用于存放join的查询结果。
public class UserProfileDTO {private String username;private String address;private String phoneNumber;// constructor, getters, and setters
}6. 创建一个Repository类其中查询是定制方法
使用Query定制查询并增加pagination and sorting:
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Page;
import java.util.List;public interface UserRepository extends CrudRepositoryUser, Long {Query(SELECT u.username, p.address, p.phoneNumber FROM users u JOIN profiles p ON u.profileId p.id ORDER BY p.id)PageUserProfileDTO findUsersWithProfiles(Pageable pageable);
}7. 创建Service类
service类中其实可以做一些转换这里简化直接返回
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;Service
public class UserProfileService {Autowiredprivate UserRepository userRepository;public PageUserProfileDTO getUsersWithProfiles(Pageable pageable) {return userRepository.findUsersWithProfiles(pageable);}
}8. 创建Controller类暴露接口
目的就是expose the service
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;RestController
public class UserProfileController {Autowiredprivate UserProfileService userProfileService;GetMapping(/users-with-profiles)public PageUserProfileDTO getUsersWithProfiles(RequestParam int page,RequestParam int size,RequestParam String sortBy) {Pageable pageable PageRequest.of(page, size, Sort.by(sortBy));return userProfileService.getUsersWithProfiles(pageable);}
}这个返回的是一个json格式的对象。
如果和thymeleaf配合需要修改一下返回值。
分页的原理有点难还需要进一步分析研究。
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
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 UserProfileController {Autowiredprivate UserProfileService userProfileService;GetMapping(/users-with-profiles)public String getUsersWithProfiles(RequestParam(defaultValue 0) int page,RequestParam(defaultValue 10) int size,RequestParam(defaultValue id) String sortBy,Model model) {PageUserProfileDTO userProfilesPage userProfileService.getUsersWithProfiles(PageRequest.of(page, size, Sort.by(sortBy)));model.addAttribute(userProfiles, userProfilesPage.getContent());model.addAttribute(page, page);model.addAttribute(size, size);model.addAttribute(sortBy, sortBy);model.addAttribute(totalPages, userProfilesPage.getTotalPages());return users;}
}9. 创建动态HTML
需要放在 src/main/resources/templates目录下users.html
!DOCTYPE html
html xmlns:thhttp://www.thymeleaf.org
headtitleUsers with Profiles/title
/head
bodyh1Users with Profiles/h1tabletheadtrthUsername/ththAddress/ththPhone Number/th/tr/theadtbodytr th:eachuserProfile : ${userProfiles}td th:text${userProfile.username}/tdtd th:text${userProfile.address}/tdtd th:text${userProfile.phoneNumber}/td/tr/tbody/tablediva th:href{|/users-with-profiles?page${page - 1}size${size}sortBy${sortBy}|} th:if${page 0}Previous/aa th:href{|/users-with-profiles?page${page 1}size${size}sortBy${sortBy}|} th:if${page totalPages - 1}Next/a/div
/body
/html10. 解决错误切换JdbcTemplate
上面看似完美的实现运行起来就报错了。
the program run and report the exception:
Caused by: java.lang.UnsupportedOperationException: Page queries are not supported using string-based queries; Offending method: public abstract org.springframework.data.domain.Page dev.zzz.repository.UserRepository.findUsersWithProfiles(org.springframework.data.domain.Pageable)at org.springframework.data.jdbc.repository.query.StringBasedJdbcQuery.init(StringBasedJdbcQuery.java:172) ~[spring-data-jdbc-3.4.0.jar:3.4.0]at org.springframework.data.jdbc.repository.support.JdbcQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JdbcQueryLookupStrategy.java:168) ~[spring-data-jdbc-3.4.0.jar:3.4.0]at org.springframework.data.jdbc.repository.support.JdbcQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JdbcQueryLookupStrategy.java:255) ~[spring-data-jdbc-3.4.0.jar:3.4.0]at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lookupQuery(QueryExecutorMethodInterceptor.java:116) ~[spring-data-commons-3.4.0.jar:3.4.0]... 59 common frames omitted
原因是The exception you’re encountering is due to the fact that Spring Data JDBC does not support pagination with string-based queries directly. To handle pagination and sorting with custom queries, you need to use a different approach. Here’s how you can achieve this:
Custom Repository Interface
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;public interface CustomUserRepository {PageUserProfileDTO findUsersWithProfiles(Pageable pageable);
}Custom Repository Implementation
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.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;import java.util.List;Repository
public class CustomUserRepositoryImpl implements CustomUserRepository {Autowiredprivate JdbcTemplate jdbcTemplate;private final RowMapperUserProfileDTO rowMapper (rs, rowNum) - new UserProfileDTO(rs.getString(username),rs.getString(address),rs.getString(phoneNumber));Overridepublic PageUserProfileDTO findUsersWithProfiles(Pageable pageable) {String query SELECT u.username, p.address, p.phoneNumber FROM users u JOIN profiles p ON u.profileId p.id ORDER BY pageable.getSort().toString().replace(:, ) LIMIT pageable.getPageSize() OFFSET pageable.getOffset();ListUserProfileDTO userProfiles jdbcTemplate.query(query, rowMapper);String countQuery SELECT COUNT(*) FROM users u JOIN profiles p ON u.profileId p.id;Long total jdbcTemplate.queryForObject(countQuery, Long.class);return new PageImpl(userProfiles, pageable, total);}
}这部分代码是关键里面有几个坑
首先运行查询提示
org.h2.jdbc.JdbcSQLSyntaxErrorException: Ambiguous column name ID; SQL statement:
SELECT u.username, p.address, p.phoneNumber FROM users u JOIN profiles p ON u.profileId p.id ORDER BY id ASC LIMIT 5 OFFSET 0 [90059-232]at org.h2.message.DbException.getJdbcSQLException(DbException.java:644) ~[h2-2.3.232.jar:2.3.232]at org.h2.message.DbException.getJdbcSQLException(DbException.java:489) ~[h2-2.3.232.jar:2.3.232]at org.h2.message.DbException.get(DbException.java:223) ~[h2-2.3.232.jar:2.3.232]at org.h2.message.DbException.get(DbException.java:199) ~[h2-2.3.232.jar:2.3.232]at org.h2.expression.ExpressionColumn.mapColumn(ExpressionColumn.java:197) ~[h2-2.3.232.jar:2.3.232]at org.h2.expression.ExpressionColumn.mapColumns(ExpressionColumn.java:175) ~[h2-2.3.232.jar:2.3.232]
关联表最好不要都叫ID无法区分改为
ORDER BY p. pageable.getSort().toString().replace(:, )
指定了profiles表的id
然后又提示错误找不到列phoneNumber,profileid经检查对象中的名字和数据库不一样数据库有下划线jdbcTemplate是不允许隐式转换的所以必须和表中的字段名一致。修改后就可以了。 Update the UserRepository Interface
import org.springframework.data.repository.CrudRepository;public interface UserRepository extends CrudRepositoryUser, Long, CustomUserRepository {
}11. 界面不美观bootstrap加上渲染
https://getbootstrap.com/docs/5.3/getting-started/introduction/
下面一段不知道能否使用。
head中增加
link hrefhttps://cdn.jsdelivr.net/npm/bootstrap5.3.3/dist/css/bootstrap.min.css relstylesheet integritysha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hWALEwIH crossoriginanonymous
table classtable table-dark
效果如下
