java程序存取 mysql 数据库的 clob 和blob 字段

package com.ibm.db;

import java.io.*;
import java.sql.*;

public class TestMysql {

/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub

// text
//insertBlob("C:\\D\\sql_oracle_temp.xml");
//queryBlob("C:\\D\\sql_oracle_temp.xml.txt");
//image
//insertBlob("C:\\D\\007.mp3");
//queryBlob("C:\\D\\008.mp3");
/**
* 在处理blob字段时候，由于直接处理的是二进制流，所以没啥问题。
* 在处理clob字段的时候，由于数据库对clob是以字符的形式进行存储，
* 这就有一个编码问题。本文虽然成功的插入读取了clob字段，但是还没有解决乱码问题，
* 因为JDBC在获取到clob的时候，已经对其进行了编码，Reader reader = rs.getCharacterStream(1);
* 这就导致了编码的混乱，如果要彻底解决，还需要看看MySQL驱动的实现。通过非常规手段来解决。为了绕开此问题，
* 可以将clob的数据存储为blog来操作，可以避免此问题。
*/
try {
insertClob("C:\\D\\sql_oracle_temp.xml");
queryClob("C:\\D\\sql_oracle_temp.xml.txt");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}


}

public static void insertBlob(String fileName) {
Connection conn = getConnection();
PreparedStatement ps = null;
try {
String sql = "insert into sampledb.user (name, pswd, pic) values (?, ?, ?)";
ps = conn.prepareStatement(sql);
ps.setString(1, "zhangsan");
ps.setString(2, "111");
// 设置二进制参数
File file = new File(fileName);
InputStream in = new BufferedInputStream(new FileInputStream(file));
ps.setBinaryStream(3, in, (int) file.length());
ps.executeUpdate();
in.close();
System.out.println("finished");
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}


public static void insertClob(String fileName) throws SQLException {
Connection conn = getConnection();
PreparedStatement ps = null;
try {
String sql = "insert into sampledb.user (name, pswd, remark) values (?, ?, ?)";
ps = conn.prepareStatement(sql);
ps.setString(1, "zhangsan");
ps.setString(2, "111");
//设置二进制参数
File file = new File(fileName);
InputStreamReader reader = new InputStreamReader(new FileInputStream(file));
ps.setCharacterStream(3, reader, (int) file.length());
ps.executeUpdate();
reader.close();
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
conn.close();
}
}


public static void queryBlob(String outFileName) {
Connection conn = getConnection();
PreparedStatement ps = null;
Statement stmt = null;
ResultSet rs = null;
try {
String sql = "select pic from user where id = 6";
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if (rs.next()) {
InputStream in = rs.getBinaryStream(1);
File file = new File(outFileName);
OutputStream out = new BufferedOutputStream(new FileOutputStream(file));
byte[] buff = new byte[1024];
for (int i = 0; (i = in.read(buff)) > 0;) {
out.write(buff, 0, i);
}
out.flush();
out.close();
in.close();
}
rs.close();
stmt.close();
System.out.println("finished query blob");
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void queryClob(String outFileName) throws SQLException {
Connection conn = getConnection();
PreparedStatement ps = null;
Statement stmt = null;
ResultSet rs = null;
try {
String sql = "select remark from sampledb.user where id =16";
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if (rs.next()) {
Reader reader = rs.getCharacterStream(1);
File file = new File(outFileName);
//OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(file));
//OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(file),"ISO-8859-1");
OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(file),"GB2312");
char[] buff = new char[1024];
for (int i = 0; (i = reader.read(buff)) > 0;) {
writer.write(buff, 0, i);
}
writer.flush();
writer.close();
reader.close();
}
rs.close();
stmt.close();
System.out.println("end");
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
conn.close();
}
}

private static Connection getConnection() {
String jdbc_url = "jdbc:mysql://localhost/sampledb?useUnicode=true&characterEncoding=UTF-8";

String jdbc_user = "root";
String jdbc_password = "root";
String jdbc_driver = "com.mysql.jdbc.Driver";
Connection conn = null;
try {
Class.forName(jdbc_driver);
conn = DriverManager.getConnection(jdbc_url, jdbc_user,
jdbc_password);
} catch (ClassNotFoundException cne) {
cne.printStackTrace();
} catch (SQLException se) {
se.printStackTrace();
} finally {

}
return conn;
}

}