您的位置: 首页  >  文章  >  DB2 创建并使用SEQUENCE

DB2 创建并使用SEQUENCE

分类: 文章 • 2023-04-05 15:31:16

1. 创建SEQUENCE

CREATE SEQUENCE "SCHEMA"."SEQ_USER_ID" AS BIGINT START WITH 1 INCREMENT BY 1 
NO MINVALUE NO MAXVALUE NO CYCLE CACHE 20 NO ORDER

2. 使用SEQUENCE

User表结构如下：

CREATE TABLE USER (
    USER_ID BIGINT NOT NULL,
    USER_NAME CHARACTER(30),
    PRIMARY KEY (USER_ID)
)

获取SEQUENCE的值：

-- method1: NEXTVAL FOR schema.sequence_name
INSERT INTO SCHEMA.USER(USER_ID, USER_NAME) VALUES(NEXTVAL FOR SCHEMA.SEQ_USER_ID, 'username1')

-- method2: query sql, 直接执行这个SQL可以获取到SEQUENCE值
VALUES NEXTVAL FOR schema.sequence_name

3. Spring对DB2 SEQUENCE的支持

在Spring配置文件中添加如下配置：

<!-- 
	1. 引用datasource
	2. 设置SEQUENCE名称
	3. 如果需要字符串形式的SEQUENCE值，则可以在左侧填充'0'
-->
<bean id="userIdSequence" class="org.springframework.jdbc.support.incrementer.DB2SequenceMaxValueIncrementer"
	p:dataSource-ref="dataSource" p:incrementerName="SEQ_USER_ID" p:paddingLength="10" />
<!-- 注意：incrementerName可能需要写成SCHEMA_NAME.SEQ_NAME的形式 -->

Java代码中调用：

@Autowired
private DB2SequenceMaxValueIncrementer userIdSequence;

public void xxx() {
    System.out.println(userIdSequence.nextIntValue()); // int 类型 1
    System.out.println(userIdSequence.nextLongValue());// long类型 2
    System.out.println(userIdSequence.nextStringValue());// string类型 "0000000003"
}

4. 扩展 DB2SequenceMaxValueIncrementer源码解析

Spring JDBC对SEQUENCE的支持：

DB2 创建并使用SEQUENCE

DB2SequenceMaxValueIncrementer的继承及实现关系如下：DB2SequenceMaxValueIncrementer extends AbstractSequenceMaxValueIncrementer  extends AbstractDataFieldMaxValueIncrementer implement DataFieldMaxValueIncrementer, InitializingBean

4.1 DataFieldMaxValueIncrementer接口

该接口用于获取某个字段的自增最大值（比如使用标准SQL实现，使用RDBMS中的SEQUENCE实现或者使用存储过程来实现），其中定义了如下3个方法：nextIntValue(), nextLongValue() 和nextStringValue()。

package org.springframework.jdbc.support.incrementer;
import org.springframework.dao.DataAccessException;

/**
 * Interface that defines contract of incrementing any data store field's
 * maximum value. Works much like a sequence number generator.
 *
 * <p>Typical implementations may use standard SQL, native RDBMS sequences
 * or Stored Procedures to do the job.
 *
 */
public interface DataFieldMaxValueIncrementer {
	/**
	 * Increment the data store field's max value as int.
	 * @return int next data store value such as <b>max + 1</b>
	 * @throws org.springframework.dao.DataAccessException in case of errors
	 */
	int nextIntValue() throws DataAccessException;

	/**
	 * Increment the data store field's max value as long.
	 * @return int next data store value such as <b>max + 1</b>
	 * @throws org.springframework.dao.DataAccessException in case of errors
	 */
	long nextLongValue() throws DataAccessException;

	/**
	 * Increment the data store field's max value as String.
	 * @return next data store value such as <b>max + 1</b>
	 * @throws org.springframework.dao.DataAccessException in case of errors
	 */
	String nextStringValue() throws DataAccessException;
}

4.2 AbstractDataFieldMaxValueIncrementer抽象类

该抽象类作为DataFieldMaxValueIncrementer接口的基本实现，把具体实现委派到抽象的getNextKey()方法上。源码如下：

package org.springframework.jdbc.support.incrementer;
import javax.sql.DataSource;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.dao.DataAccessException;
import org.springframework.util.Assert;

/**
 * Base implementation of {@link DataFieldMaxValueIncrementer} that delegates
 * to a single {@link #getNextKey} template method that returns a {@code long}.
 * Uses longs for String values, padding with zeroes if required.
 */
public abstract class AbstractDataFieldMaxValueIncrementer implements DataFieldMaxValueIncrementer, InitializingBean {
	private DataSource dataSource;

	/** The name of the sequence/table containing the sequence */
	private String incrementerName;

	/** The length to which a string result should be pre-pended with zeroes */
	protected int paddingLength = 0;

