使用JSP从SQL Server 2008中检索二进制数据[varbinary(max)] [英] Retrieving binary data [varbinary(max)] from SQL Server 2008 using JSP

问题描述

这是我用来在SQL Server 2008中创建和插入二进制数据的查询.
创建查询:

Here is the query I am using to create and insert binary data in SQL Server 2008
Create query:

CREATE TABLE Employees (
Id int,
Photo varbinary(max) not null,
Name varchar(50) not null,
Sound varbinary(max) not null
)

插入查询:

插入员工SELECT'10'，
(选择BulkColumn AS E FROM OPENROWSET(BULK'd:\ 1.jpg'， Single_Blob)bc)，"John"，(选择 从OPENROWSET(散装)获得的BulkColumn AS E 'd:\ 2.wav'，Single_Blob)bc)

INSERT INTO Employees SELECT '10',
(SELECT BulkColumn AS E FROM OPENROWSET ( BULK 'd:\1.jpg', Single_Blob) bc), 'John', (SELECT BulkColumn AS E FROM OPENROWSET ( BULK 'd:\2.wav', Single_Blob) bc)

其中一个文件是 .jpg ，另一个文件是 .wav
检索时如何知道这些文件的扩展名?
我必须使用查询来找到扩展名吗?
或
在jsp中获得结果集后，我是否必须查看内容类型?

One of the files is .jpg and the other is .wav
How can i know the extension of these files while retrieving?
Do i have to use a query for finding the extension?
OR
Do i have to see content-type after i get the resultset in jsp?

推荐答案

您需要在数据库表中添加另一列用于内容类型或文件名/扩展名.这样，您可以将其插入二进制数据中，然后再取回.

You need to add another column for the content type or file name/extension to the DB table. This way you can just insert it along the binary data and retrieve back later.

在Servlet中，您可以根据文件名/扩展名获取内容类型，如下所示:

In a Servlet, you can get the content type based on the file name/extension as follows:

String contentType = getServletContext().getMimeType(filename);
// ...

如果在插入之前执行此操作，则可以一起存储内容类型.如果在插入后执行此操作，则应将文件名存储在其中.

If you do this before insert, then you can store the content type along. If you do this after insert, then you should store the filename along.

来自servlet上下文的默认mime类型在servlet容器的web.xml中定义，例如在Tomcat位于其/conf/web.xml中的情况下.您也可以在自己的/WEB-INF/web.xml中添加其他mime类型，例如

The default mime types from the servlet context are definied in servletcontainer's web.xml, which is in case of for example Tomcat located in its /conf/web.xml. You can add another mime types to your own /WEB-INF/web.xml as well, e.g.

<mime-mapping>
    <extension>svg</extension>
    <mime-type>image/svg-xml</mime-type>
</mime-mapping>

另请参见:

• 如何检查上传的文件是否是图像?

See also:

• How to check an uploaded file whether it is an image or not?

这篇关于使用JSP从SQL Server 2008中检索二进制数据[varbinary(max)]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！