Java Reference
In-Depth Information
void deleteProduct(int id) {
sql.execute 'delete from product where id=?', id
}
The
execute
method not only creates the prepared statement, it also inserts the provided
ID into it and executes it. It's hard to get much simpler than that.
Inserting products can use the same method, but with a list of parameters:
void insertProduct(Product p) {
def params = [p.id, p.name, p.price]
sql.execute
'insert into product(id,name,price) values(?,?,?)', params
}
The class has another method called
executeInsert
, which is used if any of the
columns are auto-generated by the database. That method returns a list containing the gen-
erated values. In this example the
id
values are supplied in the program. Auto-generated
values will be considered in
section 8.3
on Hibernate and JPA.
Retrieving products involves a minor complication. There are several useful methods for
querying. Among them are
firstRow
,
eachRow
, and
rows
. The
firstRow
method
is used when a single row is required. Either
eachRow
or
rows
can be used if there are
multiple rows in the result set. In that case,
eachRow
returns a map of column names to
column values, and the
rows
method returns a list of maps, one for each row.
The complication is that the returned column names are in all capitals. For example, the
query
sql.firstRow 'select * from product where id=?', id
returns
[ID:1, NAME:baseball, PRICE:4.99]
for an
id
of
1
. Normally I'd like to use that map as the argument to the
Product
con-
structor, but because the
Product
attributes are all lowercase that won't work.
