首页 > 代码库 > Trail: JDBC(TM) Database Access1

Trail: JDBC(TM) Database Access1

package com.oracle.tutorial.jdbc;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;

import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

public class CoffeesTable {

  private String dbName;
  private Connection con;
  private String dbms;


  public CoffeesTable(Connection connArg, String dbNameArg, String dbmsArg) {
    super();
    this.con = connArg;
    this.dbName = dbNameArg;
    this.dbms = dbmsArg;

  }

  public void createTable() throws SQLException {
    String createString =
      "create table COFFEES " + "(COF_NAME varchar(32) NOT NULL, " +
      "SUP_ID int NOT NULL, " + "PRICE numeric(10,2) NOT NULL, " +
      "SALES integer NOT NULL, " + "TOTAL integer NOT NULL, " +
      "PRIMARY KEY (COF_NAME), " +
      "FOREIGN KEY (SUP_ID) REFERENCES SUPPLIERS (SUP_ID))";
    Statement stmt = null;
    try {
      stmt = con.createStatement();
      stmt.executeUpdate(createString);
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) { stmt.close(); }
    }
  }

  public void populateTable() throws SQLException {
    Statement stmt = null;
    try {
      stmt = con.createStatement();
      stmt.executeUpdate("insert into COFFEES " +
                         "values('Colombian', 00101, 7.99, 0, 0)");
      stmt.executeUpdate("insert into COFFEES " +
                         "values('French_Roast', 00049, 8.99, 0, 0)");
      stmt.executeUpdate("insert into COFFEES " +
                         "values('Espresso', 00150, 9.99, 0, 0)");
      stmt.executeUpdate("insert into COFFEES " +
                         "values('Colombian_Decaf', 00101, 8.99, 0, 0)");
      stmt.executeUpdate("insert into COFFEES " +
                         "values('French_Roast_Decaf', 00049, 9.99, 0, 0)");
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) { stmt.close(); }
    }
  }


  public void updateCoffeeSales(HashMap<String, Integer> salesForWeek) throws SQLException {

    PreparedStatement updateSales = null;
    PreparedStatement updateTotal = null;

    String updateString =
      "update COFFEES " + "set SALES = ? where COF_NAME = ?";

    String updateStatement =
      "update COFFEES " + "set TOTAL = TOTAL + ? where COF_NAME = ?";//?是预留的参数位置,字串类型也不用单引号

    try {
      con.setAutoCommit(false);//一个简单事务的演示
      updateSales = con.prepareStatement(updateString);//预编译语句相当于java端的存储过程
      updateTotal = con.prepareStatement(updateStatement);

      for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {
        updateSales.setInt(1, e.getValue().intValue());
        updateSales.setString(2, e.getKey());//可设置参数,clearParameters可以清空所有参数
        updateSales.executeUpdate();

        updateTotal.setInt(1, e.getValue().intValue());
        updateTotal.setString(2, e.getKey());
        updateTotal.executeUpdate();//预编译语句提交后返回值为0有两种可能:更新了0行,或者是DDL
        con.commit();//提交此事务
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
      if (con != null) {
        try {
          System.err.print("Transaction is being rolled back");
          con.rollback();
        } catch (SQLException excep) {
          JDBCTutorialUtilities.printSQLException(excep);
        }
      }
    } finally {
      if (updateSales != null) { updateSales.close(); }
      if (updateTotal != null) { updateTotal.close(); }
      con.setAutoCommit(true);
    }
  }

  public void modifyPrices(float percentage) throws SQLException {
    Statement stmt = null;
    try {
      stmt =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
//TYPE_SCROLL_SENSITIVE可以双向移动,而且底层修改会反映到结果集
//CONCUR_UPDATABLE是说结果集可更新
      ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");

      while (uprs.next()) {
        float f = uprs.getFloat("PRICE");
        uprs.updateFloat("PRICE", f * percentage);//可以更改某列
        uprs.updateRow();//然后直接更新此行到数据库
      }

    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) { stmt.close(); }
    }
  }


  public void modifyPricesByPercentage(String coffeeName, float priceModifier,
                                       float maximumPrice) throws SQLException {
    con.setAutoCommit(false);

    Statement getPrice = null;
    Statement updatePrice = null;
    ResultSet rs = null;
    String query =
      "SELECT COF_NAME, PRICE FROM COFFEES " + "WHERE COF_NAME = '" +
      coffeeName + "'";

    try {
      Savepoint save1 = con.setSavepoint();//设置一个回滚点
      getPrice =
          con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);//TYPE_SCROLL_INSENSITIVE可以双向移动,但数据库底层的修改不会反应上来
      updatePrice = con.createStatement();

      if (!getPrice.execute(query)) {
        System.out.println("Could not find entry for coffee named " +
                           coffeeName);
      } else {
        rs = getPrice.getResultSet();
        rs.first();//移动到第一行
        float oldPrice = rs.getFloat("PRICE");
        float newPrice = oldPrice + (oldPrice * priceModifier);
        System.out.println("Old price of " + coffeeName + " is " + oldPrice);
        System.out.println("New price of " + coffeeName + " is " + newPrice);
        System.out.println("Performing update...");
        updatePrice.executeUpdate("UPDATE COFFEES SET PRICE = " + newPrice +
                                  " WHERE COF_NAME = '" + coffeeName + "'");
        System.out.println("\nCOFFEES table after update:");
        CoffeesTable.viewTable(con);
        if (newPrice > maximumPrice) {
          System.out.println("\nThe new price, " + newPrice +
                             ", is greater than the maximum " + "price, " +
                             maximumPrice +
                             ". Rolling back the transaction...");
          con.rollback(save1);//回滚到某个点,自动让后面的回滚点失效
          System.out.println("\nCOFFEES table after rollback:");
          CoffeesTable.viewTable(con);
        }
        con.commit();//提交或完全回滚时,所有回滚点自动失效,也可以提前手动Connection.releaseSavepoint(save1)
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (getPrice != null) { getPrice.close(); }
      if (updatePrice != null) { updatePrice.close(); }
      con.setAutoCommit(true);
    }
  }


  public void insertRow(String coffeeName, int supplierID, float price,
                        int sales, int total) throws SQLException {
    Statement stmt = null;
    try {
      stmt =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
//TYPE_SCROLL_SENSITIVE是默认值,光标只能向前移动,
//CONCUR_READ_ONLY也是默认值,结果集不能更新数据到底层
      ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");

      uprs.moveToInsertRow();//可以再结果集中插入新行,可更新的结果集会多一个空间,来存放新插入的行 

      uprs.updateString("COF_NAME", coffeeName);
      uprs.updateInt("SUP_ID", supplierID);
      uprs.updateFloat("PRICE", price);
      uprs.updateInt("SALES", sales);
      uprs.updateInt("TOTAL", total);//先设置每一列 

      uprs.insertRow();//再插入此行到数据库,但之后必须移动光标,不要再指向这个插入行
      uprs.beforeFirst();//移动到初始位置,第一行之前,但CONCUR_READ_ONLY下只能调用next(),别的移动都不行

    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) { stmt.close(); }
    }
  }

  public void batchUpdate() throws SQLException {

    Statement stmt = null;
    try {

      this.con.setAutoCommit(false);//一个批更新语句的演示,推荐放在一个事务里,关闭自动提交也有利于异常的捕获
      stmt = this.con.createStatement();

      stmt.addBatch("INSERT INTO COFFEES " +
                    "VALUES('Amaretto', 49, 9.99, 0, 0)");
      stmt.addBatch("INSERT INTO COFFEES " +
                    "VALUES('Hazelnut', 49, 9.99, 0, 0)");
      stmt.addBatch("INSERT INTO COFFEES " +
                    "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
      stmt.addBatch("INSERT INTO COFFEES " +
                    "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");

      int[] updateCounts = stmt.executeBatch();//提交后会自动清空所有语句,也可以手动clearBatch()
      this.con.commit();

    } catch (BatchUpdateException b) {//要先捕获这个批异常
      JDBCTutorialUtilities.printBatchUpdateException(b);
    } catch (SQLException ex) {
      JDBCTutorialUtilities.printSQLException(ex);
    } finally {
      if (stmt != null) { stmt.close(); }
      this.con.setAutoCommit(true);//不要忘了恢复
    }
  }
  
  public static void viewTable(Connection con) throws SQLException {//一个最简单的示例
    Statement stmt = null;
    String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";
    try {
      stmt = con.createStatement();//简单语句 
      ResultSet rs = stmt.executeQuery(query);

      while (rs.next()) {//遍历结果集,结果集指针初始位置是第一行之前,要调用.next()才能使用
        String coffeeName = rs.getString("COF_NAME");
        int supplierID = rs.getInt("SUP_ID");
        float price = rs.getFloat("PRICE");
        int sales = rs.getInt("SALES");
        int total = rs.getInt("TOTAL");
        System.out.println(coffeeName + ", " + supplierID + ", " + price +
                           ", " + sales + ", " + total);
      }

    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) { stmt.close(); }//关闭语句对象
    }
  }

  public static void alternateViewTable(Connection con) throws SQLException {
    Statement stmt = null;
    String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";
    try(Statement stmt = con.createStatement()) {//JDK7新功能,在try后的括号里声明的资源会保证关闭,不用写finally
      stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      while (rs.next()) {
        String coffeeName = rs.getString(1);
        int supplierID = rs.getInt(2);
        float price = rs.getFloat(3);
        int sales = rs.getInt(4);
        int total = rs.getInt(5);
        System.out.println(coffeeName + ", " + supplierID + ", " + price +
                           ", " + sales + ", " + total);
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    }//无需finally
  }
  
  public Set<String> getKeys() throws SQLException {
    HashSet<String> keys = new HashSet<String>();
    Statement stmt = null;
    String query = "select COF_NAME from COFFEES";
    try {
      stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      while (rs.next()) {
        keys.add(rs.getString(1));
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) { stmt.close(); }
    }
    return keys;
    
  }


  public void dropTable() throws SQLException {
    Statement stmt = null;
    try {
      stmt = con.createStatement();
      if (this.dbms.equals("mysql")) {
        stmt.executeUpdate("DROP TABLE IF EXISTS COFFEES");
      } else if (this.dbms.equals("derby")) {
        stmt.executeUpdate("DROP TABLE COFFEES");
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) { stmt.close(); }
    }
  }

  public static void main(String[] args) {
    JDBCTutorialUtilities myJDBCTutorialUtilities;
    Connection myConnection = null;

    if (args[0] == null) {
      System.err.println("Properties file not specified at command line");
      return;
    } else {
      try {
        myJDBCTutorialUtilities = new JDBCTutorialUtilities(args[0]);
      } catch (Exception e) {
        System.err.println("Problem reading properties file " + args[0]);
        e.printStackTrace();
        return;
      }
    }

    try {
      myConnection = myJDBCTutorialUtilities.getConnection();

      // Java DB does not have an SQL create database command; it does require createDatabase
//      JDBCTutorialUtilities.createDatabase(myConnection,
//                                           myJDBCTutorialUtilities.dbName,
//                                           myJDBCTutorialUtilities.dbms);
//
//      JDBCTutorialUtilities.initializeTables(myConnection,
//                                             myJDBCTutorialUtilities.dbName,
//                                             myJDBCTutorialUtilities.dbms);

      CoffeesTable myCoffeeTable =
        new CoffeesTable(myConnection, myJDBCTutorialUtilities.dbName,
                         myJDBCTutorialUtilities.dbms);

      System.out.println("\nContents of COFFEES table:");
      CoffeesTable.viewTable(myConnection);

      System.out.println("\nRaising coffee prices by 25%");
      myCoffeeTable.modifyPrices(1.25f);

      System.out.println("\nInserting a new row:");
      myCoffeeTable.insertRow("Kona", 150, 10.99f, 0, 0);
      CoffeesTable.viewTable(myConnection);

      System.out.println("\nUpdating sales of coffee per week:");
      HashMap<String, Integer> salesCoffeeWeek =
        new HashMap<String, Integer>();
      salesCoffeeWeek.put("Colombian", 175);
      salesCoffeeWeek.put("French_Roast", 150);
      salesCoffeeWeek.put("Espresso", 60);
      salesCoffeeWeek.put("Colombian_Decaf", 155);
      salesCoffeeWeek.put("French_Roast_Decaf", 90);
      myCoffeeTable.updateCoffeeSales(salesCoffeeWeek);
      CoffeesTable.viewTable(myConnection);

      System.out.println("\nModifying prices by percentage");

      myCoffeeTable.modifyPricesByPercentage("Colombian", 0.10f, 9.00f);
      
      System.out.println("\nCOFFEES table after modifying prices by percentage:");
      
      myCoffeeTable.viewTable(myConnection);

      System.out.println("\nPerforming batch updates; adding new coffees");
      myCoffeeTable.batchUpdate();
      myCoffeeTable.viewTable(myConnection);

//      System.out.println("\nDropping Coffee and Suplliers table:");
//      
//      myCoffeeTable.dropTable();
//      mySuppliersTable.dropTable();

    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      JDBCTutorialUtilities.closeConnection(myConnection);
    }
  }
}


