Skip to content

Latest commit

 

History

History
97 lines (68 loc) · 3.11 KB

insert.md

File metadata and controls

97 lines (68 loc) · 3.11 KB
Raw

Insert

The insert() method receives a VO with the properties of the row to be inserted in a table.

Auto-Generated Columns, Identity Columns & Sequences

Auto-generated columns are columns that are generated by the database automatically on each insert. They can take four forms:

  • IDENTITY GENERATED ALWAYS: The database always generates a value for this column. The INSERT functionality cannot specify it.
  • IDENTITY GENERATED BY DEFAULT: The database generates this value when the INSERT functionality does not include it, or when it includes it with a null. PostgreSQL's SERIAL types and MySQL's AUTO_INCREMENT feature fall into this category.
  • Columns generated by sequences.
  • DEFAULT constraints: CRUD does not populate back the values of columns that have a DEFAULT constraint to produce their values. The developer can use an extra SELECT to retrieve their values.

Nowadays most databases inform the auto-generation properties of a column, and CRUD automatically implements the insertion logic according to it. To use sequences or when the database engine does not provide auto-generation metadata the <auto-generated-column> tag in the configuration can be added to a table to so CRUD knows how to handle a specific column.

If a table column is marked as IDENTITY GENERATED ALWAYS the value provided by the VO is ignored and populated back from the database once the row is inserted.

If a table column is marked as IDENTITY GENERATED BY DEFAULT there are two cases. If the value provided by the VO is null, this value is populated back from the database once the row is inserted. If the value provided by the VO is not null this value is used as an insertion value.

Example

The example shown below (for Oracle 12c1 and newer) includes two tables with identity columns and one that uses a sequence:

create table foo (
  id number(6) primary key generated always as identity,
  name varchar2(20)
);

create table bar (
  id number(6) primary key generated by default as identity,
  part_id number(12)
);

create sequence seq_baz;

create table baz (
  id number(6) primary key -- uses the sequence seq_baz
  amount number(8)
);

The app could insert rows in these tables using the insert() DAO method as:

@Autowired
private FooDAO fooDAO;

@Autowired
private BarDAO barDAO;

@Autowired
private BazDAO bazDAO;

...

FooVO f = new FooVO();
// Notice that setId() must not be used
f.setName("Moby Dick");
this.fooDAO.insert(f);
// The newly generated value for "id" is available in the VO at this point

BarVO b = new BarVO();
b.setPartId(34005);
this.barDAO.insert(b);
// The "id" was not set and the database generated a value for it. This value is available in the VO at this point

BarVO b2 = new BarVO();
b2.setId(101);
b2.setPartId(34012);
this.barDAO.insert(b2);
// The database does not produce any value for the "id" column and uses the provided 101 value instead

BazVO z = new BazVO();
z.setAmount(250);
this.bazDAO.insert(z);
// The database uses a sequence for the "id" column and the generated value is available in the VO at this point