将程序由mysql迁移到Oracle实战(by quqi99)

弘望舒

将程序由mysql迁移到Oracle实战（by quqi99）

作者：张华 发表于：2007-10-10 ( http://blog.csdn.net/quqi99 )

版权声明：可以任意转载，转载时请务必以超链接形式标明文章原始出处和作者信息及本版权声明。


1 数据迁移
首先是数据库的迁移，数据库迁移比较简单，直接用开源ETL工具Kettle搞定，非常顺利。
2 修改程序
接着要改程序，由于我不想用Hibernate,由于 mysql与Oracle的差异，改程序是必不可少的。
2.1 分页
在Mysql中分页比较简单，可以直接在SQL语句中用limit,如下：
limit "+(pg-1)*pgsz+","+pgsz; //pg为第几页，pgsz为每页大小
ORACLE处理翻页的SQL语句就比较繁琐了。每个结果集只有一个ROWNUM字段标明它的位置, 并且只能用ROWNUM<100, 不能用ROWNUM>80。
由于我不想太多改已有的程序结构，用ROWNUM关键字会用到很多子查询，最后，我选择了直接用JDBC中ResultSet中的游标(absolute方法)去定位：
//pstmt = conn.prepareStatement(querySql);
  pstmt = conn.prepareStatement(querySql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
  rs = pstmt.executeQuery();
if(pg>1)
  rs.absolute((pg-1)*pgsz + 1); //分页，将记录指针指向相应位置
  int num = 0;
  while(rs.next()){
   num++;
if(num > pgsz)
break;
。。。。。。。。。其他
}
2.2 日期处理
MYSQL日期字段分DATE和TIME两种，ORACLE日期字段只有DATE，包含年月日时分秒信息，用当前数据库的系统时间为SYSDATE, 精确到秒，或者用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)年-月-日 24小时:分钟:秒的格式YYYY-MM-DD HH24:MI:SS TO_DATE()还有很多种日期格式, 可以参看ORACLE DOC.日期型字段转换成字符串函数TO_CHAR(‘2001-08-01’,’YYYY-MM-DD HH24:MI:SS’)
我使用了TO_CHAR函数，改动如下：
//querySql = "SELECT y.id,y.bumen,b.bumen,xingming,date_format(riqi,'%Y-%m-%d'),yijian,zhuangtai,huifu,manyi FROM yijian y,bumen b where y.bumen=b.id and "+qStr+" order by y.id desc " + lStr;
querySql = "SELECT y.id,y.bumen,b.bumen,xingming,TO_CHAR(riqi,'YYYY-MM-DD'),yijian,zhuangtai,huifu,manyi FROM yijian y,bumen b where y.bumen=b.id and "+qStr+" order by y.id desc ";

另外，在mysql数据库中在SQL语句中用sysdate()函数取系统当前日期,在oracle数据库中在SQL语句中用SYSDATE函数取系统当前日期
2.3 CLOB大字段处理
Kettle工具能自动将mysql的TEXT字段转成ORACLE中的CLOB字段，所以在操作大字段的地方需要改动。
读取大字段的改动如下：
//读取clob字段
//CLOB yijianClob = ((OracleResultSet)rs).getCLOB(6);
//报java.lang.ClassCastException: org.apache.tomcat.dbcp.dbcp.DelegatingResultSet错误
//应该将数据源配置中加上属性：accessToUnderlyingConnectionAllowed="true"，
//如果是在Postgresql中，类似就应该是：PGConnection pgCon =(PGConnection)((DelegatingConnection)con).getInnermostDelegate();
CLOB yijianClob = (oracle.sql.CLOB)((org.apache.tomcat.dbcp.dbcp.DelegatingResultSet)rs).getClob(6);
String yijian="";
if(yijianClob!=null){
Reader is=yijianClob.getCharacterStream();
BufferedReader br=new BufferedReader(is);
String s=br.readLine();
while(s!=null){
  yijian+=s+"/n";
  s=br.readLine();
}
}
注意：应该将数据源配置中加上属性：accessToUnderlyingConnectionAllowed="true"，并且使用org.apache.tomcat.dbcp.dbcp.DelegatingResultSet，不然会报错。
插入大字段如下：
//插入留言
conn = dataSource.getConnection();
//querySql = "insert into yijian (riqi,xingming,bumen,gongkai,lianxi,leixing,yijian,huifu,zhuangtai,manyi,pingjia,jiaoyan,district,tel)"
// + " values(sysdate(),'"+xingming+"',"+bmid+","+gongkai+",'"+lianxi+"',"+leixing+",'"+yijian+"','',0,0,'',"+j+",'"+district+"','"+tel+"')";
querySql = "insert into yijian (id,riqi,xingming,bumen,gongkai,lianxi,leixing,yijian,huifu,zhuangtai,manyi,pingjia,jiaoyan,district,tel)"
  + " values(?,SYSDATE,'"+xingming+"',"+bmid+","+gongkai+",'"+lianxi+"',"+leixing+",empty_clob(),empty_clob(),0,0,'',"+j+",'"+district+"','"+tel+"')";
try {
System.out.println("将要执行： "+querySql);
int id=getNextPublicId("yijian");
   pstmt = conn.prepareStatement(querySql);
   pstmt.setInt(1,id); //设置主键
  i = pstmt.executeUpdate();
  //插入大字段
  ResultSet rsClob = null;
  //用这种容易发生错误ORA-01006: 赋值变量不存在，改成下句带？的形式
  //ResultSet rsClob = pstmt.executeQuery("select yijian from yijian where id="+id+" for update");
  PreparedStatement pstmttemp = conn.prepareStatement("select yijian from yijian where id=? for update");
  pstmttemp.setInt(1,id);
  rsClob = pstmttemp.executeQuery();  
  if(rsClob.next()){
   //避免发生错误java.lang.ClassCastException: org.apache.tomcat.dbcp.dbcp.DelegatingResultSet，改用下句
   //CLOB yijianClob = ((OracleResultSet)rsClob).getCLOB(1);
   CLOB yijianClob = (oracle.sql.CLOB)((org.apache.tomcat.dbcp.dbcp.DelegatingResultSet)rsClob).getClob(1);
   //CLOB huifuClob = (oracle.sql.CLOB)((org.apache.tomcat.dbcp.dbcp.DelegatingResultSet)rsClob).getClob(2);
   yijianClob.putString(1,yijian);
   huifuClob.putString(1,"");
   PreparedStatement pstmtClob=conn.prepareStatement("update yijian set yijian=? where id='"+id+"' ");
   pstmtClob.setClob(1,yijianClob);
   //pstmtClob.setClob(2,huifuClob);
   pstmtClob.executeUpdate();
   pstmtClob.close();
  }
  rsClob.close();
  rsClob = null;
  pstmttemp.close();
   }
  catch (SQLException ex) {
   System.err.println("插入留言失败! "+ex.getMessage());
   throw ex;
  } finally {
try {
if (rs != null) {
rs.close();
rs = null;
}
} catch (SQLException ex1) {
}
  try {
if (pstmt != null) {
pstmt.close();
pstmt = null;
}
} catch (SQLException ex2) {
}
try {
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException ex3) {
}
  }
2.4自动增长的数据类型处理
MYSQL有自动增长的数据类型，插入记录时不用操作此字段，会自动获得数据值。ORACLE没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。
CREATE SEQUENCE 序列号的名称 (最好是表名+序列号标记) INCREMENT BY 1 START WITH 1000 MAXVALUE 99999 CYCLE NOCACHE;
其中最大的值按字段的长度来定, 如果定义的自动增长的序列号 NUMBER(6) , 最大值为999999
INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL
我的改动如下，首先新建一个名为lyb_seq的序列，相应SQL语句如下：
CREATE SEQUENCE "LYB"."LYB_SEQ" INCREMENT BY 1 START WITH 1
MAXVALUE 1.0E28 MINVALUE 1 CYCLE
NOCACHE NOORDER
然后SQL语句改动如下：
// pstmt = conn.prepareStatement("insert into bumen (bumen) values ('其他')");
   pstmt = conn.prepareStatement("insert into bumen (id,bumen) values ('LYB_SEQ.NEXTVAL','其他')");

另外，在SQL语句中取序列方法为：select lyb_seq.nextval from DUAL;
2.5 表名
在Oracle中的表名不能叫user,否则会报错误：ORA-00903: 表名无效
2.6 空字符的处理
MYSQL的非空字段也有空的内容，ORACLE里定义了非空字段就不容许有空的内容。按MYSQL的NOT NULL来定义ORACLE表结构, 导数据的时候会产生错误。因此导数据时要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串。
我的改动是，加上：
if("".equals(department))
department=" ";