Getting Started with MySQL and NetBeans SQL Tools
Installing MySQL and the MySQL Workbench
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.
Installing MySQL on Mac OS
Mac users should start by downloading the MySQL server software from http://dev.mysql.com/downloads/mysql/. Be sure to select macOS from the 'Select Platform' menu on that page. Different versions of MySQL are available for different versions of Mac OS: make sure you select the installer for the version of Mac OS that your computer uses. In particular, you need to watch out for the fact that separate versions are available for Intel vs. M1 Macs. To check what type of Mac you have, go the finder and select About this Mac from the Apple menu. The processor type you have will be listed in that dialog box. If you have an Intel Mac you need to download the x86 version of MySQL. If you have an M1 Mac you need to download the ARM version of MySQL. (Most of you more than likely have an Intel Mac, so if you are not sure, download the x86 version.) When selecting the file to download, I strongly suggest you select the DMG form of the MySQL package.
When you download the disk image for MySQL, you can double-click the dmg file to open the installation folder. Inside you will find a package file. Double-click this to run the installation package. During the installation process the installer will prompt you to create a root password. Please write this root password down and keep a copy in a safe place.
Next, download the DMG file for the MySQL Workbench from http://dev.mysql.com/downloads/tools/workbench/. Double-click the dmg file after downloading, and drag the MySQL workbench icon from that folder into your applications folder.
Installing MySQL on Windows
The MySQL installer for Windows is a unified installer package that will install both the MySQL server and the other tools you will need. Go to dev.mysql.com/downloads/installer/ to download the unified installer. When the installer starts, start by selecting the Developer Default installation type.
The installer will install all of the tools you will need to do MySQL development.
After the MySQL server is installed, the installer will walk you through a series of configuration screens. On one of the screens the installer will ask you to set up a root password. Write this password down and keep it in a safe place.
Leave the options on the final screen set as they are. These options will ensure that MySQL starts up automatically when your computer starts, so it will always be available.
Setting up a user account
When you installed the MySQL server software the installer should have prompted you to create a password for the root account.
Since everyone is going to have a different root password, we will never use the root account to connect to MySQL from our Java programs. Instead, everyone will set up a second user account and we will all use that account to do all of our interactions with MySQL.
To set up this second account, start by launching the MySQL Workbench application. When it starts up should see a welcome screen that looks like this.
Click the button to connect to the local MySQL server
Click the Administration tab in the Navigator on the left and then click the Users and Privileges link.
Click the Add Account button to create a new user account, and fill in the account details as shown here. Set the password for this new account to 'Cmsc250!' (with no quotes).
Click the Administrative Roles tab, and then click the DBManager role.
Click the Schema Privileges tab, and then the Add Entry... button.
In the dialog box that appears, leave the default setting and click OK.
Finally, click the Select "ALL" button and click Apply.
You have now set up a user account with the user name 'student' and password 'cmsc250!'. We will be using this account for all of our examples to connect to MySQL in a way that is consistent for everyone.
Getting NetBeans set up for MySQL
To create a NetBeans project that can interact with MySQL we will need to add a special MySQL connector library to our projects.
For all of our projects that interact with MySQL we will be using Maven as the build system. One of the nice features of Maven is that we can add a dependency to the pom.xml file that tells Maven to download and use a specified library. For any future project that needs to talk to MySQL we will need to add the following dependency to pom.xml:
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
</dependencies>
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.
Our first JDBC example
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. You will find this source code file in the project linked at the top of the page.
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=student&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();
}
}
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
MySQL Workbench
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. 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 34. Chapter 34 is not part of the printed textbook. Instead, you can download a PDF for chapter 34 from the companion web site for the text.