tij0169.html

学习java的经典书籍

has another way to insert names into a query called <A NAME="Index2859"></A><A NAME="Index2860"></A><A NAME="Index2861"></A></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><I>stored
procedures
</I></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">,
which is used for speed. But for much of your database experimentation and for
your first cut, building your own query strings in Java is fine.
</FONT><P></DIV><DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">You
can see from this example that by using the tools currently available &#8211;
in particular the query-building tool &#8211; database programming with SQL and
JDBC can be quite straightforward.
</FONT><a name="_Toc408018783"></a><P></DIV>
<A NAME="Heading540"></A><H3 ALIGN=LEFT>
A
GUI version of the lookup program
</H3>
<DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">It&#8217;s
more useful to leave the lookup program running all the time and simply switch
to it and type in a name whenever you want to look someone up. The following
program creates the lookup program as an application/applet, and it also adds
name completion so the data will show up without forcing you to type the entire
last name:
</FONT><P></DIV>

<font color="#990000"><PRE><font color="#009900">//: VLookup.java</font>
<font color="#009900">// GUI version of Lookup.java</font>
<font color="#0000ff">import</font> java.awt.*;
<font color="#0000ff">import</font> java.awt.event.*;
<font color="#0000ff">import</font> java.applet.*;
<font color="#0000ff">import</font> java.sql.*;

