实现在mysql数据库中保存text大文本和blob大二进制数据

来源：互联网发布：java数组为空和为null 编辑：程序博客网时间：2024/05/15 11:00

项目代码如下：

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());有三种重载形式