大資料的儲存和批量語句執行

package com.itheima.dao;import java.io.File;import java.io.FileNotFoundException;import java.io.FileReader;import java.io.FileWriter;import java.io.Reader;import java.io.Writer;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import org.junit.Test;import com.itheima.util.JDBCUtil;public class D1Dao{//大資料存放區@Testpublic void addT1() throws Exception{Connection conn = null;PreparedStatement pst = null;ResultSet rs = null;File fr = new File("d://1.txt");Reader reader = new FileReader(fr);int len = 0;try{conn = JDBCUtil.getConn();String sql = "insert into t1 values(?,?)";pst = conn.prepareStatement(sql);pst.setInt(1, 1);//clob代表大量的字元資料pst.setCharacterStream(2, reader, (int)fr.length());pst.executeUpdate();} catch (SQLException e){e.printStackTrace();}}}

package com.itheima.dao;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.InputStream;import java.io.OutputStream;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import org.junit.Test;import com.itheima.util.JDBCUtil;public class BlobDao{@Testpublic void addBlob(){Connection conn = null;PreparedStatement pst = null;try{conn = JDBCUtil.getConn();String sql = "insert into t2(id,content) values(?,?)";pst = conn.prepareStatement(sql);pst.setInt(1, 1);InputStream is = new FileInputStream("src/1.jpg");pst.setBinaryStream(2, is,is.available());pst.executeUpdate();} catch (Exception e){e.printStackTrace();}finally{JDBCUtil.closeAll(conn, pst, null);}}@Testpublic void readerBlob(){Connection conn = null;PreparedStatement pst = null;ResultSet rs = null;try{conn = JDBCUtil.getConn();String sql = "select * from t2 where id = ?";pst = conn.prepareStatement(sql);pst.setInt(1, 1);rs = pst.executeQuery();if(rs.next()){//通過prepareStatement方法調用擷取字元流的方法getBinaryStreamInputStream is = rs.getBinaryStream("content");OutputStream fos = new FileOutputStream("d:/2.jpg");byte[] buff = new byte[1024];int len = 0;while((len=is.read(buff))!=-1){fos.write(buff,0,len);}is.close();fos.close();}} catch (Exception e){e.printStackTrace();}finally{JDBCUtil.closeAll(conn, pst, rs);}}}
package com.itheima.dao;
import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;
import javax.transaction.Transaction;
import org.junit.Test;
import com.itheima.util.JDBCUtil;
public class BatchStatement{ /**  * Statement的大量操作:可有有多種操作,增加或者刪除一起執行  * @author simon  *  */ @Test public void addBatch() {  Connection conn = null;  Statement pst = null;    conn = JDBCUtil.getConn();  try  {   String sql1 ="insert into t3 values(1,'你好')";    String sql2 ="insert into t3 values(2,'你好')";    String sql3 ="insert into t3 values(3,'你好')";    String sql4 ="insert into t3 values(4,'你好')";    String sql5 ="insert into t3 values(5,'你好')";    String sql6 ="insert into t3 values(6,'你好')";    String sql7 = "delete from t3 where id= 4";      pst = conn.createStatement();   pst.addBatch(sql1);   pst.addBatch(sql2);   pst.addBatch(sql3);   pst.addBatch(sql4);   pst.addBatch(sql5);   pst.addBatch(sql6);   pst.addBatch(sql7);      int[] is = pst.executeBatch();  } catch (SQLException e)  {   e.printStackTrace();  } } /**  * PreparedStatement只能用於一種形式的大量刪除,比如添加  */ @Test public void batchPreparedStatement() {  Connection conn = null;  PreparedStatement pst = null;  ResultSet rs = null;    conn = JDBCUtil.getConn();  try  {   pst = conn.prepareStatement("insert into t3 values(?,?)");   for(int i=0;i<99;i++)   {    pst.setInt(1, i);    pst.setString(2, "nihao");    pst.executeUpdate();   }     } catch (SQLException e)  {   // TODO Auto-generated catch block   e.printStackTrace();  }  finally  {   JDBCUtil.closeAll(conn, pst, rs);  } }}