實現在mysql資料庫中儲存text大文本和blob大位元據

標籤：style   blog   http   io   os   ar   使用   java   for   

項目代碼如下：

config設定檔：

className=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/lobuser=rootpassword=root

com.itheima.util包下DBUtil.java

package com.itheima.util;import java.io.FileNotFoundException;import java.io.FileReader;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Properties;public class DBUtil {private static Properties properties = null;static {properties = new Properties();try {properties.load(new FileReader(DBUtil.class.getClassLoader().getResource("config.properties").getPath()));} catch (FileNotFoundException e) {e.printStackTrace();throw new RuntimeException(e);} catch (IOException e) {e.printStackTrace();throw new RuntimeException(e);}}public static Connection getConn() {String className = properties.getProperty("className");String url = properties.getProperty("url");String user = properties.getProperty("user");String password = properties.getProperty("password");Connection conn;try {Class.forName(className);conn = DriverManager.getConnection(url, user, password);} catch (SQLException e) {e.printStackTrace();throw new RuntimeException(e);} catch (ClassNotFoundException e) {e.printStackTrace();throw new RuntimeException(e);}return conn;}public static void close (Connection conn, PreparedStatement ps, ResultSet rs) {if(rs != null) {try {rs.close();} catch (SQLException e) {rs = null;}}if(ps != null) {try {ps.close();} catch (SQLException e) {ps = null;}}if(conn != null) {try {conn.close();} catch (SQLException e) {conn = null;}}}}

com.itheima.lob包下TextDemo.java（實現儲存大文本資料）

package com.itheima.lob;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.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import org.junit.Test;import com.itheima.util.DBUtil;public class TextDemo {/* create table textdemo ( id int primary key auto_increment, name varchar(50), content mediumtext );  */@Testpublic void getText(){Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;String sql = "select * from textdemo";try {conn = DBUtil.getConn();ps = conn.prepareStatement(sql);rs = ps.executeQuery();while(rs.next()) {String name = rs.getString(2);Reader reader = rs.getCharacterStream(3);Writer writer = new FileWriter(name);int len = 0;char []data = new char[1024];while((len = reader.read(data)) != -1) {writer.write(data, 0, len);}}} catch (SQLException e) {e.printStackTrace();throw new RuntimeException(e);} catch (IOException e) {e.printStackTrace();throw new RuntimeException(e);} finally {DBUtil.close(conn, ps, rs);}}@Testpublic void addText() {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;String sql = "insert into textdemo values(null,?,?)";try {conn = DBUtil.getConn();ps = conn.prepareStatement(sql);ps.setString(1, "鋼鐵是怎樣煉成.txt");File file = new File("1.txt");ps.setCharacterStream(2, new FileReader(file), (int)file.length());ps.executeUpdate();} catch (SQLException e) {e.printStackTrace();throw new RuntimeException(e);} catch (FileNotFoundException e) {e.printStackTrace();throw new RuntimeException(e);} finally {DBUtil.close(conn, ps, rs);}}}

com.itheima.lob包下BlobDemo.java（實現儲存大位元據）

package com.itheima.lob;import java.io.File;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.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import org.junit.Test;import com.itheima.util.DBUtil;/* create table blobdemo( id int primary key auto_increment, name varchar(50), content blob ); */public class BlobDemo {@Testpublic void getBlob() {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;String sql = "select * from blobdemo";try {conn = DBUtil.getConn();ps = conn.prepareStatement(sql);rs = ps.executeQuery();while(rs.next()) {String name = rs.getString(2);InputStream is = rs.getBlob(3).getBinaryStream();OutputStream os = new FileOutputStream(name);int len = 0;byte[] data = new byte[1024];while((len = is.read(data)) != -1) {os.write(data, 0, len);}}} catch (SQLException e) {e.printStackTrace();throw new RuntimeException(e);} catch (IOException e) {e.printStackTrace();throw new RuntimeException(e);} finally {DBUtil.close(conn, ps, rs);}}@Testpublic void addBlob() {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;String sql = "insert into blobdemo values (null,?,?)";try {conn = DBUtil.getConn();ps = conn.prepareStatement(sql);ps.setString(1, "洛天依.mp3");File file = new File("1.mp3");ps.setBinaryStream(2, new FileInputStream(file), (int)file.length());ps.executeUpdate();} catch (SQLException e) {e.printStackTrace();throw new RuntimeException(e);} catch (FileNotFoundException e) {e.printStackTrace();throw new RuntimeException(e);} finally {DBUtil.close(conn, ps, rs);}}}

需要注意的是：

ps.setCharacterStream(2, new FileReader(file), (int)file.length());有三種重載形式

ps.setBinaryStream(2, new FileInputStream(file), (int)file.length());有三種重載形式

這兩個方法只有第二種重載形式mysql的jar包裡實現了，其餘兩種沒實現，故為抽象方法，所以如果使用這兩種方法會提示抽象方法錯誤

還有就是如果上傳時，提示java.lang.OutOfMemoryError，則說明超出虛擬機器記憶體，這是可以點擊

-Xms64m設定虛擬機器最小記憶體為64M

-Xmx256m設定虛擬機器最大記憶體為256M

註：jdk5.0以前虛擬機器預設的最大記憶體為64M

如果提示com.mysql.jdbc.PacketTooBigException,說明程式和資料庫之間傳送的資料包超過了預設資料包的大小，這時通過可以修改mysql目錄下的設定檔my.ini解決。

只需要在【mysqld】標籤下添加一行max_allowed_packet屬性值就可以了

[mysqld]max_allowed_packet=64M



實現在mysql資料庫中儲存text大文本和blob大位元據