BCP(Bulk CopyProgram批量数据拷贝工具,下同)是MicrosoftSQL Server和Sybase数据库提供的非常实用的数据导入/导出工具。它方便实用,性能卓越。深得程序员的喜爱。
在项目开发过程中,需要在Java应用里用定时任务实现数据的批量导入/导出。常规的做法是用insert into语句直接插入数据,或者调用BCP命令导入数据。但这两种方法都有其固有的局限性,有没有一种更为方便的方法呢?
思路
数据导出功能较易实现。只要用Statement直接执行SQL语句返回ResultSet,然后将查询结果序列化为字符串后写到文本中去。唯一需要注意的是创建Statement时一定要指定游标类型为只读(CONCUR_READ_ONLY)和前向读取(TYPE_FORWARD_ONLY),否则可能造成内存溢出。
难点是在数据导入。
使用insert into语句的方式插入数据,数据库服务器的负荷将迅速增大,日志会迅速增大甚至溢出。在实时业务系统中运用时,必须要在插入若干条后暂停并清理日志,不仅处理效率低,而且可能造成业务中断。
直接调用BCP命令的前提是要安装和配置Sybase或Microsoft SQL Server(后文中均简写为SQL Server)客户端,并且需要根据数据库类型不同,定位到不同的BCP.exe文件路径,在应用程序中也很难得到数据导入的情况。
有一个叫jBCP的开源项目,作者声称可以将数据从文件导入到 SQL Server和Sybase数据库。经过初步测试,jBCP的导入仅仅能适用于Sybase,且要导入的表至少需要满足下列条件:
1.所有字段不能为空;
2.字段值不能包含中文字符;
3.表不能包含tinyint类型的字段;
也就是说,对于BCP功能,jBCP提供的包并不具备商用条件。
尽管如此,jBCP为我们提供了解决问题的基本思路和方法,那就是直接用TDS协议与数据库进行TCP通信,向服务端发送BCP命令和数据。
数据库客户端和服务端的通信并不像我们想象的那样神秘。我们知道,数据库服务也是作为Socket服务端响应客户端连接请求的。例如在缺省状态下,SQL Server通常的端口号是1433,Oracle的端口号是1521,Sybase的端口号是5000。要跟数据库通信,就得先建立Socket连接,然后使用数据库特有的协议进行消息交互。
对于SQL Server和Sybase,它使用的Socket通信协议是TDS(Tabular Data Stream Protocol,表格化数据流协议,下同)。TDS版本和数据库的版本对应关系如下表:
TDS 版本 |
支持产品及版本 |
4.2 |
Sybase SQL Server < 10 和 Microsoft SQL Server 6.5 |
5.0 |
Sybase SQL Server >= 10 |
7.0 |
Microsoft SQL Server 7.0 |
8.0 |
Microsoft SQL Server 2000 |
9.0 |
Microsoft SQL Server 2005 |
由于Sybase10以下版本和Microsoft SQL Server6.5版本目前较少使用,本文中所指的数据库均以TDS5.0以上版本为准,同时也不针对Sybase10以下版本和Microsoft SQL Server6.5做兼容性测试。
BCP 导出功能jBCP包并没有实现。但bcp导出的过程实际上是执行Select * from table,然后把字段数据根据规则写入文件。我们可以用这个方法自行实现。
在TDS协议中,规定了导入的Bulk Copy Packet的格式。一个典型的BCP导入会话过程如下:
--> 建立连接并认证
<-- 认证结果
--> 声明将要进行BCP操作(insert bulk数据库名..表或视图名)
<-- 字段结构说明
--> 结构化数据
--> 数据结束(TDS7.0+, 即SQL Server才需要)
<-- 导入结果
由此可见,BCP导入的过程实际上是数据库服务端先准备好接收BCP数据,然后由客户端发送二进制的字段数据给服务端,服务端将这些数据快速写入数据库。归纳起来,BCP性能高的原因如下:
1.单向发送数据,避免频繁的请求/响应操作,也减少了网络交互包的数量;
2.服务端不处理SQL语句,避免做语法分析、预编译等操作;
3.服务端不做数据类型映射转换;
4.客户端直接向Socket连接写数据流,避免其它多余环节;
5.对于快速BCP,服务端不产生日志,仅记录页分配。
实践
结合搜集到的BCP资料,