Working with the Hibernate console
Use the Hibernate console to write and run HQL queries, analyze the query results, and also to perform other, associated tasks.
- Prerequisites
- Opening the Hibernate console
- Composing HQL 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
- Making all rows visible simultaneously
- 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
- Closing a console
See also, Hibernate Console Tool Window.
Prerequisites
For the Hibernate console to be fully functional, you should:
Associate your session factory with the corresponding data source, see Associating persistence units and session factories with data sources.
If you are using custom entity class/db table name mappings which are not fully reflected in your code and/or configuration files, specify the corresponding NamingStrategy implementation, see Associating a session factory with a NamingStrategy implementation class (Hibernate).
Opening the Hibernate console
Open the Persistence tool window (e.g. ).
Expand the Hibernate facet node.
Select the session factory for which you want to open the console or any node within that session factory.
-
Do one of the following:
Click
on the title bar. Select Console from the context menu.
PressCtrl+Shift+F10.
As a result, the Hibernate Console tool window opens and the input pane is shown. This is where you compose your HQL queries.
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. from, select), also the Result pane opens showing the retrieved data in table format.
Additionally, you can open the Parameters pane (
) to manage parameters in your queries.
Modify console settings
Open a console.
- Click the Settings icon (
) of the Hibernate Console tool window. The settings for the Hibernate console are on the following pages:
Composing HQL queries
When composing your queries in the input pane, use auto-completion and highlighting of HQL keywords, and object and property names.
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 useCtrl+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:
Click
on the toolbar. PressCtrl+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 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, pressEscape. )
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 > :datecould be specified as
new java.sql.Date(System.currentTimeMillis() - 24*3600*1000)Running auto-memorized queries
As you run HQL 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
on the toolbar. PressCtrl+Alt+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 filter the information: just start typing. As a result, only the queries that contain the typed text will be shown.
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 pressEnter.
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 pressDelete.
Terminating query execution
To terminate execution of the current query, do one of the following:
Click
on the toolbar. Press Ctrl+F2.
Generating SQL statements and DDL SQL scripts
You can generate SQL statements for your HQL queries and DDL SQL scripts for your session factory:
- To generate an SQL equivalent of the current query, do one of the following:
Click
on the toolbar. PressCtrl+Shift+Enter.
- To generate DDL SQL statements (
CREATE TABLE,ALTER TABLEandDROP TABLE) for all the objects (classes) associated with the corresponding session factory, do one of the following:Click on the toolbar.
PressCtrl+Shift+Alt+Enter.
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:
Click
on the title bar of the Hibernate Console tool window and click Show 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.
See also, Show query results in new tab .
Navigate between subsets of rows
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:
: Navigates to the first page of the result set. 
: Navigates to the previous page of the result set. ( Ctrl+Alt+Up ) 
: Navigates to the next page of the result set. ( Ctrl+Alt+Down ) 
: Navigates to the last page of the result set.
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 Hibernate 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.
See also, Update the table view and Result set page size.
Go to the specified row
To navigate to a row with a specified number, right-click the table and select (Ctrl+G). In the Go to row dialog, specify the row number and click OK.
Sorting data
To sort table data in a column, click the cell in the header row.
State | Description |
|---|---|
 | Indicates that the data is not sorted in this column. The initial state of the sorting marker. |
 | 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. |
 | 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:
- Do one of the following:
Right-click any of the cells in the header row and select Column List.
PressCtrl+F12.
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.
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 
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.
PressCtrl+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, pressShift+Alt+Up.
To sort the data in the descending order, pressShift+Alt+Down.
To cancel sorting by this column, pressCtrl+Shift+Alt+Backspace.
To hide the column (or show a hidden column), pressSpace. 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.
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 (
) and select To File.
Export a table to a clipboard
On the toolbar, click the Dump Data icon () and select To Clipboard.
Configure a 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.
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 TABLEstatement (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.
Right-click the cell that contains the LOB and select Save LOB.
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 (
) 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
on the toolbar. PressCtrl+Shift+F4.