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. MySQL is free software and can be freely redistributed. MySQL offers good support for Java SQL programming. You can download the software from www.mysql.com. Download the file with the installer for the MySQL 5.1 community edition and launch the installer.
|
For the installation, select the typical installation.
 |
Click through the installation screens. When you reach the last installer screen, leave the option to configure MySQL Server now checked.
 |
This will launch the MySQL Server configuration wizard.
 |
Select the detailed configuration option.
 |
Select the Developer Machine option.
 |
Select the Multifunctional Database option.
 |
For the number of concurrent connections, select the manual setting with a maximum of 10 concurrent connections. None of our examples or assignments will require more than this.
 |
On the network 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 add a firewall exception is checked.
 |
You can either leave the option to install MySQL as a windows service checked, which ensures that MySQL starts automatically whenever you start your computer, or you can start the service manually whenever you need to work with MySQL.
 |
Enter a root password and make note of the password you chose. You will need to know that password to use any of the MySQL administrative tools or connect to the server from a Java program.
 |
Click through the rest of the screens to finish configuring MySQL.
Next, download and run the installer for the MySQL GUI tools from http://dev.mysql.com/downloads/ (click on GUI Tools in the menu on the left of the page). This will install some important administrative tools that you will use to manage your MySQL installation.
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.
 |
If you do not have this library, you can download the driver file from http://dev.mysql.com/downloads/ (click on Connectors - Connector/J in the menu on the left of the page). Unpack the archive and locate the file mysql-connector-java-5.1.10-bin.jar. Drag this jar file onto your hard drive in a location that is easy to remember. Click the New Library... button to configure a new library for your driver. Give your library the name MySQL JDBC Driver. Click the Add JAR/Folder button and select the mysql-connector-java-5.1.10-bin.jar from the location where you placed it on your hard drive.
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. Unpack the archive and drag the student folder into the data folder in your MySQL installation. On my machine that folder is located in C:\Program Data\MySQL\MySQL Server 5.1\data.
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 twice 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 Query Browser that we installed as part of the MySQL GUI tools.
 |
The Query Browser 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 Query Browser 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.