JDBC之——处理大数据（CLOB和BLOG数据）

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

一、处理CLOB数据（字符数据对象）

---

数据库表：

DbUtil类：

package com.java1234.jdbc.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class DbUtil {
	//数据库地址
	private static String dbUrl="jdbc:mysql://localhost:3306/db_book";
	//用户名
	private static String dbUserName="root";
	//密码
	private static String dbPassword="root";
	//驱动名称
	private static String jdbcName="com.mysql.jdbc.Driver";
	/**
	 * 获取数据库连接
	 * 	1.加载数据库驱动
	 *  2.获取数据库连接
	 */
	public Connection getCon() throws Exception {
		Class.forName(jdbcName);//加载数据库驱动
		Connection con = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
		return con;
	}
	/**
	 * 关闭连接
	 */
	public void close(Statement stmt,Connection con) throws Exception {
		if(stmt!=null) {
			stmt.close();
		}
		if(con!=null) {
			con.close();
		}
	}
}

Book实体类：

public class Book {
	private int id;
	private String bookName;
	private float price;
	private String author;
	private int bookTypeId;
	private File context;

	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 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 class Demo1 {
	private static DbUtil dbUtil = new DbUtil();
	
	/**
	 * 添加图书
	 */
	private static int addBook(Book book) throws Exception{
		Connection con = dbUtil.getCon();
		String sql = "insert into t_book values(null,?,?,?,?,?)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, book.getBookName());
		pstmt.setFloat(2, book.getPrice());
		pstmt.setString(3, book.getAuthor());
		pstmt.setInt(4, book.getBookTypeId());
		File context = book.getContext();//获取文件
		InputStream inputStream = new FileInputStream(context);
		pstmt.setAsciiStream(5, inputStream, context.length());//给第五个坑设值
		int result = pstmt.executeUpdate();
		dbUtil.close(pstmt, con);
		return result;
	}
	/**
	 * 查询图书
	 */
	public static void getBook(int id) throws Exception{
		Connection con = dbUtil.getCon();
		String sql = "select * from t_book where id = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, id);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()) {
			String bookName = rs.getString("bookName");
			float price = rs.getFloat("price");
			String author = rs.getString("author");
			int bookTypeId = rs.getInt("bookTypeId");
			Clob clob = rs.getClob("context");//获取大文本对象
			String context = clob.getSubString(1, (int)clob.length());
			System.out.println("图书名称："+bookName);
			System.out.println("图书价格："+price);
			System.out.println("图书作者："+author);
			System.out.println("图书类型ID"+bookTypeId);
			System.out.println("图书内容："+context);
		}
		rs.close();
		dbUtil.close(pstmt, con);
	}
	public static void main(String[] args) throws Exception {
		File file = new File("c:/helloworld.txt");
		Book book = new Book("javaXX", 129.90F, "XX", 1, file);
		int result = addBook(book);
		if(result == 1) {
			System.out.println("新增成功");
		}else {
			System.out.println("新增失败");
		}
                getBook(7);
	}
}

 

二、处理BLOG数据（二进制大数据对象）

---

数据库表：

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, 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 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 class Demo2 {
	private static DbUtil dbUtil = new DbUtil();
	
	/**
	 * 添加图书
	 */
	private static int addBook(Book book) throws Exception{
		Connection con = dbUtil.getCon();
		String sql = "insert into t_book values(null,?,?,?,?,?,?)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, book.getBookName());
		pstmt.setFloat(2, book.getPrice());
		pstmt.setString(3, book.getAuthor());
		pstmt.setInt(4, book.getBookTypeId());
		File context = book.getContext();//获取文件
		InputStream inputStream = new FileInputStream(context);
		pstmt.setAsciiStream(5, inputStream, context.length());//给第五个坑设值
		File pic = book.getPic();//获取图片文件
		InputStream inputStream2 = new FileInputStream(pic);
		pstmt.setBinaryStream(6, inputStream2, pic.length());//给第六个坑大BLOB设置值
		int result = pstmt.executeUpdate();
		dbUtil.close(pstmt, con);
		return result;
	}
	
	/**
	 * 查询图书
	 */
	public static void getBook(int id) throws Exception{
		Connection con = dbUtil.getCon();
		String sql = "select * from t_book where id = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, id);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()) {
			String bookName = rs.getString("bookName");
			float price = rs.getFloat("price");
			String author = rs.getString("author");
			int bookTypeId = rs.getInt("bookTypeId");
			Clob clob = rs.getClob("context");//获取大文本对象
			String context = clob.getSubString(1, (int)clob.length());
			Blob b = rs.getBlob("pic");
			FileOutputStream out = new FileOutputStream(new File("d:/pic2.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);
		}
		rs.close();
		dbUtil.close(pstmt, con);
	}
	public static void main(String[] args) throws Exception {
		File file = new File("c:/helloworld.txt");
		File pic = new File("c:/pic1.jpg");
		Book book = new Book("javaXX", 129.90F, "XX", 1, file,pic);
		int result = addBook(book);
		if(result == 1) {
			System.out.println("新增成功");
		}else {
			System.out.println("新增失败");
		}
		getBook(8);
	}
}