NetBeans has a great integration with various Databases from various vendors. But it does not stop there; integrated into the IDE is a basic SQL editor. It might not be as powerful as the solutions provided by other vendors, such as PGAdmin or MySQL Query Browser, but it is not intended to replace those tools. Instead, it is a fast way to check data and SQL queries without leaving the comfort of your favorite IDE.
In this recipe, we will see how to use the SQL editor to create databases and tables, how to use the SQL Editor, and also how to use NetBeans integrated UI for managing tables and databases.
Java DB will be used as the primary database for this recipe, but other DB will, of course, work as well. While using Java DB, it is necessary to have it configured and running, if unsure how to perform the required configuration, please refer to the recipe Setting up with Apache Derby/Java DB in this chapter.
Let's create a database.
With NetBeans open and Java DB server started:
- Navigate to the Services window and expand the Databases node.
- Right-click on Java DB and select Create Databases....
- The Create Java DB Database window will pop up, then enter the following information:
- Database Name: nbcookbook
- User Name: test
- Password: test
- Confirm Password: test
- Database Location: Leave the default
- Press OK.
The Create Java DB Database window is as follows:
The IDE will execute the required SQL queries for database creation and, upon creation, will place two more icons in the Databases subsection:
Right-click on jdbc:derby://localhost:1527/nbcookbook and select Connect....
After completing the database creation, we will focus on how to create tables:
- Expand the connection node of nbcookbook and then expand the APP table.
- Right-click on Tables under APP and select Create Table....
- Under Table Name, write Address.
- Click on the Add Column button.
- On the Add Column window, enter the following info:
- Name: id
- Type: INTEGER
- On constraints, click on Primary key and Unique will be automatically selected.
- Click OK to return to Create a Table.
An example of Add Column window for the Primary key is as follows:
Continue adding columns until the table looks like this:
With everything in place, we will insert Data using SQL editor.
- To execute a command, right-click on the connection and select Execute Command....
- Inside the SQL Editor, type:
insert into app.address values (0, 123, 'Avenue des Champs-Élysées', 'Paris', null, 0987)
- To execute the command, either click the button as shown below or press Ctrl+Shift+E (shortcut).
- Finally, let's check the contents of our created database.
- Clean the contents of the SQL Editor and type:
select * from app.address
- To execute the command, either click the button shown below or press Ctrl+Shift+E (shortcut):
The SQL Editor is then split in two parts; one with the SQL statement and the other with the data from the Address table:
There is not much mystery on how the IDE accomplishes the work here. It is very straightforward.
For creating a database and table using the GUI, the IDE simply translates those GUI parameters into SQL queries and through the connection to the database, executes those statements.
Using the Command Execution is not much different but instead of using the GUI to build a SQL query, the IDE simply executes the statement provided by the user.
Under Java DB, we have the Databases:
Under Drivers, a connection to the database can be seen, but by default in the Disconnected mode.
Expanding the nodes of the newly created table, Address, it is possible to see how NetBeans conveniently shows the columns of that particular table:
The connection icon changes according to the connection itself. If connected, the icon is the one above; if disconnected, the icon changes to:
Working with multiple connections inside the SQL is very easy. Feeling lazy with those inserts? NetBeans to the rescue!
In this recipe, we just used one connection to one Database, but it is possible to use multiple connections to different databases and different severs from within the comfort of NetBeans.
Simply select Connection on the top of the SQL Editor and the SQL statement inside the editor will be used in that particular database.
For example:
The insert record button is a convenient way to add some rows to the table that is currently being accessed.
Here is the location of the button:
On pressing that button, a new dialog is opened—the Insert Records dialog. As the name suggests, it allows the user to insert one or multiple records.
When the dialog box shows up, it's possible to click in the fields and just type the values, as in other database tools.
There is a very nice Show SQL button where the user can check the build-up of the insert statement.
For now, we will insert only one row:
After entering the values as above, press OK and the IDE will execute the statement. The new row is added to the Database and, the table is refreshed with the new data. The select statement window from the recipe remains open.