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());
 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 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);
 }
 }
}

工具类:

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 = conn.getMetaData();
 RowIdLifetime lifetime = dbMetaData.getRowIdLifetime();
 switch (lifetime) {
 case ROWID_UNSUPPORTED:
 System.out.println("ROWID type not supported");
 break;
 case ROWID_VALID_FOREVER:
 System.out.println("ROWID has unlimited lifetime");
 break;
 case ROWID_VALID_OTHER:
 System.out.println("ROWID has indeterminate lifetime");
 break;
 case ROWID_VALID_SESSION: 
 System.out.println("ROWID type has lifetime that is valid for at least the containing session");
 break;
 case ROWID_VALID_TRANSACTION:
 System.out.println("ROWID type has lifetime that is valid for at least the containing transaction");
 }
 }
 
 

 public static void cursorHoldabilitySupport(Connection conn) throws SQLException {
 DatabaseMetaData dbMetaData = conn.getMetaData();
 System.out.println("ResultSet.HOLD_CURSORS_OVER_COMMIT = " +
 ResultSet.HOLD_CURSORS_OVER_COMMIT);//事务提交时,结果集对象是否关闭
 System.out.println("ResultSet.CLOSE_CURSORS_AT_COMMIT = " +
 ResultSet.CLOSE_CURSORS_AT_COMMIT);
 System.out.println("Default cursor holdability: " +
 dbMetaData.getResultSetHoldability());//默认的要看数据库实现
 System.out.println("Supports HOLD_CURSORS_OVER_COMMIT? " +
 dbMetaData.supportsResultSetHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT));
 System.out.println("Supports CLOSE_CURSORS_AT_COMMIT? " +
 dbMetaData.supportsResultSetHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT));
 }

 public JDBCTutorialUtilities(String propertiesFileName) throws FileNotFoundException,
 IOException,
 InvalidPropertiesFormatException {
 super();
 this.setProperties(propertiesFileName);
 }

 public static void getWarningsFromResultSet(ResultSet rs) throws SQLException {
 JDBCTutorialUtilities.printWarnings(rs.getWarnings());//要想处理Warning要先rs.getWarnings()
 }

 public static void getWarningsFromStatement(Statement stmt) throws SQLException {
 JDBCTutorialUtilities.printWarnings(stmt.getWarnings());//或者stmt.getWarnings()
 } 

 public static void printWarnings(SQLWarning warning) throws SQLException {//SQLWarning的处理
 if (warning != null) {
 System.out.println("\n---Warning---\n");
 while (warning != null) {
 System.out.println("Message: " + warning.getMessage());
 System.out.println("SQLState: " + warning.getSQLState());
 System.out.print("Vendor error code: ");
 System.out.println(warning.getErrorCode());
 System.out.println("");
 warning = warning.getNextWarning();//如果有多个警告
 }
 }
 }

 public static boolean ignoreSQLException(String sqlState) {//排除两个情况
 if (sqlState == null) {
 System.out.println("The SQL state is not defined!");
 return false;
 }
 // X0Y32: Jar file already exists in schema
 if (sqlState.equalsIgnoreCase("X0Y32"))
 return true;
 // 42Y55: Table already exists in schema
 if (sqlState.equalsIgnoreCase("42Y55"))
 return true;
 return false;
 }

 public static void printBatchUpdateException(BatchUpdateException b) {
 System.err.println("----BatchUpdateException----");
 System.err.println("SQLState: " + b.getSQLState());
 System.err.println("Message: " + b.getMessage());
 System.err.println("Vendor: " + b.getErrorCode());
 System.err.print("Update counts: ");
 int[] updateCounts = b.getUpdateCounts();//批语句的异常会有个数量统计
 for (int i = 0; i < updateCounts.length; i++) {
 System.err.print(updateCounts[i] + " ");
 }
 }

 public static void printSQLException(SQLException ex) {//SQLException的处理
 for (Throwable e : ex) {
 if (e instanceof SQLException) {
 if (ignoreSQLException(((SQLException)e).getSQLState()) == false) {
 e.printStackTrace(System.err);
 System.err.println("SQLState: " + ((SQLException)e).getSQLState());//状态代码
 System.err.println("Error Code: " + ((SQLException)e).getErrorCode());//错误代码
 System.err.println("Message: " + e.getMessage());
 Throwable t = ex.getCause();
 while (t != null) {
 System.out.println("Cause: " + t);
 t = t.getCause();
 }
 }
 }
 }
 }

 public static void alternatePrintSQLException(SQLException ex) {
 while (ex != null) {
 System.err.println("SQLState: " + ex.getSQLState());
 System.err.println("Error Code: " + ex.getErrorCode());
 System.err.println("Message: " + ex.getMessage());
 Throwable t = ex.getCause();
 while (t != null) {
 System.out.println("Cause: " + t);
 t = t.getCause();
 }
 ex = ex.getNextException();//如果有多个错误
 }
 }

 private void setProperties(String fileName) throws FileNotFoundException,
 IOException,
 InvalidPropertiesFormatException {
 this.prop = new Properties();
 FileInputStream fis = new FileInputStream(fileName);
 prop.loadFromXML(fis);

 this.dbms = this.prop.getProperty("dbms");
 this.jarFile = this.prop.getProperty("jar_file");
 this.driver = this.prop.getProperty("driver");
 this.dbName = this.prop.getProperty("database_name");
 this.userName = this.prop.getProperty("user_name");
 this.password = this.prop.getProperty("password");
 this.serverName = this.prop.getProperty("server_name");
 this.portNumber = Integer.parseInt(this.prop.getProperty("port_number"));

 System.out.println("Set the following properties:");
 System.out.println("dbms: " + dbms);
 System.out.println("driver: " + driver);
 System.out.println("dbName: " + dbName);
 System.out.println("userName: " + userName);
 System.out.println("serverName: " + serverName);
 System.out.println("portNumber: " + portNumber);

 }

 public Connection getConnectionToDatabase() throws SQLException {
 {
 Connection conn = null;
 Properties connectionProps = new Properties();
 connectionProps.put("user", this.userName);
 connectionProps.put("password", this.password);

 // Using a driver manager:

 if (this.dbms.equals("mysql")) {
// DriverManager.registerDriver(new com.mysql.jdbc.Driver());
 conn =
 DriverManager.getConnection("jdbc:" + dbms + "://" + serverName +
 ":" + portNumber + "/" + dbName,
 connectionProps);
 conn.setCatalog(this.dbName);
 } else if (this.dbms.equals("derby")) {
// DriverManager.registerDriver(new org.apache.derby.jdbc.EmbeddedDriver());
 conn =
 DriverManager.getConnection("jdbc:" + dbms + ":" + dbName, connectionProps);
 }
 System.out.println("Connected to database");
 return conn;
 }
 }

 public Connection getConnection() throws SQLException {//获取数据库连接
 Connection conn = null;
 Properties connectionProps = new Properties();
 connectionProps.put("user", this.userName);
 connectionProps.put("password", this.password);
 
 String currentUrlString = null;//JDBC4.0以前,要手动Class.forName(...),现在不用了,驱动包里有配置好的路径,会自动加载的

 if (this.dbms.equals("mysql")) {
 currentUrlString = "jdbc:" + this.dbms + "://" + this.serverName +
 ":" + this.portNumber + "/";
 conn =
 DriverManager.getConnection(currentUrlString,
 connectionProps);//得到连接
 
 this.urlString = currentUrlString + this.dbName;
 conn.setCatalog(this.dbName);//设置目前数据库
 } else if (this.dbms.equals("derby")) {
 this.urlString = "jdbc:" + this.dbms + ":" + this.dbName;
 
 conn =
 DriverManager.getConnection(this.urlString + 
 ";create=true", connectionProps);
 
 }
 System.out.println("Connected to database");
 return conn;
 }

 public Connection getConnection(String userName,
 String password) throws SQLException {
 Connection conn = null;
 Properties connectionProps = new Properties();
 connectionProps.put("user", userName);
 connectionProps.put("password", password);
 if (this.dbms.equals("mysql")) {
 conn =
 DriverManager.getConnection("jdbc:" + this.dbms + "://" + this.serverName +
 ":" + this.portNumber + "/",
 connectionProps);
 conn.setCatalog(this.dbName);
 } else if (this.dbms.equals("derby")) {
 conn =
 DriverManager.getConnection("jdbc:" + this.dbms + ":" + this.dbName +
 ";create=true", connectionProps);
 }
 return conn;
 }


 public static void createDatabase(Connection connArg, String dbNameArg,
 String dbmsArg) {

 if (dbmsArg.equals("mysql")) {
 try {
 Statement s = connArg.createStatement();
 String newDatabaseString =
 "CREATE DATABASE IF NOT EXISTS " + dbNameArg;
 // String newDatabaseString = "CREATE DATABASE " + dbName;
 s.executeUpdate(newDatabaseString);

 System.out.println("Created database " + dbNameArg);
 } catch (SQLException e) {
 printSQLException(e);
 }
 }
 }

 public static void closeConnection(Connection connArg) {
 System.out.println("Releasing all open resources ...");
 try {
 if (connArg != null) {
 connArg.close();
 connArg = null;
 }
 } catch (SQLException sqle) {
 printSQLException(sqle);
 }
 }
 
 public static String convertDocumentToString(Document doc) throws TransformerConfigurationException,
 TransformerException {
 Transformer t = TransformerFactory.newInstance().newTransformer();
// t.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
 StringWriter sw = new StringWriter();
 t.transform(new DOMSource(doc), new StreamResult(sw));
 return sw.toString();
 
 
 }

 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 {
 System.out.println("Reading properties file " + args[0]);
 myJDBCTutorialUtilities = new JDBCTutorialUtilities(args[0]);
 } catch (Exception e) {
 System.err.println("Problem reading properties file " + args[0]);
 e.printStackTrace();
 return;
 }
 }

 try {
 myConnection = myJDBCTutorialUtilities.getConnection();
 // JDBCTutorialUtilities.outputClientInfoProperties(myConnection);
 // myConnection = myJDBCTutorialUtilities.getConnection("root", "root", "jdbc:mysql://localhost:3306/");
 // myConnection = myJDBCTutorialUtilities.
 // getConnectionWithDataSource(myJDBCTutorialUtilities.dbName,"derby","", "", "localhost", 3306);

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

 JDBCTutorialUtilities.cursorHoldabilitySupport(myConnection);
 JDBCTutorialUtilities.rowIdLifetime(myConnection);

 } catch (SQLException e) {
 JDBCTutorialUtilities.printSQLException(e);
 } catch (Exception e) {
 e.printStackTrace(System.err);
 } finally {
 JDBCTutorialUtilities.closeConnection(myConnection);
 }

 }
}

?

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,连接池,分布事务(略)

?

?

?

?

?

?