Mysql中大数据类型的存取

标准SQL中提供了八种大数据类型

上面四种是针对字节数据（二进制字符串类型，主要存储图片、音频信息等），下面四种是针对字符数据（非二进制字符串类型，纯文本文件）。

MySql中不使用标准SQL中针对字符数据的类型，而是使用

下面实现对mp3的存放，首先建一个表music

这里数据类型使用mediumblob类型，使用JDBC进行存储

 

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.rowset.serial.SerialBlob;

import org.apache.commons.io.IOUtils;
import org.junit.Test;


public class InsertBlog {
    /**
     * @throws SQLException
     * @throws FileNotFoundException
     * @throws IOException
     * @throws ClassNotFoundException
     * 从配置文件dbconfig.properties中读取JDBC连接四大参数
     * 使用IOutils包，需要导包
     */
    @Test
    
    public  void insert() throws SQLException, FileNotFoundException,
            IOException, ClassNotFoundException {

        InputStream in = InsertBlog.class.getClassLoader().getResourceAsStream(
                "dbconfig.properties");
        Properties pro = new Properties();
        pro.load(in);
        String url = pro.getProperty("url");
        String name = pro.getProperty("name");
        String password = pro.getProperty("password");
        String drivername = pro.getProperty("DriverClassName");
        System.out.println(url + "" + name + "" + password + "" + drivername);
        Class.forName(drivername);

        Connection conn = DriverManager.getConnection(url, name, password);
        String sql = "insert into music values(?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, 1);
        ps.setString(2, "你还要我怎样");
        System.out.println(111);
        byte[] bytes = IOUtils
                .toByteArray(new FileInputStream("E:/你还要我怎样.mp3"));
        System.out.println(111);
        Blob blob = new SerialBlob(bytes);
        ps.setBlob(3, blob);
        ps.executeUpdate();
    }
    
     @Test
    public void find() throws IOException, ClassNotFoundException, SQLException {
         InputStream in = InsertBlog.class.getClassLoader()
                    .getResourceAsStream("dbconfig.properties");
            Properties pro = new Properties();
            pro.load(in);
            String url = pro.getProperty("url");
            String name = pro.getProperty("name");
            String password = pro.getProperty("password");
            String drivername = pro.getProperty("DriverClassName");
            System.out.println(url + "" + name + "" + password + ""
                    + drivername);
            Class.forName(drivername);

            Connection conn = DriverManager.getConnection(url, name, password);
            String sql = "Select * from music";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs=ps.executeQuery();
            if(rs.next()){
                Blob blob=rs.getBlob("data");
                InputStream n=blob.getBinaryStream();
                OutputStream out=new FileOutputStream("E:/大头鬼.mp3");
                 IOUtils.copy(n,out);
            }
    }

    
}