IntelliJ IDEA 13.1.0 Web Help

Some features described here are available in Ultimate edition only.

Use the JPA console to write and run JPQL queries, analyze the query results, and also to perform other, associated tasks.

See also, JPA Console Tool Window.

Opening the JPA console
  1. Open the Persistence tool window (View | Tool Windows | Persistence or Persistence in the lower part of the left-hand tool window bar).
  2. Expand the JPA facet node.
  3. Select the persistence unit for which you want to open the console or any node within that persistence unit.
  4. Do one of the following:
    • Click ConsoleOnTitleBar on the title bar.
    • Select Console from the context menu.
    • Press 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.

    JPAConsoleOpen

  5. If asked to choose the console, select JPA Console.

As a result, the JPA Console tool window opens and the input pane is shown. This is where you compose your JPQL queries.

JPAConsoleInput

Then, when you run your first query (runBuild), the output pane opens above the input pane. Basically, this is the log of operations performed in the console.

If your query retrieves data (e.g. select), also the Result pane opens showing the retrieved data in table format.

JPAConsoleInputOutputResult

Additionally, you can open the Parameters pane (property_yellow) to manage parameters in your queries.

JPAConsoleInputOutputResultParams

Viewing and modifying the console settings

Before actually starting to use the console, you may want to take a look at the console settings and adjust them to your needs.

  • To access these settings, click settings on the toolbar of the JPA Console tool window.

As a result, the Database page of the Settings dialog opens. On this page, you can, for example, specify how:

  • Query results are to be shown (the Data View section).
  • Retrieved table data are converted into CSV, TSV and other formats (the Data Extractors section).

Note that the settings in the Console section are irrelevant to the JPA console. For more information, see Database.

Composing JPQL queries

When composing your queries in the input pane, use auto-completion and highlighting of JPQL keywords, and object and property names.

JPAConsoleCompletion

See also, Navigating to the declaration of a class or field.

Navigating to the declaration of a class or field

When composing a query, it's sometimes useful to take a look at the declaration of a class or field for an object or property referenced in the input pane. To navigate to the corresponding declaration, do one of the following:

  • Place the cursor within the name of the object or property of interest. Then use Ctrl+B, Ctrl+Button1 Click or Button2 ClickCtrl+B, Ctrl+Button1 Click or Button2 ClickCtrl+B, Ctrl+Button1 Click or Button2 ClickCtrl+B, Ctrl+Button1 Click or Button2 ClickCtrl+Alt+G, Escape, Period, Alt+Period or Ctrl+Button1 ClickCtrl+B, Ctrl+Button1 Click or Button2 ClickF12, Shift+F2, Ctrl+B, Ctrl+Button1 Click or Button2 ClickCtrl+B, Ctrl+Button1 Click or Button2 ClickF3 or Ctrl+Button1 ClickCommand B, Command Button1 Click or Button2 ClickCommand B, Command Button1 Click or Button2 ClickF3 or Control Button1 Click. (Alternatively, you can use Navigate | Declaration from the main menu.)
  • Press and hold the Ctrl key, and point to the name of interest. When the text turns into a hyperlink, click the hyperlink.

    JPAConsoleNavToDecl

As a result, the necessary source file opens in the editor and the cursor is placed within the declaration of the corresponding class or the getter method for the corresponding field.

Running a query

To run the current query, do one of the following:

  • Click runBuild on the toolbar.
  • Press Ctrl+EnterCtrl+EnterCtrl+EnterCtrl+EnterCtrl+EnterCtrl+EnterCtrl+EnterCtrl+EnterCtrl+EnterCommand EnterCommand EnterCommand Enter.
Running parameterized queries

Your queries can contain parameters, however, by the time you run such queries the values of the parameters must be specified. There are the following ways of specifying the parameter values:

  • Click runBuild on the toolbar or press Ctrl+EnterCtrl+EnterCtrl+EnterCtrl+EnterCtrl+EnterCtrl+EnterCtrl+EnterCtrl+EnterCtrl+EnterCommand EnterCommand EnterCommand Enter to run the query. In the dialog that opens, specify the parameter values and click OK.

    JPAConsoleParamsDialog

    ( To start editing a value, double-click the corresponding table cell, or 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. To indicate that you have finished editing a value, press EnterEnterEnterEnterEnterEnterEnterEnterEnterEnterEnterEnter or click a different cell. To quit the editing mode and restore an initial value, press EscapeEscapeEscapeEscapeEscape, Escape or Ctrl+GEscapeEscapeEscapeEscapeEscapeEscapeEscape. )

  • Alternatively, you can open the Parameters pane (property_yellow on the toolbar) and specify the corresponding values there. (The values are edited in the same way as in the corresponding dialog.) Then run the query (runBuild on the toolbar or Ctrl+EnterCtrl+EnterCtrl+EnterCtrl+EnterCtrl+EnterCtrl+EnterCtrl+EnterCtrl+EnterCtrl+EnterCommand EnterCommand EnterCommand Enter).

    JPAConsoleParamsPane

