首页 > 代码库 > JDBC学习笔记：用JDBC处理大段数据
JDBC学习笔记：用JDBC处理大段数据
1.数据库——创建数据表
1 create table clob_test2 (3 id integer not null auto_increment primary key,4 big_text text not null5 );
2.用JDBC处理大段文本数据
(1)将文本数据写入数据库
1 @Test 2 public void create() throws SQLException, IOException { 3 Connection conn = null; 4 PreparedStatement ps = null; 5 ResultSet rs = null; 6 try { 7 // 建立连接 8 conn = JdbcUtils.getConnection(); 9 String sql = "insert into clob_test(big_text) values(?)";10 ps = conn.prepareStatement(sql);11 File file = new File("src/cn/itcast/jdbc/JdbcUtils.java");12 Reader reader = new BufferedReader(new FileReader(file));13 ps.setCharacterStream(1, reader, (int)file.length());14 15 int i = ps.executeUpdate();16 reader.close();17 System.out.println("i = " + i);18 } finally {19 JdbcUtils.free(rs, ps, conn);20 }21 }
(2)从数据库中读出文本数据
1 @Test 2 public void read() throws SQLException, IOException { 3 Connection conn = null; 4 Statement st = null; 5 ResultSet rs = null; 6 try { 7 conn = JdbcUtils.getConnection(); 8 st = conn.createStatement(); 9 rs = st.executeQuery("select big_text from clob_test");10 11 while (rs.next()) {12 Clob clob = rs.getClob(1);13 Reader reader = clob.getCharacterStream();14 // reader = rs.getCharacterStream(1)15 16 //将从数据库中读取的大段文本数据写入到JdbcUtils_bak.java文件中17 File file = new File("JdbcUtils_bak.java");18 Writer writer = new BufferedWriter(new FileWriter(file));19 char[] buff = new char[1024];20 21 for (int i = 0; (i=reader.read(buff)) > 0;) {22 writer.write(buff, 0, i);23 }24 writer.close();25 reader.close();26 }27 } finally {28 JdbcUtils.free(rs, st, conn);29 }30 }
(3)将二进制类型的数据写入数据库
1 @Test 2 public void create() throws SQLException, IOException { 3 Connection conn = null; 4 PreparedStatement ps = null; 5 ResultSet rs = null; 6 try { 7 conn = JdbcUtils.getConnection(); 8 String sql = "insert into blob_test(big_bit) values(?)"; 9 ps = conn.prepareStatement(sql);10 File file = new File("14.jpg");// 文件放在根目录下,大小不能超过64k11 InputStream in = new BufferedInputStream(new FileInputStream(file));12 13 ps.setBinaryStream(1, in, (int)file.length());14 15 int i = ps.executeUpdate();16 in.close();17 System.out.println("i = " + i);18 } finally {19 JdbcUtils.free(rs, ps, conn);20 }21 }
(4)从数据库读取二进制类型的数据
1 @Test 2 public void read() throws SQLException, IOException { 3 Connection conn = null; 4 Statement st = null; 5 ResultSet rs = null; 6 try { 7 conn = JdbcUtils.getConnection(); 8 st = conn.createStatement(); 9 rs = st.executeQuery("select big_bit from blob_test");10 11 while(rs.next()) {12 InputStream in = rs.getBinaryStream(1);13 14 File file = new File("14_bak.jpg");15 OutputStream out = new BufferedOutputStream(new FileOutputStream(file));16 byte[] buff = new byte[1024];17 for (int i = 0; (i = in.read(buff)) > 0;) {18 out.write(buff, 0, i);19 }20 out.close();21 in.close();22 }23 } finally {24 JdbcUtils.free(rs, st, conn);25 }26 }
@Test
public void read() throws SQLException, IOException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
rs = st.executeQuery("select big_bit from blob_test");
while(rs.next()) {
InputStream in = rs.getBinaryStream(1);
File file = new File("14_bak.jpg");
OutputStream out = new BufferedOutputStream(new FileOutputStream(file));
byte[] buff = new byte[1024];
for (int i = 0; (i = in.read(buff)) > 0;) {
out.write(buff, 0, i);
}
out.close();
in.close();
}
} finally {
JdbcUtils.free(rs, st, conn);
}
}
JDBC学习笔记：用JDBC处理大段数据