多条件的模糊分页查询
controller
@Api(tags = "用户功能相关接口", value = "UserProfileController")
@RestController
@RequestMapping("/userProfile")
@Slf4j
public class UserProfileController {
@Resource
private UserProfileService userProfileService;
@ApiOperation(value = "通过mobile(模糊查询,返回用户的基本信息,是否是会员,是否是种子会员,是否是会员,会员到期时间)")
@GetMapping("/list")
public RtData getUserProfile(@ApiParam(name = "mobile", value = "手机号码", required = false)
@RequestParam(value = "mobile", required = false) String mobile,
@ApiParam(name = "uid", value = "用户id", required = false)
@RequestParam(value = "uid", required = false) String uid,
@ApiParam(name = "currentPage", value = "第几页", required = true)
@RequestParam("currentPage") Integer currentPage,
@ApiParam(name = "pageSize", value = "每页展示多少条数据", required = true)
@RequestParam("pageSize") Integer pageSize
) {
return userProfileService.getUserProfile(mobile, uid, currentPage, pageSize);
}
}
ServiceImpl
@Override
public RtData getUserProfile(String mobile, String uid,Integer currentPage, Integer pageSize) {
if (pageSize>100){
return ResponseBuilder.fail("pageSize不能超过100");
}
currentPage=(currentPage-1)*pageSize;
List<UserProfile> userProfiles=userProfileMapper.getUserProfile(mobile,uid,currentPage,pageSize);
List<UserProfileInfo> userProfileInfos= new ArrayList<>();
for (UserProfile userProfile: userProfiles) {
UserProfileInfo userProfileInfo = new UserProfileInfo();
userProfileInfos.add(userProfileInfo);
userProfileInfo.setRegistrationTime(userProfile.getRegistrationTime()).setUid(userProfile.getUid()).setNickname(userProfile.getNickname()).setUserType(userProfile.getUserType()).setRegisterFrom(userProfile.getRegisterFrom()).setMobile(userProfile.getMobile());
// 根据用户uid查询用户是否是是年费
List<DocsMemberRight> userLevel = docsMemberRightMapper.getUserLevel(String.valueOf(userProfile.getUid()));
if (!userLevel.isEmpty()) {
DocsMemberRight memberAuthDetail = userLevel.get(0);
Date rightsEnd = memberAuthDetail.getEndTime();
if (new Date().before(rightsEnd)){
userProfileInfo.setAccountLevel(userLevel.get(0).getAccountLevel());
}else{
userProfileInfo.setAccountLevel(1);
}
userProfileInfo.setRightsEnd(userLevel.get(0).getEndTime());
}
else {
userProfileInfo.setAccountLevel(1);
}
SeedUser seedUser = seedUserMapper.selectIsSeedUser(String.valueOf(userProfile.getUid()));
if (seedUser!=null){
userProfileInfo.setSeedUser(true);
userProfileInfo.setSeedUserType(seedUser.getType());
}else {
userProfileInfo.setSeedUser(false);
}
}
Integer userAmount = userProfileMapper.getUserAmount(mobile,uid,currentPage, pageSize);
RtData rtData = new RtData();
rtData.setList(userProfileInfos);
rtData.setData("total",userAmount);
return ResponseBuilder.success(rtData);
}
}
xml
<select id="getUserList" resultType="com.iflytek.iflydocs.admin.entity.vm.UserProfile">
select *
from note_user_profile
where uid = #{uid}
</select>
<select id="getUserProfile" resultType="com.iflytek.iflydocs.admin.entity.vm.UserProfile"
parameterType="com.iflytek.iflydocs.admin.entity.vm.UserProfile">
SELECT registration_time as registrationTime,uid,mobile,nickname,register_from as registerFrom,sex FROM
note_user_profile
<where>
<if test="mobile != null and mobile!= '' ">mobile like "%"#{mobile}"%"</if>
<if test="uid != null and uid!='' ">and uid like "%"#{uid}"%"</if>
</where>
limit #{currentPage}, #{pageSize}
</select>
<select id="getUserAmount" resultType="java.lang.Integer"
parameterType="com.iflytek.iflydocs.admin.entity.vm.UserProfile">
SELECT count(*) FROM note_user_profile
<where>
<if test="mobile != null">mobile like "%"#{mobile}"%"</if>
<if test="uid != null">and uid like "%"#{uid}"%"</if>
</where>
</select>
<select id="getNickName" resultType="java.lang.String"
parameterType="com.iflytek.iflydocs.admin.entity.vm.UserProfile">
SELECT nickname FROM note_user_profile where uid=#{uid}
</select>
pojo类
@Data
@Accessors(chain = true)
public class UserProfileInfo implements Serializable {
@JsonFormat ( pattern = "yyyy-MM-dd HH:mm:ss" , timezone = " GMT+8 " )
private Date registrationTime;
private Long uid;
private String nickname;
private String registerFrom;
private String mobile;
private Integer userType;
boolean isSeedUser;
String seedUserType;
Integer accountLevel;
@JsonFormat( pattern = "yyyy-MM-dd HH:mm:ss" , timezone = " GMT+8 " )
Date rightsEnd;
}
利用日期类为用户添加权益时间
ServiceImpl
@Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
public RtData setUserRights(String uid, Integer days) {
// 查询用户的历史权益
List<DocsMemberRight> userLevel = docsMemberRightMapper.getUserLevel(uid);
DocsMemberRight docsMemberRight = new DocsMemberRight();
docsMemberRight.setUid(Long.valueOf(uid));
// 如果用户的历史权益为空则新增权益
Date date = new Date();
if(userLevel .isEmpty() || days==0){
docsMemberRight = addDays(docsMemberRight, days, date);
}
else{
DocsMemberRight memberAuthDetail1 = userLevel.get(0);
Date rightsEnd = memberAuthDetail1.getEndTime();
//如果最晚截止权益的时间小于现在,则存入的起始日期为今天
if(rightsEnd.getTime()<date.getTime()){
docsMemberRight = addDays(docsMemberRight, days, date);
}else{
docsMemberRight = addDays(docsMemberRight, days, rightsEnd);
}
}
if (days>=365){
docsMemberRight.setIsAnnual(true);
}
else {
docsMemberRight.setIsAnnual(false);
}
docsMemberRight.setUpdateTime(new Date()).setAccountLevel(2).setCid("admin_user_auth").setRegistrationTime(date);
docsMemberRightMapper.setUserRights(docsMemberRight);
docsMemberRightMapper.setUserRightsRecord(docsMemberRight);
return ResponseBuilder.success("成功添加权益"+days+"天");
}
public DocsMemberRight addDays( DocsMemberRight docsMemberRight , Integer days , Date date ){
docsMemberRight.setStartTime(date);
Calendar instance = Calendar.getInstance();
instance.setTime(date);
instance.add(Calendar.DATE, days);
Date time = instance.getTime();
docsMemberRight.setEndTime(time);
return docsMemberRight;
}
}
mongodb多数据源配置
springboot2.2.5
@Configuration
@EnableMongoRepositories(basePackages = "com.iflytek.iflydocs.admin.mapper",//此处是mapper层路径
mongoTemplateRef = "fsMongoTemplate")
public class FsMongoConfig {
@Bean
@Primary
@ConfigurationProperties(prefix = "spring.data.mongodb")
public MongoProperties fsMongoProperties() {
return new MongoProperties();
}
@Primary
@Bean(name = "fsMongoTemplate")
public MongoTemplate fsMongoTemplate(MongoMappingContext context, BeanFactory beanFactory) throws Exception {
return new MongoTemplate(fsFactory(), mappingMongoConverter(fsFactory(), context, beanFactory));
}
@Bean
@Primary
public SimpleMongoClientDbFactory fsFactory() {
// SimpleMongoClientDbFactory
ConnectionString connString = new ConnectionString(
fsMongoProperties().getUri()
);
MongoClientSettings settings = MongoClientSettings.builder()
.applyConnectionString(connString)
.retryWrites(true)
.build();
MongoClient mongoClient = MongoClients.create(settings);
return new SimpleMongoClientDbFactory(mongoClient, fsMongoProperties().getDatabase());
}
/**
* 注册一次即可
*
* @param factory
* @param context
* @param beanFactory
* @return
*/
@Bean
public MappingMongoConverter mappingMongoConverter(SimpleMongoClientDbFactory factory, MongoMappingContext context, BeanFactory beanFactory) {
DbRefResolver dbRefResolver = new DefaultDbRefResolver(factory);
MappingMongoConverter mappingConverter = new MappingMongoConverter(dbRefResolver, context);
try {
mappingConverter.setCustomConversions(beanFactory.getBean(CustomConversions.class));
} catch (NoSuchBeanDefinitionException ignore) {
}
// Don't save _class to mongo
mappingConverter.setTypeMapper(new DefaultMongoTypeMapper(null));
return mappingConverter;
}
}
mongodb数据库xml配置
#file system
spring.data.mongodb.uri=mongodb://iflynote_db_dev:6CwLPqM0UKaoVruW@172.16.59.106:10131/iflynote_db_dev
spring.data.mongodb.database=iflynote_db_dev
oss对象存储防盗链
xml配置
iflydocs.cssp.accessKeyId=4ce67c50458145958cdbe287ef10d1d0
iflydocs.cssp.accessKeySecret=4dd21345c9ff48d7ace8eb59d5247c14
iflydocs.cssp.containerBaseUrl=https://bjbdn.openstorage.cn
iflydocs.cssp.cdnContainerBaseUrl=https://bjb.openstorage.cn
iflydocs.cssp.tokenKey=YWdqaV9jc3Nw
iflydocs.cssp.tokenPathPrefix=/v1/agji/
iflydocs.cssp.expireSecond=3600
pojo类
@Document(collection = "oss_object")
@Data
public class OssObject implements Serializable {
@MongoId
private String id;
private String nickName;
private Long owner;
private String container;
private String fid;
@Field("create_time")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = " GMT+8 ")
private Date createTime;
private Long size;
@Field("copy_from")
private String copyFrom;
}
防盗链和文件分片信息查询
@Slf4j
@Service
public class OssAuthServiceImpl implements OssAuthService {
@Autowired
private CsspConfig csspConfig;
@Override
public String getSafetyChain(ContainerACL.AccessControlList acl, String container, String objectId, String method, boolean useCdn) {
if (StringUtils.isEmpty(objectId) || StringUtils.isEmpty(method) || (!method.equals(HttpMethod.GET.name()) && method.equals(HttpMethod.PUT.name()))) {
throw new RuntimeException();
}
try {
if ("PUT".equals(method)) {
// 如果获取的是上传的防盗链,则判断container是否存在,不存在则创建
CSSPClient csspClient = CSSPHandler.getInstance(csspConfig.getAccessKeyId(), csspConfig.getAccessKeySecret(), csspConfig.getContainerBaseUrl() + csspConfig.getTokenPathPrefix() + container);
Boolean isContainerExist = false;
ObjectMetadata objectMetadata = new ObjectMetadata();
try {
isContainerExist = csspClient.isContainerExist();
} catch (Exception e) {
log.error("", e);
isContainerExist = false;
}
if (!isContainerExist) {
csspClient.putContainer(objectMetadata);
csspClient.setContainerACL(acl);
}
}
int expires = (int) (System.currentTimeMillis() / 1000) + csspConfig.getExpireSecond();
String path = csspConfig.getTokenPathPrefix() + container + "/" + objectId;
String safetychainToken;
String key = csspConfig.getTokenKey();
try {
path = URLDecoder.decode(path, "utf-8");
String hmacBody = method + "\n" + expires + "\n" + path;
SecretKeySpec signingKey = new SecretKeySpec(key.getBytes("utf-8"), "HmacSHA1");
Mac mac = Mac.getInstance("HmacSHA1");
mac.init(signingKey);
byte[] rawHmac = mac.doFinal(hmacBody.getBytes("utf-8"));
safetychainToken = Hex.encodeHexString(rawHmac);
} catch (Exception e) {
log.error("getSafetychainToken Exception!", e);
throw new RuntimeException("获取临时链异常");
}
StringBuffer safetychainSb = new StringBuffer();
if (useCdn) {
safetychainSb.append(csspConfig.getCdnContainerBaseUrl());
} else {
safetychainSb.append(csspConfig.getContainerBaseUrl());
}
safetychainSb.append(path).append("?");
// 图片缩略后置处理
safetychainSb.append("token=").append(safetychainToken).append("&e=").append(expires);
String safetychain = safetychainSb.toString();
return safetychain;
} catch (Exception e) {
log.error("系统异常!", e);
throw new RuntimeException("获取临时链异常");
}
}
@Override
public List<ObjectList> getManifest(String fid, String objectId, String container) {
try {
CSSPClient csspClient = CSSPHandler.getInstance(
csspConfig.getAccessKeyId(),
csspConfig.getAccessKeySecret(),
csspConfig.getContainerBaseUrl() +
csspConfig.getTokenPathPrefix() +
container);
if (csspClient.isObjectExist(objectId)) {
List<ObjectList> result = csspClient.listMultipartUpload(objectId, fid);
log.info("获取分片清单结果:{}", result);
return result;
} else {
return new ArrayList<>();
}
} catch (Exception e) {
log.error("获取分片清点异常!", e);
return new ArrayList<>();
}
}
}
controller
@Api(tags = "用户文件信息", value = "OssObjectController")
@RestController
@RequestMapping("/ossObject")
@Slf4j
public class OssObjectController {
@Autowired
private OssObjectService ossObjectService;
@ApiOperation(value = "获取用户上传文件信息")
@GetMapping("/list")
public RtData list(@ApiParam(name = "fid", value = "fid", required = false)
@RequestParam(value = "fid", required = false) String fid,
@ApiParam(name = "objectId", value = "objectId", required = false)
@RequestParam(value = "objectId", required = false) String objectId,
@ApiParam(name = "pageNum", value = "第几页", required = true)
@RequestParam("pageNum") Integer pageNum,
@ApiParam(name = "pageSize", value = "每页展示多少条数据", required = true)
@RequestParam("pageSize") Integer pageSize) {
if (pageNum == null || pageSize == null) {
return ResponseBuilder.fail("数据不能为空");
}
if (pageNum <= 0 || pageSize > 50) {
return ResponseBuilder.fail("起始页不能超过限制");
}
PageOssObject pageOssObject = new PageOssObject(pageNum, pageSize, fid, objectId);
return ossObjectService.list(pageOssObject);
}
}
Service层
@Service
@Slf4j
public class OssObjectServiceImpl implements OssObjectService {
@Autowired
private OssObjectMapper ossObjectMapper;
@Autowired
private OssAuthService ossAuthService;
@Autowired
private CsspConfig csspConfig;
@Autowired
private UserProfileMapper userProfileMapper;
@Override
public RtData list(PageOssObject pageOssObject) {
Map<String, Object> ossObjectMap = ossObjectMapper.findOssObject(pageOssObject);
List<OssObject> ossObjectList = (List<OssObject>) ossObjectMap.get("data");
if (ossObjectList.isEmpty()) {
return ResponseBuilder.fail("输入有误");
}
String nickNmae = "";
Map<String, Object> safetyChainMap = new HashMap<>();
for (OssObject ossObject : ossObjectList) {
if ("".equals(nickNmae)) {
nickNmae = userProfileMapper.getNickName(ossObject.getOwner());
}
// 获取安全连接
String safetyChain = ossAuthService.getSafetyChain(ContainerACL.AccessControlList.Private, ossObject.getContainer(), ossObject.getId(), HttpMethod.GET.name(), false);
Map<String, Object> safetyChainObjectMap = new HashMap<>();
ossObject.setNickName(nickNmae);
safetyChainObjectMap.put("data", ossObject);
CSSPClient csspClient = CSSPHandler.getInstance(csspConfig.getAccessKeyId(),
csspConfig.getAccessKeySecret(), csspConfig.getContainerBaseUrl() + csspConfig.getTokenPathPrefix() + ossObject.getContainer());
try {
if (csspClient.isObjectExist(ossObject.getId())) {
ObjectMetadata objectMetadata = csspClient.getObjectMetadata(ossObject.getId());
safetyChainObjectMap.put("objectMetadata", objectMetadata);
//objectMetadata 中包含文件信息
}
} catch (CSSPException e) {
e.printStackTrace();
log.error("reportTime | CSSPException,{}", e);
return ResponseBuilder.fail(RtCode.SERVER_ERROR);
} catch (IOException e) {
e.printStackTrace();
log.error("reportTime | IOException,{}", e);
return ResponseBuilder.fail(RtCode.SERVER_ERROR);
}
// 获取分片信息
safetyChainObjectMap.put("Manifest", ossAuthService.getManifest(ossObject.getFid(), ossObject.getId(), ossObject.getContainer()));
safetyChainMap.put(safetyChain, safetyChainObjectMap);
}
ossObjectMap.put("data", safetyChainMap);
return ResponseBuilder.success(ossObjectMap);
}
}
Mapper层
@Component
public class OssObjectMapperImpl implements OssObjectMapper {
@Autowired
@Qualifier("fsMongoTemplate")
MongoTemplate fsMongoTemplate;
@Override
public Map<String, Object> findOssObject(PageOssObject ossObject) {
int currentPage = ossObject.getPageNum();
int pageSize = ossObject.getPageSize();
String fid = ossObject.getFid();
String objectId = ossObject.getObjectId();
Map<String, Object> resultMap = new HashMap<>();
Query query = new Query();
if (ossObject.getObjectId() != null) {
query.addCriteria(Criteria.where("id").is(objectId));
}
if (ossObject.getFid() != null) {
query.addCriteria(Criteria.where("fid").is(fid));
}
int totalCount = (int) fsMongoTemplate.count(query, OssObject.class);
query.skip((currentPage - 1) * pageSize).limit(pageSize);
// 数据总页数
int totalPage = totalCount % pageSize == 0 ? totalCount / pageSize : totalCount / pageSize + 1;
// 设置记录总数和总页数
resultMap.put("totalCount", totalCount);
resultMap.put("totalPage", totalPage);
// 按创建时间倒序
Query create_time = query.with(Sort.by(Sort.Direction.DESC, "create_time"));
// 查询当前页数据集合
List<OssObject> records = fsMongoTemplate.find(create_time, OssObject.class);
resultMap.put("data", records);
return resultMap;
}
}