odk/examples/DevelopersGuide/Database/Sales.java

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