com.huawei.dataconvert.db.convert

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("序列转换完成");
}