Insert, Update, and Delete

The basic functionality for inserting, updating and deleting data is very simple. In addition to the basics, this chapter also explains how to manage transactions which is of course a very important issue when you manipulate the data. For most code snippets in this chapter you can find example code in package modify in the PriDE manual source code repository on GitHub.

Insert

To insert a record in a database table, you create an instance of the corresponding entity class, set all its attributes and call its create() method:

Customer customer = new Customer();
customer.setId(57);
customer.setName("Fingal");
customer.setFirstnae("Paddy");
customer.create();

When you are working with separate adapters instead, create() is a method of the adapter and the code looks like that:

CustomerEntity customer = new CustomerEntity();
customer.setId(57);
customer.setName("Fingal");
customer.setFirstnae("Paddy");
new CustomerAdater(customer).create();

You can insert multiple records successively using the same entity (and adapter) by changing the entity’s data and repeatedly call create(). This is OK for small amounts of inserts. If you have to insert thousands or hundreds of thousands records, you better work with the class pm.pride.PreparedInsert as explained in chapter Prepared Operations.

If the addressed database table has auto-increment rows, you can specify these rows in the descriptor by a call of method auto() with a list of column names. In this case you leave the appropriate attributes uninitialized, and after creation PriDE will set them according to the values generated by the database. Expressing auto-incrementation in a database table definition is always a bit vendor-specific as well as the supported generation features in general. Supposed you are still experimenting with the SQLite database from the Quick Start Tutorial, you could create a modification of the CUSTOMER table as follows to make the ID and auto-increment row:

create table AUTOINCCUSTOMER (
    id integer not null primary key AUTOINCREMENT,
    name varchar(20),
    first_name varchar(30)
);

An appropriate hybrid entity class looks exactly like the one from the Quick Start Tutorial only extended by the following line at the end of the descriptor definition:

protected static final RecordDescriptor red =
    //...
    .auto(COL_ID);

Based on that, the following loop creates 10 unique test customers in a row and prints out the auto-generated ID of each of them:

Customer customer = new Customer();
for (int i = 0; i < 10; i++) {
    customer.setName("Fingal-" + i);
    customer.setFirstName("Paddy");
    customer.create();
    System.out.println(customer.getId());
}

You find an example for a customer class with auto-increment ID in package modify in the PriDE manual source code repository on GitHub.

Transactions

Try to write a loop as above without anything else and you will recognize that it does not produce any rows at all in you CUSTOMER table. Most SQL databases are fully transaction-saved by default and thus require the application to properly commit its work. The foundation for transaction management with commit and rollback is the ACID principle, which every developer must be well aware of as it is deep-seated in JDBC and every JDBC-based persistence manager.

In a JSE environment you will by default lose all your database work when the application terminates and you forgot to explicitly run a commit operation. In enterprise environments like standard JEE or Spring the application is usually not responsible for ending transactions by programmatic operations. Most applications use so-called container-managed transactions which are implicitly controlled by method annotations and exception handling. This is a very convenient and recommendable technique and it is also the key for composing higher-level methods from calls of lower-level methods in an elegant way according to the Single Level of Abstraction Principle. No method has to worry about whether it is the very top-level of the (potentially still growing) composition tree.

PriDE does not really manage transactions by its own but relies on the transaction management of the environment it is used in. The link between PriDE and its environment is the ResourceAccessor interface and you have to install one somewhere in your application. The chapter JSE, JEE, and ResourceAccessor will explain that in detail. Except in this chapter, all other examples in this manual are working on a simple JSE environments and use the class ResourceAccessorJSE. So here is how this resource accessor works concerning its simple connection management and the resulting transaction behavior:

As soon as you access the database for the first time, the accessor opens a database connection and binds it to the current thread. I.e. all succeeding database operations within the same thread are performed on the same database connection.
The ResourceAccessorJSE has no connection pooling, i.e. the number of concurrently allocated database connections corresponds to the number of threads requiring access to the database. This is not a suitable model for server applications, but if you implement server components with PriDE, you will hopefully work in a JEE environment.
The current database transaction can be committed by the call DatabaseFactory.getDatabase().commit(). This addresses only the connection being bound to the current thread. After committing, the connection is kept open and related to the thread so that subsequent databases operations run without warming up a new connection.
Alternatively the commit can be initiated from any adapter resp. hybrid entity as well. This is especially of interest when working with multiple databases because the commit then refers to the database which the entity resides in whereas DatabaseFactory.getDatabase() refers to the database which was addressed at last. This will be explained in detail in chapter Multiple Databases. When you are working with a single database, these operations are equivalent.
Instead of committing you may rollback your work by calling the rollback() method either on the database object or an adapter resp. a hybrid entity.
The resource accessor explicitly turns off auto-commit for every allocated connection.