<font color="#0000ff">public</font> <font color="#0000ff">class</font> VLookup <font color="#0000ff">extends</font> Applet {
  String dbUrl = "jdbc:odbc:people";
  String user = "";
  String password = "";
  Statement s;
  TextField searchFor = <font color="#0000ff">new</font> TextField(20);
  Label completion = 
    <font color="#0000ff">new</font> Label("                        ");
  TextArea results = <font color="#0000ff">new</font> TextArea(40, 20);
  <font color="#0000ff">public</font> <font color="#0000ff">void</font> init() {
    searchFor.addTextListener(<font color="#0000ff">new</font> SearchForL());
    Panel p = <font color="#0000ff">new</font> Panel();
    p.add(<font color="#0000ff">new</font> Label("Last name to search <font color="#0000ff">for</font>:"));
    p.add(searchFor);
    p.add(completion);
    setLayout(<font color="#0000ff">new</font> BorderLayout());
    add(p, BorderLayout.NORTH);
    add(results, BorderLayout.CENTER);
    <font color="#0000ff">try</font> {
      <font color="#009900">// Load the driver (registers itself)</font>
      Class.forName(
        "sun.jdbc.odbc.JdbcOdbcDriver");
      Connection c = DriverManager.getConnection(
        dbUrl, user, password);
      s = c.createStatement();
    } <font color="#0000ff">catch</font>(Exception e) {
      results.setText(e.getMessage());
    }
  }
  <font color="#0000ff">class</font> SearchForL <font color="#0000ff">implements</font> TextListener {
    <font color="#0000ff">public</font> <font color="#0000ff">void</font> textValueChanged(TextEvent te) {
      ResultSet r;
      <font color="#0000ff">if</font>(searchFor.getText().length() == 0) {
        completion.setText("");
        results.setText("");
        <font color="#0000ff">return</font>;
      }
      <font color="#0000ff">try</font> {
        <font color="#009900">// Name completion:</font>
        r = s.executeQuery(
          "SELECT LAST FROM people.csv people " +
          "WHERE (LAST Like '" +
          searchFor.getText()  + 
          "%') ORDER BY LAST");
        <font color="#0000ff">if</font>(r.next()) 
          completion.setText(
            r.getString("last"));
        r = s.executeQuery(
          "SELECT FIRST, LAST, EMAIL " +
          "FROM people.csv people " +
          "WHERE (LAST='" + 
          completion.getText() +
          "') AND (EMAIL Is Not Null) " +
          "ORDER BY FIRST");
      } <font color="#0000ff">catch</font>(Exception e) {
        results.setText(
          searchFor.getText() + "\n");
        results.append(e.getMessage());
        <font color="#0000ff">return</font>; 
      }
      results.setText("");
      <font color="#0000ff">try</font> {
        <font color="#0000ff">while</font>(r.next()) {
          results.append(
            r.getString("Last") + ", " 
            + r.getString("fIRST") + 
            ": " + r.getString("EMAIL") + "\n");
        }
      } <font color="#0000ff">catch</font>(Exception e) {
        results.setText(e.getMessage());
      }
    }
  }
  <font color="#0000ff">public</font> <font color="#0000ff">static</font> <font color="#0000ff">void</font> main(String[] args) {
    VLookup applet = <font color="#0000ff">new</font> VLookup();
    Frame aFrame = <font color="#0000ff">new</font> Frame("Email lookup");
    aFrame.addWindowListener(
      <font color="#0000ff">new</font> WindowAdapter() {
        <font color="#0000ff">public</font> <font color="#0000ff">void</font> windowClosing(WindowEvent e) {
          System.exit(0);
        }
      });
    aFrame.add(applet, BorderLayout.CENTER);
    aFrame.setSize(500,200);
    applet.init();
    applet.start();
    aFrame.setVisible(<font color="#0000ff">true</font>);
  }
} <font color="#009900">///:~ </PRE></font></font><DIV ALIGN=LEFT><P></DIV><DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">Much
of the database logic is the same, but you can see that a 
</FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><B>TextListener</B></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">
is added to listen to the 
</FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><B>TextField</B></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">,
so that whenever you type a new character it first tries to do a name
completion by looking up the last name in the database and using the first one
that shows up. (It places it in the 
</FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><B>completion</B></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">
</FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><B>Label</B></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">,
and uses that as the lookup text.) This way, as soon as you&#8217;ve typed
enough characters for the program to uniquely find the name you&#8217;re
looking for, you can stop.
</FONT><a name="_Toc408018784"></a><P></DIV>
<A NAME="Heading541"></A><H3 ALIGN=LEFT>
Why
the JDBC API 
<P>seems
so complex
</H3>
<DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">When
you browse the online documentation for JDBC it can seem daunting. In
particular, in the <A NAME="Index2862"></A><A NAME="Index2863"></A></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><B>DatabaseMetaData</B></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">
interface &#8211; which is just huge, contrary to most of the interfaces you
see in Java &#8211; there are methods such as 
</FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><B>dataDefinitionCausesTransactionCommit(&#160;)</B></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">,
</FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><B>getMaxColumnNameLength(&#160;)</B></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">,
</FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><B>getMaxStatementLength(&#160;)</B></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">,
</FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><B>storesMixedCaseQuotedIdentifiers(&#160;)</B></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">,
</FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><B>supportsANSI92IntermediateSQL(&#160;)</B></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">,
</FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><B>supportsLimitedOuterJoins(&#160;)</B></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">,
and so on. What&#8217;s this all about?
</FONT><P></DIV><DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">As
mentioned earlier, databases have seemed from their inception to be in a
constant state of turmoil, primarily because the demand for database
applications, and thus database tools, is so great. Only recently has there
been any convergence on the common language of SQL (and there are plenty of
other database languages in common use). But even with an SQL
&#8220;standard&#8221; there are so many variations on that theme that JDBC
must provide the large 
</FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><B>DatabaseMetaData</B></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">
interface so that your code can discover the capabilities of the particular
&#8220;standard&#8221; SQL database that it&#8217;s currently connected to. In
short, you can write simple, transportable SQL, but if you want to optimize
speed your coding will multiply tremendously as you investigate the
capabilities of a particular vendor&#8217;s database.
</FONT><P></DIV><DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">This,
of course, is not Java&#8217;s fault. The discrepancies between database
products are just something that JDBC tries to help compensate for. But bear in
mind that your life will be easier if you can either write generic queries and
not worry too much about performance, or, if you must tune for performance,
know the platform you&#8217;re writing for so you don&#8217;t need to write all
that investigation code.
</FONT><P></DIV><DIV ALIGN=LEFT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">There
is more JDBC information available in the electronic documents that come as
part of the Java 1.1<A NAME="Index2864"></A>
distribution from Sun. In addition, you can find more in the book 
</FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black"><I>JDBC
Database Access with Java 
</I></FONT><FONT FACE="Carmina Md BT" SIZE=3 COLOR="Black">(Hamilton,
Cattel, and Fisher, Addison-Wesley 1997). Other JDBC books are appearing
regularly.
</FONT><a name="_Toc408018785"></a><P></DIV>

<div align="right">
<a href="tij_c.html">Contents</a> | <a href="tij0168.html">Prev</a> | <a href="tij0170.html">Next</a>
</div>
</body></html>