Inserting Data

Generally speaking, inserts are actually updates to the data. However, there is one major difference: in most cases, inserts have to deal with primary keys. Depending on your database, you may have to set the primary key before you issue the insert statement. Your database may support implicit serial, auto_increment, or identity types; in this case, you need to select the generated key after performing the insert operation.

As you can see, an originally trivial update operation gets more complicated. You need to perform a select and update or an update and select, depending on your database. When you are developing a large system, you usually decide for one database and stick with this choice. Writing only ANSI SQL code is nice, but if you have invested in a sophisticated database, you will want to use its non-ANSI features. Even so, you should always design your data layer code so that if you ever need to change the database, the impact on your code is minimal. This is why we are going to further refactor our DAO classes. Instead of the JdbcTestDao class, we will employ an abstract JdbcAbstractTestDao that performs the majority of the DAO operations and delegate the retrieval of the primary keys to the subclasses.

Unfortunately, even this approach is not ideal because we need to decide whether we are going to select the primary key before or after the insert operation. Of course you could refactor the DAO classes further. You could create JdbcAbstractPKBeforeInsertTestDao as a subclass of JdbcAbstractTestDao whose implementation of the insert() method would get the primary key value before the insert operation, and JdbcAbstractPKAfterInsertTestDao whose insert() method would get the primary key value after the insert operation. We would not recommend that you do this, because full database portability may be a phantom requirement. If the system you are developing is using the Oracle database and the client has already invested in the database, chances are slim that they will change their mind and tell you to program the DAO against MySQL. However, for the sake of argument, in the next section, we implement a JdbcAbstractTestDao whose insert() method gets the generated primary key after the insert operation. Even though this is not an exhaustive example, we believe it is enough to demonstrate the point.

Getting the Primary Key Value

Each database has its own ways to select the generated key, whether it is select last_insert_id() in MySQL, select last_value from Sequence in PostgreSQL, or select @@IDENTITY in Microsoft SQL Server. However, all these identity selections have one thing in common: the database generates the identity as part of the insert command. Several databases require you to first obtain a new identity and then insert it. Oracle is such database; if we use Oracle, we have to modify our code to first select the new identity and then perform the insert, with the value of the primary key specified in the insert statement. The abstract class that contains most of the common SQL code has to be rewritten to satisfy the requirements of generating the identity before an insert. Obtaining an identity before an insert operation is a preferred method, and if your database supports it, you should explicitly select a new primary key from a sequence and then perform an insert. Consider what would happen if the Table table were defined as shown in Listing 8-20.

Listing 8-20: Table Definition

create table Table (     TableId serial not null,     Column varchar(50) not null,     constraint PK_TableId primary key (TableId) )

The serial data type is specific to PostgreSQL; the TableId column would have to be defined as TableId int identity not null in Microsoft SQL Server or TableId int not null auto_increment in MySQL. Let us then consider two transactions, A and B, and a sequence named sequence table_tableid_seq, all of which are running concurrently in PostgreSQL as demonstrated in Table 8-2.

Table 8-2: Potential Risk in Selecting Generated Key After an Insert

Time	Transaction/Process A	Transaction/Process B
0	insert into Table (Column) values ('value 1);
1		insert into Table (Column) values ('value 2);
3		select last_value from table_tableid_seq
4	select last_value from table_tableid_seq

A quick look at the sequence of operations performed by the two transactions or processes reveals that in Time 4, Process A retrieves a value generated by the insert operation performed by Process B. Process B, on the other hand, gets the correct value in Time 3. You may argue that this is not a problem when all your code does is just insert and then not use the generated primary key. However, if you change the code to rely on the generated value, you must make sure you are getting the correct value.

A better approach is to select the next primary key value and then attempt an insert (see Table 8-3). If another transaction commits an insert operation with the same primary key before your transaction is committed, the database reports a primary key constraint violation; you must design your code to cope with this and retry the insert operation. This way you can guarantee that the primary key value is correct.

Table 8-3: Selecting a Primary Key Value Before an Insert Operation

Time	Transaction/Process A	Transaction/Process B
0	select next_value from table_tableid_seq
1	insert into Table (TableId, Column) values (tableId, 'value 1');	select next_value from table_tableid_seq
2	commit	insert into Table (TableId, Column) values (tableId, 'value 2');
3		commit

Table 8-3 illustrates a scenario where both transactions select the next value from the sequence, store it, and attempt an insert with this value. Transaction A succeeds, but Transaction B fails because Transaction A already inserted a record with the same primary key. Process B can retry the insert and, with a bit of luck and less concurrent inserts, it will get the next value and commit the insert before another transaction manages to complete the same procedure.

Unfortunately, MySQL does not support sequences. If you are using it, you must take a chance that no other transaction will commit and thus change the last generated key before you select it. This does not stop you from refactoring your JDBC data access code to work with all databases that only allow you to select the last generated primary key after the insert operation.

Now we are going to refactor the DAO classes: first we create a JdbcAbstractUserDao class that extends JdbcDaoSupport and implements UserDao interfaces. We will keep as much generic JDBC code as possible in the abstract class. The only thing that is most likely to be database- specific is identity selection.

Listing 8-21 shows the code we have after refactoring.

Listing 8-21: JdbcAbstractTestDao Class

public abstract class JdbcAbstractTestDao      extends JdbcDaoSupport implements TestDao {          private static final String INSERT_SQL = "insert into Test (Name) values (?)";          abstract class AbstractSelect extends MappingSqlQuery {         /* ommited for clarity */      }     class SelectByName extends AbstractSelect {          /* ommited for clarity */      }          class Update extends SqlUpdate { /* ommited for clarity */ }     class Delete extends SqlUpdate { /* ommited for clarity */ }          class Insert extends SqlUpdate {         public Insert(DataSource dataSource) {             super(dataSource, INSERT_SQL);             declareParameter(new SqlParameter(Types.VARCHAR));    // Name         }     }          protected abstract String getIdentitySql();          protected void retrieveIdentity(final Test test) {         test.setTestId(getJdbcTemplate().queryForInt(getIdentitySql()));     }          private SelectByName selectByName;     private Update update;     private Delete delete;     private Insert insert;          protected void initDao() throws Exception {         super.initDao();         selectByName = new SelectByName(getDataSource());         update = new Update(getDataSource());         delete = new Delete(getDataSource());         insert = new Insert(getDataSource());     }          public void insert(Test test) {         insert.update(new Object[] { test.getName() });         retrieveIdentity(test);     }          // other TestDao methods omitted for clarity      }

As you can see, we created an abstract class that takes care of all the operations declared in the TestDao interface. It also takes care of selecting the last generated identity from the database using a call to the retrieveIdentity() method. This method uses the utility method, getJdbcTemplate(), provided by the JdbcDaoSupport class. Because the new abstract class is a subclass of JdbcDaoSupport and because JdbcDaoSupport already contains final getters and setters for the DataSource, the final step is to provide an implementation for our abstract class for the MySQL database (see Listing 8-22).

Listing 8-22: MySQL Implementation for JdbcAbstractTestDao

package com.apress.prospring.data.jdbc;      public class MySqlTestDao extends JdbcAbstractTestDao {          protected String getIdentitySql() {         return "select last_insert_id()";     }      } 

This class implements the getIdentitySql() method to return a SQL statement that returns an int of the last generated identity. Let's assume that we had to change the database to PostgreSQL and that the UserId column in the Users table is declared as UserId serial not null primary key. PostgreSQL creates an implicit sequence named users_userid_seq. You can implement the JdbcPostgreSqlUserDao class, as shown in Listing 8-23.

Listing 8-23: PostgreSQL Implementation for JdbcAbstractTestDao

package com.apress.prospring.data.jdbc;      public class PostgreSqlTestDao extends JdbcAbstractTestDao {          protected String getIdentitySql() {         return "select last_value from test_testid_seq";     }      } 

And lo! We have support for both MySQL and PostgreSQL all with just a few lines of code.