模拟数据库中分批次读取序列号
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("/"));
}
}