大数据的存取
基本概念:大数据也称之为LOB(Large Objects),LOB又分为clob和blob
clob用于存储大文本。
blob用于存储二进制数据,例如图像、声音、二进制文等。
对MySQL而言只有blob,而没有clob,mysql存储大文本采用的是Text,Text和blob分别又分为:
TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT
TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB
例:
**********************************************************************************************
大文本的存取
use day15;
create table t1(
id int primary key,
content longtext
);
public class ClobDemo {
@Test
public void testAdd(){
Connection conn = null;
PreparedStatement stmt = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("insert into t1 (id,content) value (?,?)");
stmt.setInt(1, 1);
// 大数据要使用流的形式
File file = new File("c:/jpm.txt");
Reader reader = new FileReader(file);
// 注: 最后一个参数要强转,不能使用long类型的参数,
// 因为mysql最多支持4G的数据大小,该方法只实现了int类型的参数,但是oracle支持long类型的参数
stmt.setCharacterStream(2, reader, (int)file.length());
int i = stmt.executeUpdate();
if(i>0){
System.out.println("插入成功");
}
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtil.release(null, stmt, conn);
}
}
@Test
public void testRead(){
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("select * from t1 where id=?");
stmt.setInt(1, 1);
rs = stmt.executeQuery();
// 大数据要使用流的形式
if(rs.next()){
Reader reader = rs.getCharacterStream("content");
Writer writer = new FileWriter("E:/jpm.txt");
char[] c = new char[1024];
int len = 0;
while((len=reader.read(c))!=-1){
writer.write(c,0,len);
}
writer.close();
reader.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
}
**********************************************************************************************
大二进制数据的存取
use day15;
create table t2(
id int primary key,
content longblob
);
public class BlobDemo {
@Test
public void testAdd(){
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("insert into t2 (id,content) value(?,?)");
stmt.setInt(1, 1);
// 大文本数据要采取流的形式
InputStream in = new FileInputStream("c:/1.jpg");
stmt.setBinaryStream(2, in, in.available()); // in.avaulable()为该文件的大小
int i = stmt.executeUpdate();
if(i>0){
System.out.println("插入成功");
}
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
@Test
public void testRead(){
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("select * from t2 where id=?");
stmt.setInt(1, 1);
rs = stmt.executeQuery();
// 大数据要采取流的形式
if(rs.next()){
InputStream in = rs.getBinaryStream("content");
OutputStream out = new FileOutputStream("E:/1.jpg");
byte[] b = new byte[1024];
int len = 0;
while((len=in.read(b))!=-1){
out.write(b, 0, len);
}
out.close();
in.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
}
**********************************************************************************************
基本概念:大数据也称之为LOB(Large Objects),LOB又分为clob和blob
clob用于存储大文本。
blob用于存储二进制数据,例如图像、声音、二进制文等。
对MySQL而言只有blob,而没有clob,mysql存储大文本采用的是Text,Text和blob分别又分为:
TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT
TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB
例:
**********************************************************************************************
大文本的存取
use day15;
create table t1(
id int primary key,
content longtext
);
public class ClobDemo {
@Test
public void testAdd(){
Connection conn = null;
PreparedStatement stmt = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("insert into t1 (id,content) value (?,?)");
stmt.setInt(1, 1);
// 大数据要使用流的形式
File file = new File("c:/jpm.txt");
Reader reader = new FileReader(file);
// 注: 最后一个参数要强转,不能使用long类型的参数,
// 因为mysql最多支持4G的数据大小,该方法只实现了int类型的参数,但是oracle支持long类型的参数
stmt.setCharacterStream(2, reader, (int)file.length());
int i = stmt.executeUpdate();
if(i>0){
System.out.println("插入成功");
}
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtil.release(null, stmt, conn);
}
}
@Test
public void testRead(){
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("select * from t1 where id=?");
stmt.setInt(1, 1);
rs = stmt.executeQuery();
// 大数据要使用流的形式
if(rs.next()){
Reader reader = rs.getCharacterStream("content");
Writer writer = new FileWriter("E:/jpm.txt");
char[] c = new char[1024];
int len = 0;
while((len=reader.read(c))!=-1){
writer.write(c,0,len);
}
writer.close();
reader.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
}
**********************************************************************************************
大二进制数据的存取
use day15;
create table t2(
id int primary key,
content longblob
);
public class BlobDemo {
@Test
public void testAdd(){
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("insert into t2 (id,content) value(?,?)");
stmt.setInt(1, 1);
// 大文本数据要采取流的形式
InputStream in = new FileInputStream("c:/1.jpg");
stmt.setBinaryStream(2, in, in.available()); // in.avaulable()为该文件的大小
int i = stmt.executeUpdate();
if(i>0){
System.out.println("插入成功");
}
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
@Test
public void testRead(){
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("select * from t2 where id=?");
stmt.setInt(1, 1);
rs = stmt.executeQuery();
// 大数据要采取流的形式
if(rs.next()){
InputStream in = rs.getBinaryStream("content");
OutputStream out = new FileOutputStream("E:/1.jpg");
byte[] b = new byte[1024];
int len = 0;
while((len=in.read(b))!=-1){
out.write(b, 0, len);
}
out.close();
in.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
}
**********************************************************************************************