Coming back to the example for customer creation above, the code must be completed as follows:

Customer customer = new Customer();
for (int i = 0; i < 10; i++) {
    // see above
}
customer.commit();

As a result the code will either successfully create all 10 customers or non at all because the whole work of the loop is committed at once at the end. If any of the 10 insert operations fails with an exception, the commit call would be skipped. This causes the application to terminate without any commit which in turn causes and implicit rollback. If you write an application which runs for a long time and is supposed to survive severe exceptions (i.e. a UI client), you should pay some attention on making the application robust against accidentally unterminated transactions. A recommended Java feature for an appropriate safety net is the UncaughtExceptionHandler interface. You can install a handler to every thread which preventively performs a rollback call.

Update

Updating a record is performed by calling the update() method of the adapter resp. the hybrid entity. All fields listed in the record descriptor’s key() method call are used to identify the record, and all other fields are updated. The code

Customer paddy = new Customer(57);
paddy.setFirstName("Paddy");
paddy.update();
paddy.commit();

results in the following SQL statements as you can see from the log file:

select id,name,first_name from CUSTOMER where ( id = 57 ) 
update CUSTOMER set name = 'Fingal',first_name = 'Paddy' where id = 57

All update calls return the number of affected rows which should be 0 or 1 in case of an update by primary key. It’s up to you if you check the result. PriDE has no detection which attributes actually changed since an entity has been loaded, so it simply updates all attributes which are not part of the primary key. As PriDE has no instance and change management, updates always have to be explicitly performed by the application. If you are familiar with JPA, you may recognize that the concepts are very different concerning this aspect. The chapter PriDE Design Principles explains why the much simpler approach of PriDE is not a loss.

Updating single rows by update() calls is OK for a limited number of operations per transaction. If you have to update thousands of records instead you should consider working with the class pm.pride.PreparedUpdate as explained in chapter Prepared Operations.

There are a few variants of the update() method available which allow to update multiple records at once. E.g. the method update(WhereCondition where, String... updatefields) can address the records of interest by a where condition. In these cases there are usually only particular fields requiring an update. The following example demonstrates how to change all first names from “Paddy” to “Patrick”:

Customer customer = new Customer();
customer.setFirstName("Patrick");
customer.update(new WhereCondition(COL_FIRST_NAME, "Paddy"), COL_FIRST_NAME);
customer.commit();

The code above makes clear that multi-record updates are not necessarily best to understand when they are expressed by entity operations. Have a look on the resulting update statement which is pretty clear to understand:

update CUSTOMER set first_name = 'Patrick' where ( first_name = 'Paddy' )

If the entity layer is not appropriate, you have a lower level at hand using the class pm.pride.Database. You get access to the current database by the call DatabaseFactory.getDatebase(). Here is how the renaming update looks like with a combination of the Database class and the SQLExpressionBuilder:

Database database = DatabaseFactory.getDatabase();
String operation = SQL.build(
    "update @CUSTOMER set @first_name = 'Patrick' where ( @first_name = 'Paddy' )",
    TABLE, COL_FIRST_NAME);
database.sqlUpdate(operation);
database.commit();

In this example, the SQL code is almost present in its native, well recognizable form but it is still based on the entity’s table and column name constants. The DRY principle and dependency tracking is kept up properly. The commit is performed on the Database instance as explained in section Transactions above.

Delete

To delete a record you have to call the delete() method of the adapter resp. the hybrid entity. All fields listed in the record descriptor’s key() method call are used to identify the record. All other fields are ignored, so whether they are initialized or not is irrelevant. The following code deletes the customer with ID 57:

Customer c57 = new Customer();
c57.setId(57);
paddy.delete();
paddy.commit();

Note that the code above is based on a hybrid entity and does not use the re-constructor which would immediately initiate a find operation. Entities don’t have to be loaded before deletion. Just like updates, every deletion returns the number of affected rows and it’s up to you if you check the result.

There is a deleteByExample() method available which allows to specify a different set of key attributes and usually deletes multiple records at once. More complicated multi-record deletions can be performed by the sqlUpdate() method of the Database class similar to the example section Update.