Java Web总结十三之三使用JDBC处理大数据

一、大数据也称为LOB（Large Objects），LOB又分为：clob和blob。

　　1、clob用于存储大文本。

　　2、blob用于存储二进制数据，例如图像、声音、二进制文本等。

　　3、对于MySql而言，只有blob，而没有clob，MySql存储大文本采用的是text，text和blob分别又分为：

　　　　1）tinyblob/blob/mediumblob/longblob-->255B/64K/16M/4G大小的图片/音乐等二进行数据

　　　　2）tinytext/text/mediumtext/longtext-->255B/64K/16M/4G大小的文本数据

二、MySql的数据类型　　

　　1、tinyint/smallint/mediumint/int/bigint-->1B/2B/3B/4B/8B

　　2、float/double-->单精度/双精度浮点型

　　3、decimal-->不会产生精度丢失的单精度/双精度浮点型

　　4、date-->日期类型

　　5、time-->时间类型

　　6、datetime-->日期时间类型

　　7、year-->年类型

　　8、char-->定长字符串类型

　　9、varchar-->可变长字符串类型

　　10、tinyblob/blob/mediumblob/longblob-->255B/64K/16M/4G大小的图片/音乐等二进行数据

　　11、tinytext/text/mediumtext/longtext-->255B/64K/16M/4G大小的文本数据

三、案例一：文本数据

1、Demo1.java：

package com.gnnuit.web.lob.demo;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Reader;
import java.io.Writer;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.UUID;

import com.gnnuit.web.lob.util.JdbcUtill;

//MySql对字符大对象的处理
public class Demo1 {

    public static void main(String[] args) {
        write();
        read();
    }

    /**
     * 把文件写到MySql数据库
     */
    private static void write() {
        URL url = Demo1.class.getClassLoader().getResource(
                "com/gnnuit/web/lob/config/62.txt");
        String path = url.getPath();
        File file = new File(path);
        Reader reader = null;
        try {
            reader = new FileReader(file);
        } catch (FileNotFoundException e1) {
            e1.printStackTrace();
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "insert into clob_test(id,content) values(?,?)";
        try {
            conn = JdbcUtill.getMySqlConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, UUID.randomUUID().toString());
            pstmt.setCharacterStream(2, reader, (int) file.length());
            int i = pstmt.executeUpdate();
            System.out.println(i > 0 ? "成功" : "失败");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtill.close(rs);
            JdbcUtill.close(pstmt);
            JdbcUtill.close(conn);
        }

    }

    /**
     * 把数据库里的文件读到硬盘里
     */
    private static void read() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select * from clob_test";
        Reader reader = null;
        Writer writer = null;
        try {
            conn = JdbcUtill.getMySqlConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                reader = rs.getCharacterStream("content");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtill.close(rs);
            JdbcUtill.close(pstmt);
            JdbcUtill.close(conn);
        }
        try {
            writer = new FileWriter("d:\\62.txt");
            int len = 0;
            char[] buf = new char[1024];
            while ((len = reader.read(buf)) > 0) {
                writer.write(buf, 0, len);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (writer != null) {
                try {
                    writer.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

2、Demo2.java：

package com.gnnuit.web.lob.demo;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.UUID;

import com.gnnuit.web.lob.util.JdbcUtill;

public class Demo2 {

    public static void main(String[] args) {
        write();
        read();
    }

    /**
     * 把二进制文件写到MySql数据库
     */
    private static void write() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "insert into blob_test(id,content) values(?,?)";

        try {
            conn = JdbcUtill.getMySqlConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, UUID.randomUUID().toString());

            URL url = Demo2.class.getClassLoader().getResource(
                    "com/gnnuit/web/lob/config/d1.jpg");
            File file = new File(url.getPath());
            InputStream is = new FileInputStream(file);

            pstmt.setBinaryStream(2, is, (int) file.length());
            int i = pstmt.executeUpdate();
            System.out.println(i > 0 ? "成功" : "失败");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtill.close(rs);
            JdbcUtill.close(pstmt);
            JdbcUtill.close(conn);
        }
    }

    /**
     * 将MySql数据库的文件读到硬盘里
     */
    private static void read() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select * from blob_test";
        InputStream is = null;
        OutputStream out = null;

        try {
            conn = JdbcUtill.getMySqlConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                is = rs.getBinaryStream("content");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtill.close(rs);
            JdbcUtill.close(pstmt);
            JdbcUtill.close(conn);
        }
        try {
            out = new FileOutputStream("d:\\d1.jpg");
            int len = 0;
            byte[] buf = new byte[1024];
            while ((len = is.read(buf)) > 0) {
                out.write(buf, 0, len);
            }
        } catch (Exception e1) {
            e1.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

}