java中执行oracle的sql

实例1：

/*
  BasicExample1.java shows how to:
  - import the JDBC packages
  - load the Oracle JDBC drivers
  - connect to a database
  - perform DML statements
  - control transactions
  - use ResultSet objects to retrieve rows
  - use the get methods
  - perform DDL statements
*/

// import the JDBC packages
import java.sql.*;

public class BasicExample1 {
  public static void main (String args []) {

    // declare Connection and Statement objects
    Connection myConnection = null;
    Statement myStatement = null;

    try {

      // register the Oracle JDBC drivers
      DriverManager.registerDriver(
        new oracle.jdbc.OracleDriver()
      );

      // EDIT AS NECESSARY TO CONNECT TO YOUR DATABASE
      // create a Connection object, and connect to the database
      // as the store 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);

      // create a Statement object
      myStatement = myConnection.createStatement();

      // create variables and objects used to represent
      // column values
      int customerId = 6;
      String firstName = "Jason";
      String lastName = "Red";
      java.sql.Date dob = java.sql.Date.valueOf("1969-02-22");
      java.sql.Time dobTime;
      java.sql.Timestamp dobTimestamp;
      String phone = "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
      // object is 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
      // column of 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");

      // create a ResultSet object, and populate it with the
      // result of a SELECT statement that retrieves the
      // customer_id, first_name, last_name, dob, and phone columns
      // for all the rows from the customers table  - the
      // executeQuery() method of the Statement object is used
      // to perform the SELECT
      ResultSet customerResultSet = myStatement.executeQuery(
        "SELECT customer_id, first_name, last_name, dob, phone " +
        "FROM customers"
      );
      System.out.println("Retrieved rows from customers table");

      // loop through 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);
      } // end of while loop

      // close the ResultSet object using the close() method
      customerResultSet.close();

      // rollback the changes made to the database
      myConnection.rollback();

      // create numeric variables to store the product_id and price columns
      short productIdShort;
      int productIdInt;
      long productIdLong;
      float priceFloat;
      double priceDouble;
      java.math.BigDecimal priceBigDec;

      // create another ResultSet object and retrieve the
      // product_id, product_type_id, and price columns for product #12
      // (this row has a NULL value in the product_type_id column)
      ResultSet productResultSet = 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");
        }

        // use the getObject() method to read the value, and convert it
        // to a wrapper object - this converts a database NULL value to a
        // Java null value
        java.lang.Integer productTypeId =
          (java.lang.Integer) productResultSet.getObject("product_type_id");
        System.out.println("productTypeId = " + productTypeId);

        // retrieve the product_id and price column values into
        // the various 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);
      } // end of while loop

      // close the ResultSet object
      productResultSet.close();

      // perform SQL DDL CREATE TABLE statement to create a new table
      // that may be used to store customer addresses
      myStatement.execute(
        "CREATE TABLE addresses (" +
        "  address_id INTEGER CONSTRAINT addresses_pk PRIMARY KEY," +
        "  customer_id INTEGER CONSTRAINT addresses_fk_customers " +
        "    REFERENCES customers(customer_id)," +
        "  street VARCHAR2(20) NOT NULL," +
        "  city VARCHAR2(20) NOT NULL," +
        "  state CHAR(2) NOT NULL" +
        ")"
      );
      System.out.println("Created addresses table");

      // drop this 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("Error message = " + 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 of main()
}

示例2：

/*
  BasicExample2.java shows how to use prepared SQL statements
*/

// import the JDBC packages
import java.sql.*;

class Product {
  int productId;
  int productTypeId;
  String name;
  String description;
  double price;
}

