举例clob型不能用 distinct

public List<WorkingPaper> findAssignedWorkPapers(String projectId,
String auditedObjectId, String userId) {
// String jpql = "SELECT distinct w FROM WorkingPaper w LEFT OUTER JOIN w.paperUsers AS u WHERE w.project.id = ? AND w.deleted = ? AND u.userType= ? ";
//clob型不能用 distinct
String jpql=" SELECT w FROM WorkingPaper w where w.id in (select distinct t.id FROM WorkingPaper t LEFT OUTER JOIN t.paperUsers AS u WHERE t.project.id = ? AND t.deleted = ? AND u.userType= ? ";
List<Object> params = new ArrayList<Object>();
params.add(projectId);
params.add(Boolean.FALSE);
params.add(UserType.PAPER_PRINCIPAL);
StringBuilder sb = new StringBuilder(jpql);
if (StringUtils.isNotBlank(auditedObjectId)) {
sb.append("AND t.auditedUnitId = ? ");
params.add(auditedObjectId);
}
if (StringUtils.isNotBlank(userId)) {
sb.append("AND u.userId = ? ");
params.add(userId);
}
sb.append(")");
sb.append(" order by w.code");
return this.find(sb.toString(), params.toArray());
}

解决存储字符不够，转换为clob格式SQL

举例:

--审计公示，解决审计公示内容存储字符不够
--select * from IAM_AUDIT_PUBLICITY
--第一步，将publicity_content重命名为publicity_content_bak
alter table IAM_AUDIT_PUBLICITY rename column publicity_content to publicity_content_bak;
--第二步，在表中新建clob属性列publicity_content
alter table IAM_AUDIT_PUBLICITY add publicity_content clob;
--第三部，将旧表中的数据拷贝到新表
update IAM_AUDIT_PUBLICITY set publicity_content=publicity_content_bak;
--第四部，删除备份列
alter table IAM_AUDIT_PUBLICITY drop column publicity_content_bak;