IntelliJ IDEA 2017.2 Help

Database Console

From the Database tool window (for any node within a DB data source):

  • /help/img/idea/2017.2/ConsoleOnTitleBar.png on the title bar if the toolbar is hidden
  • /help/img/idea/2017.2/runJdbcConsole.png on the toolbar if the toolbar is shown
  • Open Console or Open New Console from the context menu
  • Ctrl+Shift+F10

From the Scratches view of the Project tool window:

  • View | Jump to Source
  • Jump to Source from the context menu
  • F4

Overview

Database consoles let you compose and execute SQL statements for databases defined in IntelliJ IDEA as data sources. They also let you analyze and modify the retrieved data.

The input pane of a database console opens as a separate editor tab. This is where you compose your SQL statements.

/help/img/idea/2017.2/DBConsoleInput.png

When you execute your first statement (/help/img/idea/2017.2/run.png), the Database Console tool window opens. If the executed statement retrieves data (e.g. SELECT), there are two panes in the tool window shown on the Output and the Result tabs. (The tab showing retrieved data may be labeled Result # or, if appropriate, the table name may be shown.)

/help/img/idea/2017.2/DBConsoleResult.png

Otherwise, only the output pane is shown.

/help/img/idea/2017.2/DBConsoleOutputOnly.png

Additionally, you can open the Parameters pane (/help/img/idea/2017.2/property_yellow.png) to manage parameters in SQL statements.

/help/img/idea/2017.2/DBConsoleOutputAndParams.png

Input pane

Use the input pane to compose and execute your SQL statements as well as to perform other, associated tasks.

The available functions are accessed by means of the toolbar icons, keyboard shortcuts and context menu commands.

Toolbar icons and shortcuts

ItemShortcutDescription
/help/img/idea/2017.2/run.png Execute Ctrl+Enter Use this icon or shortcut to execute the selected (highlighted) SQL statement or statements.

If nothing is selected, the current statement is executed.

See also, Run 'console.sql', Execute in Console and Executing an SQL statement.

/help/img/idea/2017.2/consoleHistory.png Browse Console History Ctrl+Alt+E Use this icon or shortcut to open a dialog that shows all the statements that you have run for the corresponding data source.

See also, Executing auto-memorized statements.

/help/img/idea/2017.2/property_yellow.png View Parameters Use this icon to open or close the Parameters pane.
/help/img/idea/2017.2/settings.png Settings Use this icon to open the Database page of the Settings dialog to view or edit the settings for the database, Hibernate and JPA consoles, data editors and the Database tool window.
/help/img/idea/2017.2/buttonTransactionControlDB.png Transaction control Select the isolation level for database transactions and the way the transactions are committed.
  • Auto. Each statement is executed in its own transaction that is implicitly committed.
  • Manual. Transactions are committed or rolled back explicitly by means of /help/img/idea/2017.2/iconDBCommitTransaction.png or /help/img/idea/2017.2/iconDBRollbackTransaction.png on the toolbar.
/help/img/idea/2017.2/iconDBCommitTransaction.png CommitCommit the current transaction.
/help/img/idea/2017.2/iconDBRollbackTransaction.png RollbackRoll back the current transaction.
/help/img/idea/2017.2/stop.gif Cancel Running Statements Ctrl+F2 Use this icon or shortcut to terminate execution of the current statement or statements.
<schema> Select the default schema or database, or, for PostgreSQL or Redshift, form the schema search path. (This control may be unavailable).
/help/img/idea/2017.2/DBConsoleSchemaSelector.png

See also, Selecting the default schema or database and Controlling the schema search path for PostgreSQL and Redshift.

Most useful context menu commands

ItemShortcutDescription
Edit as Table If an INSERT statement is currently selected: Open the editor for working with the data in table format.
/help/img/idea/2017.2/sqlConsoleTableEditorForInsert.png

See also, Editing data for INSERT statements in table format.

Change Dialect (<CurrentDialect>) Use this command to change the SQL dialect being used. Select the necessary dialect from the list.

In addition to particular dialects, also the following option is available:

  • <Generic SQL>. Basic SQL92-based support is provided including completion and highlighting for SQL keywords, and table and column names. Syntax error highlighting is not available. So all the statements in the input pane are always shown as syntactically correct.

See also, Changing the SQL dialect.

Explain Plan Show an execution plan (a.k.a. explain plan) for the current statement. The result is shown in a mixed tree/table format on a dedicated Plan tab.
Explain Plan (Raw) Show an execution plan (a.k.a. explain plan) for the current statement. The result is shown in table format. (Technically, EXPLAIN <CURRENT_STATEMENT> or similar statement is executed.)
ExecuteCtrl+EnterExecute the current statement or the sequence of selected statements.
Execute to File Execute the current statement (e.g. SELECT) and save the result in a text file. Select the output format, and specify the file location and name.
Run 'console.sql'Ctrl+Shift+F10Use this command or shortcut to execute all the statements contained in the console.
DiagramsCtrl+Shift+Alt+U
Ctrl+Alt+U
If the cursor is within the name of a schema: Open a UML class diagram for the schema.
  • Show Visualisation (Ctrl+Shift+Alt+U). The diagram opens on a separate editor tab.
  • Show Visualisation Popup (Ctrl+Alt+U). The diagram opens in a pop-up window.

Toolbar of the Database Console tool window

To hide or show the toolbar, click /help/img/idea/2017.2/icon_viewMode.png on the title bar and select Show Toolbar.

ItemShortcutDescription
/help/img/idea/2017.2/settings.png Settings Use this icon to open the Database page of the Settings dialog to view or edit the settings for the database, Hibernate and JPA consoles, data editors and the Database tool window.
/help/img/idea/2017.2/DBConsoleEnableDBMSOutputIcon.png Enable SYS.DBMS_OUTPUT Ctrl+F8 For Oracle: use this icon or shortcut to enable or disable showing the contents of the DBMS_OUTPUT buffer in the output pane.
/help/img/idea/2017.2/property_yellow.png View Parameters Use this icon to open or close the Parameters pane.
/help/img/idea/2017.2/consoleHistory.png Browse Console History Ctrl+Alt+E Use this icon or shortcut to open a dialog that shows all the statements that you have run for the corresponding data source.

See also, Executing auto-memorized statements.

/help/img/idea/2017.2/DBConsoleTWLayout.png Restore Layout Use this icon to restore the original tool window layout (after the rearrangements that you have made).
/help/img/idea/2017.2/stop.gif Cancel Running Statements Ctrl+F2 Use this icon or shortcut to terminate execution of the current statement or statements.
/help/img/idea/2017.2/close.png Close Ctrl+Shift+F4 Use this icon or shortcut to close the tool window.

Output pane

This pane shows the SQL statements that you have run as well as information about other operations performed in the console. These include turning the autocommit mode on or off, committing or rolling back a transaction, etc.

The information about the errors that occur is also shown in this pane.

For most of the events the following information is provided:

  • Timestamp, that is, when the event took place.
  • For data definition and data manipulation operations - how many rows were affected (e.g. added, changed or deleted). For data retrieval operations - how many rows were retrieved.
  • Duration in milliseconds.

The summary info is also shown on the status bar.

Use the following context menu commands:

  • Copy (Ctrl+C) to copy the text selected in the output pane to the clipboard.
  • Compare with Clipboard to compare the text selected in the output pane with the contents of the clipboard.
  • Clear All to clear all the contents of the output pane.

Result pane

This pane shows the data retrieved from the database in table format. You can sort, add, edit and remove the data as well as perform other, associated tasks.

Main functions

Most of the functions in the Result pane are accessed by means of controls on the toolbar, context menu commands for the data cells, and associated keyboard shortcuts.

ItemShortcutDescription
/help/img/idea/2017.2/dataSourceTableEditorFirstPage.png, /help/img/idea/2017.2/dataSourceTableEditorPreviousPage.png, /help/img/idea/2017.2/dataSourceTableEditorNextPage.png and /help/img/idea/2017.2/dataSourceTableEditorLastPage.png These icons and corresponding commands are for switching between the result set pages, i.e. the pages that show the retrieved data.

A fixed number of rows shown simultaneously is referred to as a result set page. If this number is less than the number of rows that satisfy the query, only a subset of all the rows is shown at a time.

In such cases, you can use /help/img/idea/2017.2/dataSourceTableEditorFirstPage.png, /help/img/idea/2017.2/dataSourceTableEditorPreviousPage.png, /help/img/idea/2017.2/dataSourceTableEditorNextPage.png and /help/img/idea/2017.2/dataSourceTableEditorLastPage.png to switch between the subsets. (If all the rows are currently shown, these icons and the corresponding commands are inactive.)

The result set page size is set on the Database page of the Settings dialog.

/help/img/idea/2017.2/dataSourceTableEditorFirstPage.png First Page Use this icon or command to switch to the first of the result set pages to see the first series of rows.
/help/img/idea/2017.2/dataSourceTableEditorPreviousPage.png Previous Page Ctrl+Alt+Up Use this icon, command or shortcut to switch to the previous result set page to see the previous series of rows.
/help/img/idea/2017.2/dataSourceTableEditorNextPage.png Next Page Ctrl+Alt+Down Use this icon, command or shortcut to switch to the next result set page to see the next series of rows.
/help/img/idea/2017.2/dataSourceTableEditorLastPage.png Last Page Use this icon or command to switch to the last of the result set pages to see the last series of rows.
/help/img/idea/2017.2/refresh.png Reload Page Ctrl+F5 Use this icon, command or shortcut to refresh the current table view. 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.
/help/img/idea/2017.2/DBAddRow.png Add New Row Alt+Insert Use this icon, command or shortcut to add a new row to the table.

Complete entering a value into a cell by pressing Enter. To save the new row, select Submit New Row from the context menu or press Ctrl+Enter.

If inappropriate in the current context (i.e. for the table currently shown), this function is not available.

See also, Adding a row.

/help/img/idea/2017.2/DBDeleteRows.png Delete Rows Ctrl+Y Use this icon, command or shortcut to delete the selected row or rows.

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.

If inappropriate in the current context (i.e. for the table currently shown), this function is not available.

/help/img/idea/2017.2/buttonTransactionControlDB.png Tx and Tx Isolation Select the isolation level for database transactions and the way the transactions are committed.
  • Auto. The current transaction is committed automatically when you submit your local changes to the database server.
  • Manual. The changes submitted to the database server are accumulated in a transaction that can either be committed or rolled back.
/help/img/idea/2017.2/iconDBSubmit.png Submit Ctrl+Enter Submit local changes to the database server. See Submitting and reverting changes.
/help/img/idea/2017.2/iconDBCommitTransaction.png CommitCommit the current transaction. See also, Tx.
/help/img/idea/2017.2/iconDBRollbackTransaction.png RollbackRoll back the current transaction. See also, Tx.
/help/img/idea/2017.2/stop.gif Cancel Query Ctrl+F2 Use this icon or shortcut to terminate execution of the current query.
/help/img/idea/2017.2/CompareWithIcon.png Compare With Use this icon to compare the current table with another table. The tables open in the data editors and ones shown in the Database Console tool window are suggested for comparison.
/help/img/idea/2017.2/pin.png Pin Tab Use this icon or command to pin the tab to the tool window to keep the query result.

See also, Pinning the Result tab.

/help/img/idea/2017.2/DataExtractorButton.png Data Extractor: <current_format> Use this button or command to open a menu in which you can select an output format for your data.

In addition to output formats, there are also the following options and commands:

  • Allow Transposition. For 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). For SQL INSERTs and UPDATEs: When copying or saving data (Copy, Dump Data | To File, Dump Data | To Clipboard), don't include auto-increment fields.
  • Add Table Definition (SQL). For SQL INSERTs and UPDATEs: When copying or saving data, add the table definition (CREATE TABLE).
  • Configure CSV Formats. Open the CSV Formats dialog that lets you manage your delimiter-separated values formats (e.g. CSV, TSV).
  • Go to Scripts Directory. Switch to the directory where the scripts that convert table data into various output formats are stored.
