Most Sample Code from "Java How to Program, Sixth Edition" from Deitel and Deitel

IDE (Integrated Design Environments)

Eclipse (Presented by Xiao Feng Li)

For more on Eclipse see:

http://www.eclipse.org/

Eclipse offers such things as:

Netbeans (Presented by Sean Mitchell)

For more on Netbeans see:

http://www.netbeans.org/

For the slides created by Sean, click here:

beanspresentation.tar.gz

For the promo on Matisse (a new way of setting up GUI interfaces) see:

http://www.netbeans.org/kb/41/flash-matisse.html

Netbeans offers:

MySQL

Getting Started in MySQL

You first have to have a database created for you on hercules:

  1. Go to the following web page: https://www.cs.uregina.ca/
  2. Click on the MySQL link
  3. Log-on using your Computer Science UserID and password
  4. Read the Licence Agreement and accept it
  5. Choose a password for your database (I suggest something different from your CS password)

.....Wait 15 minutes before trying to access your database

Before you start playing with SQL, you can get the following file:

Original Modified
books.orig books.sql

(you want the Modified version, but the original is left in case you want to play with SQL on your own MySQL server)

Now, you can start playing with sql. You will first have to be on hercules:

ssh hercules

The relevant command for mysql is:

mysql -p username

It should ask you for your sql password (the one that you chose above). If all is well, you will get an sql prompt.

Understanding SQL

The following table (from page 1197 of Deitel) summarizes some SQL query keywords:

SQL keyword Description
SELECT Retrieves data from one or more tables
FROM Tables involved in the query. Required in every SELECT.
WHERE Criteria for selection that determine the rows to be retrieved, deleted or updated. Optional in a SQL query or a SQL statement
GROUP BY Criteria for grouping rows. Optional in a SELECT query.
ORDER BY Criteria for ordering rows. Optional in a SELECT query.
INNER JOIN Merge rows from multiple tables
INSERT Insert rows into a specified table
UPDATE Update rows in a specified table
DELETE Delete rows from a specified table

We will walk through examples of using each of these using the book.sql database. These examples are taken from Deitel's book.

Let's get started. First let's see what you've got in your database:

show tables;

Nothing eh? Well, let's load up the books.sql database which is going load up four tables:

source books.sql

Now, you should have four tables. If we want to show all columns from the authors table, we can type:
select * from authors;

For only certain columns, we can type:
select authorID, lastName from authors;

Let's get a subset of the database using "where":
select title, editionNumber, copyright
from titles
where copyright > 2002;

Where clause can contain the operators: <, >, <=, >=, =, <> and LIKE. Like is used for pattern matching with wildcard characters percent (%) and under
score (_). The below pattern searches for authors whose last name starts with D. The % indicates any number of characters (including zero) can appear after the D.
select authorID, firstName, lastName
from authors
where lastName like 'D%';

The below example locates authors whose last names start with any character (specified by _), followed by the letter i, followed by any number of additional characters (specified by %)
select authorID, firstName, lastName
from authors
where lastName like '_i%';

The following displays the authors table in ascending order based on the last name. The order can be either ASC (ascending) or DESC (descending). If you do not specify an order, then ASC is used.
select authorID, firstName, lastName
from authors
order by lastName ASC;

The following shows ordering in ascending order based on lastName then firstName:
select authorId, firstName, lastName
from authors
order by lastName, firstName;

If we want to sort in descending order for both fields, how would we do it?

The following shows a combination of "where" and "order by":
select isbn, title, editionNumber, copyright, price
from titles
where title like '%How to Program'
order by title;

The following shows a join of two tables:
select firstName, lastName, isbn
from authors
inner join authorISBN on authors.authorID = authorISBN.authorID
order by lastName, firstName;

Can we do this in another way?

The following inserts a new record into the authors table:
insert into authors (firstName, lastName)
values ('Sue', 'Smith');

The following shows updating Sue's record so that her last name is now "Jones"
update authors
set lastName = 'Jones'
where lastName = 'Smith' and firstName = 'Sue';

Instead of using where lastName='Smith' and firstName = 'Sue', is there anything else we could have typed?

The following demonstrates how to remove Sue from the authors table:
delete from authors
where authorID=5;

