How to create custom order by in hibernate Criteria API

สืบเนื่องจากการใช้งาน Hibernate กับฐานข้อมูล oracle แล้วเจอปัญหาเวลาทำการเรียงลำดับข้อมูล (Order by) แล้วภาษาไทยจะเรียงไม่ถูกใจผู้ใช้งาน เช่น
การเรียงข้อมูลจังหวัด

กรุงเทพ
เชียงราย
เชียงใหม่
พะเยา
อ่างทอง

ข้อมูลจะถูกเรียงลำดับดังนี้

กรุงเทพ
พะเยา
อ่างทอง
เชียงราย
เชียงใหม่

ถ้าต้องการเรียงให้ถูกต้องตามหลักภาษาไทย (ภาษาไทยเทพเกิน) ต้องเพิ่ม function NLSSORT เข้าไปด้วยดังนี้

SELECT PROVINCE_NAME 
FROM CONF_PROVINCE
ORDER BY NLSSORT(PROVINCE_NAME, 'NLS_SORT = THAI_DICTIONARY');

ทุกอย่างเหมือนไม่มีอะไรซับซ้อนแต่ปัญหาก็คือ ระบบใช้ Hibernate และก็ Qurey ผ่าน Hibernate Criteria หลังจากสอบถาม google ก็ไปเจอกับแนวทางดังนี้

How to order by a custom SQL formula/expression when using hibernate Criteria API
เลยได้แนวทางมาแก้ไขปัญหาโดยสร้าง class OrderOracleThaiSort แล้วทำการ extends Order ดังนี้

/*
 * ----------------------------------------------------------------------------
 * Copyright © 2014 by http://www.secondknow.com All rights reserved.
 * ----------------------------------------------------------------------------
 */
package com.secondknow.hibernate;

import java.sql.Types;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.criterion.CriteriaQuery;
import org.hibernate.criterion.Order;
import org.hibernate.engine.SessionFactoryImplementor;
import org.hibernate.type.Type;

/**
 * @author Supot Saelao
 * @version 1.0
 */
public class OrderOracleThaiSort extends Order {
	private static final long serialVersionUID = 1L;
	private boolean ignoreCase = false;
	private boolean ascending;
	private String propertyName;
	
	public OrderOracleThaiSort(String propertyName) {
		super(propertyName, true);
		this.ascending = true;
		this.propertyName = propertyName;
	}
	
	public OrderOracleThaiSort(String propertyName, boolean ascending) {
		super(propertyName, ascending);
		this.ascending = ascending;
		this.propertyName = propertyName;
	}
	
	@Override
	public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery)
			throws HibernateException {
		
		String[] columns = criteriaQuery.getColumnsUsingProjection(criteria, propertyName);
		Type type = criteriaQuery.getTypeUsingProjection(criteria, propertyName);
		StringBuffer fragment = new StringBuffer();
		for (int i = 0; i < columns.length; i++) {
			fragment.append("NLSSORT(");
			SessionFactoryImplementor factory = criteriaQuery.getFactory();
			boolean lower = ignoreCase && type.sqlTypes(factory)[i] == Types.VARCHAR;
			if (lower) {
				fragment.append(factory.getDialect().getLowercaseFunction()).append('(');
			}
			fragment.append(columns[i]);
			if (lower) {
				fragment.append(')');
			}
			
			fragment.append(", 'NLS_SORT = THAI_DICTIONARY')");
			
			fragment.append(ascending ? " asc" : " desc");
			if (i < columns.length - 1) {
				fragment.append(", ");
			}
		}
		return fragment.toString();
	}

	@Override
	public String toString() {
		return super.toString();
	}

	public static Order asc(String propertyName) {
		return new OrderOracleThaiSort(propertyName);
	}

	public static Order desc(String propertyName) {
		return new OrderOracleThaiSort(propertyName, false);
	}
}

จุดสำคัญอยู่ที่ การ overrid method toSqlString จริงๆ ไม่น่าเรียกว่า overrid เพราะผมเล่นไป copy code เดิมมาแล้วเพิ่มไปสองบรรทัดคือ

fragment.append("NLSSORT(");

และ

fragment.append(", 'NLS_SORT = THAI_DICTIONARY')");

นอกนั้นลอกมาเต็มๆ เลย จากนั้นขั้นตอนการนำไปใช้ก็ตามนี้เลย

@Override
@Transactional(readOnly = true)
public List<Province> getProviceList(SearchParam search) {
	
	Criteria criteria = getSession().createCriteria(Province.class);
	if (ValidatorUtils.isNotNullAndEmpty(search.countryCode)) {
		criteria.add(Restrictions.eq("countryCode", search.countryCode));
	}
	if (ValidatorUtils.isNotNullAndEmpty(search.region)) {
		criteria.add(Restrictions.eq("region", search.region));
	}
	
	criteria.addOrder(Order.asc("countryCode"));
	criteria.addOrder(OrderOracleThaiSort.asc("provinceName"));
	
	return criteria.list();
}

แหล่งข้อมูล
How to order by a custom SQL formula/expression when using hibernate Criteria API

  1. No comments yet.

  1. No trackbacks yet.