155.Oracle数据库SQL开发之 JAVA——示例程序1

155.Oracle数据库SQL开发之 JAVA——示例程序1

欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/50185977

1.  代码如下：

import java.sql.*;

 

public class BasicExample1 {

  public staticvoid main (String args []) {

 

    // declareConnection and Statement objects

    ConnectionmyConnection = null;

    StatementmyStatement = null;

 

    try {

 

      //register the Oracle JDBC drivers

     DriverManager.registerDriver(

        neworacle.jdbc.OracleDriver()

      );

 

      // EDITAS NECESSARY TO CONNECT TO YOUR DATABASE

      // createa Connection object, and connect to the database

      // as thestore user using the Oracle JDBC Thin driver

     myConnection = DriverManager.getConnection(

       "jdbc:oracle:thin:@localhost:1521:ORCL",

       "store",

       "store_password"

      );

 

      //disable auto-commit mode

      myConnection.setAutoCommit(false);

 

      // createa Statement object

     myStatement = myConnection.createStatement();

 

      // createvariables and objects used to represent

      // columnvalues

      intcustomerId = 6;

      StringfirstName = "Jason";

      StringlastName = "Red";

     java.sql.Date dob = java.sql.Date.valueOf("1969-02-22");

     java.sql.Time dobTime;

     java.sql.Timestamp dobTimestamp;

      Stringphone = "800-555-1216";

 

      //perform SQL INSERT statement to add a new row to the

      //customers table using the values set in the previous

      // step -the executeUpdate() method of the Statement

      // objectis used to perform the INSERT

     myStatement.executeUpdate(

       "INSERT INTO customers " +

       "(customer_id, first_name, last_name, dob, phone) VALUES (" +

         customerId + ", '" + firstName + "', '" + lastName +"', " +

       "TO_DATE('" + dob + "', 'YYYY, MM, DD'), '" + phone+ "')"

      );

     System.out.println("Added row to customers table");

 

      //perform SQL UPDATE statement to modify the first_name

      // columnof customer #1

      firstName= "Jean";

     myStatement.executeUpdate(

       "UPDATE customers " +

       "SET first_name = '" + firstName + "' " +

       "WHERE customer_id = 1"

      );

     System.out.println("Updated row in customers table");

 

      //perform SQL DELETE statement to remove customer #5

     myStatement.executeUpdate(

       "DELETE FROM customers " +

       "WHERE customer_id = 5"

      );

     System.out.println("Deleted row from customers table");

 

      ResultSetcustomerResultSet = myStatement.executeQuery(

       "SELECT customer_id, first_name, last_name, dob, phone " +

       "FROM customers"

      );

      System.out.println("Retrievedrows from customers table");

 

      // loopthrough the rows in the ResultSet object using the

      // next()method, and use the get methods to read the values

      //retrieved from the database columns

      while(customerResultSet.next()) {

       customerId = customerResultSet.getInt("customer_id");

       firstName = customerResultSet.getString("first_name");

       lastName = customerResultSet.getString("last_name");

        dob =customerResultSet.getDate("dob");

        dobTime= customerResultSet.getTime("dob");

       dobTimestamp = customerResultSet.getTimestamp("dob");

        phone =customerResultSet.getString("phone");

 

       System.out.println("customerId = " + customerId);

       System.out.println("firstName = " + firstName);

       System.out.println("lastName = " + lastName);

       System.out.println("dob = " + dob);

       System.out.println("dobTime = " + dobTime);

       System.out.println("dobTimestamp = " + dobTimestamp);

        System.out.println("phone= " + phone);

      } // endof while loop

 

      // closethe ResultSet object using the close() method

     customerResultSet.close();

 

      //rollback the changes made to the database

     myConnection.rollback();

 

      // createnumeric variables to store the product_id and price columns

      shortproductIdShort;

      intproductIdInt;

      longproductIdLong;

      floatpriceFloat;

      doublepriceDouble;

     java.math.BigDecimal priceBigDec;

     ResultSetproductResultSet = myStatement.executeQuery(

       "SELECT product_id, product_type_id, price " +

       "FROM products " +

       "WHERE product_id = 12"

      );

     System.out.println("Retrieved row from products table");

 

      while (productResultSet.next()){

       System.out.println("product_id = " +

         productResultSet.getInt("product_id"));

       System.out.println("product_type_id = " +

         productResultSet.getInt("product_type_id"));

 

        //check if the value just read by the get method was NULL

        if(productResultSet.wasNull()) {

         System.out.println("Last value read was NULL");

        }

 

        // usethe getObject() method to read the value, and convert it

        // to awrapper object - this converts a database NULL value to a

        // Javanull value

       java.lang.Integer productTypeId =

         (java.lang.Integer)productResultSet.getObject("product_type_id");

       System.out.println("productTypeId = " + productTypeId);

 

        // retrieve the product_id and price columnvalues into

        // thevarious numeric variables created earlier

       productIdShort = productResultSet.getShort("product_id");

       productIdInt = productResultSet.getInt("product_id");

        productIdLong= productResultSet.getLong("product_id");

       priceFloat = productResultSet.getFloat("price");

       priceDouble = productResultSet.getDouble("price");

       priceBigDec = productResultSet.getBigDecimal("price");

        System.out.println("productIdShort= " + productIdShort);

       System.out.println("productIdInt = " + productIdInt);

       System.out.println("productIdLong = " + productIdLong);

       System.out.println("priceFloat = " + priceFloat);

        System.out.println("priceDouble= " + priceDouble);

       System.out.println("priceBigDec = " + priceBigDec);

      } // endof while loop

     productResultSet.close();

 

      //perform SQL DDL CREATE TABLE statement to create a new table

      // thatmay be used to store customer addresses

     myStatement.execute(

       "CREATE TABLE addresses (" +

       "  address_id INTEGERCONSTRAINT addresses_pk PRIMARY KEY," +

       "  customer_id INTEGERCONSTRAINT addresses_fk_customers " +

       "    REFERENCES customers(customer_id)," +

       "  street VARCHAR2(20) NOTNULL," +

       "  city VARCHAR2(20) NOTNULL," +

       "  state CHAR(2) NOTNULL" +

       ")"

      );

     System.out.println("Created addresses table");

 

      // dropthis table using the SQL DDL DROP TABLE statement

     myStatement.execute("DROP TABLE addresses");

     System.out.println("Dropped addresses table");

 

    } catch(SQLException e) {

 

     System.out.println("Error code = " + e.getErrorCode());

      System.out.println("Errormessage = " + e.getMessage());

     System.out.println("SQL state = " + e.getSQLState());

     e.printStackTrace();

 

    } finally {

 

      try {

 

        //close the Statement object using the close() method

        if(myStatement != null) {

         myStatement.close();

        }

 

        //close the Connection object using the close() method

        if(myConnection != null) {

         myConnection.close();

        }

      } catch(SQLException e) {

       e.printStackTrace();

      }

    }

  } // end ofmain()

}

