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:
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
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:
+----------+
| Database |
+----------+
| mysql |
| soup |
| test |
+----------+
"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.
Let's create this table in our "SOUP" database. Type
"CREATE TABLE VIDEOS
( TITLE VARCHAR(80) NOT NULL, STAR VARCHAR(40) NULL, TYPE VARCHAR(15)
NULL, VHS CHAR(1) NULL, DVD CHAR(1) NULL, DESCRIPTION VARCHAR(200) NULL);"
(or you can try your hand at cut and paste).
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.
If you now type "select * from videos;" you should see:
+------------------------+---------------+--------+------+------+----------------------------------------------------------+
| TITLE | STAR | TYPE | VHS | DVD | DESCRIPTION |
+------------------------+---------------+--------+------+------+----------------------------------------------------------+
| The Gods Must Be Crazy | a coke bottle | comedy | Y | N | A bushman is introduced to civilization by a coke bottle.|
+------------------------+---------------+--------+------+------+----------------------------------------------------------+
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!
This is the core of most work in JDBC: Finding one particular record.
More info on SQL
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.
Cut and paste this program into a file called Test.java:
import java.sql.* ;
public class Test
{
public static void main( String[] args )
{
try
{
Class.forName( "com.mysql.jdbc.Driver" ).newInstance();
try
{
Connection con = DriverManager.getConnection( "jdbc:mysql://localhost/soup", "root", null );
try
{
Statement statement = con.createStatement();
ResultSet rs = statement.executeQuery("SELECT TITLE FROM VIDEOS");
while ( rs.next() )
{
System.out.println( rs.getString( "TITLE" ) );
}
rs.close();
statement.close();
}
catch ( SQLException e )
{
System.out.println( "JDBC error: " + e );
}
finally
{
con.close();
}
}
catch( SQLException e )
{
System.out.println( "could not get JDBC connection: " + e );
}
}
catch( Exception e )
{
System.out.println( "could not load JDBC driver: " + e );
}
}
}
Compile it and run it. Do you see all of your video titles?
Now copy this source into TestServlet.java:
import java.sql.* ;
import java.io.* ;
import com.javaranch.common.* ;
public class TestServlet extends LogServlet
{
public void doGet( PrintWriter out , CastMap parms )
{
try
{
Class.forName( "com.mysql.jdbc.Driver" ).newInstance();
try
{
Connection con = DriverManager.getConnection( "jdbc:mysql://localhost/soup", "root", null );
try
{
Statement statement = con.createStatement();
ResultSet rs = statement.executeQuery("SELECT TITLE FROM VIDEOS");
while ( rs.next() )
{
out.println( rs.getString( "TITLE" ) + "<p>" );
}
rs.close();
statement.close();
}
catch ( SQLException e )
{
out.println( "JDBC error: " + e );
}
finally
{
con.close();
}
}
catch( SQLException e )
{
out.println( "could not get JDBC connection: " + e );
}
}
catch( Exception e )
{
out.println( "could not load JDBC driver: " + e );
}
}
}
Compile it. Copy the class file to your servlet server directory. Copy
the mysql-connector-java-xxxxxxx-bin.jar jar file into c:\orion\lib.
Direct your browser to http://localhost/servlet/TestServlet - do you see
your titles again?
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.
First we'll add some properties defining a
couple more properties files to our init target based on the
information found on the
the JavaRanch build standards page. .
<target name='init'>
<property name='app.name' value='${ant.project.name}'/>
<property file='${user.home}/${app.name}.properties'/>
<property file='${user.home}/build.properties'/>
<property name='config.filename' value='${user.name}.properties'/>
<property file='config/${config.filename}' />
<property name="prop.message" value="must be defined, preferably in config/${config.filename}
(details at javaranch.com/props)"/>
Now we'll add some properties that we'll need for our new target just below
the properties that previously existed..
<property name='sql.dir' location='${src.dir}/db'/>
<fail message='db.driver.jar ${prop.message}' unless='db.driver.jar'/>
<fail message='db.driver ${prop.message}' unless='db.driver'/>
<fail message='db.url ${prop.message}' unless='db.url'/>
<fail message='db.userid ${prop.message}' unless='db.userid'/>
<fail message='db.password ${prop.message}' unless='db.password'/>
</target>
Of course, that means that, as we did with the deploy target, we need to add
these properties to our properties file.
Now add a new target that looks like this:
<target name='db' depends='init'>
<sql
driver='${db.driver}'
url='${db.url}'
userid='${db.userid}'
password='${db.password}'
classpath = '${db.driver.jar}'
onerror='continue'
>
<transaction src="${sql.dir}/dropAllTables.sql"/>
<transaction src="${sql.dir}/createVideos.sql"/>
</sql>
</target>
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:
<target name='jenny' depends='init'>
<java failonerror='true' classname='com.javaranch.jenny.dev.Jenny'>
<arg value='${user.home}/jenny.properties'/>
<arg value='Driver=${db.driver}'/>
<arg value='URL=${db.url}'/>
<arg value='UserName=${db.userid}'/>
<arg value='Password=${db.password}'/>
<arg value='FacadeDriver=${db.driver}'/>
<arg value='FacadeURL=${db.url}'/>
<arg value='FacadeUserName=${db.userid}'/>
<arg value='FacadePassword=${db.password}'/>
<classpath>
<pathelement location='${lib.dir}/development/jenny-dev.jar'/>
<pathelement location='${lib.dir}/production/jenny-prod.jar'/>
<pathelement location='${db.driver.jar}'/>
</classpath>
</java>
</target>
Be sure that you have these properties defined in your jenny.properties
file in your "user home" directory (next to your build.properties file).
Re-write assignment JDBC-2a using
Jenny. Submit only
VideoServlet.java and build.xml.
Assignment JDBC-3a
Purpose: To experience the SQL command UPDATE.
Extend assignment JDBC-2a to include the ability to edit an existing
video.
Modify the video list, so each video has an "edit" button. Allow the user
to edit the video data and save it. Submit VideoServlet.java and any jsp pages
that you change.
Assignment JDBC-3b
Purpose: To experience the SQL command DELETE.
Extend assignment JDBC-2a to include the ability to delete an existing
video.
Modify the video list, so each video has a "delete" button. Allow the
user to delete the video data. Submit VideoServlet.java and any jsp pages
that you change.
Assignment JDBC-4
Purpose: To gain more experience with Jenny
Extend assignment JDBC-2b to include the ability to edit or delete an
existing video. Submit VideoServlet.java and any jsp pages
that you change.
|