Open in app

Sign in

Write

Sign in

Oracle AI Vector Search for Java Developers with the Oracle Database 23ai

Juarez Junior
Oracle Developers
Published in
10 min readMay 3, 2024
Oracle Database 23ai

by Juarez Junior

Introduction

As you may have heard, Oracle released Oracle Database 23ai, the next long-term support release of the industry-leading Oracle Database. It provides over 300 new features, strongly focusing on artificial intelligence (AI) and developer productivity.

Among such features, Oracle Database 23ai now includes semantic search capabilities supported by Oracle AI Vector Search. This feature includes a new vector data type, vector indexes, a Vector Utility API, Support for ONNX-Format Models as First-Class Database Objects, and new vector search SQL operators and syntax.

Such features enable Oracle Database 23ai to store the semantic content of documents, images, and other unstructured data as vectors, and use these to run fast similarity queries.

Among the hundreds of new features, developers should get to know JSON Relational Duality, Property Graph, and True Cache.

Per the announcement, Oracle Database 23ai is now available for download. We’ll use the Oracle Database 23ai Free on this blog post, and the container image for Docker/Podman that you can get from OCIR (Oracle Container Registry).

Nevertheless, there are plenty of options available for developers, including an Oracle VM VirtualBox (ova) image, several Linux versions, and also available in the cloud on Exadata Database Service, Base Database Service, and Always Free Autonomous Database.

Speaking of Java, Oracle JDBC has added the necessary components to the JDBC drivers to support the AI Vector Search and the Vector Data Type, including SQLType, DatabaseMetaData, ResultSetMetaData and ParameterMetaData, VectorMetaData, Java to SQL Conversions with PreparedStatement and CallableStatement, SQL to Java Conversions with CallableStatement, SQL to Java Conversions with CallableStatment and ResultSet, and VECTOR Datum class. For all the details please check the Working with Vectors section of the JDBC Developer’s Guide.

Needless to say, this blog post is not an exhaustive exploration of all those features. Its goal is to provide you with a quick steps guide to kick the tyres and try the Oracle Database 23ai with Java and the Vector Search support provided by the updated Oracle JDBC Driver.

So without further ado, let’s get started!

Prerequisites

Oracle Database 23ai

If you check the download link, you will see that it is available as a Linux x64 RPM installation file (OL8 or RHEL8). So, if you're a Java developer using Windows or macOS, you may wonder how you can kick the tyres and try it.

The good news is that a container image is also available on Oracle Container Registry (OCIR), which you can use to install it and test its amazing features.

Let’s try it quickly and easily and test it on Windows. To do so, first, run the docker pull command below:

docker pull container-registry.oracle.com/database/free:latest

Now, you can run the docker images command below to confirm that it was pulled properly:

Then, run the command below from the Windows Command Prompt (cmd):

docker run -d -p 1521:1521 -e ORACLE_PWD=<your_password> -v oracle-volume:/opt/oracle/oradata container-registry.oracle.com/database/free:latest

Replace <your password> above with your chosen password as required. The command above will run a new persistent database container (data is kept throughout container lifecycles).

If everything goes well, you can run the command docker ps -al to confirm that your container instance is starting as expected.

Container status — health: starting

Please note that the Oracle Database 23ai will be ready to use when the STATUS field shows (healthy), as below.

Container status — healthy

There are some additional options if you want to configure environment variables to support your connection details, such as username, password, and other variables. You can also check more details about the specific database user (SYS, SYSTEM, PDBADMIN) you may want to use.

If you want to explore such options, please check the official page for the related container image on the Oracle Container Registry (OCIR).

Now your database is installed! Let’s proceed to access it from both SQL Developer and Java!

Connect to Oracle Database 23ai

The next step is to connect to Oracle Database 23ai to run the required DDL script. Please follow the instructions in this blog post, which covers how to do so with the IntelliJ IDEA tool.

Alternatively, you can also use Oracle SQL Developer, a free tool that simplifies the development and management of Oracle Databases in traditional and cloud deployments, or any other tool you might prefer.

