odk/examples/DevelopersGuide/Database/Sales.java

   1 /*************************************************************************
   2  *
   3  *  The Contents of this file are made available subject to the terms of
   4  *  the BSD license.
   5  *
   6  *  Copyright 2000, 2010 Oracle and/or its affiliates.
   7  *  All rights reserved.
   8  *
   9  *  Redistribution and use in source and binary forms, with or without
  10  *  modification, are permitted provided that the following conditions
  11  *  are met:
  12  *  1. Redistributions of source code must retain the above copyright
  13  *     notice, this list of conditions and the following disclaimer.
  14  *  2. Redistributions in binary form must reproduce the above copyright
  15  *     notice, this list of conditions and the following disclaimer in the
  16  *     documentation and/or other materials provided with the distribution.
  17  *  3. Neither the name of Sun Microsystems, Inc. nor the names of its
  18  *     contributors may be used to endorse or promote products derived
  19  *     from this software without specific prior written permission.
  20  *
  21  *  THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
  22  *  "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
  23  *  LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
  24  *  FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
  25  *  COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
  26  *  INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
  27  *  BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
  28  *  OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
  29  *  ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
  30  *  TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
  31  *  USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  32  *
  33  *************************************************************************/
  34
  35 //  import com.sun.star.bridge.XUnoUrlResolver;
  36 import com.sun.star.uno.*;
  37 import com.sun.star.util.Date;
  38 import com.sun.star.beans.XPropertySet;
  39 import com.sun.star.sdbc.*;
  40
  41 public class Sales
  42 {
  43     private XConnection con;
  44
  45     public Sales(XConnection connection )
  46     {
  47         con = connection;
  48     }
  49     // create the table sales.
  50     public void createSalesTable() throws com.sun.star.uno.Exception
  51     {
  52         String createTableSales = "CREATE TABLE SALES " +
  53                 "(SALENR INTEGER NOT NULL, " +
  54                 " COS_NR INTEGER, " +
  55                 " SNR INTEGER, " +
  56                 " NAME VARCHAR(50)," +
  57                 " SALEDATE DATE," +
  58                 " PRICE FLOAT(10), " +
  59                 " PRIMARY KEY(SALENR)" +
  60                 " )";
  61         XStatement stmt = con.createStatement();
  62         stmt.executeUpdate( createTableSales );
  63     }
  64
  65     // drop the table sales.
  66     public void dropSalesTable() throws com.sun.star.uno.Exception
  67     {
  68         String createTableSalesman = "DROP TABLE SALES ";
  69         XStatement stmt = con.createStatement();
  70         stmt.executeUpdate( createTableSalesman );
  71     }
  72
  73     // insert data into the table sales.
  74     public void insertDataIntoSales() throws com.sun.star.uno.Exception
  75     {
  76         XStatement stmt = con.createStatement();
  77         stmt.executeUpdate("INSERT INTO SALES " +
  78                 "VALUES (1, '100', '1','Linux','2001-02-12',15)");
  79         stmt.executeUpdate("INSERT INTO SALES " +
  80                 "VALUES (2, '101', '2','Beef','2001-10-18',15.78)");
  81         stmt.executeUpdate("INSERT INTO SALES " +
  82                 "VALUES (3, '104', '4','orange juice','2001-08-09',1.5)");
  83     }
  84
  85     // update the table sales with a prepared statement.
  86     public void updateSales() throws com.sun.star.uno.Exception
  87     {
  88         XStatement stmt = con.createStatement();
  89         String updateString =   "UPDATE SALES " +
  90                                 "SET PRICE = 30 " +
  91                                 "WHERE SALENR = 1";
  92         stmt.executeUpdate(updateString);
  93     }
  94
  95     // retrieve the data of the table sales.
  96     public void retrieveSalesData() throws com.sun.star.uno.Exception
  97     {
  98         XStatement stmt = con.createStatement();
  99         String query =  "SELECT NAME, PRICE FROM SALES " +
 100                         "WHERE SALENR = 1";
 101         XResultSet rs = stmt.executeQuery(query);
 102         XRow      row = UnoRuntime.queryInterface(XRow.class, rs);
 103         while (rs.next()) {
 104                 String s = row.getString(1);
 105                 float  n = row.getFloat(2);
 106                 System.out.println("The current price for " + s + " is: $" + n + ".");
 107         }
 108     }
 109
 110     // create a scrollable resultset.
 111     public void retrieveSalesData2() throws com.sun.star.uno.Exception
 112     {
 113         // example for a programmatic way to do updates.
 114         XStatement stmt = con.createStatement();
 115         XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class,stmt);
 116
 117         xProp.setPropertyValue("ResultSetType", new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE));
 118         xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.UPDATABLE));
 119
 120         XResultSet srs = stmt.executeQuery("SELECT NAME, PRICE FROM SALES");
 121         XRow       row = UnoRuntime.queryInterface(XRow.class,srs);
 122
 123         srs.afterLast();
 124         while (srs.previous()) {
 125             String name = row.getString(1);
 126             float price = row.getFloat(2);
 127             System.out.println(name + "     " + price);
 128         }
 129
 130         srs.last();
 131         XRowUpdate updateRow = UnoRuntime.queryInterface(XRowUpdate.class,srs);
 132         updateRow.updateFloat(2, (float)0.69);
 133
 134         XResultSetUpdate updateRs = UnoRuntime.queryInterface(
 135             XResultSetUpdate.class,srs);
 136         updateRs.updateRow(); // this call updates the data in DBMS
 137
 138         srs.last();
 139         updateRow.updateFloat(2, (float)0.99);
 140         updateRs.cancelRowUpdates();
 141         updateRow.updateFloat(2, (float)0.79);
 142         updateRs.updateRow();
 143     }
 144
 145     // inserts a row programmatically.
 146     public void insertRow() throws com.sun.star.uno.Exception
 147     {
 148         // example for a programmatic way to do updates.
 149         XStatement stmt = con.createStatement();
 150 //      stmt.executeUpdate("INSERT INTO SALES " +
 151 //                   "VALUES (4, 102, 5, 'FTOP Darjeeling tea', '2002-01-02',150)");
 152 //
 153 //      stmt = con.createStatement();
 154         XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class,stmt);
 155         xProp.setPropertyValue("ResultSetType", new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE));
 156         xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.UPDATABLE));
 157         XResultSet rs = stmt.executeQuery("SELECT * FROM SALES");
 158         XRow       row = UnoRuntime.queryInterface(XRow.class,rs);
 159
 160         // insert a new row
 161         XRowUpdate updateRow = UnoRuntime.queryInterface(XRowUpdate.class,rs);
 162         XResultSetUpdate updateRs = UnoRuntime. queryInterface(XResultSetUpdate.class,rs);
 163         updateRs.moveToInsertRow();
 164         updateRow.updateInt(1, 4);
 165         updateRow.updateInt(2, 102);
 166         updateRow.updateInt(3, 5);
 167         updateRow.updateString(4, "FTOP Darjeeling tea");
 168         updateRow.updateDate(5, new Date((short)1,(short)2,(short)2002));
 169         updateRow.updateFloat(6, 150);
 170         updateRs.insertRow();
 171     }
 172
 173     // deletes a row programmatically.
 174     public void deleteRow() throws com.sun.star.uno.Exception
 175     {
 176         // example for a programmatic way to do updates.
 177         XStatement stmt = con.createStatement();
 178         XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class,stmt);
 179         xProp.setPropertyValue("ResultSetType", new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE));
 180         xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.UPDATABLE));
 181         XResultSet rs = stmt.executeQuery("SELECT * FROM SALES");
 182         XRow       row = UnoRuntime.queryInterface(XRow.class,rs);
 183
 184         XResultSetUpdate updateRs = UnoRuntime. queryInterface(XResultSetUpdate.class,rs);
 185         // move to the inserted row
 186         rs.absolute(4);
 187         updateRs.deleteRow();
 188     }
 189
 190     // refresh a row
 191     public void refreshRow() throws com.sun.star.uno.Exception
 192     {
 193         // example for a programmatic way to do updates.
 194         // first we need the 4 row
 195         insertRow();
 196
 197         XStatement stmt = con.createStatement();
 198         XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class,stmt);
 199         xProp.setPropertyValue("ResultSetType", new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE));
 200         xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.READ_ONLY));
 201         XResultSet rs = stmt.executeQuery("SELECT NAME, PRICE FROM SALES");
 202         XRow       row = UnoRuntime.queryInterface(XRow.class, rs);
 203         rs.absolute(4);
 204         float price1 = row.getFloat(2);
 205
 206         // modifiy the 4 row
 207         XRowUpdate updateRow = UnoRuntime.queryInterface(XRowUpdate.class,rs);
 208         XResultSetUpdate updateRs = UnoRuntime. queryInterface(XResultSetUpdate.class,rs);
 209         updateRow.updateFloat(2, 150);
 210         updateRs.updateRow();
 211         // repositioning
 212         rs.absolute(4);
 213         rs.refreshRow();
 214         float price2 = row.getFloat(2);
 215         if (price2 != price1) {
 216             System.out.println("Prices are different.");
 217         }
 218         else
 219             System.out.println("Prices are equal.");
 220         deleteRow();
 221     }
 222
 223     // displays the column names
 224     public void displayColumnNames() throws com.sun.star.uno.Exception
 225     {
 226         XStatement stmt = con.createStatement();
 227         XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class,stmt);
 228         xProp.setPropertyValue("ResultSetType", new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE));
 229         xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.READ_ONLY));
 230         XResultSet rs = stmt.executeQuery("SELECT NAME, PRICE FROM SALES");
 231         XResultSetMetaDataSupplier xRsMetaSup = UnoRuntime.queryInterface(XResultSetMetaDataSupplier.class,rs);
 232         XResultSetMetaData xRsMetaData =  xRsMetaSup.getMetaData();
 233         int nColumnCount =  xRsMetaData.getColumnCount();
 234         for(int i=1 ; i <= nColumnCount ; ++i)
 235         {
 236            System.out.println("Name: " + xRsMetaData.getColumnName(i) + " Type: " +
 237                               xRsMetaData.getColumnType(i));
 238         }
 239     }
 240 }
 241