SQL services
A SQL service is a special type of service capable of executing SQL statements. This is the type of service you should use if you want to perform SQL statements against a database via Gloop.
Creating a SQL service
To create a SQL service, follow the steps below:
- Launch the SQL service wizard by right-clicking on the target package's
code
directory (or any of the code directories underneath it) where you'd like to store the SQL service, and then select New > SQL Service. -
In the wizard, specify the following details:
- Location - where the SQL service will reside; its default value will be the folder where you started the wizard from
- Name - the name of the SQL service
- Connection Name - the name of the database that the service will connect to while developing the service, and by default when executing it
- Query Type - the type of SQL statement the service will perform
-
Click Finish.
Launch the wizard using keyboard shortcuts only
In Martini, you can open the SQL service wizard by pressing (or in Martini Online) and
typing sql
in the dialog's search box. After that, press and the wizard will appear.
Editing SQL services
After creating the SQL service, Martini will automatically open the service for you, and you will be shown the SQL service editor.
Here's a breakdown of its primary components:
-
Connection Name drop-down
The name of the database connection Martini will connect to when being executed. By clicking on the drop-down list, you will be able to choose the database you want to execute queries against. Only registered databases will be selectable. You can change the target database at runtime by setting the
$martiniConnectionPool
input property to the name of another database. -
Type drop-down
The specific type of operation that the service will execute. Currently, these are the supported types:
Type Description Select Single Used to get the first row of the result set from a SELECT
statement.Select Multi Returns an input cursor representing the result set rows from a SELECT
statement.Insert Used to insert one row into a table. Insert Batch Used to insert multiple rows into a table. These types of services return an output cursor. Update Used to update a single row. Update Batch Used to update multiple rows in a table. These types of services return an output cursor. Delete Used to delete a single row. Delete Batch Used to delete multiple rows from a table. These types of services return an output cursor. -
Database tree
Displays the target database's schemas, functions, and procedures. You can use this to navigate around the database.
Export a table to a data model
You can create a data model from a database table by right clicking the latter in the Database tree, and then choosing Export to data model from the appearing context menu.
-
Statement text area
The SQL statement based on the SQL operation you selected. You can edit the service's SQL directly using this component.
Format your SQL query
You can format your query by right clicking on the statement text area and selecting Format, or by using the shortcut .
Export a SQL service to a SQL query
You can create a SQL query from a SQL service file by right clicking the latter from the Navigator, and then choosing Export > SQL Query from the appearing context menu.
Inputs and outputs
Like regular services, SQL services have inputs and outputs, too. As the SQL statement changes, the SQL service's inputs and outputs will change as well.
If the SQL statement is incorrect or the output model could not be generated, then a warning will appear underneath the SQL editor. When you hover your mouse pointer over the warning, a tooltip will show up which will provide you with more information on what is wrong with the statement.
SQL services also have special input and output properties you can set or fetch:
Property | Type | Applicable Operations | Description |
---|---|---|---|
$martiniConnectionPool |
Input | All operations | The database name. |
$generatedKeys |
Output | Insert | The ID(s) of the record(s) that were inserted. |
$parentJDBCBatchCursor |
Output | All batch operations | Any parent table that needs to have it's batch executed prior to any child batches (to avoid foreign key issues). If you have left this empty, the batch operation will be executed without executing the parent batch first. |
$updateCount |
Output | All single operations | The total number of records that were updated/deleted as a result of the operation. |
$batchSize |
Output | All batch operations | The size the batch will reach before sending the updated data to the database |
Parameterized queries
Parameterized SQL queries are also supported in Gloop. Parameters allow for flexible SQL commands - arguments don't need to be hard-coded in the SQL statement, you can specify their values by setting the parameters. This functionality is the same as a Java prepared statement.
However, instead of using a question mark in the statement, simply replace the substitutable value in the
command with a placeholder in this format :<name-of-parameter>
. After you have successfully re-structured the SQL
statement, the SQL service will have additional input properties that have the same name as the SQL
parameters. Below is an example:
1 2 3 4 5 6 7 8 |
|
To allow for Gloop to use input properties for this statement, change this to:
1 2 3 4 5 6 7 8 |
|
This SQL statement will allow us to fetch rows from the EMPLOYEES
table provided that the row's FIRSTNAME
column
is equal to the service's firstName
property when executed, and the row's LASTNAME
column is equal to the
service's lastName
property. As you modify the SQL and add named parameters, the Input/Output view will change as
shown below:
You can also use Martini to help write the SQL statements for you. Right click on the table or columns you would like to include in the statement and choose the type of SQL statement you would like to execute from the appearing context menu. The items in the context menu will depend on the selected value for the Type dropdown. In the example below, Update was selected for Type, hence selectable statements are UPDATE statements only.
Generate statements by dragging and dropping table or column nodes to the editor
You can drag and drop tables or columns displayed on the Database node in the Navigator view to
the editor. Doing this will prompt Martini to generate a SQL statement based on the selected statement type and
provided tables or columns. By default, an AND
statement will be generated. If you want to generate use OR
instead, hold while dragging and dropping.
Transactions
Martini includes services that can be used to wrap SQL services in
JDBC transactions like regular Java JDBC code. These services exist in the
io.toro.martini.SqlMethods
class from the core
package.
Example services, meanwhile, exist in the included examples
package, in the
databaseMigration
code directory. Similar to Java code:
- A transaction
start
is executed - A
try
block is executed, manipulating data in the database (and optionally, JMS destinations) - If everything worked, a
commit
is performed (generally the last line in thetry
block) - If anything went wrong, a
rollback
is executed in acatch
block
In the corresponding service below, you can see that the code is very similar:
- A transaction is started on line
3
(before thetry
block) - A commit is executed on line
15
(at the end of thetry
block) if everything was executed without error - A rollback in the corresponding
catch
block on line17
is executed if anything went wrong
XA transactions
If configured correctly, Martini will also handle XA transactions.
Exporting a schema table to a data model
You can create data models from existing schema tables through the SQL service editor. In order to do that, follow the steps below:
- Open a SQL service of your choice.
- Under the Database tree, expand the Schemas node.
- Expand a schema of your choice.
- Expand Tables. This will show you a list of schema tables.
- Right click the schema table of your choice and select Export to Gloop Model.
- In the dialog that appears, specify the location and name of your model.
The Location and Name fields are pre-populated by default,
and set to the
code
directory and the name of the schema table respectively. - Click Finish.