public class BasicExample2 {
  public static void main (String args []) {
    try {
      // register the Oracle JDBC drivers
      DriverManager.registerDriver(
        new oracle.jdbc.OracleDriver()
      );

      // EDIT AS NECESSARY TO CONNECT TO YOUR DATABASE
      // create a Connection object, and connect to the database
      // as the store user using the Oracle JDBC Thin driver
      Connection myConnection = DriverManager.getConnection(
        "jdbc:oracle:thin:@localhost:1521:ORCL",
        "store",
        "store_password"
      );

      // disable auto-commit mode
      myConnection.setAutoCommit(false);

      Product [] productArray = new Product[5];
      for (int counter = 0; counter < productArray.length; counter ++) {
        productArray[counter] = new Product();
        productArray[counter].productId = counter + 13;
        productArray[counter].productTypeId = 1;
        productArray[counter].name = "Test product";
        productArray[counter].description = "Test product";
        productArray[counter].price = 19.95;
      } // end of for loop

      // create a PreparedStatement object
      PreparedStatement myPrepStatement = myConnection.prepareStatement(
        "INSERT INTO products " +
        "(product_id, product_type_id, name, description, price) VALUES (" +
        "?, ?, ?, ?, ?" +
        ")"
      );

      // initialize the values for the new rows using the
      // appropriate set methods
      for (int counter = 0; counter < productArray.length; counter ++) {
        myPrepStatement.setInt(1, productArray[counter].productId);
        myPrepStatement.setInt(2, productArray[counter].productTypeId);
        myPrepStatement.setString(3, productArray[counter].name);
        myPrepStatement.setString(4, productArray[counter].description);
        myPrepStatement.setDouble(5, productArray[counter].price);
        myPrepStatement.execute();
      } // end of for loop

      // close the PreparedStatement object
      myPrepStatement.close();

      // retrieve the product_id, product_type_id, name, description, and
      // price columns for these new rows using a ResultSet object
      Statement myStatement = myConnection.createStatement();
      ResultSet productResultSet = myStatement.executeQuery(
        "SELECT product_id, product_type_id, " +
        "  name, description, price " +
        "FROM products " +
        "WHERE product_id > 12"
      );

      // display the column values
      while (productResultSet.next()) {
        System.out.println("product_id = " +
          productResultSet.getInt("product_id"));
        System.out.println("product_type_id = " +
          productResultSet.getInt("product_type_id"));
        System.out.println("name = " +
          productResultSet.getString("name"));
        System.out.println("description = " +
          productResultSet.getString("description"));
        System.out.println("price = " +
          productResultSet.getDouble("price"));
      } // end of while loop

      // close the ResultSet object using the close() method
      productResultSet.close();

      // rollback the changes made to the database
      myConnection.rollback();

      // close the other JDBC objects
      myStatement.close();
      myConnection.close();

    } catch (SQLException e) {
      System.out.println("Error code = " + e.getErrorCode());
      System.out.println("Error message = " + e.getMessage());
      System.out.println("SQL state = " + e.getSQLState());
      e.printStackTrace();
    }
  } // end of main()
}

实例3：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.util.Date;

public class BasicExample1{
    public static void main(String[] args) {
        String driver = "oracle.jdbc.OracleDriver";    //驱动标识符
        String url = "jdbc:oracle:thin:@localhost:1521:orcl"; //链接字符串
        String user = "scott";         //数据库的用户名
        String password = "tiger";     //数据库的密码
        Connection con = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        boolean flag = false;

        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url,user, password);
            String sql = "select * from emp";
            pstm =con.prepareStatement(sql);
            rs = pstm.executeQuery();
            while(rs.next()) {
                int empno = rs.getInt("empno");
                String ename =rs.getString("ename");
                double sal = rs.getDouble("sal");
                Date hiredate =rs.getDate("hiredate");
                int deptno = rs.getInt(("deptno"));
                System.out.println(empno +"\t"+ ename +"\t"+ sal +"\t"+ hiredate +"\t"+ deptno);
            }

            flag = true;
        } catch(ClassNotFoundException e) {
            e.printStackTrace();
        }
        catch(SQLException e) {
            e.printStackTrace();
        }
        finally {
            if(rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            // 关闭执行通道
            if(pstm !=null) {
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            // 关闭连接通道
            try {
                if(con != null &&(!con.isClosed())) {
                    try {
                        con.close();
                    } catch (SQLException e) {
                       e.printStackTrace();
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(flag) {
            System.out.println("执行成功！");
        } else {
            System.out.println("执行失败！");
        }
    }
}

注意，如果出现oracle.jdbc cannot be resolved to a type，说明没有找到ojdbc

两种方法：
    1 项目上右击--》Build Path--》Configure Build Path,默认应该是在Libraries那个Tab,然后点Add External Jars，找到你的驱动Jar.
    2 直接将你的Jar复制粘贴到项目WEN_INF/lib目录下