Java tutorial
/* * Copyright (c) 1995, 2011, Oracle and/or its affiliates. All rights reserved. * * Redistribution and use in source and binary forms, with or without * modification, are permitted provided that the following conditions * are met: * * - Redistributions of source code must retain the above copyright * notice, this list of conditions and the following disclaimer. * * - Redistributions in binary form must reproduce the above copyright * notice, this list of conditions and the following disclaimer in the * documentation and/or other materials provided with the distribution. * * - Neither the name of Oracle or the names of its * contributors may be used to endorse or promote products derived * from this software without specific prior written permission. * * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS * IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, * THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR * PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR * CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR * PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF * LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING * NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ 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()); } public static void getWarningsFromStatement(Statement stmt) throws SQLException { JDBCTutorialUtilities.printWarnings(stmt.getWarnings()); } public static void printWarnings(SQLWarning warning) throws SQLException { 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) { 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; 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); } } }