001: /*
002: * Copyright 2002-2007 the original author or authors.
003: *
004: * Licensed under the Apache License, Version 2.0 (the "License");
005: * you may not use this file except in compliance with the License.
006: * You may obtain a copy of the License at
007: *
008: * http://www.apache.org/licenses/LICENSE-2.0
009: *
010: * Unless required by applicable law or agreed to in writing, software
011: * distributed under the License is distributed on an "AS IS" BASIS,
012: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013: * See the License for the specific language governing permissions and
014: * limitations under the License.
015: */
016:
017: package org.springframework.jdbc.support.incrementer;
018:
019: import java.sql.Connection;
020: import java.sql.ResultSet;
021: import java.sql.SQLException;
022: import java.sql.Statement;
023:
024: import javax.sql.DataSource;
025:
026: import org.springframework.dao.DataAccessException;
027: import org.springframework.dao.DataAccessResourceFailureException;
028: import org.springframework.jdbc.datasource.DataSourceUtils;
029: import org.springframework.jdbc.support.JdbcUtils;
030:
031: /**
032: * Class to increment maximum value of a given MySQL table with the equivalent
033: * of an auto-increment column. Note: If you use this class, your MySQL key
034: * column should <i>NOT</i> be auto-increment, as the sequence table does the job.
035: *
036: * <p>The sequence is kept in a table; there should be one sequence table per
037: * table that needs an auto-generated key. The table type of the sequence table
038: * should be MyISAM so the sequences are allocated without regard to any
039: * transactions that might be in progress.
040: *
041: * <p>Example:
042: *
043: * <pre class="code">create table tab (id int unsigned not null primary key, text varchar(100));
044: * create table tab_sequence (value int not null) type=MYISAM;
045: * insert into tab_sequence values(0);</pre>
046: *
047: * If cacheSize is set, the intermediate values are served without querying the
048: * database. If the server or your application is stopped or crashes or a transaction
049: * is rolled back, the unused values will never be served. The maximum hole size in
050: * numbering is consequently the value of cacheSize.
051: *
052: * @author Jean-Pierre Pawlak
053: * @author Thomas Risberg
054: * @author Juergen Hoeller
055: */
056: public class MySQLMaxValueIncrementer extends
057: AbstractDataFieldMaxValueIncrementer {
058:
059: /** The Sql string for retrieving the new sequence value */
060: private static final String VALUE_SQL = "select last_insert_id()";
061:
062: /** The name of the column for this sequence */
063: private String columnName;
064:
065: /** The number of keys buffered in a cache */
066: private int cacheSize = 1;
067:
068: /** The next id to serve */
069: private long nextId = 0;
070:
071: /** The max id to serve */
072: private long maxId = 0;
073:
074: /**
075: * Default constructor.
076: **/
077: public MySQLMaxValueIncrementer() {
078: }
079:
080: /**
081: * Convenience constructor.
082: * @param ds the DataSource to use
083: * @param incrementerName the name of the sequence/table to use
084: * @param columnName the name of the column in the sequence table to use
085: **/
086: public MySQLMaxValueIncrementer(DataSource ds,
087: String incrementerName, String columnName) {
088: setDataSource(ds);
089: setIncrementerName(incrementerName);
090: this .columnName = columnName;
091: afterPropertiesSet();
092: }
093:
094: /**
095: * Set the name of the column in the sequence table.
096: */
097: public void setColumnName(String columnName) {
098: this .columnName = columnName;
099: }
100:
101: /**
102: * Return the name of the column in the sequence table.
103: */
104: public String getColumnName() {
105: return this .columnName;
106: }
107:
108: /**
109: * Set the number of buffered keys.
110: */
111: public void setCacheSize(int cacheSize) {
112: this .cacheSize = cacheSize;
113: }
114:
115: /**
116: * Return the number of buffered keys.
117: */
118: public int getCacheSize() {
119: return this .cacheSize;
120: }
121:
122: public void afterPropertiesSet() {
123: super .afterPropertiesSet();
124: if (this .columnName == null) {
125: throw new IllegalArgumentException("columnName is required");
126: }
127: }
128:
129: protected synchronized long getNextKey() throws DataAccessException {
130: if (this .maxId == this .nextId) {
131: /*
132: * Need to use straight JDBC code because we need to make sure that the insert and select
133: * are performed on the same connection (otherwise we can't be sure that last_insert_id()
134: * returned the correct value)
135: */
136: Connection con = DataSourceUtils
137: .getConnection(getDataSource());
138: Statement stmt = null;
139: try {
140: stmt = con.createStatement();
141: DataSourceUtils.applyTransactionTimeout(stmt,
142: getDataSource());
143: // increment the sequence column
144: stmt.executeUpdate("update " + getIncrementerName()
145: + " set " + this .columnName
146: + " = last_insert_id(" + this .columnName
147: + " + " + getCacheSize() + ")");
148: // retrieve the new max of the sequence column
149: ResultSet rs = stmt.executeQuery(VALUE_SQL);
150: try {
151: if (!rs.next()) {
152: throw new DataAccessResourceFailureException(
153: "last_insert_id() failed after executing an update");
154: }
155: this .maxId = rs.getLong(1);
156: } finally {
157: JdbcUtils.closeResultSet(rs);
158: }
159: this .nextId = this .maxId - getCacheSize() + 1;
160: } catch (SQLException ex) {
161: throw new DataAccessResourceFailureException(
162: "Could not obtain last_insert_id()", ex);
163: } finally {
164: JdbcUtils.closeStatement(stmt);
165: DataSourceUtils.releaseConnection(con, getDataSource());
166: }
167: } else {
168: this.nextId++;
169: }
170: return this.nextId;
171: }
172:
173: }
|