SQL DDL script

Provided that everything was configured properly, you can run a query to validate it all. Copy the sample query below:

SELECT * FROM V$VERSION;

Check the results and look for the BANNER_FULL column in the resulting record. You should see a result similar to the one below, which confirms you’re connected to an instance of Oracle Database 23ai.

"Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05"

Now, let’s create the required database table with the SQL script; it’s also included below for your convenience. Note the specification of a VECTOR data type column below as VECTOR_DATA VECTOR(3, FLOAT64).

CREATE USER VECTOR_USER IDENTIFIED BY <YOUR_PASSWORD> QUOTA UNLIMITED ON USERS;
GRANT DB_DEVELOPER_ROLE TO VECTOR_USER;
GRANT CREATE SESSION TO VECTOR_USER;

GRANT SELECT ANY TABLE ON SCHEMA VECTOR_USER TO VECTOR_USER;
GRANT SELECT ANY TABLE ON SCHEMA VECTOR_USER TO VECTOR_USER;
GRANT SELECT ANY TABLE ON SCHEMA VECTOR_USER TO VECTOR_USER;
GRANT SELECT ANY TABLE ON SCHEMA VECTOR_USER TO VECTOR_USER;
ALTER SESSION SET CURRENT_SCHEMA = VECTOR_USER;
CREATE TABLE VECTOR_USER.ORACLE_AI_VECTOR_SEARCH_DEMO (ID NUMBER PRIMARY KEY, VECTOR_DATA VECTOR(3, FLOAT64));
COMMIT;

Now you can proceed to connect to your Oracle Database 23ai Free instance from Java! The code sample is available on GitHub.

Connect to your Oracle Database 23ai Free instance from a Java application with JDBC

The first step is to add the required Oracle JDBC and UCP JARs as dependencies. You have to include the JAR dependencies listed below in your Maven pom.xml file.

Oracle JDBC Driver + UCP 23ai (23.4.0.24.05) — Maven dependencies

Now, you can use your preferred Java IDE, such as Eclipse, IntelliJ, or VS Code, to test the example app!

JDBC connection details

Copy the code sample and replace the placeholders for the JDBC connection details with our own values.

 // https://docs.oracle.com/en/database/oracle/oracle-database/23/jjdbc/data-sources-and-URLs.html
 // JDBC connection URL sample - "jdbc:oracle:thin:@localhost:1521/FREEPDB1"
 private final static String URL = "<JDBC_CONNECTION_URL>";
 private final static String USERNAME = "<ORACLE_DATABASE_USERNAME>";
 private final static String PASSWORD = "<ORACLE_DATABASE_PASSWORD>";

Alternatively, you can also configure environment variables to store such values and retrieve them from your Java code as shown below.

JDBC connection details — environment variables

Oracle JDBC — PooledDataSource

The code sample uses an instance of oracle.ucp.jdbc.PoolDataSource, as shown below.

 private Connection getConnectionFromPooledDataSource() throws SQLException {
  // Create pool-enabled data source instance
  PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
  // set connection properties on the data source
  pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
  pds.setURL(URL);
  pds.setUser(USERNAME);
  pds.setPassword(PASSWORD);
  // Configure pool properties with a Properties instance
  Properties prop = new Properties();
  prop.setProperty("oracle.jdbc.vectorDefaultGetObjectType", "String");
  pds.setConnectionProperties(prop);
  // Override any pool properties directly
  pds.setInitialPoolSize(10);
  // Get a database connection from the pool-enabled data source
  Connection conn = pds.getConnection();
  return conn;
 }

The oracle.jdbc.OracleType Java Enum and the VECTOR constants

JDBC drivers represent SQL data types as instances of the java.sql.SQLType interface. For each data type of Oracle Database, the Oracle JDBC Driver declares an instance of SQLType as a member of oracle.jdbc.OracleType.

New instances of SQLType have been added to the oracle.jdbc.OracleType enum for Vector support. These instances represent the VECTOR data type. In summary:

Check the Javadoc documentation (links above) for all the details about each type. Besides, there are new interfaces, such as VectorMetaData, and methods, such as getVectorMetaData, of the OracleResultSetMetaData and OracleParameterMetaData interfaces.

Once again, for all the details please check the Working with Vectors section of the JDBC Developer’s Guide.

Inserting a vector with OracleType.VECTOR

The method below provides an example of inserting a vector with JDBC. As you can see, from a Java standpoint, it’s quite straightforward. The example uses an array of double values and the OracleType.VECTOR type. Besides, the SQL DML query is a simple one as well, with no special syntax for this scenario.

private String insertSql = "INSERT INTO ORACLE_AI_VECTOR_SEARCH_DEMO (ID, VECTOR_DATA) VALUES (?, ?)";
...
 
 private void insertVector(Connection connection) throws SQLException {
  PreparedStatement insertStatement = connection.prepareStatement(insertSql);
  float[] vector = { 1.1f, 2.2f, 3.3f };
  System.out.println("SQL DML: " + insertSql);
  System.out.println("VECTOR to be inserted: " + Arrays.toString(vector));
  insertStatement.setInt(1, randomize());
  insertStatement.setObject(2, vector, OracleType.VECTOR_FLOAT64);
  insertStatement.executeUpdate();
 }

Inserting a vector with OracleType.VARCHAR2

The Oracle JDBC driver is quite robust yet flexible. The method below provides an example of how to insert a vector with JDBC but now using OracleType.VARCHAR2 instead of OracleType.VECTOR. Again, there are no tricks here; as you can see from a Java standpoint, it’s quite straightforward to do it.

private String insertSql = "INSERT INTO ORACLE_AI_VECTOR_SEARCH_DEMO (ID, VECTOR_DATA) VALUES (?, ?)";

...

 private void insertVectorWithVarChar2(Connection connection) throws SQLException {
  PreparedStatement insertStatement = connection.prepareStatement(insertSql);
  float[] vector = { 1.1f, 2.2f, 3.3f };
  System.out.println("SQL DML: " + insertSql);
  System.out.println("VECTOR to be inserted: " + Arrays.toString(vector));
  insertStatement.setInt(1, randomize());
  insertStatement.setObject(2, Arrays.toString(vector), OracleType.VARCHAR2);
  insertStatement.executeUpdate();
 }

Inserting vectors as a batch

Another interesting option is to use the Batch API to insert many vectors at once, as shown below.

private String insertSql = "INSERT INTO ORACLE_AI_VECTOR_SEARCH_DEMO (ID, VECTOR_DATA) VALUES (?, ?)";

...

 private void insertVectorWithBatchAPI(Connection connection) throws SQLException {

  float[][] vectors = { { 1.1f, 2.2f, 3.3f }, { 1.3f, 7.2f, 4.3f }, { 5.9f, 5.2f, 7.3f } };
  System.out.println("SQL DML: " + insertSql);
  System.out.println("VECTORs to be inserted as a batch: " + Arrays.toString(vectors[0]) + ", "
    + Arrays.toString(vectors[1]) + ", " + Arrays.toString(vectors[2]));
  try (PreparedStatement insertStatement = connection.prepareStatement(insertSql)) {
   for (float[] vector : vectors) {
    insertStatement.setInt(1, randomize());
    insertStatement.setObject(2, vector, OracleType.VECTOR_FLOAT64);
    insertStatement.addBatch();
   }
   insertStatement.executeBatch();
  }
 }

Retrieving a vector as arrays

The method below provides an example of how to perform SQL selects to retrieve your vectors as arrays.

private String querySql = "SELECT ID, VECTOR_DATA FROM ORACLE_AI_VECTOR_SEARCH_DEMO";

...

 private void retrieveVectorAsArray(Connection connection) throws SQLException {
  PreparedStatement queryStatement = connection.prepareStatement(querySql);
  System.out.println("SQL DML: " + querySql);
  ResultSet resultSet = queryStatement.executeQuery();
  float[] vector = null;
  while (resultSet.next()) {
   vector = resultSet.getObject(2, float[].class);
  }
  System.out.println("Retrieved VECTOR: " + Arrays.toString(vector));
 }