Running auto-memorized queries

As you run JPQL queries in the console, IntelliJ IDEA memorizes them. So, at a later time, you can view the queries you have already run and, if necessary, run them again.

To open the dialog where the auto-memorized queries are shown (the History dialog), do one of the following:

  • Click consoleHistory on the toolbar.
  • Press Ctrl+Alt+ECtrl+Alt+ECtrl+Alt+ECtrl+Alt+ECtrl+Alt+ECtrl+Alt+ECtrl+Alt+ECtrl+Alt+ECtrl+Alt+EAlt Command EAlt Command EAlt Command E.

There are two panes in the History dialog. The left-hand pane shows the list of the queries that you have run. For "long" queries, only their beginnings are shown. When you select a query in this pane, the overall query is shown in the pane to the right.

You can copy the queries from the History dialog into the input pane of the console. To copy a query do one of the following:

  • Double-click the query to be copied.
  • Select the query of interest and press EnterEnterEnterEnterEnterEnterEnterEnterEnterEnterEnterEnter.
  • Select the query and click OK.

(Once the query is in the input pane, you can run it straight away.)

You can delete unnecessary memorized queries. To delete a query select the query in the History dialog and press DeleteDeleteDeleteDeleteDelete or Ctrl+DDeleteDeleteDeleteDeleteDelete, Backspace or Command BackspaceDelete, Backspace or Command BackspaceDelete, Backspace or Command Backspace.

Finally, you can copy the queries from the console history into the input pane without opening the History dialog. When in the input pane, press the Up or Down arrow key. As a result, the previous or next of the memorized queries is copied into the input pane.

Terminating query execution

To terminate execution of the current query, do one of the following:

  • Click stop on the toolbar.
  • Press Ctrl+Shift+Alt+DeleteCtrl+Shift+Alt+DeleteCtrl+Shift+Alt+DeleteCtrl+Shift+Alt+DeleteCtrl+Shift+Alt+DeleteCtrl+Shift+Alt+DeleteCtrl+Shift+Alt+DeleteCtrl+Shift+Alt+DeleteCtrl+Shift+Alt+DeleteShift Alt Command DeleteShift Alt Command DeleteShift Alt Command Delete.
Generating SQL statements and DDL SQL scripts

You can generate SQL statements for your JPQL queries and DDL SQL scripts for your persistence unit:

  • To generate an SQL equivalent of the current query, do one of the following:
    • Click icon_sqlCopyQuery on the toolbar.
    • Press Ctrl+Shift+EnterCtrl+Shift+EnterCtrl+Shift+EnterCtrl+Shift+EnterCtrl+Shift+EnterCtrl+Shift+EnterCtrl+Shift+EnterCtrl+Shift+EnterCtrl+Shift+EnterShift Command EnterShift Command EnterShift Command Enter.
  • To generate DDL SQL statements (CREATE TABLE, ALTER TABLE and DROP TABLE) for all the objects (classes) associated with the corresponding persistence unit, do one of the following:
    • Click DDLCopy on the toolbar.
    • Press Ctrl+Shift+Alt+EnterCtrl+Shift+Alt+EnterCtrl+Shift+Alt+EnterCtrl+Shift+Alt+EnterCtrl+Shift+Alt+EnterCtrl+Shift+Alt+EnterCtrl+Shift+Alt+EnterCtrl+Shift+Alt+EnterCtrl+Shift+Alt+EnterShift Alt Command EnterShift Alt Command EnterShift Alt Command Enter.

The generated SQL statements are shown in the output pane.

JPAConsoleDDL

Hiding or showing the Result pane toolbar

To hide or show the toolbar of the Result pane:

  • Click viewMode on the title bar of the JPA Console tool window and click Show Result Toolbar.
Pinning the Result tab

If one and the same tab is used to show your query results, and you get the result that you want to keep, you can pin the tab to the tool window. To do that:

  • Right-click the tab and select Pin Tab.

    JPAConsoleResultPinTab

Navigating through subsets of rows

If only a subset of the rows that satisfy the query 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 that satisfy the query to be shown simultaneously:

  1. Click settings on the toolbar of the JPA Console tool window.
  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.
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.

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

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 corresponding query, and all the columns are shown.

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

When working with the Result pane, the table structure view is available 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 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 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.
  • 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.
  • 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.
  • 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.)

    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 Result pane without opening the structure view.

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

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 that satisfy the corresponding query 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.

Closing the console

To close the console, do one of the following:

  • Click close on the toolbar.
  • Press Ctrl+Shift+F4Ctrl+Shift+F4Ctrl+Shift+4Ctrl+Shift+F4Ctrl+Shift+F4Ctrl+Shift+F4Ctrl+Shift+F4Ctrl+Shift+F4Ctrl+WControl Shift F4Shift Command F4Command W.

See Also

Procedures:

Reference:

Web Resources: