IntelliJ IDEA 2018.1 Help

Working with the data editor

The data editor provides a GUI for working with table data. You can sort, filter, add, edit, and remove the data from tables. When you submit changes to the database, the Database Console tool window shows the particular queries that were executed.

The data editor

Opening a table in the data editor

In the Database tool window, do one of the following:

  • Double-click the table of interest.
  • Click the table and click DataTables on the toolbar (if the toolbar is not currently hidden).
  • Select the table and press F4.
  • Right-click the table and select Open Editor from the context menu.

Protecting a table from accidental modifications

To protect a table from accidental modifications in the data editor, you can make it read-only. To do that, click the padlock icon in the lower-right corner of IntelliJ IDEA workspace.

setReadOnlyStatus

As a result, the icon appearance will change to readOnlyStatusOn, a padlock will appear on the corresponding editor tab, and you won't be able to make changes to the table.

readOnlyStatusOnForTable

To turn off the table's read-only status, click the padlock icon again.

Note that the tables with the read-only status in the data editor can still be modified when using the database consoles or in the Database tool window.

Switching between subsets of rows

If only a subset of all the rows is currently shown, to switch between the subsets, use:

  • dataSourceTableEditorFirstPage  First Page
  • dataSourceTableEditorPreviousPage  Previous Page ( Ctrl+Alt+Up )
  • dataSourceTableEditorNextPage  Next Page ( Ctrl+Alt+Down )
  • dataSourceTableEditorLastPage  Last Page

See also,Making all rows visible simultaneously.

Making all rows visible simultaneously

If you want all the rows to be shown simultaneously:

  1. Click IconDBToolbarMenu on the toolbar and select Settings.
  2. Switch to the Database | Data Views page, specify 0 in the Result set page size field, and click OK.
  3. Click refresh or press Ctrl+F5 to refresh the table view.

See also, Updating the table view and Result set page size.

Navigating to a specified row

To switch to a row with a specified number:

  1. Do one of the following:
    • PressCtrl+G.
    • Right-click the table and select Go To | Row from the context menu.
    • Select Navigate | Row from the main menu.
  2. In the dialog that opens, specify the row number and click OK.

Navigating to related records

If a row references a record in a different table or is referenced in a different table, you can switch to the corresponding table to see the related record or records.

To switch to a referenced row:

  1. Do one of the following:
    • Press Ctrl+B.
    • Select Go To | Referenced Data from the context menu.
  2. If more than one record is referenced, select the target record in the pop-up that appears.

To switch to a row that references the current one, or to see all the rows that reference the current one:

  1. Do one of the following:
    • PressAlt+F7.
    • Select Go To | Referencing Data from the context menu.
  2. Select the target in one of the following categories:
    • First Referencing Row. All the rows in the corresponding table will be shown and the first of the rows that references the current row will be selected.
    • All Referencing Rows. Only the rows that reference the current row will be shown.

The options described above can also be accessed by using one of the following:

  • F4.
  • Go To | Related Data in the context menu.
  • Navigate | Related Data in the main menu.

Sorting data

You can sort table data by any of the columns by clicking the cells in the header row.

Each cell in this row has a sorting marker in the right-hand part and, initially, a cell may look something like this:TableEditorHeaderCellUnsorted. The sorting marker in this case indicates that the data is not sorted by this column.

If you click the cell once, the data is sorted by the corresponding column in the ascending order. This is indicated by the sorting marker appearance:TableEditorHeaderCellSortAsc. The number to the right of the marker (1 on the picture) is the sorting level. (You can sort by more than one column. In such cases, different columns will have different sorting levels.)

When you click the cell for the second time, the data is sorted in the descending order. Here is how the sorting marker indicates this order:TableEditorHeaderCellSortDesc.

Finally, when you click the cell for the third time, the initial state is resorted. That is, sorting by the corresponding column is canceled:TableEditorHeaderCellUnsorted.

You can turn on the Sort via ORDER BY option, to enable sorting the data by the corresponding DBMS.

See also, Restoring the initial table view and Using the Structure view to sort data, and hide and show columns.

Filtering data

  1. If the filter box is not currently shown, click IconDBToolbarMenu on the toolbar and select Row Filter.
  2. In the filter box, specify filtering conditions.

    The filtering conditions are specified as in a WHERE clause but without the word WHERE, e. g. name LIKE 'a%' AND notes LIKE '%metal%'. Within the LIKE expressions, the SQL wildcards can be used: the percent sign (%) for zero or more characters and underscore (_) for a single character.

    TableEditorFilteredTable

    To apply the conditions currently specified in the box, pressEnter. To cancel filtering, clickclear, or delete the contents of the filter box and pressEnter.

    To reapply a memorized filter, click find1 and select the filter in the list. See also, Filter history size.

    DBFilterHistorySize2