*Bonus* The following command is useful for finding out information about column headers and what kind of data is in each field (or column) of the specified table.
describe authors;

Java and SQL Together

Note: in order to run these programs, you must be on hercules and have loaded books.sql into mysql. See the above section for a description on how to do this.

The following code connects to your database and displays the contents of the authors table

Original Modified
DisplayAuthors.orig DisplayAuthors.java

You will have to modify two lines to get this working:

  1. change:
    static final String DATABASE_URL="jdbc:mysql://localhost/nova";

    to:
    static final String DATABASE_URL="jdbc:mysql://localhost/your_userID";

  2. change:
    connection=DriverManager.getConnection(DATABASE_URL,"nova", "blah");

    to
    connection=DriverManager.getConnection(DATABASE_URL,"your_userID", "your_sql_password");

A couple of comments on this code:

This next code allows the user to enter their own query:

Original Modified
ResultSetTableModel.orig ResultSetTableModel.java
DisplayQueryResults.orig DisplayQueryResults.java

Once again, to get this code working you will have to modify the following lines of code in DisplayQueryResults.java:

static final String DATABASE_URL = "jdbc:mysql://localhost/nova";
static final String USERNAME="nova";
static final String PASSWORD="blah";

Change it to your username and password.

The purpose of DisplayQueryResults.java is to produce the GUI interface. Notice that you are using BorderLayout and the the "NORTH" has a TextArea and Submit button. The "CENTER" has the resultTable that contains the results of the query to your database in tabular format.

When you click on the "Submit Query" button, the table is adjusted according to the query in the textarea. The code in DisplayQueryResults.java that initiates this is:

tableModel.setQuery(queryArea.getText());

We haven't talked about ResultSetTableModel.java. This is the code that performs the connection to the database and maintains the ResultSet. The magic in how the table is updated has to do with the fact that ResultSetTableModel extends class AbstractTableModel, which implements interface TableModel. ResultSetTableModel overrides TableModel methods:

These are the functions that are behind the magic of displaying a new Table based on the current query.

Notice that the ResultSetTableModel constructor accepts five arguments--the driver class name, the URL of the database, the username, the password and the default query to perform.

The constructor for the ResultSetTableModel completes the following steps:

  1. load the database driver
  2. establish a connection
  3. set up a statement based on the connection. Notice that there is a different version of the method createStatement; it takes two arguments--the result set type and the result set concurrency. The result set type specifies whether the ResultSet's cursor is able to scroll in both directions or forward only and whether the ResultSet is sensitive to changes. The result set concurrency specifies whether the ResultSet can be updated with ResultSet's update methods. With the arguments in this example, ResultSet is scrollable, insensitive to changes and read only. (page 1217, Deitel)
  4. invoke the ResultSetTableModel's method setQuery, which performs the default query.

A couple of things to note:

The next example shows a method of querying databases without using Connections and Statements. You use a RowSet instead. This example performs the same action as the DisplayAuthors.java

Original Modified
JdbcRowSetTest.orig JdbcRowSetTest.java

Again, you will have to modify the code to contain your own database, username and password

You can note the following:

 

Additional Links

The following are a couple of recommended links on sql:

You also have access to Oracle on hercules. Try:

sqlplus /

Working at Home

To get MySQL working on your home computer, you need two things (from www.mysql.com):

  1. MySQL--an open-source database management system
  2. MySQL Connector--a driver that allows programs to access MySQL databases via JDBC

The tricky part about the driver is connecting it to the classpath. When you download the connector, there are two .jar files. The important one is:
mysql-connector-java-3.1.10-bin.jar

If you do not add this .jar file to the classpath, then a "java.lang.ClassNotFoundException " will be thrown. You have two solutions:

  1. Add it to the class path with a command such as this (note: you will have to type the appropriate directory where the jar file is):
    java -classpath C:\mysql-connector-java-3.1.10\mysql-connector-java-3.1.10-bin.jar;. DisplayAuthors

    2. Note: don't forget the ";." this indicates the current directory. Without it, DisplayAuthors will not be found. The above is specific to Windows. On Unix, you use a full colon ":" instead of a semi-colon ";"

  2. Copy the mysql-connector-java-3.1.10-bin.jar file to the JRE's lib\ext directory