Java 向SQL Server插入檔案資料

標籤：

package sqlserver;import java.util.Date;import java.util.UUID;import java.text.SimpleDateFormat;import java.io.BufferedOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.PrintStream;import java.sql.*;public class sqlserver {static String uuid=null;//////////////////////////////////////////////////////public static void main(String[] args) {FileOutputStream bos = null;try {bos = new FileOutputStream("F:/output.txt");} catch (FileNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}System.setOut(new PrintStream(bos));System.out.println(insertData());//    getData();    }public static Connection MSSQLConnection(){String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; // 載入JDBC驅動String dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName=filesdata"; // 串連伺服器和資料庫testString userName = "sa"; // 預設使用者名String userPwd = "sa"; // 密碼Connection dbConn = null;SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");try {Class.forName(driverName);dbConn = DriverManager.getConnection(dbURL, userName, userPwd);System.out.println(df.format(new Date())+" "+"資料庫連接成功。"); // 如果串連成功return dbConn;    // 控制台輸出Connection// Successful!} catch (Exception e) {e.printStackTrace();}return null;}// read the file and insert into the video table;static String insertData() {int isFilesFound=0;SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");Date date2 = new Date();Connection dbConn = null;dbConn=MSSQLConnection();//UUID uuid = UUID.randomUUID();String f_id = UUID.randomUUID().toString();//用來產生資料庫的主鍵id非常不錯..uuid=f_id;File file = new File("F:/工作檔案/PSD.rar");System.out.println(df.format(new Date())+" "+"正在處理檔案："+file.getName()+" UUID："+uuid);FileInputStream fis = null;try{fis = new FileInputStream(file);isFilesFound=1;} catch(FileNotFoundException e){System.out.println(df.format(new Date())+" "+"未找到檔案。");}if(isFilesFound==1){try {System.out.println(df.format(new Date())+" "+"開始向資料庫寫入檔案。");PreparedStatement ps = dbConn.prepareStatement("Insert into files (f_id,f_name,f_content,f_date) values (?,?,?,?)");ps.setString(1,f_id); ps.setString(2,file.getName());ps.setBinaryStream(3,fis,(int)file.length());ps.setDate(4,new java.sql.Date(date2.getTime()));System.out.println(df.format(new Date())+" "+"正在寫入..。"); ps.executeUpdate(); ps.close(); try{ fis.close(); System.out.println(df.format(new Date())+" "+"檔案已寫入資料表且資料流已關閉。"); }catch(IOException e){ System.out.println(df.format(new Date())+" "+"資料流無法關閉。"); } } catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}try {dbConn.close();System.out.println(df.format(new Date())+" "+"關閉資料庫連接.");} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return df.format(new Date())+" 插入資料完成。"; }// get the data file from database;static String getData(){Connection dbcon = null;SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");try{dbcon = MSSQLConnection();//OutputStream out = new FileOutputStream("d:/1.exe");Statement st = dbcon.createStatement();ResultSet rs = st.executeQuery("select * from files where f_id = ‘f7453b56-92f2-4aa9-8781-6ca85ab3f0ce‘");while(rs.next()){java.io.InputStream fi=rs.getBinaryStream("f_content");File file=new File("d:/1.zip");FileOutputStream fo = new FileOutputStream(file);BufferedOutputStream bo = new BufferedOutputStream(fo);byte[] buff = new byte[1024];System.out.println(df.format(new Date())+" "+"正在從資料庫寫入本地...");while((fi.read(buff))>0){bo.write(buff);}System.out.println(df.format(new Date())+" "+"寫入完畢.");bo.close();fo.close();fi.close();System.out.println(df.format(new Date())+" "+"關閉讀寫流.");}}catch(Exception ex){ex.printStackTrace();}finally{if (dbcon!=null){try{dbcon.close();System.out.println(df.format(new Date())+" "+"關閉資料庫連接.");}catch(Exception ex){ex.printStackTrace();}}dbcon = null;}return null;}//////////////////////////////////////////////////////}

 

Java 向SQL Server插入檔案資料