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

川长思鸟来 2022-05-19 02:09 237阅读 0赞

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

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

--------------------

数据库表：

![70][]

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数据（二进制大数据对象） ###

--------------------

数据库表：

![70 1][]

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);
    	}
    }

[70]: /images/20220519/ee675c5ed2e744e2b5a4f7fde999dbc0.png
[70 1]: /images/20220519/008bd01546144556ac6c17875adc01ee.png