diff options
Diffstat (limited to 'src/main/java/com/mesasoft/cn/dao')
12 files changed, 1120 insertions, 0 deletions
diff --git a/src/main/java/com/mesasoft/cn/dao/AuthDAO.java b/src/main/java/com/mesasoft/cn/dao/AuthDAO.java new file mode 100644 index 0000000..2d62e69 --- /dev/null +++ b/src/main/java/com/mesasoft/cn/dao/AuthDAO.java @@ -0,0 +1,107 @@ +package com.mesasoft.cn.dao; + +import com.mesasoft.cn.dao.sqlprovider.AuthSqlProvider; +import com.mesasoft.cn.entity.Auth; +import com.mesasoft.cn.model.AuthRecord; +import org.apache.ibatis.annotations.*; +import org.springframework.stereotype.Repository; + +import java.util.List; + +/** + * @author pantao + * @since 2018/1/19 + */ +@Repository +public interface AuthDAO { + + /** + * 检测某个权限是否存在 + * + * @param userId 用户编号 + * @param fileId 文件编号 + * + * @return {@link Auth} + */ + @Select("select * from auth where user_id=#{userId} and file_id=#{fileId}") + Auth exists(@Param("userId") int userId, @Param("fileId") long fileId); + + /** + * 批量删除权限记录 + * + * @param ids 权限编号集 + * + * @return 是否删除成功 + */ + @DeleteProvider(type = AuthSqlProvider.class, method = "batchDelete") + boolean batchDelete(@Param("ids") String ids); + + /** + * 添加一条权限记录 + * + * @param auth {@link Auth} + * + * @return 是否添加成功 + */ + @Insert("insert into auth(user_id,file_id,is_downloadable,is_uploadable,is_deletable,is_updatable,is_visible) " + + "values(#{userId},#{fileId},#{isDownloadable},#{isUploadable},#{isDeletable},#{isUpdatable},#{isVisible})") + boolean insertAuth(Auth auth); + + /** + * 删除一条权限记录 + * + * @param id 编号 + */ + @Delete("delete from auth where id=#{id}") + void removeAuthById(int id); + + /** + * 删除一条权限记录 + * + * @param userId 编号 + */ + @Delete("delete from auth where user_id=#{userId}") + void removeAuthByUserId(int userId); + + /** + * 删除一条权限记录 + * + * @param fileId 编号 + * + * @return 是否删除成功 + */ + @Delete("delete from auth where file_id=#{fileId}") + boolean removeAuthByFileId(long fileId); + + /** + * 更新权限记录 + * + * @param id 编号 + * @param isDownloadable 下载权限 + * @param isUploadable 上传权限 + * @param isVisible 可查权限 + * @param isDeletable 删除权限 + * @param isUpdatable 更新权限 + * + * @return 是否更新成功 + */ + @UpdateProvider(type = AuthSqlProvider.class, method = "updateAuthById") + boolean updateAuthById(@Param("id") long id, @Param("isDownloadable") int isDownloadable, @Param("isUploadable") + int isUploadable, @Param("isDeletable") int isDeletable, @Param("isUpdatable") int isUpdatable, @Param + ("isVisible") int isVisible); + + /** + * 获取权限记录 + * + * @param id 编号，值小于等于0时不作为条件 + * @param userId 用户编号，值小于等于0时不作为条件 + * @param fileId 文件编号，值小于等于0时不作为条件 + * @param fileName 模糊搜索文件名（当参数不为空时） + * @param offset 偏移 + * + * @return {@link List} + */ + @SelectProvider(type = AuthSqlProvider.class, method = "getAuthBy") + List<AuthRecord> listAuthBy(@Param("id") long id, @Param("userId") int userId, @Param("fileId") long fileId, + @Param("fileName") String fileName, @Param("offset") int offset); +} diff --git a/src/main/java/com/mesasoft/cn/dao/CategoryDAO.java b/src/main/java/com/mesasoft/cn/dao/CategoryDAO.java new file mode 100644 index 0000000..848d6b8 --- /dev/null +++ b/src/main/java/com/mesasoft/cn/dao/CategoryDAO.java @@ -0,0 +1,103 @@ +package com.mesasoft.cn.dao; + +import com.mesasoft.cn.entity.Category; +import org.apache.ibatis.annotations.*; +import org.springframework.stereotype.Repository; + +import java.util.List; + +/** + * @author pantao + * @since 2018/1/18 + */ +@Repository +public interface CategoryDAO { + + /** + * 通过分类名获取ID + * + * @param name 分类名 + * + * @return {@link Integer} + */ + @Select("select id from category where name=#{name}") + int getIdByName(String name); + + /** + * 添加一个分类 + * + * @param name 分类名 + * + * @return 是否添加成功 + */ + @Insert("insert into category(name) values(#{name})") + boolean insertCategory(String name); + + /** + * 通过编号删除一个分类 + * + * @param id 编号 + * + * @return 是否删除成功 + */ + @Delete("delete from category where id=#{id}") + boolean removeCategoryById(int id); + + /** + * 通过名称删除一个分类 + * + * @param name 分类名称 + * + * @return 是否删除成功 + */ + @Delete("delete from category where name=#{name}") + boolean removeCategoryByName(String name); + + /** + * 更新一个分类名 + * + * @param name 分类名 + * @param id 分类ID + * + * @return 是否更新成功 + */ + @Update("update category set name=#{name} where id=#{id}") + boolean updateNameById(@Param("id") int id, @Param("name") String name); + + /** + * 通过分类名更新分类名 + * + * @param newName 新的分类名 + * @param oldName 旧的分类名 + */ + @Update("update category set name=#{newName} where name=#{oldName}") + void updateNameByName(String newName, String oldName); + + /** + * 获取所有分类 + * + * @return {@link List} + */ + @Select("select * from category") + List<Category> listCategory(); + + /** + * 通过编号获取一个分类 + * + * @param id 编号 + * + * @return {@link Category} + */ + @Select("select * from category where id=#{id}") + Category getCategoryById(int id); + + /** + * 通过名称获取一个分类 + * + * @param name 名称 + * + * @return {@link Category} + */ + @Select("select * from category where name=#{name}") + Category getCategoryByName(String name); +} diff --git a/src/main/java/com/mesasoft/cn/dao/DownloadedDAO.java b/src/main/java/com/mesasoft/cn/dao/DownloadedDAO.java new file mode 100644 index 0000000..263945c --- /dev/null +++ b/src/main/java/com/mesasoft/cn/dao/DownloadedDAO.java @@ -0,0 +1,53 @@ +package com.mesasoft.cn.dao; + +import com.mesasoft.cn.dao.sqlprovider.DownloadedSqlProvider; +import com.mesasoft.cn.model.DownloadRecord; +import org.apache.ibatis.annotations.Delete; +import org.apache.ibatis.annotations.Insert; +import org.apache.ibatis.annotations.Param; +import org.apache.ibatis.annotations.SelectProvider; +import org.springframework.stereotype.Repository; + +import java.util.List; + +/** + * @author pantao + * @since 2018/1/18 + */ +@Repository +public interface DownloadedDAO { + + /** + * 新增一条下载记录 + * + * @param userId 用户编号 + * @param fileId 文件编号 + */ + @Insert("insert into download(user_id,file_id) values(#{userId},#{fileId})") + void insertDownload(@Param("userId") int userId, @Param("fileId") long fileId); + + /** + * 查询下载记录 + * + * @param userId 用户编号，不使用用户编号作为条件时设置值小于等于0即可 + * @param fileId 文件编号，不使用文件编号作为条件时设置值小于等于0即可 + * @param categoryId 分类编号，不用分类编号作为条件时设置值小于等于0即可 + * @param fileName 文件名，不使用文件名作为条件时设置值为空即可 + * @param offset 偏移 + * + * @return 下载记录 + */ + @SelectProvider(type = DownloadedSqlProvider.class, method = "getDownloadBy") + List<DownloadRecord> listDownloadedBy(@Param("userId") int userId, @Param("fileId") long fileId, @Param + ("fileName") String fileName, @Param("categoryId") int categoryId, @Param("offset") int offset); + + /** + * 删除文件 + * + * @param fileId 文件编号 + * + * @return 是否删除成功 + */ + @Delete("delete from download where file_id=#{fileId}") + boolean removeByFileId(long fileId); +} diff --git a/src/main/java/com/mesasoft/cn/dao/FileDAO.java b/src/main/java/com/mesasoft/cn/dao/FileDAO.java new file mode 100644 index 0000000..fe33c54 --- /dev/null +++ b/src/main/java/com/mesasoft/cn/dao/FileDAO.java @@ -0,0 +1,340 @@ +package com.mesasoft.cn.dao; + +import com.mesasoft.cn.dao.sqlprovider.FileSqlProvider; +import com.mesasoft.cn.entity.File; +import com.mesasoft.cn.model.BaseAuthRecord; +import com.mesasoft.cn.model.FileBasicRecord; +import com.mesasoft.cn.model.FileRecord; +import org.apache.ibatis.annotations.*; +import org.springframework.stereotype.Repository; + +import java.util.List; + +/** + * @author pantao + * @since 2018/1/19 + */ +@Repository +public interface FileDAO { + + /** + * 获取文件权限 + * + * @param id 文件编号 + * + * @return {@link BaseAuthRecord} + */ + @Select("select is_downloadable,is_uploadable,is_deletable,is_updatable,is_visible from file where id=#{id}") + BaseAuthRecord getAuth(long id); + + /** + * 通过编号获取文件 + * + * @param id 编号 + * + * @return {@link File} + */ + @Select("select * from file where id=#{id}") + File getById(long id); + + /** + * 通过ID获取本地路径 + * + * @param fileId 文件编号 + * + * @return {@link String} + */ + @Select("select local_url from file where id=#{id}") + String getLocalUrlById(long fileId); + + /** + * 通过编号删除文件 + * + * @param id 编号 + * + * @return 是否删除成功 + */ + @Delete("delete from file where id=#{id}") + boolean removeById(long id); + + /** + * 通过本地路径获取文件编号 + * + * @param visitUrl 本地路径 + * + * @return 编号 + */ + @Select("select id from file where visit_url=#{visitUrl}") + long getIdByVisitUrl(String visitUrl); + + /** + * 通过本地路径获取文件编号 + * + * @param localUrl 本地路径 + * + * @return 编号 + */ + @Select("select id from file where local_url=#{localUrl}") + long getIdByLocalUrl(String localUrl); + + /** + * 通过访问路径获取本地文件路径 + * + * @param visitUrl 访问路径 + * + * @return {@link String} + */ + @Select("select local_url from file where visit_url=#{visitUrl}") + String getLocalUrlByVisitUrl(String visitUrl); + + /** + * 通过访问路径删除 + * + * @param visitUrl 访问路径 + * + * @return 是否删除成功 + */ + @Delete("delete from file where visit_url=#{visitUrl}") + boolean removeByVisitUrl(String visitUrl); + + /** + * 通过本地路径删除 + * + * @param localUrl 本地路径 + * + * @return 是否删除成功 + */ + @Delete("delete from file where local_url=#{localUrl}") + boolean removeByLocalUrl(String localUrl); + + /** + * 检查本地路径 + * + * @param localUrl 本地路径 + * + * @return {@link Integer} + */ + @Select("select count(*) from file where local_url=#{localUrl}") + int checkLocalUrl(String localUrl); + + /** + * 检查访问路径 + * + * @param visitUrl 访问路径 + * + * @return {@link Integer} + */ + @Select("select count(*) from file where visit_url=#{visitUrl}") + int checkVisitUrl(String visitUrl); + + /** + * 添加一个文件 + * + * @param file {@link File} + * + * @return 是否添加成功 + */ + @Insert("insert into file(name,suffix,local_url,visit_url,size,description,tag,user_id,category_id," + + "is_downloadable,is_uploadable,is_deletable,is_updatable,is_visible) values(#{name},#{suffix}," + + "#{localUrl},#{visitUrl},#{size},#{description},#{tag},#{userId},#{categoryId},#{isDownloadable}," + + "#{isUploadable},#{isDeletable},#{isUpdatable},#{isVisible})") + boolean insertFile(File file); + + /** + * 删除一个文件 + * + * @param id 文件编号 + */ + @Delete("delete from file where id=#{id}") + void deleteFileById(int id); + + /** + * 删除文件 + * + * @param userId 用户编号 + */ + @Delete("delete from file where user_id=#{userId}") + void deleteFileByUserId(int userId); + + /** + * 删除文件 + * + * @param categoryId 分类编号 + */ + @Delete("delete from file where category_d=#{categoryId}") + void deleteFileByCategoryId(int categoryId); + + /** + * 更新文件基本信息 + * + * @param file 文件 + * + * @return 是否更新成功 + */ + @Update("update file set name=#{name},suffix=#{suffix},local_url=#{localUrl},visit_url=#{visitUrl}," + + "description=#{description},tag=#{tag},category_id=#{categoryId},last_modify_time=current_timestamp " + + "where" + " id=#{id}") + boolean updateFileInfo(File file); + + /** + * 更新文件权限 + * + * @param id 编号 + * @param isDownloadable 下载权限 + * @param isUploadable 上传权限 + * @param isVisible 可查权限 + * @param isDeletable 删除权限 + * @param isUpdatable 上传权限 + * + * @return 是否更新成功 + */ + @UpdateProvider(type = FileSqlProvider.class, method = "updateAuthById") + boolean updateAuthById(@Param("id") long id, @Param("isDownloadable") int isDownloadable, @Param("isUploadable") + int isUploadable, @Param("isDeletable") int isDeletable, @Param("isUpdatable") int isUpdatable, @Param + ("isVisible") int isVisible); + + /** + * 更新文件名 + * + * @param id 编号 + * @param name 文件名 + * @param suffix 后缀名 + */ + @Update("update file set name=#{name},suffix=#{suffix},last_modify_time=current_timestamp where id=#{id}") + void updateFileNameById(@Param("id") int id, @Param("name") String name, @Param("suffix") String suffix); + + /** + * 更新文件修改时间 + * + * @param id 编号 + */ + @Update("update file set last_modify_time=current_timestamp where id=#{id}") + void updateLastModifyTimeById(int id); + + /** + * 更新文件本地路径 + * + * @param id 编号 + * @param localUrl 本地路径 + */ + @Update("update file set local_url=#{localUrl} where id=#{id}") + void updateLocalUrlById(@Param("id") int id, @Param("localUrl") String localUrl); + + /** + * 更新文件访问路径 + * + * @param id 编号 + * @param visitUrl 访问链接 + */ + @Update("update file set visit_url=#{visitUrl} where id=#{id}") + void updateVisitUrlById(@Param("id") int id, @Param("visitUrl") String visitUrl); + + /** + * 更新文件描述 + * + * @param id 文件编号 + * @param description 描述 + */ + @Update("update file set description=#{description} where id=#{id}") + void updateDescriptionById(@Param("id") int id, @Param("description") String description); + + /** + * 更新文件查看次数 + * + * @param id 编号 + */ + @Update("update file set check_times=check_times+1 where id=#{id}") + void updateCheckTimesById(int id); + + /** + * 更新文件下载次数 + * + * @param id 编号 + */ + @Update("update file set download_times=download_times+1 where id=#{id}") + void updateDownloadTimesById(long id); + + /** + * 更新文件标签 + * + * @param id 编号 + * @param tag 标签 + */ + @Update("update file set tag=#{tag} where id=#{id}") + void updateTagById(@Param("id") int id, @Param("tag") String tag); + + /** + * 更新文件分类 + * + * @param id 编号 + * @param categoryId 分类编号 + */ + @Update("update file set category_id=#{categoryId} where id=#{id}") + void updateCategoryById(@Param("id") int id, @Param("categoryId") int categoryId); + + /** + * 获取文件信息 + * + * @param visitUrl 访问链接 + * + * @return {@link File} + */ + @Select("select * from file where visit_url=#{visitUrl}") + File getFileByVisitUrl(String visitUrl); + + /** + * 获取所有文件 + * + * @param userId 用户编号 + * @param offset 偏移 + * @param categoryId 分类编号 + * @param orderBy 排序方式 + * @param search 搜索 + * + * @return {@link List} + */ + @SelectProvider(type = FileSqlProvider.class, method = "getAll") + List<FileRecord> listAll(@Param("userId") int userId, @Param("offset") int offset, @Param("categoryId") int + categoryId, @Param("orderBy") String orderBy, @Param("search") String search); + + /** + * 获取用户的上传资源 + * + * @param userId 用户编号 + * @param offset 偏移 + * @param search 搜索 + * + * @return {@link List} + */ + @SelectProvider(type = FileSqlProvider.class, method = "getUserUploaded") + List<FileRecord> listUserUploaded(@Param("userId") int userId, @Param("offset") int offset, @Param("search") + String search); + + /** + * 获取用户的下载资源 + * + * @param userId 用户编号 + * @param offset 偏移 + * @param search 搜索 + * + * @return {@link List} + */ + @SelectProvider(type = FileSqlProvider.class, method = "getUserDownloaded") + List<FileRecord> listUserDownloaded(@Param("userId") int userId, @Param("offset") int offset, @Param("search") + String search); + + /** + * 查询文件基本信息 + * + * @param userId 用户编号，不使用用户编号作为条件时设置值小于等于0即可 + * @param fileId 文件编号，不使用文件编号作为条件时设置值小于等于0即可 + * @param categoryId 分类编号，不用分类编号作为条件时设置值小于等于0即可 + * @param fileName 文件名，不使用文件名作为条件时设置值为空即可 + * @param offset 偏移 + * + * @return 上传记录 + */ + @SelectProvider(type = FileSqlProvider.class, method = "getBasicBy") + List<FileBasicRecord> listBasicBy(@Param("userId") int userId, @Param("fileId") long fileId, @Param("fileName") + String fileName, @Param("categoryId") int categoryId, @Param("offset") int offset); +} diff --git a/src/main/java/com/mesasoft/cn/dao/UploadedDAO.java b/src/main/java/com/mesasoft/cn/dao/UploadedDAO.java new file mode 100644 index 0000000..8fa32d0 --- /dev/null +++ b/src/main/java/com/mesasoft/cn/dao/UploadedDAO.java @@ -0,0 +1,32 @@ +package com.mesasoft.cn.dao; + +import com.mesasoft.cn.dao.sqlprovider.UploadedSqlProvider; +import com.mesasoft.cn.model.UploadedRecord; +import org.apache.ibatis.annotations.Param; +import org.apache.ibatis.annotations.SelectProvider; +import org.springframework.stereotype.Repository; + +import java.util.List; + +/** + * @author pantao + * @since 2018/2/28 + */ +@Repository +public interface UploadedDAO { + + /** + * 查询上传记录 + * + * @param userId 用户编号，不使用用户编号作为条件时设置值小于等于0即可 + * @param fileId 文件编号，不使用文件编号作为条件时设置值小于等于0即可 + * @param categoryId 分类编号，不用分类编号作为条件时设置值小于等于0即可 + * @param fileName 文件名，不使用文件名作为条件时设置值为空即可 + * @param offset 偏移 + * + * @return 上传记录 + */ + @SelectProvider(type = UploadedSqlProvider.class, method = "getDownloadBy") + List<UploadedRecord> listUploadedBy(@Param("userId") int userId, @Param("fileId") long fileId, @Param("fileName") + String fileName, @Param("categoryId") int categoryId, @Param("offset") int offset); +} diff --git a/src/main/java/com/mesasoft/cn/dao/UserDAO.java b/src/main/java/com/mesasoft/cn/dao/UserDAO.java new file mode 100644 index 0000000..d2993c3 --- /dev/null +++ b/src/main/java/com/mesasoft/cn/dao/UserDAO.java @@ -0,0 +1,167 @@ +package com.mesasoft.cn.dao; + +import com.mesasoft.cn.dao.sqlprovider.UserSqlProvider; +import com.mesasoft.cn.entity.User; +import org.apache.ibatis.annotations.*; +import org.springframework.stereotype.Repository; + +import java.util.List; + +/** + * @author pantao + * @since 2018/1/12 + */ +@Repository +public interface UserDAO { + + /** + * 更新用户权限 + * + * @param id 用户编号 + * @param permission 权限 + * + * @return 是否更新成功 + */ + @Update("update user set permission=#{permission} where id=#{id}") + boolean updatePermission(@Param("id") int id, @Param("permission") int permission); + + /** + * 用过用户名获取用户Id + * + * @param usernameOrEmail 用户名或邮箱 + * + * @return 用户编号 + */ + @Select("select id from user where username=#{usernameOrEmail} or email=#{usernameOrEmail}") + int getUserId(String usernameOrEmail); + + /** + * 通过ID更新用户基本信息 + * + * @param id 编号 + * @param avatar 头像 + * @param realName 真实姓名 + * @param email 邮箱 + * + * @return 是否更新成功 + */ + @Update("update user set avatar=#{avatar},real_name=#{realName},email=#{email} where id=#{id}") + boolean updateBasicInfo(@Param("id") int id, @Param("avatar") String avatar, @Param("realName") String realName, + @Param("email") String email); + + /** + * 通过id获取一个用户 + * + * @param id 编号 + * + * @return {@link User} + */ + @Select("select * from user where id=#{id}") + User getUserById(int id); + + /** + * 通过权限获取用户 + * + * @param permission 权限 + * @param condition 条件 + * @param offset 偏移 + * + * @return {@link List} + */ + @SelectProvider(type = UserSqlProvider.class, method = "getUserBy") + List<User> listUserBy(@Param("permission") int permission, @Param("condition") String condition, + @Param("offset") int offset); + + /** + * 用户登录 + * + * @param usernameOrEmail 用户名 + * @param password 密码 + * + * @return {@link User} + */ + @Select("select * from user where (username=#{usernameOrEmail} or email=#{usernameOrEmail}) and password=sha2" + + "(#{password},256)") + User login(@Param("usernameOrEmail") String usernameOrEmail, @Param("password") String password); + + /** + * 添加一个用户 + * + * @param user {@link User} + * + * @return 是否插入成功 + */ + @Insert("insert into user(username,real_name,email,password,is_downloadable,is_uploadable,is_deletable," + + "is_updatable,is_visible) values(#{username},#{realName},#{email},sha2(#{password},256)," + + "#{isDownloadable},#{isUploadable},#{isDeletable},#{isUpdatable},#{isVisible})") + boolean insertUser(User user); + + /** + * 通过id更新用户登录时间 + * + * @param id 编号 + * + * @return {@link Boolean} + */ + @Update("update user set last_login_time=current_timestamp where id=#{id}") + boolean updateUserLoginTime(int id); + + /** + * 更新操作用户权限 + * + * @param id 用户编号 + * @param isDownloadable 下载权限 + * @param isUploadable 上传权限 + * @param isVisible 可查权限 + * @param isDeletable 删除权限 + * @param isUpdatable 更新权限 + * + * @return {@link Boolean} + */ + @UpdateProvider(type = UserSqlProvider.class, method = "updateAuthById") + boolean updateAuthById(@Param("id") int id, @Param("isDownloadable") int isDownloadable, + @Param("isUploadable") int isUploadable, @Param("isDeletable") int isDeletable, @Param( + "isUpdatable") int isUpdatable, @Param("isVisible") int isVisible); + + /** + * 通过编号哦更新密码 + * + * @param id 编号 + * @param password 密码 + * + * @return {@link Boolean} + */ + @Update("update user set password=sha2(#{password},256) where id=#{id}") + boolean updatePasswordById(@Param("id") int id, @Param("password") String password); + + /** + * 通过邮箱更新密码 + * + * @param password 密码 + * @param email 邮箱 + * + * @return {@link Boolean} + */ + @Update("update user set password=sha2(#{password},256) where email=#{email}") + boolean updatePasswordByEmail(@Param("password") String password, @Param("email") String email); + + /** + * 检查用户名 + * + * @param username 用户名 + * + * @return {@link Integer} + */ + @Select("select count(*) from user where username=#{username}") + int checkUsername(String username); + + /** + * 检查邮箱 + * + * @param email 邮箱 + * + * @return {@link Integer} + */ + @Select("select count(*) from user where email=#{email}") + int checkEmail(String email); +} diff --git a/src/main/java/com/mesasoft/cn/dao/sqlprovider/AuthSqlProvider.java b/src/main/java/com/mesasoft/cn/dao/sqlprovider/AuthSqlProvider.java new file mode 100644 index 0000000..3c9c10f --- /dev/null +++ b/src/main/java/com/mesasoft/cn/dao/sqlprovider/AuthSqlProvider.java @@ -0,0 +1,48 @@ +package com.mesasoft.cn.dao.sqlprovider; + +import com.mesasoft.cn.SketchApplication; +import com.mesasoft.cn.modules.constant.ConfigConsts; +import com.zhazhapan.util.Checker; +import org.apache.ibatis.annotations.Param; +import org.apache.ibatis.jdbc.SQL; + +/** + * @author pantao + * @since 2018/1/19 + */ +public class AuthSqlProvider { + + public String updateAuthById() { + return CommonSqlProvider.updateAuthById("auth"); + } + + public String batchDelete(@Param("ids") String ids) { + return "delete from auth where id in " + (ids.startsWith("(") ? "" : "(") + ids + (ids.endsWith(")") ? "" : + ")"); + } + + public String getAuthBy(@Param("id") long id, @Param("userId") int userId, @Param("fileId") long fileId, @Param + ("fileName") String fileName, @Param("offset") int offset) { + String sql = new SQL() {{ + SELECT("a.id,a.user_id,a.file_id,u.username,f.name file_name,f.local_url,a.is_downloadable,a" + "" + "" + + ".is_uploadable,a.is_deletable,a.is_updatable,a.is_visible,a.create_time"); + FROM("auth a"); + JOIN("user u on u.id=a.user_id"); + JOIN("file f on f.id=a.file_id"); + if (id > 0) { + WHERE("a.id=#{id}"); + } + if (userId > 0) { + WHERE("u.id=#{userId}"); + } + if (fileId > 0) { + WHERE("f.id=#{fileId}"); + } else if (Checker.isNotEmpty(fileName)) { + WHERE("f.local_url like '%" + fileName + "%'"); + } + ORDER_BY("a." + SketchApplication.settings.getStringUseEval(ConfigConsts.AUTH_ORDER_BY_OF_SETTINGS)); + }}.toString(); + int size = SketchApplication.settings.getIntegerUseEval(ConfigConsts.AUTH_PAGE_SIZE_OF_SETTINGS); + return sql + " limit " + (offset * size) + "," + size; + } +} diff --git a/src/main/java/com/mesasoft/cn/dao/sqlprovider/CommonSqlProvider.java b/src/main/java/com/mesasoft/cn/dao/sqlprovider/CommonSqlProvider.java new file mode 100644 index 0000000..ad2d024 --- /dev/null +++ b/src/main/java/com/mesasoft/cn/dao/sqlprovider/CommonSqlProvider.java @@ -0,0 +1,24 @@ +package com.mesasoft.cn.dao.sqlprovider; + +import org.apache.ibatis.jdbc.SQL; + +/** + * @author pantao + * @since 2018/1/19 + */ +public class CommonSqlProvider { + + private CommonSqlProvider() {} + + public static String updateAuthById(String table) { + return new SQL() {{ + UPDATE(table); + SET("is_downloadable=#{isDownloadable}"); + SET("is_uploadable=#{isUploadable}"); + SET("is_deletable=#{isDeletable}"); + SET("is_updatable=#{isUpdatable}"); + SET("is_visible=#{isVisible}"); + WHERE("id=#{id}"); + }}.toString(); + } +} diff --git a/src/main/java/com/mesasoft/cn/dao/sqlprovider/DownloadedSqlProvider.java b/src/main/java/com/mesasoft/cn/dao/sqlprovider/DownloadedSqlProvider.java new file mode 100644 index 0000000..5c1745f --- /dev/null +++ b/src/main/java/com/mesasoft/cn/dao/sqlprovider/DownloadedSqlProvider.java @@ -0,0 +1,48 @@ +package com.mesasoft.cn.dao.sqlprovider; + +import com.mesasoft.cn.SketchApplication; +import com.mesasoft.cn.modules.constant.ConfigConsts; +import com.zhazhapan.util.Checker; +import org.apache.ibatis.annotations.Param; +import org.apache.ibatis.jdbc.SQL; + +/** + * @author pantao + * @since 2018/1/19 + */ +public class DownloadedSqlProvider { + + /** + * 生成一条下载记录表的查询语句 + * + * @param userId 用户编号 + * @param fileId 文件编号 + * + * @return SQL语句 + */ + public String getDownloadBy(@Param("userId") int userId, @Param("fileId") long fileId, @Param("fileName") String + fileName, @Param("categoryId") int categoryId, @Param("offset") int offset) { + String sql = new SQL() {{ + SELECT("d.id,d.user_id,d.file_id,u.username,u.email,f.name file_name,c.name category_name,f.visit_url,d" + + ".create_time"); + FROM("download d"); + JOIN("user u on d.user_id=u.id"); + JOIN("file f on d.file_id=f.id"); + JOIN("category c on f.category_id=c.id"); + if (userId > 0) { + WHERE("d.user_id=#{userId}"); + } + if (fileId > 0) { + WHERE("d.file_id=#{fileId}"); + } else if (Checker.isNotEmpty(fileName)) { + WHERE("f.local_url like '%" + fileName + "%'"); + } + if (categoryId > 0) { + WHERE("c.id=#{categoryId}"); + } + ORDER_BY("d." + SketchApplication.settings.getStringUseEval(ConfigConsts.DOWNLOAD_ORDER_BY_OF_SETTINGS)); + }}.toString(); + int size = SketchApplication.settings.getIntegerUseEval(ConfigConsts.DOWNLOAD_PAGE_SIZE_OF_SETTINGS); + return sql + " limit " + (offset * size) + "," + size; + } +} diff --git a/src/main/java/com/mesasoft/cn/dao/sqlprovider/FileSqlProvider.java b/src/main/java/com/mesasoft/cn/dao/sqlprovider/FileSqlProvider.java new file mode 100644 index 0000000..8a2ecdc --- /dev/null +++ b/src/main/java/com/mesasoft/cn/dao/sqlprovider/FileSqlProvider.java @@ -0,0 +1,110 @@ +package com.mesasoft.cn.dao.sqlprovider; + +import com.mesasoft.cn.SketchApplication; +import com.mesasoft.cn.modules.constant.ConfigConsts; +import com.zhazhapan.modules.constant.ValueConsts; +import com.zhazhapan.util.Checker; +import org.apache.ibatis.annotations.Param; +import org.apache.ibatis.jdbc.SQL; +import org.springframework.stereotype.Component; + +/** + * @author pantao + * @since 2018/1/19 + */ +@Component +public class FileSqlProvider { + + public String updateAuthById() { + return CommonSqlProvider.updateAuthById("file"); + } + + /** + * 生成一条文件基本信息的查询语句 + * + * @param userId 用户编号 + * @param fileId 文件编号 + * + * @return SQL语句 + */ + public String getBasicBy(@Param("userId") int userId, @Param("fileId") long fileId, @Param("fileName") String + fileName, @Param("categoryId") int categoryId, @Param("offset") int offset) { + String sql = new SQL() {{ + SELECT("f.id,u.username,f.local_url,c.name category_name,f.visit_url,f.download_times," + "f" + "" + "" + + ".create_time"); + FROM("file f"); + JOIN("user u on f.user_id=u.id"); + JOIN("category c on f.category_id=c.id"); + if (userId > 0) { + WHERE("f.user_id=#{userId}"); + } + if (fileId > 0) { + WHERE("f.id=#{fileId}"); + } else if (Checker.isNotEmpty(fileName)) { + WHERE("f.local_url like '%" + fileName + "%'"); + } + if (categoryId > 0) { + WHERE("c.id=#{categoryId}"); + } + ORDER_BY("f." + SketchApplication.settings.getStringUseEval(ConfigConsts.FILE_ORDER_BY_OF_SETTING)); + }}.toString(); + int size = SketchApplication.settings.getIntegerUseEval(ConfigConsts.FILE_PAGE_SIZE_OF_SETTING); + return sql + " limit " + (offset * size) + "," + size; + } + + private String getSqlEnds(int offset, String orderBy, String search) { + int size = SketchApplication.settings.getIntegerUseEval(ConfigConsts.FILE_PAGE_SIZE_OF_SETTING); + return getSearch(search) + " order by " + (Checker.isEmpty(orderBy) ? SketchApplication.settings + .getStringUseEval(ConfigConsts.FILE_ORDER_BY_OF_SETTING) : orderBy) + " limit " + offset * size + + "," + size; + } + + public String getAll(@Param("offset") int offset, @Param("categoryId") int categoryId, @Param("orderBy") String + orderBy, @Param("search") String search) { + return getBaseSql(ValueConsts.FALSE) + " where f.is_visible=1" + (categoryId < 1 ? "" : " and " + + "category_id=#{categoryId}") + " and ((select a.is_visible from auth a where a.file_id=f.id and a" + + ".user_id=#{userId}) is null or (a.user_id=#{userId} and a.is_visible=1))" + getSqlEnds(offset, + orderBy, search); + } + + public String getUserUploaded(@Param("offset") int offset, @Param("search") String search) { + return getBaseSql(ValueConsts.FALSE) + " where f.is_visible=1 and (f.user_id=#{userId} or a.is_updatable=1 or" + + " a.is_deletable=1)" + getSqlEnds(offset, + ValueConsts.EMPTY_STRING, search); + } + + public String getUserDownloaded(@Param("offset") int offset, @Param("search") String search) { + return getBaseSql(ValueConsts.TRUE) + " where d.user_id=#{userId}" + getSqlEnds(offset, ValueConsts + .EMPTY_STRING, search); + } + + private String getSearch(String search) { + if (Checker.isEmpty(search)) { + return ValueConsts.EMPTY_STRING; + } else { + search = "'%" + search + "%'"; + return " and (f.name like " + search + " or f.visit_url like " + search + " or f.description like " + + search + " or f.tag like " + search + ")"; + } + } + + private String getBaseSql(boolean isDownloaded) { + return new SQL() {{ + SELECT("distinct f.id,f.user_id,u.username,u.avatar,f.name file_name,f.size,f.create_time,c.name " + + "category_name,f" + + ".description,f.tag,f.check_times,f.download_times,f.visit_url,f.is_uploadable,f.is_deletable," + + "f.is_updatable,f.is_downloadable,f.is_visible"); + if (isDownloaded) { + SELECT("d.create_time download_time"); + } + FROM("file f"); + JOIN("user u on u.id=f.user_id"); + JOIN("category c on c.id=f.category_id"); + if (isDownloaded) { + JOIN("download d on d.file_id=f.id"); + } else { + JOIN("auth a on a.file_id=f.id"); + } + }}.toString(); + } +} diff --git a/src/main/java/com/mesasoft/cn/dao/sqlprovider/UploadedSqlProvider.java b/src/main/java/com/mesasoft/cn/dao/sqlprovider/UploadedSqlProvider.java new file mode 100644 index 0000000..720b716 --- /dev/null +++ b/src/main/java/com/mesasoft/cn/dao/sqlprovider/UploadedSqlProvider.java @@ -0,0 +1,47 @@ +package com.mesasoft.cn.dao.sqlprovider; + +import com.mesasoft.cn.SketchApplication; +import com.mesasoft.cn.modules.constant.ConfigConsts; +import com.zhazhapan.util.Checker; +import org.apache.ibatis.annotations.Param; +import org.apache.ibatis.jdbc.SQL; + +/** + * @author pantao + * @since 2018/2/28 + */ +public class UploadedSqlProvider { + + /** + * 生成一条上传记录表的查询语句 + * + * @param userId 用户编号 + * @param fileId 文件编号 + * + * @return SQL语句 + */ + public String getDownloadBy(@Param("userId") int userId, @Param("fileId") long fileId, @Param("fileName") String + fileName, @Param("categoryId") int categoryId, @Param("offset") int offset) { + String sql = new SQL() {{ + SELECT("f.id,f.user_id,u.username,u.email,f.name file_name,c.name category_name,f.local_url,f.visit_url," + + "" + "" + "" + "f" + ".create_time"); + FROM("file f"); + JOIN("user u on f.user_id=u.id"); + JOIN("category c on f.category_id=c.id"); + if (userId > 0) { + WHERE("f.user_id=#{userId}"); + } + if (fileId > 0) { + WHERE("f.id=#{fileId}"); + } else if (Checker.isNotEmpty(fileName)) { + WHERE("f.local_url like '%" + fileName + "%'"); + } + if (categoryId > 0) { + WHERE("c.id=#{categoryId}"); + } + ORDER_BY("f." + SketchApplication.settings.getStringUseEval(ConfigConsts.FILE_ORDER_BY_OF_SETTING)); + }}.toString(); + int size = SketchApplication.settings.getIntegerUseEval(ConfigConsts.FILE_PAGE_SIZE_OF_SETTING); + return sql + " limit " + (offset * size) + "," + size; + } +} diff --git a/src/main/java/com/mesasoft/cn/dao/sqlprovider/UserSqlProvider.java b/src/main/java/com/mesasoft/cn/dao/sqlprovider/UserSqlProvider.java new file mode 100644 index 0000000..5d36845 --- /dev/null +++ b/src/main/java/com/mesasoft/cn/dao/sqlprovider/UserSqlProvider.java @@ -0,0 +1,41 @@ +package com.mesasoft.cn.dao.sqlprovider; + +import com.mesasoft.cn.SketchApplication; +import com.mesasoft.cn.modules.constant.ConfigConsts; +import com.mesasoft.cn.modules.constant.DefaultValues; +import com.zhazhapan.util.Checker; +import org.apache.ibatis.annotations.Param; +import org.apache.ibatis.jdbc.SQL; + +/** + * @author pantao + * @since 2018/1/19 + */ +public class UserSqlProvider { + + public String updateAuthById() { + return CommonSqlProvider.updateAuthById("user"); + } + + public String getUserBy(@Param("permission") int permission, @Param("condition") String condition, @Param + ("offset") int offset) { + String sql = new SQL() {{ + SELECT("*"); + FROM("user"); + if (permission == DefaultValues.THREE_INT) { + WHERE("permission<3"); + } else if (permission == DefaultValues.TWO_INT) { + WHERE("permission<2"); + } else { + WHERE("permission<0"); + } + if (Checker.isNotEmpty(condition)) { + WHERE("username like '%" + condition + "%' or email like '%" + condition + "%' or real_name like '" + + condition + "'"); + } + ORDER_BY(SketchApplication.settings.getStringUseEval(ConfigConsts.USER_ORDER_BY_OF_SETTINGS)); + }}.toString(); + int size = SketchApplication.settings.getIntegerUseEval(ConfigConsts.USER_PAGE_SIZE_OF_SETTINGS); + return sql + " limit " + (offset * size) + "," + size; + } +} |