Posts Tagged ‘ Oracle

Custom Hibernate Sequence and JPA ID generator

หัวข้อนี้ก็ยังหนีไม่พ้น Hibernate อีกตามเคยจากหัวข้อที่แล้ว เรื่องการสร้าง custom order by หัวข้อนี้กลับมาดูเรื่องการ Custom การสร้าง ID และ Sequence แบบฉบับตามใจคนใช้งาน
เริ่มแรกก็ขอคววามช่วยเหลือจาก google ตามเคย หลังจากสอบถามก็ได้ข้อมูลมาสองที่ดังนี้
Customize IdGenerator in JPA, gap between Hibernate and JPA annotations และ
Custom Hibernate Sequence Generator for id field
เมื่อได้แหล่งข้อมูลตัวอย่างแล้วก็นำมาปรับแก้ดังนี้

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

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import org.hibernate.HibernateException;
import org.hibernate.MappingException;
import org.hibernate.dialect.Dialect;
import org.hibernate.engine.SessionImplementor;
import org.hibernate.exception.JDBCExceptionHelper;
import org.hibernate.id.SequenceGenerator;
import org.hibernate.type.Type;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @author Supot Saelao
 * @version 1.0
 */
public class OracleSeqWithDateFormatGenerator extends SequenceGenerator {
	private static final Logger logger = LoggerFactory.getLogger(OracleSeqWithDateFormatGenerator.class);
	
	private String sql;
	
	@Override
	public void configure(Type type, Properties params, Dialect dialect)
			throws MappingException {
		super.configure(type, params, dialect);
		
		String sequenceName = " TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') || " + getSequenceName();
		sql = dialect.getSequenceNextValString(sequenceName);
	}

	@Override
	public Serializable generate(SessionImplementor session, Object obj)
			throws HibernateException {
		return generateSeqStr(session);
	}
	
	private String generateSeqStr(SessionImplementor session) {
		try {
			logger.info("Seq statement : {}", sql);
			
			PreparedStatement st = session.getBatcher().prepareSelectStatement(sql);
			try {
				ResultSet rs = st.executeQuery();
				try {
					rs.next();
					String seqStr = rs.getString(1);
					logger.info("Seq value : {}", seqStr);
					
					return seqStr;
				} finally {
					rs.close();
				}
			} finally {
				session.getBatcher().closeStatement(st);
			}
		} catch (SQLException sqle) {
			throw JDBCExceptionHelper.convert(session.getFactory()
					.getSQLExceptionConverter(), sqle, "could not get next sequence value", sql);
		}
	}	
}

Read more

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 ก็ไปเจอกับแนวทางดังนี้
Read more

Passing of BOOLEAN parameters to PL/SQL stored procedures

ปัญหาเกิดจาก function ใน oracle ที่เขียนขึ้นมานั้น มี parameter type เป็น boolean แต่ JDBC ไม่ support การส่งค่าแบบ boolean ไปใน function ถ้าทดลองส่งไปจะเกิด exception ประมาณนี้

java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'FN_TEST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
	at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:218)

วิธีการแก้ปัญหาก็บ้านๆ มากครับ สร้าง function ที่รับ parameter ที่ไม่ใช่ boolean มาแล้ว ไปเรียก function ที่ต้องส่ง boolean เข้าไปอีกที่ ประมาณนี้

CREATE OR REPLACE FUNCTION boolFunc(x boolean)
RETURN VARCHAR2 AS
BEGIN
	//code 
	return "OK";
END;

CREATE OR REPLACE FUNCTION boolwrap(x int)
RETURN VARCHAR2 AS
BEGIN
	IF (x=1) THEN
	  return boolFunc(TRUE);
	ELSE
	  return boolFunc(FALSE);
	END IF;
END;

เวลาเรียกใช้งานก็ เรียก function boolwrap แทนที่ boolFunc

แหล้งข้อมูล
http://docs.oracle.com/cd/B19306_01/java.102/b14355/apxtblsh.htm#i1005380

Format วันที่ใน Oracle ให้แสดงปี พ.ศ

ปกติแล้วเวลาต้องการแสดงวันที่ในรูปแบบ พ.ศ ผมจะนำมาจัด ูปแบบหลังจาก ดquery ข้อมูลมาจากฐานข้อมูลแล้ว เพราะเวลาเปลี่ยนฐานข้อมูลจะได้ไม่ลำบากอะไรในการแก้ไข แต่พอมาทำงานกับฐานข้อมูล Oracle เพียงอย่างเดียว และ code ก็ไม่ได้มีอะไรมาก เพราะ query ข้อมูลทุกอย่างออกมาเป็น String หมดเลยเลยต้องมาจัด format ตั้งแต่ใน query สืบเสาะหาจนได้วิํธีการมาดังนี้

SELECT TO_CHAR(SYSDATE, 'DD/MM/YYYY', 'NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI') FROM DUAL

ก็จะได้ข้อมูลประมาณนี้

17/08/2554

และทำให้ทราบเพิ่มเติมว่าหาก ต้องการจัดเรียงข้อมูลตามอักษรภาษาไทยก็ใช้แบบนี้ได้เลย

ORDER BY NLSSORT(COL_NAME, 'NLS_SORT=THAI_DICTIONARY')

แหล่งข้อมูล
3 Setting Up a Globalization Support Environment

[Oracle] ความแตกต่างระหว่าง TRUNCATE และ DELETE

พอดีมีปัญหาในการลบข้อมูลในการทำงาน ซึ่งต้องลบข้อมูลจากตารางเดิมออกให้หมด แล้วทำการบันทึกข้อมูลใหม่เข้าไป ตอนแรกที่ทำไว้ก็คือ ใช้คำสั่ง

DELETE FROM XX

ตอนข้อมูลไม่เยอะก็ทำงานได้ดีไม่มีปัญหาอะไร แต่พอข้อมูลเยอะขึ้นเรื่อย ก็จะช้ามากขึ้น เนื่องจากข้อมูลมี Index, PK, FK Constraints อยู่พอสมควร ก็เลยเปลี่ยนมาใช้ คำสั่ง

TRUNCATE TABLE XX 

แทนซึ่งก็ช่วยได้มาก แต่ TRUNCATE เองก็มีข้อจำกัดเหมือนกัน จากการหาข้อมูลก็เจอข้อแตกต่างดังนี้
0. TRUNCATE เป็นคำสั่งใน กลุ่ม DDL (Data Definition Language)
1. TRUNCATE ทำงานได้เร็วกว่า DELETE
2. TRUNCATE ไม่สามารถทำการ Rollback
3. ไม่สามารถระบุ เงื่อนไข WHERE ใน TRUNCATE
4. Triggers จะไม่ทำงานกับคำสั่ง TRUNCATE (กรณีมีการใช้ Triggers ในการตรวจตราการเปลี่ยนแปลงของข้อมูล)
สาเหตุหลัก ประมาณ 5 ข้อที่ว่านี่ละครับที่ทำให้ TRUNCATE ทำงานได้เร็วกว่า DELETE

หมายเหตุ : คำสั่งในกลุ่ม DDL, DCL (Data Control Language) ไม่สามารถ Rollback ได้
ข้อมูลอ้างอิง :
Difference between TRUNCATE, DELETE and DROP commands
Oracle Truncate Table