Using quick filtering options

In addition to specifying filtering conditions manually (see Filtering data), you can use quick filtering options.

Available as context menu commands, these options are a set of filtering conditions for the current column name. The conditions themselves depend on the value in the current cell.

To use a quick filtering option:

  1. Right-click a cell of interest and point to Filter by.
  2. Select the necessary condition from the list.

Reordering columns

To reorder columns, use drag-and-drop for the corresponding cells in the header row.

TableEditorReorderColumns

See also,Restoring the initial table view.

Hiding and showing columns

To hide a column, right-click the corresponding header cell and select Hide column.

To show a hidden column:

  1. Do one of the following:
    • Right-click any of the cells in the header row and select Column List.
    • PressCtrl+F12.

    In the list that appears, the names of hidden columns are shown struck through.

    TableResultPaneHideColumn
  2. Select (highlight) the column name of interest and pressSpace.
  3. Press Enter or Escape to close the list.

See also, Restoring the initial table view and Using the Structure view to sort data, and hide and show columns.

Restoring the initial table view

Click IconDBToolbarMenu on the toolbar and select Reset View to restore the initial table view after reordering or hiding the columns, or sorting the data. As a result, the data, generally, becomes unsorted, the columns appear in the order they are defined in the database, and all the columns are shown.

Using the Structure view to sort data, and hide and show columns

When working with a data editor, the table structure view is available in the Structure tool window or as the corresponding popup.

The structure view shows the list of all the columns and lets you sort the data as well as hide and show the columns.

To open the Structure tool window, do one of the following:

  • Select View | Tool Windows | Structure in the main menu.
  • Click Structure on the left-hand tool window bar.
  • PressAlt+7.

To open the structure popup, do one of the following:

  • Right-click a cell in the table header row and select Column List.
  • PressCtrl+F12.

In the tool window or the popup, select the column of interest and do one of the following:

  • To sort the data by this column in the ascending order, pressShift+Alt+Up. (In the tool window, you can, alternatively, select Sort | Ascending from the context menu.)
  • To sort the data in the descending order, pressShift+Alt+Down. (In the tool window, alternatively, Sort | Descending.)
  • To cancel sorting by this column, pressCtrl+Shift+Alt+Backspace. (In the tool window, alternatively, Sort | Unsorted.)
  • To hide the column (or show a hidden column), pressSpace. (The names of hidden columns are shown struck through. In the tool window, alternatively, the Hide Column or Show Column context menu command can be used.)
    TableResultPaneHideColumn

The shortcuts for sorting table data (Shift+Alt+Up, Shift+Alt+Down and Ctrl+Shift+Alt+Backspace) can be used in the data editor without opening the structure view.

See also, You can sort table data by any of the columns by clicking the cells in the header row. , Hiding and showing columns and Restoring the initial table view.

Using the quick documentation view

The quick documentation view provides details about the values in the selected cell or cells. For example, if a cell contains long text, normally, you can see only its beginning. The whole text is shown in the quick documentation view.

DBTableQuickDoc

If a cell contains an image, you can see that image in the quick documentation view.

DBTableQuickDocPicture

You can also see the records referenced in the current record as well as the records that reference the current one.

TableValueView

If necessary, you can switch to the transposed view. This is when the rows and columns are interchanged. Thus, for a row, the cells are shown one beneath the other.

TableTransposedRowView

To open the quick documentation view, press Ctrl+Q or select Quick Documentation from the View or the context menu.

To switch to the transposed view, click Transposed View. See also, Transposing the table.

To close the quick documentation view, pressEscape.

Transposing the table

The transposed table view is available. In this view, the rows and columns are interchanged.

To turn this view on or off, click IconDBToolbarMenu on the toolbar and select Transpose. Alternatively, use the Transpose context menu command.

Enabling coding assistance for a column

You can assign a column one of the supported languages (e.g. SQL, HTML or XML): right-click the corresponding header cell, select Edit As and select the language. As a result, you get coding assistance for the selected language in all the cells of the corresponding column.

You can also assign a language to an individual cell.

Selecting cells and ranges: using unobvious techniques

Adding cells with the same contents. Select a cell. Now, to add the nearest cell with the same contents to the selection, pressAlt+J. (When looking for the corresponding cell, IntelliJ IDEA moves down.) Each next press of Alt+J will add another cell to the selection.

To remove the cells from the selection one by one - starting from the last selected cell - useShift+Alt+J.

