心得6--JDBC回顾-如何处理大文本和图像声音案例分析

1.  基础知识回顾

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

对MySQL而言只有blob，而没有clob，mysql存储大文本采用的是Text，Text和blob分别又分为：

•       TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT

•       TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB

2. 具体案例分析

（1）运用jdbc插入图片（二进制）和输出图片

packagecom.bigText;

 

importjava.io.File;

importjava.io.FileInputStream;

importjava.io.FileOutputStream;

importjava.io.InputStream;

importjava.sql.Connection;

importjava.sql.PreparedStatement;

importjava.sql.ResultSet;

importcom.Db.DbManager;

 

publicclass BlobTest {

   public void insert(){

      Connection con =DbManager.getConnection();

      PreparedStatement st = null;

      try {  

           String sql = "insert into dbBlob(image) values(?)";

              st = con.prepareStatement(sql); 

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

                FileInputStream fs = newFileInputStream(f);

               st.setBinaryStream(1,fs,f.length()); //注意length长度须设置，并且设置为int型

           int i = st.executeUpdate(); 

           if(i>0){

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

           }

        } catch (Exception e) {

        // TODO Auto-generated catch block

          e.printStackTrace();

      }finally{

        DbManager.closeDB(con, st, null);

      }

   } 

   public void select(){

      Connection con =DbManager.getConnection();

      PreparedStatement st = null;

      ResultSet rs = null;

      try {  

        String sql = "select image fromdbBlob";

        st = con.prepareStatement(sql); 

        rs = st.executeQuery(); 

        while(rs.next()){

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

          FileOutputStream fs = new FileOutputStream("D:\\2.jpg");

          byte[] b = new byte[1024];

          int count = 0;

          while((count=is.read(b,0,1024))!=-1){

             fs.write(b,0,count);

            }

          is.close();

          fs.close();

        }

      } catch (Exception e) {

        // TODO Auto-generated catch block

        e.printStackTrace();

      }finally{

        DbManager.closeDB(con, st, null);

      }

   } 

   public static void main(String[] args) {

      BlobTest ct = new BlobTest();

      ct.select();

   }

}

（2）插入大文本和遍历大文本内容

packagecom.bigText;

 

importjava.io.BufferedReader;

importjava.io.File;

importjava.io.FileReader;

importjava.io.Reader;

importjava.sql.Connection;

importjava.sql.PreparedStatement;

importjava.sql.ResultSet;

importcom.Db.DbManager;

 

publicclass ClobTest {

 

   public void insert(){

      Connection con =DbManager.getConnection();

      PreparedStatement st = null;

      try {  

           String sql = "insert into dbClob values(?)";

           st = con.prepareStatement(sql); 

                File f = new File("src/com/code/Demo1.java");

                BufferedReader br = newBufferedReader(new FileReader(f));

               st.setCharacterStream(1,br,f.length());

           int i = st.executeUpdate(); 

           if(i>0){

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

           }

        } catch (Exception e) {

        // TODO Auto-generated catch block

          e.printStackTrace();

      }finally{

        DbManager.closeDB(con, st, null);

      }

   } 

   public void select(){

      Connection con =DbManager.getConnection();

      PreparedStatement st = null;

      ResultSet rs = null;

      try {  

        String sql = "select member fromdbClob";

        st = con.prepareStatement(sql); 

        //st.setInt(1, 1);

        rs = st.executeQuery(); 

        while(rs.next()){

          Reader r = rs.getCharacterStream("member");

          BufferedReader br = new BufferedReader(r);

          String line = null;

          while((line=br.readLine())!=null){

             System.out.println(line);

          }

          r.close();

          br.close();

        }

      } catch (Exception e) {

        // TODO Auto-generated catch block

        e.printStackTrace();

      }finally{

        DbManager.closeDB(con, st, null);

      }

   } 

   public static void main(String[] args) {

      ClobTest ct = new ClobTest();

      ct.select();

   }

}

（3）一个综合案例

  向person表中插入数据（姓名、头像、资料）等

package com.zuoye;

 

importjava.io.BufferedReader;

importjava.io.File;

importjava.io.FileInputStream;

importjava.io.FileReader;

importjava.sql.Connection;

importjava.sql.PreparedStatement;

 

importcom.Db.DbManager;

 

publicclass Person {

 

   publicvoid insert(){

      Connection con = DbManager.getConnection();

      PreparedStatement st = null;

      try {  

           String sql = "insertinto person values(?,?,?,?)";

          st = con.prepareStatement(sql); 

               File f1 = new File("src/Eclipse.txt");

               File f2 = new File("src/1.jpg");

               BufferedReader br =newBufferedReader(new FileReader(f1));

               FileInputStream fs = new FileInputStream(f2);

               st.setInt(1,1);

               st.setString(2, "789");

               st.setBinaryStream(3, fs, f2.length());

               st.setCharacterStream(4,br,f1.length());

          int i = st.executeUpdate(); 

          if(i>0){

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

          }

        } catch (Exception e) {

        // TODO Auto-generated catch block

          e.printStackTrace();

      }finally{

        DbManager.closeDB(con, st, null);

      }

   } 

   publicstaticvoid main(String[]args) {

      Person p = new Person();

      p.insert();

 

   }

 

}

 

转载于:https://www.cnblogs.com/yangkai-cn/archive/2012/09/08/4017495.html