Oracle REST Data Services (ORDS) provides a powerful way to expose Oracle databases via REST, what if you could extend this capability to databases like DB/2, MySQL, PostgreSQL, and SQL Server—without installing ORDS on each database? In summary, your applications can interact with multiple databases, because a universal RESTful interface can streamline access and reduce development complexity.
Find out more and see the custom code in action!
As an extensible product where you can develop your own plugins, you can code your own ORDS JDBC AutoREST Plugin. To learn more about plugins in ORDS see: Crafting your ORDS plugin the Maven way
What is the ORDS JDBC AutoREST Plugin?
The ORDS JDBC AutoREST Plugin is a custom plugin for Oracle REST Data Services that enables RESTful access to a variety of relational databases. Using standard JDBC, it acts as a bridge between ORDS and databases beyond Oracle, providing a unified API layer.
Key Features
Multi-Database Support: Works with DB/2, MySQL, Oracle, PostgreSQL, and SQL Server.
Standard JDBC: Uses JDBC drivers to communicate with target databases.
No ORDS Installation Required: The target database does not need an ORDS installation.
RESTful Interface: Automatically generates REST endpoints for CRUD operations.
API Driven: The service definition metadata is in an OpenAPIV3 document which is reloaded every 5 minutes. Changes are picked up without a restart.
How It Works
Architecture Overview
The plugin integrates with ORDS and utilizes JDBC to interact with databases. ORDS remains the entry point, processing RESTful requests and translating them into SQL queries executed via JDBC connections.
A client sends a REST request to ORDS.
ORDS uses the basic authentication credentials to make a JDBC connection to the target database.
The ORDS JDBC AutoREST Plugin reads the API document that describes the database objects and services. It then generates the SQL statement and executes it over JDBC.
The database processes the request and returns results.
ORDS formats and sends back the response.
Subtle difference from ORDS AutoREST:
No installation required in the database
No HATEOS links generated
Only basic authentication
Of course, since you are building your own custom plugin, you can code any additional functionality you want.
Uses standard JDBC classes. No vendor specific code references.
Example Usage
Once configured, the plugin enables RESTful endpoints that map to database tables and views. For instance, a request to:
GET /autorest/employees/
Could return JSON data from an employees table, regardless of whether the underlying database is Oracle, MySQL, PostgreSQL, etc. Note that a HTTP request will require basic authentication with credentials for a database user and that user must have access to the underlying data.
Setting Up the Plugin
Prerequisites
Oracle REST Data Services (ORDS) installed and running.
JDBC drivers for the target databases.
Installation Steps
Clone the repository:git clone https://github.com/pobalopalous/ords-jdbc-autorest.git
Build the plugin and deploy it into your ORDS instance.
Configure database connections using JDBC connection strings.
Update the API document for your database to target tables or views.
Using x-autorest extension
An extension called x-autorest is used in the API document to define what tables and views are to be available, as well as what operations ( DELETE, GET, PUT, POST ) are permitted. Note that this requires that tables are defined with a primary key.
The REST service path can be an alias for the database object.For databases that use the concept of an object owner, it can be specified here.
A description of the x-autorest extension.
x-autorest:
owner: DB2INST1 // Database user that owns the object
identifier: INVENTORY // The database object
type: table // The type of object table or view
Benefits & Use Cases
Why Use This Plugin?
Simplified API Development: Unified RESTful API for multiple databases.
Reduced Overhead: No need to install ORDS on every database.
Flexible Database Integration: Easily switch between databases without major application changes.
Ideal Use Cases
Multi-database applications needing a standardized REST API.
Data migration projects requiring uniform RESTful access.
Reporting and analytics tools aggregating data from multiple sources.
Conclusion
The ORDS JDBC AutoREST Plugin provides a powerful way to extend RESTful database access beyond Oracle to DB/2, MySQL, PostgreSQL, and SQL Server. With minimal setup and configuration, developers can use ORDS to expose database resources through a standard REST API, ensuring reliability without needing to install ORDS on each target database.
Explore the source code and start using it today: GitHub Repository.
Brian Spendolini already covers the steps for defining a MySQL connection in Database Tools so I won’t repeat them here. Also covered in my previous article is how to determine the REST Enabled SQL URL for the Database Tools connection and how to define the API key so that APEX can access that service.
What’s different now in APEX 22.1 is that it can determine the database product behind the connection. So when you add a REST Enabled SQL service for a MySQL database it will ask you for the default database to use for all queries. That is quite literal, APEX will send a ‘use <database>’ statement as part of the query script sent over REST Enabled SQL.
APEX does something special when adding a REST Enabled SQL service URL for a MySQL connection
Using a query on your MySQL data
The connection defined is to a database which has the sample sakila database so lets have a chart in APEX to show the Sales by Film Category data.
Create an APEX applicationAdding a chart with REST Enabled SQL source for the MySQL Sakila databaseThe query for getting category and total_sales from the sales_by_film_category
Note in the above image the Column Mapping for the series specifies the Label and Value to use.
Chart showing data from the hosted MySQL Data Service
Conclusion
Now you can, with APEX 22.1, query data from your Oracle Cloud hosted MySQL database. The use of ORDS REST Enabled SQL service in this manner is only supported when used with Oracle Cloud hosted MySQL databases.
Or at least the majority of data types. In a previous post, MySQL Sakila Structure, I went through the structure of the example sakila database and mentioned that it does not cover all supported MySQL data types. That means the example database is not sufficient on its own to test the behaviour of the MySQL Connector/J JDBC driver and how it works with different data types. In an attempt to bridge that gap I have produced a script to create a single table with all the supported data types along with modifiers that have an impact on how MySQL Connector/J represents them. See https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-type-conversions.html for more details on the nuances of the implementation’s handling of particular data types.
MySQL has a sample database which is useful for tutorials, samples and a reference for testing integration scenarios. The scripts to create the sakila sample schema put tables, views, procedures, triggers and data in place.
The sample also includes the sakila.mwb file which is a MySQL Workbench data model that you can open within MySQL Workbench to examine the database structure. For more information, see MySQL Workbench.
Data model of sakila sample database
Not all MySQL data types are used in the sample schema but it does cover the most common as well as a BLOB for a picture and GEOMETRY for location data. Here’s a description of each table in a convenience text structure which is searchable.
describe actor;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| actor_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | MUL | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe actor_info;
+------------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------------+------+-----+---------+-------+
| actor_id | smallint unsigned | NO | | 0 | |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | | NULL | |
| film_info | text | YES | | NULL | |
+------------+-------------------+------+-----+---------+-------+
describe address;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| address_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| address | varchar(50) | NO | | NULL | |
| address2 | varchar(50) | YES | | NULL | |
| district | varchar(20) | NO | | NULL | |
| city_id | smallint unsigned | NO | MUL | NULL | |
| postal_code | varchar(10) | YES | | NULL | |
| phone | varchar(20) | NO | | NULL | |
| location | geometry | NO | MUL | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe category;
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
| category_id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(25) | NO | | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
describe city;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| city_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| city | varchar(50) | NO | | NULL | |
| country_id | smallint unsigned | NO | MUL | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe country;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| country_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| country | varchar(50) | NO | | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe customer;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| customer_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| store_id | tinyint unsigned | NO | MUL | NULL | |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | MUL | NULL | |
| email | varchar(50) | YES | | NULL | |
| address_id | smallint unsigned | NO | MUL | NULL | |
| active | tinyint(1) | NO | | 1 | |
| create_date | datetime | NO | | NULL | |
| last_update | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe customer_list;
+----------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------------+------+-----+---------+-------+
| ID | smallint unsigned | NO | | 0 | |
| name | varchar(91) | YES | | NULL | |
| address | varchar(50) | NO | | NULL | |
| zip code | varchar(10) | YES | | NULL | |
| phone | varchar(20) | NO | | NULL | |
| city | varchar(50) | NO | | NULL | |
| country | varchar(50) | NO | | NULL | |
| notes | varchar(6) | NO | | | |
| SID | tinyint unsigned | NO | | NULL | |
+----------+-------------------+------+-----+---------+-------+
describe film;
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| film_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(128) | NO | MUL | NULL | |
| description | text | YES | | NULL | |
| release_year | year | YES | | NULL | |
| language_id | tinyint unsigned | NO | MUL | NULL | |
| original_language_id | tinyint unsigned | YES | MUL | NULL | |
| rental_duration | tinyint unsigned | NO | | 3 | |
| rental_rate | decimal(4,2) | NO | | 4.99 | |
| length | smallint unsigned | YES | | NULL | |
| replacement_cost | decimal(5,2) | NO | | 19.99 | |
| rating | enum('G','PG','PG-13','R','NC-17') | YES | | G | |
| special_features | set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') | YES | | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
describe film_actor;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| actor_id | smallint unsigned | NO | PRI | NULL | |
| film_id | smallint unsigned | NO | PRI | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe film_category;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| film_id | smallint unsigned | NO | PRI | NULL | |
| category_id | tinyint unsigned | NO | PRI | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe staff;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| staff_id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | | NULL | |
| address_id | smallint unsigned | NO | MUL | NULL | |
| picture | blob | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
| store_id | tinyint unsigned | NO | MUL | NULL | |
| active | tinyint(1) | NO | | 1 | |
| username | varchar(16) | NO | | NULL | |
| password | varchar(40) | YES | | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe staff_list;
+----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| ID | tinyint unsigned | NO | | 0 | |
| name | varchar(91) | YES | | NULL | |
| address | varchar(50) | NO | | NULL | |
| zip code | varchar(10) | YES | | NULL | |
| phone | varchar(20) | NO | | NULL | |
| city | varchar(50) | NO | | NULL | |
| country | varchar(50) | NO | | NULL | |
| SID | tinyint unsigned | NO | | NULL | |
+----------+------------------+------+-----+---------+-------+
describe store;
+------------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| store_id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| manager_staff_id | tinyint unsigned | NO | UNI | NULL | |
| address_id | smallint unsigned | NO | MUL | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+------------------+-------------------+------+-----+-------------------+-----------------------------------------------+
That’s it. All the tables and views in the sakila sample database described.
Notice that sakila does not cover all MySQL data types such as TINYBLOB, VARBINARY etc. The next blog post will examine how the MySQL Connector/J JDBC driver represents that data and introduce a new table for testing all the supported data types.
Peter's Random Topic Library 