处理大数据对象

1. 处理大数据对象

大数据对象处理主要有CLOB(character large object)和BLOB(binary large object)两种类型的字段；在CLOB中可以存储大字符数据对象，比如长篇小说；在BLOB中可以存放二进制大数据对象，比如图片，电影，音乐；

1.1) 处理CLOB数据

alter table t_book add context longtext;

Book.java

package com.andrew.jdbc.model;

import java.io.File;

public class Book {
    private int id;
    private String bookName;
    private float price;
    private String author;
    private int bookTypeId;
    private File context;
    private File pic;
    public Book(String bookName, float price, String author, int bookTypeId) {
        super();
        this.bookName = bookName;
        this.price = price;
        this.author = author;
        this.bookTypeId = bookTypeId;
    }
    public Book(int id, String bookName, float price, String author, int bookTypeId) {
        super();
        this.id = id;
        this.bookName = bookName;
        this.price = price;
        this.author = author;
        this.bookTypeId = bookTypeId;
    }
    public Book(String bookName, float price, String author, int bookTypeId, File context, File pic) {
        super();
        this.bookName = bookName;
        this.price = price;
        this.author = author;
        this.bookTypeId = bookTypeId;
        this.context = context;
        this.pic = pic;
    }
    public Book(String bookName, float price, String author, int bookTypeId, File context) {
        super();
        this.bookName = bookName;
        this.price = price;
        this.author = author;
        this.bookTypeId = bookTypeId;
        this.context = context;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getBookName() {
        return bookName;
    }
    public void setBookName(String bookName) {
        this.bookName = bookName;
    }
    public float getPrice() {
        return price;
    }
    public void setPrice(float price) {
        this.price = price;
    }
    public String getAuthor() {
        return author;
    }
    public void setAuthor(String author) {
        this.author = author;
    }
    public int getBookTypeId() {
        return bookTypeId;
    }
    public void setBookTypeId(int bookTypeId) {
        this.bookTypeId = bookTypeId;
    }
    public File getContext() {
        return context;
    }
    public void setContext(File context) {
        this.context = context;
    }
    public File getPic() {
        return pic;
    }
    public void setPic(File pic) {
        this.pic = pic;
    }
    @Override
    public String toString() {
        return "["+this.id+","+this.bookName+","+this.price+","+this.author+","+this.bookTypeId+"]";
    }
}

FileTest.java

package com.andrew.jdbc.chap06;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.andrew.jdbc.model.Book;
import com.andrew.jdbc.util.DbUtil;

public class FileTest {
    private static DbUtil dbUtil = new DbUtil();
    private static int addBook(Book book) throws Exception {
        Connection connection = dbUtil.getConnection();
        String sql = "insert into t_book values(null,?,?,?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, book.getBookName());
        preparedStatement.setFloat(2, book.getPrice());
        preparedStatement.setString(3, book.getAuthor());
        preparedStatement.setInt(4, book.getBookTypeId());
        File context = book.getContext();
        InputStream inputStream = new FileInputStream(context);
        preparedStatement.setAsciiStream(5, inputStream, context.length());
        int result = preparedStatement.executeUpdate();
        dbUtil.close(preparedStatement, connection);
        return result;
    }
    public static void getBook(int id) throws Exception {
        Connection connection = dbUtil.getConnection();
        String sql = "select * from t_book where id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, id);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            String bookName = resultSet.getString("bookName");
            float price = resultSet.getFloat("price");
            String author = resultSet.getString("author");
            int bookTypeId = resultSet.getInt("bookTypeId");
            Clob c = resultSet.getClob("context");
            String context = c.getSubString(1, (int) c.length());
            System.out.println("图书名称：" + bookName);
            System.out.println("图书价格:" + price);
            System.out.println("图书作者：" + author);
            System.out.println("图书类型ID：" + bookTypeId);
            System.out.println("图书内容：" + context);
        }
        dbUtil.close(preparedStatement, connection);
    }
    public static void main(String[] args) throws Exception {
        File context = new File("e:/helloWorld.txt");
        Book book = new Book("helloWorld", 100, "andrew", 1, context);
        int result = addBook(book);
        if (result == 1) {
            System.out.println("添加成功！");
        } else {
            System.out.println("添加失败！");
        }
        getBook(5);
    }
}

添加成功！
图书名称：helloWorld
图书价格:100.0
图书作者：andrew
图书类型ID：1
图书内容：package com.andrew.jdbc.chap06.sec01;

public class Demo1 {

    public static void main(String[] args) {
        System.out.println("HelloWorld!");
    }
}

1.2) 处理BLOG数据

alter table t_book add pic blob;

PicTest.java

package com.andrew.jdbc.chap06;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.andrew.jdbc.model.Book;
import com.andrew.jdbc.util.DbUtil;

public class PicTest {
    private static DbUtil dbUtil = new DbUtil();
    private static int addBook(Book book) throws Exception {
        Connection connection = dbUtil.getConnection();
        String sql = "insert into t_book values(null,?,?,?,?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, book.getBookName());
        preparedStatement.setFloat(2, book.getPrice());
        preparedStatement.setString(3, book.getAuthor());
        preparedStatement.setInt(4, book.getBookTypeId());
        File context = book.getContext();
        InputStream inputStream = new FileInputStream(context);
        preparedStatement.setAsciiStream(5, inputStream, context.length());
        File pic = book.getPic(); // 获取图片文件
        InputStream inputStream2 = new FileInputStream(pic);
        preparedStatement.setBinaryStream(6, inputStream2, pic.length());
        int result = preparedStatement.executeUpdate();
        dbUtil.close(preparedStatement, connection);
        return result;
    }

    public static void getBook(int id) throws Exception {
        Connection connection = dbUtil.getConnection();
        String sql = "select * from t_book where id=?";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setInt(1, id);
        ResultSet resultSet = pstmt.executeQuery();
        if (resultSet.next()) {
            String bookName = resultSet.getString("bookName");
            float price = resultSet.getFloat("price");
            String author = resultSet.getString("author");
            int bookTypeId = resultSet.getInt("bookTypeId");
            Clob c = resultSet.getClob("context");
            String context = c.getSubString(1, (int) c.length());
            Blob b = resultSet.getBlob("pic");
            FileOutputStream out = new FileOutputStream(new File("E:/pic1jdbc.jpg"));
            out.write(b.getBytes(1, (int) b.length()));
            out.close();
            System.out.println("图书名称：" + bookName);
            System.out.println("图书价格:" + price);
            System.out.println("图书作者：" + author);
            System.out.println("图书类型ID：" + bookTypeId);
            System.out.println("图书内容：" + context);
        }
        dbUtil.close(pstmt, connection);
    }

    public static void main(String[] args) throws Exception {
        File context = new File("E:/helloWorld.txt");
        File pic = new File("E:/pic1.jpg");
        Book book = new Book("helloWorld", 100, "andrew", 1, context, pic);
        int result = addBook(book);
        if (result == 1) {
            System.out.println("添加成功！");
        } else {
            System.out.println("添加失败！");
        }
        getBook(6);
    }
}

运行结果：
添加成功！
图书名称：helloWorld
图书价格:100.0
图书作者：andrew
图书类型ID：1
图书内容：package com.andrew.jdbc.chap06.sec01;

public class Demo1 {

    public static void main(String[] args) {
        System.out.println("HelloWorld!");
    }
}