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.
- Opening the JPA console
- Running the console with custom JVM options
- Viewing and modifying console settings
- Composing JPQL queries
- Navigating to the declaration of a class or field
- Running a query
- Running parameterized queries
- Running auto-memorized queries
- Terminating query execution
- Generating SQL statements and DDL SQL scripts
- Hiding or showing the toolbar
- Pinning the Result tab
- Switching between subsets of rows
- Making all rows visible simultaneously
- Navigating to a specified row
- Sorting data
- Reordering columns
- Hiding and showing columns
- Restoring the initial table view
- Using the Structure view to sort data, and hide and show columns
- Copying table data to the clipboard or saving them in a file
- Specifying data output format and options
- Saving a LOB in a file
- Updating the table view
- Viewing the query
- Closing a console
See also, JPA Console Tool Window.
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
- Open the Persistence tool window (e.g. ).
- Expand the JPA facet node.
- Select the persistence unit for which you want to open the console or any node within that persistence unit.
- Do one of the following:
- If asked to choose the console, select JPA Console.
When you run your first query (), 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.
Running the console with custom JVM options
The JPA console is a Java process. If necessary, you can start it with custom JVM options:
- Create an Application run configuration: .
- When starting the console, IntelliJ IDEA will now display an additional Configurations popup with the following options:
Viewing and modifying console settings
Before actually starting to use a console, you may want to take a look at the console settings and adjust them to your needs.
- To access these settings, click on the toolbar of the JPA Console tool window. (Alternatively, Ctrl+Alt+S .)
As a result, the Database page of the Settings / Preferences dialog will open. 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.
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. (Alternatively, you can use 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.
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:
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 on the toolbar or press Ctrl+Enter to run the query. In the dialog that opens, specify the parameter values and click OK.
( 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. )
- Alternatively, you can open the Parameters pane ( 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 ( on the toolbar or Ctrl+Enter).
Parameter values can be specified just as text or numbers, or as Groovy expressions that contain object references and method calls. For example, the value for the
date parameter in the query
SELECT o FROM Order o WHERE o.date > :date
could be specified as
new java.sql.Date(System.currentTimeMillis() - 24*3600*1000)
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.
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 filter the information: just start typing. As a result, only the queries that contain the typed text will be shown.
- Double-click the query to be copied.
- Select the query of interest and press Enter.
- Select the query and click OK.
(Once the query is in the input pane, you can run it straight away.)
Terminating query execution
To terminate execution of the current query, do one of the following:
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:
- To generate DDL SQL statements (
DROP TABLE) for all the objects (classes) associated with the corresponding persistence unit, do one of the following:
The generated SQL statements are shown in the output pane.
Hiding or showing the toolbar
To hide or show the toolbar of the Result pane:
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:
See also, Show query results in new tab.
Switching between subsets of rows
If only a subset of the rows that satisfy the query is currently shown, to switch between the subsets, use:
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:
- Click on the toolbar of the JPA Console tool window.
- Switch to the page, specify
0in the Result set page size field, and click OK.
- Click or press Ctrl+F5 to refresh the table view.
Navigating to a specified row
To switch to a row with a specified number:
- Do one of the following:
- In the dialog that opens, specify the row number and click OK.
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: . 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: . 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: .
Finally, when you click the cell for the third time, the initial state is resorted. That is, sorting by the corresponding column is canceled: .
To reorder columns, use drag-and-drop for the corresponding cells in the header row.
See also, Restoring the initial table view.
Hiding and showing columns
To show a hidden column:
- Do one of the following:
In the list that appears, the names of hidden columns are shown struck through.
- Select (highlight) the column name of interest and press Space.
- Press Enter or Escape to close the list.
Restoring the initial table view
Click 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 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:
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.)
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.
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
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 is Ctrl+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 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 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.
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:
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.
- 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.
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.
- Right-click the cell that contains the LOB of interest and select Save LOB To File.
- 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 on the toolbar.
- Right-click the table and select Reload Page from the context menu.
- Press Ctrl+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, press Escape.
Closing a console
To close a console, do one of the following: