自己开发中常遇到的问题
1. 模糊查询常用的两种写法:
2.管理员获取用户信息
3.在buyer端获取用户信息
Buyer buyer = UserContext.getBuyer();
4.在Seller端获取信息
Seller seller = UserContext.getSeller();
5.范围查询的写法
6.删除数据库数据的写法
delete from 表名
7.DTO、VO的写法
继承DO,实现Serializable并添加版本序列号
8.对象之间Cope的方法
BeanUtils.copyProperties(goldCoinBankDTO, goldCoinBankDO);
将goldCoinBankDTO复制给goldCoinBankDO
9.按照发行时间查询,把发行时间按照一个月的区间查询,传参是只传busiDate (还没太搞明白 在GoldCoinBankManagerImpl中)
if (StringUtils.isNotEmpty(busiDate)) {
Date startDate = DateUtil.toDate(busiDate, "yyyy-MM");
// 开始时间
String startTime = DateUtil.toString(startDate, "yyyy-MM-dd HH:mm:ss");
Calendar right = Calendar.getInstance();
assert startDate != null;
right.setTime(startDate);
right.add(Calendar.MONTH, 1);
Date endDate = right.getTime();
// 结束时间
String endTime = DateUtil.toString(endDate, "yyyy-MM-dd HH:mm:ss");
sql += "and busi_date >= '" + startTime + "' and busi_date <= '" + endTime + "' ";
}
10.修改功能用sql语句体现
String sql = "update es_gold_coin_bank set data_valid = ? where id = ? ";
11.日期格式转换
1.String类型转换成Date类型
String expireDateStr = DateUtilsEx.getBeforeNowDate(new Date(), -periodsNum); // 原到期日期
log.info("expireDateStr = " + expireDateStr + ", dayReleaseAmount = " + dayReleaseAmount);
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd"); // 注意月份是MM
Date expireDate = simpleDateFormat.parse(expireDateStr);
12.Date类型转换成String类型
String str = "1996-10-01 10:10:10";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd-HH:mm:ss");
Date date = sdf.parse(str);
System.out.println(date.getTime());
13.管理员对店铺进行操作
@DeleteMapping(value = "/{id}")
@ApiOperation(value = "删除商家推荐")
@ApiImplicitParams({
@ApiImplicitParam(name = "id", value = "要删除的商家推荐主键", required = true, dataType = "int", paramType = "path")
})
public AdminEditShop deleteShopRecommend(@PathVariable Integer id) {
log.info(" id: " + id);
AdminEditShop adminEditShop = new AdminEditShop();
adminEditShop.setOperator("管理员删除商家推荐");
this.shopRecommendManager.delete(id);
return adminEditShop;
}
14.用sql语句查询数量
//焦点图不能超过8个
String sql = "select count(0) from es_focus_picture where client_type=?";
Integer count = this.daoSupport.queryForInt(sql, cmsFocusPicture.getClientType());
logger.info("] count = " + count);
if (count >= 8) {
throw new ServiceException(SystemErrorCode.E956.code(), "焦点图数量不能超过八张");
}
log.info("cmsFocusPicture= "+ JSON.toJSONString(cmsFocusPicture));
this.daoSupport.insert(cmsFocusPicture);
15.参数日期之间的转换
@Override
public Page<MarketNorm> listMarketNorm(Integer pageNo, Integer pageSize, String startDate, String endDate) {
List<Object> params = new ArrayList<>();
String sql = "select * from es_market_norm ";
if (StringUtils.isNotEmpty(startDate)) {
sql += "where date_format(? , '%Y%m%d') <= date_format(norm_date, '%Y%m%d') ";
params.add(startDate);
}
if (StringUtils.isNotEmpty(endDate)){
sql += "and date_format(? , '%Y%m%d') >= date_format(norm_date, '%Y%m%d') ";
params.add(endDate);
}
sql += " order by norm_date desc ";
log.info("sql = " + sql);
Page<MarketNorm> webPage = this.daoSupport.queryForPage(sql, pageNo, pageSize, MarketNorm.class, params.toArray());
log.info("webPage = " + JSON.toJSONString(webPage));
return webPage;
}
startDate和endDate是开始查询日期和查询结束日期,是参数,数据库中没有,norm_date是数据库中行情日期。
转换: date_format(? , '%Y%m%d') <= date_format(norm_date, '%Y%m%d');
16.分页查询时分页处理
/** 分页处理 */
if (null == pageNo || pageNo < 1) {
pageNo = 1;
}
if (null == pageSize || pageSize > 10) {
pageSize = 10;
}
或者
int pageNo = null == goldCoinBankDTO.getPageNo() ? 1 : goldCoinBankDTO.getPageNo();
if (pageNo < 1) {
pageNo = 1;
}
int pageSize = null == goldCoinBankDTO.getPageSize() ? 10 : goldCoinBankDTO.getPageSize();
17.多表联查
sql += "select fa.*, fb.id as member_car_detail_id, fb.vin, fb.license_plate_number, fb.car_type, fb.engine_number, " +
"fb.new_energy_type, fb.driving_license_face, fb.driving_license_back, fb.seat_num, fb.record_date, fb.opening_date " +
"from es_member_car fa left join es_member_car_detail fb on fa.member_car_id = fb.member_car_id " +
"where fa.member_car_id = " + memberCarId + " and fa.member_id = " + memberId;
@Override
@Transactional(value = "memberTransactionManager", propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
public CarAndCarDetailVO listMemberCarDetail(Integer memberCarId) {
int memberId = UserContext.getBuyer().getUid(); // 获取用户主键
// 查询数据库
String sql = "";
sql += "select fa.*, fb.id as member_car_detail_id, fb.vin, fb.license_plate_number, fb.car_type, fb.engine_number, " +
"fb.new_energy_type, fb.driving_license_face, fb.driving_license_back, fb.seat_num, fb.record_date, fb.opening_date " +
"from es_member_car fa left join es_member_car_detail fb on fa.member_car_id = fb.member_car_id " +
"where fa.member_car_id = " + memberCarId + " and fa.member_id = " + memberId;
log.info("sql = " + sql + " memberCarId = " + memberCarId + " memberId = " + memberId);
CarAndCarDetailVO carAndCarDetailVO = daoSupport.queryForObject(sql, CarAndCarDetailVO.class);
log.info("carAndCarDetailVO = " + JSON.toJSONString(carAndCarDetailVO));
return carAndCarDetailVO;
}
18.添加事务
@Transactional(value = "memberTransactionManager", propagation = Propagation.REQUIRED, rollbackFor = Exception.class)