tij0169.html

学习java的经典书籍

how to set up a database using your database administration software.
</FONT><P></DIV><DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">Of
course, this process can vary radically from machine to machine, but the
process I used to make it work under 32-bit Windows might give you clues to
help you attack your own situation.
</FONT><P></DIV>
<A NAME="Heading535"></A><H4 ALIGN=LEFT>
Step
1: Find the JDBC Driver
</H4>
<DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">The
program above contains the statement:
</FONT><P></DIV><DIV ALIGN=LEFT><TT><FONT FACE="Courier New" SIZE=3 COLOR="Black">Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");</FONT></TT><P></DIV><DIV ALIGN=LEFT><P></DIV><DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">This
implies a directory structure, which is deceiving. With this particular
installation of JDK 1.1, there was no file called 
</FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><B>JdbcOdbcDriver.class</B></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">,
so if you looked at this example and went searching for it you&#8217;d be
frustrated. Other published examples use a pseudo name, such as
&#8220;myDriver.ClassName,&#8221; which is less than helpful. In fact, the load
statement above for the jdbc-odbc driver (the only one that actually comes with
JDK 1.1) appears in only a few places in the online documentation (in
particular, a page labeled &#8220;JDBC-ODBC Bridge Driver&#8221;). If the load
statement above doesn&#8217;t work, then the name might have been changed as
part of a Java version change, so you should hunt through the documentation
again.
</FONT><P></DIV><DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">If
the load statement is wrong, you&#8217;ll get an exception at this point. To
test whether your driver load statement is working correctly, comment out the
code after the statement and up to the 
</FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><B>catch</B></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">
clause; if the program throws no exceptions it means that the driver is loading
properly.
</FONT><P></DIV>
<A NAME="Heading536"></A><H4 ALIGN=LEFT>
Step
2: Configure the database
</H4>
<DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">Again,
this is specific to 32-bit Windows; you might need to do some research to
figure it out for your own platform.
</FONT><P></DIV><DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">First,
open the control panel. You might find two icons that say &#8220;ODBC.&#8221;
You must use the one that says &#8220;32bit ODBC,&#8221; since the other one is
for backwards compatibility with 16-bit ODBC software and will produce no
results for JDBC. When you open the &#8220;32bit ODBC&#8221; icon, you&#8217;ll
see a tabbed dialog with a number of tabs, including &#8220;User DSN,&#8221;
&#8220;System DSN,&#8221; &#8220;File DSN,&#8221; etc., in which
&#8220;DSN&#8221; means &#8220;Data Source Name.&#8221; It turns out that for
the JDBC-ODBC bridge, the only place where it&#8217;s important to set up your
database is &#8220;System DSN,&#8221; but you&#8217;ll also want to test your
configuration and create queries, and for that you&#8217;ll also need to set up
your database in &#8220;File DSN.&#8221; This will allow the Microsoft Query
tool (that comes with Microsoft Office) to find the database. Note that other
query tools are also available from other vendors.
</FONT><P></DIV><DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">The
most interesting database is one that you&#8217;re already using. Standard ODBC
supports a number of different file formats including such venerable workhorses
as DBase. However, it also includes the simple &#8220;comma-separated
ASCII&#8221; format, which virtually every data tool has the ability to write.
In my case, I just took my &#8220;people&#8221; database that I&#8217;ve been
maintaining for years using various contact-management tools and exported it as
a comma-separated ASCII file (these typically have an extension of 
</FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><B>.csv</B></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">).
In the &#8220;File DSN&#8221; section I chose &#8220;Add,&#8221; chose the text
driver to handle my comma-separated ASCII file, and then un-checked &#8220;use
current directory&#8221; to allow me to specify the directory where I exported
the data file.
</FONT><P></DIV><DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">You&#8217;ll
notice when you do this that you don&#8217;t actually specify a file, only a
directory. That&#8217;s because a database is typically represented as a
collection of files under a single directory (although it could be represented
in other forms as well). Each file usually contains a single table, and the SQL
statements can produce results that are culled from multiple tables in the
database (this is called a <A NAME="Index2851"></A><A NAME="Index2852"></A></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><I>join</I></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">).
A database that contains only a single table (like this one) is usually called a <A NAME="Index2853"></A><A NAME="Index2854"></A><A NAME="Index2855"></A></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><I>flat-file
database
</I></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">.
Most problems that go beyond the simple storage and retrieval of data generally
require multiple tables that must be related by joins to produce the desired
results, and these are called <A NAME="Index2856"></A><A NAME="Index2857"></A><A NAME="Index2858"></A></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><I>relational</I></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">
databases.
</FONT><P></DIV>
<A NAME="Heading537"></A><H4 ALIGN=LEFT>
Step
3: Test the configuration
</H4>
<DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">To
test the configuration you&#8217;ll need a way to discover whether the database
is visible from a program that queries it. Of course, you can simply run the
JDBC program example above up to and including the statement:
</FONT><P></DIV><DIV ALIGN=LEFT><TT><FONT FACE="Courier New" SIZE=3 COLOR="Black">Connection
c = DriverManager.getConnection(
</FONT></TT><P><TT><FONT FACE="Courier New" SIZE=3 COLOR="Black">
 dbUrl, user, password);
</FONT></TT><P></DIV><DIV ALIGN=LEFT><P></DIV><DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">If
an exception is thrown, your configuration was incorrect.
</FONT><P></DIV><DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">However,
it&#8217;s useful to get a query-generation tool involved at this point. I used
Microsoft Query that came with Microsoft Office, but you might prefer something
else. The query tool must know where the database is, and Microsoft Query
required that I go to the ODBC Administrator&#8217;s &#8220;File DSN&#8221; tab
and add a new entry there, again specifying the text driver and the directory
where my database lives. You can name the entry anything you want, but
it&#8217;s helpful to use the same name you used in &#8220;System DSN.&#8221;
</FONT><P></DIV><DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">Once
you&#8217;ve done this, you will see that your database is available when you
create a new query using your query tool.
</FONT><P></DIV>
<A NAME="Heading538"></A><H4 ALIGN=LEFT>
Step
4: Generate your SQL query
</H4>
<DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">The
query that I created using Microsoft Query not only showed me that my database
was there and in good order, but it also automatically created the SQL code
that I needed to insert into my Java program. I wanted a query that would
search for records that had the last name that was typed on the command line
when starting the Java program. So as a starting point, I searched for a
specific last name, &#8216;Eckel&#8217;. I also wanted to display only those
names that had email addresses associated with them. The steps I took to create
this query were:
</FONT><P></DIV>
<OL>
<LI><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">	Start
a new query and use the Query Wizard. Select the &#8220;people&#8221; database.
(This is the equivalent of opening the database connection using the
appropriate database URL.)
</FONT><LI><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">	Select
the &#8220;people&#8221; table within the database. From within the table,
choose the columns FIRST, LAST, and EMAIL.
</FONT><LI><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">	Under
&#8220;Filter Data,&#8221; choose LAST and select &#8220;equals&#8221; with an
argument of Eckel. Click the &#8220;And&#8221; radio button.
</FONT><LI><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">	Choose
EMAIL and select &#8220;Is not Null.&#8221;
</FONT><LI><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">	Under
&#8220;Sort By,&#8221; choose FIRST.
</FONT></OL><DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">The
result of this query will show you whether you&#8217;re getting what you want.
</FONT><P></DIV><DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">Now
you can press the SQL button and without any research on your part, up will pop
the correct SQL code, ready for you to cut and paste. For this query, it looked
like this:
</FONT><P></DIV>

<font color="#990000"><PRE>SELECT people.FIRST, people.LAST, people.EMAIL
FROM people.csv people
WHERE (people.LAST='Eckel') AND 
(people.EMAIL Is Not Null)
ORDER BY people.FIRST </PRE></font><DIV ALIGN=LEFT><P></DIV><DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">With
more complicated queries it&#8217;s easy to get things wrong, but with a query
tool you can interactively test your queries and automatically generate the
correct code. It&#8217;s hard to argue the case for doing this by hand.
</FONT><P></DIV>
<A NAME="Heading539"></A><H4 ALIGN=LEFT>
Step
5: Modify and paste in your query
</H4>
<DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">You&#8217;ll
notice that the code above looks different from what&#8217;s used in the
program. That&#8217;s because the query tool uses full qualification for all of
the names, even when there&#8217;s only one table involved. (When more than one
table is involved, the qualification prevents collisions between columns from
different tables that have the same names.) Since this query involves only one
table, you can optionally remove the &#8220;people&#8221; qualifier from most
of the names, like this:
</FONT><P></DIV>

<font color="#990000"><PRE>SELECT FIRST, LAST, EMAIL
FROM people.csv people
WHERE (LAST='Eckel') AND 
(EMAIL Is Not Null)
ORDER BY FIRST </PRE></font><DIV ALIGN=LEFT><P></DIV><DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">In
addition, you don&#8217;t want this program to be hard coded to look for only
one name. Instead, it should hunt for the name given as the command-line
argument. Making these changes and turning the SQL statement into a
dynamically-created 
</FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><B>String</B></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">
produces:
</FONT><P></DIV>

<font color="#990000"><PRE>"SELECT FIRST, LAST, EMAIL " +
"FROM people.csv people " +
"WHERE " +
"(LAST='" + args[0] + "') " +
" AND (EMAIL Is Not Null) " +
"ORDER BY FIRST"); </PRE></font><DIV ALIGN=LEFT><P></DIV><DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">SQL