    // 构造方法
	public AbstractDataFieldMaxValueIncrementer() {
	}
	public AbstractDataFieldMaxValueIncrementer(DataSource dataSource, String incrementerName) {
		Assert.notNull(dataSource, "DataSource must not be null");
		Assert.notNull(incrementerName, "Incrementer name must not be null");
		this.dataSource = dataSource;
		this.incrementerName = incrementerName;
	}

    // 获取下一个值必须要要有dataSource和incrementerName
    @Override
	public void afterPropertiesSet() {
		if (this.dataSource == null) {
			throw new IllegalArgumentException("Property 'dataSource' is required");
		}
		if (this.incrementerName == null) {
			throw new IllegalArgumentException("Property 'incrementerName' is required");
		}
	}
	
    // 获取int值（long值强转为int值）
    @Override
	public int nextIntValue() throws DataAccessException {
		return (int) getNextKey();
	}

    // 获取long值
    @Override
	public long nextLongValue() throws DataAccessException {
		return getNextKey();
	}

    // 获取String值，为String值的左侧填充'0'直到等于paddingLength
    @Override
	public String nextStringValue() throws DataAccessException {
		String s = Long.toString(getNextKey());
		int len = s.length();
		if (len < this.paddingLength) {
			StringBuilder sb = new StringBuilder(this.paddingLength);
			for (int i = 0; i < this.paddingLength - len; i++) {
				sb.append('0');
			}
			sb.append(s);
			s = sb.toString();
		}
		return s;
	}

	/**
	 * Determine the next key to use, as a long.
	 * @return the key to use as a long. It will eventually be converted later
	 * in another format by the public concrete methods of this class.
	 */
  	// 最终需要实现的抽象方法，此方法返回一个long类型的值
	protected abstract long getNextKey();
}

4.3 AbstractSequenceMaxValueIncrementer抽象类

这个抽象类专门作为使用SEQUENCE作为自增值的情况。

package org.springframework.jdbc.support.incrementer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
/**
 * Abstract base class for {@link DataFieldMaxValueIncrementer} implementations that use
 * a database sequence. Subclasses need to provide the database-specific SQL to use.
 */
public abstract class AbstractSequenceMaxValueIncrementer extends AbstractDataFieldMaxValueIncrementer {
	// 构造方法
    public AbstractSequenceMaxValueIncrementer() {
	}
	public AbstractSequenceMaxValueIncrementer(DataSource dataSource, String incrementerName) {
		super(dataSource, incrementerName);
	}


	/**
     * 执行SQL查询来查询SEQUENCE的下一个值
	 * Executes the SQL as specified by {@link #getSequenceQuery()}.
	 */
	@Override
	protected long getNextKey() throws DataAccessException {
		Connection con = DataSourceUtils.getConnection(getDataSource());
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = con.createStatement();
			DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
			rs = stmt.executeQuery(getSequenceQuery());
			if (rs.next()) {
				return rs.getLong(1);
			}
			else {
				throw new DataAccessResourceFailureException("Sequence query did not return a result");
			}
		}
		catch (SQLException ex) {
			throw new DataAccessResourceFailureException("Could not obtain sequence value", ex);
		}
		finally {
			JdbcUtils.closeResultSet(rs);
			JdbcUtils.closeStatement(stmt);
			DataSourceUtils.releaseConnection(con, getDataSource());
		}
	}

	/**
     * 返回一个用于查询序列值得与特定数据库相关的查询SQL。
     * 提供的SQL的查询结果应该只有一行，并且返回值能够被解包为long类型。
	 * Return the database-specific query to use for retrieving a sequence value.
	 * <p>The provided SQL is supposed to result in a single row with a single
	 * column that allows for extracting a {@code long} value.
	 */
	protected abstract String getSequenceQuery();

}

4.4 DB2SequenceMaxValueIncrementer-DB2序列值的实现类

DB2SequenceMaxValueIncrementer这个类实现了AbstractSequenceMaxValueIncrementer中的getSequenceQuery()方法，从而获取到特定序列序列值。

package org.springframework.jdbc.support.incrementer;
import javax.sql.DataSource;

/**
 * {@link DataFieldMaxValueIncrementer} that retrieves the next value of a given sequence
 * on DB2 UDB (for Unix and Windows). Thanks to Mark MacMahon for the suggestion!
 *
 */
public class DB2SequenceMaxValueIncrementer extends AbstractSequenceMaxValueIncrementer {
	// 构造方法
	public DB2SequenceMaxValueIncrementer() {
	}
	public DB2SequenceMaxValueIncrementer(DataSource dataSource, String incrementerName) {
		super(dataSource, incrementerName);
	}

        // DB2查询序列的下一个值的SQL
	@Override
	protected String getSequenceQuery() {
		return "values nextval for " + getIncrementerName();
	}
}

所以这里的重点是一条查询序列的SQL：values nextval for 序列名称。

像Oracle的序列，也只需要提供如下方法就可实现：

@Override
protected String getSequenceQuery() {
    return "select " + getIncrementerName() + ".nextval from dual";
}

参考

1. [Spring中sequence的使用小结](https://blog.csdn.net/wang0928007/article/details/7175905)

相关推荐