JavaWeb(26) : 使用JDBC处理MySql大数据的三种方法

零、大数据基本概念

大数据也称之为LOB(Large Objects)，LOB又分为：clob和blob，clob用于存储大文本，blob用于存储二进制数据，例如图像、声音、二进制文等。

在实际开发中，有时是需要用程序把大文本或二进制数据直接保存到数据库中进行储存的。
　
对MySQL而言只有blob，而没有clob，mysql存储大文本采用的是Text，Text和blob分别又分为：

TINYTEXT(1B~256B)、TEXT、MEDIUMTEXT和LONGTEXT
TINYBLOB(1B-256B)、 BLOB(1B–64KB)、MEDIUMBLOB(1B—16MB)和LONGBLOB(1B~4GB)

数据库表1

CREATE TABLE `testblob` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `music` longblob,
  PRIMARY KEY (`id`)
) 

数据库表2

CREATE TABLE `tab_bin` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `filename` varchar(100) DEFAULT NULL,
  `data` mediumblob,
  PRIMARY KEY (`id`)
) 

方法1

package waf.yty.demo4;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import waf.yty.utils.JdbcUtils;

public class Demo4 {
	@Test
	public void add() throws SQLException, IOException {
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		
		conn = JdbcUtils.getConnection();
		String sql = "insert into testblob(music) values(?)";
		st = conn.prepareStatement(sql);
		String path = Demo4.class.getClassLoader().getResource("2.gif").getPath();
		path = path.replaceAll("%20", " ");
		File file = new File(path);
		FileInputStream fis = new FileInputStream(file);
		st.setBinaryStream(1, fis ,(int)file.length());
		int num = st.executeUpdate();
		if (num > 0) {
			System.out.println("插入成功!");
		}
		fis.close();
		JdbcUtils.release(conn, st, rs);
	}
	
	@Test
	public void read() throws SQLException, IOException {
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		
		conn = JdbcUtils.getConnection();
		String sql = "select music from testblob where id = ?";
		st = conn.prepareStatement(sql);
		st.setInt(1, 1);
		rs = st.executeQuery();
		if (rs.next()) {
			InputStream in = rs.getBinaryStream("music");
//			int len = 0;
//			int by = 0;
//			byte[] bys = new byte[1024];
//			FileOutputStream fos = new FileOutputStream("/Users/yangtengyu/desktop/CSDN/远洋荣域金陵雷神.gif");
			String outPath = "/Users/yangtengyu/desktop/CSDN/那些年的湖凯大战.gif";
			BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(outPath));
//			FileOutputStream fos = new FileOutputStream(outPath);
//			int by = 0;
			int len = 0;
			byte[] bys = new byte[1024];
			while ((len = in.read(bys)) != -1 ) {
				bos.write(bys,0,len);
			}
			in.close();
			bos.close();
		}
		JdbcUtils.release(conn, st, rs);
	}
}

方法2

package waf.yty.demo4;

import java.io.BufferedOutputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.rowset.serial.SerialBlob;

import org.junit.Test;

import waf.yty.utils.JdbcUtils;

public class Demo46 {
	@Test
	public void fun1() throws SQLException, IOException {
		
		Connection con = JdbcUtils.getConnection();
		String sql = "insert into tab_bin values(?,?,?)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		ResultSet rs = null;
		
		pstmt.setObject(1, null);
		pstmt.setString(2, "天龙八部原声带之欢快桂州.mp3");
		
		String path = "/Users/yangtengyu/downloads/摇就完事儿了哦铁汁/原声带-桂州.m4a";
		byte[] bytesByFile = getBytesByFile(path);
		Blob blob = new SerialBlob(bytesByFile);
		
		pstmt.setBlob(3, blob);
		
		int result = pstmt.executeUpdate();
		
		if (result != 0) {
			System.out.println("音乐植入成功!music!!");
		}
		JdbcUtils.release(con, pstmt, rs);
	}
	
	public byte[] getBytesByFile(String filePath) throws IOException {
		FileInputStream fis = new FileInputStream(filePath);
		ByteArrayOutputStream bos = new ByteArrayOutputStream();;
		byte[] bys = new byte[1024];
		int len = 0;
		while ((len = fis.read(bys)) != -1) {
			bos.write(bys, 0, len);
		}
		fis.close();
		byte[] data = bos.toByteArray();
		bos.close();
		return data;
		
	}
	
	@Test
	public void fun2() throws SQLException, IOException {
		Connection con = JdbcUtils.getConnection();
		String sql = "select * from tab_bin";
		PreparedStatement pstmt = con.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		
		if (rs.next()) {
			InputStream is = rs.getBinaryStream("data");
			BufferedOutputStream bos = 
			new BufferedOutputStream(new FileOutputStream("/Users/yangtengyu/desktop/Wdnmd/桂州小闸总.mp3"));
			int len = 0;
			byte[] bys = new byte[1026];
			while ((len = is.read(bys)) != -1) {
				bos.write(bys, 0, len);
			}
			bos.close();
			is.close();
		}
		
		JdbcUtils.release(con, pstmt, rs);
	}

}

方法3

package waf.yty.demo4;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.io.IOUtils;
import org.junit.Test;

import waf.yty.utils.JdbcUtils;

public class Demo66 {
	@Test
	public void fun1() throws SQLException, IOException {
		Connection con = JdbcUtils.getConnection();
		String sql = "insert into testblob(music) values (?) ";
		PreparedStatement pstmt = con.prepareStatement(sql);
		ResultSet rs = null;
		String filePath = "/Users/yangtengyu/downloads/摇就完事儿了哦铁汁/原声带-河南开封.m4a";
		pstmt.setBlob(1, new FileInputStream(filePath));
		int result = pstmt.executeUpdate();
		if (result != 0) {
			System.out.println("植入音乐成功!");
		}
		JdbcUtils.release(con, pstmt, rs);
	}
	
	@Test
	public void read() throws SQLException, IOException {
		Connection con = JdbcUtils.getConnection();
		String sql = "select music from testblob where id = ? ";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, 2);
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			Blob blob = rs.getBlob("music");
			InputStream ins = blob.getBinaryStream();
			FileOutputStream fos = new FileOutputStream("/Users/yangtengyu/desktop/Wdnmd/河南开封蔡建平.mp3");
			IOUtils.copy(ins, fos);
		}
		JdbcUtils.release(con, pstmt, rs);
	}

}