[C:\Users\Administrator\Desktop\JDBCTutorial\src\com\oracle\tutorial\jdbc\CachedRowSetSample.java] /* * 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 com.sun.rowset.CachedRowSetImpl; import java.net.MalformedURLException; import java.net.URL; import java.sql.Connection; import java.sql.Date; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.util.Calendar; import java.util.GregorianCalendar; import java.util.HashMap; import javax.sql.rowset.CachedRowSet; import javax.sql.rowset.spi.SyncProviderException; import javax.sql.rowset.spi.SyncResolver; public class CachedRowSetSample { private String dbName; private Connection con; private String dbms; private JDBCTutorialUtilities settings; public CachedRowSetSample(Connection connArg, JDBCTutorialUtilities settingsArg) { super(); this.con = connArg; this.dbName = settingsArg.dbName; this.dbms = settingsArg.dbms; this.settings = settingsArg; } public void testPaging() throws SQLException, MalformedURLException { CachedRowSet crs = null; this.con.setAutoCommit(false); try { crs = new CachedRowSetImpl(); crs.setUsername(settings.userName); crs.setPassword(settings.password); if (this.dbms.equals("mysql")) { crs.setUrl(settings.urlString + "?relaxAutoCommit=true"); } else { crs.setUrl(settings.urlString); } crs.setCommand("select * from MERCH_INVENTORY"); // Setting the page size to 4, such that we // get the data in chunks of 4 rows @ a time. crs.setPageSize(100); // Now get the first set of data crs.execute(); crs.addRowSetListener(new ExampleRowSetListener()); // Keep on getting data in chunks until done. int i = 1; do { System.out.println("Page number: " + i); while (crs.next()) { System.out.println("Found item " + crs.getInt("ITEM_ID") + ": " + crs.getString("ITEM_NAME")); if (crs.getInt("ITEM_ID") == 1235) { int currentQuantity = crs.getInt("QUAN") + 1; System.out.println("Updating quantity to " + currentQuantity); crs.updateInt("QUAN", currentQuantity + 1); crs.updateRow(); // Syncing the row back to the DB crs.acceptChanges(con); } } // End of inner while i++; } while (crs.nextPage()); // End of outer while // Inserting a new row // Doing a previous page to come back to the last page // as we ll be after the last page. int newItemId = 123456; if (this.doesItemIdExist(newItemId)) { System.out.println("Item ID " + newItemId + " already exists"); } else { crs.previousPage(); crs.moveToInsertRow(); crs.updateInt("ITEM_ID", newItemId); crs.updateString("ITEM_NAME", "TableCloth"); crs.updateInt("SUP_ID", 927); crs.updateInt("QUAN", 14); Calendar timeStamp; timeStamp = new GregorianCalendar(); timeStamp.set(2006, 4, 1); crs.updateTimestamp("DATE_VAL", new Timestamp(timeStamp.getTimeInMillis())); crs.insertRow(); crs.moveToCurrentRow(); // Syncing the new row back to the database. System.out.println("About to add a new row..."); crs.acceptChanges(con); System.out.println("Added a row..."); this.viewTable(con); } } catch (SyncProviderException spe) { SyncResolver resolver = spe.getSyncResolver(); Object crsValue; // value in the RowSet object Object resolverValue; // value in the SyncResolver object Object resolvedValue; // value to be persisted while (resolver.nextConflict()) { if (resolver.getStatus() == SyncResolver.INSERT_ROW_CONFLICT) { int row = resolver.getRow(); crs.absolute(row); int colCount = crs.getMetaData().getColumnCount(); for (int j = 1; j <= colCount; j++) { if (resolver.getConflictValue(j) != null) { crsValue = crs.getObject(j); resolverValue = resolver.getConflictValue(j); // Compare crsValue and resolverValue to determine // which should be the resolved value (the value to persist) // // This example choses the value in the RowSet object, // crsValue, to persist., resolvedValue = crsValue; resolver.setResolvedValue(j, resolvedValue); } } } } } catch (SQLException sqle) { JDBCTutorialUtilities.printSQLException(sqle); } finally { if (crs != null) crs.close(); this.con.setAutoCommit(true); } } private boolean doesItemIdExist(int id) throws SQLException { Statement stmt = null; String query = "select ITEM_ID from MERCH_INVENTORY where ITEM_ID = " + id; try { stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); if (rs.next()) { return true; } } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (stmt != null) { stmt.close(); } } return false; } public static void viewTable(Connection con) throws SQLException { Statement stmt = null; String query = "select * from MERCH_INVENTORY"; try { stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); while (rs.next()) { System.out.println("Found item " + rs.getInt("ITEM_ID") + ": " + rs.getString("ITEM_NAME") + " (" + rs.getInt("QUAN") + ")"); } } 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(); if (myJDBCTutorialUtilities == null) { System.out.println("myJDBCTU is null"); } if (myConnection == null) { System.out.println("myConnection is null"); } CachedRowSetSample myCachedRowSetSample = new CachedRowSetSample(myConnection, myJDBCTutorialUtilities); myCachedRowSetSample.viewTable(myConnection); myCachedRowSetSample.testPaging(); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } catch (Exception ex) { System.out.println("Unexpected exception"); ex.printStackTrace(); } finally { JDBCTutorialUtilities.closeConnection(myConnection); } } } [C:\Users\Administrator\Desktop\JDBCTutorial\src\com\oracle\tutorial\jdbc\CityFilter.java] /* * 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.SQLException; import javax.sql.RowSet; import javax.sql.rowset.CachedRowSet; import javax.sql.rowset.Predicate; public class CityFilter implements Predicate { private String[] cities; private String colName = null; private int colNumber = -1; public CityFilter(String[] citiesArg, String colNameArg) { this.cities = citiesArg; this.colNumber = -1; this.colName = colNameArg; } public CityFilter(String[] citiesArg, int colNumberArg) { this.cities = citiesArg; this.colNumber = colNumberArg; this.colName = null; } public boolean evaluate(Object valueArg, String colNameArg) { if (colNameArg.equalsIgnoreCase(this.colName)) { for (int i = 0; i < this.cities.length; i++) { if (this.cities[i].equalsIgnoreCase((String)valueArg)) { return true; } } } return false; } public boolean evaluate(Object valueArg, int colNumberArg) { if (colNumberArg == this.colNumber) { for (int i = 0; i < this.cities.length; i++) { if (this.cities[i].equalsIgnoreCase((String)valueArg)) { return true; } } } return false; } public boolean evaluate(RowSet rs) { if (rs == null) return false; try { for (int i = 0; i < this.cities.length; i++) { String cityName = null; if (this.colNumber > 0) { cityName = (String)rs.getObject(this.colNumber); } else if (this.colName != null) { cityName = (String)rs.getObject(this.colName); } else { return false; } if (cityName.equalsIgnoreCase(cities[i])) { return true; } } } catch (SQLException e) { return false; } return false; } } [C:\Users\Administrator\Desktop\JDBCTutorial\src\com\oracle\tutorial\jdbc\ClobSample.java] /* * 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.io.BufferedReader; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.FileReader; import java.io.IOException; import java.io.Reader; import java.io.StringWriter; import java.io.Writer; import java.sql.Clob; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class ClobSample { private String dbName; private Connection con; private String dbms; private JDBCTutorialUtilities settings; public ClobSample(Connection connArg, JDBCTutorialUtilities settingsArg) { super(); this.con = connArg; this.dbName = settingsArg.dbName; this.dbms = settingsArg.dbms; this.settings = settingsArg; } public String retrieveExcerpt(String coffeeName, int numChar) throws SQLException { String description = null; Clob myClob = null; PreparedStatement pstmt = null; try { String sql = "select COF_DESC from COFFEE_DESCRIPTIONS " + "where COF_NAME = ?"; pstmt = this.con.prepareStatement(sql); pstmt.setString(1, coffeeName); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { myClob = rs.getClob(1); System.out.println("Length of retrieved Clob: " + myClob.length()); } description = myClob.getSubString(1, numChar); } catch (SQLException sqlex) { JDBCTutorialUtilities.printSQLException(sqlex); } catch (Exception ex) { System.out.println("Unexpected exception: " + ex.toString()); } finally { if (pstmt != null) pstmt.close(); } return description; } public void addRowToCoffeeDescriptions(String coffeeName, String fileName) throws SQLException { PreparedStatement pstmt = null; try { Clob myClob = this.con.createClob(); Writer clobWriter = myClob.setCharacterStream(1); String str = this.readFile(fileName, clobWriter); System.out.println("Wrote the following: " + clobWriter.toString()); if (this.settings.dbms.equals("mysql")) { System.out.println("MySQL, setting String in Clob object with setString method"); myClob.setString(1, str); } System.out.println("Length of Clob: " + myClob.length()); String sql = "INSERT INTO COFFEE_DESCRIPTIONS VALUES(?,?)"; pstmt = this.con.prepareStatement(sql); pstmt.setString(1, coffeeName); pstmt.setClob(2, myClob); pstmt.executeUpdate(); } catch (SQLException sqlex) { JDBCTutorialUtilities.printSQLException(sqlex); } catch (Exception ex) { System.out.println("Unexpected exception: " + ex.toString()); } finally { if (pstmt != null) { pstmt.close(); } } } private String readFile(String fileName, Writer writerArg) throws FileNotFoundException, IOException { BufferedReader br = new BufferedReader(new FileReader(fileName)); String nextLine = ""; StringBuffer sb = new StringBuffer(); while ((nextLine = br.readLine()) != null) { System.out.println("Writing: " + nextLine); writerArg.write(nextLine); sb.append(nextLine); } // Convert the content into to a string String clobData = sb.toString(); // Return the data. return clobData; } 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(); ClobSample myClobSample = new ClobSample(myConnection, myJDBCTutorialUtilities); myClobSample.addRowToCoffeeDescriptions("Colombian", "txt/colombian-description.txt"); String description = myClobSample.retrieveExcerpt("Colombian", 10); System.out.println(description); } catch (Exception e) { e.printStackTrace(); } finally { JDBCTutorialUtilities.closeConnection(myConnection); } } } [C:\Users\Administrator\Desktop\JDBCTutorial\src\com\oracle\tutorial\jdbc\CoffeesFrame.java] /* * 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 com.sun.rowset.CachedRowSetImpl; import javax.sql.RowSetEvent; import javax.swing.*; import java.awt.*; import java.awt.event.*; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import javax.sql.RowSetListener; import javax.sql.rowset.CachedRowSet; public class CoffeesFrame extends JFrame implements RowSetListener { JDBCTutorialUtilities settings; Connection connection; JTable table; // The table for displaying data JLabel label_COF_NAME; JLabel label_SUP_ID; JLabel label_PRICE; JLabel label_SALES; JLabel label_TOTAL; JTextField textField_COF_NAME; JTextField textField_SUP_ID; JTextField textField_PRICE; JTextField textField_SALES; JTextField textField_TOTAL; JButton button_ADD_ROW; JButton button_UPDATE_DATABASE; JButton button_DISCARD_CHANGES; CoffeesTableModel myCoffeesTableModel; public CoffeesFrame(JDBCTutorialUtilities settingsArg) throws SQLException { super("The Coffee Break: COFFEES Table"); // Set window title this.settings = settingsArg; connection = settings.getConnection(); // Close connections exit the application when the user // closes the window addWindowListener(new WindowAdapter() { public void windowClosing(WindowEvent e) { try { connection.close(); } catch (SQLException sqle) { JDBCTutorialUtilities.printSQLException(sqle); } System.exit(0); } }); // Initialize and lay out window controls CachedRowSet myCachedRowSet = getContentsOfCoffeesTable(); myCoffeesTableModel = new CoffeesTableModel(myCachedRowSet); myCoffeesTableModel.addEventHandlersToRowSet(this); table = new JTable(); // Displays the table table.setModel(myCoffeesTableModel); label_COF_NAME = new JLabel(); label_SUP_ID = new JLabel(); label_PRICE = new JLabel(); label_SALES = new JLabel(); label_TOTAL = new JLabel(); textField_COF_NAME = new JTextField(10); textField_SUP_ID = new JTextField(10); textField_PRICE = new JTextField(10); textField_SALES = new JTextField(10); textField_TOTAL = new JTextField(10); button_ADD_ROW = new JButton(); button_UPDATE_DATABASE = new JButton(); button_DISCARD_CHANGES = new JButton(); label_COF_NAME.setText("Coffee Name:"); label_SUP_ID.setText("Supplier ID:"); label_PRICE.setText("Price:"); label_SALES.setText("Sales:"); label_TOTAL.setText("Total Sales:"); textField_COF_NAME.setText("Enter new coffee name"); textField_SUP_ID.setText("101"); textField_PRICE.setText("0"); textField_SALES.setText("0"); textField_TOTAL.setText("0"); button_ADD_ROW.setText("Add row to table"); button_UPDATE_DATABASE.setText("Update database"); button_DISCARD_CHANGES.setText("Discard changes"); // Place the components within the container contentPane; use GridBagLayout // as the layout. Container contentPane = getContentPane(); contentPane.setComponentOrientation(ComponentOrientation.LEFT_TO_RIGHT); contentPane.setLayout(new GridBagLayout()); GridBagConstraints c = new GridBagConstraints(); c.fill = GridBagConstraints.BOTH; c.anchor = GridBagConstraints.CENTER; c.weightx = 0.5; c.weighty = 1.0; c.gridx = 0; c.gridy = 0; c.gridwidth = 2; contentPane.add(new JScrollPane(table), c); c.fill = GridBagConstraints.HORIZONTAL; c.anchor = GridBagConstraints.LINE_START; c.weightx = 0.25; c.weighty = 0; c.gridx = 0; c.gridy = 1; c.gridwidth = 1; contentPane.add(label_COF_NAME, c); c.fill = GridBagConstraints.HORIZONTAL; c.anchor = GridBagConstraints.LINE_END; c.weightx = 0.75; c.weighty = 0; c.gridx = 1; c.gridy = 1; c.gridwidth = 1; contentPane.add(textField_COF_NAME, c); c.fill = GridBagConstraints.HORIZONTAL; c.weightx = 0.25; c.weighty = 0; c.anchor = GridBagConstraints.LINE_START; c.gridx = 0; c.gridy = 2; c.gridwidth = 1; contentPane.add(label_SUP_ID, c); c.fill = GridBagConstraints.HORIZONTAL; c.anchor = GridBagConstraints.LINE_END; c.weightx = 0.75; c.weighty = 0; c.gridx = 1; c.gridy = 2; c.gridwidth = 1; contentPane.add(textField_SUP_ID, c); c.fill = GridBagConstraints.HORIZONTAL; c.anchor = GridBagConstraints.LINE_START; c.weightx = 0.25; c.weighty = 0; c.gridx = 0; c.gridy = 3; c.gridwidth = 1; contentPane.add(label_PRICE, c); c.fill = GridBagConstraints.HORIZONTAL; c.anchor = GridBagConstraints.LINE_END; c.weightx = 0.75; c.weighty = 0; c.gridx = 1; c.gridy = 3; c.gridwidth = 1; contentPane.add(textField_PRICE, c); c.fill = GridBagConstraints.HORIZONTAL; c.anchor = GridBagConstraints.LINE_START; c.weightx = 0.25; c.weighty = 0; c.gridx = 0; c.gridy = 4; c.gridwidth = 1; contentPane.add(label_SALES, c); c.fill = GridBagConstraints.HORIZONTAL; c.anchor = GridBagConstraints.LINE_END; c.weightx = 0.75; c.weighty = 0; c.gridx = 1; c.gridy = 4; c.gridwidth = 1; contentPane.add(textField_SALES, c); c.fill = GridBagConstraints.HORIZONTAL; c.anchor = GridBagConstraints.LINE_START; c.weightx = 0.25; c.weighty = 0; c.gridx = 0; c.gridy = 5; c.gridwidth = 1; contentPane.add(label_TOTAL, c); c.fill = GridBagConstraints.HORIZONTAL; c.anchor = GridBagConstraints.LINE_END; c.weightx = 0.75; c.weighty = 0; c.gridx = 1; c.gridy = 5; c.gridwidth = 1; contentPane.add(textField_TOTAL, c); c.fill = GridBagConstraints.HORIZONTAL; c.anchor = GridBagConstraints.LINE_START; c.weightx = 0.5; c.weighty = 0; c.gridx = 0; c.gridy = 6; c.gridwidth = 1; contentPane.add(button_ADD_ROW, c); c.fill = GridBagConstraints.HORIZONTAL; c.anchor = GridBagConstraints.LINE_END; c.weightx = 0.5; c.weighty = 0; c.gridx = 1; c.gridy = 6; c.gridwidth = 1; contentPane.add(button_UPDATE_DATABASE, c); c.fill = GridBagConstraints.HORIZONTAL; c.anchor = GridBagConstraints.LINE_START; c.weightx = 0.5; c.weighty = 0; c.gridx = 0; c.gridy = 7; c.gridwidth = 1; contentPane.add(button_DISCARD_CHANGES, c); // Add listeners for the buttons in the application button_ADD_ROW.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { JOptionPane.showMessageDialog(CoffeesFrame.this, new String[] { "Adding the following row:", "Coffee name: [" + textField_COF_NAME.getText() + "]", "Supplier ID: [" + textField_SUP_ID.getText() + "]", "Price: [" + textField_PRICE.getText() + "]", "Sales: [" + textField_SALES.getText() + "]", "Total: [" + textField_TOTAL.getText() + "]" }); try { myCoffeesTableModel.insertRow(textField_COF_NAME.getText(), Integer.parseInt(textField_SUP_ID.getText().trim()), Float.parseFloat(textField_PRICE.getText().trim()), Integer.parseInt(textField_SALES.getText().trim()), Integer.parseInt(textField_TOTAL.getText().trim())); } catch (SQLException sqle) { displaySQLExceptionDialog(sqle); } } }); button_UPDATE_DATABASE.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { try { myCoffeesTableModel.coffeesRowSet.acceptChanges(); } catch (SQLException sqle) { displaySQLExceptionDialog(sqle); // Now revert back changes try { createNewTableModel(); } catch (SQLException sqle2) { displaySQLExceptionDialog(sqle2); } } } }); button_DISCARD_CHANGES.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { try { createNewTableModel(); } catch (SQLException sqle) { displaySQLExceptionDialog(sqle); } } }); } private void displaySQLExceptionDialog(SQLException e) { // Display the SQLException in a dialog box JOptionPane.showMessageDialog( CoffeesFrame.this, new String[] { e.getClass().getName() + ": ", e.getMessage() } ); } private void createNewTableModel() throws SQLException { myCoffeesTableModel = new CoffeesTableModel(getContentsOfCoffeesTable()); myCoffeesTableModel.addEventHandlersToRowSet(this); table.setModel(myCoffeesTableModel); } public static void main(String[] args) throws Exception { JDBCTutorialUtilities myJDBCTutorialUtilities; 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 { CoffeesFrame qf = new CoffeesFrame(myJDBCTutorialUtilities); qf.pack(); qf.setVisible(true); } catch (SQLException sqle) { JDBCTutorialUtilities.printSQLException(sqle); } catch (Exception e) { System.out.println("Unexpected exception"); e.printStackTrace(); } } public CachedRowSet getContentsOfCoffeesTable() throws SQLException { CachedRowSet crs = null; try { connection = settings.getConnection(); crs = new CachedRowSetImpl(); crs.setType(ResultSet.TYPE_SCROLL_INSENSITIVE); crs.setConcurrency(ResultSet.CONCUR_UPDATABLE); crs.setUsername(settings.userName); crs.setPassword(settings.password); // In MySQL, to disable auto-commit, set the property relaxAutoCommit to // true in the connection URL. if (this.settings.dbms.equals("mysql")) { crs.setUrl(settings.urlString + "?relaxAutoCommit=true"); } else { crs.setUrl(settings.urlString); } // Regardless of the query, fetch the contents of COFFEES crs.setCommand("select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES"); crs.execute(); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } return crs; } public void actionPerformed(ActionEvent event) { } public void rowSetChanged(RowSetEvent event) { } public void rowChanged(RowSetEvent event) { CachedRowSet currentRowSet = this.myCoffeesTableModel.coffeesRowSet; try { currentRowSet.moveToCurrentRow(); myCoffeesTableModel = new CoffeesTableModel(myCoffeesTableModel.getCoffeesRowSet()); table.setModel(myCoffeesTableModel); } catch (SQLException ex) { JDBCTutorialUtilities.printSQLException(ex); // Display the error in a dialog box. JOptionPane.showMessageDialog( CoffeesFrame.this, new String[] { // Display a 2-line message ex.getClass().getName() + ": ", ex.getMessage() } ); } } public void cursorMoved(RowSetEvent event) { } } [C:\Users\Administrator\Desktop\JDBCTutorial\src\com\oracle\tutorial\jdbc\CoffeesTable.java] /* * 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.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); updateTotal = con.prepareStatement(updateStatement); for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) { updateSales.setInt(1, e.getValue().intValue()); updateSales.setString(2, e.getKey()); updateSales.executeUpdate(); updateTotal.setInt(1, e.getValue().intValue()); updateTotal.setString(2, e.getKey()); updateTotal.executeUpdate(); 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); 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); 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(); } } 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); 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(); } 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(); 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()) { 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 { 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 { if (stmt != null) { stmt.close(); } } } 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); } } } [C:\Users\Administrator\Desktop\JDBCTutorial\src\com\oracle\tutorial\jdbc\CoffeesTableModel.java] /* * 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 com.sun.rowset.CachedRowSetImpl; import java.sql.Connection; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import javax.sql.RowSetListener; import javax.sql.rowset.CachedRowSet; import javax.swing.event.TableModelListener; import javax.swing.table.TableModel; public class CoffeesTableModel implements TableModel { CachedRowSet coffeesRowSet; // The ResultSet to interpret ResultSetMetaData metadata; // Additional information about the results int numcols, numrows; // How many rows and columns in the table public CachedRowSet getCoffeesRowSet() { return coffeesRowSet; } public CoffeesTableModel(CachedRowSet rowSetArg) throws SQLException { this.coffeesRowSet = rowSetArg; this.metadata = this.coffeesRowSet.getMetaData(); numcols = metadata.getColumnCount(); // Retrieve the number of rows. this.coffeesRowSet.beforeFirst(); this.numrows = 0; while (this.coffeesRowSet.next()) { this.numrows++; } this.coffeesRowSet.beforeFirst(); } public void addEventHandlersToRowSet(RowSetListener listener) { this.coffeesRowSet.addRowSetListener(listener); } public void insertRow(String coffeeName, int supplierID, float price, int sales, int total) throws SQLException { try { this.coffeesRowSet.moveToInsertRow(); this.coffeesRowSet.updateString("COF_NAME", coffeeName); this.coffeesRowSet.updateInt("SUP_ID", supplierID); this.coffeesRowSet.updateFloat("PRICE", price); this.coffeesRowSet.updateInt("SALES", sales); this.coffeesRowSet.updateInt("TOTAL", total); this.coffeesRowSet.insertRow(); this.coffeesRowSet.moveToCurrentRow(); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } } public void close() { try { coffeesRowSet.getStatement().close(); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } } /** Automatically close when we're garbage collected */ protected void finalize() { close(); } /** Method from interface TableModel; returns the number of columns */ public int getColumnCount() { return numcols; } /** Method from interface TableModel; returns the number of rows */ public int getRowCount() { return numrows; } /** Method from interface TableModel; returns the column name at columnIndex * based on information from ResultSetMetaData */ public String getColumnName(int column) { try { return this.metadata.getColumnLabel(column + 1); } catch (SQLException e) { return e.toString(); } } /** Method from interface TableModel; returns the most specific superclass for * all cell values in the specified column. To keep things simple, all data * in the table are converted to String objects; hence, this method returns * the String class. */ public Class getColumnClass(int column) { return String.class; } /** Method from interface TableModel; returns the value for the cell specified * by columnIndex and rowIndex. TableModel uses this method to populate * itself with data from the row set. SQL starts numbering its rows and * columns at 1, but TableModel starts at 0. */ public Object getValueAt(int rowIndex, int columnIndex) { try { this.coffeesRowSet.absolute(rowIndex + 1); Object o = this.coffeesRowSet.getObject(columnIndex + 1); if (o == null) return null; else return o.toString(); } catch (SQLException e) { return e.toString(); } } /** Method from interface TableModel; returns true if the specified cell * is editable. This sample does not allow users to edit any cells from * the TableModel (rows are added by another window control). Thus, * this method returns false. */ public boolean isCellEditable(int rowIndex, int columnIndex) { return false; } // Because the sample does not allow users to edit any cells from the // TableModel, the following methods, setValueAt, addTableModelListener, // and removeTableModelListener, do not need to be implemented. public void setValueAt(Object value, int row, int column) { System.out.println("Calling setValueAt row " + row + ", column " + column); } public void addTableModelListener(TableModelListener l) { } public void removeTableModelListener(TableModelListener l) { } } [C:\Users\Administrator\Desktop\JDBCTutorial\src\com\oracle\tutorial\jdbc\DatalinkSample.java] /* * 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; // Java io imports import java.io.BufferedReader; import java.io.IOException; import java.io.InputStreamReader; // Java net imports import java.net.InetSocketAddress; import java.net.Proxy; import java.net.URL; import java.net.URLConnection; // SQL imports import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DatalinkSample { private String dbName; private Connection con; private String dbms; private JDBCTutorialUtilities settings; private static String proxy = "http://www-proxy.us.oracle.com:80"; public DatalinkSample(Connection connArg, JDBCTutorialUtilities settingsArg) { super(); this.con = connArg; this.dbName = settingsArg.dbName; this.dbms = settingsArg.dbms; this.settings = settingsArg; } public static void viewTable(Connection con, Proxy proxy) throws SQLException, IOException { Statement stmt = null; String query = "SELECT document_name, url FROM data_repository"; try { stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); if ( rs.next() ) { String documentName = null; java.net.URL url = null; documentName = rs.getString(1); // Retrieve the value as a URL object. url = rs.getURL(2); if (url != null) { // Retrieve the contents from the URL. URLConnection myURLConnection = url.openConnection(proxy); BufferedReader bReader = new BufferedReader(new InputStreamReader(myURLConnection.getInputStream())); System.out.println("Document name: " + documentName); String pageContent = null; while ((pageContent = bReader.readLine()) != null ) { // Print the URL contents System.out.println(pageContent); } } else { System.out.println("URL is null"); } } } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } catch(IOException ioEx) { System.out.println("IOException caught: " + ioEx.toString()); } catch (Exception ex) { System.out.println("Unexpected exception"); ex.printStackTrace(); } finally { if (stmt != null) { stmt.close(); } } } public void addURLRow(String description, String url) throws SQLException { PreparedStatement pstmt = null; try { pstmt = this.con.prepareStatement( "INSERT INTO data_repository(document_name,url) VALUES (?,?)"); pstmt.setString(1, description); pstmt.setURL(2,new URL(url)); pstmt.execute(); } catch (SQLException sqlex) { JDBCTutorialUtilities.printSQLException(sqlex); } catch (Exception ex) { System.out.println("Unexpected exception"); ex.printStackTrace(); } finally { if (pstmt != null) { pstmt.close(); } } } public static void main(String[] args) { JDBCTutorialUtilities myJDBCTutorialUtilities; Connection myConnection = null; Proxy myProxy; InetSocketAddress myProxyServer; 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(); DatalinkSample myDatalinkSample = new DatalinkSample(myConnection, myJDBCTutorialUtilities); myDatalinkSample.addURLRow("Oracle", "http://www.oracle.com"); // myProxyServer = new InetSocketAddress("www-proxy.example.com", 80); // myProxy = new Proxy(Proxy.Type.HTTP, myProxyServer); DatalinkSample.viewTable(myConnection, Proxy.NO_PROXY); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } catch (Exception ex) { System.out.println("Unexpected exception"); ex.printStackTrace(); } finally { JDBCTutorialUtilities.closeConnection(myConnection); } } } [C:\Users\Administrator\Desktop\JDBCTutorial\src\com\oracle\tutorial\jdbc\ExampleRowSetListener.java] /* * 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 javax.sql.RowSetEvent; import javax.sql.RowSetListener; public class ExampleRowSetListener implements RowSetListener { public void rowSetChanged(RowSetEvent event) { System.out.println("Called rowSetChanged in ExampleRowSetListener"); } public void rowChanged(RowSetEvent event) { System.out.println("Called rowChanged in ExampleRowSetListener"); } public void cursorMoved(RowSetEvent event) { System.out.println("Called cursorMoved in ExampleRowSetListener"); } } [C:\Users\Administrator\Desktop\JDBCTutorial\src\com\oracle\tutorial\jdbc\FilteredRowSetSample.java] /* * 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 com.sun.rowset.CachedRowSetImpl; import com.sun.rowset.FilteredRowSetImpl; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.sql.rowset.CachedRowSet; import javax.sql.rowset.FilteredRowSet; public class FilteredRowSetSample { private String dbName; private Connection con; private String dbms; private JDBCTutorialUtilities settings; public FilteredRowSetSample(Connection connArg, JDBCTutorialUtilities settingsArg) { super(); this.con = connArg; this.dbName = settingsArg.dbName; this.dbms = settingsArg.dbms; this.settings = settingsArg; } private void viewFilteredRowSet(FilteredRowSet frs) throws SQLException { if (frs == null) { return; } CachedRowSet crs = (CachedRowSet)frs; while (crs.next()) { if (crs == null) { break; } System.out.println( crs.getInt("STORE_ID") + ", " + crs.getString("CITY") + ", " + crs.getInt("COFFEE") + ", " + crs.getInt("MERCH") + ", " + crs.getInt("TOTAL")); } } public static void viewTable(Connection con) throws SQLException { Statement stmt = null; String query = "select * from COFFEE_HOUSES"; try { stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); while (rs.next()) { System.out.println(rs.getInt("STORE_ID") + ", " + rs.getString("CITY") + ", " + rs.getInt("COFFEE") + ", " + rs.getInt("MERCH") + ", " + rs.getInt("TOTAL")); } } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (stmt != null) { stmt.close(); } } } public void testFilteredRowSet() { FilteredRowSet frs = null; StateFilter myStateFilter = new StateFilter(10000, 10999, 1); String[] cityArray = { "SF", "LA" }; CityFilter myCityFilter = new CityFilter(cityArray, 2); try { frs = new FilteredRowSetImpl(); frs.setCommand("SELECT * FROM COFFEE_HOUSES"); frs.setUsername(settings.userName); frs.setPassword(settings.password); frs.setUrl(settings.urlString); frs.execute(); System.out.println("\nBefore filter:"); FilteredRowSetSample.viewTable(this.con); System.out.println("\nSetting state filter:"); frs.beforeFirst(); frs.setFilter(myStateFilter); this.viewFilteredRowSet(frs); System.out.println("\nSetting city filter:"); frs.beforeFirst(); frs.setFilter(myCityFilter); this.viewFilteredRowSet(frs); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } } 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(); FilteredRowSetSample myFilteredRowSetSample = new FilteredRowSetSample(myConnection, myJDBCTutorialUtilities); myFilteredRowSetSample.testFilteredRowSet(); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } catch (Exception ex) { System.out.println("Unexpected exception"); ex.printStackTrace(); } finally { JDBCTutorialUtilities.closeConnection(myConnection); } } } [C:\Users\Administrator\Desktop\JDBCTutorial\src\com\oracle\tutorial\jdbc\JdbcRowSetSample.java] /* * 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 com.sun.rowset.JdbcRowSetImpl; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.sql.RowSet; import javax.sql.rowset.JdbcRowSet; public class JdbcRowSetSample { private String dbName; private Connection con; private String dbms; private JDBCTutorialUtilities settings; public JdbcRowSetSample(Connection connArg, JDBCTutorialUtilities settingsArg) { super(); this.con = connArg; this.dbName = settingsArg.dbName; this.dbms = settingsArg.dbms; this.settings = settingsArg; } public void testJdbcRowSet() throws SQLException { JdbcRowSet jdbcRs = null; ResultSet rs = null; Statement stmt = null; try { // An alternative way to create a JdbcRowSet object // stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); // rs = stmt.executeQuery("select * from COFFEES"); // jdbcRs = new JdbcRowSetImpl(rs); // Another way to create a JdbcRowSet object // jdbcRs = new JdbcRowSetImpl(); // jdbcRs.setCommand("select * from COFFEES"); // jdbcRs.setUrl(this.settings.urlString); // jdbcRs.setUsername(this.settings.userName); // jdbcRs.setPassword(this.settings.password); // jdbcRs.execute(); jdbcRs = new JdbcRowSetImpl(con); jdbcRs.setCommand("select * from COFFEES"); jdbcRs.execute(); jdbcRs.absolute(3); jdbcRs.updateFloat("PRICE", 10.99f); jdbcRs.updateRow(); System.out.println("\nAfter updating the third row:"); CoffeesTable.viewTable(con); jdbcRs.moveToInsertRow(); jdbcRs.updateString("COF_NAME", "HouseBlend"); jdbcRs.updateInt("SUP_ID", 49); jdbcRs.updateFloat("PRICE", 7.99f); jdbcRs.updateInt("SALES", 0); jdbcRs.updateInt("TOTAL", 0); jdbcRs.insertRow(); jdbcRs.moveToInsertRow(); jdbcRs.updateString("COF_NAME", "HouseDecaf"); jdbcRs.updateInt("SUP_ID", 49); jdbcRs.updateFloat("PRICE", 8.99f); jdbcRs.updateInt("SALES", 0); jdbcRs.updateInt("TOTAL", 0); jdbcRs.insertRow(); System.out.println("\nAfter inserting two rows:"); CoffeesTable.viewTable(con); jdbcRs.last(); jdbcRs.deleteRow(); System.out.println("\nAfter deleting last row:"); CoffeesTable.viewTable(con); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (stmt != null) stmt.close(); this.con.setAutoCommit(false); } } private void outputRowSet(RowSet rs) throws SQLException { rs.beforeFirst(); 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); } } 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(); JdbcRowSetSample myJdbcRowSetSample = new JdbcRowSetSample(myConnection, myJDBCTutorialUtilities); myJdbcRowSetSample.testJdbcRowSet(); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { JDBCTutorialUtilities.closeConnection(myConnection); } } } [C:\Users\Administrator\Desktop\JDBCTutorial\src\com\oracle\tutorial\jdbc\JDBCTutorialUtilities.java] /* * 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); } } } [C:\Users\Administrator\Desktop\JDBCTutorial\src\com\oracle\tutorial\jdbc\JoinSample.java] /* * 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 com.sun.rowset.CachedRowSetImpl; import com.sun.rowset.JoinRowSetImpl; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.sql.rowset.CachedRowSet; import javax.sql.rowset.JoinRowSet; public class JoinSample { Connection con; JDBCTutorialUtilities settings; public JoinSample(Connection connArg, JDBCTutorialUtilities settingsArg) { this.con = connArg; this.settings = settingsArg; } public static void getCoffeesBoughtBySupplier(String supplierName, Connection con) throws SQLException { Statement stmt = null; String query = "SELECT COFFEES.COF_NAME " + "FROM COFFEES, SUPPLIERS " + "WHERE SUPPLIERS.SUP_NAME LIKE '" + supplierName + "' " + "and SUPPLIERS.SUP_ID = COFFEES.SUP_ID"; try { stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); System.out.println("Coffees bought from " + supplierName + ": "); while (rs.next()) { String coffeeName = rs.getString(1); System.out.println(" " + coffeeName); } } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (stmt != null) { stmt.close(); } } } public void testJoinRowSet(String supplierName) throws SQLException { CachedRowSet coffees = null; CachedRowSet suppliers = null; JoinRowSet jrs = null; try { coffees = new CachedRowSetImpl(); coffees.setCommand("SELECT * FROM COFFEES"); coffees.setUsername(settings.userName); coffees.setPassword(settings.password); coffees.setUrl(settings.urlString); coffees.execute(); suppliers = new CachedRowSetImpl(); suppliers.setCommand("SELECT * FROM SUPPLIERS"); suppliers.setUsername(settings.userName); suppliers.setPassword(settings.password); suppliers.setUrl(settings.urlString); suppliers.execute(); jrs = new JoinRowSetImpl(); jrs.addRowSet(coffees, "SUP_ID"); jrs.addRowSet(suppliers, "SUP_ID"); System.out.println("Coffees bought from " + supplierName + ": "); while (jrs.next()) { if (jrs.getString("SUP_NAME").equals(supplierName)) { String coffeeName = jrs.getString(1); System.out.println(" " + coffeeName); } } } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (jrs != null) { jrs.close(); } if (suppliers != null) { suppliers.close(); } if (coffees != null) { coffees.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(); System.out.println("\nCoffees bought by each supplier:"); JoinSample.getCoffeesBoughtBySupplier("Acme, Inc.", myConnection); System.out.println("\nUsing JoinRowSet:"); JoinSample myJoinSample = new JoinSample(myConnection, myJDBCTutorialUtilities); myJoinSample.testJoinRowSet("Acme, Inc."); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { JDBCTutorialUtilities.closeConnection(myConnection); } } } [C:\Users\Administrator\Desktop\JDBCTutorial\src\com\oracle\tutorial\jdbc\ProductInformationTable.java] /* * 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.io.IOException; 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.Map; import java.util.Set; import javax.xml.parsers.DocumentBuilder; import javax.xml.parsers.DocumentBuilderFactory; import javax.xml.parsers.ParserConfigurationException; import javax.xml.xpath.*; import org.w3c.dom.Document; import org.w3c.dom.Node; import org.w3c.dom.NodeList; import org.xml.sax.SAXException; public class ProductInformationTable { private String dbName; private Connection con; private String dbms; public ProductInformationTable(Connection connArg, String dbNameArg, String dbmsArg) { super(); this.con = connArg; this.dbName = dbNameArg; this.dbms = dbmsArg; } public void populateTable(String fileName) throws SQLException, ParserConfigurationException, SAXException, IOException, XPathExpressionException { javax.xml.parsers.DocumentBuilderFactory factory = javax.xml.parsers.DocumentBuilderFactory.newInstance(); // factory.setNamespaceAware(true); factory.setNamespaceAware(true); DocumentBuilder builder = factory.newDocumentBuilder(); Document doc = builder.parse(fileName); XPathFactory xPathfactory = XPathFactory.newInstance(); XPath xPath = xPathfactory.newXPath(); NodeList nodes = (NodeList) xPath.evaluate( "/coffee-product-information/item[coffee = 'Columbian']", doc, XPathConstants.NODESET); for (int i = 0; i < nodes.getLength(); i++) { Node currentNode = nodes.item(i); // Retrieve the description element currentNode.normalize(); if (currentNode == null) { System.out.println("Current node is null"); } // System.out.println(currentNode.getTextContent()); Node descriptionNode = (Node) xPath.evaluate( "description", currentNode, XPathConstants.NODE); if (descriptionNode == null) { System.out.println("DescriptionNode is null"); } else { System.out.println(descriptionNode.getTextContent()); NodeList descriptionNodeChildren = descriptionNode.getChildNodes(); System.out.println("Description node has " + descriptionNodeChildren.getLength() + " child nodes"); Node descNodeChild = descriptionNode.getFirstChild(); System.out.println("Only child node type: " + descNodeChild.getNodeType()); } // System.out.println("Description: " + descriptionNode.getNodeValue()); // System.out.println(nodes.item(i).getNodeValue()); } } public void createTable() throws SQLException { String createString = "create table PRODUCT_INFORMATION" + " (COF_NAME varchar(32) NOT NULL," + " INFO clob NOT NULL," + " FOREIGN KEY (COF_NAME) REFERENCES COFFEES (COF_NAME))"; Statement stmt = null; try { stmt = con.createStatement(); stmt.executeUpdate(createString); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (stmt != null) { stmt.close(); } } } public void dropTable() throws SQLException { Statement stmt = null; try { stmt = con.createStatement(); if (this.dbms.equals("mysql")) { stmt.executeUpdate("DROP TABLE IF EXISTS PRODUCT_INFORMATION"); } else if (this.dbms.equals("derby")) { stmt.executeUpdate("DROP TABLE PRODUCT_INFORMATION"); } } 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); */ ProductInformationTable myProductInformationTable = new ProductInformationTable(myConnection, myJDBCTutorialUtilities.dbName, myJDBCTutorialUtilities.dbms); myProductInformationTable.populateTable("xml/coffee-information.xml"); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } catch (Exception ex) { ex.printStackTrace(); } finally { JDBCTutorialUtilities.closeConnection(myConnection); } } } [C:\Users\Administrator\Desktop\JDBCTutorial\src\com\oracle\tutorial\jdbc\RSSFeedsTable.java] /* * 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.io.IOException; import java.io.StringReader; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.SQLXML; import java.sql.Statement; import javax.xml.parsers.DocumentBuilder; import javax.xml.parsers.ParserConfigurationException; import javax.xml.transform.TransformerConfigurationException; import javax.xml.transform.TransformerException; import javax.xml.transform.dom.DOMResult; import javax.xml.xpath.XPath; import javax.xml.xpath.XPathConstants; import javax.xml.xpath.XPathExpressionException; import javax.xml.xpath.XPathFactory; import org.w3c.dom.Document; import org.w3c.dom.Node; import org.xml.sax.InputSource; import org.xml.sax.SAXException; public class RSSFeedsTable { private String dbName; private Connection con; private String dbms; public RSSFeedsTable(Connection connArg, String dbNameArg, String dbmsArg) { super(); this.con = connArg; this.dbName = dbNameArg; this.dbms = dbmsArg; } public void createTable() throws SQLException { Statement stmt = null; try { if (this.dbms.equals("derby")) { String createString = "create table RSS_FEEDS (RSS_NAME varchar(32) NOT NULL," + " RSS_FEED_XML xml NOT NULL, PRIMARY KEY (RSS_NAME))"; stmt = con.createStatement(); stmt.executeUpdate(createString); } else if (this.dbms.equals("mysql")) { String createString = "create table RSS_FEEDS (RSS_NAME varchar(32) NOT NULL," + " RSS_FEED_XML longtext NOT NULL, PRIMARY KEY (RSS_NAME))"; stmt = con.createStatement(); stmt.executeUpdate(createString); } } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (stmt != null) { stmt.close(); } } } public void dropTable() throws SQLException { Statement stmt = null; try { stmt = con.createStatement(); if (this.dbms.equals("mysql")) { stmt.executeUpdate("DROP TABLE IF EXISTS RSS_FEEDS"); } else if (this.dbms.equals("derby")) { stmt.executeUpdate("DROP TABLE RSS_FEEDS"); } } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { stmt.close(); } } public void addRSSFeed(String fileName) throws ParserConfigurationException, SAXException, IOException, XPathExpressionException, TransformerConfigurationException, TransformerException, SQLException { // Parse the document and retrieve the name of the RSS feed String titleString = null; javax.xml.parsers.DocumentBuilderFactory factory = javax.xml.parsers.DocumentBuilderFactory.newInstance(); factory.setNamespaceAware(true); DocumentBuilder builder = factory.newDocumentBuilder(); Document doc = builder.parse(fileName); XPathFactory xPathfactory = XPathFactory.newInstance(); XPath xPath = xPathfactory.newXPath(); Node titleElement = (Node)xPath.evaluate("/rss/channel/title[1]", doc, XPathConstants.NODE); if (titleElement == null) { System.out.println("Unable to retrieve title element"); return; } else { titleString = titleElement.getTextContent().trim().toLowerCase().replaceAll("\\s+", "_"); System.out.println("title element: [" + titleString + "]"); } System.out.println(JDBCTutorialUtilities.convertDocumentToString(doc)); PreparedStatement insertRow = null; SQLXML rssData = null; System.out.println("Current DBMS: " + this.dbms); try { if (this.dbms.equals("mysql")) { // For databases that support the SQLXML data type, this creates a // SQLXML object from org.w3c.dom.Document. System.out.println("Adding XML file " + fileName); String insertRowQuery = "insert into RSS_FEEDS (RSS_NAME, RSS_FEED_XML) values" + " (?, ?)"; insertRow = con.prepareStatement(insertRowQuery); insertRow.setString(1, titleString); System.out.println("Creating SQLXML object with MySQL"); rssData = con.createSQLXML(); System.out.println("Creating DOMResult object"); DOMResult dom = (DOMResult)rssData.setResult(DOMResult.class); dom.setNode(doc); insertRow.setSQLXML(2, rssData); System.out.println("Running executeUpdate()"); insertRow.executeUpdate(); } else if (this.dbms.equals("derby")) { System.out.println("Adding XML file " + fileName); String insertRowQuery = "insert into RSS_FEEDS (RSS_NAME, RSS_FEED_XML) values" + " (?, xmlparse(document cast (? as clob) preserve whitespace))"; insertRow = con.prepareStatement(insertRowQuery); insertRow.setString(1, titleString); String convertedDoc = JDBCTutorialUtilities.convertDocumentToString(doc); insertRow.setClob(2, new StringReader(convertedDoc)); System.out.println("Running executeUpdate()"); insertRow.executeUpdate(); } } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } catch (Exception ex) { System.out.println("Another exception caught:"); ex.printStackTrace(); } finally { if (insertRow != null) { insertRow.close(); } } } public void viewTable(Connection con) throws SQLException, ParserConfigurationException, SAXException, IOException, TransformerConfigurationException, TransformerException { Statement stmt = null; try { stmt = con.createStatement(); if (this.dbms.equals("derby")) { String query = "select RSS_NAME, xmlserialize (RSS_FEED_XML as clob) from RSS_FEEDS"; ResultSet rs = stmt.executeQuery(query); while (rs.next()) { String rssName = rs.getString(1); String rssFeedXML = rs.getString(2); javax.xml.parsers.DocumentBuilderFactory factory = javax.xml.parsers.DocumentBuilderFactory.newInstance(); factory.setNamespaceAware(true); DocumentBuilder builder = factory.newDocumentBuilder(); Document doc = builder.parse(new InputSource(new StringReader(rssFeedXML))); System.out.println("RSS identifier: " + rssName); System.out.println(JDBCTutorialUtilities.convertDocumentToString(doc)); } } else if (this.dbms.equals("mysql")) { String query = "select RSS_NAME, RSS_FEED_XML from RSS_FEEDS"; ResultSet rs = stmt.executeQuery(query); while (rs.next()) { String rssName = rs.getString(1); SQLXML rssFeedXML = rs.getSQLXML(2); javax.xml.parsers.DocumentBuilderFactory factory = javax.xml.parsers.DocumentBuilderFactory.newInstance(); factory.setNamespaceAware(true); DocumentBuilder builder = factory.newDocumentBuilder(); Document doc = builder.parse(rssFeedXML.getBinaryStream()); System.out.println("RSS identifier: " + rssName); System.out.println(JDBCTutorialUtilities.convertDocumentToString(doc)); } } } 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(); RSSFeedsTable myRSSFeedsTable = new RSSFeedsTable(myConnection, myJDBCTutorialUtilities.dbName, myJDBCTutorialUtilities.dbms); myRSSFeedsTable.addRSSFeed("xml/rss-coffee-industry-news.xml"); myRSSFeedsTable.addRSSFeed("xml/rss-the-coffee-break-blog.xml"); myRSSFeedsTable.viewTable(myConnection); } catch (Exception e) { e.printStackTrace(); } finally { JDBCTutorialUtilities.closeConnection(myConnection); } } } [C:\Users\Administrator\Desktop\JDBCTutorial\src\com\oracle\tutorial\jdbc\StateFilter.java] /* * 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.SQLException; import javax.sql.RowSet; import javax.sql.rowset.CachedRowSet; import javax.sql.rowset.Predicate; public class StateFilter implements Predicate { private int lo; private int hi; private String colName = null; private int colNumber = -1; public StateFilter(int lo, int hi, int colNumber) { this.lo = lo; this.hi = hi; this.colNumber = colNumber; } public StateFilter(int lo, int hi, String colName) { this.lo = lo; this.hi = hi; this.colName = colName; } public boolean evaluate(Object value, String columnName) { boolean evaluation = true; if (columnName.equalsIgnoreCase(this.colName)) { int columnValue = ((Integer)value).intValue(); if ((columnValue >= this.lo) && (columnValue <= this.hi)) { evaluation = true; } else { evaluation = false; } } return evaluation; } public boolean evaluate(Object value, int columnNumber) { boolean evaluation = true; if (this.colNumber == columnNumber) { int columnValue = ((Integer)value).intValue(); if ((columnValue >= this.lo) && (columnValue <= this.hi)) { evaluation = true; } else { evaluation = false; } } return evaluation; } public boolean evaluate(RowSet rs) { CachedRowSet frs = (CachedRowSet)rs; boolean evaluation = false; try { int columnValue = -1; if (this.colNumber > 0) { columnValue = frs.getInt(this.colNumber); } else if (this.colName != null) { columnValue = frs.getInt(this.colName); } else { return false; } if ((columnValue >= this.lo) && (columnValue <= this.hi)) { evaluation = true; } } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); return false; } catch (NullPointerException npe) { System.out.println("NullPointerException caught"); return false; } return evaluation; } } [C:\Users\Administrator\Desktop\JDBCTutorial\src\com\oracle\tutorial\jdbc\StoredProcedureJavaDBSample.java] /* * 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.math.BigDecimal; import java.math.BigInteger; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; public class StoredProcedureJavaDBSample { private String dbName; private Connection con; private String dbms; private String schema = "APP"; public StoredProcedureJavaDBSample(Connection connArg, String dbName, String dbmsArg) { super(); this.con = connArg; this.dbName = dbName; this.dbms = dbmsArg; } public static void raisePrice(String coffeeName, double maximumPercentage, BigDecimal[] newPrice) throws SQLException { Connection con = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement pstmt = null; ResultSet rs = null; BigDecimal oldPrice; String queryGetCurrentCoffeePrice = "select COFFEES.PRICE " + "from COFFEES " + "where COFFEES.COF_NAME = ?"; pstmt = con.prepareStatement(queryGetCurrentCoffeePrice); pstmt.setString(1, coffeeName); rs = pstmt.executeQuery(); if (rs.next()) { oldPrice = rs.getBigDecimal(1); } else { return; } BigDecimal maximumNewPrice = oldPrice.multiply(new BigDecimal(1 + maximumPercentage)); // Test if newPrice[0] > maximumNewPrice if (newPrice[0].compareTo(maximumNewPrice) == 1) { newPrice[0] = maximumNewPrice; } // Test if newPrice[0] <= oldPrice if (newPrice[0].compareTo(oldPrice) < 1) { newPrice[0] = oldPrice; return; } String queryUpdatePrice = "update COFFEES " + "set COFFEES.PRICE = ? " + "where COFFEES.COF_NAME = ?"; pstmt = con.prepareStatement(queryUpdatePrice); pstmt.setBigDecimal(1, newPrice[0]); pstmt.setString(2, coffeeName); pstmt.executeUpdate(); } public static void getSupplierOfCoffee(String coffeeName, String[] supplierName) throws SQLException { Connection con = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement pstmt = null; ResultSet rs = null; String query = "select SUPPLIERS.SUP_NAME " + "from SUPPLIERS, COFFEES " + "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " + "and ? = COFFEES.COF_NAME"; pstmt = con.prepareStatement(query); pstmt.setString(1, coffeeName); rs = pstmt.executeQuery(); if (rs.next()) { supplierName[0] = rs.getString(1); } else { supplierName[0] = null; } } public static void showSuppliers(ResultSet[] rs) throws SQLException { Connection con = DriverManager.getConnection("jdbc:default:connection"); Statement stmt = null; String query = "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " + "from SUPPLIERS, COFFEES " + "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " + "order by SUP_NAME"; stmt = con.createStatement(); rs[0] = stmt.executeQuery(query); } public void createProcedures(Connection con) throws SQLException { Statement stmtCreateShowSuppliers = null; Statement stmtCreateGetSupplierOfCoffee = null; Statement stmtCreateRaisePrice = null; Statement stmtDropShowSuppliers = null; Statement stmtDropGetSupplierOfCoffee = null; Statement stmtDropRaisePrice = null; String queryDropShowSuppliers = "DROP PROCEDURE SHOW_SUPPLIERS"; String queryDropGetSupplierOfCoffee = "DROP PROCEDURE GET_SUPPLIER_OF_COFFEE"; String queryDropRaisePrice = "DROP PROCEDURE RAISE_PRICE"; String queryShowSuppliers = "CREATE PROCEDURE SHOW_SUPPLIERS() " + "PARAMETER STYLE JAVA " + "LANGUAGE JAVA " + "DYNAMIC RESULT SETS 1 " + "EXTERNAL NAME 'com.oracle.tutorial.jdbc.StoredProcedureJavaDBSample.showSuppliers'"; String queryGetSupplierOfCoffee = "CREATE PROCEDURE GET_SUPPLIER_OF_COFFEE(IN coffeeName varchar(32), OUT supplierName varchar(40)) " + "PARAMETER STYLE JAVA " + "LANGUAGE JAVA " + "DYNAMIC RESULT SETS 0 " + "EXTERNAL NAME 'com.oracle.tutorial.jdbc.StoredProcedureJavaDBSample.getSupplierOfCoffee'"; String queryRaisePrice = "CREATE PROCEDURE RAISE_PRICE(IN coffeeName varchar(32), IN maximumPercentage float, INOUT newPrice numeric(10,2)) " + "PARAMETER STYLE JAVA " + "LANGUAGE JAVA " + "DYNAMIC RESULT SETS 0 " + "EXTERNAL NAME 'com.oracle.tutorial.jdbc.StoredProcedureJavaDBSample.raisePrice'"; try { System.out.println("Calling DROP PROCEDURE"); stmtDropShowSuppliers = con.createStatement(); stmtDropShowSuppliers.execute(queryDropShowSuppliers); stmtDropGetSupplierOfCoffee = con.createStatement(); stmtDropGetSupplierOfCoffee.execute(queryDropGetSupplierOfCoffee); stmtDropRaisePrice = con.createStatement(); stmtDropRaisePrice.execute(queryDropRaisePrice); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (stmtDropShowSuppliers != null) { stmtDropShowSuppliers.close(); } if (stmtDropGetSupplierOfCoffee != null) { stmtDropGetSupplierOfCoffee.close(); } if (stmtDropRaisePrice != null) { stmtDropRaisePrice.close(); } } try { System.out.println("Calling CREATE PROCEDURE"); stmtCreateShowSuppliers = con.createStatement(); stmtCreateShowSuppliers.execute(queryShowSuppliers); stmtCreateGetSupplierOfCoffee = con.createStatement(); stmtCreateGetSupplierOfCoffee.execute(queryGetSupplierOfCoffee); stmtCreateRaisePrice = con.createStatement(); stmtCreateRaisePrice.execute(queryRaisePrice); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (stmtCreateShowSuppliers != null) { stmtCreateShowSuppliers.close(); } if (stmtCreateGetSupplierOfCoffee != null) { stmtCreateGetSupplierOfCoffee.close(); } if (stmtCreateRaisePrice != null) { stmtCreateRaisePrice.close(); } } } public void registerJarFile(String jarPathName) throws SQLException { CallableStatement cs2 = null; CallableStatement cs2a = null; CallableStatement cs3 = null; String query2 = "CALL sqlj.install_jar('" + jarPathName + "','" + this.schema + ".JDBCTutorial',0)"; String query2a = "CALL sqlj.replace_jar('" + jarPathName + "','" + this.schema + ".JDBCTutorial')"; String query3 = "CALL syscs_util.syscs_set_database_property('derby.database.classpath','" + this.schema + ".JDBCTutorial')"; try { System.out.println("Calling " + query2); cs2 = con.prepareCall(query2); cs2.execute(); } catch (SQLException e2) { JDBCTutorialUtilities.printSQLException(e2); } finally { if (cs2 != null) { cs2.close(); } try { System.out.println("Calling " + query2a); cs2a = con.prepareCall(query2a); cs2a.execute(); } catch (SQLException e2a) { JDBCTutorialUtilities.printSQLException(e2a); } finally { if (cs2a != null) { cs2a.close(); } } } try { System.out.println("Calling " + query3); cs3 = con.prepareCall(query3); cs3.execute(); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (cs3 != null) { cs3.close(); } } } public void runStoredProcedures(String coffeeNameArg, double maximumPercentageArg, double newPriceArg) throws SQLException { CallableStatement cs = null; try { System.out.println("\nCalling the stored procedure GET_SUPPLIER_OF_COFFEE"); cs = this.con.prepareCall("{call GET_SUPPLIER_OF_COFFEE(?, ?)}"); cs.setString(1, coffeeNameArg); cs.registerOutParameter(2, Types.VARCHAR); cs.execute(); String supplierName = cs.getString(2); if (supplierName != null) { System.out.println("\nSupplier of the coffee " + coffeeNameArg + ": " + supplierName); } else { System.out.println("\nUnable to find the coffee " + coffeeNameArg); } System.out.println("\nCalling the procedure SHOW_SUPPLIERS"); cs = this.con.prepareCall("{call SHOW_SUPPLIERS()}"); ResultSet rs = cs.executeQuery(); while (rs.next()) { String supplier = rs.getString("SUP_NAME"); String coffee = rs.getString("COF_NAME"); System.out.println(supplier + ": " + coffee); } System.out.println("\nContents of COFFEES table before calling RAISE_PRICE:"); CoffeesTable.viewTable(this.con); System.out.println("\nCalling the procedure RAISE_PRICE"); cs = this.con.prepareCall("{call RAISE_PRICE(?,?,?)}"); cs.setString(1, coffeeNameArg); cs.setDouble(2, maximumPercentageArg); cs.registerOutParameter(3, Types.DOUBLE); cs.setDouble(3, newPriceArg); cs.execute(); System.out.println("\nValue of newPrice after calling RAISE_PRICE: " + cs.getFloat(3)); System.out.println("\nContents of COFFEES table after calling RAISE_PRICE:"); CoffeesTable.viewTable(this.con); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (cs != null) { cs.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(); StoredProcedureJavaDBSample mySP = new StoredProcedureJavaDBSample(myConnection, myJDBCTutorialUtilities.dbName, myJDBCTutorialUtilities.dbms); // JDBCTutorialUtilities.initializeTables(myConnection, // myJDBCTutorialUtilities.dbName, // myJDBCTutorialUtilities.dbms); System.out.println("\nCreating stored procedure:"); mySP.createProcedures(myConnection); // System.out.println("\nAdding jar file to Java DB class path:"); // mySP.registerJarFile(myJDBCTutorialUtilities.jarFile); System.out.println("\nRunning all stored procedures:"); mySP.runStoredProcedures("Colombian", 0.10f, 19.99f); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { JDBCTutorialUtilities.closeConnection(myConnection); } } } [C:\Users\Administrator\Desktop\JDBCTutorial\src\com\oracle\tutorial\jdbc\StoredProcedureMySQLSample.java] /* * 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.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; public class StoredProcedureMySQLSample { private String dbName; private Connection con; private String dbms; public StoredProcedureMySQLSample(Connection connArg, String dbName, String dbmsArg) { super(); this.con = connArg; this.dbName = dbName; this.dbms = dbmsArg; } public void createProcedureRaisePrice() throws SQLException { String createProcedure = null; String queryDrop = "DROP PROCEDURE IF EXISTS RAISE_PRICE"; createProcedure = "create procedure RAISE_PRICE(IN coffeeName varchar(32), IN maximumPercentage float, INOUT newPrice numeric(10,2)) " + "begin " + "main: BEGIN " + "declare maximumNewPrice numeric(10,2); " + "declare oldPrice numeric(10,2); " + "select COFFEES.PRICE into oldPrice " + "from COFFEES " + "where COFFEES.COF_NAME = coffeeName; " + "set maximumNewPrice = oldPrice * (1 + maximumPercentage); " + "if (newPrice > maximumNewPrice) " + "then set newPrice = maximumNewPrice; " + "end if; " + "if (newPrice <= oldPrice) " + "then set newPrice = oldPrice;" + "leave main; " + "end if; " + "update COFFEES " + "set COFFEES.PRICE = newPrice " + "where COFFEES.COF_NAME = coffeeName; " + "select newPrice; " + "END main; " + "end"; Statement stmt = null; Statement stmtDrop = null; try { System.out.println("Calling DROP PROCEDURE"); stmtDrop = con.createStatement(); stmtDrop.execute(queryDrop); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (stmtDrop != null) { stmtDrop.close(); } } try { stmt = con.createStatement(); stmt.executeUpdate(createProcedure); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (stmt != null) { stmt.close(); } } } public void createProcedureGetSupplierOfCoffee() throws SQLException { String createProcedure = null; String queryDrop = "DROP PROCEDURE IF EXISTS GET_SUPPLIER_OF_COFFEE"; createProcedure = "create procedure GET_SUPPLIER_OF_COFFEE(IN coffeeName varchar(32), OUT supplierName varchar(40)) " + "begin " + "select SUPPLIERS.SUP_NAME into supplierName " + "from SUPPLIERS, COFFEES " + "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " + "and coffeeName = COFFEES.COF_NAME; " + "select supplierName; " + "end"; Statement stmt = null; Statement stmtDrop = null; try { System.out.println("Calling DROP PROCEDURE"); stmtDrop = con.createStatement(); stmtDrop.execute(queryDrop); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (stmtDrop != null) { stmtDrop.close(); } } try { stmt = con.createStatement(); stmt.executeUpdate(createProcedure); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (stmt != null) { stmt.close(); } } } public void createProcedureShowSuppliers() throws SQLException { String createProcedure = null; String queryDrop = "DROP PROCEDURE IF EXISTS SHOW_SUPPLIERS"; createProcedure = "create procedure SHOW_SUPPLIERS() " + "begin " + "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " + "from SUPPLIERS, COFFEES " + "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " + "order by SUP_NAME; " + "end"; Statement stmt = null; Statement stmtDrop = null; try { System.out.println("Calling DROP PROCEDURE"); stmtDrop = con.createStatement(); stmtDrop.execute(queryDrop); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (stmtDrop != null) { stmtDrop.close(); } } try { stmt = con.createStatement(); stmt.executeUpdate(createProcedure); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (stmt != null) { stmt.close(); } } } public void runStoredProcedures(String coffeeNameArg, float maximumPercentageArg, float newPriceArg) throws SQLException { CallableStatement cs = null; try { System.out.println("\nCalling the procedure GET_SUPPLIER_OF_COFFEE"); cs = this.con.prepareCall("{call GET_SUPPLIER_OF_COFFEE(?, ?)}"); cs.setString(1, coffeeNameArg); cs.registerOutParameter(2, Types.VARCHAR); cs.executeQuery(); String supplierName = cs.getString(2); if (supplierName != null) { System.out.println("\nSupplier of the coffee " + coffeeNameArg + ": " + supplierName); } else { System.out.println("\nUnable to find the coffee " + coffeeNameArg); } System.out.println("\nCalling the procedure SHOW_SUPPLIERS"); cs = this.con.prepareCall("{call SHOW_SUPPLIERS}"); ResultSet rs = cs.executeQuery(); while (rs.next()) { String supplier = rs.getString("SUP_NAME"); String coffee = rs.getString("COF_NAME"); System.out.println(supplier + ": " + coffee); } System.out.println("\nContents of COFFEES table before calling RAISE_PRICE:"); CoffeesTable.viewTable(this.con); System.out.println("\nCalling the procedure RAISE_PRICE"); cs = this.con.prepareCall("{call RAISE_PRICE(?,?,?)}"); cs.setString(1, coffeeNameArg); cs.setFloat(2, maximumPercentageArg); cs.registerOutParameter(3, Types.NUMERIC); cs.setFloat(3, newPriceArg); cs.execute(); System.out.println("\nValue of newPrice after calling RAISE_PRICE: " + cs.getFloat(3)); System.out.println("\nContents of COFFEES table after calling RAISE_PRICE:"); CoffeesTable.viewTable(this.con); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (cs != null) { cs.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.getConnectionToDatabase(); StoredProcedureMySQLSample myStoredProcedureSample = new StoredProcedureMySQLSample(myConnection, myJDBCTutorialUtilities.dbName, myJDBCTutorialUtilities.dbms); // JDBCTutorialUtilities.initializeTables(myConnection, // myJDBCTutorialUtilities.dbName, // myJDBCTutorialUtilities.dbms); System.out.println("\nCreating SHOW_SUPPLIERS stored procedure"); myStoredProcedureSample.createProcedureShowSuppliers(); System.out.println("\nCreating GET_SUPPLIER_OF_COFFEE stored procedure"); myStoredProcedureSample.createProcedureGetSupplierOfCoffee(); System.out.println("\nCreating RAISE_PRICE stored procedure"); myStoredProcedureSample.createProcedureRaisePrice(); System.out.println("\nCalling all stored procedures:"); myStoredProcedureSample.runStoredProcedures("Colombian", 0.10f, 19.99f); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { JDBCTutorialUtilities.closeConnection(myConnection); } } } [C:\Users\Administrator\Desktop\JDBCTutorial\src\com\oracle\tutorial\jdbc\SuppliersTable.java] /* * 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.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class SuppliersTable { private String dbName; private Connection con; private String dbms; public SuppliersTable(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 SUPPLIERS " + "(SUP_ID integer NOT NULL, " + "SUP_NAME varchar(40) NOT NULL, " + "STREET varchar(40) NOT NULL, " + "CITY varchar(20) NOT NULL, " + "STATE char(2) NOT NULL, " + "ZIP char(5), " + "PRIMARY KEY (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 dropTable() throws SQLException { Statement stmt = null; try { stmt = con.createStatement(); if (this.dbms.equals("mysql")) { System.out.println("Dropping table SUPPLIERS from MySQL"); stmt.executeUpdate("DROP TABLE IF EXISTS SUPPLIERS"); } else if (this.dbms.equals("derby")) { stmt.executeUpdate("DROP TABLE SUPPLIERS"); } } 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 SUPPLIERS " + "values(49, 'Superior Coffee', '1 Party Place', " + "'Mendocino', 'CA', '95460')"); stmt.executeUpdate("insert into SUPPLIERS " + "values(101, 'Acme, Inc.', '99 Market Street', " + "'Groundsville', 'CA', '95199')"); stmt.executeUpdate("insert into SUPPLIERS " + "values(150, 'The High Ground', '100 Coffee Lane', " + "'Meadows', 'CA', '93966')"); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (stmt != null) { stmt.close(); } } } public void viewSuppliers() throws SQLException { Statement stmt = null; String query = "select SUP_NAME, SUP_ID from SUPPLIERS"; try { stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); System.out.println("Suppliers and their ID Numbers:"); while (rs.next()) { String s = rs.getString("SUP_NAME"); int n = rs.getInt("SUP_ID"); System.out.println(s + " " + n); } } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (stmt != null) { stmt.close(); } } } public static void viewTable(Connection con) throws SQLException { Statement stmt = null; String query = "select SUP_ID, SUP_NAME, STREET, CITY, STATE, ZIP from SUPPLIERS"; try { stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); while (rs.next()) { int supplierID = rs.getInt("SUP_ID"); String supplierName = rs.getString("SUP_NAME"); String street = rs.getString("STREET"); String city = rs.getString("CITY"); String state = rs.getString("STATE"); String zip = rs.getString("ZIP"); System.out.println(supplierName + "(" + supplierID + "): " + street + ", " + city + ", " + state + ", " + zip); } } 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); System.out.println("\nContents of SUPPLIERS table:"); SuppliersTable.viewTable(myConnection); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { JDBCTutorialUtilities.closeConnection(myConnection); } } } [C:\Users\Administrator\Desktop\JDBCTutorial\src\com\oracle\tutorial\jdbc\WebRowSetSample.java] /* * 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 com.sun.rowset.CachedRowSetImpl; import com.sun.rowset.WebRowSetImpl; import java.io.FileReader; import java.io.FileWriter; import java.io.IOException; import java.sql.Connection; import java.sql.SQLException; import javax.sql.rowset.WebRowSet; public class WebRowSetSample { private String dbName; private Connection con; private String dbms; private JDBCTutorialUtilities settings; public WebRowSetSample(Connection connArg, JDBCTutorialUtilities settingsArg) { super(); this.con = connArg; this.dbName = settingsArg.dbName; this.dbms = settingsArg.dbms; this.settings = settingsArg; } public void testWebRowSet() throws SQLException, IOException { FileReader fReader = null; FileWriter fWriter = null; String priceListFileName = "pricelist.xml"; int [] keyCols = {1}; WebRowSet priceList = new WebRowSetImpl(); priceList.setUsername(settings.userName); priceList.setPassword(settings.password); priceList.setUrl(settings.urlString); priceList.setCommand("select COF_NAME, PRICE from COFFEES"); priceList.setKeyColumns(keyCols); // Populate the WebRowSet priceList.execute(); System.out.println("Size of the WebRowSet is: " + priceList.size()); // Insert a new row priceList.moveToInsertRow(); priceList.updateString("COF_NAME", "Kona"); priceList.updateFloat("PRICE", 8.99f); priceList.insertRow(); priceList.moveToCurrentRow(); System.out.println("New row inserted"); System.out.println("Size of the WebRowSet is: "+priceList.size()); //Delete the row with "Espresso" priceList.beforeFirst(); while(priceList.next()) { if(priceList.getString(1).equals( "Espresso" )) { System.out.println( "Deleting row with Espresso..." ); priceList.deleteRow(); break; } } // Update price of Colombian priceList.beforeFirst(); while(priceList.next()) { if(priceList.getString(1).equals("Colombian")) { System.out.println("Updating row with Colombian..."); priceList.updateFloat(2, 6.99f); priceList.updateRow(); break; } } int size1 = priceList.size(); fWriter = new FileWriter( priceListFileName ); priceList.writeXml(fWriter); fWriter.flush(); fWriter.close(); // Create the receiving WebRowSet object WebRowSet receiver = new WebRowSetImpl(); receiver.setUrl(settings.urlString); receiver.setUsername(settings.userName); receiver.setPassword(settings.password); //Now read the XML file. fReader = new FileReader( priceListFileName ); receiver.readXml(fReader); int size2 = receiver.size(); if (size1 == size2) { System.out.println( "WebRowSet serialized and " + "deserialiazed properly" ); } else { System.out.println("Error....serializing/deserializng the WebRowSet"); } } 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 WebRowSetSample myWebRowSetSample = new WebRowSetSample(myConnection, myJDBCTutorialUtilities); myWebRowSetSample.testWebRowSet(); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } catch (Exception ex) { System.out.println("Unexpected exception"); ex.printStackTrace(); } finally { JDBCTutorialUtilities.closeConnection(myConnection); } } }