JDBC處理大資料

標籤：bin   txt   null   binary   插入   connect   jpg   result   finally   

1、處理大文本

 1 package com.demo; 2  3 import java.io.File; 4 import java.io.FileNotFoundException; 5 import java.io.FileReader; 6 import java.io.FileWriter; 7 import java.io.IOException; 8 import java.io.Reader; 9 import java.sql.Connection;10 import java.sql.PreparedStatement;11 import java.sql.ResultSet;12 import java.sql.SQLException;13 14 import org.junit.Test;15 16 import com.utils.DButils;17 18 //jdbc存大文本資料19 20 public class Demo1 {21     @Test22     public void insert() throws SQLException, FileNotFoundException{23         Connection con = null;24         PreparedStatement st = null;25         ResultSet result = null;26         try {27             con = DButils.getConnection();28             String sql = "insert into testclob(id,resume) values(?,?)";29             st = con.prepareStatement(sql);30             st.setString(1,"1");31             32             File file = new File("src/1.txt");33             FileReader reader = new FileReader(file);34             35             //設定大文本的列36             st.setCharacterStream(2, reader, file.length());37             int num = st.executeUpdate();38             if(num>0){39                 System.out.println("插入成功");40             }41         }finally{42             DButils.release(con, st, result);43         }44     }45     46     //讀取大文本資料47     @Test48     public void read() throws SQLException, IOException{49         Connection con = null;50         PreparedStatement st = null;51         ResultSet result = null;52         53         try {54             con = DButils.getConnection();55             String sql = "select id,resume from testclob where id=‘1‘";56             st = con.prepareStatement(sql);57             result = st.executeQuery();58             if(result.next()){59                 //String resume = result.getString("resume");不能用String儲存，佔用記憶體過大60                 Reader reader = result.getCharacterStream("resume");61                 FileWriter writer = new FileWriter("c:\\1.text");62                 try{63                     int len = 0;64                     char buffer[] = new char[1024];65                     while((len=reader.read(buffer))>0){66                         writer.write(buffer, 0, len);67                     }68                 }finally{69                     if(reader!=null){70                         reader.close();71                     }72                     writer.close();73                 }74             }75         }finally{76             DButils.release(con, st, result);77         }78     }79     80 }

 

2、處理二進位檔案

 1 package com.demo; 2  3 import java.io.File; 4 import java.io.FileInputStream; 5 import java.io.FileNotFoundException; 6 import java.io.FileOutputStream; 7 import java.io.IOException; 8 import java.io.InputStream; 9 import java.io.OutputStream;10 import java.sql.Connection;11 import java.sql.PreparedStatement;12 import java.sql.ResultSet;13 import java.sql.SQLException;14 15 import org.junit.Test;16 17 import com.utils.DButils;18 19 //jdbc存取二進位檔案20 public class Demo2 {21     @Test22     public void insert() throws SQLException, FileNotFoundException{23         Connection con = null;24         PreparedStatement st = null;25         ResultSet result = null;26         try{27             con = DButils.getConnection();28             String sql = "insert into testblob(id,image) values(?,?)";29             st = con.prepareStatement(sql);30             st.setString(1, "1");31             File file = new File("src/1.jpg");32             FileInputStream in = new FileInputStream(file);33             st.setBinaryStream(2, in, file.length());34             st.executeUpdate();35         }finally{36             DButils.release(con, st, result);37         }38     }39     40     @Test41     public void read() throws SQLException, IOException{42         Connection con = null;43         PreparedStatement st = null;44         ResultSet result = null;45         try{46             con = DButils.getConnection();47             String sql = "select id,image from testblob where id=‘1‘";48             st = con.prepareStatement(sql);49             result = st.executeQuery();50             if(result.next()){51                 InputStream in = result.getBinaryStream("image");52                 OutputStream out = new FileOutputStream("c:\\1.jpg");53                 try{54                      int len = 0;55                      byte[] buffer = new byte[1024];56                      while((len=in.read(buffer))>0){57                          out.write(buffer, 0, len);58                      }59                 }finally{60                     if(in!=null){61                         in.close();62                     }63                     if(out!=null){64                         out.close();65                     }66                 }67             }68         }finally{69             DButils.release(con, st, result);70         }71     }72 }

 

JDBC處理大資料