Bridging native JDBC calls with DBaaS using ESB
Methods and apparatus are provided for receiving a database query and executing the query against a remote database. The database query is used to generate an execution sequence comprising HTTP requests and data processing operations based on the database query. The HTTP requests are sent to a RESTful API provided by a database system. The HTTP requests are executed by the database system against a database, and the responses from the database system are returned to be processed according to the data processing operations. Finally, a set of query results is returned to the sender of the original database query.
1. A method comprising:
receiving, by an Enterprise Service Bus (ESB) component of an ESB system, from a first system, a database query formatted in a first format;
generating a Structured Query Language (SQL) query plan for executing the database query, wherein the SQL query plan comprises a plurality of operations;
generating an execution sequence based on the SQL query plan, by mapping each of the plurality of operations within the SQL query plan to be processed by either the ESB component a second system, wherein mapping the plurality of operations comprises:
upon determining that a first operation of the plurality of operations is a database operation used to insert data into a database, generating an HTTP request to carry out the first operation, wherein the HTTP request is a POST request;
mapping, to the second system, the first operation comprising the HTTP request; and
mapping, to the ESB component, a second operation comprising a data processing operation to be performed on a result of the database operation;
transmitting, based on the mapping, the HTTP request to a RESTful API provided by the second system, wherein the second system is configured to execute the HTTP request against the database;
receiving, by the ESB component, from the second system, a first set of results generated by executing the HTTP request against the database;
generating, by the ESB component, a set of query results for the database query by processing the received first set of results, comprising executing, based on the mapping, the data processing operation on the first set of results received from the second system; and
sending, to the first system, the set of query results.
2. The method of claim 1 , wherein the first set of results comprises one or more JSON objects, and wherein generating the set of query results for the database query comprises mapping the one or more JSON objects to one or more SQL formatted set of query results.
3. The method of claim 1 , wherein the data processing operation comprises one of (i) a join and (ii) a sort.
4. The method of claim 1 , wherein the database query is received from a database driver that intercepts SQL queries on the first system and reroutes them, and wherein the set of query results comprises one or more JSON objects.
5. The method of claim 1 , wherein generating the execution sequence further comprises assigning a respective in queue and a respective out queue for each of the first data processing operation and the HTTP request, and defining data transmission paths between the plurality of in queues and out queues.
6. The method of claim 1 , the method further comprising converting the database query from a first database schema associated with a legacy database to a second database schema associated with the database of the second system, wherein the first and second database schemas are different.
7. A system, comprising:
a first system;
a second system; and
an enterprise service bus (ESB) system comprising one or more computer processors and configured perform an operation by operation of the one or more computer processors, the operation comprising:
receiving, by an ESB component of the ESB system, from the first system, a database query formatted in a first format;
generating a Structured Query Language (SQL) query plan for executing the database query, wherein the SQL query plan comprises a plurality of operations;
generating an execution sequence based on the SQL query plan, by mapping each of the plurality of operations within the SQL query plan to be processed by either the ESB component or a second system, wherein mapping the plurality of operations comprises:
upon determining that a first operation of the plurality of operations is a database operation used to insert data into a database, generating an HTTP request to carry out the first operation, wherein the HTTP request is a POST request;
mapping, to the second system, the first operation comprising the HTTP request; and
mapping, to the ESB component, a second operation comprising a data processing operation to be performed on a result of the database operation;
transmitting, based on the mapping, the HTTP request to a RESTful API provided by the second system, wherein the second system is configured to execute the HTTP request against the database;
receiving, by the ESB system, from the second system, a first set of results generated by executing the HTTP request against the database;
generating, by the ESB system, a set of query results for the database query by processing the received first set of results, comprising executing, based on the mapping, the data processing operation on the first set of results received from the second system; and
sending, to the first system, the set of query results.
8. The system of claim 7 , wherein the first set of results comprises one or more JSON objects, and wherein generating the set of query results for the database query comprises mapping the one or more JSON objects to one or more SQL formatted set of query results.
9. The system of claim 7 , wherein the data processing operation comprises a sort.
10. The system of claim 7 , wherein the database query is received from a database driver that intercepts SQL queries on the first system and reroutes them, and wherein the set of query results comprises one or more JSON objects.
11. The system of claim 7 , wherein generating the execution sequence further comprises assigning a respective in queue and a respective out queue for each of the first data processing operation and the HTTP request, and defining data transmission paths between the plurality of in queues and out queues.
12. The system of claim 7 , the operation further comprising converting the database query from a first database schema associated with a legacy database to a second database schema associated with the database of the second system, wherein the first and second database schemas are different.
13. A computer program product for providing access to a remote database, the computer program product comprising:
a computer-readable storage medium containing computer-readable program code that, when executed by operation of one or more computer processors, performs an operation, comprising:
receiving, by an Enterprise Service Bus (ESB) component of an ESB system, from a first system, a database query formatted in a first format;
generating a Structured Query Language (SQL) query plan for executing the database query, wherein the SQL query plan comprises a plurality of operations;
generating an execution sequence based on the SQL query plan, by mapping each of the plurality of operations within the SQL query plan to be processed either the ESB component or a second system, wherein mapping the plurality of operations comprises:
upon determining that a first operation of the plurality of operations is a database operation used to insert data into a database, generating an HTTP request to carry out the first operation, wherein the HTTP request is a POST request;
mapping, to the second system, the first operation comprising the HTTP request; and
mapping, to the ESB component, a second operation comprising a data processing operation to be performed on a result of the database operation;
transmitting, based on the mapping, the HTTP request to a RESTful API provided by the second system, wherein the second system is configured to execute the HTTP request against the database;
receiving, by the ESB component, from the second system, a first set of results generated by executing the HTTP request against the database;
generating, by the ESB component, a set of query results for the database query by processing the received first set of results, comprising executing, based on the mapping, the data processing operation on the first set of results received from the second system; and
sending, to the first system, the set of query results.
14. The computer program product of claim 13 , wherein the database query is received from a database driver that intercepts SQL queries on the first system and reroutes them, and wherein the set of query results comprises one or more JSON objects.
15. The computer program product of claim 13 , wherein generating the execution sequence further comprises assigning a respective in queue and a respective out queue for each of the first data processing operation and the first HTTP request, and defining data transmission paths between the plurality of in queues and out queues.
16. The computer program product of claim 13 , the operation further comprising converting the database query from a first database schema associated with a legacy database to a second database schema associated with the database of the second system, wherein the first and second database schemas are different.