数据库中文排序注意事项

SQL Server

默认写法如果首行有空格或者生僻字会排在最前面，需要做下面的处理

select fd_id,doc_subject
from kms_knowledge_base_doc 
ORDER BY ltrim(doc_subject) COLLATE Chinese_Simplified_Pinyin_100_CI_AS

HQL写法

// 排序字段首行有空格的情况
if(orderBy.contains("kmsKnowledgeBaseDoc.docSubject")){
    orderBy = orderBy.replace("kmsKnowledgeBaseDoc.docSubject","trim(kmsKnowledgeBaseDoc.docSubject)");
}

另外，ifnull在hql中的使用

// 后台置顶列表排序
if ("top".equals(dataType)) {
	if (driverClass.equals("oracle.jdbc.driver.OracleDriver"))
		orderByIndex = "nvl(kmsKnowledgeBaseDoc.docIsIndexTop,0) desc";
	else
		orderByIndex = "coalesce(kmsKnowledgeBaseDoc.docIsIndexTop,0) desc";
} else {
	// oracle数据库字段数据为null的问题
	if (driverClass.equals("oracle.jdbc.driver.OracleDriver"))
		orderByIndex = "nvl(kmsKnowledgeBaseDoc.docIsIndexTop,0) desc,kmsKnowledgeBaseDoc.fdSetTopTime desc";
	else
		orderByIndex = "coalesce(kmsKnowledgeBaseDoc.docIsIndexTop,0) desc,kmsKnowledgeBaseDoc.fdSetTopTime desc";
}