Oracle 23ai JDBC访问vector表

1. 前提条件

1. JDK 11
2. Oracle JDBC11 for Oracle 23ai

2. 创建表

CREATE TABLE SENTENCE_EMBEDDINGS (
    ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    SENTENCE VARCHAR2(4000),
    EMBEDDING VECTOR(384)
);

3. java程序

首先针对sentence生成向量，这一步调用外部的embed生成REST服务，例子中假设部署在本地8000端口(可以搭建all-MiniLM-L6-v2)。

import java.net.http.*;
import java.net.http.HttpRequest.BodyPublishers;
import java.net.URI;
import java.sql.*;

import java.io.IOException;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.core.JsonProcessingException;

import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
import oracle.jdbc.OracleType;

public class TestVector {

    private static float[] generateVector(String content) throws IOException, InterruptedException, JsonProcessingException{
        String requestBody = "{ \"text\": \"" + content + "\" }";

        // 1. Call embedding server
        HttpClient client = HttpClient.newHttpClient();
        HttpRequest request = HttpRequest.newBuilder()
                .uri(URI.create("http://localhost:8000/embed"))
                .header("Content-Type", "application/json")
                .POST(BodyPublishers.ofString(requestBody))
                .build();
        HttpResponse<String> response = client.send(request, HttpResponse.BodyHandlers.ofString());

        // 3. Parse JSON
        ObjectMapper mapper = new ObjectMapper();
        JsonNode root = mapper.readTree(response.body());
        JsonNode vectorNode = root.get("embedding");

        // 4. Convert to float[]
        int dim = vectorNode.size();
        float[] vector = new float[dim];
        for (int i = 0; i < dim; i++) {
            vector[i] = (float) vectorNode.get(i).asDouble();
        }
        return vector;
    }

    private static void insertVectorToOracle(String content, float[] vector) throws SQLException {
         // 5. JDBC insert into Oracle
        Connection conn = DriverManager.getConnection(
            "jdbc:oracle:thin:@...",
            "<username>",
            "<password>"
        );

        String sql = "INSERT INTO SENTENCE_EMBEDDINGS (SENTENCE, EMBEDDING) VALUES (?, ?)";
        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, content);
        stmt.setObject(2, vector, OracleType.VECTOR_FLOAT32);
        stmt.executeUpdate();

        stmt.close();
        conn.close();
    }

    private static void selectVectorFromOracle(float[] vector) throws SQLException {
         // 5. JDBC insert into Oracle
        Connection conn = DriverManager.getConnection(
            "jdbc:oracle:thin:@...",
            "<username>",
            "<password>"
        );

        String sql = "SELECT ID, SENTENCE FROM SENTENCE_EMBEDDINGS ORDER BY EMBEDDING <-> TO_VECTOR(?, 384, FLOAT32) FETCH FIRST 3 ROWS ONLY";
        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setObject(1, vector, OracleType.VECTOR_FLOAT32);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("ID");
            String sentence = rs.getString("SENTENCE");
            System.out.println("ID: " + id + ", Sentence: " + sentence);
        }

        rs.close();
        stmt.close();
        conn.close();
    }


    public static void main(String[] args) throws Exception {
        String sentence = "hello world";
        float[] vector = generateVector("you");

        insertVectorToOracle(sentence, vector);
        selectVectorFromOracle(vector);
        System.out.println("Success.");
    }
}