插入和读取blob和clob类型数据

/******************blob*********************/
public class Blob_Test {
    //创建表
    @Test
    public void create(){
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs = null;

        JDBCUtils utils = JDBCUtils.getInstance();
        String sql ="create table Blob_Test(id int not null auto_increment,image BLOB,PRIMARY KEY(ID))";
        try {
            conn=utils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            utils.free(conn, ps, rs);
        }


    }
    //插入Blob数据
    @Test
    public void insert() throws IOException{
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs = null;
        JDBCUtils utils = JDBCUtils.getInstance();
        //得到一个文件
        File file = new File("src/2.jpg");
        //创建字节输入流
        InputStream in = new FileInputStream(file);
        //创建sql语句
        String sql ="insert into Blob_Test(image) values (?)";
        try {
            conn=utils.getConnection();
            ps = conn.prepareStatement(sql);
            //为？赋值 使用setBinaryStream方法
            ps.setBinaryStream(1, in, file.length());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            utils.free(conn, ps, rs);
        }
    }
    //读取数据库blob数据
    @Test
    public void read() throws IOException{
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs = null;
        JDBCUtils utils = JDBCUtils.getInstance();
        //创建一个文件
        File file = new File("BeautifulGirl.jpg");
        //创建一个字节输出流
        OutputStream out = new FileOutputStream(file);
        //创建sql语句，从数据库查出blob数据
        String sql ="select image from Blob_Test";
        try {
            conn=utils.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            //创建一个数组
            byte b[] = new byte[1024];
            while(rs.next()){
                //得到结果集中的blob数据
                Blob blob = rs.getBlob("image");
                //得到一个输入流
                InputStream in = blob.getBinaryStream();
                int len;
                while((len=in.read(b))!=-1){
                    //将数据流的数据写到输出流
                    out.write(b, 0, len);
                }
                //关闭流
                out.close();
                in.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            utils.free(conn, ps, rs);
        }
    }
}
/******************clob*********************/
public class Clob_Test {
    //创建表
    @Test
    public void create(){
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs =null;
        JDBCUtils utils = JDBCUtils.getInstance();
        try {
            String sql = "create table Clob_Test(id int not null auto_increment,text longtext,primary key(id))";
            conn = utils.getConnection();
            ps= conn.prepareStatement(sql);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            utils.free(conn, ps, rs);
        }
    }
    //向数据库插入clob数据
    @Test
    public void insert() throws IOException{
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs =null;
        JDBCUtils utils = JDBCUtils.getInstance();
        //将一个文件插入到数据库
        File file = new File("src/com/hdbc/day01/JDBCUtils.java");
        //创建一个字符输入缓冲流
        Reader reader = new  BufferedReader(new FileReader(file));
        try {
            String sql = "insert into Clob_Test(text) values (?)";
            conn = utils.getConnection();
            ps= conn.prepareStatement(sql);
            //以字符流的形式将文件存到数据库
            ps.setCharacterStream(1, reader,file.length());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            utils.free(conn, ps, rs);
        }
    }

    //从数据库读取clob数据
    @Test
    public void read() throws IOException{
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs =null;
        JDBCUtils utils = JDBCUtils.getInstance();
        //将clob数据读到文件中
        File file = new File("JDBCUtils_bak.java");
        //创建一个字符输出缓冲流
        Writer writer = new BufferedWriter(new FileWriter(file));
        char ch[] = new char[1024];
        try {
            String sql = "select text from Clob_Test";
            conn = utils.getConnection();
            ps= conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                //两种方式：
                //一种是先得到数据clob数据，
//              Clob clob = rs.getClob("text");
                //再得带字符输入流
//              Reader reader = clob.getCharacterStream();
                //第二种是可以直接得到clob数据的输入流
                Reader reader = rs.getCharacterStream("text");
                for(int i=0;(i=reader.read(ch))>0;){
                    //将数据写到目标文件中
                    writer.write(ch, 0, i);
                }
                writer.close();
                reader.close();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            utils.free(conn, ps, rs);
        }
    }
}