001/* 002 * Copyright 2002-2017 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 * https://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 017package org.springframework.jdbc.support.incrementer; 018 019import java.sql.Connection; 020import java.sql.ResultSet; 021import java.sql.SQLException; 022import java.sql.Statement; 023import javax.sql.DataSource; 024 025import org.springframework.dao.DataAccessException; 026import org.springframework.dao.DataAccessResourceFailureException; 027import org.springframework.jdbc.datasource.DataSourceUtils; 028import org.springframework.jdbc.support.JdbcUtils; 029 030/** 031 * {@link DataFieldMaxValueIncrementer} that increments the maximum value of a given MySQL table 032 * with the equivalent of an auto-increment column. Note: If you use this class, your MySQL 033 * key column should <i>NOT</i> be auto-increment, as the sequence table does the job. 034 * 035 * <p>The sequence is kept in a table; there should be one sequence table per 036 * table that needs an auto-generated key. The storage engine used by the sequence table 037 * can be MYISAM or INNODB since the sequences are allocated using a separate connection 038 * without being affected by any other transactions that might be in progress. 039 * 040 * <p>Example: 041 * 042 * <pre class="code">create table tab (id int unsigned not null primary key, text varchar(100)); 043 * create table tab_sequence (value int not null); 044 * insert into tab_sequence values(0);</pre> 045 * 046 * If "cacheSize" is set, the intermediate values are served without querying the 047 * database. If the server or your application is stopped or crashes or a transaction 048 * is rolled back, the unused values will never be served. The maximum hole size in 049 * numbering is consequently the value of cacheSize. 050 * 051 * <p>It is possible to avoid acquiring a new connection for the incrementer by setting the 052 * "useNewConnection" property to false. In this case you <i>MUST</i> use a non-transactional 053 * storage engine like MYISAM when defining the incrementer table. 054 * 055 * @author Jean-Pierre Pawlak 056 * @author Thomas Risberg 057 * @author Juergen Hoeller 058 */ 059public class MySQLMaxValueIncrementer extends AbstractColumnMaxValueIncrementer { 060 061 /** The SQL string for retrieving the new sequence value */ 062 private static final String VALUE_SQL = "select last_insert_id()"; 063 064 /** The next id to serve */ 065 private long nextId = 0; 066 067 /** The max id to serve */ 068 private long maxId = 0; 069 070 /** Whether or not to use a new connection for the incrementer */ 071 private boolean useNewConnection = false; 072 073 074 /** 075 * Default constructor for bean property style usage. 076 * @see #setDataSource 077 * @see #setIncrementerName 078 * @see #setColumnName 079 */ 080 public MySQLMaxValueIncrementer() { 081 } 082 083 /** 084 * Convenience constructor. 085 * @param dataSource 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 */ 089 public MySQLMaxValueIncrementer(DataSource dataSource, String incrementerName, String columnName) { 090 super(dataSource, incrementerName, columnName); 091 } 092 093 094 /** 095 * Set whether to use a new connection for the incrementer. 096 * <p>{@code true} is necessary to support transactional storage engines, 097 * using an isolated separate transaction for the increment operation. 098 * {@code false} is sufficient if the storage engine of the sequence table 099 * is non-transactional (like MYISAM), avoiding the effort of acquiring an 100 * extra {@code Connection} for the increment operation. 101 * <p>Default is {@code false} in the Spring Framework 4.3.x line. 102 * @since 4.3.6 103 * @see DataSource#getConnection() 104 */ 105 public void setUseNewConnection(boolean useNewConnection) { 106 this.useNewConnection = useNewConnection; 107 } 108 109 110 @Override 111 protected synchronized long getNextKey() throws DataAccessException { 112 if (this.maxId == this.nextId) { 113 /* 114 * If useNewConnection is true, then we obtain a non-managed connection so our modifications 115 * are handled in a separate transaction. If it is false, then we use the current transaction's 116 * connection relying on the use of a non-transactional storage engine like MYISAM for the 117 * incrementer table. We also use straight JDBC code because we need to make sure that the insert 118 * and select are performed on the same connection (otherwise we can't be sure that last_insert_id() 119 * returned the correct value). 120 */ 121 Connection con = null; 122 Statement stmt = null; 123 boolean mustRestoreAutoCommit = false; 124 try { 125 if (this.useNewConnection) { 126 con = getDataSource().getConnection(); 127 if (con.getAutoCommit()) { 128 mustRestoreAutoCommit = true; 129 con.setAutoCommit(false); 130 } 131 } 132 else { 133 con = DataSourceUtils.getConnection(getDataSource()); 134 } 135 stmt = con.createStatement(); 136 if (!this.useNewConnection) { 137 DataSourceUtils.applyTransactionTimeout(stmt, getDataSource()); 138 } 139 // Increment the sequence column... 140 String columnName = getColumnName(); 141 try { 142 stmt.executeUpdate("update " + getIncrementerName() + " set " + columnName + 143 " = last_insert_id(" + columnName + " + " + getCacheSize() + ")"); 144 } 145 catch (SQLException ex) { 146 throw new DataAccessResourceFailureException("Could not increment " + columnName + " for " + 147 getIncrementerName() + " sequence table", ex); 148 } 149 // Retrieve the new max of the sequence column... 150 ResultSet rs = stmt.executeQuery(VALUE_SQL); 151 try { 152 if (!rs.next()) { 153 throw new DataAccessResourceFailureException("last_insert_id() failed after executing an update"); 154 } 155 this.maxId = rs.getLong(1); 156 } 157 finally { 158 JdbcUtils.closeResultSet(rs); 159 } 160 this.nextId = this.maxId - getCacheSize() + 1; 161 } 162 catch (SQLException ex) { 163 throw new DataAccessResourceFailureException("Could not obtain last_insert_id()", ex); 164 } 165 finally { 166 JdbcUtils.closeStatement(stmt); 167 if (con != null) { 168 if (this.useNewConnection) { 169 try { 170 con.commit(); 171 if (mustRestoreAutoCommit) { 172 con.setAutoCommit(true); 173 } 174 } 175 catch (SQLException ignore) { 176 throw new DataAccessResourceFailureException( 177 "Unable to commit new sequence value changes for " + getIncrementerName()); 178 } 179 JdbcUtils.closeConnection(con); 180 } 181 else { 182 DataSourceUtils.releaseConnection(con, getDataSource()); 183 } 184 } 185 } 186 } 187 else { 188 this.nextId++; 189 } 190 return this.nextId; 191 } 192 193}