IntelliJ IDEA Database Tools

Writing JDBC code without looking at the actual schema is like driving with a paper bag over your head. The Database tool window in IntelliJ IDEA lets you browse tables, run ad-hoc queries, and test SQL before you embed it in Java. This chapter covers connecting to MySQL, exploring the schema, and using the SQL Console to prototype queries.

Prerequisites

• IntelliJ IDEA installed (Ultimate Edition, or the standalone DataGrip IDE)
• MySQL running with the jdbc_demo database
• The jdbc_user account configured in the installation chapters

Adding a MySQL Data Source

Open the Database tool window from the right-hand sidebar, or via View → Tool Windows → Database. Click the + icon, choose Data Source → MySQL, and fill in the form:

Click Test Connection. If the driver is missing, IntelliJ will prompt you to download it automatically. This driver is managed by the IDE separately from your Maven project; it is only used for the tool window.

Browsing the Schema

Once connected, expand the data source tree:

jdbc_demo@
  ├── schemas
  │   └── jdbc_demo
  │       ├── tables
  │       │   ├── accounts
  │       │   └── users
  │       ├── routines
  │       └── views

Double-click a table name to open it in the data viewer. You will see a grid with every row and column, plus buttons to filter, sort, and paginate. This is invaluable for confirming that your Java inserts and updates actually landed in the database.

Right-click a table and choose Jump to Source to see the auto-generated DDL. This is a quick way to verify column names and types without writing DESCRIBE commands.

The SQL Console

The SQL Console is where you write and execute arbitrary SQL against your data source.

Opening a Console

Right-click the jdbc_demo data source and choose New → Query Console. A new editor tab opens. Type any SQL and press Ctrl+Enter (or Cmd+Enter on macOS) to run the statement under the cursor. If nothing is selected, the IDE executes the current statement intelligently.

Prototyping Queries

Before you copy a complex query into Java, test it in the console:

-- Verify the data you expect
SELECT id, name, email, age FROM users WHERE age > 25;
 
-- Check how many rows an update will touch
SELECT COUNT(*) FROM users WHERE name = 'Alice';
 
-- Preview the execution plan
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

If EXPLAIN shows type: ALL, you are performing a full table scan and should consider adding an index.

Running Script Files

You can open any .sql file in the IDE and run it against a configured data source. This is useful for executing the setup scripts from the installation chapters:

1. Open the .sql file in the editor.
2. Click the green execute arrow in the gutter, or press Ctrl+Enter.
3. Choose the target data source (jdbc_demo@127.0.0.1) from the popup.
4. The IDE runs the entire file and shows results in the Services tool window.

Viewing and Editing Data

The data viewer is not read-only. You can double-click a cell, change a value, and press Submit to generate and run the corresponding UPDATE statement. For learning purposes, this is faster than writing Java code just to tweak a single row.

However, be careful: the IDE generates UPDATE and DELETE statements automatically based on the primary key. If the table has no primary key, the generated WHERE clause may affect multiple rows.

Generating Java Code from Schema

IntelliJ IDEA Ultimate can generate Java classes from database tables. Right-click a table, choose Scripted Extensions → Generate POJOs, and select your target package. The IDE creates a plain Java class with fields matching the table columns.

Even if you do not use the generator, the schema browser makes it trivial to copy exact column names into your Java code, avoiding typos that lead to SQLException: Column not found at runtime.

FAQ

Can I connect to multiple databases at the same time?

Yes. The Database tool window supports many simultaneous data sources. You can have MySQL, PostgreSQL, and H2 connections open side by side, each with its own SQL Console.

Does IntelliJ IDEA store my database password securely?

By default, passwords are stored in the IDE's native keychain integration (macOS Keychain, Windows Credential Manager, or Linux Secret Service). You can also choose to keep passwords in memory only, though you will have to re-enter them after restarting the IDE.

Can I export query results to CSV or JSON?

Yes. After running a query, click the Export to File icon in the results toolbar. You can choose CSV, TSV, HTML, JSON, and several other formats. This is useful for generating test fixtures or sharing data snapshots with teammates.

Is the SQL Console connected to the same database as my Java application?

It connects to the same MySQL server and database, but it opens a separate connection. This means transactions started in your Java code are not visible in the SQL Console unless the Java transaction is committed and the isolation level allows it.