IntelliJ IDEA 2019.2 Help

Working with the JPA console

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.

Prerequisite

For the JPA console to be fully functional, you should associate your persistence unit with the corresponding data source, see Associating persistence units and session factories with data sources.

Opening the JPA console

  1. Open the Persistence tool window (e.g. View | Tool Windows | Persistence).

  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 database openapi icons consoleRun svg on the title bar.

    • Select Console from the context menu.

    • Press Ctrl+Shift+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

When you run your first query icons toolwindows toolWindowRun svg, 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

Running the console with custom JVM options

The JPA console is a Java process. If necessary, you can start it with custom JVM options:

  1. Create an Application run configuration: Run | Edit Configurations | the Add button | Application.

    In the VM options field, specify the options that you want to pass to the JVM at its start. The rest of the run configuration settings don't matter and you don't need to specify them.

  2. When starting the console, IntelliJ IDEA will now display an additional Configurations popup with the following options:

    • <default>. This option corresponds to an ordinary way of starting the console.

    • <YourRunConfigurationName>. Select this option to start the console with the VM options you have specified.

Modify console settings

  • Open a console and click the Settings icon (the Setting icon) of the JPA Console tool window.

    The settings for the JPA console are on the following pages:

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.

You

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. (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 icons toolwindows toolWindowRun svg on the toolbar.

  • Press Ctrl+Enter.

Running parameterized queries

If you have parameters in your statement, you must specify the values of the parameters before you execute the statement.

To execute a parameterized statement, click the Execute button the Execute button on the toolbar and enter values in the second column. Alternatively, to open the Parameters dialog, click the View Parameters button the View Parameters button.

Execute a parametrized statement

Running auto-memorized queries

IntelliJ IDEA stores history of all statements that you have run.

To open the History dialog, click the History button or press Ctrl+Alt+E.

  • To filter information, start typing your search query.

  • To paste the query from the History dialog to the console, double-click the query in the left pane of the History dialog.

  • To delete a record from history, select the record and press Delete.

View history of executed queries

Terminating query execution

To terminate execution of the current statement or statements, click the Disconnect button the Disconnect button or press Ctrl+F2.

Terminate statement execution

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+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 Generate Script on the toolbar and select Generate DDL.

    • Press Ctrl+Shift+Alt+Enter.

The generated SQL statements are shown in the output pane.

JPAConsoleDDL

Pin the tab with query results

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.

  • Right-click the tab and select Pin Active Tab.

  • Click Pin button on the toolbar.

Pin the result tab

Depending on the value of the Limit page size to parameter, the result set might be divided into several pages. For example, you set the Limit page size to parameter to 100, but your query returned 200 rows. You will have two pages of rows, with 100 rows on each page. To navigate between pages, use the following controls:

  • The first page icon: Navigates to the first page of the result set.

  • The previous page icon: Navigates to the previous page of the result set. ( Ctrl+Alt+Up )

  • The next page icon: Navigates to the next page of the result set. ( Ctrl+Alt+Down )

  • Go to the last page icon: Navigates to the last page of the result set.

Set a number of rows in the result set

By default, when you issue a query, the number of returned rows is limited to 500. This limitation is introduced to avoid an overload (for example, when your SELECT statement returns one million rows).

To change this limit, open settings settings Ctrl+Alt+S and navigate to Tools | Database | Data Views. In the Limit page size to field, specify a new number. To disable the limitation, clear the Limit page size to checkbox.

Also, you can click the Change page size list in the editor, and select a predefined value.

Go to the specified row

To navigate to a row with a specified number, right-click the table and select Go To | Row Ctrl+G. In the Go to row dialog, specify the row number and click OK.

Go to a specified row

Sort data

To sort table data in a column, click the cell in the header row.

State

Description

No sorting

Indicates that the data is not sorted in this column. The initial state of the sorting marker.

Ascending order

The data is sorted in the ascending order. 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.

Descending order

The data is sorted in the descending order.

Reordering columns

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

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.

    TableResultPaneHideColumn
  2. Select (highlight) the column name of interest and press Space.

  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 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 in which they were defined initially, and all the columns are shown.

Using the Structure view to sort data, and hide and show 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+F12.

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

  • To sort the data in the descending order, press Shift+Alt+Down.

  • To cancel sorting by this column, press Ctrl+Shift+Alt+Backspace.

  • To hide the column (or show a hidden column), press Space. The names of hidden columns are shown struck through.

    TableResultPaneHideColumn

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

See also, To sort table data in a column, click the cell in the header row. , Hiding and showing columns and Restoring the initial table view.

Export a table to a file

When you copy table data to the clipboard or save it in a file, the data is converted into one of the available output formats. As an output format, you can select SQL INSERT or UPDATE statements, TSV or CSV, an HTML table or JSON data.

Open a table or a result set, click the Dump Data icon (The Dump Data icon) and select To File.

Export a view to a file

Export a table to a clipboard

On the toolbar, click the Dump Data icon (The Dump Data icon) and select To Clipboard.

Configure data output format and options

You can configure how extracted data is converted into text using predefined output formats, customized formats, and scripts. To configure the output for extracted data, right-click any cell in the table and point to Data Extractor.

The data editor context menu

In this menu, you can select the output format (for example, as SQL INSERT statements, HTML table, or in CSV format) or a script that converts data into a specific format. The following additional options are also available:

Skip Computed Columns (SQL)
Enable to exclude columns with calculated values (affects SQL output formats).
Skip Generated Columns (SQL)
Enable to exclude columns with auto-increment values (affects SQL output formats).
Add Table Definition (SQL)
Enable to include the CREATE TABLE statement (affects SQL output formats).

Additionally, you can configure the following parameters:

  • Configure CSV Formats. Opens the CSV Formats dialog that you can use to manage your delimiter-separated values formats (for example, CSV or TSV).

  • Go to Scripts Directory. Opens a directory with the scripts that convert table data into various output formats.

Saving a LOB in a file

You can save a binary large object (BLOB or LOB) from a cell into a file.

  1. Right-click the cell that contains the LOB and select Save LOB.

  2. In the save dialog, specify the name and location of the file and click OK.

Update the table view

You need to update the table view if you want to synchronize the data that you see in the editor with the contents of the database. Or, when you want to apply the page size limit setting after its change.

To refresh the table view, consider the following actions:

  • Click the Synchronize icon The Synchronize icon on the toolbar.

  • Right-click the table and select Reload Page from the context menu.

  • Press Ctrl+F5.

View the query that generated the table

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

Closing a console

To close a console, do one of the following:

  • Click icons actions close svg on the toolbar.

  • Press Ctrl+Shift+F4.

Last modified: 28 August 2019

See Also