2.  运行输出如下：

Added row to customers table

Updated row in customers table

Deleted row from customers table

Retrieved rows from customers table

customerId = 1

firstName = Jean

lastName = Brown

dob = 1965-01-01

dobTime = 00:00:00

dobTimestamp = 1965-01-01 00:00:00.0

phone = 800-555-1211

customerId = 2

firstName = Cynthia

lastName = Green

dob = 1968-02-05

dobTime = 00:00:00

dobTimestamp = 1968-02-05 00:00:00.0

phone = 800-555-1212

customerId = 3

firstName = Steve

lastName = White

dob = 1971-03-16

dobTime = 00:00:00

dobTimestamp = 1971-03-16 00:00:00.0

phone = 800-555-1213

customerId = 4

firstName = Gail

lastName = Black

dob = null

dobTime = null

dobTimestamp = null

phone = 800-555-1214

customerId = 6

firstName = Jason

lastName = Red

dob = 1969-02-22

dobTime = 00:00:00

dobTimestamp = 1969-02-22 00:00:00.0

phone = 800-555-1216

Retrieved row from products table

product_id = 12

product_type_id = 0

Last value read was NULL

productTypeId = null

productIdShort = 12

productIdInt = 12

productIdLong = 12

priceFloat = 13.49

priceDouble = 13.49

priceBigDec = 13.49

Created addresses table

Dropped addresses table