大数据的存储和批量语句执行

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
{
	//大数据存储
	@Test
	public 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
{
	@Test
	public 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);
		}
	}
	@Test
	public 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方法调用获取字符流的方法getBinaryStream
				InputStream 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);
  }
 }
}