001: package org.springframework.jdbc.support.incrementer;
002:
003: import org.springframework.dao.DataAccessException;
004: import org.springframework.dao.DataAccessResourceFailureException;
005: import org.springframework.jdbc.datasource.DataSourceUtils;
006: import org.springframework.jdbc.support.JdbcUtils;
007:
008: import javax.sql.DataSource;
009: import java.sql.Connection;
010: import java.sql.ResultSet;
011: import java.sql.SQLException;
012: import java.sql.Statement;
013:
014: /**
015: * Class to increment maximum value of a given Derby table with the equivalent
016: * of an auto-increment column. Note: If you use this class, your Derby key
017: * column should <i>NOT</i> be defined as an IDENTITY column, as the sequence table does the job.
018: * Thanks to Endre St¿lsvik for the suggestion!
019: *
020: * <p>The sequence is kept in a table. There should be one sequence table per
021: * table that needs an auto-generated key.
022: *
023: * <p>Derby requires an additional column to be used for the insert since it is impossible
024: * to insert a null into the identity column and have the value generated. This is solved by
025: * providing the name of a dummy column that also must be created in the sequence table.
026: *
027: * <p>Example:
028: *
029: * <pre class="code">create table tab (id int not null primary key, text varchar(100));
030: * create table tab_sequence (value int generated always as identity, dummy char(1));
031: * insert into tab_sequence (dummy) values(null);</pre>
032: *
033: * If cacheSize is set, the intermediate values are served without querying the
034: * database. If the server or your application is stopped or crashes or a transaction
035: * is rolled back, the unused values will never be served. The maximum hole size in
036: * numbering is consequently the value of cacheSize.
037: *
038: * <b>HINT:</b> Since Derby supports the JDBC 3.0 method getGeneratedKeys it's recommended to
039: * use IDENTITY columns directly in the tables and then utilizing a {@link org.springframework.jdbc.support.KeyHolder}
040: * when calling the with the update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder) method of
041: * the {@link org.springframework.jdbc.core.JdbcTemplate}.
042: *
043: * @author Thomas Risberg
044: * @since 2.5
045: */
046: public class DerbyMaxValueIncrementer extends
047: AbstractDataFieldMaxValueIncrementer {
048:
049: /** The default for dummy name */
050: private static final String DEFAULT_DUMMY_NAME = "dummy";
051:
052: /** The name of the column for this sequence */
053: private String columnName;
054:
055: /** The name of the dummy column used for inserts */
056: private String dummyName = DEFAULT_DUMMY_NAME;
057:
058: /** The number of keys buffered in a cache */
059: private int cacheSize = 1;
060:
061: private long[] valueCache = null;
062:
063: /** The next id to serve from the value cache */
064: private int nextValueIndex = -1;
065:
066: /**
067: * Default constructor.
068: **/
069: public DerbyMaxValueIncrementer() {
070: }
071:
072: /**
073: * Convenience constructor.
074: * @param ds the DataSource to use
075: * @param incrementerName the name of the sequence/table to use
076: * @param columnName the name of the column in the sequence table to use
077: **/
078: public DerbyMaxValueIncrementer(DataSource ds,
079: String incrementerName, String columnName) {
080: this (ds, incrementerName, columnName, DEFAULT_DUMMY_NAME);
081: }
082:
083: /**
084: * Convenience constructor.
085: * @param ds the DataSource to use
086: * @param incrementerName the name of the sequence/table to use
087: * @param columnName the name of the column in the sequence table to use
088: * @param dummyName the name of the dummy column used for inserts
089: **/
090: public DerbyMaxValueIncrementer(DataSource ds,
091: String incrementerName, String columnName, String dummyName) {
092: setDataSource(ds);
093: setIncrementerName(incrementerName);
094: this .columnName = columnName;
095: this .dummyName = dummyName;
096: afterPropertiesSet();
097: }
098:
099: /**
100: * Set the name of the column in the sequence table.
101: */
102: public void setColumnName(String columnName) {
103: this .columnName = columnName;
104: }
105:
106: /**
107: * Return the name of the column in the sequence table.
108: */
109: public String getColumnName() {
110: return this .columnName;
111: }
112:
113: /**
114: * Set the name of the dummy column.
115: */
116: public void setDummyName(String dummyName) {
117: this .dummyName = dummyName;
118: }
119:
120: /**
121: * Return the name of the dummy column.
122: */
123: public String getDummyName() {
124: return dummyName;
125: }
126:
127: /**
128: * Set the number of buffered keys.
129: */
130: public void setCacheSize(int cacheSize) {
131: this .cacheSize = cacheSize;
132: }
133:
134: /**
135: * Return the number of buffered keys.
136: */
137: public int getCacheSize() {
138: return this .cacheSize;
139: }
140:
141: public void afterPropertiesSet() {
142: super .afterPropertiesSet();
143: if (this .columnName == null) {
144: throw new IllegalArgumentException("columnName is required");
145: }
146: }
147:
148: protected synchronized long getNextKey() throws DataAccessException {
149: if (this .nextValueIndex < 0
150: || this .nextValueIndex >= getCacheSize()) {
151: /*
152: * Need to use straight JDBC code because we need to make sure that the insert and select
153: * are performed on the same connection (otherwise we can't be sure that last_insert_id()
154: * returned the correct value)
155: */
156: Connection con = DataSourceUtils
157: .getConnection(getDataSource());
158: Statement stmt = null;
159: try {
160: stmt = con.createStatement();
161: DataSourceUtils.applyTransactionTimeout(stmt,
162: getDataSource());
163: this .valueCache = new long[getCacheSize()];
164: this .nextValueIndex = 0;
165: for (int i = 0; i < getCacheSize(); i++) {
166: stmt.executeUpdate("insert into "
167: + getIncrementerName() + " ("
168: + getDummyName() + ") values(null)");
169: ResultSet rs = stmt
170: .executeQuery("select IDENTITY_VAL_LOCAL() from "
171: + getIncrementerName());
172: try {
173: if (!rs.next()) {
174: throw new DataAccessResourceFailureException(
175: "IDENTITY_VAL_LOCAL() failed after executing an update");
176: }
177: this .valueCache[i] = rs.getLong(1);
178: } finally {
179: JdbcUtils.closeResultSet(rs);
180: }
181: }
182: long maxValue = this .valueCache[(this .valueCache.length - 1)];
183: stmt.executeUpdate("delete from "
184: + getIncrementerName() + " where "
185: + this .columnName + " < " + maxValue);
186: } catch (SQLException ex) {
187: throw new DataAccessResourceFailureException(
188: "Could not obtain IDENTITY value", ex);
189: } finally {
190: JdbcUtils.closeStatement(stmt);
191: DataSourceUtils.releaseConnection(con, getDataSource());
192: }
193: }
194: return this.valueCache[this.nextValueIndex++];
195: }
196:
197: }
|