package com.skyline.wo.dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import com.skyline.base.dao.impl.BaseDaoImpl;
import com.skyline.base.type.Activity;
import com.skyline.common.bean.Page;
import com.skyline.common.cache.annotation.Cache;
import com.skyline.common.cache.annotation.CacheCategoryType;
import com.skyline.common.cache.annotation.CacheDelete;
import com.skyline.common.cache.annotation.Fk;
import com.skyline.common.cache.annotation.Param;
import com.skyline.wo.dao.PhotoDao;
import com.skyline.wo.mapper.PhotoMapper;
import com.skyline.wo.model.Photo;
//TODO:加注释
@Repository("photoDao")
public class PhotoDaoImpl extends BaseDaoImpl implements PhotoDao {
/**
* select
* id,ownerId,ownerNickname,ownerPortrait,albumId,albumName,description
* ,file,middleFile,smallFile,ext,
* size,orderNo,commentNum,visitNum,up,down,shareNum,createTime,updateTime
* ,authority,activity from photo where albumId=? and authority<=? and
* (activity=? or activity=?) order by orderNo,id desc
* */
@Value("${PhotoDao.queryPhotosWithDetailOfAlbum}")
private String queryPhotosWithDetailOfAlbumSql;
/**
* select
* id,ownerId,ownerNickname,ownerPortrait,albumId,albumName,description
* ,file,middleFile,smallFile,ext,
* size,orderNo,commentNum,visitNum,up,down,shareNum,createTime,updateTime
* ,authority,activity from photo where id=? limit 1
* */
@Value("${PhotoDao.queryPhotoDetailById}")
private String queryPhotoDetailByIdSql;
/**
* select id,ownerId,ownerNickname,ownerPortrait,albumId,albumName,file,
* middleFile,smallFile ,ext,size,orderNo
* ,commentNum,visitNum,up,down,shareNum,createTime,updateTime,authority
* ,activity from photo where id=? limit 1
* */
@Value("${PhotoDao.queryPhotoById}")
private String queryPhotoByIdSql;
/**
* update photo set authority=?,albumName=? where albumId=?
* */
@Value("${PhotoDao.updatePhotoInfoOfAlbum}")
private String updatePhotoInfoOfAlbumSql;
/**
* update photo set visitCount=visitCount+? where id=?
* */
@Value("${PhotoDao.updateVisitCount}")
private String updateVisitCountSql;
/**
* update photo set activity=? where albumId=?
* */
@Value("${PhotoDao.updatePhotosActivityByAlbumId}")
private String updatePhotosActivityByAlbumIdSql;
/**
* insert into
* photo(ownerId,ownerNickname,ownerPortrait,albumId,albumName,description
* ,file,middleFile,smallFile,ext,size,orderNo,commentNum,visitNum,up,down,
* shareNum,createTime, updateTime ,authority,activity) values
* (?,?,?,?,?,null,?,?,?,?,?,0,0,0,0,0,0,current_timestamp
* ,current_timestamp,?,?)
* */
@Value("${PhotoDao.insertPhotosReturnIds}")
private String insertPhotosReturnIdsSql;
/**
* update photo set description=? where id=?
* */
@Value("${PhotoDao.updatePhotosDescription}")
private String updatePhotosDescriptionSql;
/**
* update photo set description=? where id=?
* */
@Value("${PhotoDao.updatePhotoDescription}")
private String updatePhotoDescriptionSql;
/**
* update photo a,album b set
* a.albumId=b.id,a.albumName=b.albumName,a.authority=b.authority where
* a.id=? and b.id=?
* */
@Value("${PhotoDao.updatePhotoAlbum}")
private String updatePhotoAlbumSql;
/**
* update photo set activity=? where id=?
* */
@Value("${PhotoDao.updatePhotoActivity}")
private String updatePhotoActivitySql;
/**
* select id,middleFile,smallFile,ext,size,orderNo from photo where
* albumId=? and authority<=? and (activity=? or activity=?) order by
* orderNo,id desc
* */
@Value("${PhotoDao.queryPhotoFilesOfAlbum}")
private String queryPhotoFilesOfAlbumSql;
/**
* insert into
* photo(ownerId,ownerNickname,ownerPortrait,albumId,albumName,file
* ,middleFile
* ,smallFile,ext,size,orderNo,commentNum,visitNum,up,down,shareNum
* ,createTime,updateTime,authority,activity) select
* ownerId,ownerNickname,ownerPortrait
* ,?,?,file,middleFile,smallFile,ext,size
* ,0,0,0,0,0,0,current_timestamp,current_timestamp,0,? from photo p where
* p.id=?
* */
@Value("${PhotoDao.insertByCopyExistPhotoReturnId}")
private String insertByCopyExistPhotoReturnIdSql;
/**
* select id,ownerId,ownerNickname,ownerPortrait,albumId,albumName,file,
* middleFile
* ,smallFile,ext,size,orderNo,commentNum,visitNum,up,down,shareNum
* ,createTime,updateTime,authority,activity from photo where albumId=? and
* activity=? order by orderNo,id desc limit 1
* */
@Value("${PhotoDao.queryLatestPhotoOfAlbum}")
private String queryLatestPhotoOfAlbumSql;
// /**
// * select
// *
// id,ownerId,ownerNickname,ownerPortrait,albumId,albumName,file,middleFile,smallFile,ext,size,
// * orderNo
// * ,commentNum,visitNum,up,down,shareNum,createTime,updateTime,authority
// * ,activity from photo where id=? and ownerId=? limit 1
// * */
// @Value("${PhotoDao.queryUserPhotoById}")
// private String queryUserPhotoByIdSql;
// /**
// * select
// *
// id,userId,userNick,albumId,albumName,file,middleFile,smallFile,ext,size,orderNo,commentCount
// * ,visitCount
// * ,upCount,downCount,shareCount,createDate,updateDate,authority,activity
// * from photo where albumId=? and (activity=? or activity=?) order by
// * orderNo,createDate
// * */
// @Value("${PhotoDao.queryPhotosOfAlbum}")
// private String queryPhotosOfAlbumSql;
@Cache(keyPattern = "photo-l-d-album:aid-auth:auth", type = CacheCategoryType.PHOTO, pagination = true)
public List<Photo> queryPhotosWithDetailOfAlbum(@Param("aid") @Fk long albumId,
@Param("auth") int authority, Page page) {
if (page == null) {
return jdbcTemplate
.query(queryPhotosWithDetailOfAlbumSql, PhotoMapper.getMapper(), albumId,
authority, Activity.NORMAL.toString(), Activity.UNDELETABLE.toString());
}
return this.getPaginationResult(queryPhotosWithDetailOfAlbumSql, page, PhotoMapper
.getMapper(), albumId, authority, Activity.NORMAL.toString(), Activity.UNDELETABLE
.toString());
}
@CacheDelete(type = CacheCategoryType.PHOTO)
public void updatePhotoInfoOfAlbum(int authority, String albumName, @Fk long albumId) {
jdbcTemplate.update(updatePhotoInfoOfAlbumSql, authority, albumName, albumId);
}
@Cache(keyPattern = "photo-d-:id", type = CacheCategoryType.PHOTO)
public Photo queryPhotoDetailById(@Param("id") long photoId) {
List<Photo> photos = jdbcTemplate.query(queryPhotoDetailByIdSql, PhotoMapper.getMapper(),
photoId);
if (photos.isEmpty()) {
return null;
} else {
return photos.get(0);
}
}
@Cache(keyPattern = "photo-:id", type = CacheCategoryType.PHOTO)
public Photo queryPhotoById(@Param("id") long photoId) {
List<Photo> photos = jdbcTemplate
.query(queryPhotoByIdSql, PhotoMapper.getMapper(), photoId);
if (photos.isEmpty()) {
return null;
} else {
return photos.get(0);
}
}
@CacheDelete(type = CacheCategoryType.PHOTO)
public void updateVisitCount(@Fk long albumId, long photoId, int addedCount) {
jdbcTemplate.update(updateVisitCountSql, addedCount, photoId);
}
@CacheDelete(type = CacheCategoryType.PHOTO)
public void updatePhotosActivityByAlbumId(@Fk long albumId, Activity activity) {
jdbcTemplate.update(updatePhotosActivityByAlbumIdSql, activity.toString(), albumId);
}
@CacheDelete(type = CacheCategoryType.PHOTO)
public List<Long> insertPhotosReturnIds(long ownerId, String ownerNickname,
String ownerPortrait, @Fk long albumId, String albumName, int authority,
Activity activity, List<String> fileKeys, List<String> middleFileKeys,
List<String> smallFileKeys, List<String> fileExts, List<Long> fileSizes) {
int length = Math.min(fileKeys.size(), fileSizes.size());
length = Math.min(fileExts.size(), length);
List<Object[]> argsList = new ArrayList<Object[]>(length);
if (length == 1) { // 只有单个就调insertWithIdReturn
long id = this.insertWithIdReturn(insertPhotosReturnIdsSql, ownerId, ownerNickname,
ownerPortrait, albumId, albumName, fileKeys.get(0), middleFileKeys.get(0),
smallFileKeys.get(0), fileExts.get(0), fileSizes.get(0), authority, activity
.toString());
List<Long> ids = new ArrayList<Long>(1);
ids.add(id);
return ids;
}
for (int i = 0; i < length; i++) {
argsList.add(new Object[] { ownerId, ownerNickname, ownerPortrait, albumId, albumName,
fileKeys.get(i), middleFileKeys.get(i), smallFileKeys.get(i), fileExts.get(i),
fileSizes.get(i), authority, activity.toString() });
}
return this.batchInsertWithIdReturn(insertPhotosReturnIdsSql, argsList);
}
@CacheDelete(type = CacheCategoryType.PHOTO)
public void updatePhotosDescription(@Fk long albumId, List<String> descriptions, List<Long> ids) {
int length = Math.min(descriptions.size(), ids.size());
List<Object[]> argsList = new ArrayList<Object[]>(length);
for (int i = 0; i < length; i++) {
argsList.add(new Object[] { descriptions.get(i), ids.get(i) });
}
this.batchUpdate(updatePhotosDescriptionSql, argsList);
}
@CacheDelete(type = CacheCategoryType.PHOTO)
public void updatePhotoDescription(@Fk long albumId, String description, long id) {
jdbcTemplate.update(updatePhotoDescriptionSql, description, id);
}
@CacheDelete(type = CacheCategoryType.PHOTO)
public void updatePhotoAlbum(long photoId, @Fk long albumId) {
jdbcTemplate.update(updatePhotoAlbumSql, photoId, albumId);
}
@CacheDelete(type = CacheCategoryType.PHOTO)
public void updatePhotoActivity(@Fk long albumId, long photoId, Activity activity) {
jdbcTemplate.update(updatePhotoActivitySql, activity.toString(), photoId);
}
@Cache(keyPattern = "photo-l-f-album:aid-auth:auth", type = CacheCategoryType.PHOTO)
public List<Photo> queryPhotoFilesOfAlbum(@Param("aid") @Fk long albumId,
@Param("auth") int authority) {
return jdbcTemplate.query(queryPhotoFilesOfAlbumSql, new RowMapper<Photo>() {
public Photo mapRow(ResultSet rs, int rowNum) throws SQLException {
Photo photo = new Photo();
photo.setId(rs.getLong(PhotoMapper.COLUMN_ID));
photo.setMiddleFile(rs.getString(PhotoMapper.COLUMN_MIDDLE_FILE));
photo.setSmallFile(rs.getString(PhotoMapper.COLUMN_SMALL_FILE));
photo.setExt(rs.getString(PhotoMapper.COLUMN_EXT));
photo.setSize(rs.getLong(PhotoMapper.COLUMN_SIZE));
photo.setOrderNo(rs.getInt(PhotoMapper.COLUMN_ORDERNO));
return photo;
}
}, albumId, authority, Activity.NORMAL.toString(), Activity.UNDELETABLE.toString());
}
@CacheDelete(type = CacheCategoryType.PHOTO)
public long insertByCopyExistPhotoReturnId(@Fk long destAlbumId, String destAlbumName,
Activity destActivity, long srcPhotoId) {
return insertWithIdReturn(insertByCopyExistPhotoReturnIdSql, destAlbumId, destAlbumName,
destActivity.toString(), srcPhotoId);
}
@Cache(keyPattern = "photo-latest-album:aid-act:act", type = CacheCategoryType.PHOTO)
public Photo queryLatestPhotoOfAlbum(@Param("aid") long albumId, @Param("act") Activity activity) {
List<Photo> photos = jdbcTemplate.query(queryLatestPhotoOfAlbumSql,
PhotoMapper.getMapper(), albumId, activity.toString());
if (photos.isEmpty()) {
return null;
} else {
return photos.get(0);
}
}
// @Override
// public Photo queryUserPhotoById(long photoId, long userId) {
// List<Photo> photos = jdbcTemplate.query(queryUserPhotoByIdSql,
// PhotoMapper.getMapper(), photoId, userId);
// if (photos.isEmpty()) {
// return null;
// } else {
// return photos.get(0);
// }
// }
/** -------------------华丽的分割线，下面的还没实现呢-------------------------------- **/
// @Override
// public List<Photo> queryPhotosOfAlbum(long albumId) {
// return jdbcTemplate.query(queryPhotosOfAlbumSql, PhotoMapper.getMapper(),
// albumId, Activity.NORMAL.toString(),
// Activity.UNDELETABLE.toString());
// }
// @Override
// public void updatePhotoName(long photoId, String photoName) {
// // TODO Auto-generated method stub
// }
//
// @Override
// public int countUserPhotoById(long photoId, long userId) {
// // TODO Auto-generated method stub
// return 0;
// }
}