JetBrains IntelliJ IDEA Blog

There are many little things in database and SQL area that got attention in yesterday’s IntelliJ IDEA 11 release but now I just want to highlight the major ones.

Data Sources view got a new look and now includes stored procedures both from DDL scripts and real databases. The existing SQL dialects support has been redesigned with two new dialects introduced: H2 and MS Transact-SQL. So the list of supported SQL languages now looks like this:

MySQL 5.6 PostgreSQL 9.0.5 Oracle 11.2 SQLite 3.7 SQL Server 10.50 Derby 10.8 HSQLDB 2.2 H2 1.3 SQL-92    Featuring: Exact syntax as in docs Context-aware completion PL-extensions support

Database view:

• Includes stored procedures. Search for usages, navigate to, drag and drop to editor, etc.
• Working with large databases is now less painful. Tables and procedures are available the moment their names are loaded. Columns, procedure parameters and other information are loaded in background afterwards.
• If something is changed there’s no need to wait for the whole synchronization. Refresh only selected schemas, tables or procedures.

Database Console:

• Console now remembers its state so you won’t lose statements you have never run.
• If you do not want to switch editors back and forth you can run statements directly from an SQL file or any other place where SQL injection takes place: invoke “Run Query in Console” intention for the first time then use console “Execute” action shortcut as if you are in console.
• Transact-SQL batch execution mode (the toggle is hidden in the console properties dialog)
• Console editor now can be undocked and split as any other editor.

Enjoy.

We at JetBrains know how important it is to track what has been changed in your project from one version or revision to another. This is why IntelliJ IDEA 10.5 brings you new Diff Tools for comparing local directories, DB objects (Data Sources, Schemes, Tables). In later updates we’re planning on including even remote to local files comparison as well. Diff Tools are available via Ctrl+D, or from context menu, wherever you may need them.

Say, to compare two Data Sources (or Tables, or Schemes, come to that), all you need is select them in Data Sources tool window, and then invoke the Compare action either from context menu, or via toolbar:

Comparing two local folders is just as easy: the action is available in Project View file tree.

In IDEA 10.5 “Find Usages” (Alt-F7) results for tables and columns include database schema usages, i.e. references in indices, primary and foreign keys, see screenshot below:

Develop with pleasure!

IntelliJ IDEA X features a simple database table editor and viewer. You can add and remove rows, sort by specific column and choose columns to display just by pressing F4 on a table in the Data Sources view.

If you find the console-like way better than the GUI way, you can still run all those delete from… and insert into… statements in the Database console.

Try this in the latest IntelliJ IDEA 10 EAP and let us know your thoughts on it.

In IntelliJ IDEA 10 a new JPA Console is added to existing Hibernate and Database consoles. Now you can run and test your JPA queries in a target environment of your application.

Both Hibernate and JPA consoles are available if applicable.

Note the new “Run Query in Console” Intention Action that is now available on all the supported types of queries: JPA QL, Hibernate QL and SQL. It allows you to quickly run a query directly from the editor in a corresponding console either new or already running.

The latest IntelliJ IDEA 9.0.2 EAP contains a big number of Database-related functionality changes:

• Database Console now has its own toolwindow
• New console-like UI that keeps input and output in one place and retains the highlighting for execution history
• Keyboard shortcuts for all Console actions are now configurable
• Per-result Page Up, Page Down and Refresh actions
• Complete support for In-Memory Databases (try running some DDL in Console then click Refresh Tables and all the tables will be loaded in the Data Sources tree)
• Improved support for heavy JDBC drivers that use native libraries to run
• The notion of Database Connection allows IntelliJ IDEA to do some cool stuff such as Quick Table Contents Lookup integrated with Quick Documentation Lookup (Ctrl+Q)

The Hibernate Console has also been improved accordingly (separate toolwindow, console-like UI and per-result paging actions).

Try all this in the latest EAP and let us know what you think.

UPD: In the next EAP SQL scripts can be run directly from Project View or Editor via Run SQL Script (Ctrl+Shift+F10).

Since IntelliJ IDEA 9.0.2 Database Diagram supports drag-and-drop for adding more tables to the view. The screenshot below shows the way to access the diagram if you somehow missed the What’s New in 9.0 page.

You can try this right now in the latest EAP.

The upcoming IntelliJ IDEA 8.1.1 includes a new option to generate separate XML mapping per entity.

With IntelliJ IDEA you can quickly generate persistence mappings from any database schema. This feature is not so easy to find, so here’s the tip:

In IntelliJ IDEA HQL console can be used with pure JPA facets (as well as with Hibernate facets) to run JPAQL and HQL queries. All you need is Hibernate libraries and JDBC driver in a module classpath. If persistence.xml contains no Hibernate-specific database connection options, it should be mapped to an existing data source via JPA facet settings.

Tip: HQL console can also be used to generate DDL statements for JPA and Hibernate mappings via its Generate DDL command.

Update: If a Persistence Unit or Session Factory is mapped to a valid local Data Source there is no need to specify JDBC driver jars in a module classpath (Since 8.1.1).