Updating the database

The following sections of this article provide information about updating the database:

UPDATE and DELETE statements

The UPDATE and DELETE SQL statements are driven through the update method, whereas INSERT statements should be run using the insert method. The update method returns the number of affected rows while the insert method returns a generated key. The parameter passing rules are the same for the query* update and insert methods, including Groovy's GString parameter passing for zero.data.groovy.Manager and use of lists for binding parameters, and Java's variable arguments and named arguments with beans and maps.

An UPDATE and DELETE example

// java
int rowsAffected = data.update("UPDATE APP.test SET innitials=?,name=?",
'bjs', 'Brandon Smith');

// groovy with list parameters
def rowsAffected = data.update('UPDATE APP.test SET innitials=?,name=?',
['bjs', 'Brandon Smith'])

// groovy with gstring
def initials = 'bjs'
def name = 'Brandon Smith'
def rowsAffected = data.update("UPDATE APP.test SET initials=$initials,name=$name")

INSERT statement and auto generated keys

The insert method is provided to support a shortcut to obtaining auto generated keys. There are more differences than other portions of the API between zero.data.groovy.Manager and zero.data.Manager so this documentation is separated. Common to both, however, is that the column name for the autogenerated key is passed.

// groovy using GString to pass parameters
def id = data.insert("INSERT INTO person (name, phone, email) VALUES ($name, $phone, $email)", ['id'])

// groovy using List to pass parameters
def id = data.insert('INSERT INTO person (name, phone, email) VALUES (?,?,?)', ['id'], [name, phone, email])

// java
int id = data.insert("INSERT INTO person (name, phone, email) VALUES (?,?,?)", int.class, new String[] {"id"}, name, phone, email);

The major difference between zero.data.groovy.Manager and zero.data.Manager is that the Groovy version assumes, for simplicity sake, that a java.lang.Number is always returned. However, in zero.data.Manager, the return value of the method is the key itself cast to a class you define. In the above example, int.class is used to indicate to the engine to return the generated key as a Java int.

The subsequent examples illustrate further uses of the zero.data.Manager insert method and assumes the following table:

CREATE TABLE catalog_entry (
id int NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
name varchar(30)
);

Person bean = new Person();
bean.setInitials("hp");
bean.setName("Henry Proctor");

Example: Retrieve key as int

// java
int id = data.insert("insert into APP.TEST (INITIALS, NAME) values (:initials,:name)", int.class, new String[] { "id" }, bean);
assertEquals(1, id);

Example: Retrieve key as Number

// java
Number id = data.insert(sql, Number.class, new String[] { "id" }, bean);
assertEquals(1, id.intValue());

Example: Retrieve key as String

// java
String id = data.insert(sql, String.class, new String[] { "id" }, bean);
assertEquals("1", id);