simplesqlclient.java

来自「JAVA用jdbc联接ORACLE的例子。」· Java 代码 · 共 157 行

package examples.jdbc.oracle;

import java.sql.Driver;
import java.sql.SQLException;
import java.util.Properties;

/**
 * This simple example shows how to drop and create tables and add and delete rows using a 
 * Statement and standard SQL via JDBC; Run this example first.
 * <p>
 * This example is run on the command line and is output to
 * System.out.
 * <p><h3>Build the Example</h3>  
 * <ol>
 * <li>In the <font face="Courier New" size = -1>%SAMPLES_HOME%\server\examples\src\examples.properties</font>
 * file, specify a value for the following properties:
 * <ul>
 * <li>sid - The Oracle System Identifier (SID) of the database. This service must be listed in your tnsnames.ora file.
 * <li>db.user - The user name to use to connect to the database and create
 * tables.
 * <li>db.password - The password to use when connecting to the database.
 * </ul>
 * <p>The ant scripts for building and running the example use properties set
 * in this file.
 * <p>
 * <li> Open a new command shell.
 * <p>
 * <li>Set up this development shell as described in
 * <a href=../../examples.html#environment>Setting up Your Environment for
 * Building and Running the Examples</a>.
 * <p>
 * <li> Change to the <font face="Courier New" size = -1>%SAMPLES_HOME%\server\examples\src\examples\jdbc\oracle</font> directory.
 * <p>
 * 
 * <p>
 * <li>Compile the example by executing the following command:
 * <p>
 * <pre>  prompt&gt;<b> ant</b></pre> 
 * <p>
 * <p><b>Note:</b> This script builds all of the examples in the oracle
 * directory.
 * </ol>
 * <p><h3>Run the Example</h3>
 * <p>
 * Execute the following command in your development shell: 
 * 
 * <pre>  prompt&gt;<b> ant run.simple.sql</b></pre>
 * <p>
 * <p>You should see the following results:
 * <pre>
 * Buildfile: build.xml
 * 
 * simple.sql:
 *      [java] Starting Loading Oracle thin driver .....
 *      [java] Table empdemo doesn't need to be dropped.
 *      [java] Table empdemo created.
 *      [java] Number of rows inserted = 1
 *      [java] Number of rows deleted = 1
 * 
 * BUILD SUCCESSFUL
 * </pre>
 * <p>
 * <h3>There's More</h3>
 *
 * <p>For more information about WebLogic JDBC, see
 * <a href="http://e-docs.bea.com/wls/docs81/jdbc/index.html"><i>Programming WebLogic JDBC</i></a>.
 * <p>
 * @author Copyright (c) 1996-2004 by BEA Systems, Inc.  All Rights Reserved.
 */
public class SimpleSqlClient
{
  public static void main(String argv[]) throws Exception {
    String user = "scott";
    String password = "tiger";
    String server = "DEMO";

    try {
      for (int i = 0; i < argv.length; i++) 
      {
        if (argv[i].equals("-user")) {
          i++; 
          user = (argv[i].equals("null") ? "" : argv[i]);
        } 
        else if (argv[i].equals("-password")) {
          i++; 
          password = (argv[i].equals("null") ? "" : argv[i]);
        }
        else if (argv[i].equals("-server")) {
          i++; 
          server = (argv[i].equals("null") ? "" : argv[i]);
        }
      }
    } catch(ArrayIndexOutOfBoundsException aiobe) {
      System.err.println("\nUsage: java examples.jdbc.oracle.SimpleSqlClient [options] \n\n" +
                         "where options include:\n" +
                         "    -user <user>            User name to be passed to database.\n" +
                         "    -password <password>    User password to be passed to database.\n" +
                         "    -server <server>        DNS name of database server.\n"); 
      return;
    }

    // Execute example
    run(user, password, server);
  }

  private static void run(String user, String password, String server)
    throws Exception
  {
    java.sql.Connection conn = null;
    java.sql.Statement stmt  = null;

    Properties props = new Properties();
    props.put("user",     user);
    props.put("password", password);
    props.put("server",   server);

    try {
      Driver myDriver = (Driver)Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
      conn = myDriver.connect("jdbc:oracle:thin:", props);
      stmt = conn.createStatement();
    
      try {
        stmt.execute("drop table empdemo");
        System.out.println("Table empdemo dropped.");
      }
      catch (SQLException e) {
        System.out.println("Table empdemo doesn't need to be dropped.");
      }
    
      stmt.execute("create table empdemo (empid integer, name varchar2(30), dept number(4))");
      System.out.println("Table empdemo created.");
    
      int numrows = stmt.executeUpdate("insert into empdemo values (0, 'John Smith', 12)");
      System.out.println("Number of rows inserted = " + numrows);
    
      numrows = stmt.executeUpdate("delete from empdemo where empid = 0");
      System.out.println("Number of rows deleted = " + numrows);
    } 
    catch (Exception ex) {
      System.out.println("Exception was thrown: " + ex.getMessage());
      throw ex;
    }
    finally { //close connections and statements in a finally block
      try {
        if(stmt != null)
          stmt.close();
        if(conn != null)
          conn.close();
      } 
      catch (SQLException sqle) {
        System.out.println("SQLException was thrown: " + sqle.getMessage());
        throw sqle;
      }
    }
  }
}