Retrieving a vector as Strings

Another example but now the vectors are retrieved as strings.

private String querySql = "SELECT ID, VECTOR_DATA FROM ORACLE_AI_VECTOR_SEARCH_DEMO";

...

 private void retrieveVectorAsString(Connection connection) throws SQLException {
  PreparedStatement queryStatement = connection.prepareStatement(querySql);
  System.out.println("SQL DML: " + querySql);
  ResultSet resultSet = queryStatement.executeQuery();
  String vector = null;
  while (resultSet.next()) {
   vector = (String) resultSet.getObject(2);
  }
  System.out.println("Retrieved VECTOR: " + vector);
 }

Retrieving a vector with a bound vector (input parameter)

This last example shows how to use a bound array to retrieve your vector based on the COSINE_DISTANCE and the VECTOR_DISTANCE function.

private String querySqlWithBind = "SELECT ID, VECTOR_DATA FROM ORACLE_AI_VECTOR_SEARCH_DEMO ORDER BY VECTOR_DISTANCE(VECTOR_DATA, ?, COSINE)";

...

 private void retrieveVectorWithBoundVector(Connection connection) throws SQLException {
  PreparedStatement queryStatement = connection.prepareStatement(querySqlWithBind);
  float[] inputVector = { 1.0f, 2.2f, 3.3f };
  System.out.println("SQL DML: " + querySqlWithBind);
  System.out.println("Bound VECTOR: " + Arrays.toString(inputVector));
  queryStatement.setObject(1, inputVector, OracleType.VECTOR_FLOAT64);
  ResultSet resultSet = queryStatement.executeQuery();
  resultSet.next();
  float[] outputVector = resultSet.getObject(2, float[].class);
  System.out.println("Retrieved VECTOR: " + Arrays.toString(outputVector));
 }

Full code sample

For your convenience, the code sample is included below as well.

Run the app— Oracle AI Vector Search with the Oracle JDBC driver

Provided that you followed all the steps above, you can now run the code sample above with your preferred Java IDE. As expected, the messages below will be logged to your console output.

Oracle AI Vector Search with the Oracle JDBC driver and Oracle Database 23ai

Wrapping it up

That’s it! You can now create AI-related applications with Java, JDBC, and the Oracle Database 23ai combined with Oracle AI Vector Search.

Once again, the Oracle Database 23ai—New Features Guide provides a comprehensive explanation of this release's features.

I hope you liked this blog post. Several exciting blog posts are coming to cover things like Spring AI, LangChain4J, RAG with Oracle Database 23ai and other topics. Stay tuned!

References

Oracle® Database JDBC Java API Reference, Release 23ai

Oracle JDBC Driver 23ai (23.4.0.24.05) — Maven Central

Developers Guide For Oracle JDBC on Maven Central

Oracle Database 23ai — New Features

Oracle Announces General Availability of AI Vector Search in Oracle Database 23ai

Oracle Database Free Release 23ai — Container Image Documentation

Oracle Database 23ai Free VirtualBox Appliance

Quickstart: Connect to Oracle Database 23ai using IntelliJ IDEA

Oracle SQL Developer

Develop Java applications with Oracle Database

The Java Tutorials: JDBC Basics

Oracle Developers and Oracle OCI Free Tier

Join our Oracle Developers channel on Slack to discuss Java, JDK, JDBC, GraalVM, Microservices with Spring Boot, Helidon, Quarkus, Micronaut, Reactive Streams, Cloud, DevOps, IaC, and other topics!

Build, test, and deploy your applications on Oracle Cloud—for free! Get access to the OCI Cloud Free Tier!

Oracle Database 23ai
Java
Jdbc
Vector Search
Vector Database

--

--

Juarez Junior
Oracle Developers

Written by Juarez Junior

1.1K Followers
Writer for

Developer Relations @ Oracle ☕️🥑 https://linktr.ee/juarezjunior

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams