Using IntelliJ Plugin for Development

Browse tables and execute queries on your Oracle NoSQL Database Cloud Service instance or simulator from IntelliJ.

The Oracle NoSQL Database Cloud Service IntelliJ plugin connects to a running instance of Oracle NoSQL Database Cloud Service or simulator and allows you to:
  • View the tables in a well-defined tree structure with Table Explorer.
  • View information on columns, indexes, primary key(s), and shard key(s) for a table.
  • View column data in a well-formatted JSON Structure.
  • Create tables using form-based schema entry or supply DDL statements.
  • Drop tables.
  • Add new columns using form-based entry or supply DDL statements.
  • Drop Columns.
  • Create Indexes.
  • Drop Indexes.
  • Execute SELECT SQL queries on a table and view query results in tabular format.
  • Execute DML statements to update, insert, and delete data from a table.

This article has the following topics:

Setting Up IntelliJ Plug-in

Learn how to set up the IntelliJ plug-in for Oracle NoSQL Database Cloud Service instance or simulator.

As a prerequisite, you must first install IntelliJ IDEA. You can download IntelliJ IDEA from JetBrains.

You can install the plugin using one of the following options:
  1. Option 1: Search the Oracle NoSQL Database Cloud Service Connector in the JetBrains plug-in repository, and install it.
  2. Option 2: Install from Oracle Technology Network.

    Note:

    The most up-to-date version can be downloaded from Oracle Technology Network.
    1. Download the IntelliJ plugin from Oracle Technology Network. Do not extract the files.
    2. Launch the IntelliJ IDEA, navigate to File > Settings > Plugins. You can also access settings by selecting the IDE and Project Settings icon project-settings-icon located in the upper right corner of the main menu bar.
    3. Select the Manage Repositories, Configure Proxy or Install Plugin from Disk icon on the upper-right and then select Install Plugin from disk.
    4. Select the downloaded plugin zip file.

Restart the IDE. You will see the Schema Explorer icon on the right panel.

After you successfully set up your IntelliJ plugin, create a NoSQL project, and connect it to your Oracle NoSQL Database Cloud Service instance or simulator.

Creating a NoSQL Project in IntelliJ

Learn how to create a NoSQL project in IntelliJ.

Perform the following steps:
  1. Open IntelliJ IDEA. Click File > New > Project.
  2. Enter a value for Project Name and Project Location. Select Create.
  3. Select a build system and JDK path.
  4. IntelliJ creates your NoSQL project directory, which includes a sample Java file. If you have selected Maven as the build system, your project directory also includes a pom.xml file.
  5. Make sure Notifications are enabled for your Oracle NoSQL project. To enable Notifications, press Alt+\ to open Main Menu. Click View, expand Tool Windows > Notifications. A Notification Icon notification-bell-icon appears on the right tool window bar.
After you successfully created a NoSQL project in IntelliJ, you can connect your project to Oracle NoSQL Database Cloud Service or simulator.

Connecting to Oracle NoSQL Database Cloud Service from IntelliJ

Learn how to connect your NoSQL project to Oracle NoSQL Database Cloud Service using the IntelliJ plugin

