odk/examples/DevelopersGuide/Database/SalesMan.java

   1 /*************************************************************************
   2  *
   3  *  $RCSfile: SalesMan.java,v $
   4  *
   5  *  $Revision: 1.3 $
   6  *
   7  *  last change: $Author: hr $ $Date: 2003-06-30 15:17:51 $
   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 SalesMan
  56 {
  57     private XConnection con;
  58
  59     public SalesMan(XConnection connection )
  60     {
  61         con = connection;
  62     }
  63     // create the table salesman.
  64     public void createSalesManTable() throws com.sun.star.uno.Exception
  65     {
  66         String createTableSalesman = "CREATE TABLE SALESMAN " +
  67                 "(SNR INTEGER NOT NULL, "+
  68                 " FIRSTNAME VARCHAR(50)," +
  69                 " LASTNAME VARCHAR(100)," +
  70                 " STREET VARCHAR(50)," +
  71                 " STATE VARCHAR(50)," +
  72                 " ZIP INTEGER," +
  73                 " BIRTHDATE DATE," +
  74                 " PRIMARY KEY(SNR)" +
  75                 " )";
  76         XStatement stmt = con.createStatement();
  77         stmt.executeUpdate( createTableSalesman );
  78     }
  79
  80     // drop the table salesman
  81     public void dropSalesManTable() throws com.sun.star.uno.Exception
  82     {
  83         String createTableSalesman = "DROP TABLE SALESMAN ";
  84         XStatement stmt = con.createStatement();
  85         stmt.executeUpdate( createTableSalesman );
  86     }
  87
  88     // insert data into the table salesman
  89     public void insertDataIntoSalesMan() throws com.sun.star.uno.Exception
  90     {
  91         XStatement stmt = con.createStatement();
  92         stmt.executeUpdate("INSERT INTO SALESMAN " +
  93                 "VALUES (1, 'Joseph', 'Smith','Bond Street','CA',95460,"
  94                 + "'1946-07-02')");
  95         stmt.executeUpdate("INSERT INTO SALESMAN " +
  96                 "VALUES (2, 'Frank', 'Jones','Lake Silver','CA',95460,"
  97                 + "'1963-12-24')");
  98         stmt.executeUpdate("INSERT INTO SALESMAN " +
  99                 "VALUES (3, 'Jane', 'Esperansa','23 Hollywood drive','CA',95460,"
 100                 + "'1972-04-01')");
 101         stmt.executeUpdate("INSERT INTO SALESMAN " +
 102                 "VALUES (4, 'George', 'Flint','12 Washington street','CA',95460,"
 103                 + "'1953-02-13')");
 104         stmt.executeUpdate("INSERT INTO SALESMAN " +
 105                 "VALUES (5, 'Bob', 'Meyers','2 Moon way','CA',95460,"
 106                 + "'1949-09-07')");
 107     }
 108
 109     // update the table sales man with a prepared statement.
 110     public void updateSalesMan() throws com.sun.star.uno.Exception
 111     {
 112         XPreparedStatement updateStreet = con.prepareStatement(
 113             "UPDATE SALESMAN SET STREET = ? WHERE SNR = ?");
 114         XParameters setPara = (XParameters)UnoRuntime.queryInterface(XParameters.class,updateStreet);
 115         setPara.setString(1, "34 Main Road");
 116         setPara.setInt(2, 1);
 117         updateStreet.executeUpdate();
 118
 119         setPara.setString(1, "Marryland");
 120         setPara.setInt(2, 4);
 121         updateStreet.executeUpdate();
 122         // changes STREET column of salesman George to Marryland
 123         setPara.setString(1, "Michigan road");
 124         updateStreet.executeUpdate();
 125         // changes again STREET column of salesman George to
 126         // Michigan road
 127         // parameter 2 stayed 4, and the first parameter was reset
 128         // to "Michigan road")
 129
 130         setPara.setString(1, "Bond Street");
 131         setPara.setInt(2, 3);
 132         int n = updateStreet.executeUpdate();
 133         System.out.println("executeUpdate returns: " + n);
 134         // n = 1 because one row had a change in it
 135     }
 136
 137     // retrieve the data of the table salesman
 138     public void retrieveSalesManData() throws com.sun.star.uno.Exception
 139     {
 140         XStatement stmt = con.createStatement();
 141         XResultSet rs   = stmt.executeQuery("SELECT FIRSTNAME, LASTNAME, BIRTHDATE FROM SALESMAN");
 142         XRow row = (XRow)UnoRuntime.queryInterface(XRow.class,rs);
 143         while ( rs != null && rs.next() ) {
 144             String fn = row.getString( 1 );
 145             String ln = row.getString( 2 );
 146             Date   dt = row.getDate( 3 );
 147             System.out.println(fn + "    " + ln + "    " + dt.Month + "/" + dt.Day + "/" + dt.Year);
 148         }
 149     }
 150 }
 151