java和mysql資料庫大文本和二進位操作

1.   TEXT(CLOB)

MySQL中VARCHAR上限為65535個位元組, 如果超出這個範圍. 需要設定為TEXT.

LONGTEXT最大可以4G

儲存TEXT//定義sql語句String sql="insert into clob(file) value(?)";

PreparedStatement ps =conn.prepareStatement(sql);

File file = new File("res/ClobDemo.java");

Reader reader = new FileReader(file);

ps.setCharacterStream(1, reader,(int) file.length());

ps.executeUpdate();

//最後要關閉流

reader.close();

讀取TEXT

String sql="select file from clob";

PreparedStatement ps =conn.prepareStatement(sql);

ResultSet rs =ps.executeQuery();

if (rs.next()) {

       Reader reader = rs.getCharacterStream(int columnIndex);

       // 這個Reader就是從資料庫中讀取資料的流, 操作這個流來讀取資料

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();

}

2.   BLOB

BLOB用來儲存大段的位元據, 例片, 音頻, 視頻. LONGBLOB最大4G

 儲存BLOB

PreparedStatement ps =conn.prepareStatement("insert intobig_binary(file) values(?)");

File file = new File("src/cn/itcast/jdbc1/clob_blob/IMG_0007.jpg");

InputStream in = newFileInputStream(file);

ps.setBinaryStream(1, in, (int) file.length());

ps.executeUpdate();

 讀取BLOB

PreparedStatement ps =conn.prepareStatement("select filefrom big_binary");

ResultSet rs =ps.executeQuery();

if (rs.next()) {

     
InputStream in = rs.getBinaryStream(1);

     // 這個InputStream就是從資料庫中讀取資料的流, 操作這個流來讀取資料

File file = new File("res/newgirl.jpg");
FileOutputStream fos = new FileOutputStream(file);
int num = in.available();
byte buffer[] = new byte[num];
while(in.read(buffer,0,num) != -1){
fos.write(buffer);
}
System.out.println("左邊");
in.close();
fos.close();

}