Perform the following steps:
  1. Open your NoSQL project in IntelliJ.
  2. Click the task icon task-icon in the Schema Explorer window to open the Settings dialog for the plugin.
  3. Expand Tools > Oracle NoSQL in the Settings Explorer, and click Connections.
  4. Select Cloud from the drop-down menu for the Profile type. You can view all the existing connections for the Cloud profile type under the Connections dropdown.
  5. Click Add Connection. You have two options to create a connection.

    1. Using configuration file: You specify the configuration file path, which points to the location of the OCI configuration file stored on your local system. This file contains the essential parameters required to connect to Oracle NoSQL Database Cloud Service such as region, tenant ID, user ID, fingerprint, passphrase, and private key path.

      Enter the required values for the following connection parameters and select ADD. Then select OK.

      Table - Connection Parameters Configuration File Option

      Parameter Description Sample Value
      Connection Name A unique name, that is given to a specific connection specification is mandatory from the plugin version 1.5.1. Updating the Connection Name field is recommended after upgrading the plugin from version 1.4.0 or lower.

      Note:

      You can add multiple connections and the stored connection specifications are persistent.
      		ndcs_con1
      Configuration File Path of a directory where the configuration file is stored in the local system. For more information about the configuration file and its contents, see User Principals method in Authentication to connect to Oracle NoSQL Database. ~/.oci/config
      Profile Name of the profile.

      Note:

      You can create multiple profiles with different values for these entries, and then you can specify which profile to load.
      		DEFAULT
      Compartment (Optional) The compartment OCID/compartment name for your NoSQL database schema.

      Note:

      When you specify the compartment name, you need to specify the entire hierarchy with a colon separating each entry.
      		developers:dev1.

      Here dev1 is a compartment under the compartment developers.

      Note:

      If no value is specified, it defaults to the Root compartment.

      The plugin connects to the region specified in the configuration file.

      If you use Session token-based authentication, your configuration file includes the designated profile for session-token along with the token path. To connect to Oracle NoSQL Database Cloud Service, you must select the configuration file option and specify the appropriate profile.

    2. Advanced: You directly specify the connection parameters required to connect to Oracle NoSQL Database Cloud Service. Enter the required values for the following parameters and select ADD. Then select OK.

    Table - Connection Parameters Advanced Option

    Parameter Description Sample Value
    Connection Name A unique name, that is given to a specific connection specification is mandatory from the plugin version 1.5.1. Updating the Connection Name field is recommended after upgrading the plugin from version 1.4.0 or lower.

    Note:

    You can add multiple connections and the stored connection specifications are persistent.
    		ndcs_con1
    Endpoint Regional network access point to the Oracle NoSQL Database Cloud Service. https://nosql.us-ashburn-1.oci.oraclecloud.com (for the Ashburn Oracle NoSQL Database Cloud Service region identifier in the North America region. See Data Regions and Associated Service Endpoints for a list of service endpoints.
    Tenant ID and User ID Tenancy's OCID and User's OCID for your Oracle NoSQL Database Cloud Service. See Where to get the Tenancy's OCID and User's OCID in Oracle Cloud Infrastructure Documentation.
    Fingerprint and Passphrase(Optional) The fingerprint and passphrase of the signing key created while generating and uploading the API Signing Key.
    See the following resources in Oracle Cloud Infrastructure Documentation:
    Private Key The private key generated for the user. For the application user, an API signing key must be generated and uploaded. See How to Generate an API Signing Key to generate the signing key with an optional passphrase.
    Compartment (Optional) The compartment OCID/compartment name for your NoSQL database schema.

    Note:

    When you specify the compartment name, you need to specify the entire hierarchy with a colon separating each entry.
    		developers:dev1.

    Here dev1 is a compartment under the compartment developers.

    Note:

    If no value is specified, it defaults to the Root compartment.

    Note:

    1. Starting with version 1.5.4, the IntelliJ plugin automatically downloads the latest Oracle NoSQL Java SDK and sets the SDK path when creating a connection.
    2. If you are updating the plugin from version 1.4.0 or lower, all the stored connections migrate to the new version. In this case, the Connection Name will be the same as Endpoint. Follow the below step to change the Connection Name.
  6. The IntelliJ Plugin saves the connection details in the connection name specified. To modify the connection details, choose the connection name in the drop-down for Connections. Click Modify Connection. You can change any of the connection parameters (mentioned above) and click OK to save the settings. To remove a connection name from the plugin, choose the connection name and click Delete Connection. Once you confirm the action to delete, the connection name is removed from the plugin.
  7. Click the Web icon in the Schema Explorer. The list of existing connections is displayed in the drop-down box. The connection name will be displayed in the NoSQL tool window in the following format:

    Table - Connection Display

    Option used to create a connection Display in the NoSQL tool window
    Advanced Connection Name:Endpoint:Compartment Name/OCID (if other than root)
    Use Configuration File Connection Name:Configuration file path:Profile:Compartment Name/OCID(if other than root)

    Choose the connection and click OK. The IntelliJ plugin connects your project to Oracle NoSQL Database Cloud Service and displays its schema in the Schema Explorer window.

