mysql数据处理

在数据库中大数据 LOB —— Oracle 提供Clob Blob Clob大型文本数据 Blob大型二进制数据
Mysql Text 相当于 Oracle Clob
* LOB 最大存储范围4GB longText longBlob

创建存放Text文本文件 table
create table textdata(
id int primary key not null auto_increment,
mytext longtext
);

java.lang.AbstractMethodError: com.mysql.jdbc.PreparedStatement.setCharacterStream(ILjava/io/Reader;)V
没有设置文件长度 —- 必须设置文件长度，类型int

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1928574 > 1048576). You can change this value on the server by setting the max_allowed_packet’ variable.
文件太大了
* 必须设置mysql/my.ini
[mysqld] max_allowed_packet=64M

新建存放blob类型table
create table blobdata(
id int primary key not null auto_increment,
myblob longblob
);

• 在实际开发中，很少需要将 大文本文件或者音乐、电影保存到数据库

package blob;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.PrintWriter;
import java.io.Reader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import it.cast.jdbc.JDBCUtils;

public class lobtest {
@Test
public void demo3(){
//将一个文本文件保存到数据库
Connection conn=null;
PreparedStatement stmt=null;
try {
conn=JDBCUtils.getConnection();
String sql=”insert into blobdata values(null,?)”;
stmt=conn.prepareStatement(sql);//预编译
File file=new File(“oldboy.mp3”);
stmt.setBinaryStream(1, new FileInputStream(“oldboy.mp3”),(int)file.length());//给问号填数据，前面的是问号的角标
System.out.println(1);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
JDBCUtils.release(conn, stmt);
}
}
@Test
public void demo2(){
//将一个文本文件保存到数据库
Connection conn=null;
PreparedStatement stmt=null;
ResultSet rs=null;
try {
conn=JDBCUtils.getConnection();
String sql=”select * from textdata where id=?”;
stmt=conn.prepareStatement(sql);//预编译
stmt.setInt(1, 2);
rs=stmt.executeQuery();
while(rs.next()){
Reader reader=rs.getCharacterStream(“mytext”);
File file=new File(“d://heeh.txt”);
Writer writer=new PrintWriter(“file”);
BufferedReader bufr=new BufferedReader(reader);
int c;
while((c=bufr.read())!=-1){
writer.write(c);

            }
            reader.close();
            bufr.close();
            writer.close();
        }
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } finally{
        JDBCUtils.release(conn, stmt);
    }
 }

@Test
public void demo1(){
//将一个文本文件保存到数据库
Connection conn=null;
PreparedStatement stmt=null;
try {
conn=JDBCUtils.getConnection();
String sql=”insert into textdata values(null,?)”;
stmt=conn.prepareStatement(sql);//预编译
File file=new File(“dulala.txt”);
stmt.setCharacterStream(1, new FileReader(“dulala.txt”),(int)file.length());//给问号填数据，前面的是问号的角标
System.out.println(1);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
JDBCUtils.release(conn, stmt);
}
}
}