1 /************************************************************** 2 * 3 * Licensed to the Apache Software Foundation (ASF) under one 4 * or more contributor license agreements. See the NOTICE file 5 * distributed with this work for additional information 6 * regarding copyright ownership. The ASF licenses this file 7 * to you under the Apache License, Version 2.0 (the 8 * "License"); you may not use this file except in compliance 9 * with the License. You may obtain a copy of the License at 10 * 11 * http://www.apache.org/licenses/LICENSE-2.0 12 * 13 * Unless required by applicable law or agreed to in writing, 14 * software distributed under the License is distributed on an 15 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 16 * KIND, either express or implied. See the License for the 17 * specific language governing permissions and limitations 18 * under the License. 19 * 20 *************************************************************/ 21 22 23 24 import java.io.*; 25 26 // import com.sun.star.comp.helper.RegistryServiceFactory; 27 // import com.sun.star.comp.servicemanager.ServiceManager; 28 // import com.sun.star.lang.XMultiServiceFactory; 29 // import com.sun.star.lang.XServiceInfo; 30 import com.sun.star.lang.XComponent; 31 // import com.sun.star.bridge.XUnoUrlResolver; 32 import com.sun.star.uno.*; 33 import com.sun.star.util.Date; 34 import com.sun.star.beans.XPropertySet; 35 import com.sun.star.container.XNameAccess; 36 import com.sun.star.sdbc.*; 37 38 public class SalesMan 39 { 40 private XConnection con; 41 SalesMan(XConnection connection )42 public SalesMan(XConnection connection ) 43 { 44 con = connection; 45 } 46 // create the table salesman. createSalesManTable()47 public void createSalesManTable() throws com.sun.star.uno.Exception 48 { 49 String createTableSalesman = "CREATE TABLE SALESMAN " + 50 "(SNR INTEGER NOT NULL, "+ 51 " FIRSTNAME VARCHAR(50)," + 52 " LASTNAME VARCHAR(100)," + 53 " STREET VARCHAR(50)," + 54 " STATE VARCHAR(50)," + 55 " ZIP INTEGER," + 56 " BIRTHDATE DATE," + 57 " PRIMARY KEY(SNR)" + 58 " )"; 59 XStatement stmt = con.createStatement(); 60 stmt.executeUpdate( createTableSalesman ); 61 } 62 63 // drop the table salesman dropSalesManTable()64 public void dropSalesManTable() throws com.sun.star.uno.Exception 65 { 66 String createTableSalesman = "DROP TABLE SALESMAN "; 67 XStatement stmt = con.createStatement(); 68 stmt.executeUpdate( createTableSalesman ); 69 } 70 71 // insert data into the table salesman insertDataIntoSalesMan()72 public void insertDataIntoSalesMan() throws com.sun.star.uno.Exception 73 { 74 XStatement stmt = con.createStatement(); 75 stmt.executeUpdate("INSERT INTO SALESMAN " + 76 "VALUES (1, 'Joseph', 'Smith','Bond Street','CA',95460," 77 + "'1946-07-02')"); 78 stmt.executeUpdate("INSERT INTO SALESMAN " + 79 "VALUES (2, 'Frank', 'Jones','Lake Silver','CA',95460," 80 + "'1963-12-24')"); 81 stmt.executeUpdate("INSERT INTO SALESMAN " + 82 "VALUES (3, 'Jane', 'Esperansa','23 Hollywood drive','CA',95460," 83 + "'1972-04-01')"); 84 stmt.executeUpdate("INSERT INTO SALESMAN " + 85 "VALUES (4, 'George', 'Flint','12 Washington street','CA',95460," 86 + "'1953-02-13')"); 87 stmt.executeUpdate("INSERT INTO SALESMAN " + 88 "VALUES (5, 'Bob', 'Meyers','2 Moon way','CA',95460," 89 + "'1949-09-07')"); 90 } 91 92 // update the table sales man with a prepared statement. updateSalesMan()93 public void updateSalesMan() throws com.sun.star.uno.Exception 94 { 95 XPreparedStatement updateStreet = con.prepareStatement( 96 "UPDATE SALESMAN SET STREET = ? WHERE SNR = ?"); 97 XParameters setPara = (XParameters)UnoRuntime.queryInterface(XParameters.class,updateStreet); 98 setPara.setString(1, "34 Main Road"); 99 setPara.setInt(2, 1); 100 updateStreet.executeUpdate(); 101 102 setPara.setString(1, "Marryland"); 103 setPara.setInt(2, 4); 104 updateStreet.executeUpdate(); 105 // changes STREET column of salesman George to Marryland 106 setPara.setString(1, "Michigan road"); 107 updateStreet.executeUpdate(); 108 // changes again STREET column of salesman George to 109 // Michigan road 110 // parameter 2 stayed 4, and the first parameter was reset 111 // to "Michigan road") 112 113 setPara.setString(1, "Bond Street"); 114 setPara.setInt(2, 3); 115 int n = updateStreet.executeUpdate(); 116 System.out.println("executeUpdate returns: " + n); 117 // n = 1 because one row had a change in it 118 } 119 120 // retrieve the data of the table salesman retrieveSalesManData()121 public void retrieveSalesManData() throws com.sun.star.uno.Exception 122 { 123 XStatement stmt = con.createStatement(); 124 XResultSet rs = stmt.executeQuery("SELECT FIRSTNAME, LASTNAME, BIRTHDATE FROM SALESMAN"); 125 XRow row = (XRow)UnoRuntime.queryInterface(XRow.class,rs); 126 while ( rs != null && rs.next() ) { 127 String fn = row.getString( 1 ); 128 String ln = row.getString( 2 ); 129 Date dt = row.getDate( 3 ); 130 System.out.println(fn + " " + ln + " " + dt.Month + "/" + dt.Day + "/" + dt.Year); 131 } 132 } 133 } 134 135