After you successfully connect your project to your Oracle NoSQL Database Cloud Service, you can manage the tables and data in your schema.

Connecting to Oracle NoSQL Database Cloud Simulator from IntelliJ

Learn how to connect your NoSQL project to Oracle NoSQL Database Cloud Simulator using the IntelliJ plugin.

Perform the following steps:
  1. Download and start Oracle NoSQL Database Cloud Simulator. See Downloading the Oracle NoSQL Database Cloud Simulator .
  2. Open your NoSQL project in IntelliJ.
  3. Click the task icon task-icon in the Schema Explorer window to open the Settings dialog for the plugin.
  4. Expand Tools > Oracle NoSQL in the Settings Explorer, and click Connections. You can view all the existing connections for the Cloudsim profile type under the Connections dropdown.
  5. Select Cloudsim from the drop-down menu for the Profile type.
  6. Click Add Connection. Enter values for the following connection parameters, and click OK.

    Table - Connection Parameters

    Parameter Description Sample Value
    Connection Name A unique name, that is given to a specific connection specification is mandatory from plugin version 1.5.1. Updating the Connection Name field is recommended after upgrading the plugin from version 1.4.0 or lower.

    Note:

    You can add multiple connections and the stored connection specifications are persistent.
    		nosql_sim1
    Service URL The IP address and port on which the Oracle NoSQL Database Cloud Simulator is running. The default value is http://localhost:8080
    Tenant Identifier Unique identifier to identify the tenant. The default value is exampleId. Retain this value if you want to test the examples.

    Note:

    1. Starting with version 1.5.4, the IntelliJ plugin automatically downloads the latest NoSQL Java SDK and sets the SDK path when creating a connection.
    2. If you are updating the plugin from version 1.4.0 or lower, all the stored connections migrate to the new version. In this case, the Connection Name will be the same as Endpoint. Follow the below step to change the Connection Name.
  7. The IntelliJ Plugin saves the connection details in the connection name specified. To modify the connection details, choose the connection name in the drop-down for Connections. Click Modify Connection. You can change any of the connection parameters (mentioned above) and click OK to save the settings. To remove a connection name from the plugin, choose the connection name and click Delete Connection. Once you confirm the action to delete, the connection name is removed from the plugin.
  8. Click the Web icon in the Schema Explorer. The list of existing connections is displayed in the drop-down box. The connection name will be displayed in the NoSQL tool window in the following format: 
    Connection Name:service Url : Tenant Identifier

    Choose the connection and click OK. The IntelliJ plugin connects your project to the Oracle NoSQL Database Cloud Simulator and displays its schema in the Schema Explorer window.

    Note:

    Before connecting your project to Oracle NoSQL Database Cloud Simulator, it must be started and running. Otherwise, your connection request will fail in IntelliJ.
After you successfully connect your project to your Oracle NoSQL Database Cloud Simulator, you can manage the tables and data in your schema.

Managing Tables Using the IntelliJ Plugin

Learn how to create tables and view table data in Oracle NoSQL Database Cloud Service or Oracle NoSQL Database Cloud Simulator from IntelliJ.