/help/img/idea/2017.2/downloadArtifacts.png Dump Data | To Clipboard Use this command to copy the table data onto the clipboard.
/help/img/idea/2017.2/downloadArtifacts.png Dump Data | To File Use this command to save the table data in a file. In the dialog that opens, specify the location and name of the file.
/help/img/idea/2017.2/iconExportToDB.png Export to Database Export the data to another table, schema or database. Select the target schema (a new table will be created) or table (the data will be added to the selected table). In the dialog that opens, specify the data mapping info and the settings for the target table.
View Query Use this button to view the query which was used to generate the table. To close the pane where the query is shown, press Escape.
/help/img/idea/2017.2/IconDBToolbarMenu.png This icon provides access to the following commands:
  • Transpose. Turn the transposed table view on or off. (In the transposed view, the rows and columns are interchanged. So, the rows are shown as columns and vice versa.)
  • Reset View. Restore the initial table view after reordering or hiding the columns, or sorting the data.
  • Settings. Open the Database page of the Settings dialog to view or edit the settings for your database, Hibernate and JPA consoles, data editors and the Database tool window.
Edit F2 Use this command or shortcut to start editing a value in the selected cell or cells. (Alternatively, you can double-click the cell or simply start typing.)

To open the value completion suggestion list, press Ctrl+Space. To enter the modified value, press Enter. To cancel editing, press Escape.

See also, Modifying cell contents and Modifying values in a number of cells at once.

Edit MaximizedShift+Enter Maximize the selected cell and start editing a value in it.

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, press Escape.

See also, Modifying cell contents.

Set DEFAULTCtrl+Alt+DIf appropriate: Set the default value or values.
Set NULLCtrl+Alt+NIf appropriate: Replace the value or values with null.
Load FileIf appropriate: Load a file into the field.
RevertCtrl+Z Revert the changes within the selection. See Submitting and reverting changes.
Clone RowCtrl+DUse this command or shortcut to create a copy of the selected row.
Quick Documentation Ctrl+Q Use this command or shortcut to open the quick documentation view. To close the view, press Escape. For more information, see Using the quick documentation view.
TransposeTurn the transposed table view on or off. Alternatively, use /help/img/idea/2017.2/IconDBToolbarMenu.png | Transpose.
Go To | Row Ctrl+G Use this command or shortcut to switch to a specified row. In the dialog that opens, specify the row number to go to.
Go To | Related Data F4 Use this command or shortcut to switch to a related record. The command options are a combination of those for Go To | Referenced Data and Go To | Referencing Data.

The command is not available if there are no related records.

Go To | Referenced Data Ctrl+B Use this command or shortcut to switch to a record that the current record references. If more than one record is referenced, select the target record in the pop-up that appears.

