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