After connecting to the Oracle NoSQL Database Cloud Simulator or Oracle NoSQL Database Cloud Service, you can can download the Oracle NoSQL Database Java SDK and execute the examples to create a sample table. With the help of the IntelliJ Plugin, you can view the tables and their data in the Schema Explorer window.
Perform the following steps to execute a program:
  1. Download the latest Oracle NoSQL Database Java SDK from GitHub.
  2. Extract (unzip) the downloaded file in a local repository.
  3. Launch IntelliJ IDEA and open the NoSQL project connected to your Oracle NoSQL Database Cloud Service or simulator.
  4. From the Project Explorer window, locate the local repository and open the BasicTableExample script. You will find this in the examples folder under oracle-nosql-java-sdk. This program creates a table called audienceData, adds two rows into this table, queries the inserted rows, deletes the inserted rows, and finally drops the audienceData table.
  5. To pass the required arguments, click Run > Edit Configurations. Depending on the connection type, enter the following program arguments, and click OK.

    Table - Program Arguments

    Connection Type Program Arguments More Information
    Cloudsim http://localhost:8080 If you started the Oracle NoSQL Database Cloud Simulator on a different port, you must replace 8080 with that port number.
    Cloud us-ashburn-1 -configFile D:\OCI_PROP\config The first argument indicates the data region of your Oracle NoSQL Database Cloud Service. The second argument passes a configuration file that contains the credentials to connect to the Oracle NoSQL Database Cloud Service. For more information about the configuration file and its contents, see User Principals method in Authentication to connect to Oracle NoSQL Database.
  6. To execute this program, click Run > Run 'BasicExampleTable' or press Shift + 10.
  7. Verify the logs in the terminal to confirm that the code executed successfully. You can see the display messages that indicate table creation, rows insertion, and so on.

    Tip:

    As the BasicExampleTable deletes the inserted rows and drops the audienceData table, you can't view this table in the Schema Explorer. If you want to see the table in the Schema Explorer, comment the code that deletes the inserted rows and drops the table, and rerun the program.
  8. To view the tables and their data:
    1. Locate the Schema Explorer, and click the Refresh icon icon to reload the schema.
    2. Locate the audienceData table under your tenant identifier, and expand it to view its columns, primary key, and shard key details.
    3. Double-click the table name to view its data. Alternatively, you can right-click the table and select Browse Table.
    4. A record viewer window appears in the main editor. Click Execute to run the query and display table data.
    5. To view individual cell data separately, double-click the cell.

Perform DDL operations using IntelliJ

You can use IntelliJ to perform DDL operations.

Some of the DDL operations that can be performed from inside the IntelliJ plugin are

CREATE TABLE

  • Locate the Schema Explorer, and click the Refresh icon to reload the schema.
  • Right click the connection name and choose Create Table.
  • In the prompt, enter the details for your new table. You can create the Oracle NoSQL Database table in two modes:
    • **Simple DDL Input** : You can use this mode to create the table declaratively, that is, without writing a DDL statement.
    • **Advanced DDL Input** : You can use this mode to create the table using a DDL statement.
  • You have the option to view the DDL statement before creating. Click Show DDL to view the DDL statement formed based on the values entered in the fields in the Simple DDL input mode. This DDL statement gets executed when you click Create.
  • Click Create to create the table.
  • To create a child table, right click on the desired table and choose Create Child Table. You can create a child table in two modes:
    • **Simple DDL Input** : You can use this mode to create a child table by simply entering a table name along with other required details.
    • **Advanced DDL Input** : You can use this mode to create a child table using a DDL statement.

    For more details on child tables, see Table Hierarchies in Oracle NoSQL Database Cloud Service Guide.

  • Click Create to create a child table.
  • You have an option to the view the DDL statement after creating a table. Right click on the existing table. Choose View Table DDL. To copy the DDL statement, click Copy to Clipboard. Click OK to close the dialog box.

DROP TABLE

  • Locate the Schema Explorer, and click the Refresh icon to reload the schema.
  • Right click on the table that you want to drop. Choose Drop Table.
  • A confirmation window appears, click Ok to confirm the drop action.

CREATE INDEX

  • Locate the Schema Explorer, and click the Refresh icon to reload the schema.
  • Right click on the table where index need to be created. Choose Create Index.
  • In the Create Index panel, you have an option to create index in two modes:
    • **Form Based Index Creation(Simple DDL Input)** : Enter the details for creating an index without writing any DDL statement. Specify the name of the index and the columns to be part of the index. If the column is of JSON data type, you see an additional field called "JSON Path to Index Field" appear. Enter the path to the location of the JSON field, and choose the data type for it.
    • **Create Index as DDL Statement (For Advanced DDL input)** : Enter a valid DDL statement to create an index. It can also include complex data type i.e. array, map, and record.
  • Click Add Index.

