将MariaDB几何点映射为自定义Hibernate类型

Question

我想用Hibernate将MariaDB Point字段映射到自定义Vector2类型。

我有下表：

CREATE TABLE `ships` (
    `accounts_id` int   NOT NULL,
    `maps_id`     int   NOT NULL,
    `position`    point NOT NULL
) ENGINE InnoDB CHARACTER SET utf8;

我想把它映射到这样一个类：

class Ship {
    public Account account;
    public Map map;
    public Vector2 position;
}

问题在于position字段，如何将其映射到已经存在的类型？

我发现的解决方案暗示使用hibernate-spatial来使用它的Point类，但是我想使用我的Vector2类而不是那个类。

Answer 1

经过几个小时的阅读，我终于明白了。

首先，我需要一种简单的方法来获取point列的X和Y坐标。然而，根据手册它们存储在WKB中。，当我尝试检索原始字节时，有些地方是不正确的：

ResultSet rs = statement.executeQuery("SELECT * FROM accounts_ships");
rs.next();

byte[] position = rs.getBytes("position");
// position ==> byte[25] { 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 80, -44, 64, 0, 0, 0, 0, 0, 0, -55, 64 }

数组开头有4个额外的字节。其中的一部分，一切都是正确的，所以我继续解析结果，同时记住这些字节：

var in = new ByteArrayInputStream(rs.getBytes(names[0]));
if (in.available() == 25) {
    in.skip(4);
}

var order = ByteOrder.BIG_ENDIAN;
if (in.read() == 1) {
    order = ByteOrder.LITTLE_ENDIAN;
}

var typeBytes = new byte[4];
var xBytes    = new byte[8];
var yBytes    = new byte[8];

try {
    in.read(typeBytes);
    in.read(xBytes);
    in.read(yBytes);
} catch (Exception e) {
    throw new HibernateException("Can't parse point column!", e);
}

var type = ByteBuffer.wrap(typeBytes)
                     .order(order);

if (type.getInt() != 1) {
    throw new HibernateException("Not a point!");
}

var x = ByteBuffer.wrap(xBytes)
                  .order(order);
var y = ByteBuffer.wrap(yBytes)
                  .order(order);

return new Vector2((float) x.getDouble(), (float) y.getDouble());

唯一要做的就是定制一个类型，这样hibernate就可以解析它了。

TL;DR这是工作代码：

package com.manulaiko.kalaazu.persistence.database;

import com.manulaiko.kalaazu.math.Vector2;

import org.hibernate.HibernateException;
import org.hibernate.annotations.TypeDef;
import org.hibernate.annotations.TypeDefs;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.usertype.UserType;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.Serializable;
import java.nio.ByteBuffer;
import java.nio.ByteOrder;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

/**
 * Vector2 type.
 * =============
 *
 * Maps a MySQL geometry point to a Vector2 object.
 *
 * @author Manulaiko <manulaiko@gmail.com>
 */
@TypeDefs({
        @TypeDef(name = "point", typeClass = com.manulaiko.kalaazu.math.Vector2.class)
})
public class Vector2Type implements UserType {
    @Override
    public int[] sqlTypes() {
        return new int[]{
                Types.BINARY
        };
    }

    @Override
    public Class returnedClass() {
        return Vector2.class;
    }

    @Override
    public boolean equals(Object o, Object o1) throws HibernateException {
        return o.equals(o1);
    }

    @Override
    public int hashCode(Object o) throws HibernateException {
        return o.hashCode();
    }

    @Override
    public Object nullSafeGet(
            ResultSet rs, String[] names,
            SharedSessionContractImplementor sharedSessionContractImplementor, Object o
    ) throws HibernateException, SQLException {
        var in = new ByteArrayInputStream(rs.getBytes(names[0]));
        if (in.available() == 25) {
            // The WKB format says it's 21 bytes,
            // however, when testing, it retrieved 25 bytes
            // so skip first 4 bytes which are 0.
            in.skip(4);
        }

        var order = ByteOrder.BIG_ENDIAN;
        if (in.read() == 1) {
            order = ByteOrder.LITTLE_ENDIAN;
        }

        var typeBytes = new byte[4];
        var xBytes    = new byte[8];
        var yBytes    = new byte[8];

        try {
            in.read(typeBytes);
            in.read(xBytes);
            in.read(yBytes);
        } catch (Exception e) {
            throw new HibernateException("Can't parse point column!", e);
        }

        var type = ByteBuffer.wrap(typeBytes)
                             .order(order);

        if (type.getInt() != 1) {
            throw new HibernateException("Not a point!");
        }

        var x = ByteBuffer.wrap(xBytes)
                          .order(order);
        var y = ByteBuffer.wrap(yBytes)
                          .order(order);

        return new Vector2((float) x.getDouble(), (float) y.getDouble());
    }

    @Override
    public void nullSafeSet(
            PreparedStatement stmt, Object value, int index,
            SharedSessionContractImplementor sharedSessionContractImplementor
    ) throws HibernateException, SQLException {
        if (value == null) {
            stmt.setNull(index, Types.BINARY);
            return;
        }

        if (!(value instanceof Vector2)) {
            throw new UnsupportedOperationException("can't convert " + value.getClass());
        }

        var v = (Vector2) value;
        try {
            // Store it as 25 bytes because it's how my db server stored it.
            var out = new ByteArrayOutputStream(25);
            out.write(new byte[4]);

            // Store byte order the same as the system's
            if (ByteOrder.nativeOrder() == ByteOrder.BIG_ENDIAN) {
                out.write(0);
            } else {
                out.write(1);
            }

            out.write(
                    ByteBuffer.allocate(8)
                              .putDouble((double) v.x)
                              .array()
            );
            out.write(
                    ByteBuffer.allocate(8)
                              .putDouble((double) v.y)
                              .array()
            );

            stmt.setBytes(index, out.toByteArray());
        } catch (Exception e) {
            throw new HibernateException("Couldn't write point!", e);
        }
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException {
        if (value == null) {
            return null;
        }

        if (!(value instanceof Vector2)) {
            throw new UnsupportedOperationException("can't convert " + value.getClass());
        }
        return new Vector2((Vector2) value);
    }

    @Override
    public boolean isMutable() {
        return true;
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        if (!(value instanceof Vector2)) {
            throw new UnsupportedOperationException("can't convert " + value.getClass());
        }

        return new Vector2((Vector2) value);
    }

    @Override
    public Object assemble(Serializable serializable, Object o) throws HibernateException {
        return serializable;
    }

    @Override
    public Object replace(Object o, Object o1, Object o2) throws HibernateException {
        return o;
    }
}