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 Sales 50 { 51 private XConnection con; 52 53 public Sales(XConnection connection ) 54 { 55 con = connection; 56 } 57 // create the table sales. 58 public void createSalesTable() throws com.sun.star.uno.Exception 59 { 60 String createTableSales = "CREATE TABLE SALES " + 61 "(SALENR INTEGER NOT NULL, " + 62 " COS_NR INTEGER, " + 63 " SNR INTEGER, " + 64 " NAME VARCHAR(50)," + 65 " SALEDATE DATE," + 66 " PRICE FLOAT(10), " + 67 " PRIMARY KEY(SALENR)" + 68 " )"; 69 XStatement stmt = con.createStatement(); 70 stmt.executeUpdate( createTableSales ); 71 } 72 73 // drop the table sales. 74 public void dropSalesTable() throws com.sun.star.uno.Exception 75 { 76 String createTableSalesman = "DROP TABLE SALES "; 77 XStatement stmt = con.createStatement(); 78 stmt.executeUpdate( createTableSalesman ); 79 } 80 81 // insert data into the table sales. 82 public void insertDataIntoSales() throws com.sun.star.uno.Exception 83 { 84 XStatement stmt = con.createStatement(); 85 stmt.executeUpdate("INSERT INTO SALES " + 86 "VALUES (1, '100', '1','Linux','2001-02-12',15)"); 87 stmt.executeUpdate("INSERT INTO SALES " + 88 "VALUES (2, '101', '2','Beef','2001-10-18',15.78)"); 89 stmt.executeUpdate("INSERT INTO SALES " + 90 "VALUES (3, '104', '4','orange juice','2001-08-09',1.5)"); 91 } 92 93 // update the table sales with a prepared statement. 94 public void updateSales() throws com.sun.star.uno.Exception 95 { 96 XStatement stmt = con.createStatement(); 97 String updateString = "UPDATE SALES " + 98 "SET PRICE = 30 " + 99 "WHERE SALENR = 1"; 100 stmt.executeUpdate(updateString); 101 } 102 103 // retrieve the data of the table sales. 104 public void retrieveSalesData() throws com.sun.star.uno.Exception 105 { 106 XStatement stmt = con.createStatement(); 107 String query = "SELECT NAME, PRICE FROM SALES " + 108 "WHERE SALENR = 1"; 109 XResultSet rs = stmt.executeQuery(query); 110 XRow row = (XRow)UnoRuntime.queryInterface(XRow.class, rs); 111 while (rs.next()) { 112 String s = row.getString(1); 113 float n = row.getFloat(2); 114 System.out.println("The current price for " + s + " is: $" + n + "."); 115 } 116 } 117 118 // create a scrollable resultset. 119 public void retrieveSalesData2() throws com.sun.star.uno.Exception 120 { 121 // example for a programmatic way to do updates. This doesn't work with adabas. 122 XStatement stmt = con.createStatement(); 123 XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,stmt); 124 125 xProp.setPropertyValue("ResultSetType", new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE)); 126 xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.UPDATABLE)); 127 128 XResultSet srs = stmt.executeQuery("SELECT NAME, PRICE FROM SALES"); 129 XRow row = (XRow)UnoRuntime.queryInterface(XRow.class,srs); 130 131 srs.afterLast(); 132 while (srs.previous()) { 133 String name = row.getString(1); 134 float price = row.getFloat(2); 135 System.out.println(name + " " + price); 136 } 137 138 srs.last(); 139 XRowUpdate updateRow = (XRowUpdate)UnoRuntime.queryInterface(XRowUpdate.class,srs); 140 updateRow.updateFloat(2, (float)0.69); 141 142 XResultSetUpdate updateRs = ( XResultSetUpdate )UnoRuntime.queryInterface( 143 XResultSetUpdate.class,srs); 144 updateRs.updateRow(); // this call updates the data in DBMS 145 146 srs.last(); 147 updateRow.updateFloat(2, (float)0.99); 148 updateRs.cancelRowUpdates(); 149 updateRow.updateFloat(2, (float)0.79); 150 updateRs.updateRow(); 151 } 152 153 // inserts a row programmatically. 154 public void insertRow() throws com.sun.star.uno.Exception 155 { 156 // example for a programmatic way to do updates. This doesn't work with adabas. 157 XStatement stmt = con.createStatement(); 158 // stmt.executeUpdate("INSERT INTO SALES " + 159 // "VALUES (4, 102, 5, 'FTOP Darjeeling tea', '2002-01-02',150)"); 160 // 161 // stmt = con.createStatement(); 162 XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,stmt); 163 xProp.setPropertyValue("ResultSetType", new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE)); 164 xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.UPDATABLE)); 165 XResultSet rs = stmt.executeQuery("SELECT * FROM SALES"); 166 XRow row = (XRow)UnoRuntime.queryInterface(XRow.class,rs); 167 168 // insert a new row 169 XRowUpdate updateRow = (XRowUpdate)UnoRuntime.queryInterface(XRowUpdate.class,rs); 170 XResultSetUpdate updateRs = ( XResultSetUpdate )UnoRuntime. queryInterface(XResultSetUpdate.class,rs); 171 updateRs.moveToInsertRow(); 172 updateRow.updateInt(1, 4); 173 updateRow.updateInt(2, 102); 174 updateRow.updateInt(3, 5); 175 updateRow.updateString(4, "FTOP Darjeeling tea"); 176 updateRow.updateDate(5, new Date((short)1,(short)2,(short)2002)); 177 updateRow.updateFloat(6, 150); 178 updateRs.insertRow(); 179 } 180 181 // deletes a row programmatically. 182 public void deleteRow() throws com.sun.star.uno.Exception 183 { 184 // example for a programmatic way to do updates. This doesn't work with adabas. 185 XStatement stmt = con.createStatement(); 186 XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,stmt); 187 xProp.setPropertyValue("ResultSetType", new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE)); 188 xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.UPDATABLE)); 189 XResultSet rs = stmt.executeQuery("SELECT * FROM SALES"); 190 XRow row = (XRow)UnoRuntime.queryInterface(XRow.class,rs); 191 192 XResultSetUpdate updateRs = ( XResultSetUpdate )UnoRuntime. queryInterface(XResultSetUpdate.class,rs); 193 // move to the inserted row 194 rs.absolute(4); 195 updateRs.deleteRow(); 196 } 197 198 // refresh a row 199 public void refreshRow() throws com.sun.star.uno.Exception 200 { 201 // example for a programmatic way to do updates. This doesn't work with adabas. 202 // first we need the 4 row 203 insertRow(); 204 205 XStatement stmt = con.createStatement(); 206 XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,stmt); 207 xProp.setPropertyValue("ResultSetType", new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE)); 208 xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.READ_ONLY)); 209 XResultSet rs = stmt.executeQuery("SELECT NAME, PRICE FROM SALES"); 210 XRow row = (XRow)UnoRuntime.queryInterface(XRow.class, rs); 211 rs.absolute(4); 212 float price1 = row.getFloat(2); 213 214 // modifiy the 4 row 215 XRowUpdate updateRow = (XRowUpdate)UnoRuntime.queryInterface(XRowUpdate.class,rs); 216 XResultSetUpdate updateRs = ( XResultSetUpdate )UnoRuntime. queryInterface(XResultSetUpdate.class,rs); 217 updateRow.updateFloat(2, 150); 218 updateRs.updateRow(); 219 // repositioning 220 rs.absolute(4); 221 rs.refreshRow(); 222 float price2 = row.getFloat(2); 223 if (price2 != price1) { 224 System.out.println("Prices are different."); 225 } 226 else 227 System.out.println("Prices are equal."); 228 deleteRow(); 229 } 230 231 // displays the column names 232 public void displayColumnNames() throws com.sun.star.uno.Exception 233 { 234 XStatement stmt = con.createStatement(); 235 XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,stmt); 236 xProp.setPropertyValue("ResultSetType", new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE)); 237 xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.READ_ONLY)); 238 XResultSet rs = stmt.executeQuery("SELECT NAME, PRICE FROM SALES"); 239 XResultSetMetaDataSupplier xRsMetaSup = (XResultSetMetaDataSupplier) 240 UnoRuntime.queryInterface(XResultSetMetaDataSupplier.class,rs); 241 XResultSetMetaData xRsMetaData = xRsMetaSup.getMetaData(); 242 int nColumnCount = xRsMetaData.getColumnCount(); 243 for(int i=1 ; i <= nColumnCount ; ++i) 244 { 245 System.out.println("Name: " + xRsMetaData.getColumnName(i) + " Type: " + 246 xRsMetaData.getColumnType(i)); 247 } 248 } 249 } 250 251