工具类:

package com.oracle.tutorial.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.*;
import java.io.*;
import java.sql.BatchUpdateException;
import java.sql.DatabaseMetaData;
import java.sql.RowIdLifetime;
import java.sql.SQLWarning;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.Document;

public class JDBCTutorialUtilities {

  public String dbms;
  public String jarFile;
  public String dbName; 
  public String userName;
  public String password;
  public String urlString;
  
  private String driver;
  private String serverName;
  private int portNumber;
  private Properties prop;
  
  public static void initializeTables(Connection con, String dbNameArg, String dbmsArg) throws SQLException {
    SuppliersTable mySuppliersTable =
      new SuppliersTable(con, dbNameArg, dbmsArg);
    CoffeesTable myCoffeeTable =
      new CoffeesTable(con, dbNameArg, dbmsArg);
    RSSFeedsTable myRSSFeedsTable = 
      new RSSFeedsTable(con, dbNameArg, dbmsArg);
    ProductInformationTable myPIT =
      new ProductInformationTable(con, dbNameArg, dbmsArg);

    System.out.println("\nDropping exisiting PRODUCT_INFORMATION, COFFEES and SUPPLIERS tables");
    myPIT.dropTable();
    myRSSFeedsTable.dropTable();
    myCoffeeTable.dropTable();
    mySuppliersTable.dropTable();

    System.out.println("\nCreating and populating SUPPLIERS table...");

    System.out.println("\nCreating SUPPLIERS table");
    mySuppliersTable.createTable();
    System.out.println("\nPopulating SUPPLIERS table");
    mySuppliersTable.populateTable();

    System.out.println("\nCreating and populating COFFEES table...");

    System.out.println("\nCreating COFFEES table");
    myCoffeeTable.createTable();
    System.out.println("\nPopulating COFFEES table");
    myCoffeeTable.populateTable();
    
    System.out.println("\nCreating RSS_FEEDS table...");    
    myRSSFeedsTable.createTable();
  }
  
  public static void rowIdLifetime(Connection conn) throws SQLException {
    DatabaseMetaData dbMetaData = http://www.mamicode.com/conn.getMetaData();>


SQL出错的演示:

SQLState: 42Y55
Error Code: 30000
Message: ‘DROP TABLE‘ cannot be performed on
‘TESTDB.COFFEES‘ because it does not exist.

con.setAutoCommit(false);
PreparedStatement pstmt = con.prepareStatement(//用预编译语句也可以写批更新,只是语句是一定的,每次参数可换
                              "INSERT INTO COFFEES VALUES( " +
                              "?, ?, ?, ?, ?)");
pstmt.setString(1, "Amaretto");
pstmt.setInt(2, 49);
pstmt.setFloat(3, 9.99);
pstmt.setInt(4, 0);
pstmt.setInt(5, 0);
pstmt.addBatch();

pstmt.setString(1, "Hazelnut");
pstmt.setInt(2, 49);
pstmt.setFloat(3, 9.99);
pstmt.setInt(4, 0);
pstmt.setInt(5, 0);
pstmt.addBatch();

// ... and so on for each new
// type of coffee

int [] updateCounts = pstmt.executeBatch();
con.commit();
con.setAutoCommit(true);


execute: 用于返回多个 ResultSet 的情况. 反复调用 Statement.getResultSet来得到每个结果集


rs.getString可以用于任何类型,得到的是java的String对象


关于DataSource,连接池,分布事务(略)