mysql数据库二进制类型_用jdbc访问二进制类型的数据

1、创建数据表(MYSQL数据库)

create table big_blob (

id int(10),

blob_txt blob

);

2、JdbcUtils工具类

package com.sunrex;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

/**

* 东南西北风

* @author Administrator

*

*/

public final class JdbcUtils {

private static Connection conn = null;

/* 访问数据库的url*/

private final static String DATABASE_URL =

"jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=UTF8";

private final static String DATABASE_USERNAME = "root";

private final static String DATABASE_PASSWORD = "root";

static {

try {

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

conn = DriverManager.getConnection(DATABASE_URL, DATABASE_USERNAME, DATABASE_PASSWORD);

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

}

private JdbcUtils(){}

public static Connection getConnection() {

return conn;

}

public static void close(Connection conn, Statement stmt, ResultSet rs) {

try {

if(rs!=null) rs.close();

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

if(stmt!=null)

stmt.close();

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

if(conn!=null)

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}

}

3、二进制文件操作类

package com.sunrex.demo06;

import java.io.BufferedInputStream;

import java.io.BufferedOutputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.InputStream;

import java.io.OutputStream;

import java.sql.Blob;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import com.sunrex.JdbcUtils;

/**

* 用jdbc访问二进制类型的数据

* 在数据库中，经常需要用到大字段类型，

* 如oracle中long/blob/clob,

* sqlserver中text/image，

* mysql中的text/longtext/clob/blob。

*/

public class BlobTest {

public static void main(String[] args) throws Exception {

//insert();

read(1);

}

/**

* 添加数据

* @throws Exception

*/

public static void insert() throws Exception {

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

try {

conn = JdbcUtils.getConnection();

//---注意：如果数据库中设置的是blob类型字段，二进制的最大容量不能超过64K

String sql = "insert into big_blob (id, blob_txt) values (?, ?)";

pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, 1);

//使用字节流，读取文本信息

File file = new File("blob.jpg");

InputStream is = new BufferedInputStream(new FileInputStream(file));

pstmt.setBinaryStream(2, is, (int)file.length());//填充二进制流

is.close();

pstmt.executeUpdate();

} finally {

JdbcUtils.close(conn, pstmt, rs);

}

}

/**

* 读取数据

* @param id

*/

private static void read(int id) throws Exception {

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

try {

conn = JdbcUtils.getConnection();

//---注意：如果数据库中设置的是blob类型字段，二进制的最大容量不能超过64K

String sql = "select blob_txt from big_blob where id=?";

pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, id);

rs =pstmt.executeQuery();

while(rs.next()) {

读取方法一：

//Blob blob = rs.getBlob("blob_txt");

//InputStream is = blob.getBinaryStream();//输入流

//读取的方法二：

InputStream is = rs.getBinaryStream("blob_txt");

byte[] bytes = new byte[1024];

//输出流

File file = new File("blob_bak.jpg");

OutputStream os = new BufferedOutputStream(new FileOutputStream(file));

for(int i=0; (i=is.read(bytes))>0;) {

os.write(bytes, 0, i);

}

os.close();

is.close();

}

} finally {

JdbcUtils.close(conn, pstmt, rs);

}

}

}

分享到：

2010-09-05 21:20

浏览 800

评论