Inserting Data

It is time to tackle the last data operation—insert. An insert is very similar to an update operation, with one exception: you need to generate the primary key value before you perform the insert, or you need to get the generated primary key value after the insert.[4]

For the insert example, we are going to use PostgreSQL, which allows us to demonstrate how to select a primary key value before, as well as after, an insert operation. If you are using automatically generated values and your database system offers no way to know what the next primary key value is going to be, it certainly offers a way to retrieve a value that was generated by the last insert operation. The catch is in the word "last." In a high-contention system, another insert operation may complete and change the last generated value before you can select it. The result is that even though the rows have been inserted with unique primary keys, your application has two different domain objects with the same value for their primary key. This problem is discussed more extensively in

Let's take a look at the ideal scenario: we select the next value from a sequence and attempt an insert operation. It is impossible for two processes to get the same value from a sequence as each select nextval('<sequence>') increases the sequence counter. Our insert operation consists of two separate steps: selecting the next value from the sequence and performing the actual insert operation. We also want to modify the property of the domain object that stores the primary key value. It sounds complicated, but with iBATIS, it is not at all difficult. Listing 10-37 shows how to select a value from the sequence and then perform the insert operation.

Listing 10-37: SqlMap File Showing How to Select the Primary Key Values

image from book
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE sqlMap PUBLIC "-// SQL Map 2.0//EN" " dtd/sql-map-2.dtd">   <sqlMap>     <!-- as previous -->          <insert  parameter>         <selectKey keyProperty="testId" result>             select nextval('Test_TestId_Seq')         </selectKey>         insert into Test (TestId, Name, RunDate) values (#testId#, #name#,  #runDate#)     </insert> </sqlMap>
image from book

If you look at the <insert> element, you can see that the first step is to select a key from the sequence and then simply use the selected key in the insert operation. The <selectKey> element also sets the domain object's testId property to the value of the selected value from the sequence. The Java implementation of the insert(Test) method is incredibly simple, as you can see in Listing 10-38.

Listing 10-38: TestDao insert(This) iBATIS Implementation

image from book
import com.apress.prospring.ch12.domain.Test;      public class SqlMapClientTestDao      extends SqlMapClientDaoSupport      implements TestDao {          private void insert(Test test) {         getSqlMapClientTemplate().insert("insertTest", test);     }          // other TestDao methods are implemented as stubs }
image from book

That is all the code you have to write, honest. In Listing 10-39, we modify the source code for the Main class to test that our new object does indeed get inserted.

Listing 10-39: Main Class Calling the save(Test) Method

image from book
package com.apress.prospring.ch12;      public class Main {          private ApplicationContext context;          private void run() {         // get the context and testDao         Date today = Calendar.getInstance().getTime();              System.out.println("Inserting new Test record");         Test test = new Test();         test.setName("new one");         test.setRunDate(today);;                  System.out.println("Test inserted " + test);              }          public static void main(String[] args) {         new Main().run();     }      }
image from book

Running the build script, shown in the following output, proves that we are selecting the next value from the sequence, setting the testId property to the sequence value, and inserting a row with the selected testId primary key.

Buildfile: d:\projects\pro-spring\ch12\build.xml init: compile:     [javac] Compiling 2 source files to D:\projects\pro-spring\ch12\build run:      [java] Getting testDao      [java] Inserting new Test record      [java] Test inserted Test { testId: 4, name: new one,          runDate: Wed Aug 25 17:10:33 BST 2004 }      [java] Test { testId: 1, name: foo, runDate: Thu Jan 01 00:00:00 GMT 2004 }      [java] Test { testId: 2, name: bar, runDate: Fri Jan 02 00:00:00 GMT 2004 }      [java] Test { testId: 4, name: new one, runDate: Wed Aug 25 17:10:33 BST 2004 }      [java] Took 13203 ms      [java] Took 27329 ms      [java] Done BUILD SUCCESSFUL Total time: 43 seconds 

Unfortunately, some databases do not support sequences or do not allow us to obtain the value that is generated for the primary key in an insert operation. The only thing we can do is to get the value generated by the last insert operation. One such database is MySQL. If we use MySQL, we have to modify the SQL script (as shown in Listing 10-40) as well as the <insert> element in the SqlMap file.

Listing 10-40: MySQL Create Script

image from book
create table Test (     TestId int auto_increment not null,     Name varchar(50) not null,      RunDate timestamp not null,          constraint PK_TestId primary key (TestId) );
image from book

The only difference is the TestId column definition; instead of using sequence data type, we are using int with the auto_increment modifier. This tells MySQL that it should generate a unique value for the primary key if no value is supplied for the column in the insert statement.

The sqlMap also needs to be modified to select the generated key after the insert operation (see Listing 10-41).

Listing 10-41: sqlMap File for the Test Domain Object for MySQL

image from book
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE sqlMap PUBLIC "-// SQL Map 2.0//EN" " dtd/sql-map-2.dtd">   <sqlMap>     <!-- as previous -->          <insert  parameter>         insert into Test (TestId, Name, RunDate) values (#testId#, #name#,  #runDate#)         <selectKey keyProperty="testId" result>             select select last_insert_id()         </selectKey>     </insert> </sqlMap>
image from book

The implementation of TestDao remains the same. If we create a MySQL database, create the Test table in it, modify the dataSource bean connection properties, and run the application, we get the same output in almost all cases,[5] and we also get the correct value for the generated primary key.

[4] Most databases let you create a table without a primary key, but you should always have a primary key in a table.

[5] The chances of selecting a primary key value generated by another insert operation are remote. An insert operation takes thousands of CPU instructions to finish; the chance that the scheduler of the operating system will switch threads so that this situation would arise are as likely as another Big Bang happening in your coffee mug—but remember, even the Big Bang has happened at least once!

Pro Spring
Pro Spring
ISBN: 1590594614
EAN: 2147483647
Year: 2006
Pages: 189

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: