使用JDBC处理大数据（大文本/二进制数据）

 

使用JDBC处理大数据（大文本/二进制数据）：

大数据（LOB）：clod和blob

MySql中存储大文本只能用text

二进制数据用blob：图像。图片、声音等

 

一、举例向数据库中插入和读取大文本数据：

准备：

(1)从数据库中建表       表名为testclob

    create database jdbc;

    user jdbc;

    create table testclob

    (

       id int primary key auto_increment;

       resume text

    );

(2)jdbc驱动

(3)建立com.hbsi.utils包 ，DBManager类---载入驱动

（4）在项目中放置一个文本文件1.txt

编写代码：Demo1.java实现

// 大文本数据的存储

    public void insert() {

       Connection con = null;

       PreparedStatement st = null;

       ResultSet rs = null;

      

       try {

           //获取连接

           con = DBManager.getConnection();

           //定义sql语句

           String sql = "insert into testclob (resume) value(?)";

           //创建预处理对象

           st = con.prepareStatement(sql);

           File f = new File("1.txt");

           //系统会自动的读取1.txt文件中的内容并放到流中

           st.setCharacterStream(1, new FileReader(f),f.length());

           //执行更新语句

           int result = st.executeUpdate();

           if(result>0){

              System.out.println("插入成功");

           }else{

              System.out.println("插入失败");

           }

      

           DBManager.release(con, st, rs);

       } catch (SQLException e) {

           // TODO Auto-generated catch block

           e.printStackTrace();

       } catch (FileNotFoundException e) {

           // TODO Auto-generated catch block

           e.printStackTrace();

       }

   

    }

 

    //大文本数据的读取

    public void find(){

       Connection con = null;

       PreparedStatement st = null;

       ResultSet rs = null;

       try {

           //获取连接

           con = DBManager.getConnection();

           //定义sql语句

           String sql = "select resume from testclob where id=1";

           //创建预处理对象

           st = con.prepareStatement(sql);

           //执行更新

           rs = st.executeQuery();

           if(rs.next()){

              //如果文本太大，会死机

              //放到流中

              Reader reader = rs.getCharacterStream("resume");

              //创建缓冲区

              char buff[] = new char[1024];

              //通过循环的方法读取

              int len = 0;

              while((len = reader.read(buff))>0){

                  //读取后放到文件中

                  System.out.println(new String(buff,0,len));

              }

          

           }

           DBManager.release(con, st, rs);

       } catch (SQLException e) {

           // TODO Auto-generated catch block

           e.printStackTrace();

       } catch (IOException e) {

           // TODO Auto-generated catch block

           e.printStackTrace();

       }

    }

 

二、二进制数据的插入和读取：

// 把大二进制储存刀数据库中

    public void insert() {

       Connection con = null;

       PreparedStatement st = null;

       ResultSet rs = null;

       try {

           // 获取连接

           con = DBManager.getConnection();

           // 定义sql语句

           String sql = "insert into testblob (image) value(?)";

           // 创建预处理对象

           st = con.prepareStatement(sql);

      

           File f = new File("1.jpg");

           st.setBinaryStream(1, new FileInputStream(f), f.length());

           // 执行更新语句

           int result = st.executeUpdate();

           // 判断

           if (result > 0) {

              System.out.println("插入成功");

           } else {

              System.out.println("插入失败");

           }

 

           DBManager.release(con, st, rs);

       } catch (SQLException e) {

           // TODO Auto-generated catch block

           e.printStackTrace();

       } catch (FileNotFoundException e) {

           // TODO Auto-generated catch block

           e.printStackTrace();

       }

    }

 

    // 大二进制数据的读取

    public void find() {

       Connection con = null;

       PreparedStatement st = null;

       ResultSet rs = null;

 

       try {

 

           // 获取连接

           con = DBManager.getConnection();

           // 定义sql语句

           String sql = "select image from testblob where id=1";

           // 创建预处理对象

           st = con.prepareStatement(sql);

           // 执行更新

           rs = st.executeQuery();

           //判断

           if (rs.next()) {

              //读取

              InputStream fis =  rs.getBinaryStream("image");

              //定义缓冲区，读取到缓冲区 中

              byte[] buff = new byte[1024];

              int len= 0;

              FileOutputStream fos = new FileOutputStream("3.jpg");

              while((len=fis.read(buff))>0){

                  fos.write(buff,0,len);

              }

           }

           DBManager.release(con, st, rs);

       } catch (SQLException e) {

           // TODO Auto-generated catch block

           e.printStackTrace();

       } catch (IOException e) {

           // TODO Auto-generated catch block

           e.printStackTrace();

       }

    }