模拟数据库中分批次读取序列号

模拟数据库中分批次读取序列号

package main.java.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;

import com.mysql.jdbc.StringUtils;

import main.java.entity.Book;
import main.java.utils.JdbcUtil;

public class SequenceDaoImpl implements SequenceDao{
	public static HashMap
   
   
    
     SEQUENCE = new HashMap
    
    
     
     ();
	@Override
	public void insert(Book t) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public Long getSeqId(String sequenceName) throws Exception {
		if(SEQUENCE.isEmpty()){
			if(StringUtils.isNullOrEmpty(sequenceName)){
				throw new RuntimeException("未知序列");
			}
			Connection conn = JdbcUtil.getConnection();
			String sql = "SELECT SEQUENCE_NAME_,SEQ_ID_,NEXT_ID_,INCREASE_STEP_,CONTAIN_ FROM T_SEQUENCE WHERE SEQUENCE_NAME_ = ? LIMIT 1";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, sequenceName);
			ResultSet rs = ps.executeQuery();
			Long sqlId = 0L;
			while(rs.next()){//第一次调用光标移到第一行
				sqlId = rs.getLong("SEQ_ID_");
				SEQUENCE.put("sequenceName", rs.getString("SEQUENCE_NAME_"));
				SEQUENCE.put("seqId", rs.getLong("SEQ_ID_"));
				SEQUENCE.put("nextId", rs.getLong("NEXT_ID_"));
				SEQUENCE.put("increaseStep", rs.getLong("INCREASE_STEP_"));
				SEQUENCE.put("contain", rs.getLong("CONTAIN_"));
			}
			if(sqlId == 0L){
				throw new RuntimeException("未知序列");
			}
			ps.close();
		}else{
			//该次获取后理论上当前的序列值
			Long preSeqId = (Long)SEQUENCE.get("seqId")+(Long)SEQUENCE.get("increaseStep");
			if(preSeqId >= (Long)SEQUENCE.get("nextId")){
				//如果当前累计序列值大于数据库中下一个序列值，则执行序列表中值更新动作
				Connection conn = JdbcUtil.getConnection();
				String updateSql = "UPDATE T_SEQUENCE SET SEQ_ID_ = ? ,NEXT_ID_ = ? WHERE SEQUENCE_NAME_ = ?";
				PreparedStatement ps = conn.prepareStatement(updateSql);
				ps.setLong(1, (Long)SEQUENCE.get("nextId"));
				ps.setLong(2, (Long)SEQUENCE.get("nextId")+(Long)SEQUENCE.get("contain"));
				ps.setString(3, (String)SEQUENCE.get("sequenceName"));
				ps.executeUpdate();
				ps.close();
				//更新sequence的map集合
				SEQUENCE.put("seqId", (Long)SEQUENCE.get("nextId"));
				SEQUENCE.put("nextId", (Long)SEQUENCE.get("nextId")+(Long)SEQUENCE.get("contain"));
			}else{
				//序列map中值增加步长
				SEQUENCE.put("seqId", (Long)SEQUENCE.get("seqId")+(Long)SEQUENCE.get("increaseStep"));
			}
		}
		return Long.valueOf(SEQUENCE.get("seqId")+"");
	}
	
	public static void main(String[] args) throws Exception{
		SequenceDaoImpl sdi = new SequenceDaoImpl();
		for(int i= 0;i<110;i++){
			Long id = sdi.getSeqId("sequence");
			System.out.println(id);
		}
	}
}
/**删除创建表序列**/
/**DROP TABLE IF EXISTS T_SEQUENCE;**/

/**创建序列表**/
CREATE TABLE T_SEQUENCE(
	ID_ INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '序列号',
	SEQUENCE_NAME_ VARCHAR(30) NOT NULL COMMENT '序列名称',
	SEQ_ID_ INT(18) COMMENT '当前seqId值',
	NEXT_ID_ INT(18) COMMENT '*下次插入seqId值',
	INCREASE_STEP_ INT(4) COMMENT '每次增加的步长',
	CONTAIN_ INT(6) COMMENT '每次包含多少数',
	DESCRIBE_ VARCHAR(128) COMMENT '对序列名称及用途描述'
);

/**维护一条序列表数据**/
DELETE FROM T_SEQUENCE WHERE ID_ = 1 AND SEQUENCE_NAME_ = 'sequence';
INSERT INTO T_SEQUENCE (ID_,SEQUENCE_NAME_,SEQ_ID_,NEXT_ID_,INCREASE_STEP_,CONTAIN_,DESCRIBE_) 
	VALUES (1,'sequence',1,101,1,100,'所有实体公用的主键');

package main.java.utils;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class JdbcUtil {
	/**读取config.properties中值
	 * pathName:文件绝对路径或资源路径 如：D://test.properties 
	 * 							  resource/test.properties**/
	public static Properties getProperties(String pathName){
		//此种方式不可取，此种方式是获取经过编译后的文件，而不是原目录文件。当原目录文件发生变化时，该文件不会随之变化
		//JdbcUtil jdbcUtil = new JdbcUtil();
		//InputStream in = jdbcUtil.getClass().getResourceAsStream("/config.properties");
		
		//保险做法还是通过文件流获取
		File file = new File(pathName);
		InputStream in = null;
		Properties p = new Properties();
		try {
			in = new FileInputStream(file);
			InputStream inStream = new BufferedInputStream(in);
			p.load(inStream);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return p;
	}
	
	/**获取数据库连接**/
	public static Connection getConnection() throws Exception{
		//加载配置文件
		Properties properties = getProperties("resource/config.properties");
		String driverName = properties.getProperty("jdbc.mysql");//com.mysql.jdbc.Driver
		String url = properties.getProperty("jdbc.url");//jdbc:mysql://localhost:3306/seftest
		String username= properties.getProperty("jdbc.username");
		String password = properties.getProperty("jdbc.password");
		Connection conn = null;
		try {
			//1.注册驱动
			Class.forName(driverName);
			//2.获取链接
			conn = DriverManager.getConnection(url,username,password);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return conn;
	}
	
	public static void main(String[] args){
		Properties p = getProperties("resource/config.properties");
		System.out.println(p.get("jdbc.url"));
		JdbcUtil jdbcUtil = new JdbcUtil();
		System.out.println(jdbcUtil.getClass().getResource("/"));
	}

}