代码的功能是生成一个随机的序列化的订单号，正常的情况下一个一个跑没问题，在进行压力测试的时候，多线程高并发下，oracle的create语句就会报错，说是
java.lang.RuntimeException: org.springframework.jdbc.BadSqlGrammarException: ConnectionCallback; bad SQL grammar []; nested exception is java.sqlSQLException: ORA-00955: 名称已由现有对象使用
求大神看下。
代码如下：
package utils.spring;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.stereotype.Component;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;
import utils.Lang;
import javax.sql.DataSource;
import java.sql.*;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
/**
* 序列发生器（可用于高并发、可回收等场景）
*
* @author lujijiang
*
*/
@Component
@ConfigurationProperties(prefix = "seqGenerator")
final public class SequenceGenerator implements InitializingBean {
interface Atom<T> {
T atom(Connection connection) throws Exception;
}
public static <T> T atom(Connection connection, Atom<T> atom)
throws Exception {
connection.setAutoCommit(false);
connection
.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
try {
T obj = atom.atom(connection);
connection.commit();
return obj;
} catch (Exception e) {
connection.rollback();
throw e;
}
}
static Map<String, String> createSqlMap = new ConcurrentHashMap<String, String>();
static {
createSqlMap
.put("h2",
"CREATE TABLE %s (v$key BIGINT NOT NULL,v$key$ varchar(256),v$seq BIGINT NOT NULL,v$state CHAR(1) NOT NULL, PRIMARY KEY (v$key,v$seq))");
createSqlMap
.put("mysql",
"CREATE TABLE %s (v$key BIGINT NOT NULL,v$key$ varchar(256),v$seq BIGINT NOT NULL,v$state CHAR(1) NOT NULL, PRIMARY KEY (v$key,v$seq))");
createSqlMap
.put("informix",
"CREATE TABLE %s (v$key DECIMAL(16,0) NOT NULL,v$key$ varchar(256),v$seq DECIMAL(16,0) NOT NULL,v$state CHAR(1) NOT NULL, PRIMARY KEY (v$key,v$seq))");
createSqlMap
.put("oracle",
"CREATE TABLE %s (v$key NUMBER(16,0) NOT NULL,v$key$ varchar2(256),v$seq NUMBER(16,0) NOT NULL,v$state CHAR(1) NOT NULL, PRIMARY KEY (v$key,v$seq))");
}
static Map<String, String> insertSqlMap = new ConcurrentHashMap<String, String>();
static {
insertSqlMap
.put("h2",
"insert into %s(v$key,v$key$,v$seq,v$state) values(%d,'%s',(select ifnull(max(t.v$seq),%d)+%d from %s t where t.v$key=%d),'1')");
insertSqlMap
.put("mysql",
"insert into %s(v$key,v$key$,v$seq,v$state) values(%d,'%s',(select ifnull(max(t.v$seq),%d)+%d from %s t where t.v$key=%d),'1')");
insertSqlMap
.put("informix",
"insert into %s(v$key,v$key$,v$seq,v$state) values(%d,'%s',(select nvl(max(t.v$seq),%d)+%d from %s t where t.v$key=%d),'1')");
insertSqlMap
.put("oracle",
"insert into %s(v$key,v$key$,v$seq,v$state) values(%d,'%s',(select nvl(max(t.v$seq),%d)+%d from %s t where t.v$key=%d),'1')");
}
/**
* 事务模版
*/
TransactionTemplate transactionTemplate;
/**
* 是否使用新事务
*/
boolean newTransaction = true;
/**
* JDBC模版
*/
JdbcTemplate jdbcTemplate;
/**
* 尝试次数（默认10次）
*/
int tryTime = 10;
/**
* 数据库类型
*/
String databaseType;
/**
* 表名
*/
private String tableName;
/**
* 容量
*/
private int capacity = 25;
/**
* 步进
*/
private int step = 1;
/**
* 是否可回收序列号
*/
protected boolean recyclable;
@Autowired
public void setTransactionManager(
PlatformTransactionManager transactionManager) {
if (transactionManager != null) {
transactionTemplate = new TransactionTemplate(transactionManager);
transactionTemplate
.setIsolationLevel(TransactionTemplate.ISOLATION_READ_COMMITTED);
transactionTemplate
.setPropagationBehavior(TransactionTemplate.PROPAGATION_REQUIRED);
}
}
public boolean isNewTransaction() {
return newTransaction;
}
public void setNewTransaction(boolean newTransaction) {
this.newTransaction = newTransaction;
}
public TransactionTemplate getTransactionTemplate() {
return transactionTemplate;
}
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@Autowired
public void setDataSource(DataSource dataSource) {
if (jdbcTemplate == null) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
}
public void setTransactionTemplate(TransactionTemplate transactionTemplate) {
this.transactionTemplate = transactionTemplate;
}
public void setTryTime(int tryTime) {
this.tryTime = tryTime;
}
public void setDatabaseType(String databaseType) {
if (databaseType != null) {
databaseType = databaseType.trim().toLowerCase();
this.databaseType = databaseType;
}
}
public void setTableName(String tableName) {
this.tableName = tableName == null ? null : tableName.trim();
}
public void setCapacity(int capacity) {
this.capacity = capacity;
}
public void setStep(int step) {
this.step = step < 1 ? 1 : step;
}
public void setRecyclable(boolean recyclable) {
this.recyclable = recyclable;
}
/**
* 创建序列
*
* @param name
* @param length
* @param start
* @return
*/
public String generateSequence(String name, int length, long start) {
final long key = generateKey(name);
try {
return generateSequence(key, length, start, name);
} catch (Throwable t) {
createSequenceTable();//错误就是定位在这里
int time = tryTime >= 0 ? tryTime : 10000;
while (time-- > 0) {
try {
return generateSequence(key, length, start, name);
} catch (Throwable e) {
}
}
throw new RuntimeException("Generate sequence failure", t);
}
}
private long generateKey(String name) {
return 10000000000L + name.trim().hashCode();
}
/**
* 创建序列
*
* @param key
* @param length
* @return
*/
public String generateSequence(String key, int length) {
return generateSequence(key, length, 0);
}
/**
* 创建序列
*
* @param key
* @return
*/
public String generateSequence(String key) {
return generateSequence(key, 6);
}
private synchronized void createSequenceTable() {
try {
jdbcTemplate.execute(new ConnectionCallback<Integer>() {
public Integer doInConnection(Connection connection)
throws SQLException, DataAccessException {
ResultSet rs = connection.getMetaData().getTables(null,
null, tableName, null);
try {
while (rs.next()) {
return 1;
}
} finally {
rs.close();
}
String createSql = String.format(
createSqlMap.get(databaseType), tableName);
Statement statement = connection.createStatement();
try {
statement.execute(createSql);//错误就是定位在这里
} finally {
statement.close();
}
return 0;
}
});
} catch (Throwable t) {
throw new RuntimeException(t);
}
}
private String generateSequence(final long key, final int length,
final long start, final String name) {
if (newTransaction) {
try {
Connection connection = jdbcTemplate.getDataSource()
.getConnection();
try {
return atom(connection, new Atom<String>() {
@Override
public String atom(Connection connection)
throws Exception {
if (recyclable) {
String no = getRecoveredSequence(connection,
key, length);
if (no != null) {
return no;
}
}
Statement statement = connection.createStatement();
try {
String insertSql = String.format(insertSqlMap
.get(databaseType), tableName, key,
name.replace("'", "''"), start < 0 ? 0
: start, step, tableName, key);
execute(connection, insertSql);
String querySql = String
.format("select max(v$seq) from %s where v$key=%d and v$state='1'",
tableName, key);
ResultSet rs = statement.executeQuery(querySql);
try {
while (rs.next()) {
Long max = rs.getLong(1);
if (max < start) {// 重新初始化
String deleteSql = String
.format("delete from %s where v$key=%d and v$state='1'",
tableName, key);
execute(connection, deleteSql);
insertSql = String.format(
insertSqlMap
.get(databaseType),
tableName, key,
name.replace("'", "''"),
start < 0 ? 0 : start,
step, tableName, key);
execute(connection, insertSql);
return fillLeftZero(start, length);
}
if (capacity > 0 && max % capacity == 0) {
String deleteSql = String
.format("delete from %s where v$key=%d and v$seq<>%d and v$state='1'",
tableName, key, max);
execute(connection, deleteSql);
}
return fillLeftZero(max, length);
}
} finally {
rs.close();
}
} finally {
statement.close();
}
throw new IllegalStateException(
SequenceGenerator.this.getClass()
.getSimpleName()
+ "无法获取单号，不可能发生的异常");
}
});
} finally {
connection.close();
}
} catch (Exception e) {
throw Lang.unchecked(e);
}
}
return getTransactionTemplate().execute(
new TransactionCallback<String>() {
public String doInTransaction(TransactionStatus ts) {
try {
if (recyclable) {
String no = jdbcTemplate
.execute(new ConnectionCallback<String>() {
public String doInConnection(
Connection connection)
throws SQLException,
DataAccessException {
return getRecoveredSequence(
connection, key, length);
}
});
if (no != null) {
return no;
}
}
String insertSql = String.format(insertSqlMap
.get(databaseType), tableName, key, name
.replace("'", "''"), start < 0 ? 0 : start,
step, tableName, key);
execute(insertSql);
String querySql = String
.format("select max(v$seq) from %s where v$key=%d and v$state='1'",
tableName, key);
Long max = jdbcTemplate.queryForObject(querySql,
Long.class);
if (max < start) {// 重新初始化
String deleteSql = String
.format("delete from %s where v$key=%d and v$state='1'",
tableName, key);
execute(deleteSql);
insertSql = String.format(insertSqlMap
.get(databaseType), tableName, key,
name.replace("'", "''"), start < 0 ? 0
: start, step, tableName, key);
execute(insertSql);
return fillLeftZero(start, length);
}
if (capacity > 0 && max % capacity == 0) {
String deleteSql = String
.format("delete from %s where v$key=%d and v$seq<>%d and v$state='1'",
tableName, key, max);
execute(deleteSql);
}
return fillLeftZero(max, length);
} catch (Throwable t) {
ts.isRollbackOnly();
throw new RuntimeException(t);
}
}
});
}
/**
* 参数化执行SQL
*
* @param sql
* @param args
* @return
*/
protected int execute(String sql, final Object... args) {
return jdbcTemplate.execute(sql,
new PreparedStatementCallback<Integer>() {
public Integer doInPreparedStatement(PreparedStatement ps)
throws SQLException, DataAccessException {
if (args != null) {
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
}
return ps.executeUpdate();
}
});
}
/**
* 参数化执行SQL
*
* @param sql
* @param args
* @return
* @throws SQLException
*/
protected int execute(Connection conn, String sql, final Object... args)
throws SQLException {
PreparedStatement ps = conn.prepareStatement(sql);
try {
if (args != null) {
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
}
return ps.executeUpdate();
} finally {
ps.close();
}
}
protected String fillLeftZero(Long max, int length) {
StringBuilder sb = new StringBuilder();
sb.append(max);
if (sb.length() >= length) {
return sb.substring(sb.length() - length);
} else {
for (int i = sb.length(); i < length; i++) {
sb.insert(0, '0');
}
}
return sb.toString();
}
/**
* 获取已回收的序列
*
* @param connection
* @param key
* @throws SQLException
*/
protected String getRecoveredSequence(Connection connection, long key,
int length) throws SQLException {
Statement statement = connection.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
try {
String querySql = String
.format("select v$key,v$seq,v$state from %s where v$key = %d and v$state<>'1' for update",
tableName, key);
statement.executeQuery(querySql);
querySql = String
.format("select v$key,v$seq,v$state from %s where v$key = %d and v$state<>'1' order by v$seq asc",
tableName, key);
ResultSet rs = statement.executeQuery(querySql);
try {
while (rs.next()) {
try {
Long min = rs.getLong(2);
rs.updateString(3, "1");
rs.updateRow();
return fillLeftZero(min, length);
} catch (Throwable ex) {
}
}
} finally {
rs.close();
}
} finally {
statement.close();
}
return null;
}
/**
* 回收序列号
*
* @param name
* @param sequence
*/
public void recoveredSequence(final String name, final String sequence) {
final long key = generateKey(name);
final long seq = Long.valueOf(sequence);
if (newTransaction) {
try {
Connection connection = jdbcTemplate.getDataSource()
.getConnection();
try {
atom(connection, new Atom<Object>() {
public Object atom(Connection connection)
throws Exception {
String updateSql = String
.format("update %s set v$state='0' where v$key=%d and v$seq=%d",
tableName, key, seq);
int count = execute(connection, updateSql);
if (count == 0) {
String insertSql = String
.format("insert into %s(v$key,v$key$,v$seq,v$state) values(%d,'%s',%d,'0')",
tableName, key,
name.replace("'", "''"), seq);
execute(connection, insertSql);
}
return null;
}
});
connection.commit();
} finally {
connection.close();
}
} catch (Exception e) {
throw Lang.unchecked(e);
}
} else {
transactionTemplate.execute(new TransactionCallback<Integer>() {
public Integer doInTransaction(TransactionStatus status) {
try {
String updateSql = String
.format("update %s set v$state='0' where v$key=%d and v$seq=%d",
tableName, key, seq);
int count = execute(updateSql);
if (count == 0) {
String insertSql = String
.format("insert into %s(v$key,v$key$,v$seq,v$state) values(%d,'%s',%d,'0')",
tableName, key,
name.replace("'", "''"), seq);
execute(insertSql);
}
return null;
} catch (Throwable t) {
status.isRollbackOnly();
throw new RuntimeException(t);
}
}
});
}
}
public void afterPropertiesSet() throws Exception {
if (this.databaseType == null) {
throw new IllegalArgumentException(String.format(
"Property '%s' is required", "databaseType"));
}
if (databaseType != null) {
if (!createSqlMap.containsKey(databaseType)) {
throw new IllegalStateException(String.format(
"Unsupported database type:%s", databaseType));
}
}
if (this.jdbcTemplate == null) {
throw new IllegalArgumentException(String.format(
"Property '%s' or '%s' is required", "dataSource",
"jdbcTemplate"));
}
if (this.tableName == null) {
throw new IllegalArgumentException(String.format(
"Property '%s' is required", "tableName"));
}
if (!tableName.matches("[a-zA-Z_][a-zA-Z0-9_]*")
|| tableName.length() > 16) {
throw new IllegalStateException(String.format(
"Illegal tableName:%s", tableName));
}
if (!this.newTransaction) {
if (this.transactionTemplate == null) {
throw new IllegalArgumentException(String.format(
"Property '%s' is required", "transactionManager"));
}
}
}
}
java.lang.RuntimeException: org.springframework.jdbc.BadSqlGrammarException: ConnectionCallback; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-00955: 名称已由现有对象使用
at utils.spring.SequenceGenerator.createSequenceTable(SequenceGenerator.java:272)
at utils.spring.SequenceGenerator.generateSequence(SequenceGenerator.java:202)
at utils.spring.SequenceGenerator.generateSequence(SequenceGenerator.java:227)
at cart.service.service.OrderService.generateOrderNo(OrderService.java:806)
at cart.service.service.OrderService.saveVirtualGoodsToOrder(OrderService.java:220)
at cart.service.service.OrderService$$FastClassBySpringCGLIB$$19f99be7.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
at cart.service.service.OrderService$$EnhancerBySpringCGLIB$$feb8eef.saveVirtualGoodsToOrder(<generated>)
at cart.service.apiImpl.OrderMainApiImpl.saveVirtualGoodsToOrder(OrderMainApiImpl.java:223)
at cart.service.apiImpl.OrderMainApiImpl$$FastClassBySpringCGLIB$$fa3518e.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:651)
at cart.service.apiImpl.OrderMainApiImpl$$EnhancerBySpringCGLIB$$497f044.saveVirtualGoodsToOrder(<generated>)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.weibo.api.motan.rpc.DefaultProvider.invoke(DefaultProvider.java:57)
at com.weibo.api.motan.rpc.AbstractProvider.call(AbstractProvider.java:47)
at com.weibo.api.motan.filter.AccessLogFilter.filter(AccessLogFilter.java:56)
at com.weibo.api.motan.protocol.support.ProtocolFilterDecorator$2.call(ProtocolFilterDecorator.java:150)
at utils.rpc.motan.filter.OpenEntityManagerInMotanFilter.filter(OpenEntityManagerInMotanFilter.java:171)
at com.weibo.api.motan.protocol.support.ProtocolFilterDecorator$2.call(ProtocolFilterDecorator.java:150)
at com.weibo.api.motan.transport.ProviderMessageRouter.call(ProviderMessageRouter.java:96)
at com.weibo.api.motan.transport.ProviderProtectedMessageRouter.call(ProviderProtectedMessageRouter.java:79)
at com.weibo.api.motan.transport.ProviderMessageRouter.handle(ProviderMessageRouter.java:91)
at com.weibo.api.motan.transport.support.DefaultRpcHeartbeatFactory$HeartMessageHandleWrapper.handle(DefaultRpcHeartbeatFactory.java:82)
at com.weibo.api.motan.transport.netty.NettyChannelHandler.processRequest(NettyChannelHandler.java:139)
at com.weibo.api.motan.transport.netty.NettyChannelHandler.access$000(NettyChannelHandler.java:47)
at com.weibo.api.motan.transport.netty.NettyChannelHandler$1.run(NettyChannelHandler.java:116)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.springframework.jdbc.BadSqlGrammarException: ConnectionCallback; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-00955: 名称已由现有对象使用
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:360)
at utils.spring.SequenceGenerator.createSequenceTable(SequenceGenerator.java:247)
... 39 more
Caused by: java.sql.SQLException: ORA-00955: 名称已由现有对象使用
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:961)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1190)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1726)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1696)
at utils.spring.SequenceGenerator$1.doInConnection(SequenceGenerator.java:264)
at utils.spring.SequenceGenerator$1.doInConnection(SequenceGenerator.java:247)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:353)
... 40 more
