版权声明:尊重博主原创文章,转载请注明出处哦~http://blog.csdn.net/eson_15/article/details/51317001
上一节主要讲了JDBC的基本操作,这一节主要总结一下JDBC如何处理大文本、如何处理图片以及进行批处理。
1.JDBC处理大文本
在MySQL中,大文本是text类型,使用Java操作数据库中的大文本需要两个方法setCharacterStream和getCharacterStream,一个是写入数据库的字符流,一个是从数据库中读取的字符流。setCharacterStream(index,Reader,length)有三个参数,Reader表示获取文件的流,length表示文件的长度,index表示参数的索引。那么获取文件的流Reader如何获得呢?我们可以通过下面方法获取:
- String path = Demo1.class.getClassLoader().getResource("1.txt").getPath(); //获取指定文件的Path
- File file = new File(path); //以这个path为参数构建一个file对象
- Reader reader = new FileReader(file); //以这个file对象为参数构建Reader流,这个流与这个文件就关联了
- create database tes<span style="font-family:Microsoft YaHei;">t</span>;
- use test;
- create table testclob
- (
- id int primary key auto_increment,
- resume text
- );
- public class Demo1 {
- @Test//向数据库中插入一个文本
- public void add() throws FileNotFoundException {
- Connection conn = null;
- PreparedStatement st = null;
- ResultSet rs = null;
- try {
- conn = JDBCUtils.getConnection(); //使用上一节中的JDBCUtils
- String sql = "insert into testclob(resume) values(?)";
- st = conn.prepareStatement(sql); //预处理sql语句
- String path = Demo1.class.getClassLoader().getResource("1.txt").getPath();//在工程src目录下存放一个1.txt文件
- File file = new File(path);
- st.setCharacterStream(1, new FileReader(file), file.length());
- int num = st.executeUpdate(); //执行向数据库中插入
- if(num > 0) {
- System.out.println("插入成功");
- }
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- JdbcUtils.release(conn, st, rs);
- }
- }
- @Test //从数据库中读取文本
- public void read() throws IOException {
- Connection conn = null;
- PreparedStatement st = null;
- ResultSet rs = null;
- try {
- conn = JDBCUtils.getConnection();
- String sql = "select resume from testclob where id=?";
- st = conn.prepareStatement(sql);
- st.setInt(1, 1);
- rs = st.executeQuery(); //执行sql语句
- if(rs.next()){
- Reader reader = rs.getCharacterStream("resume"); //获取字段未resume的项,也就是我们刚刚存到数据库的1.txt文件
- char buffer[] = new char[1024];
- int len = 0;
- FileWriter out = new FileWriter("D:\\1.txt"); //写到D盘下
- while((len = reader.read(buffer)) > 0){
- out.write(buffer, 0, len);
- }
- out.close();
- reader.close();
- }
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- JdbcUtils.release(conn, st, rs);
- }
- }
- }
2. JDBC处理图片
在MySQL中,大文本是image类型,我们现在数据库中建立新的表:
- create table testblob
- (
- id int primary key auto_increment,
- image longblob
- );
- public class Demo2 {
- @Test //向数据库中写入图片
- public void add() throws FileNotFoundException {
- Connection conn = null;
- PreparedStatement st = null;
- ResultSet rs = null;
- try {
- conn = JdbcUtils.getConnection();
- String sql = "insert into testblob(image) values(?)";
- st = conn.prepareStatement(sql);
- String path = Demo2.class.getClassLoader().getResource("honey.jpg").getPath(); //图片存在src目录下
- st.setBinaryStream(1, new FileInputStream(path), new File(path).length());//这里用的是setBinaryStream,字节流
- //这种方法也行,FileInputStream方法重载了,既可以传递路径,也可以传递具体文件
- //st.setBinaryStream(1, new FileInputStream(new File(path)), new File(path).length());
- int num = st.executeUpdate();
- /*这里会有这个错误:
- * Packet for query is too large (4531349 > 1048576).
- * You can change this value on the server by setting the max_allowed_packet' variable.
- * 原因:MySQL的一个系统参数:max_allowed_packet,其默认值为1048576(1M),即允许传递的最大packet为1M,如果照片超过1M无法导入
- * 查询:show VARIABLES like '%max_allowed_packet%';修改此变量的值:set global max_allowed_packet = 1024*1024*10;(10M)
- * */
- if(num > 0){
- System.out.println("插入成功");
- }
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- JdbcUtils.release(conn, st, rs);
- }
- }
- @Test //从数据库中读取图片
- public void read() throws IOException {
- Connection conn = null;
- PreparedStatement st = null;
- ResultSet rs = null;
- try {
- conn = JdbcUtils.getConnection();
- String sql = "select image from testblob where id=?";
- st = conn.prepareStatement(sql);
- st.setInt(1, 1);
- rs = st.executeQuery();
- while(rs.next()){
- InputStream in = rs.getBinaryStream("image");//获取用getBinaryStream,也是字节流
- int len = 0;
- byte buffer[] = new byte[1024];
- FileOutputStream out = new FileOutputStream("D:\\honey.jpg"); //写到D盘下
- while((len = in.read(buffer)) > 0){
- out.write(buffer, 0, len);
- }
- out.close();
- in.close();
- }
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- JdbcUtils.release(conn, st, rs);
- }
- }
- }
JDBC操作图片也比较简单,跟操作大文本差不多。下面看看JDBC如何进行批处理。
3. JDBC进行批处理
首先我们还是先建立一个表:
- create table testbatch
- (
- id int primary key,
- name varchar(20)
- );
- //jdbc进行批处理
- public class Demo3 {
- @Test
- public void testBatch() {
- Connection conn = null;
- Statement st = null;
- ResultSet rs = null;
- try {
- conn = JdbcUtils.getConnection();
- String sql1 = "insert into testbatch(id,name) values(1,'aaa')";
- String sql2 = "insert into testbatch(id,name) values(2,'bbb')";
- String sql3 = "insert into testbatch(id,name) values(3,'ccc')";
- String sql4 = "delete from testbatch where id=1";
- st = conn.createStatement();
- st.addBatch(sql1);
- st.addBatch(sql2);
- st.addBatch(sql3);
- st.addBatch(sql4); //将四条sql语句加入Batch
- st.executeBatch(); //然后依次执行这四条sql语句
- st.clearBatch();//执行完后清除batch
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- JdbcUtils.release(conn, st, rs);
- }
- }
- @Test //大量插入
- public void testBatch2() {
- Connection conn = null;
- PreparedStatement st = null;
- ResultSet rs = null;
- try {
- conn = J<span style="font-family:Microsoft YaHei;">DBC</span>Utils.getConnection();
- String sql = "insert into testbatch(id,name) values(?,?)";
- st = conn.prepareStatement(sql);
- for(int i = 0; i < 10000000; i++){
- st.setInt(1, i);
- st.setString(2, "aa"+i);
- st.addBatch();
- if(i % 1000 == 0){//每1000条向数据库中添加一次
- st.executeBatch();
- st.clearBatch();
- }
- }
- st.executeBatch();//防止还剩一些零头的数据,这里刚好是1000的倍数
- st.clearBatch();
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- JdbcUtils.release(conn, st, rs);
- }
- }
- }
相关阅读:http://blog.csdn.net/column/details/servletandjsp.html
_____________________________________________________________________________________________________________________________________________________
-----乐于分享,共同进步!