Archive for the ‘ Database ’ Category

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

ปัญหาการใช้ Single quote (‘) ใน SQLite

มีงานที่ต้องใช้ PHP กับ SQLite จากเดิม code ที่เขียนการ intsert ใน Database จะไม่มีปัญหากับการ Insert single quote เพราะใช้กับฐานข้อมูล MySQL คำสั่ง insert เป็นดังนี้

INSERT INTO TB(ID, NAME) VALUES(1, 'Hi I\'m Supot');

สามารถ Insert ข้อมูลได้ปกติไม่มีปัญหาอะไร และ ข้อมูลที่ลงไปก็ถูกต้อง คือ ลง Hi I’m Supot แต่พอเปลี่ยนฐานข้อมูลจาก MySQL ไปเป็น SQLite คำสั่้ง insert ข้างต้นไม่สามารถใช้ได้ เนื่องจากคำสั่งข้อมูลได้มาจากการใช้ function addslashes จากการค้นหาพบงิธีการแก้ไขจากที่นี่ SQL Quoting And PHPเปลี่ยนไปใช้ function sqlite_escape_string

$name = sqlite_escape_string(DATA_XX)

แหล่งข้อมูล
SQL Quoting And PHP
addslashes
sqlite_escape_string

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