DROP INDEX

  • Locate the Schema Explorer, and click the Refresh icon to reload the schema.
  • Click on the target table to see the listed columns, Primary Keys, Indexes and Shard Keys.
  • Locate the target-index which has to be dropped and right-click on it. Click Drop Index.
  • A confirmation window appears, click Ok to confirm the drop action.

ADD COLUMN

  • Locate the Schema Explorer, and click the Refresh icon to reload the schema.
  • Right click on the table where column needs to be added. Choose Add Column.
  • You can add new COLUMNs in two modes:
    • Simple DDL Input : You can use this mode to add new columns without writing a DDL statement. In case of binary or fixed binary select the data type as Binary. For fixed binary, enter the size of the file in the Size field and keep the field null in case of binary data type.
    • Advanced DDL Input : You can use this mode to add new columns into the table by supplying a valid DDL statement. This mode can also create columns of complex data type. For example, array, map, or record and also in nested format.
  • In both the modes, specify the name of the column and define the column with its properties - datatype, default value and whether it is nullable.
  • Click Add Column.

DROP COLUMN

  • Locate the Schema Explorer, and click the Refresh icon to reload the schema.
  • Click on the target table to see the listed columns, Primary Keys, Indexes and Shard Keys.
  • Locate the target-column which has to be dropped and right-click on it. Click Drop Column.
  • A confirmation window appears, click Ok to confirm the drop action.

Freeze/UnFreeze Schema

You need to freeze the schema of a singleton table before making it a Global Active table. Once you freeze the schema of the table, you cannot make any changes to the schema. To freeze the schema of a singleton table, it should have at least one JSON column. Right-click on the table and choose Freeze/Unfreeze. Once you confirm, the schema of the table gets frozen. Similarly, to unfreeze the schema of the table, right-click on the table and choose Freeze/Unfreeze. Once you confirm, the schema of the table is changed back to mutable which means the schema can be altered.

Note:

The table regional replicas need to be dropped (the table must be a singleton table) before the unfreeze operation can be performed.

Manage Replicas

See Regional Table Replicas to understand what are replicas and how to convert a singleton table to a Global active table by adding regional replicas.

Add Replica

You can add a regional replica to a singleton table, to make it a Global active table or add a replica to an existing Global active table. The table should be frozen before you add a replica to it. Right-click on the table and choose Add Replica from Regional Replicas. You can choose a replica from the dropdown of the Replication region. You can decide on the Read Units and Write Units of the table in that replication region. The Disk Storage value for the table cannot be changed/edited in the replica. Click Add Replica. The table gets replicated in the region.

View Replica

Right-click on the table and choose View Replicas from Regional Replicas. You can view the list of replicas for the table.

Drop Replicas

Right-click on the table and choose Drop Replicas from Regional Replicas. Click Add and choose a replica to be removed. You can choose more than one replica to be dropped at a single time. Click Remove if you want to remove the replica from the list of replicas that need to be dropped. Click Drop Replicas. Once you confirm, the table is dropped from all the selected replicas.

Edit Reserved Capacity

You can edit the reserved capacity and the usage model of a table. Right-click on the table and choose Edit Reserved Capacity. You can choose one of the two capacity modes - Provisioned capacity or On-demand capacity. Edit the values and click Apply Changes.

If the table that is edited is a Global Active table:
  • Change in storage capacity has a global scope (change in one regional table replica is automatically propagated to all regional table replicas).
  • Change in read units, write units or change in capacity mode from On-Demand to provisioned or vice-versa has a local scope (change only in the regional table replica where it is initiated).

Perform DML operations using IntelliJ

You can add data, modify existing data and query data from tables using IntelliJ plugin.

Insert data

  • Locate the Schema Explorer, and click the Refresh icon to reload the schema.
  • Right click on the table where a row needs to be inserted. Choose Insert Row.
  • In the Insert Row panel, enter the details for inserting a new row. You can INSERT a new ROW in two modes:
    • Simple Input : You can use this mode to insert the new row without writing a DML statement. Here a form based row fields entry is loaded, where you can enter the value of every field in the row.
      • For binary data type, the string typed in should be a valid Base64 encoding of a binary value or select the file to upload in the desired column.
      • For fixed binary data type, the string typed in should be a valid Base64 encoding of a binary value or upload the file of size defined during the creation of the particular column.

      Note:

      The file format you upload for binary data type should only have .bin extension.
    • Advanced JSON Input : You can use this mode to insert a new row into the table by supplying a JSON Object containing the column name and its corresponding value as key-value pairs. The input can also be of complex data type i.e. array, map, record.
  • Click Insert Row.

Modify Data - UPDATE ROW/DELETE ROW

  • Locate the Schema Explorer, and click the Refresh icon to reload the schema.
  • Right click on the table where a row needs to be inserted. Choose Browse Table.
  • In the textbox on the left, enter the SQL statement to fetch data from your table. Click Execute to run the statement.
  • To view individual cell data separately, click the table cell.
  • To perform DML operations like Update and Delete Row, right-click on the particular row. Pick your option from the context-menu that appears.
    • Delete Row : A confirmation window appears, click Ok to delete the row.

    • Update Row : A separate HTML window opens displaying the column names and its corresponding values. You can enter new values for row data in two modes: form-based row fields entry (Simple DDL input) and Supply row contents as a JSON object (Advanced DDL input). In the advanced DDL input mode, JSON data is presented as a tree structure to simplify viewing and updating.

      Select Execute to refresh and view the updated data.

      Note:

      In any row, PRIMARY KEY and GENERATED ALWAYS AS IDENTITY columns cannot be updated.

Query tables

  • Locate the Schema Explorer, and click the Refresh icon to reload the schema.
  • Right click on the table and choose Browse Table.
  • In the textbox on the left, enter the SELECT statement to fetch data from your table. When you start typing the query, you are prompted with the list of possible words to auto-complete the SQL statement. All SQL keywords and column names for the given table are provided in the prompt to auto complete the SQL statement.
  • The SQL syntax is highlighted in every query which provides a better SQL writing experience.
  • You can format the query to improve its readability. Select your query, right click and select Prettify. The query is formatted and lines are wrapped to enhance readability.
  • Click Execute to run the query. The corresponding data is retrieved from the table. When you double click the data retrieved, the column data is opened in a new window. Any JSON data is displayed in a tree structure in the new window. Click ‘+’ to expand or ‘-’ to collapse the tree structure. Click Ctrl ^F in the new window to enable the search option, which allows you to search for any value in the JSON tree. Click the up or down arrow to move to the previous or next search occurrence, respectively.
  • Right click on any row and click Download JSON. In the dialog box, navigate to the location where you want to save the file and click Save. Once the file is downloaded, a notification appears at the bottom-right of the screen. Click the link to open the downloaded file. The file opens in the browser.
    • In case of Binary data type, simply click Download Binary Object in the output.
  • Click Download Query Result, to download all the data in the query result. In the dialog box, navigate to the location where you want to save the file and click Save. In case of multiple rows, a progress bar appears on the bottom-right of the screen to showing the number of rows downloaded in real time. Once the file is downloaded, a notification appears at the bottom-right of the screen. Click the link to open the downloaded file. The file opens in the browser.
  • Click Show Query Plan to view the execution plan of the query.
  • Click the Previous Commands drop-down, to view the top 20 recently executed SQL statements that had provided an output.

    Note:

    The drop-down will only show SQL statements related to the table you are working on.

Schema Explorer

  • In the Schema Explorer window, you can verify the full data type of a particular column. Locate the particular column and the data type is followed by the column name.