The command is not available if there are no referenced records.

Go To | Referencing Data Alt+F7 Use this command or shortcut to see the records that reference the current record. In the pop-up that appears there are two categories for the target records:
  • 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 command is not available if there are no records that reference the current one.

CopyCtrl+C Copy the selection onto the clipboard. See also, Copying and pasting data: data types are converted if necessary.
PasteCtrl+V Paste the contents of the clipboard into the table. See also, Copying and pasting data: data types are converted if necessary.
Save LOB Use this command to save the large object (LOB) currently selected in the table in a file.
Alt+J, Shift+Alt+J, Ctrl+W See Selecting cells and ranges: using unobvious techniques.

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: /help/img/idea/2017.2/TableEditorHeaderCellUnsorted.png. 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: /help/img/idea/2017.2/TableEditorHeaderCellSortAsc.png. 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: /help/img/idea/2017.2/TableEditorHeaderCellSortDesc.png.

Finally, when you click the cell for the third time, the initial state is resorted. That is, sorting by the corresponding column is canceled: /help/img/idea/2017.2/TableEditorHeaderCellUnsorted.png.

Here is an example of a table where data are sorted by two of its columns.

/help/img/idea/2017.2/TableEditorSortedTable.png

To restore the initial "unsorted" state for the table, click /help/img/idea/2017.2/IconDBToolbarMenu.png and select Reset View.

Reordering columns

To reorder columns, use drag-and-drop for the corresponding cells in the header row. To restore the initial order of columns, click /help/img/idea/2017.2/IconDBToolbarMenu.png and select Reset View.

/help/img/idea/2017.2/TableEditorReorderColumns.png

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+F12.

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

    /help/img/idea/2017.2/TableResultPaneHideColumn.png
  2. Select (highlight) the column name of interest and press Space.
  3. Press Enter or Escape to close the list.

To show all the columns, click /help/img/idea/2017.2/IconDBToolbarMenu.png and select Reset View.

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

Parameters pane

The Parameters pane shows the parameters detected in the input pane and lets you edit their values. To open or close this pane, use /help/img/idea/2017.2/property_yellow.png on the toolbar.

To start editing a value, switch to the corresponding table cell and start typing. To indicate that you have finished editing a value, press Enter or switch to a different cell. To quit the editing mode and restore an initial value, press Escape.

When you select a row in the table, the corresponding parameter is highlighted in the input pane.

See also, Executing parameterized statements.

See Also

Last modified: 18 August 2017