1.1. TEXT(CLOB)
MySQL中VARCHAR上限为65535个字节, 如果超出这个范围. 需要设置为TEXT.
LONGTEXT最大可以4G
1.1.1. 存储TEXT
PreparedStatement ps = conn.prepareStatement("insert into clob(file) values(?)");
File file = new File("src/cn/itcast/jdbc1/clob_blob/ClobDemo.java");
Reader reader = new FileReader(file);
ps.setCharacterStream(1, reader, (int) file.length());
ps.executeUpdate()
也可以使用ps.setString()直接设置, 但如果字符串太大, 有可能超出虚拟机内存
1.1.2. 读取TEXT
PreparedStatement ps = conn.prepareStatement("select file from clob");
ResultSet rs = ps.executeQuery();
if (rs.next()) {
Reader reader = rs.getCharacterStream(int columnIndex);
// 这个Reader就是从数据库中读取数据的流, 操作这个流来读取数据
}
也可以使用rs.getString(), 在字符串太大的情况下, 同样有可能超出虚拟机内存
import java.io.*; import java.sql.*; public class TestTextField { public TestTextField() throws IOException { Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; try { con = DriverManager.getConnection("jdbc:mysql://localhost/loginDb", "root", "admin"); String sql = "insert into text(introduce) values(?);"; pstmt = con.prepareStatement(sql); File f = new File("res/introduce.txt"); // File f = new File("src/T1.java"); FileReader fr = new FileReader(f); pstmt.setCharacterStream(1, fr, (int) f.length()); pstmt.executeUpdate(); sql = "select * from text"; rs = pstmt.executeQuery(sql); File newFile = new File("res/new.txt"); if (rs.next()) { Reader r = rs.getCharacterStream(2); BufferedReader br = new BufferedReader(r); BufferedWriter bw = new BufferedWriter(new FileWriter( "res/new.txt")); String s = ""; while ((s = br.readLine()) != null) { bw.write(s + "\n"); } bw.flush(); br.close(); r.close(); bw.close(); } } catch (SQLException e) { e.printStackTrace(); } finally { try { rs.close(); pstmt.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void main(String[] args) { try { new TestTextField(); } catch (IOException e) { e.printStackTrace(); } } } |
1.2. BLOB
BLOB用来存储大段的二进制数据, 例如图片, 音频, 视频. LONGBLOB最大4G
1.2.1. 存储BLOB
PreparedStatement ps = conn.prepareStatement("insert into big_binary(file) values(?)");
File file = new File("src/cn/itcast/jdbc1/clob_blob/IMG_0007.jpg");
InputStream in = new FileInputStream(file);
ps.setBinaryStream(1, in, (int) file.length());
ps.executeUpdate();
1.2.2. 读取BLOB
PreparedStatement ps = conn.prepareStatement("select file from big_binary");
ResultSet rs = ps.executeQuery();
if (rs.next()) {
InputStream in = rs.getBinaryStream(1);
// 这个InputStream就是从数据库中读取数据的流, 操作这个流来读取数据
}