|
|
|
|
JavaRanch Cattle Drive - JDBCDownload and install mySQLGo 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:
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>".Trying a little SQLThere 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
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
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:
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-1Purpose: 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 clauseLet'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. Download and install the mysql JDBC driverGo 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 ConnectivityJDBC 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-2aPurpose: 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: 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-2bPurpose: 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. Inside our compile target, we want to add the new generated source code
to the classpath, so add
" 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-3aPurpose: 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-3bPurpose: 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-4Purpose: 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. |
Page maintained by Marilyn de Queiroz
Distraction: The original Java College was written by Paul Wheaton, who now dabbles in permaculture. He's a certified master gardener and has written articles on organic lawn care, proper use of diatomaceous earth and a new way of raising chickens. His research into less toxic living prompted him to write about using cast iron skillets and a healthier way to control fleas in the home.
You can follow Paul's future work on his permaculture blog, his Missoula blog or by following his permaculture podcasts or permaculture videos.