MybatisSqlUtils.java 3.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596
  1. package cn.com.ty.lift.common.utils;
  2. import java.util.List;
  3. import java.util.Map;
  4. /**
  5. * @author huangyuan
  6. * @date 2020/4/18
  7. * @description mybatis @select 自定义sql 处理工具
  8. */
  9. public class MybatisSqlUtils {
  10. /**
  11. * @param params 查询参数
  12. * @return 拼接的sql语句
  13. * @description 通过给定参数拼接sql - 获取当前公司指定角色的用户信息
  14. * @date 2020/4/18 6:27 下午
  15. */
  16. @SuppressWarnings("unchecked")
  17. public String getPushUserInfoByRoleCodesSql(Map<String, Object> params) {
  18. List<String> roleCodeList = (List<String>) params.get("roleCodeList");
  19. Long companyId = (Long) params.get("companyId");
  20. //获取通用语句
  21. StringBuilder sql = getPushUserInfoSql();
  22. if (roleCodeList != null && roleCodeList.size() > 0) {
  23. sql.append("and r.code in (");
  24. //拼接list语句
  25. spliceList(roleCodeList, sql);
  26. }
  27. sql.append("and ur.company_id=").append(companyId);
  28. System.out.println(sql.toString());
  29. return sql.toString();
  30. }
  31. /**
  32. * @param params 查询参数
  33. * @return 拼接的sql语句
  34. * @description 通过给定参数拼接sql - 获取当前公司指定角色的用户信息
  35. * @date 2020/4/18 6:27 下午
  36. */
  37. @SuppressWarnings("unchecked")
  38. public String getPushUserInfoByIdsSql(Map<String, Object> params) {
  39. List<Long> userIdList = (List<Long>) params.get("userIdList");
  40. Long companyId = (Long) params.get("companyId");
  41. //获取通用语句
  42. StringBuilder sql = getPushUserInfoSql();
  43. if (userIdList != null && userIdList.size() > 0) {
  44. sql.append("and ua.user_id in (");
  45. //拼接list语句
  46. spliceList(userIdList, sql);
  47. }
  48. sql.append("and ur.company_id=").append(companyId);
  49. System.out.println(sql);
  50. return sql.toString();
  51. }
  52. /**
  53. * @param list 要拼接的list
  54. * @description 拼接语句
  55. * @date 2020/4/18 6:37 下午
  56. */
  57. private static void spliceList(List<?> list, StringBuilder sql) {
  58. for (int i = 0; i < list.size(); i++) {
  59. sql.append("'").append(list.get(i));
  60. if (i == list.size() - 1) {
  61. sql.append("')");
  62. } else {
  63. sql.append("',");
  64. }
  65. }
  66. }
  67. /**
  68. * @return 通用语句
  69. * @description 获取查询通用语句
  70. * @date 2020/4/18 6:51 下午
  71. */
  72. private static StringBuilder getPushUserInfoSql() {
  73. StringBuilder sql = new StringBuilder();
  74. sql.append("select ui.user_id as userId, ");
  75. sql.append("ui.name as userName, ");
  76. sql.append("ua.mobile as mobile, ");
  77. sql.append("ua.device_model as deviceModel, ");
  78. sql.append("ua.device_flag as deviceFlag, ");
  79. sql.append("r.name as roleName from ");
  80. sql.append("user_account ua ");
  81. sql.append("left join user_info ui on ua.user_id = ui.user_id ");
  82. sql.append("left join user_role ur on ua.user_id = ur.user_id ");
  83. sql.append("left join role r on r.id = ur.role_id ");
  84. sql.append("where 1 = 1 ");
  85. return sql;
  86. }
  87. }