Database Support Plugin in JetBrains IDE
Even among those who have been actively using IntelliJ IDEA for a long time, there are quite a few people who know nothing about the plugin that allows working with databases directly from the IDE. Meanwhile, for me personally, this is one of the most useful features of IDEA, because I can write code in vi. It is important how convenient it is to debug and improve. The question is, what does Database support have to do with it? Actually, when debugging applications, I use it.
The story about the Database Support plugin will be completely unofficial, I myself used it only in web development and in development for Android, but I hope it will be interesting for hardcore developers of a hard enterprise.
Once I got the idea to write a web application. There was a chance that the load on it would be quite high and uneven, so I decided to use PostgreSQL as a database engine in the hope that it would work faster than usual MySQL. No sooner said than done. The prototype has been written, the database has been created, and we are starting to fill in the tables with any test filth.
')
After a while, it becomes clear that the structure of the tables needs a little tweaking, and the test records in them have multiplied - they need to be cleaned. And here it turns out that the standard manual tool with PostgreSQL - pgAdmin - is not at all like phpMyAdmin, and in general it is an independent desktop application.
The question is, is there any universal thing that can work with a convenient interface to the database, and an interface whose appearance does not depend on the database with which it works?
JetBrains has come up with such a thing for quite some time, this is Database Support plugin that works with IntelliJ IDEA Ultimate, RubyMine, PyCharm Professional Edition and PhpStorm. It is rather complicated in development and support, so it is not included in free IDEs.
Let's see how he can help us. To begin with, we will configure it to work with PostgreSQL. If we do not yet have a PostgreSQL JDBC driver on our computer, the IDE will offer us to download and install it. In my case, the DBMS is running on the same computer as the IDE:
See the Database tab on the right? When you click on it, the Database Support plugin window opens (you can open it through the Windows View | Tool | Database, and through the list of the Windows Toolbox windows):
Now let's see what we can do with it. First, you can look at the database structure in the plug-in window, and if you need to display a UML diagram, then this can be done (press <Ctrl + Alt + U>). Secondly, we can view and edit database tables. Press F4 on the required table and here it is, in front of us:
We can edit any cell by pressing F2, the “Auto-commit” mark means that commit will be automatically made after the transfer of changes to the database. If you uncheck the box, you can commit and rollback manually.
When sorting a table (click on the column name), changes to the database do not occur, because the table is just the result of a SELECT *, and if while you are viewing it, the database is changed by someone else, you will not see it.
Sorting can be done in different columns - first one by one, then by another, and the Reset columns order and visibility button
will appear if the window is wide enough, or you hover your mouse over the double angle brackets to the left of the Auto-commit setting.
Press <Ctrl + F12> or use the Structure Toolwindow (<Alt + 7>), and we can choose which columns we want to display and which ones we don’t.
In the same selection window, the order in which the columns are sorted is displayed (A - ascending, ascending, D - descending, descending). If the font of your look and feel allows, then instead of A and D you will see beautiful up and down arrows. You can select columns for viewing by a space .
Sorting can be done both in the table on the IDE side, and in advance - on the base side. The latter is done with the ORDER BY button, and using the View Query, you can see which IDEA request is sent to the database:
Of the unobvious possibilities, it is worth noting recently - in EAP version 13 - the row transposition appeared (there was no such thing in IDEA 12): if there are so many columns that the row does not fit on the screen, then by double <Ctrl + Q> it can be obtained as column, and the arrows still run up and down the table with long rows. Of course, nothing happens with the contents of the database; it remains the same.
So far we have been working with the Table View base. However, the console mode is also supported (click on the console icon above the tree view in the Databases window):
Now we can communicate with the database directly, giving it SQL commands, and the familiar autocompletion by <Ctrl + space> is fully supported in SQL.
In the console mode, there is also an “Auto-commit” setting. As before, it means that after the transfer of changes to the database, the transaction will be confirmed automatically. If unchecked, buttons for manual commit and rollback are activated to the right of it.
The results of the queries, if any are expected (for example, with SELECTs) will be shown in a separate table at the bottom of the screen, and they can be viewed in the same way as the table view of the database invoked by F4.
Naturally, using this plugin you can change not only the values ​​of the fields in the tables, but also the structure of the tables, for this it is easy to use the context menus in the database tree on the right.
If the support of traditional databases were limited, then the article on Habr could not be included: there are a lot of conveniences in working with databases with JDBC, but there was a lack of a final chord for working with JDBC. Accord happened in the fall of 2013, when I came to my colleague writing all Android support in JetBrains.
Android developer easily recognizes his torment in the screenshot of the command line below. In order to work with the SQLite database on a device or an emulator, directly, without an application, you have to call the adb shell, then call sqlite3, and ask it to open the database at the address that includes the full package name of the application. A name can be long. And autocompletion to the adb shell is not attached.
Therefore, my visit was a gesture of despair: even in order to tell DROP DATABASE, when the tables had accumulated enough unnecessary test data, I had to spend time on a routine set of long lines.
JetBrains' favorite developer business is the fight against routine, so my earnest requests for something to do with this well lay down the path of good intentions, already laid out by fifty voices for this feature in our tracker. Despite the cloud of other tasks, SQLite support in Android was completed in two weeks. The solution turned out to be elegant: the SQLite database is drawn onto the computer from the device or the emulator transparently to the user, then the local copy can be worked like a regular database, and then it can be poured back into the device by calling Upload from the context menu.
To make sure that you are working with the current copy of the database, you can synchronize with it on the device by pressing the Syncronize button or <Ctrl + Alt + Y>:
Otherwise, working with the database when developing for Android is similar to working with a regular SQL server.
In conclusion, it remains to say that Database Support plugin supports all DBMS for which there are JDBC drivers (and this is almost all popular DBMS on the market), and understands 12 SQL dialects. Which dialect you want to use (and have the appropriate autocompletion and other features for it) can be specified in the IDE settings: Settings | SQL Dialects.
The story about the Database Support plugin will be completely unofficial, I myself used it only in web development and in development for Android, but I hope it will be interesting for hardcore developers of a hard enterprise.
Web developer history
Once I got the idea to write a web application. There was a chance that the load on it would be quite high and uneven, so I decided to use PostgreSQL as a database engine in the hope that it would work faster than usual MySQL. No sooner said than done. The prototype has been written, the database has been created, and we are starting to fill in the tables with any test filth.
')
After a while, it becomes clear that the structure of the tables needs a little tweaking, and the test records in them have multiplied - they need to be cleaned. And here it turns out that the standard manual tool with PostgreSQL - pgAdmin - is not at all like phpMyAdmin, and in general it is an independent desktop application.
The question is, is there any universal thing that can work with a convenient interface to the database, and an interface whose appearance does not depend on the database with which it works?
JetBrains has come up with such a thing for quite some time, this is Database Support plugin that works with IntelliJ IDEA Ultimate, RubyMine, PyCharm Professional Edition and PhpStorm. It is rather complicated in development and support, so it is not included in free IDEs.
Let's see how he can help us. To begin with, we will configure it to work with PostgreSQL. If we do not yet have a PostgreSQL JDBC driver on our computer, the IDE will offer us to download and install it. In my case, the DBMS is running on the same computer as the IDE:
See the Database tab on the right? When you click on it, the Database Support plugin window opens (you can open it through the Windows View | Tool | Database, and through the list of the Windows Toolbox windows):
Now let's see what we can do with it. First, you can look at the database structure in the plug-in window, and if you need to display a UML diagram, then this can be done (press <Ctrl + Alt + U>). Secondly, we can view and edit database tables. Press F4 on the required table and here it is, in front of us:
We can edit any cell by pressing F2, the “Auto-commit” mark means that commit will be automatically made after the transfer of changes to the database. If you uncheck the box, you can commit and rollback manually.
under the spoiler - collage, at the same time it’s impossible to see the Commit and Rollback hints, don't try!
When sorting a table (click on the column name), changes to the database do not occur, because the table is just the result of a SELECT *, and if while you are viewing it, the database is changed by someone else, you will not see it.
Sorting can be done in different columns - first one by one, then by another, and the Reset columns order and visibility button
will appear if the window is wide enough, or you hover your mouse over the double angle brackets to the left of the Auto-commit setting.Press <Ctrl + F12> or use the Structure Toolwindow (<Alt + 7>), and we can choose which columns we want to display and which ones we don’t.
In the same selection window, the order in which the columns are sorted is displayed (A - ascending, ascending, D - descending, descending). If the font of your look and feel allows, then instead of A and D you will see beautiful up and down arrows. You can select columns for viewing by a space .
Sorting can be done both in the table on the IDE side, and in advance - on the base side. The latter is done with the ORDER BY button, and using the View Query, you can see which IDEA request is sent to the database:
Of the unobvious possibilities, it is worth noting recently - in EAP version 13 - the row transposition appeared (there was no such thing in IDEA 12): if there are so many columns that the row does not fit on the screen, then by double <Ctrl + Q> it can be obtained as column, and the arrows still run up and down the table with long rows. Of course, nothing happens with the contents of the database; it remains the same.
So far we have been working with the Table View base. However, the console mode is also supported (click on the console icon above the tree view in the Databases window):
Now we can communicate with the database directly, giving it SQL commands, and the familiar autocompletion by <Ctrl + space> is fully supported in SQL.
In the console mode, there is also an “Auto-commit” setting. As before, it means that after the transfer of changes to the database, the transaction will be confirmed automatically. If unchecked, buttons for manual commit and rollback are activated to the right of it.
The results of the queries, if any are expected (for example, with SELECTs) will be shown in a separate table at the bottom of the screen, and they can be viewed in the same way as the table view of the database invoked by F4.
Naturally, using this plugin you can change not only the values ​​of the fields in the tables, but also the structure of the tables, for this it is easy to use the context menus in the database tree on the right.
Android developer history
If the support of traditional databases were limited, then the article on Habr could not be included: there are a lot of conveniences in working with databases with JDBC, but there was a lack of a final chord for working with JDBC. Accord happened in the fall of 2013, when I came to my colleague writing all Android support in JetBrains.
Android developer easily recognizes his torment in the screenshot of the command line below. In order to work with the SQLite database on a device or an emulator, directly, without an application, you have to call the adb shell, then call sqlite3, and ask it to open the database at the address that includes the full package name of the application. A name can be long. And autocompletion to the adb shell is not attached.
Therefore, my visit was a gesture of despair: even in order to tell DROP DATABASE, when the tables had accumulated enough unnecessary test data, I had to spend time on a routine set of long lines.
JetBrains' favorite developer business is the fight against routine, so my earnest requests for something to do with this well lay down the path of good intentions, already laid out by fifty voices for this feature in our tracker. Despite the cloud of other tasks, SQLite support in Android was completed in two weeks. The solution turned out to be elegant: the SQLite database is drawn onto the computer from the device or the emulator transparently to the user, then the local copy can be worked like a regular database, and then it can be poured back into the device by calling Upload from the context menu.
To make sure that you are working with the current copy of the database, you can synchronize with it on the device by pressing the Syncronize button or <Ctrl + Alt + Y>:
Otherwise, working with the database when developing for Android is similar to working with a regular SQL server.
In conclusion, it remains to say that Database Support plugin supports all DBMS for which there are JDBC drivers (and this is almost all popular DBMS on the market), and understands 12 SQL dialects. Which dialect you want to use (and have the appropriate autocompletion and other features for it) can be specified in the IDE settings: Settings | SQL Dialects.
Source: https://habr.com/ru/post/204064/
All Articles