odk/examples/DevelopersGuide/Database/Sales.java

   1 /* -*- Mode: Java; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
   2 /*************************************************************************
   3  *
   4  *  The Contents of this file are made available subject to the terms of
   5  *  the BSD license.
   6  *
   7  *  Copyright 2000, 2010 Oracle and/or its affiliates.
   8  *  All rights reserved.
   9  *
  10  *  Redistribution and use in source and binary forms, with or without
  11  *  modification, are permitted provided that the following conditions
  12  *  are met:
  13  *  1. Redistributions of source code must retain the above copyright
  14  *     notice, this list of conditions and the following disclaimer.
  15  *  2. Redistributions in binary form must reproduce the above copyright
  16  *     notice, this list of conditions and the following disclaimer in the
  17  *     documentation and/or other materials provided with the distribution.
  18  *  3. Neither the name of Sun Microsystems, Inc. nor the names of its
  19  *     contributors may be used to endorse or promote products derived
  20  *     from this software without specific prior written permission.
  21  *
  22  *  THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
  23  *  "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
  24  *  LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
  25  *  FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
  26  *  COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
  27  *  INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
  28  *  BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
  29  *  OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
  30  *  ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
  31  *  TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
  32  *  USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  33  *
  34  *************************************************************************/
  35
  36 import com.sun.star.uno.*;
  37 import com.sun.star.beans.XPropertySet;
  38 import com.sun.star.sdbc.*;
  39
  40 public class Sales
  41 {
  42     private final XConnection con;
  43
  44     public Sales(XConnection connection )
  45     {
  46         con = connection;
  47     }
  48     // create the table sales.
  49     public void createSalesTable() throws com.sun.star.uno.Exception
  50     {
  51         String createTableSales = "CREATE TABLE SALES " +
  52                 "(SALENR INTEGER NOT NULL, " +
  53                 " COS_NR INTEGER, " +
  54                 " SNR INTEGER, " +
  55                 " NAME VARCHAR(50)," +
  56                 " SALEDATE DATE," +
  57                 " PRICE FLOAT(10), " +
  58                 " PRIMARY KEY(SALENR)" +
  59                 " )";
  60         XStatement stmt = con.createStatement();
  61         stmt.executeUpdate( createTableSales );
  62     }
  63
  64     // drop the table sales.
  65     public void dropSalesTable() throws com.sun.star.uno.Exception
  66     {
  67         String createTableSalesman = "DROP TABLE SALES ";
  68         XStatement stmt = con.createStatement();
  69         stmt.executeUpdate( createTableSalesman );
  70     }
  71
  72     // insert data into the table sales.
  73     public void insertDataIntoSales() throws com.sun.star.uno.Exception
  74     {
  75         XStatement stmt = con.createStatement();
  76         stmt.executeUpdate("INSERT INTO SALES " +
  77                 "VALUES (1, '100', '1','Linux','2001-02-12',15)");
  78         stmt.executeUpdate("INSERT INTO SALES " +
  79                 "VALUES (2, '101', '2','Beef','2001-10-18',15.78)");
  80         stmt.executeUpdate("INSERT INTO SALES " +
  81                 "VALUES (3, '104', '4','orange juice','2001-08-09',1.5)");
  82     }
  83
  84     // update the table sales with a prepared statement.
  85     public void updateSales() throws com.sun.star.uno.Exception
  86     {
  87         XStatement stmt = con.createStatement();
  88         String updateString =   "UPDATE SALES " +
  89                                 "SET PRICE = 30 " +
  90                                 "WHERE SALENR = 1";
  91         stmt.executeUpdate(updateString);
  92     }
  93
  94     // retrieve the data of the table sales.
  95     public void retrieveSalesData() throws com.sun.star.uno.Exception
  96     {
  97         XStatement stmt = con.createStatement();
  98         String query =  "SELECT NAME, PRICE FROM SALES " +
  99                         "WHERE SALENR = 1";
 100         XResultSet rs = stmt.executeQuery(query);
 101         XRow      row = UnoRuntime.queryInterface(XRow.class, rs);
 102         while (rs.next()) {
 103                 String s = row.getString(1);
 104                 float  n = row.getFloat(2);
 105                 System.out.println("The current price for " + s + " is: $" + n + ".");
 106         }
 107     }
 108
 109
 110
 111     // displays the column names
 112     public void displayColumnNames() throws com.sun.star.uno.Exception
 113     {
 114         XStatement stmt = con.createStatement();
 115         XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class,stmt);
 116         xProp.setPropertyValue("ResultSetType", Integer.valueOf(ResultSetType.SCROLL_INSENSITIVE));
 117         xProp.setPropertyValue("ResultSetConcurrency", Integer.valueOf(ResultSetConcurrency.READ_ONLY));
 118         XResultSet rs = stmt.executeQuery("SELECT NAME, PRICE FROM SALES");
 119         XResultSetMetaDataSupplier xRsMetaSup = UnoRuntime.queryInterface(XResultSetMetaDataSupplier.class,rs);
 120         XResultSetMetaData xRsMetaData =  xRsMetaSup.getMetaData();
 121         int nColumnCount =  xRsMetaData.getColumnCount();
 122         for(int i=1 ; i <= nColumnCount ; ++i)
 123         {
 124            System.out.println("Name: " + xRsMetaData.getColumnName(i) + " Type: " +
 125                               xRsMetaData.getColumnType(i));
 126         }
 127     }
 128 }
 129
 130 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */