Inserting and
- Creating Statements
- Inserting and Updating Data
- Getting Data from a Table
The following examples use a sample Adabas D database. Generate an Adabas D database in the OpenOffice.org API installation and define a new table named SALESMAN.
The illustration above shows the definition of the SALESMAN table in the OpenOffice.org API data source administrator. The description column shows the lengths defined for the text fields of the table. After all the fields are defined, right-click the row header of the column SNR and choose Primary Key to make SNR the primary key. Afterwards a small key icon in the row header shows that SNR is the primary key of the table SALESMAN. When completed, save the table as SALESMAN. It is important to use uppercase letters for the table name, otherwise the example SQL code will not work.
The table does not contain any data. Use the following INSERT
command to insert data into the table one row at a time:
INSERT INTO SALESMAN ( SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE ) VALUES ( 1, 'Joseph', 'Smith', 'Bond Street', 'CA', '95460', '1946-07-02' )
The following code sample inserts one row of data with the value 1
in the column SNR
, 'Joseph
' in FIRSTNAME
, 'Smith
' in LASTNAME
, with other information in the following columns of the table SALESMAN. To issue the command against the database, create a Statement
object and then execute it using the method executeUpdate()
:
XStatement xStatement = xConnection.createStatement(); xStatement.executeUpdate("INSERT INTO SALESMAN (" + "SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " + "VALUES (1, 'Joseph', 'Smith','Bond Street','CA','95460','1946-07-02')");
The next call to executeUpdate()
inserts more rows into the table SALESMAN
. Note the Statement object stmt
is reused, rather than creating a new one for each update.
xStatement.executeUpdate("INSERT INTO SALESMAN (" + "SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " + "VALUES (2, 'Frank', 'Jones', 'Lake Silver', 'CA', '95460', '1963-12-24')"); xStatement.executeUpdate("INSERT INTO SALESMAN (" + "SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " + "VALUES (3, 'Jane', 'Esperanza', '23 Hollywood drive', 'CA', '95460', '1972-01-04')"); xStatement.executeUpdate("INSERT INTO SALESMAN (" + "SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " + "VALUES (4, 'George', 'Flint', '12 Washington street', 'CA', '95460', '1953-02-13')"); xStatement.executeUpdate("INSERT INTO SALESMAN (" + "SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " + "VALUES (5, 'Bob', 'Meyers', '2 Moon way', 'CA', '95460', '1949-09-07')");
Updating tables is basically the same process. The SQL command:
UPDATE SALESMAN SET STREET='Grant Street', STATE='FL' WHERE SNR=2
writes a new street and state entry for Frank Jones who has SNR=2. The corresponding executeUpdate()
call looks like this:
int n = xStatement.executeUpdate("UPDATE SALESMAN " + "SET STREET='Grant Street', STATE='FL' " + "WHERE SNR=2");
The return value of executeUpdate()
is an int that indicates how many rows of a table were updated. Our update command affected one row, so n is equal to 1.
Content on this page is licensed under the Public Documentation License (PDL). |