Advanced Database Persistence for Java
Examples & Reference Manual
|
Advanced Database Persistence for Java Examples & Reference Manual |
This example shows the different ways of inserting on database tables with or without PK and with or without autogeneration PKs.
package examples;
import java.sql.Date;
import java.sql.SQLException;
import java.sql.Timestamp;
import daos.BranchDAO;
import daos.ClientDAO;
import daos.DailyReportDAO;
import daos.VehicleDAO;
import daos.VisitDAO;
/**
* Example 02 - Insert Using Sequences and Identity PKs
*
* @author Vladimir Alarcon
*
*/
public class Example02 {
public static void main(String[] args) throws SQLException {
System.out.println("=== Running Example 02 - Insert Using Sequences and Identity PKs ===");
int rows;
// 1. Insert into a table with no PK
VisitDAO vi = new VisitDAO();
vi.setRecordedAt(Timestamp.valueOf("2017-03-01 11:30:00"));
vi.setBranchId(105);
vi.setNotes("This is a note of the visit");
rows = vi.insert();
System.out.println(" ");
System.out.println("1. Visit inserted (table with no PK). Rows inserted=" + rows);
// 2. Insert into table with PK, but no auto-generation
DailyReportDAO r = new DailyReportDAO();
r.setBranchId(104); // PK column 1
r.setReportDate(Date.valueOf("2017-02-28")); // PK column 2
r.setTotalSold(524850L);
r.insert();
System.out.println(" ");
System.out.println("2. Daily Report inserted (table with PK, but no auto-generation). Rows inserted=" + rows);
// 3. Insert into a table with identity-generated PK
VehicleDAO v = new VehicleDAO();
v.setId(123); // ignored: the DB will generate the PK
v.setBrand("Skoda");
v.setModel("Octavia");
v.setType("CAR");
v.setVin("VN4408");
v.setEngineNumber("EN9401");
v.setMileage(12100);
v.setPurchasedOn(Date.valueOf("2017-02-17"));
v.setBranchId(102);
v.setListPrice(22400);
v.setSold(false);
rows = v.insert();
System.out.println(" ");
System.out.println("3. Vehicle inserted (identity PK). ID=" + v.getId() + ". Rows inserted=" + rows);
// 4. Insert into a table with sequence-generated PK
ClientDAO c = new ClientDAO();
c.setId(123); // ignored: the PK value will be generated by the sequence
c.setCreatedAt(Timestamp.valueOf("2017-02-22 18:15:21"));
c.setName("Lee Van Cliff");
c.setState("NM");
c.setDriversLicense("TB1583-4530-KV");
c.setReferredById(null);
c.setTotalPurchased(0L);
c.setVip(false);
rows = c.insert();
System.out.println(" ");
System.out.println("4. Client inserted (using sequence). ID=" + c.getId() + ". Rows inserted=" + rows);
// 5.a) Insert using into a table with optional identity PK (not specified)
BranchDAO b = new BranchDAO();
b.setId(null); // null: the DB will generate the PK
b.setName("Wichita");
b.setCurrentCash(0);
rows = b.insert();
System.out.println(" ");
System.out.println("5.a) Branch inserted (identity PK not specified). ID=" + b.getId() + ". Rows inserted=" + rows);
// 5.b) Insert using into a table with optional identity PK (specified)
b = new BranchDAO();
b.setId(144); // specified value: this value will be used for the PK
b.setName("Cincinnati");
b.setCurrentCash(0);
rows = b.insert();
System.out.println(" ");
System.out.println("5.b) Branch inserted (identity PK specified). ID=" + b.getId() + ". Rows inserted=" + rows);
System.out.println(" ");
System.out.println("=== Example 02 Complete ===");
}
}
The Example 02 is included in the download package. To run this example please refer to the section How to Run the Examples above.
When inserting into a table with no PK the insert takes a simple form
where you need to speficy the values of the columns you want in a DAO
object and then call the
insert()
method on it.
This is shown inserting into the table
VISIT
that does not have a PK.
When inserting into a table with a PK but no autogeneration logic for its values, you need to speficy the values for the PK column(s) (simple or composite).
The configuration file does not include the
<auto-generated-column>
tag.
This is shown inserting into the table
DAILY_REPORT
that does have a composite PK (
BRANCH_ID
,
REPORT_DATE
) and there's no auto-generation logic for it.
When inserting into a table with an auto-generated PK using identity database functionality, any value you speficy for the PK column(s) are ignored. The database produces the value(s) and this(ese) value(s) are populated back into the DAO object after insertion (except for Oracle 12c, see reference section).
The configuration file includes the following entry to define it's an identity-generated PK:
<auto-generated-column name="id" />
This is shown inserting into the table
VEHICLE
that does have a single column PK (
ID
).
When inserting into a table with an auto-generated PK using a database sequence, any value you speficy for the PK column(s) are ignored. The database produces the value(s) and this(ese) value(s) are populated back into the DAO object after insertion.
The configuration file includes the following entry to define its a sequence-generated PK:
<auto-generated-column name="id" sequence="client_seq" />
This is shown inserting into the table
CLIENT
.
This case is different from case #3 since the developer can actually specify the desired value for the PK.
Most RDBMSs that support PK auto-generation, either as identity PK or auto-increment columns, support this functionality.
If left unspecified (null) the database will generate a new value, and this value will be populated back into the DAO property (except for Oracle 12c, see reference section).
If the PK value(s) are specified its(their) value will be honored.
The configuration file includes the following entry to define it's an identity-generated PK:
<auto-generated-column name="id" allows-specified-value="true" />
This is shown inserting into the table
BRANCH
that does have a single column PK (
ID
).