2018/05/11 - [프로그램 자료/MySQL & MariaDB] - [MySQL / MariaDB] UUID GUID Key 관리방법

ANSI join 문 사용을 위해 hibernate를 5.2.x 버전으로 올리기 위해 spring boot를 2.0 버전으로 올리고 아래 내용이 동작하지 않는 다는 걸 알게 되었음

mariadb 10.x

spring 2.0

hibernate 5.2

Identify Generator(O) : 다른 세션으로 생성 후 닫기(부트 2.6에서 테스트함) - 아마 부트 버전 차이가 문제가 아닐 것 같은 느낌

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.id.IdentifierGenerator;

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

@Slf4j
@RequiredArgsConstructor
public class CustomUuidGenerator implements IdentifierGenerator {

    private final String QUERY = "SELECT BIN_ID() FROM DUAL";

    @Override
    public Serializable generate(SharedSessionContractImplementor session, Object object) {
        PreparedStatement st = null;
        try {
            st = session.getJdbcCoordinator().getStatementPreparer().prepareStatement(QUERY, false);
            ResultSet resultSet = session.getJdbcCoordinator().getResultSetReturn().execute(st);
            if (resultSet.next()) {
                return resultSet.getBytes(1);
            } else {
                throw new RuntimeException("No result");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (st != null) {
                session.getJdbcCoordinator().getLogicalConnection().getResourceRegistry().release(st);
                session.getJdbcCoordinator().afterStatementExecution();
            }
        }
    }

}

Identify Generator(O) : 다른 세션으로 생성 후 닫기 (부트 2.0 정도 까지는 잘 되었던 것 같다.)

import lombok.extern.slf4j.Slf4j;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.id.Configurable;
import org.hibernate.id.IdentifierGenerator;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;


@Slf4j
public class CustomUUIDGenerator implements IdentifierGenerator, Configurable {

    private static final String QUERY_CALL_STORE_FUNC = "{ ? = call ufn_cm_uuid_binary() }";

    @Override
    public Serializable generate(SharedSessionContractImplementor session, Object object) throws HibernateException {
        // TODO Auto-generated method stub
        byte[] result = null;
        Connection connection = null;
        try {
            connection = session.getJdbcConnectionAccess().obtainConnection();
            CallableStatement callableStmt = connection.prepareCall(QUERY_CALL_STORE_FUNC);
            callableStmt.executeQuery();
            // get result from out parameter #1
            result = callableStmt.getBytes(1);
            log.debug("binary pk : {}", byteArrayToHex(result));
        } catch (SQLException sqlException) {
            throw new HibernateException(sqlException);
        } finally {
            try {
                if (!connection.isClosed())
                    connection.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return result;
    }

    private String byteArrayToHex(byte[] a) {
        StringBuilder sb = new StringBuilder();
        for (final byte b : a)
            sb.append(String.format("%02x ", b & 0xff));
        return sb.toString();
    }
}

Identify Generator (x) : 오류난다

import lombok.extern.slf4j.Slf4j;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.id.IdentifierGenerator;

import java.io.Serializable;

@Slf4j

public class CustomUUIDGenerator implements IdentifierGenerator {
    private static final String QUERY_CALL_STORE_PROC = "SELECT UFN_CM_UUID_BINARY()";

    public Serializable generate(SharedSessionContractImplementor session, Object object) throws HibernateException {
        // TODO Auto-generated method stub
        byte[] result = null;
        try {
            result = (byte[]) session.createNativeQuery(QUERY_CALL_STORE_PROC).getSingleResult();
            log.debug("binary pk : {}", byteArrayToHex(result));
            return result;
        } catch (RuntimeException he) {
            he.printStackTrace();
            throw he;
        }
    }

    private String byteArrayToHex(byte[] a) {
        StringBuilder sb = new StringBuilder();
        for (final byte b : a)
            sb.append(String.format("%02x ", b & 0xff));
        return sb.toString();
    }
}

@Entity
@Getter
@Setter
@Table(name = "test_entity")
public class TestEntity {
    @Id
    @Column(columnDefinition = "BINARY(16)", name = "Id")
    @GenericGenerator(name = "customUuid", strategy = "com.motolies.CustomUUIDGenerator")
    @GeneratedValue(generator = "customUuid")
    private byte[] id;

    private String name;
}

현재 개발환경

mariadb 10.x

spring 1.5

hibernate 5.1.x

위와 같은 환경으로 개발을 하고 있는데, PK의 기본값을 uuid로 사용하기로 했다.

숫자형 같은 경우 생성값의 전략만 정해주면 되었는데,

uuid로 하면서 db에서 생성한 값을 가져오려고 하다보니 조금 추가해야 했다.

public class CustomUUIDGenerator extends IdentityGenerator {

    private static final String QUERY_CALL_STORE_FUNC = "{ ? = call fn_uuid_to_binary() }";

    @Override
    public Serializable generate(SessionImplementor session, Object obj) throws HibernateException {
        byte[] result = null;
        try {
            Connection connection = session.getJdbcConnectionAccess().obtainConnection();
            CallableStatement callableStmt = connection.prepareCall(QUERY_CALL_STORE_FUNC);
            callableStmt.executeQuery();
            // get result from out parameter #1
            result = callableStmt.getBytes(1);
        } catch (SQLException sqlException) {
            throw new HibernateException(sqlException);
        }
        return result;
    }
}

@Entity
@Getter
@Setter
@Table(name = "test_entity")
public class TestEntity {

    @Id
    @Column(columnDefinition = "BINARY(16)", name = "Id")
    @GenericGenerator(name = "customUuid", strategy = "com.motolies.CustomUUIDGenerator")
    @GeneratedValue(generator = "customUuid", strategy = GenerationType.IDENTITY)
    private byte[] id;

    @Column(name = "Name", length = 50)
    private String name;

}

@Repository
public interface TestEntityRepository extends JpaRepository<TestEntity, byte[]> {
    public TestEntity findByName(String name);
}

[hibernate] 객체의 PK 생성을 내 마음대로 custom identify generator