MySQL中大文本或大文件的存取_mysql获取text too large的内容-优快云博客

本文介绍了一种使用Java进行LOB（大对象）数据操作的方法，包括如何存储和检索大型文本和二进制文件到数据库中。通过具体示例展示了如何利用Java流处理技术将文件读写操作与数据库交互相结合。

Clob:Character Large Object字符（小说）
Blob:Binary Large Object二进制

LobDemo.java文件：

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Reader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.junit.Test;

//大数据的存取
public class LobDemo {
    /*
     create table t2(
        id int,
        content longtext
     );
     */

    //存大文本数据
    @Test
    public void test1() throws Exception{
        Connection conn = JdbcUtil.getConnection();
        PreparedStatement stmt = conn.prepareStatement("insert into t2 values (?,?)");
        stmt.setInt(1, 1);
        //以流的方式
        File file = new File("src/jpm.txt");
        Reader reader = new FileReader(file);
        stmt.setCharacterStream(2, reader, (int)file.length());//PreparedStatement的实现是由数据库驱动提供的
                                                            //MySQL:setCharacterStream(int,Reader,long);根本没有实现。
                                                            //MySQL根本不支持那么大的数据。
        stmt.executeUpdate();
        JdbcUtil.release(null, stmt, conn);
    }

    //取大文本数据
    @Test
    public void test2() throws Exception{
        Connection conn = JdbcUtil.getConnection();
        PreparedStatement stmt = conn.prepareStatement("select * from t2 where id=1");
        ResultSet rs = stmt.executeQuery();
        if(rs.next()){
            Reader r = rs.getCharacterStream("content");
            //内容保存D盘的1.txt文件中
            Writer w = new FileWriter("d:/1.txt");
            int len = -1;
            char c[] = new char[1024];
            while((len=r.read(c))!=-1){
                w.write(new String(c), 0, len);
            }
            r.close();
            w.close();
        }
        JdbcUtil.release(rs, stmt, conn);
    }

    /*
     create table t3(
        id int,
        content longblob
     );
     */
    //存大文件
    @Test
    public void test3() throws Exception{
        Connection conn = JdbcUtil.getConnection();
        PreparedStatement stmt = conn.prepareStatement("insert into t3 values (?,?)");
        stmt.setInt(1, 1);
        //以流的方式
        InputStream in = new FileInputStream("src/26.jpg");

        stmt.setBinaryStream(2, in, in.available());
        stmt.executeUpdate();
        JdbcUtil.release(null, stmt, conn);
    }

    //取大文件
    @Test
    public void test4() throws Exception{
        Connection conn = JdbcUtil.getConnection();
        PreparedStatement stmt = conn.prepareStatement("select * from t3 where id=1");
        ResultSet rs = stmt.executeQuery();
        if(rs.next()){
            InputStream in = rs.getBinaryStream("content");
            OutputStream out = new FileOutputStream("d:/wife.jpg");

            int len = -1;
            byte b[] = new byte[1024];
            while((len=in.read(b))!=-1){
                out.write(b,0,len);
            }
            in.close();
            out.close();
        }
        JdbcUtil.release(null, stmt, conn);
    }
}

JdbcUtil.java文件：

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

//工具类
public class JdbcUtil {

    private static String driverClass;
    private static String url;
    private static String user;
    private static String password;

    static{
        try {
            ClassLoader cl = JdbcUtil.class.getClassLoader();
            InputStream in = cl.getResourceAsStream("dbcfg.properties");
            Properties props = new Properties();
            props.load(in);
            driverClass = props.getProperty("driverClass");
            url = props.getProperty("url");
            user = props.getProperty("user");
            password = props.getProperty("password");

            Class.forName(driverClass);
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }


    public static Connection getConnection() throws Exception{
        Connection conn = DriverManager.getConnection(url,user, password);
        return conn;
    }
    public static void release(ResultSet rs,Statement stmt,Connection conn){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            stmt = null;
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }
}