If a number of cells in the same row are initially selected, Alt+J and Shift+Alt+J work the same way.

Expanding a selection: cell - column - row - table. Select a cell. Now, to select all the cells in the current column, pressCtrl+W. The second press of Ctrl+W cancels the selection of the column and selects all the cells in the current row. Finally, the third press of Ctrl+W selects the whole table.

Ctrl+W works similarly if a number of cells or a range is initially selected.

Modifying cell contents

You can modify values in the table cells and, if appropriate, upload files.

  1. To start editing a value or uploading a file, do one of the following:
    • Double-click the corresponding table cell.
    • Right-click the cell and select Edit or Edit Maximized from the context menu.
    • Select the cell and press F2 or Shift+Enter. In the latter case, the cell will be maximized.
    • Select the cell and start typing. Note that in this case the initial cell contents are deleted right away and is replaced with the typed value.
  2. When in the editing mode, you can:
    • Modify the value right in the cell. To start a new line, useCtrl+Enter. To enter the value, pressEnter. To restore an initial value and quit the editing mode, pressEscape.
      DBEditValueInCell
    • Use value completion. Press Ctrl+Space to open the suggestion list. The list contains the values from the current column that match your input.
    • Maximize the cell if you need more room for editing. To do that, pressCtrl+Shift+Alt+M, or right-click the cell and select Maximize.

      When working in a maximized cell, use Enter to start a new line and Ctrl+Enter to enter the value. To restore an initial value and quit the editing mode, pressEscape.

      DBEditValueInBigCell
    • Upload a file into the field (e.g. to replace an existing file with a new one). To do that, right-click the cell and select Load File. Then select the necessary file in the dialog that opens.

      If a field can contain text, this function can be used to insert the contents of a text file into the field.

    • Replace the current value with the default one or null (if appropriate). To do that, right-click the cell and select Set DEFAULT or Set NULL.
    • Edit a value in the cell as a fragment in one of the supported languages (e.g. SQL, HTML or XML). To do that, right-click the cell, select Edit As and select the language. As a result, you get coding assistance for the language you have selected.
      DBEditValueInCellAsLang
  3. To complete the task, you may want to submit the changes. See Submitting and reverting changes.

Modifying values in a number of cells at once

You can modify a value in a number of cells at once:

  1. Select the range or ranges of interest.
  2. Start editing the value: select Edit from the context menu, press F2 or simply start typing. The changes are applied to all the selected cells only if those cells can contain the same value.
  3. To enter the value, pressEnter. To cancel editing, pressEscape.
  4. To complete the task, you may want to submit the changes. See Submitting and reverting changes.

Adding a row

If DBAddRow on the toolbar is enabled, you can add rows to the table.

  1. To start adding a row, do one of the following:
    • Click DBAddRow on the toolbar.
    • Right-click the table and select Add New Row from the context menu.
    • PressAlt+Insert.

    Note that the context menu Clone Row command (Ctrl+D) can be used as an alternative.

  2. Enter the values into the cells. For instructions, seeModifying cell contents.
  3. To save the new row, select Submit from the context menu or press Ctrl+Enter.

    See also, Submitting and reverting changes.

Deleting rows

If DBDeleteRows on the toolbar is enabled, you can delete rows. To do that:

  1. Select the row or rows that you want to delete.

    Rows are selected by clicking the cells in the column where the row numbers are shown. To select more than one row, use mouse clicks in combination with the Ctrl key.

  2. Do one of the following:
    • Click DBDeleteRows on the toolbar.
    • Press Ctrl+Y orDelete.
  3. Submit the changes to the server or confirm you intention to delete the selected row or rows.

    See also, Submitting and reverting changes.

Submitting and reverting changes

IntelliJ IDEA lets you specify how the changes that you make to data in a table are submitted to the database server. There is the Submit changes immediately option for that.

By default, this option is off. So the changes are accumulated in IntelliJ IDEA unless you carry out the Submit command ( iconDBSubmit on the toolbar, Submit in the context menu or Ctrl+Enter). Before you submit the changes, you can revert them (Revert in the context menu or Ctrl+Z).

The changes for a table are submitted all at once.

The scope of the Revert command is defined by the current selection in a table: the command is applied only to the changes within the selection. So you can revert an individual change, a group of changes or all the changes.

If nothing is currently selected, the Revert command is applied to the whole table.

To revert the changes, if the manual commit mode is selected, you can also use iconDBRollbackTransaction or the Rollback command.

Unsubmitted changes are highlighted. New rows are green, cells with changed values are blue, and the rows that are going to be deleted are gray.

If the Submit changes immediately option is on, the changes are submitted right-away, and, generally, you don't need to use the Submit command.

Managing database transactions

You can select to commit transactions automatically or manually. To change the commit mode, use the Tx switch buttonTransactionControlDB on the toolbar.

TransactionControlDB

If the commit mode is set to Auto, each change of a value, or adding or deleting a row - when submitted to the database server - is implicitly committed and cannot be rolled back.

If the commit mode is set to Manual, the changes you have submitted to the server can be explicitly committed or rolled back by means of iconDBCommitTransaction or iconDBRollbackTransaction on the toolbar, or the Commit or the Rollback context menu command.

The Tx switch can also be used for selecting the isolation level for the transactions.

Comparing tables

You can compare the current table with any other table which is open in a data editor or shown in the Database Console tool window. To do that, click CompareWithIcon on the toolbar and select the table of interest.

The comparison results are shown in the differences viewer.

To compare contents of two or three cells within one table, select them and press Ctrl+Shift+D or select Compare Cells from the context menu.

DBCCellDiff

Copying table data to the clipboard or saving them in a file

When copying table data to the clipboard or saving them in a file, the data are converted into one of the available output formats. This can be SQL INSERT or UPDATE statements, TSV or CSV, an HTML table or JSON data. See Specifying data output format and options.

To copy or save the data, use:

  • Copy (available in the Edit and the context menu, the keyboard equivalent isCtrl+C). This command copies the data for the selected cells onto the clipboard.
  • Dump Data | To Clipboard (available in the context menu and can also be accessed by means of downloadArtifacts on the toolbar). This command copies the data for the whole table onto the clipboard.
  • Dump Data | To File (available in the context menu and can also be accessed by means of downloadArtifacts on the toolbar). This command saves the data for the whole table in a file. Before actually saving the data, the dialog is shown which lets you select the output format and see how your data will look in a file.

Copying and pasting data: data types are converted if necessary

You can copy (Ctrl+C) and paste (Ctrl+V) selected cells and ranges of cells - within the same table or from one table to another one. When pasting, IntelliJ IDEA converts data types automatically if and as necessary.

Specifying data output format and options

To specify the output format and options for the Copy and Dump Data commands (see Copying table data to the clipboard or saving them in a file ), do one of the following:

  • Click DataExtractorButton on the toolbar.
  • Right-click the table and point to Data Extractor: <current_format>.

In the menu that opens, the output formats are in the upper part: SQL Inserts, SQL Updates, etc. (The options that look like file names are also the output formats or, to be more exact, the scripts that implement corresponding data converters.)

The output option are:

  • Allow Transposition. This option affects only delimiter-separated values formats (TSV, CSV). If the table is shown transposed and you are copying selected cells or rows to the clipboard (e.g.Ctrl+C), the selection is copied transposed (as shown) if the option is on and non-transposed (as in the original table) otherwise.
  • Skip Generated Columns (SQL). This is the option for SQL INSERTs and UPDATEs. When on, auto-increment fields are not included.
  • Add Table Definition (SQL). This is also the option for SQL INSERTs and UPDATEs. When on, the table definition (CREATE TABLE) is added.

Additionally:

  • Configure CSV Formats. This command opens the CSV Formats dialog that lets you manage your delimiter-separated values formats (e.g. CSV, TSV).
  • Go to Scripts Directory. This command lets you switch to the directory where the scripts that convert table data into various output formats are stored.

Exporting the data to another table, schema or database

You can export the data to another table, schema or database:

  1. Do one of the following:
    • Click iconExportToDB on the toolbar.
    • Select Export to Database from the context menu.
  2. Select the target schema (a new table will be created) or table (the data will be added to the selected table).
  3. In the dialog that opens, specify the data mapping info and the settings for the target table.

Saving a LOB in a file

If a cell contains a binary large object (a.k.a. BLOB or LOB), you can save such a LOB in a file.

  1. Right-click the cell that contains the LOB of interest and select Save LOB To File.
  2. In the dialog that opens, specify the name and location of the destination file and click OK.

Updating the table view

To refresh the table view, do one of the following:

  • Click refresh on the toolbar.
  • Right-click the table and select Reload Page from the context menu.
  • PressCtrl+F5.

Use this function to:

  • Synchronize the data shown with the actual contents of the database.
  • Apply the Result set page size setting after its change.

Viewing the query

To see the query that was used to generate the table:

  • Click View Query on the toolbar.

    If necessary, you can select the query text and copy it to the clipboard (Ctrl+C).

To close the pane where the query is shown, pressEscape.

Last modified: 24 July 2018

See Also

Language and Framework-Specific Guidelines: