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 = 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以前,要手动<code>Class.forName</code>(...),现在不用了,驱动包里有配置好的路径,会自动加载的
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,连接池,分布事务(略)
相关推荐
VS代码的Sourcetrail扩展 此扩展使VS Code可以与进行通信 链接 项目主页，新闻： 说明文件： 下载，评论： 代码，问题： 特征 插件设置 通过上下文菜单将VS Code中的位置发送到Sourcetrail 显示插件是否连接...
1. **项目规模**：小型项目可能更适合轻量级的库，如jQuery，而大型复杂项目则可能需要更强大的框架如Angular。 2. **学习曲线**：对于新接触JavaScript的团队，Vue.js通常因其易学性和直观的API而受到青睐。 3. **...
Trail 是一个协作的实时任务管理环境。 堆 Trail 的客户端使用 AngularJS 构建，使用 Firebase 作为持久性服务。 Trail 还使用 NodeJS 服务器来存储附加（外围）数据。 服务 Firebase：用于获得实时用户体验。 所有...
Trail 是一个专为 Scala 平台设计的轻量级路由库，主要应用于 Web 开发领域。它提供了简洁、高效的 API，使得开发者能够方便地构建和管理应用的路由规则。Trail 的设计目标是零依赖，这意味着它不依赖任何外部库，这...
续篇论文集 想要的帮助：请尝试sequelize-paper-trail@3.0.0-rc.6并给 :...const sequelize = new Sequelize ( 'database' , 'username' , 'password' ) ; 然后添加Sequelize Paper Trail很简单： const PaperTrail =
ProtonTrail-Godot的3D Trail插件 概述 Godot Engine的3D Trail自定义节点 在运行时生成 可调分辨率 它只是一个网格，因此您可以在其上应用自己的材料 如何使用 将存储addons到您的addons文件夹 转到Project ...
vim-sourcetrail Vim插件可将光标位置与同步。 安装 使用您选择的插件管理器。 git clone https://github.com/CoatiSoftware/vim-sourcetrail ~/.vim/bundle/vim-sourcetrail 将Plugin 'CoatiSoftware/vim-source...
Trail是一种模块化且灵活的审核跟踪日志服务。 特征 作为独立服务器运行或扩展现有服务 具有灵活架构的Postgres后端 REST和GraphQL接口 支持Fastify和Hapi框架 文献资料 完整文档可在或docs/文件夹中找到。 执照 ...
[1]杜东阳，吕文兵等。 用于从治疗后鼻咽18F-FDG PET / CT图像中最佳区分复发与炎症的机器学习方法。 J Nucl Med，2018年第1期。 59号补充1125 该matlab代码包含2个演示： “ demo_tr_te_CI”：matlab代码，通过使用...
"Paper-Trail: OSU Hackathon 2021年Spring"是一个编程竞赛项目，可能由俄亥俄州立大学（OSU）的学生在2021年春季黑客马拉松活动中创建。项目名称“纸足迹”暗示它可能关注的是数据跟踪、环境保护或信息记录方面的...
这是一个用于跟踪的存储库 - 一...启动 Trail 的步骤： cd kibana/样品 节点服务器.js 转到浏览器并启动： 笔记： 确保 elastic-search 在 localhost:9200 上运行 在elastic-search中保留一些数据以便能够开始查询
本项目“ML-model-deployment-trail”聚焦于通过Heroku平台进行Web API的部署，以实现远程访问和测试模型。Heroku是一个流行的云平台，特别适合小型应用和快速原型的部署。 在描述中提到，此项目是用于测试在Heroku...
踪迹该软件包将公开一个@trail刀片指令，您可以将其包含在主刀片或布局文件中。 此刀片指令公开了一个Trail JavaScript对象，该对象包含config和应用程序具有的命名路由的集合。 如果您知道我的意思，该指令还公开了...
$ npm install --save eulerian-trail 用法 var eulerianTrail = require ( 'eulerian-trail' ) ; eulerianTrail ( { edges : [ [ 0 , 1 ] , [ 1 , 2 ] , [ 2 , 3 ] , [ 3 , 6 ] , [ 6 , 7 ] , [ 7 , 10 ] ,...
步骤1 要安装，您必须首先选择一个放置此存储库的文件夹。 我已经选择： /root/backend/extensions/modules因为这对我来说似乎是最正确的。 将此存储库克隆到该位置。第2步该安装表了。 您可以使用此扩展的原始作者...
paper_trail 科 Ruby 活动记录 未发行 主 > = 2.5.0 > = 5.2，<6> = 2.4.0 > = 5.2，<6> = 2.3.0 > = 4.2，<6> = 2.3.0 > = 4.2，<5> = 2.2.0 > = 4.2，<5> = 2.1.0 > = 4.0，<5> = 1.9.3 > = 4.0，<5> = ...
D3 步道布局 这是用于在 D3 中创建路径的布局函数，其中（与原生 d3.svg.line() 元素不同）您需要将特定的美学应用到行的每个元素。 演示 最初的用例是具有衰减不透明度的轨迹来表示运动：这是一个示例图像： （即将...
在我们的“Digital Humanities 1030”课程中，有一个名为“Washington_Trail”的项目，它提供了一种独特的方式来探索历史，特别是美国国父乔治·华盛顿年轻时的一段重要旅程。通过这个项目，我们可以深入研究华盛顿...
你可以通过`PaperTrail.version_changes/1`查询这些变化。 为了恢复到历史中的某个版本，你可以使用`PaperTrail.revert/3`函数。这个函数接受模型实例、目标版本号以及可选的事务选项，它会将模型回滚到指定版本的...
区块链审计追踪 该存储库包含一个分支，在其顶部构建了概念验证的区块链审计跟踪实现。 该区块链审计追踪是我在阿姆斯特丹大学验证审计追踪数据的学士论文的一部分。 该论文的完整版本可，该实现的演示视频可在获得...