This website requires JavaScript.

    Java: 使用jdbc 连接MySQL数据库(五) 处理BLOB及CLOB

    默认分类

    2020-01-15

    1344

    0

    BLOB(binary large object) 二进制大对象,是一个可以存储二进制数据的容器.基本上是文档,图像,音频或者其他二进制对象. 一般数据库支持BLOB的并不多

    CLOB(character large object) 字符大对象是一个可以存储字符的容器,基本上用来存储文本文档(如text,XML)

    BLOB样本数据建立

    create database if not exists demo;

use demo;

drop table if exists employees;

CREATE TABLE `employees` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `last_name` varchar(64) DEFAULT NULL,
   `first_name` varchar(64) DEFAULT NULL,
   `email` varchar(64) DEFAULT NULL,
   `department` varchar(64) DEFAULT NULL,
   `salary` DECIMAL(10,2) DEFAULT NULL,
   `resume` BLOB,

   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (1,'Doe','John','john.doe@foo.com', 'HR', 55000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (2,'Public','Mary','mary.public@foo.com', 'Engineering', 75000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (3,'Queue','Susan','susan.queue@foo.com', 'Legal', 130000.00);

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (4,'Williams','David','david.williams@foo.com', 'HR', 120000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (5,'Johnson','Lisa','lisa.johnson@foo.com', 'Engineering', 50000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (6,'Smith','Paul','paul.smith@foo.com', 'Legal', 100000.00);

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (7,'Adams','Carl','carl.adams@foo.com', 'HR', 50000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (8,'Brown','Bill','bill.brown@foo.com', 'Engineering', 50000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (9,'Thomas','Susan','susan.thomas@foo.com', 'Legal', 80000.00);

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (10,'Davis','John','john.davis@foo.com', 'HR', 45000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (11,'Fowler','Mary','mary.fowler@foo.com', 'Engineering', 65000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (12,'Waters','David','david.waters@foo.com', 'Legal', 90000.00);

    Blob存储

    import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 
 * @author www.luv2code.com
 *
 */
public class WriteBlobDemo {

   public static void main(String[] args) throws Exception {

      Connection myConn = null;
      PreparedStatement myStmt = null;

      FileInputStream input = null;

      try {
         // 1\. Get a connection to database
         myConn = DriverManager.getConnection(
               "jdbc:mysql://localhost:3306/demo", "student", "student");

         // 2\. Prepare statement
         String sql = "update employees set resume=? where email='john.doe@foo.com'";
         myStmt = myConn.prepareStatement(sql);

         // 3\. Set parameter for resume file name
         File theFile = new File("sample_resume.pdf");
         input = new FileInputStream(theFile);
         myStmt.setBinaryStream(1, input);

         System.out.println("Reading input file: " + theFile.getAbsolutePath());

         // 4\. Execute statement
         System.out.println("\nStoring resume in database: " + theFile);
         System.out.println(sql);

         myStmt.executeUpdate();

         System.out.println("\nCompleted successfully!");

      } catch (Exception exc) {
         exc.printStackTrace();
      } finally {          
         if (input != null) {
            input.close();
         }

         close(myConn, myStmt);       
      }
   }

   private static void close(Connection myConn, Statement myStmt)
         throws SQLException {

      if (myStmt != null) {
         myStmt.close();
      }

      if (myConn != null) {
         myConn.close();
      }
   }

}

    BLOB读取

    import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 
 * @author www.luv2code.com
 *
 */
public class ReadBlobDemo {

   public static void main(String[] args) throws Exception {

      Connection myConn = null;
      Statement myStmt = null;
      ResultSet myRs = null;

      InputStream input = null;
      FileOutputStream output = null;

      try {
         // 1\. Get a connection to database
         myConn = DriverManager.getConnection(
               "jdbc:mysql://localhost:3306/demo", "student", "student");

         // 2\. Execute statement
         myStmt = myConn.createStatement();
         String sql = "select resume from employees where email='john.doe@foo.com'";
         myRs = myStmt.executeQuery(sql);

         // 3\. Set up a handle to the file
         File theFile = new File("resume_from_db.pdf");
         output = new FileOutputStream(theFile);

         if (myRs.next()) {

            input = myRs.getBinaryStream("resume"); 
            System.out.println("Reading resume from database...");
            System.out.println(sql);

            byte[] buffer = new byte[1024];
            while (input.read(buffer) > 0) {
               output.write(buffer);
            }

            System.out.println("\nSaved to file: " + theFile.getAbsolutePath());

            System.out.println("\nCompleted successfully!");            
         }

      } catch (Exception exc) {
         exc.printStackTrace();
      } finally {
         if (input != null) {
            input.close();
         }

         if (output != null) {
            output.close();
         }

         close(myConn, myStmt);
      }
   }

   private static void close(Connection myConn, Statement myStmt)
         throws SQLException {

      if (myStmt != null) {
         myStmt.close();
      }

      if (myConn != null) {
         myConn.close();
      }
   }
}

    CLOB样本建立

    create database if not exists demo;

use demo;

drop table if exists employees;

CREATE TABLE `employees` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `last_name` varchar(64) DEFAULT NULL,
   `first_name` varchar(64) DEFAULT NULL,
   `email` varchar(64) DEFAULT NULL,
   `department` varchar(64) DEFAULT NULL,
   `salary` DECIMAL(10,2) DEFAULT NULL,
   `resume` LONGTEXT,

   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (1,'Doe','John','john.doe@foo.com', 'HR', 55000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (2,'Public','Mary','mary.public@foo.com', 'Engineering', 75000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (3,'Queue','Susan','susan.queue@foo.com', 'Legal', 130000.00);

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (4,'Williams','David','david.williams@foo.com', 'HR', 120000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (5,'Johnson','Lisa','lisa.johnson@foo.com', 'Engineering', 50000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (6,'Smith','Paul','paul.smith@foo.com', 'Legal', 100000.00);

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (7,'Adams','Carl','carl.adams@foo.com', 'HR', 50000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (8,'Brown','Bill','bill.brown@foo.com', 'Engineering', 50000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (9,'Thomas','Susan','susan.thomas@foo.com', 'Legal', 80000.00);

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (10,'Davis','John','john.davis@foo.com', 'HR', 45000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (11,'Fowler','Mary','mary.fowler@foo.com', 'Engineering', 65000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (12,'Waters','David','david.waters@foo.com', 'Legal', 90000.00);

    CLOB写入

    import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 
 * @author www.luv2code.com
 *
 */
public class WriteClobDemo {

   public static void main(String[] args) throws Exception {

      Connection myConn = null;
      PreparedStatement myStmt = null;

      FileReader input = null;

      try {
         // 1\. Get a connection to database
         myConn = DriverManager.getConnection(
               "jdbc:mysql://localhost:3306/demo", "student", "student");

         // 2\. Prepare statement
         String sql = "update employees set resume=? where email='john.doe@foo.com'";
         myStmt = myConn.prepareStatement(sql);

         // 3\. Set parameter for resume file name
         File theFile = new File("sample_resume.txt");
         input = new FileReader(theFile);
         myStmt.setCharacterStream(1, input);

         System.out.println("Reading input file: " + theFile.getAbsolutePath());

         // 4\. Execute statement
         System.out.println("\nStoring resume in database: " + theFile);
         System.out.println(sql);

         myStmt.executeUpdate();

         System.out.println("\nCompleted successfully!");

      } catch (Exception exc) {
         exc.printStackTrace();
      } finally {          
         if (input != null) {
            input.close();
         }

         close(myConn, myStmt);       
      }
   }

   private static void close(Connection myConn, Statement myStmt)
         throws SQLException {

      if (myStmt != null) {
         myStmt.close();
      }

      if (myConn != null) {
         myConn.close();
      }
   }

}

    CLOB读取

    import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 
 * @author www.luv2code.com
 *
 */
public class ReadClobDemo {

   public static void main(String[] args) throws Exception {

      Connection myConn = null;
      Statement myStmt = null;
      ResultSet myRs = null;

      Reader input = null;
      FileWriter output = null;

      try {
         // 1\. Get a connection to database
         myConn = DriverManager.getConnection(
               "jdbc:mysql://localhost:3306/demo", "student", "student");

         // 2\. Execute statement
         myStmt = myConn.createStatement();
         String sql = "select resume from employees where email='john.doe@foo.com'";
         myRs = myStmt.executeQuery(sql);

         // 3\. Set up a handle to the file
         File theFile = new File("resume_from_db.txt");
         output = new FileWriter(theFile);

         if (myRs.next()) {

            input = myRs.getCharacterStream("resume"); 
            System.out.println("Reading resume from database...");
            System.out.println(sql);

            int theChar;
            while ((theChar = input.read()) > 0) {
               output.write(theChar);
            }

            System.out.println("\nSaved to file: " + theFile.getAbsolutePath());

            System.out.println("\nCompleted successfully!");            
         }

      } catch (Exception exc) {
         exc.printStackTrace();
      } finally {
         if (input != null) {
            input.close();
         }

         if (output != null) {
            output.close();
         }

         close(myConn, myStmt);
      }
   }

   private static void close(Connection myConn, Statement myStmt)
         throws SQLException {

      if (myStmt != null) {
         myStmt.close();
      }

      if (myConn != null) {
         myConn.close();
      }
   }
}
    

    

     
     #Java8
      
    0条评论 
    avatar
     
     
      
     
       
       
       
      
    © 2014 - present | Haseo Chen | Proudly powered by
    Dice
       
      沪ICP备20002437号