使用JDBC处理大数据 读写文件

在实际开发中，程序需要把大文本或二进制数据保存到数据库。


基本概念：大数据也称之为LOB(Large Objects)，LOB又分为：
clob和blob
clob用于存储大文本。Text
blob用于存储二进制数据，例如图像、声音、二进制文等。


对MySQL而言只有blob，而没有clob，mysql存储大文本采用的是Text，Text和blob分别又分为：
TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT
TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 

------------------读写--------------------------------  字符流读写-----

public class LongTextTest {
public static void main(String[] args) throws IOException {
// write();
read();
}


private static void read() throws IOException {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select * from textdatas where id =1 ";
stmt = conn.prepareStatement(sql);


rs = stmt.executeQuery();
if (rs.next()) {
Reader reader = rs.getCharacterStream("mytext");
Writer out = new FileWriter("dulala_bak.txt");
int temp;
while ((temp = reader.read()) != -1) {
out.write(temp);
}
out.close();
reader.close();
}


} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtils.release(stmt, conn);
}
}


private static void write() throws FileNotFoundException {         //数据写入数据库
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into textdatas values(null,?)";
stmt = conn.prepareStatement(sql);


// 设置动态数据
File file = new File("dulala.txt");
Reader reader = new FileReader(file);
stmt.setCharacterStream(1, reader, (int) file.length());


stmt.executeUpdate();


} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtils.release(stmt, conn);
}
}
}

-

--------------    字节流  读写--------------------------------------------------------------------

public class LongBlobTest {
public static void main(String[] args) throws IOException {
// write();
read();
}


private static void read() throws IOException {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select * from blobdatas where id = 1";
stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
if (rs.next()) {
InputStream in = new BufferedInputStream(rs
.getBinaryStream("myblob"));
OutputStream out = new BufferedOutputStream(
new FileOutputStream("laonanhai.mp3"));
int temp;
while ((temp = in.read()) != -1) {
out.write(temp);
}
out.close();
in.close();
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}


}


private static void write() throws IOException {
Connection conn = null;
PreparedStatement stmt = null;


try {
conn = JDBCUtils.getConnection();
String sql = "insert into blobdatas values(null,?)";
stmt = conn.prepareStatement(sql);
// 设置数据
File file = new File("oldboy.mp3");
InputStream in = new FileInputStream(file);
stmt.setBinaryStream(1, in, (int) file.length());
// 执行
stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(stmt, conn);
}
}
}