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