首页 > 代码库 > 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,连接池,分布事务(略)
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。