代码的功能是生成一个随机的序列化的订单号，正常的情况下一个一个跑没问题，在进行压力测试的时候，多线程高并发下，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

lllzcs

可以动态查询多个单号的语句怎么写

6条回答

为你推荐