现有sqlserver和oracle数据库 从sqlserver里读取一个为image类型的字段 怎么转换成oracle里的BLOB类型插入到oracle里呢
解决方案 »
- Struts2 DoubleSelect标签 使用Ajax返回后报错:tag 'doubleselect', field '...
- JBPM oracle 自增主键问题
- 关于hibernate的一级缓存的一个问题
- 前辈帮帮忙吧,MyEclipse问题,第一次接触这个IDE。
- tomcat和数据库连接池
- 求j2ee的视频教程
- hibernate高手进,hibernate中调用sql的疑问(100分),6点前不定时看贴
- 集成windows认证的webservice(Reporting Service)使用Axis客户端怎么访问?
- 使用struts中的formbean初始化自定义taglib的问题!急!!!在线等待
- JB7+WebLogic 8.1 在build时出现两个warning,请问该怎么解决?
- 把struts.xml存入src出现错误,图标不是xml图标 不能运行
- 怎么保存几个线程内的socket。。。求各位大侠。。。。
JDBC读取写入SQLServer的Blob(image)字段数据的代码:
import java.sql.*;
import java.io.*; public class Test {
// 注意下面是jdbc2.0的驱动写法,3.0的请自行查找
static String driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
static String url = "jdbc:microsoft:sqlserver://192.168.0.202:9999999999;DatabaseName=dddd";
static String user = "sa";
static String passwd = "ps"; public static void method1() throws Exception { Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, passwd);
int op = 0;
// 插入
if (op == 0) {
PreparedStatement ps = conn
.prepareStatement("insert into tb_file values (?,?)");
ps.setString(1, "aaa.exe");
InputStream in = new FileInputStream("d:/aaa.exe");
ps.setBinaryStream(2, in, in.available());
ps.executeUpdate();
ps.close();
} else {
// 取出
PreparedStatement ps = conn
.prepareStatement("select * from tb_file where filename = ?");
ps.setString(1, "aaa.exe");
ResultSet rs = ps.executeQuery();
rs.next();
InputStream in = rs.getBinaryStream("filecontent");
System.out.println(in.available());
FileOutputStream out = new FileOutputStream("d:/bbb.exe");
byte[] b = new byte[1024];
int len = 0;
while ((len = in.read(b)) != -1) {
out.write(b, 0, len);
out.flush();
}
out.close();
in.close();
rs.close();
ps.close();
}
} catch (Exception ex) {
ex.printStackTrace(System.out);
} finally {
try {
conn.close();
} catch (Exception ex) {
}
}
} public static void method2() {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost:3306/exercise";
// String url=
// "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=GBK";
String user = "root";
String password = "roger";
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
System.out.println("ClassNotFoundException ->" + e);
}
try {
Connection conn = DriverManager.getConnection(url, user, password);
PreparedStatement pstmt = conn
.prepareStatement("select * from picture where picturename=?");
pstmt.setString(1, "z0011.jpg");
ResultSet res = pstmt.executeQuery();
res.beforeFirst();
while (res.next()) {
Blob blob = res.getBlob(2);
try {
FileOutputStream fout = new FileOutputStream("z0018.jpg");
fout.write(blob.getBytes(1, (int) blob.length()));
System.out.println("z0011.jpg file size ->"
+ (int) blob.length());
fout.flush();
fout.close();
} catch (IOException e) {
System.out.println("create jpg file exception ->" + e);
}
}
conn.close();
} catch (SQLException e) {
System.out.println("SQLException ->" + e);
}
}
} 通过java往oracle中blob字段写入数据:
public class applyPhotoBLOB {
final static String sDBDriver = "oracle.jdbc.driver.OracleDriver";
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection connSDC = null;
Connection conn = null;
String sConnStr = "jdbc:oracle:thin:@127.0.0.1:1521:sledu";
String sConnStrSDC = "jdbc:oracle:thin:@10.10.8.12:1521:rac2";
String sDBUid = "test";
String sDBPwd = "test";
String sDBUidSDC = "sdcmanager";
String sDBPwdSdc = "sdcmanager_888";
try
{
applyPhotoBLOB apply = new applyPhotoBLOB();
connSDC = apply.getConn(sConnStrSDC,sDBUidSDC,sDBPwdSdc); if(connSDC!=null)
{
apply.testBOLB(connSDC);
}
System.out.println("处理完成!");
}
catch(Exception e)
{
System.out.println(e.getMessage());
}
finally
{
try
{
if(conn!=null) conn.close();
if(connSDC!=null) connSDC.close();
}
catch(Exception e)
{
System.out.println(e.getMessage());
}
}
} public void testBOLB(Connection conn) throws Exception
{
String strSQL = "Insert Into BKS_XSZPXX(XH,ZPLXM,ZP) Values('3071801040','1',empty_blob())";
updateTable1(strSQL,conn);
conn.setAutoCommit(false);
strSQL = "Select ZP from BKS_XSZPXX where XH='3071801040' For Update";
Statement stmt = null;
ResultSet rs = null;
stmt = conn.createStatement();
rs = stmt.executeQuery(strSQL); rs.next();
BLOB blob = (BLOB) rs.getBlob("ZP");
OutputStream os = blob.getBinaryOutputStream();// 建立输出流
BufferedOutputStream output = new BufferedOutputStream(os);
BufferedInputStream input = new BufferedInputStream(new File("F:/3071801040.jpg").toURL().openStream());
byte[] buff = new byte[2048000]; //用做文件写入的缓冲
int bytesRead;
while(-1 != (bytesRead = input.read(buff, 0, buff.length)))
{
output.write(buff, 0, bytesRead);
//System.out.println(bytesRead);
} output.close();
input.close();
rs.close();
conn.commit();
conn.setAutoCommit(true);
stmt.close();
} private int updateTable1(String strSQL,Connection conn) throws Exception
{
PreparedStatement stmt = null;
int result = 0;
try
{
stmt = conn.prepareStatement(strSQL);
result = stmt.executeUpdate();
}
catch(Exception e)
{
throw new Exception(e.getMessage());
}
finally
{
stmt.close();
}
return result ;
} public Connection getConn(String StrConn,String uid,String pwd) throws Exception
{
Connection conn = null;
try
{
Class.forName(sDBDriver);
conn = DriverManager.getConnection(StrConn,uid,pwd);
}
catch (Exception e)
{
throw new Exception(e.getMessage());
}
return conn;
}}
//SQLServer端读数据
Statement sqlserverstmt = sqlserverConn.createStatement();
ResultSet sqlserverRs = sqlserverstmt.executeQuery("select * from your_table");
while (sqlserverRs.next()) {
DataInputStream dis = new DateInputStream(sqlserverRs.getBinaryStream("your_field"));
byte[] b = new byte[1024];
int len = 0;
long size = 0;
ByteArrayOutputStream bos = new ByteArrayOutputStream();
while ((len=dis.read(b)) != -1) {
size += len;
bos.wirte(b, 0, len);
} //Oracle端写数据
Statement oraclestmt = oracleConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
RsultSet oracleRs = oraclestmt.executeQuery("select * from your_table where id = " + sqlserverRs.getInt("id"));
while (orcaleRs.next()) {
BLOB blob = oracleRs.getBlob("your_field");
DateOutputStream dos = new DataOutputStream(blob.setBinaryStream(size));
byte[] bb = bos.toByteArray();
dos.write(bb, 0, bb.length);
dos.close();
//或者直接用oracleRs.updateBlob("your_field", new ByteArrayInputStream(bos.toByteArray()), size);
}
}
然后再select出来,通过上面的例子代码修改blob字段的信息
PreparedStatement oraclestmt = oracleConn.prepareStatement("insert your_table (id, your_blob, others) values (?, ?, ?)");
oraclestmt.setInt(1, sqlserverRs.getInt("id"));
oraclestmt.setBlob(2, new ByteArrayInputStream(bos.toByteArray()), size);
oraclestmt.setObject(3, sqlserverRs.get("others"));
oraclestmt.executeUpdate();