---

Introducing NetBeans 6.1 and MySQL

In addition to being a great Java IDE, NetBeans is firmly establishing itself as a development environment for a range of dynamic/scripting languages, including Ruby/JRuby, JavaScript and PHP (with Jython and Groovy on the horizon). It is also turning into an excellent developer's front-end to MySQL and other database engines. In this short tutorial we'll look at how you can use NetBeans to create, update and interact with MySQL, though the same functionality should exist for other supported databases. To follow along you'll need to have NetBeans 6.1 installed and access to a MySQL instance running on a (local or remote) server.

The starting point is to create a new project in NetBeans 6.1 ? which could be a Java, Ruby, PHP or other type of project. Use the simplest form of project, so for Ruby use Ruby Application not a Ruby On Rails project. From the file menu click File > New Project and then click on the Ruby category and the Ruby Application project type. Follow the dialogs to create a new application, which has a single source file called main.rb in it. For Java use the Class Library project type as it is the simplest option to use.

On the MySQL side of things we need to create the database and user for that database. If you have access to the server running MySQL then the simplest option is to log on and either use the command-line or a GUI tool to create an empty database and a user that has the full set of access rights to that database. Let's call our sample database devs because we're going to store details of different developers on it.

Alternatively, if you have an existing logon to your MySQL then we can use NetBeans to do everything. Firstly we click on the Services tab (or Window > Services from the menu), right-click on Databases and select New Connection from the menu. Click on the Name drop-down and select MySQL from the list of supported databases. On the Database URL enter the server URL and default port (e.g. jdbc:mysql://server_name:3306), and enter your MySQL user name and password. When you hit OK NetBeans will connect to the MySQL instance on your Server, and if successful you'll have see a new Connection node on the database tree. If you have the user rights you can now right-click on the connection node and click on 'Execute Command…'. This opens a SQL command window which is effectively an interactive prompt to the server. We can now create our new database by issuing the following command:

create DATABASE devs;

When we click on the Run SQL icon on the command window toolbar the SQL will be executed and the output displayed on screen:

Executed successfully in 0.121 s, 1 rows affected.
Line 1, column 1

Execution finished after 0.121 s, 0 error(s) occurred

Using SQL files

So far so good, we've got ourselves a blank database to play with.

The next step in the process is to create some tables in this database. Now, as we're going to be working with this new database from here on we can just connect to it directly by creating a new database connection, but this time we'll specify the database name in the URL. So, this time our database URL is of the form: jdbc:mysql://server_name:3306/devs. This just makes life simpler as we're going to be working on developing this database extensively.

While we could work just from a command window, as we did just now, it makes much more sense to use code to do all of the work. That way we can re-use it later on, as well as adapting and changing it as we go along. So, switching back to the Project tab we right-click on the Source Files node of the project tree and select New > Empty File (you may need to navigate to Other… > Other > Empty File if you can't see it ? though why SQL isn't a file type is a bit of a mystery). Give the new file an extension of .sql to create a SQL script file. In this example we'll call it sql_commands.sql. The .sql extension means that the new file is opened with the SQL command toolbar already in place, though the SQL connection that we want to use needs to be selected from the toolbar dropdown.

The first thing we want to do is create a table called devs to store information about developers. The following code does the trick:

DROP TABLE IF EXISTS devs;

CREATE TABLE devs (
    First_Name VARCHAR(30) NOT NULL,
    Last_Name VARCHAR(30) NOT NULL,
    Age INTEGER NOT NULL,
    Gender CHAR NOT NULL,
    id INTEGER NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id)
);

Enter that into the editor (and you'll note that NetBeans does syntax colouring of SQL code…) and save the sql_commands.sql file before hitting the Run SQL button. As before you'll see the MySQL output in NetBeans verifying the creation of the table. If we Switch back to the Services view we can expand the connection tab, click on the Tables node and see that the devs table is there, with all of the fields listed. If we had some data we could even perform some queries…

Switch back to the SQL editor and append the following code:

INSERT INTO devs VALUES('Bill','Bloggs',25,'m',1);
INSERT INTO devs VALUES('Joe','Bloggs',26,'m',2);
INSERT INTO devs VALUES('Peter','Parker',39,'m',3);
INSERT INTO devs VALUES('Candy','Bliggs',19,'f',4);
INSERT INTO devs VALUES('Amber','Bliggs',27,'f',5);
INSERT INTO devs VALUES('Peter','Parker',49,'f',6);

Click on the Run SQL command and then head back to the Services tab so we can look at the data. Right click on the devs node and select View Data…, which will be displayed both in a grid view and the query that generated it:

select * from devs

OK, let's make this a bit more interesting and say we want to look at how much support our developers are doing. We want a support_hours table, and some sample data to go with it. We can create a new SQL file or just append to our existing file. In either case here's our SQL code:

DROP TABLE IF EXISTS support_hours;

CREATE TABLE support_hours (
    Hours NUMERIC NOT NULL,
    dev_id INTEGER NOT NULL,
    id INTEGER NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id)
);

INSERT INTO support_hours VALUES(10.0,1,1);
INSERT INTO support_hours VALUES(4.0,2,2);
INSERT INTO support_hours VALUES(3.5,3,3);
INSERT INTO support_hours VALUES(15.0,4,4);
INSERT INTO support_hours VALUES(10.4,5,5);
INSERT INTO support_hours VALUES(4.6,6,6);