jdbc.html

jdbc书

		"update registration 
		set balance=balance -0.50 
		where theuser= ?");
       ps.setString(1, seller);
       ps.close();
       stmt= con.createStatement();
       stmt.executeQuery(
		"select balance from registration 
		where theuser='"+seller+"'");
       rs = stmt.getResultSet();
       if(rs.next()) {
         balance=rs.getDouble(1);
       }
       stmt.close();
       if(balance <0) {
         con.rollback();
         con.close();
         return (-1);
       }

       stmt= con.createStatement();
       stmt.executeUpdate(
		"update auctionitems set 
		counter=counter+1");
       stmt.close();
       con.commit();
       con.close();
       return(0);
     } catch(SQLException e) {
       try {
         con.rollback();
         con.close();
         stmt.close();
         ps.close();
       }catch (Exception ignore){}
     }
     return (0);
  }
</PRE>

<A NAME="esc"></A>
<H3>Escaping Characters</H3>

The JDBC API provides the <CODE>escape</CODE> keyword so you
can specify the character you want to use to escape characters.
For example, if you want to use the percent sign (<CODE>%</CODE>)
as the percent sign and not have it interpreted as the SQL wildcard 
used in SQL <CODE>LIKE</CODE> queries, you have to escape it with the 
escape character you specify with the <CODE>escape</CODE> keyword.

<P>
This next statements shows how you would use the <CODE>escape</CODE> 
keyword to look for the value <CODE>10%</CODE>.

<PRE>
  stmt.executeQuery(
   "select tax from sales where tax like 
       '10\%' {escape '\'}");
</PRE>

If your program stores names and addresses to the database entered
from the command line or by way of a user interface, the single quotes 
(<CODE>'</CODE>) symbol might appear in the data. Passing single quotes
directly into a SQL string causes problems when the SQL statement is 
parsed because SQL gives this symbol another meaning unless it is
escaped. 

<P>
To solve this problem, the following method escapes any <CODE>'</CODE> 
symbol found in the input line. This method can be extended to escape 
any other characters such as commas <CODE>,</CODE> that the database or 
database driver might interpret another way.

<PRE>
static public String escapeLine(String s) {
  String retvalue = s;
  if (s.indexOf ("'") != -1 ) {
    StringBuffer hold = new StringBuffer();
    char c;
    for(int i=0; i &lt; s.length(); i++ ) {
      if ((c=s.charAt(i)) == '\'' ) {
      hold.append ("''");
    }else {
      hold.append(c);
    }
  }
  retvalue = hold.toString();
  }
  return retvalue;
}
</PRE>

However, if you use a <CODE>PreparedStatement</CODE> instead of a
simple <CODE>Statement</CODE>, most of these escape
problems go away.
For example, instead of this line with the escape sequence:

<PRE>
stmt.executeQuery(
"select tax from sales where tax like 
     '10\%' {escape '\'}");
</PRE>

You could use this line:

<PRE>
preparedstmt = C.prepareStatement(
                  "update tax set tax = ?");
</PRE>

<A NAME="db"></A>
<H3>Mapping Database Types</H3>

Apart from a few JDBC types such as <CODE>INTEGER</CODE> that are
represented as an <CODE>INTEGER</CODE> in most popular databases, you 
might find that the JDBC type for a table column does not match the type 
as it is represented in the database. This means 
calls to <CODE>ResultSet.getObject</CODE>,
<CODE>PreparedStatement.setObject</CODE> and 
<CODE>CallableStatement.getObject()</CODE> will 
very likely fail. 

<P>
Your program can determine the database column type from the
database meta data and use that information to check the value 
before retrieving it. This next code checks that the value is 
in fact type <CODE>INTEGER</CODE> before retrieving its value.

<PRE>
  int count=0;
  Connection con=getConnection();
  Statement stmt= con.createStatement();
  stmt.executeQuery(
         "select counter from auctionitems");
  ResultSet rs = stmt.getResultSet();
  if(rs.next()) {
    if(rs.getMetaData().getColumnType(1) == 
                             Types.INTEGER) {
    Integer i=(Integer)rs.getObject(1);
    count=i.intValue();
    }
  }
  rs.close();
</PRE>

<A NAME="date"></A>
<H3>Mapping Date types</H3>

The <CODE>DATE</CODE> type is where most mismatches occur. 
This is because the <CODE>java.util.Date</CODE> class represents both Date and 
Time, but SQL has the following three types to represent data and time information:

<UL>
<LI><FONT FACE="Verdana, Arial, Helvetica, sans-serif">A <CODE>DATE</CODE> type that represents the date only (03/23/99).</FONT>
<LI><FONT FACE="Verdana, Arial, Helvetica, sans-serif">A <CODE>TIME</CODE> type that specifies the time only (12:03:59)</FONT>
<LI><FONT FACE="Verdana, Arial, Helvetica, sans-serif">A <CODE>TIMESTAMP</CODE> that represents time value in nanoseconds.</FONT>
</UL>

These three additional types are provided in the <CODE>java.sql</CODE> package 
as <CODE>java.sql.Date</CODE>, <CODE>java.sql.Time</CODE> and 
<CODE>java.sql.Timestamp</CODE> and are all subclasses of
<CODE>java.util.Date</CODE>. This means you can use convert
<CODE>java.util.Date</CODE> values to the type you need to be compatible
with the database type. 

<P>
<BLOCKQUOTE>
<HR>
<STRONG>Note:</STRONG>
The <CODE>Timestamp</CODE> class loses precision when it is
converted to a <CODE>java.util.Date</CODE> because <CODE>java.util.Date</CODE> 
does not contain a nanosecond field, it is better to not convert a 
<CODE>Timestamp</CODE>instance if the value will be written back to the database.
<HR>
</BLOCKQUOTE>

<P>
This example uses the <CODE>java.sql.Date</CODE> class to convert the
<CODE>java.util.Date</CODE> value returned by the call to
<CODE>Calendar.getTime</CODE> to a <CODE>java.sql.Date</CODE>. 
 
<PRE>
  Calendar currenttime=Calendar.getInstance();
  java.sql.Date startdate=
     new java.sql.Date((
           currenttime.getTime()).getTime());
</PRE>

You can also use the <CODE>java.text.SimpleDateFormat</CODE> class to 
do the conversion. This example uses the 
<CODE>java.text.SimpleDateFormat</CODE> class to convert a
<CODE>java.util.Date</CODE> object to a
<CODE>java.sql.Date</CODE> object:

<PRE>
  SimpleDateFormat template = 
	new SimpleDateFormat("yyyy-MM-dd"); 
  java.util.Date enddate = 
	new java.util.Date("10/31/99"); 
  java.sql.Date sqlDate = 
	java.sql.Date.valueOf(
	                template.format(enddate)); 
</PRE>

If you find a database date representation cannot be
mapped to a Java type with a call to <CODE>getObject</CODE> or 
<CODE>getDate</CODE>, retrieve the value with a call to 
<CODE>getString</CODE> and format the string as a 
<CODE>Date</CODE> value using the <CODE>SimpleDateFormat</CODE> class 
shown above.

<P>
_______<BR>
<A NAME="TJVM"><SUP>1</SUP></A> As used on this web site, 
the terms &quot;Java virtual 
machine&quot; or &quot;JVM&quot; mean a virtual machine 
for the Java platform.

<P ALIGN="RIGHT">
<FONT SIZE="-1">[<A HREF="#top">TOP</A>]</FONT>

</FONT>
</TD>
</TR>
</TABLE>




<!-- ================ -->
<!-- End Main Content -->
<!-- ================ -->

</TD>
</TR>
</TABLE>

<!-- Copyright Insert -->

<BR CLEAR="ALL">

<FORM ACTION="/cgi-bin/search.cgi" METHOD="POST">
<TABLE WIDTH="100%" CELLPADDING="0" BORDER="0" CELLSPACING="5">   
  <TR>
    <TD VALIGN="TOP">
	
    <P ALIGN=CENTER>
    <FONT SIZE="-1" COLOR="#999999" FACE="Verdana, Arial, Helvetica, sans-serif">
    [ This page was updated: <!-- new date --> 13-Oct-99 ]</font></P>
    </TD>
  </TR>
  
  <TR>
    <TD BGCOLOR="#CCCCCC">
    <IMG SRC="/images/pixel.gif" HEIGHT="1" WIDTH="1" ALT=""></TD>
  </TR>
  
  <TR>
    <TD>
    <CENTER>
    <FONT SIZE="-2" FACE="Verdana, Arial, Helvetica, sans-serif">
    <A HREF="http://java.sun.com/products/">Products &amp; APIs</A> | 
    <A HREF="/developer/index.html">Developer Connection</A> | 
    <A HREF="/developer/infodocs/index.shtml">Docs &amp; Training</A> | 
    <A HREF="/developer/support/index.html">Online Support</A><BR>
    <A HREF="/developer/community/index.html">Community Discussion</A> |
    <A HREF="http://java.sun.com/industry/">Industry News</A> | 
    <A HREF="http://java.sun.com/solutions">Solutions Marketplace</A> | 
    <A HREF="http://java.sun.com/casestudies">Case Studies</A>
    </FONT>
    </CENTER>
    </TD>
  </TR>
  
  <TR>
    <TD BGCOLOR="#CCCCCC">
    <IMG SRC="/images/pixel.gif" HEIGHT="1" WIDTH="1" ALT=""></TD>
  </TR>

  <TR>
    <TD ALIGN="CENTER">
    <FONT SIZE="-2" FACE="Verdana, Arial, Helvetica, sans-serif">
    <A HREF="http://java.sun.com/docs/glossary.html">Glossary</A> - 
    <A HREF="http://java.sun.com/applets/">Applets</A> - 
    <A HREF="http://java.sun.com/docs/books/tutorial/">Tutorial</A> - 
    <A HREF="http://java.sun.com/jobs/">Employment</A> - 
    <A HREF="http://java.sun.com/nav/business/">Business &amp; Licensing</A> - 
    <A HREF="http://java.sun.com/javastore/">Java Store</A> -
    <A HREF="http://java.sun.com/casestudies/">Java in the Real World</A>
    </FONT>
    </TD>
  </TR>

  <TR>
    <TD>
    <CENTER>
    <FONT SIZE="-2" FACE="Verdana, Arial, Helvetica, sans-serif">
    <a href="/siteinfo/faq.html">FAQ</a> |
    <a href="/feedback/index.html">Feedback</a> | 
    <a href="http://www.dynamicdiagrams.net/mapa/cgi-bin/help.tcl?db=javasoft&dest=http://java.sun.com/">Map</a> | 
    <A HREF="http://java.sun.com/a-z/index.html">A-Z Index</A>
    </FONT>
    </CENTER>

    </TD>
  </TR>
  
  <TR>
    <TD>

    <TABLE WIDTH="100%" CELLPADDING="0" BORDER="0" CELLSPACING="0">
      <TR>
        <TD WIDTH="50%">
        <FONT SIZE="-2" FACE="Verdana, Arial, Helvetica, sans-serif">
        For more information on Java technology<BR>
        and other software from Sun Microsystems, call:<BR>
        </FONT>
        <FONT SIZE="-1" FACE="Verdana, Arial, Helvetica, sans-serif">
        [phone]<BR></FONT>
        <FONT SIZE="-2" FACE="Verdana, Arial, Helvetica, sans-serif">
        Outside the U.S. and Canada, dial your country's 
        <A HREF="http://www.att.com/business_traveler/attdirecttollfree/">AT&amp;T&nbsp;Direct&nbsp;Access&nbsp;Number</A> first.<BR>
        </FONT>
        </TD>

        <TD ALIGN="RIGHT" WIDTH="50%">
        <A HREF="http://www.sun.com"><IMG SRC="/images/lgsun.gif" width="64" height="30" border="0" ALT="Sun Microsystems, Inc."></A><BR>
        <FONT SIZE="-2" FACE="Verdana, Arial, Helvetica, sans-serif">
        Copyright &copy; 1995-99
        <A HREF="http://www.sun.com">Sun Microsystems, Inc.</A><BR>
        All Rights Reserved. 
        <a href="http://www.sun.com/share/text/SMICopyright.html">Legal Terms</a>. 
        <A HREF="http://www.sun.com/privacy/">Privacy&nbsp;Policy</A>.
        </FONT>
        </TD>
      </TR>
    </TABLE>
	
    </TD>
  </TR> 
</TABLE>
</FORM>

<!-- End Copyright Insert -->


</BODY>
</HTML>