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