Convert Sequence And index(Oracle to Hsql)

甄如云

public void convertSeque(Connection oraConn, Connection hsqlConn)
throws Exception {
List<Sequence> sequences = new ArrayList<Sequence>();
Statement oraStmt = oraConn.createStatement();
Statement hsqlStmt = hsqlConn.createStatement();
String sql = "SELECT * FROM USER_SEQUENCES ";

System.out.println("开始查找序列："+sql);
ResultSet rs = oraStmt.executeQuery(sql);
List<String> seqNames = new ArrayList<String>();
while(rs.next()){
Sequence seq = new Sequence();
String seq_name = rs.getString("SEQUENCE_NAME");
seq.setSeq_name(seq_name);
seqNames.add(seq_name);
seq.setMin_value(rs.getBigDecimal("MIN_VALUE"));
BigDecimal max = rs.getBigDecimal("MAX_VALUE");
seq.setMax_value(max);
seq.setIncrement_by(rs.getBigDecimal("INCREMENT_BY"));
seq.setCurrval(rs.getBigDecimal("LAST_NUMBER"));
String cycflag = rs.getString("CYCLE_FLAG");
boolean cyc_flag=true;
if(cycflag.equalsIgnoreCase("N"))cyc_flag=false;
seq.setCycle_flag(cyc_flag);
String orderS_flag = rs.getString("ORDER_FLAG");
boolean ord_flag = false;
if(orderS_flag.equalsIgnoreCase("N"));
seq.setOrder_flag(ord_flag);
sequences.add(seq);
}

System.out.println("序列个数："+sequences.size());
//查找序列当前值
System.out.println("查找序列当前值");
for(Sequence seq:sequences){
String sqll = "select "+seq.getSeq_name()+".NEXTVAL FROM DUAL";
rs = oraStmt.executeQuery(sqll);
rs.next();
seq.setCurrval(rs.getBigDecimal(1));
}

//创建序列
System.out.println("开始创建序列");
BigDecimal hsqlmax_seq = new BigDecimal("999999999999999999");//18个9
for(Sequence seq:sequences){
/**
create sequence FFN_LOG_ID
AS DECIMAL
minvalue 1
maxvalue 9999999999999999
start with 2000
increment by 1
**/
StringBuffer createsql = new StringBuffer("create sequence ");
createsql.append(seq.getSeq_name()).append(" \n");
createsql.append("AS DECIMAL ");
createsql.append("minvalue ").append(seq.getMin_value()).append(" \n");
BigDecimal maxValue = seq.getMax_value();
createsql.append("maxvalue ");
if(1==(maxValue.compareTo(hsqlmax_seq))){//当最大值大于18个9时
createsql.append(hsqlmax_seq).append(" \n");
}else{
createsql.append(seq.getMax_value()).append(" \n");
}
createsql.append("start with ").append(seq.getCurrval()).append(" \n");
createsql.append("increment by ").append(seq.getIncrement_by());
try{
hsqlStmt.executeUpdate(createsql.toString());
}catch(Exception e){
e.printStackTrace();
System.out.println("创建序列失败："+createsql.toString());
}

int cycle_flag = 0;
if(seq.isCycle_flag()){
cycle_flag=1;
}
int order_flag = 0;
if(seq.isOrder_flag()){
order_flag=1;
}
String insertSeqInfo = "INSERT INTO UBHAVE_SEQINFO" +
"( SEQ_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG, ORDER_FLAG, CURRVAL)" +
"VALUES " +
"( '"+seq.getSeq_name()+"', '"+seq.getMin_value()+"', '"+seq.getMax_value()+
"', '"+seq.getIncrement_by()+"',"+cycle_flag+" ,"+order_flag+" , '"+seq.getCurrval()+"')";
hsqlStmt.executeUpdate(insertSeqInfo);
}
oraStmt.close();
rs.close();
hsqlStmt.close();
System.out.println("序列转换完成");
}