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.Savepoi
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 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 e : salesForWeek.entrySet()) { updateSales.setInt(1, e.getValue().intValue()); updateSales.setString(2, e.getKey());//可设置参数,clearParameters可以清空所有参数 updateSales.executeUpdate(); updateTotal.setInt(1, e.getValue().intValue()); upda<em>本文来源gao.dai.ma.com搞@代*码(网$</em>teTotal.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 getKeys() throws SQLException { HashSet keys = new HashSet(); 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 salesCoffeeWeek = new HashMap(); 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); } }}
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：Trail: JDBC(TM) Database Access(1)
转载请注明原文链接：Trail: JDBC(TM) Database Access(1)
