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