jdbc方式读取oracle的clob字段实例
可能大家也都习惯了spring和hibernate对CLOB字段的处理，在spring中配置clob的oracle处理句柄，在hibernate中配置映射类型，然后就可以很轻松的以String 的形式处理大字段。今天我做了个需求，需要以jdbc的方式从mysql导一些备份数据到oracle正式库，就查了一些资料，最后写了个例子： 1：首先：写个连接数据库的类，里面有返回mysq， oracle连接的方法
public Connection getConn(String flag){
Connection con=null;
try
{
if(flag.equals("1"))
{
Class.forName(“oracle.jdbc.driver.OracleDriver”);
con = DriverManager.getConnection(“jdbc:oracle:thin:@IP:1521:数据库名字”,"name","password");
}
if(flag.equals("2"))
{
Class.forName("org.gjt.mm.mysql.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/数据库名?user=用户名&password=密码&useUnicode=true&characterEncoding=GBK");
}
}
catch(Exception e)
{
e.printStackTrace();
}
return con;
}
public Connection getConn(String flag){
Connection con=null;
try
{
if(flag.equals("1"))
{
Class.forName(“oracle.jdbc.driver.OracleDriver”);
con = DriverManager.getConnection(“jdbc:oracle:thin:@IP:1521:数据库名字”,"name","password");
}
if(flag.equals("2"))
{
Class.forName("org.gjt.mm.mysql.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/数据库名?user=用户名&password=密码&useUnicode=true&characterEncoding=GBK");
}
}
catch(Exception e)
{
e.printStackTrace();
}
return con;
}
view plaincopy to clipboardprint?
<PRE class=csharp name="code">public void setData() {
conn = new Conn();
try {
String sqlfrom = "selectp.id,p.content from <A title=table href="http://www.alimama.com/membersvc/buyadzone/buy_ad_zone.htm?adzoneid=892989 " target=_blank>table</A> porder by p.id ";
String sqlinsert = "insert into <A title=table href="http://www.alimama.com/membersvc/buyadzone/buy_ad_zone.htm?adzoneid=892989 " target=_blank>table</A> values(?,?)";
con = conn.getConn("2");
stmt = con.createStatement(); //从mysql取出大字段
rs = stmt.executeQuery(sqlfrom);
con = conn.getConn("1");
PreparedStatement pstmt = con.prepareStatement(sqlinsert); //向oracle中插入大字段
int i = 0;
while (rs.next()) {
pstmt.setInt(1, rs.getInt(1));
pstmt.setClob(2, <A title=oracle href="http://www.google.cn/search?sbi=股票&amp;q=股票&amp;sbb=搜索&amp;sa=搜索&amp;client=pub-6430022987645146&amp;forid=1&amp;prog=aff&amp;ie=GB2312&amp;oe=GB2312&amp;hl=zh-CN" target=_blank>oracle</A>.sql.CLOB.empty_lob());
pstmt.executeUpdate();//插入时将大字段设为空
this.updateOne(con,rs.getInt(1),rs.getString(2));// 这里调用然后更新这个大字段
}
rs.close();//关闭相关连接
pstmt.close();
stmt.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
try
{
con.rollback();
} catch (Exception e1) {
<A title=system href="http://www.alimama.com/membersvc/buyadzone/buy_ad_zone.htm?adzoneid=892989 " target=_blank>system</A>.out.println("回滚出现异常！");
e1.printStackTrace();
}
}
}
3:该方法实现对应大字段记录的更新
<PRE class=java name="code">public void updateOne(Connection con,int id, String content) {
String str = "select t.content from <A title=table href="http://www.alimama.com/membersvc/buyadzone/buy_ad_zone.htm?adzoneid=892989 " target=_blank>table</A>t where t.id=" id"for update";
try {
// 注意：存取操作开始前，必须用setAutoCommit(false)取消自动提交，否则Oracle将抛出“读取违反顺序”的错误。
con.setAutoCommit(false);
stmt = con.createStatement();
ResultSet rs_clob = stmt.executeQuery(str);
while ( rs_clob .next()) {
/* 取出clob数据*/
<A title=oracle href="http://www.google.cn/search?sbi=股票&amp;q=股票&amp;sbb=搜索&amp;sa=搜索&amp;client=pub-6430022987645146&amp;forid=1&amp;prog=aff&amp;ie=GB2312&amp;oe=GB2312&amp;hl=zh-CN" target=_blank>oracle</A>.sql.CLOB clob = (oracle.sql.CLOB)rs_clob .getClob(1);
/* 向clob中写入数据*/
clob.putString(1, content);
}
stmt.close();
con.commit();
con.setAutoCommit(true);
con.close();
} catch (Exception e) {
e.printStackTrace();
try
{
con.rollback();
} catch (Exception e1) {
<A title=system href="http://www.alimama.com/membersvc/buyadzone/buy_ad_zone.htm?adzoneid=892989 " target=_blank>system</A>.out.println("回滚出现异常！");
e1.printStackTrace();
}
}
}
</PRE>
现在就完成了一行记录的更新。
上一页
4：读clob字段以String 的形式返回（当然也可以将读到的内容写入文件，大家改一下就可以了）
<PRE class=java name="code">/**
* 读clob字段
* @param con
* @param id
* @return
*/
public StringreadClob(Connection con,int id)
{
String content="";
try
{
con.setAutoCommit(false);
stmt=con.createStatement();
ResultSet rs_clob=stmt.executeQuery("selectt.contentfrom<A title=table href="http://www.alimama.com/membersvc/buyadzone/buy_ad_zone.htm?adzoneid=892989 " target=_blank>table</A> t where t.id=" id);
<A title=oracle href="http://www.google.cn/search?sbi=股票&amp;q=股票&amp;sbb=搜索&amp;sa=搜索&amp;client=pub-6430022987645146&amp;forid=1&amp;prog=aff&amp;ie=GB2312&amp;oe=GB2312&amp;hl=zh-CN" target=_blank>oracle</A>.sql.CLOB contents=null;
while (rs_clob.next())
{ // 取出CLOB对象
contents= (oracle.sql.CLOB) rs_clob.getClob("1");
}
BufferedReader a = new BufferedReader(contents.getCharacterStream());//以字符流的方式读入BufferedReader
String str = "";
while ((str = a.readLine()) != null) {
content = content.concat(str);//最后以String的形式得到
}
con.commit();
/*
BufferedWriter out = new BufferedWriter(new FileWriter("e:/test.txt"));
out.write(content);//写入文件
out.close();*/
con.setAutoCommit(true);
con.close();
}catch(Exception e)
{
<A title=system href="http://www.alimama.com/membersvc/buyadzone/buy_ad_zone.htm?adzoneid=892989 " target=_blank>system</A>.out.println("出现异常");
e.printStackTrace();
try
{
con.rollback();
}
catch (Exception e1)
{
<A title=system href="http://www.alimama.com/membersvc/buyadzone/buy_ad_zone.htm?adzoneid=892989 " target=_blank>system</A>.out.println("回滚出现异常！");
e1.printStackTrace();
}
}
return content;
}
</PRE>
</PRE>
上一页
页:
[1]