IntelliJ IDEA 13.1.0 Web Help

Some features described here are available in Ultimate edition only.

The Table Editor provides a graphical interface for working with table data. You can sort, filter, add, edit and remove the data as well as perform other, associated tasks.

Note

The Table Editor is not available for DDL data sources.

Opening a table in the Table Editor
  • In the Database tool window, do one of the following:
    • Click the table of interest and click DataTables on the toolbar (if the toolbar is not currently hidden).
    • Select the table and press F4F4F4F4F4F4F4F4F12 or Ctrl+Shift+ECommand Down or F4F4F3.
    • Right-click the table and select Table Editor from the context menu.

As a result, the table opens in the Table Editor on a separate editor tab.

Protecting a table from accidental modifications

To protect a table from accidental modifications in the Table 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 Table Editor can still be modified when using the database console or in the Database tool window.

Navigating through 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+UpCtrl+Alt+UpCtrl+Alt+UpCtrl+Alt+UpCtrl+Alt+UpCtrl+Alt+UpCtrl+Alt+UpCtrl+Alt+UpCtrl+Alt+UpAlt Command UpAlt Command UpAlt Command Up)
  • dataSourceTableEditorNextPage Next Page (Ctrl+Alt+DownCtrl+Alt+DownCtrl+Alt+DownCtrl+Alt+DownCtrl+Alt+DownCtrl+Alt+DownCtrl+Alt+DownCtrl+Alt+DownCtrl+Alt+DownAlt Command DownAlt Command DownAlt Command 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 settings on the toolbar of the Table Editor.
  2. On the Database page that opens, specify 0 in the Result set page size field, and click OK.
  3. Click refresh or press Ctrl+RCtrl+RCtrl+RCtrl+RCtrl+RCtrl+RCtrl+RCtrl+RCtrl+RConsole.TableResult.ReloadCommand RConsole.TableResult.Reload to refresh the table view.
Navigating to a specified row

To switch to a row with a specified number:

  1. Do one of the following:
    • Press Ctrl+GCtrl+GCtrl+GCtrl+GAlt+GCtrl+GCtrl+GCtrl+GCtrl+LCommand LCommand GCommand L.
    • 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:
    • Press Alt+F7Shift+Alt+7Shift+Alt+7Alt+F7Alt+F7 or Shift+Alt+SAlt+F7 or Ctrl+Shift+EnterShift+Alt+F7Alt+F7Ctrl+GAlt F7Alt F7Shift Command G.
    • 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:

  • F4F4F4F4F4F4F4F4F12 or Ctrl+Shift+ECommand Down or F4F4F3.
  • 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 (sortAlphabetically), to enable sorting the data by the corresponding DBMS.

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

Filtering data
  1. If the filter box is not currently shown, click ShowFilter on the toolbar.
  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 '%y%' and relation <> 'sister'. 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

    Use:

    • EnterEnterEnterEnterEnterEnterEnterEnterEnterEnterEnterEnter to apply the conditions currently specified in the box.
    • find1 to open the list of all the conditions that you have used. Select the condition in the list to apply it again.

      TableEditorFilterHistory

    • clear to clear the box and restore the initial table view (i.e. when no filtering is applied). When the box is empty, clicking this button hides the box.
    • EscapeEscapeEscapeEscapeEscape, Escape or Ctrl+GEscapeEscapeEscapeEscapeEscapeEscapeEscape to restore the initial (unfiltered) table view and hide the box.
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.
    • Press Ctrl+F12Ctrl+F12Ctrl+0Ctrl+F12Ctrl+F12Ctrl+F12Ctrl+F12Ctrl+F12 or Ctrl+Shift+F12Ctrl+F3 or Ctrl+OCommand F12Command F12Command O.

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

    TableResultPaneHideColumn

  2. Select (highlight) the column name of interest and press Console.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibility.
  3. Press EnterEnterEnterEnterEnterEnterEnterEnterEnterEnterEnterEnter or EscapeEscapeEscapeEscapeEscape, Escape or Ctrl+GEscapeEscapeEscapeEscapeEscapeEscapeEscape to close the list.

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

Restoring the initial table view

Click ResetColumnOrderAndVisibility on the toolbar 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 the data, and hide and show the columns

When working with the Table 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.
  • Press Alt+7Alt+7Alt+7Alt+7Alt+7Alt+7 or Ctrl+Alt+SAlt+7Ctrl+7 or Alt+7Alt+7Command 7Command 7Command 7.

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

  • Right-click a cell in the table header row and select Column List.
  • Press Ctrl+F12Ctrl+F12Ctrl+0Ctrl+F12Ctrl+F12Ctrl+F12Ctrl+F12Ctrl+F12 or Ctrl+Shift+F12Ctrl+F3 or Ctrl+OCommand F12Command F12Command O.

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, press Shift+Alt+UpShift+Alt+UpShift+Alt+UpShift+Alt+UpShift+Alt+UpShift+Alt+UpShift+Alt+UpShift+Alt+UpShift+Alt+UpShift Alt UpShift Alt UpShift Alt Up. (In the tool window, you can, alternatively, select Sort | Ascending from the context menu.)
  • To sort the data in the descending order, press Shift+Alt+DownShift+Alt+DownShift+Alt+DownShift+Alt+DownShift+Alt+DownShift+Alt+DownShift+Alt+DownShift+Alt+DownShift+Alt+DownShift Alt DownShift Alt DownShift Alt Down. (In the tool window, alternatively, Sort | Descending.)
  • To cancel sorting by this column, press Console.TableResult.ColumnSortResetConsole.TableResult.ColumnSortResetConsole.TableResult.ColumnSortResetConsole.TableResult.ColumnSortResetConsole.TableResult.ColumnSortResetConsole.TableResult.ColumnSortResetConsole.TableResult.ColumnSortResetConsole.TableResult.ColumnSortResetConsole.TableResult.ColumnSortResetConsole.TableResult.ColumnSortResetConsole.TableResult.ColumnSortResetConsole.TableResult.ColumnSortReset. (In the tool window, alternatively, Sort | Unsorted.)
  • To hide the column (or show a hidden column), press Console.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibility. (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+UpShift+Alt+UpShift+Alt+UpShift+Alt+UpShift+Alt+UpShift+Alt+UpShift+Alt+UpShift+Alt+UpShift+Alt+UpShift Alt UpShift Alt UpShift Alt Up, Shift+Alt+DownShift+Alt+DownShift+Alt+DownShift+Alt+DownShift+Alt+DownShift+Alt+DownShift+Alt+DownShift+Alt+DownShift+Alt+DownShift Alt DownShift Alt DownShift Alt Down and Console.TableResult.ColumnSortResetConsole.TableResult.ColumnSortResetConsole.TableResult.ColumnSortResetConsole.TableResult.ColumnSortResetConsole.TableResult.ColumnSortResetConsole.TableResult.ColumnSortResetConsole.TableResult.ColumnSortResetConsole.TableResult.ColumnSortResetConsole.TableResult.ColumnSortResetConsole.TableResult.ColumnSortResetConsole.TableResult.ColumnSortResetConsole.TableResult.ColumnSortReset) can be used in the Table Editor without opening the structure view.

See also, Sorting data, Hiding and showing columns and Restoring the initial table view.

Using the Value and the Transposed Row views

Two additional, a la quick-doc views are available for a table:

  • Value view. This view provides details about the values in the selected cell or cells. For example, if a cell contains text with line breaks, normally, you can see only the first line. To see the whole text, you can use the Value view.

    In the Value view, you can also see the records referenced in the current record as well as the records that reference the current one.

    TableValueView

  • Transposed Row view. In this view the rows and columns are interchanged. Thus, for a row, the cells are shown one beneath the other. This view is useful for tables with many columns when only a subset of all the columns is visible at a time.

    TableTransposedRowView

To open the Value view, press Ctrl+Q .

To open the Transposed Row view, press Ctrl+Q again.

To close the view, press EscapeEscapeEscapeEscapeEscape, Escape or Ctrl+GEscapeEscapeEscapeEscapeEscapeEscapeEscape.

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 Value from the context menu.
    • Click the cell and press F2 or Alt+EnterF2 or Alt+EnterF2 or Alt+EnterF2 or Alt+EnterF2 or Alt+EnterF2 or Alt+EnterF2 or Alt+EnterF2 or Alt+EnterF2 or Alt+EnterF2 or Alt EnterF2 or Alt EnterF2 or Alt Enter.
  2. Now you can edit the value right in the cell, or open a dialog which gives you more room for editing and additional options. Do one of the following:
    • Modify the value right in the cell. To save the changes, press EnterEnterEnterEnterEnterEnterEnterEnterEnterEnterEnterEnter or click a different cell. To restore an initial value and quit the editing mode, press EscapeEscapeEscapeEscapeEscape, Escape or Ctrl+GEscapeEscapeEscapeEscapeEscapeEscapeEscape.
    • Click browseButton or press Shift+EnterShift+EnterShift+EnterShift+EnterShift+EnterShift+EnterShift+EnterShift+EnterShift+EnterShift EnterShift EnterShift Enter. In the dialog that opens, you can:
      • Make the necessary edits in the upper area.
      • Upload a file into the field (e.g. to replace an existing file with a new one). To do that, click Load file and select the necessary file in the dialog that opens.
      • Replace the current value with null (click Set NULL).
Adding a row
  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.
    • Press Alt+InsertAlt+InsertAlt+InsertAlt+InsertAlt+InsertAlt+InsertAlt+InsertAlt+Insert or Ctrl+NAlt+InsertCommand N or Control EnterControl N or Control EnterCommand N or Control Enter.
  2. To enter values into the cells, use the same procedure as in the case of Modifying cell contents.
Deleting rows
  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+YCtrl+YCtrl+YCtrl+YCtrl+YCtrl+YCtrl+Shift+LCtrl+ECtrl+DCommand BackspaceCommand YCommand D or DeleteDeleteDeleteDeleteDelete or Ctrl+DDeleteDeleteDeleteDeleteDelete, Backspace or Command BackspaceDelete, Backspace or Command BackspaceDelete, Backspace or Command Backspace.
  3. Confirm you intention to delete the selected row or rows.
Managing database transactions

The Auto-commit check box, and the Commit DBCommit and Rollback DBRollback icons on the toolbar let you manage database transactions.

The Auto-commit check box is used to turn the autocommit mode for the database connection on or off.

In the autocommit mode, each SQL statement is executed in its own transaction that is implicitly committed. Consequently, the SQL statements executed in this mode cannot be rolled back.

(Each change of a value in the table, or adding or deleting a row results in executing an SQL statement.)

If the autocommit mode is off, transactions are committed or rolled back explicitly by means of the Commit (DBCommit) or Rollback (DBRollback) command. Each commit or rollback starts a new transaction which provides grouping for a series of subsequent SQL statements.

In this case, the data manipulations in the transaction scope are committed or rolled back all at once when the transaction is committed or rolled back.

Copying the table to the clipboard or saving the table in a file
  1. Do one of the following:
    • Click copyOpen on the toolbar and select Copy All To Clipboard or Save All To File.
    • Right-click the table and select Copy All To Clipboard or Save All To File from the context menu.
  2. If you are saving the table in a file, specify the file name and location.

If only a subset of rows is currently shown, all the rows are copied to the clipboard or saved in a file anyway.

See also, Changing the table output format.

Changing the table output format

Several output formats are available for copying tables to the clipboard or saving them in files. (The output formats, in IntelliJ IDEA are called data extractors.)

When using the Copy All To Clipboard or Save All To File command, the output format (the extractor) which is currently active is used.

To make a different extractor active or to create a new extractor:

  1. Do one of the following:
    • Click DataExtractorButton on the toolbar.
    • Right-click the table and point to Data Extractor: <extractor_name> in the context menu.
  2. Do one of the following:
    • To make a different extractor active, click the name of the desired extractor.
    • To create a new extractor, or to view or modify the settings for an existing extractor, click Configure Extractors. As a result, the Data Extractors dialog will open.
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. Click the cell that contains the LOB of interest and then click mainToolbarSave on the toolbar.
  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.
  • Press Ctrl+RCtrl+RCtrl+RCtrl+RCtrl+RCtrl+RCtrl+RCtrl+RCtrl+RConsole.TableResult.ReloadCommand RConsole.TableResult.Reload.

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 or Ctrl+InsertCtrl+C or Ctrl+InsertCtrl+C or Ctrl+InsertCtrl+C or Ctrl+InsertCtrl+Insert, Alt+C or Escape, WCtrl+C or Ctrl+InsertCtrl+C or Ctrl+InsertCtrl+C or Ctrl+InsertCtrl+C or Ctrl+InsertCommand C or Command InsertCommand C or Command InsertCommand C or Command Insert).

To close the pane where the query is shown, press EscapeEscapeEscapeEscapeEscape, Escape or Ctrl+GEscapeEscapeEscapeEscapeEscapeEscapeEscape.

Working with the CREATE TABLE statement

Click the Text tab (in the lower-left part of the Table Editor) to see the CREATE TABLE statement used to create the table.

If necessary, you can edit this statement and then run it (runBuild on the toolbar or Ctrl+Shift+F10Ctrl+Shift+F10Ctrl+Shift+F10Ctrl+Shift+F10Ctrl+Shift+F10Ctrl+Shift+F10Ctrl+Shift+F10Ctrl+Shift+F10Ctrl+Shift+F10Shift Command F10Shift Command F10Shift Command F10).

To regenerate the CREATE TABLE statement for the current state of the table in the database, use refresh or Ctrl+RCtrl+RCtrl+RCtrl+RCtrl+RCtrl+RCtrl+RCtrl+RCtrl+RConsole.TableResult.ReloadCommand RConsole.TableResult.Reload.

See Also

Language and Framework-Specific Guidelines:

Reference:

Web Resources: