NetBeans comes with a built-in SQL database system called JavaDB, but is also compatible with a range of other databases. I have chosen the MySQL database server for use in this course. The Community Edition of MySQL is free software available for all major operating systems and can be freely redistributed. MySQL offers good support for Java SQL programming. You can download the software from http://www.mysql.com/downloads/. For Windows you should download and run the installer from that page. For other platforms, follow the links from this page to the download pages for the MySQL Community Server and the MySQL Workbench.
The next portion of this document will walk you through the steps in the Windows installation. Installation on other platforms should be similar.
|
For the installation, select the Developer Default installation. This will install both the MySQL server and the MySQL workbench. On platforms other than windows you will have to download and run separate installers for the server and the workbench.
After the server and workbench are installed, you will be asked for additional configuration information.
 |
Select the Developer Machine option on the first screen.
 |
On the next configuration screen make note of the TCP/IP port that the server uses. You will need to know this port number when you attempt to connect to the database server from a Java program. Make sure the option to create a Windows service is checked so MySQL will start automatically whenever you start your computer. Finally, select a root password to use. All of the examples I will show in class will assume a root password of 'cmsc250', so you may want to use the same root password.
To communicate with a database server Java programs need to make use of a JDBC driver. NetBeans should already be set up with a JDBC driver library. To confirm this, select Libraries from the Tools menu. The MySQL JDBC Driver should appear on the list of available libraries.
 |
Click OK to dismiss the Library Manager.
Next, you might want to obtain a pre-constructed example database to work with. I will be using the student database for the remainder of these notes. You can get a copy of this database by clicking this link. This guide will explain how to import that database into MySQL.
Below is the source code for a simple example program that demonstrates how to use JDBC to connect to a database and extract some information from it.
import java.sql.*;
public class SimpleJdbc {
public static void main(String[] args)
throws SQLException, ClassNotFoundException {
// Load the JDBC driver
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Driver loaded");
// Establish a connection
Connection connection = DriverManager.getConnection
("jdbc:mysql://localhost:3306/student?user=root&password=cmsc250");
System.out.println("Database connected");
// Create a statement
Statement statement = connection.createStatement();
// Execute a statement
ResultSet resultSet = statement.executeQuery
("select firstName, mi, lastName from Student where lastName "
+ " = 'Smith'");
// Iterate through the result and print the student names
while (resultSet.next())
System.out.println(resultSet.getString(1) + "\t" +
resultSet.getString(2) + "\t" + resultSet.getString(3));
// Close the connection
connection.close();
}
}
Create a new project in NetBeans. In that project make a new class named SimplJdbc. Copy the source code above into that file. You may need to edit the line
("jdbc:mysql://localhost:3306/student?user=root&password=cmsc250");
to use the password you chose when you set up your MySQL installation.
Because the code for our example will be using a JDBC driver to connect to MySQL, we will also need to configure the project with the MySQL JDBC Driver library I mentioned above. In the Project pane for your project, right-click on the Libraries folder and select the Add Library... option. Select the MySQL JDBC Driver library from the list that appears and add it to your project.
With the database and the JDBC driver in place, we are ready to run this program. The program establishes a connection to the student database and runs an SQL query against the database. The query asks the database to return the first name, middle initial, and last name of all entries in the Student table of the database whose last name is Smith. The results of the query come back in the form of a result set. The program then iterates through the result set printing the names of all students whose last name is Smith.
The output of this program should look like this:
Driver loaded Database connected Jacob R Smith George K Smith Jean K Smith Josh R Smith
NetBeans also has some handy built-in tools you can use to view and manipulate databases from inside NetBeans. These tools are especially helpful when you want to test SQL queries against a live database to see if they are correctly constructed.
To access the database tools, bring up the Services window by selecting Services from the Window menu. One of the categories in the Services window is Databases. To establish a connection to the student database, right-click on Databases and select the New Connection... option. Fill out the dialog as shown below.
 |
Click OK to dismiss this dialog.
You should now see an entry for the connection to your student database in the Services pane. If the icon is solid as shown below, you are connected to the database. If it is not solid, you can right click on this entry and select Connect... to establish a live connection to the student database. You can expand this entry to see details of the database. Expand the Tables folder to get a view of the tables in the database.
 |
Right-click on the student table and select the View Data... command. This will run a query against the database to show you the contents of this table.
 |
The pane that appears at the right when you do this is a SQL query pane. You can type SQL code in the edit field at the top of the pane and then run that code against the database. The results of the query appear in a table at the bottom of the pane. For example, if you edit the query to read
select * from student where lastName='Smith'
and click the Run SQL button (the button with the green triangle next to Connection), you will see all of the table rows whose last name is Smith.
The most common use for the SQL query pane is to test SQL code that you want to embed in a Java program. You will find it convenient to test your SQL against the database before embedding that code in your Java programs.
Another tool that you will find useful as you work with MySQL is the MySQL Workbench.
 |
The Workbench contains convenient tools for directly creating databases, tables, and entries in tables. (I will cover some of these tools in more detail in the next set of notes.) You will find the Workbench convenient for testing your SQL code.
In addition to the select statement, you will typically need only three SQL statements to work with databases in this class.
To insert a new entry into a table, use the SQL insert statement.
insert into student (ssn,firstName,lastName,deptId) values ('100','Joe','Gregg','CS')
To change one or more fields in an existing row or rows, use the update statement.
update student set zipCode = '54911' where ssn = '100'
To delete one or more rows from a table use the delete statement with a where clause that identifies the rows to delete.
delete from student where ssn = '100'
You can find more information on SQL in the textbook in chapter 37.