mysql存clob对象_JDBC处理CLOB 和 BLOB大对象

在数据库中：

clob用于存储大量的文本数据

可以使用字符流操作

clob用于存储大量的二进制数据

可以使用字节流操作

以mysql为例 先准备一张表：

CREATE TABLE `t_user2` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`username` varchar(10) DEFAULT NULL,

`myInfo` text,

`headImage` blob,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

ClOB 操作：

public static void main(String[] args) {

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

BufferedReader br = null;

try {

// 加载驱动类

Class.forName("com.mysql.jdbc.Driver");

// 建立连接

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc?useSSL=false", "root", "123456");

/*存入

* ps = conn.prepareStatement("insert into t_user2 (username, myInfo) values(?, ?)");

ps.setString(1, "张三");

//ps.setClob(2, new FileReader(new File("f:/temp/最强兵王0--10.txt")));

ps.setClob(2, new BufferedReader(

new InputStreamReader(new ByteArrayInputStream("hello world!".getBytes()))));

ps.executeUpdate();*/

// 取出

ps = conn.prepareStatement("select * from t_user2 where id = ?");

ps.setInt(1, 2);

rs = ps.executeQuery();

while(rs.next()) {

Clob clob = rs.getClob("myInfo");

br = new BufferedReader(clob.getCharacterStream());

String line;

while((line = br.readLine()) != null) {

System.out.println(line);

}

}

} catch (Exception e) {

e.printStackTrace();

}finally {

try {

if(br != null) {

br.close();

}

} catch (IOException e) {

e.printStackTrace();

}

try {

if(rs != null) {

rs.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

try {

if(ps != null) {

ps.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

try {

if(conn != null) {

conn.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

}

}

运行代码：

可以看到我把存入的小说又读出来了

blob操作

public static void main(String[] args) {

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

BufferedReader br = null;

ByteArrayInputStream bis = null;

FileOutputStream fos = null;

try {

// 加载驱动类

Class.forName("com.mysql.jdbc.Driver");

// 建立连接

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc?useSSL=false", "root", "123456");

//存入

/*ps = conn.prepareStatement("insert into t_user2 (username, headImage) values(?, ?)");

ps.setString(1, "张飞");

ps.setBlob(2, new BufferedInputStream(new FileInputStream("f:/aa/temp/张飞.jpg")));

ps.executeUpdate();*/

// 取出

ps = conn.prepareStatement("select * from t_user2 where id = ?");

ps.setInt(1, 3);

rs = ps.executeQuery();

while(rs.next()) {

Blob blob = rs.getBlob("headImage");

bis = (ByteArrayInputStream) blob.getBinaryStream();

ByteArrayOutputStream bos = new ByteArrayOutputStream();

byte[] flush = new byte[1024];

int len;

while((len = bis.read(flush)) != -1) {

bos.write(flush, 0, len);

}

fos = new FileOutputStream("f:/aa/a.jpg");

fos.write(bos.toByteArray());

fos.flush();

}

} catch (Exception e) {

e.printStackTrace();

}finally {

try {

if(fos != null) {

fos.close();

}

} catch (IOException e) {

e.printStackTrace();

}

try {

if(br != null) {

br.close();

}

} catch (IOException e) {

e.printStackTrace();

}

try {

if(rs != null) {

rs.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

try {

if(ps != null) {

ps.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

try {

if(conn != null) {

conn.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

}

}

运行测试：

{{uploading-image-960855.png(uploading...)}}

存入的图片又回来了