sqlserver二进制存储

CREATE TABLE myTable_yq(Document varbinary(max),yq varchar(20)) 

--SELECT @xmlFileName = 'c:\TestXml.xml'

INSERT INTO myTable(Document)
SELECT * FROM
OPENROWSET(BULK N'E:\20110330110932_61311.jpg', SINGLE_BLOB) AS XMLDATA

 

INSERT INTO myTable(Document)
SELECT * FROM
OPENROWSET(BULK N'D:\newviewhigh\serializable.txt', SINGLE_BLOB) AS XMLDATA

select * from myTable

 

select * from myTable_yq

 

 

 

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.viewhigh.base.databaseconnection.entity.DatabaseConnection;
import com.viewhigh.base.databaseconnection.service.IDatabaseConnectionService;
import com.viewhigh.common.util.DBHelper;
import com.viewhigh.base.databaseconnection.service.impl.DatabaseConnectionServiceImpl;

public class InsertBlobTest {
	
	
	
	
	public void insert2zwdt(Connection conn) throws Exception{   
		//IDatabaseConnectionService databaseConnectionService = new DatabaseConnectionServiceImpl();
		//DatabaseConnection targetDatabaseConnection = databaseConnectionService
		//		.getDatabaseConnectionById("40288a5a57d13f0b0157d140652e0003");
		
		//Connection conn = new DBHelper(targetDatabaseConnection)
		
		

    	
    	
    	//ObjectOutputStream oos = new ObjectOutputStream(new FileOutputStream(file));
       // oos.writeObject(user);
        ByteArrayOutputStream bos = new ByteArrayOutputStream(); 
        
        //ObjectInputStream ins = new ObjectInputStream(oos); 
        
        
        byte[] bytes = bos.toByteArray();            
    	InputStream bis = new ByteArrayInputStream(bytes);  
    	
					//.getConnection();
		Statement stmt = conn.createStatement();
		 PreparedStatement pstmt= null;
			conn.setAutoCommit(false);// 取消自动提交
		
        //================================  
        /*Connection conn = getConnection();  
        conn.setAutoCommit(false); // 设置手动提交  
        PreparedStatement pstmt=null; */ 
        File file=new File("D:\\newviewhigh\\serializable.txt");  
        InputStream in = new FileInputStream(file);  
          System.out.println("test---------------->");
        String sql="insert into myTable_yq(Document,yq) "  
                + "values(?,?)";  
          
        pstmt=conn.prepareStatement(sql);  
        pstmt.setBinaryStream(1, bis, file.length()); 
        pstmt.setString(2, "yangqing");
      
          
        pstmt.execute();  
        conn.commit();  
        pstmt.close();  
    }  
	
	
	
	
	
	
	public static void main(String[] args){
		
		
		
		
		String driverName="com.microsoft.sqlserver.jdbc.SQLServerDriver";

		  String dbURL="jdbc:sqlserver://localhost:1438;DatabaseName=ah_datai_yd";

		  String userName="sa";

		  String userPwd="sa";

		  try

		  {

		   Class.forName(driverName);

		   Connection dbConn=DriverManager.getConnection(dbURL,userName,userPwd);

		    System.out.println("连接数据库成功");
		    
		    
		    InsertBlobTest ib = new InsertBlobTest();
			
			try {
				ib.insert2zwdt(dbConn);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		    
		    

		  }

		  catch(Exception e)

		  {

		   e.printStackTrace();

		   System.out.print("连接失败");

		  }    

		 }
	
	
	
	
	
	
		
		
		
	}

　　

 

 

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;

import com.viewhigh.base.databaseconnection.entity.DatabaseConnection;
import com.viewhigh.base.databaseconnection.service.IDatabaseConnectionService;
import com.viewhigh.common.util.DBHelper;
import com.viewhigh.base.databaseconnection.service.impl.DatabaseConnectionServiceImpl;

public class InsertBlob {
	
	
	
	//public void insert2zwdt(String xmlFileName, Long index) throws Exception{     
	public void insert2zwdt(Connection conn) throws Exception{   
		//IDatabaseConnectionService databaseConnectionService = new DatabaseConnectionServiceImpl();
		//DatabaseConnection targetDatabaseConnection = databaseConnectionService
		//		.getDatabaseConnectionById("40288a5a57d13f0b0157d140652e0003");
		
		//Connection conn = new DBHelper(targetDatabaseConnection)
					//.getConnection();
		Statement stmt = conn.createStatement();
		 PreparedStatement pstmt= null;
			conn.setAutoCommit(false);// 取消自动提交
		
        //================================  
        /*Connection conn = getConnection();  
        conn.setAutoCommit(false); // 设置手动提交  
        PreparedStatement pstmt=null; */ 
        File file=new File("D:\\newviewhigh\\serializable.txt");  
        InputStream in = new FileInputStream(file);  
          System.out.println("test---------------->");
        String sql="insert into myTable_yq(Document,yq) "  
                + "values(?,?)";  
          
        pstmt=conn.prepareStatement(sql);  
        pstmt.setBinaryStream(1, in, file.length()); 
        pstmt.setString(2, "yangqing");
       /* pstmt.setInt(1, index.intValue());  
        pstmt.setString(2, exData.getStSource());  
        pstmt.setString(3, exData.getStKey());  
        pstmt.setString(4, exData.getStVersion());  
        pstmt.setString(5, exData.getStValue());  
        pstmt.setString(6, exData.getStOp());  
        pstmt.setString(7, exData.getStMemo());  
        pstmt.setBinaryStream(8, in, file.length());  
        pstmt.setString(9, exData.getStDest());  */
          
        pstmt.execute();  
        conn.commit();  
        pstmt.close();  
    }  
	
	
	
	/*public void insert2own(ExData exData, String xmlFileName, Long index) throws Exception{  
        File file=new File(xmlFileName);  
        InputStream in = new FileInputStream(file);  
          
        DataSending dataSending = new DataSending();  
        dataSending.setNmSeqId(index.intValue());  
        dataSending.setStSource(exData.getStSource());  
        dataSending.setStKey(exData.getStKey());  
        dataSending.setStVersion(exData.getStVersion());  
        dataSending.setStValue(exData.getStValue());  
        dataSending.setStOp(exData.getStOp());  
        dataSending.setStMemo(exData.getStMemo());  
        dataSending.setSendDate(new Date());   
        byte[] data=new byte[]{};  
        data=inputStreamToByte(in);  
        dataSending.setBlData(data);  
        dataSending.setStDest(exData.getStDest());   
        dataSendingDao.save(dataSending);  
          
    }  */
	
	
	/*private byte[] inputStreamToByte(InputStream in) throws IOException{  
        ByteArrayOutputStream baos=new ByteArrayOutputStream();  
        int ch;  
        while((ch=in.read())!=-1){  
            baos.write(ch);  
        }  
        byte[] data=baos.toByteArray();  
        baos.close();  
        return data;  
    }  */
	
	
	public static void main(String[] args){
		
		/*InsertBlob ib = new InsertBlob();
		
		try {
			ib.insert2zwdt();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}*/
		
		
		
		
		
		String driverName="com.microsoft.sqlserver.jdbc.SQLServerDriver";

		  String dbURL="jdbc:sqlserver://localhost:1438;DatabaseName=ah_datai_yd";

		  String userName="sa";

		  String userPwd="sa";

		  try

		  {

		   Class.forName(driverName);

		   Connection dbConn=DriverManager.getConnection(dbURL,userName,userPwd);

		    System.out.println("连接数据库成功");
		    
		    
		    InsertBlob ib = new InsertBlob();
			
			try {
				ib.insert2zwdt(dbConn);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		    
		    

		  }

		  catch(Exception e)

		  {

		   e.printStackTrace();

		   System.out.print("连接失败");

		  }    

		 }
		
		
		
	}

　　

转载于:https://www.cnblogs.com/8899man/p/6489115.html