JavaRanch Cattle Drive - JDBC

Download and install mySQL

Go to www.mysql.com and download the
latest version for your operating system. Install it.

Assuming that you are using some flavor of Windows, here is how it might work:

Download mysql-xxxxxxxx-win.zip from mysql.com. I downloaded
mysql-noinstall-4.1.9-win32.zip
from
http://dev.mysql.com/downloads/.
When you unzip it in your C:\ directory, and it will create a subdirectory for
itself. You might want to rename the subdirectory to "mysql".

MySQL should now be installed. This is a command line server program
although there are GUI interfaces available. To activate it, run the program
"C:\mysql\bin\mysqld" (the "d" stands for "daemon" meaning a program that runs
in the background). If it doesn't run, there are some
other flavors of mysqld available to try. When you run it on Windows, it
will not spawn a new window or anything, so it will just sit there and run.
You need to open a new console window to interact with it.

Open a new console (DOS window) and run
\mysql\bin\mysql -u root.
You should see a prompt like this: "mysql>".

mySQL is now installed and working.

Trying a little SQL

There are a variety of commands you can type at the mysql prompt as well
as SQL (Structured Query Language - sometimes called "sequel"). Try typing
"SHOW DATABASES;" and press enter. You should see something like
this:

This shows that there are two databases that mySQL is currently aware of.
One called "mysql" where it keeps track of its own stuff and "test" where I
suppose you can go tinker without screwing up the other databases.

"SHOW" is not an SQL command. It's something that mySQL provides for you
on top of SQL.

Let's try a SQL command. Type "CREATE DATABASE SOUP;" and press enter.
If you now type "SHOW DATABASES;" and press enter you should see:

"CREATE" is an SQL command. There are other things that can be created
with the "CREATE" command, so we specified "DATABASE". "SOUP" is something
I just made up. The semicolon on the end is not SQL, but is required by
mySQL to show the end of a command.

Now give the command "USE SOUP;" - this way mySQL will know that future
commands are to be applied to the soup database.

What is a relational database?

The main idea of a relational database is that it is a collection of
tables (rows and columns) of data. Particular rows of data might have a
defined relationship with rows of data in another table, which might have
a defined relationship with rows of data in yet another table .... You get
the idea. For all of these assignments we only care about the tables.

Suppose that we have a table

Title

Star

Type

VHS

DVD

Description

Alien

Sigourney Weaver

action

yes

yes

On the way home to Earth, a small team of miners discovers a new life form.

The Gods Must Be Crazy

A coke bottle

comedy

yes

no

A bushman is introduced to civilization by a coke bottle.

Aladdin

Punk kid

kids

yes

no

A young criminal dances his way into the princess' heart with the help of a genie.

Relational databases are made for this kind of data. Each row represents
a video. Each column represents what data can be stored in a video. Once
you have defined the columns, you add one to millions of rows.

Different brands of relational databases might store this data in all
sorts of weird ways, but it doesn't matter to us. We are going to work
with the relational database through a common interface: SQL. Nearly all
relational databases speak SQL. So if we write programs that interface
with the database only via SQL, it should all work the same no matter what
database we use on the back end.

This creates a table called "VIDEOS" with six columns. The "VARCHAR()"
stuff means that it is a string of variable length and the number inside
shows the maximum length. The "CHAR(1)" stuff means that it is a string of
a fixed length of one. "NULL" is saying that it is okay to have nothing in
that field (the column within one row is called "a field"). "NOT NULL"
means that the database won't allow you to put nothing in that field.
"TITLE", "STAR", "TYPE", "VHS", "DVD" and "DESCRIPTION" are the names of
the columns.

Now let's add one row to our new table. Type "INSERT INTO VIDEOS ( TITLE
, STAR , TYPE , VHS , DVD , DESCRIPTION ) VALUES ( 'The Gods Must Be
Crazy' , 'a coke bottle' , 'comedy' , 'Y' , 'N' , 'A bushman is introduced
to civilization by a coke bottle.' );". This is getting a little trickier.
You have to make sure that your column names line up with your data.

This is a good time to talk about the "SELECT" statement. It is what you
use to get data from the database. The syntax is:

SELECT columns FROM table [ WHERE conditions ]

The square brackets in this case mean "this is optional". And in the
above example, we wanted to see all of the rows, so we opted to not use a
where clause. In the example above, we used "*" to represent what columns
we wanted. This means that we want all of the columns.

Assignment JDBC-1

Purpose: To gain a little experience with SQL.

Add two more records (rows) to the videos table.

View the updated table.

Create a new table called "users" that has columns "name", "password" and
"email". Populate it with five rows (records).

View the new table.

Note that if you want to have data that has a single quote (apostrophe),
you need to use two single quotes in a row to make a literal single quote.

There is nothing to send in with this assignment.

The WHERE clause

Let's add another row to your "users" table with a name of "The Tick",
password of "spoon" and email of "tick@thecity.com".

Entering Mr. Rogers' land of make believe, let's pretend that your
"users" table has thousands of rows. If you want to look up the e-mail
address of "The Tick", you would use the command "SELECT EMAIL FROM USERS
WHERE NAME='The Tick'" - try it!

Download and install the mysql JDBC driver

Go back to www.mysql.com and download
the latest version of Connector/J (mysql-connector-java-xxxxxx.zip). Unzip
the outer file into your root directory. Don't unjar the inner jar file
(mysql-connector-java-xxxxxxx-bin.jar) - you're going to just mash that
into your class path.

JDBC: Java Database Connectivity

JDBC is what makes it easy for you to access SQL capable databases from
Java. I have to admit that a lot of the stuff in the Test and TestServlet
classes doesn't look really easy, but most of that is to just get the JDBC
stuff set up. Once you're set up, there isn't much to JDBC. Most of the
action is still inside the SQL. You pass in strings, you get string
results.

Here is some information that may be of some use to you:

We are able to have Java programs talk to the relational database thanks to
the "JDBC Driver" - in this case "MySQL Connector/J".

The "Class.forName()" stuff is what loads the driver.

DriverManager.getConnection() gets an internet type of network connection
to the database.

The Statement object is where we build our query and eventually execute it.
This will return a ResultSet which we pick apart to see what came back.

Anywhere along the way, our network connection could go down, so an
exception could be thrown.

Assignment JDBC-2a

Purpose: To gain a little experience with JDBC and SQL. Specifically
with the SELECT and INSERT commands.

We created the VIDEOS manually at the mysql prompt, but we may need to
create many tables (or recreate the same table many times), so we want
some automation to help us.

We're going to put some sql scripts into a dir off the src dir and add
an ant target to exercise those scripts. Once that's done, anyone
will be able to create the tables exactly the same way every time.

Create a file named createVideos.sql in the src/db directory. The
contents of the file will be the same create statement we used above to
create the VIDEOS table manually.

Create a second file named dropAllTables.sql in the same directory. The
contents of this file will be: DROP TABLE VIDEOS;

We will add some properties to your
previous ant build.xml. The app.name property
and the property file ${user.home}/build.properties should already exist.

Re-write assignment Servlets-4b (using the instructor's Servlets-4b
solution as your base) so it uses the soup database instead of the
ArrayList. Submit only VideoServlet.java and your build.xml file. The jsp
pages should be almost identical to those in the instructor's solution
to Servlets 4b.

Assignment JDBC-2b

Purpose: To gain a little experience with Jenny.

We will use Jenny by incorporating a jenny target into our build script.
First, we need to add another property. <property name='gen.src.dir' location='${build.dir}/gen-src'/>.

Inside our compile target, we want to add the new generated source code
to the classpath, so add
":${gen.src.dir}"
to
<javac srcdir='${src.dir}/java' destdir='${classes.dir}'>to make it look like
<javac srcdir='${src.dir}/java:${gen.src.dir}' destdir='${classes.dir}'>

Finally, we need to add the jenny target to our build.xml file. It looks
like this: