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 a feature called AI Vector Search, which 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.

Other features developers should get to know include JSON Relational Duality, Property Graph, and True Cache, among the hundreds of new features!

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 tires 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

Installation

By checking the download link, you will see that it is available as a Linux x64 RPM installation file (OL8 or RHEL8), so you may wonder how you can kick the tyres and try it if you’re a Java Developer using Windows or macOS.

The good news is that there’s also a container image available on Oracle Container Registry (OCIR) that 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. Besides, you can check more details about the specific database user (SYS, SYSTEM, PDBADMIN) you may want to use.

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

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

Install SQL Developer

Oracle SQL Developer is a free tool that simplifies the development and management of Oracle Database in both traditional and Cloud deployments. It’s a handy tool for developers and students alike!

The installation process is quite straightforward. Please download and install SQL Developer from this page.

Open the Oracle SQL Developer tool, then create a New Database Connection as below.

Oracle SQL Developer -> New Database Connection

Configure your database connection details, including your password, then click the Test button to validate your connection, then click the Connect button to connect to it.

Oracle Database 23ai Free— connection details

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

SELECT * FROM V$VERSION;

Now, click the Run Statement button, below:

Oracle Database 23ai Free — query execution

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"

SQL DDL script

Create the required database table with the SQL script, and 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.

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>";

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) to get all the details about each type. Besides, there are new interfaces such as VectorMetaData and methods such as getVectorMetaData of 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 how to insert a vector with JDBC. As you can see, from a Java standpoint it’s quite straightforward to do it. 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);
  double[] vector = { 1.1, 2.2, 3.3 };
  System.out.println("SQL DML: " + insertSql);
  System.out.println("VECTOR to be inserted: " + Arrays.toString(vector));
  insertStatement.setInt(1, index++);
  insertStatement.setObject(2, vector, OracleType.VECTOR);
  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);
  double[] vector = { 1.1, 2.2, 3.3 };
  System.out.println("SQL DML: " + insertSql);
  System.out.println("VECTOR to be inserted: " + Arrays.toString(vector));
  insertStatement.setInt(1, index++);
  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 {
  double[][] vectors = { { 1.1, 2.2, 3.3 }, { 1.3, 7.2, 4.3 }, { 5.9, 5.2, 7.3 } };
  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 (double[] vector : vectors) {
    insertStatement.setInt(1, index++);
    insertStatement.setObject(2, vector, OracleType.VECTOR);
    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();
  double[] vector = null;
  while (resultSet.next()) {
   vector = resultSet.getObject(2, double[].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 Euclidean 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, ?, DOT)";

...

 private void retrieveVectorWithBoundVector(Connection connection) throws SQLException {
  // Bind a Vector to a select
  PreparedStatement queryStatement = connection.prepareStatement(querySqlWithBind);
  double[] inputVector = { 1.0, 2.2, 3.3 };
  System.out.println("SQL DML: " + querySqlWithBind);
  System.out.println("Bound VECTOR: " + Arrays.toString(inputVector));
  queryStatement.setObject(1, inputVector, OracleType.VECTOR);
  ResultSet resultSet = queryStatement.executeQuery();
  resultSet.next();
  double[] outputVector = resultSet.getObject(2, double[].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, and you will see the messages below logged to your console output as expected.

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

Wrapping it up

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

Once again, for a comprehensive explanation of all the features comprised by this release, please check the Oracle Database 23ai — New Features Guide.

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

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 OCI Cloud Free Tier!

Oracle Database 23ai
Java
Jdbc
Vector Search
Vector Database

--

--

Juarez Junior
Oracle Developers

Written by Juarez Junior

1.1K Followers

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

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams