前一个星期碰到一个需求,是这样的:
本地系统有一张表,用于存放一种病人文书的记录,类似于一张病人信息登记表,需要将这个一定时间段内的数据,导出成dbf文件
碰到需求的时候,我在像dbf文件是个啥玩意儿,没见过啊。百度一下,哦,原来是数据库的数据文件,而且要数据库的控制文件里有描述信息,数据库实例,才可以加载里面的数据到内存。 所以我就建了个tablespace,以及dbf文件,然后把本地的数据插入这个表空间的一个新表(new)里,每次点击导出,调用存储过程,先清空new(避免护士手贱,经常点啊点的,把表不断扩大,到时候我查数据都卡死),然后把本地的表查出来,插入到new表,ok,告诉客户,你每次都从数据库所在的服务器,oracle的目录里找那个,我新建的dbf文件,拷过去就ok了。 可是。。。。。是不对的,客户说你这个dbf文件,导不进去。
后来,医院那边说,他们是用foxPro打开dbf文件的。ok,我装了一个,装了之后,我把我之前建的dbf文件打开,我嚓,真打不开,报错,“不是一个表”。
我就想,难道一个表也可以导出成一个dbf文件? 我百度。。果然可以,搜了一下找到一个博客,说要用javadbf外来jar包,来导出成dbf。我按照他给的实例,敲了一个。果然可以。
下面是我的代码:
//这里是总的业务逻辑,下面的方法是具体的底层实现
public boolean exportBirthInfoFromDocForm( final Date from,final Date to) throws DBFException{
OutputStream fos = null;
this.docPatientFormDAO.getBirthInfoFromDocForm(from, to);
DBFField[] fields = this.docPatientFormDAO.getDBFFields("BIRTH");
//定义DBFWriter实例用来写DBF文件
DBFWriter writer = new DBFWriter();
//把字段信息写入DBFWriter实例,即定义表结构
try {
writer.setFields(fields);
} catch (DBFException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
writer= this.docPatientFormDAO.addRecordsToWriter(writer,fields);
//定义输出流,并关联的一个文件
try {
fos = new FileOutputStream("C:\\xxxx.dbf");
writer.write(fos);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
try{
fos.close();
}catch(Exception e){}
}
return true;
}
docPatientFormDAO里的<span style="font-family: Arial, Helvetica, sans-serif;">getDBFFields方法:</span>
<span style="font-family: Arial, Helvetica, sans-serif;">public DBFField[] getDBFFields(String tableName) {
<span style="white-space:pre"> </span>// TODO Auto-generated method stub</span>
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="white-space:pre"> </span>//x现获取表的定义
<span style="white-space:pre"> </span>String sql ="select column_name,data_type,data_length from all_tab_columns where table_name=? order by column_name";
<span style="white-space:pre"> </span> List<TableDefinition> list = this.jdbcTemplate.query(sql, new Object[] {tableName}, new RowMapper<TableDefinition>() {
<span style="white-space:pre"> </span> public TableDefinition mapRow(ResultSet rs, int rowNum) throws SQLException {
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>TableDefinition def = new TableDefinition();
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>def.columnName=rs.getString("column_name");
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>def.columnDataType=rs.getString("data_type");
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>def.columnLength=rs.getInt("data_length"); <span style="white-space:pre"> </span>
<span style="white-space:pre"> </span> return def;
<span style="white-space:pre"> </span> }
<span style="white-space:pre"> </span> });</span>
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="white-space:pre"> </span>//然后将表的定义转换成dbf文件里的表头,就是一个个dbfField(其实跟excel差不多)
<span style="white-space:pre"> </span> List<DBFField> fields = new ArrayList<DBFField>();
<span style="white-space:pre"> </span> for(TableDefinition def:list){
<span style="white-space:pre"> </span> DBFField field = new DBFField();</span>
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="white-space:pre"> </span>//这个javadbf文件的jar包,也是个坑货(不过还是值得称道的,毕竟给我解决了一个难题),这个field的name居然有长度限制小于等于10</span>
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="white-space:pre"> </span>//将字段的名称,减去一个字母,此处请原谅我的无能,没办法,需求崔的急。不过我事后想了下,应该这里特殊处理没关系的,我取前十的字母,然后</span>
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="white-space:pre"> </span>//填写dbf的Field的时候,我和表的字段定义比较一下,然后把减去的字母不上,不知道有没有更好的方法
<span style="white-space:pre"> </span> if(def.columnName.equals("CF_JZD_QHDM")){
<span style="white-space:pre"> </span> def.columnName="CF_JZ_QHDM";
<span style="white-space:pre"> </span> }
<span style="white-space:pre"> </span> field.setName(def.columnName);
<span style="white-space:pre"> </span> /**
<span style="white-space:pre"> </span> *
<span style="white-space:pre"> </span>DBFField.FIELD_TYPE_D 表示Date型
<span style="white-space:pre"> </span>DBFField.FIELD_TYPE_L 表示布尔型
<span style="white-space:pre"> </span>DBFField.FIELD_TYPE_N 表示数字
<span style="white-space:pre"> </span>DBFField.FIELD_TYPE_C 表示字符串
<span style="white-space:pre"> </span> */
<span style="white-space:pre"> </span> if(def.columnDataType.endsWith("VARCHAR2")){
<span style="white-space:pre"> </span> field.setDataType(DBFField.FIELD_TYPE_C);
<span style="white-space:pre"> </span> }
<span style="white-space:pre"> </span> if(def.columnDataType.equals("NUMBER")){
<span style="white-space:pre"> </span> field.setDataType(DBFField.FIELD_TYPE_N);
<span style="white-space:pre"> </span> }
<span style="white-space:pre"> </span> field.setFieldLength(def.columnLength);
<span style="white-space:pre"> </span> fields.add(field);
<span style="white-space:pre"> </span> }
<span style="white-space:pre"> </span> return fields.toArray(new DBFField[fields.size()]);
<span style="white-space:pre"> </span>}
</span>
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="white-space:pre"> </span></span><pre name="code" class="html">docPatientFormDAO.addRecordsToWriter(writer,fields)这个方法的实现:
public DBFWriter addRecordsToWriter(DBFWriter writer,DBFField[] fields) throws DBFException{
<span style="white-space:pre"> </span>//每行5个字段
<span style="white-space:pre"> </span>List<DBFField> fieldList = java.util.Arrays.asList(fields);
<span style="white-space:pre"> </span>String sql ="select JG_BCRQ,JG_LXDH,JG_TBR,JG_DWFZR,FM_PF4, "+
<span style="white-space:pre"> </span>" FM_RSJJ4,FM_XB4,FM_PF3,FM_RSJJ3,FM_XB3, "+
<span style="white-space:pre"> </span>"FM_PF2,FM_RSJJ2,FM_XB2,FM_PF1,FM_RSJJ1,"+
<span style="white-space:pre"> </span>"FM_XB1,CF_FMRQ,CF_FMFS,CF_FMDD,CF_GWYS,"+
<span style="white-space:pre"> </span>"CF_CC,CF_YC,CF_JZD_QHDM,CF_JZD_XQ,CF_JZD_SD,"+
<span style="white-space:pre"> </span>"CF_JZD_S,CF_HJ_QHDM,CF_HJ_XQ,CF_HJ_SD,CF_HJ_S,"+
<span style="white-space:pre"> </span>"CF_MZ,CF_GJ,CF_CSRQ,CF_ZJHM,CF_ZJLX,"+
<span style="white-space:pre"> </span>"CF_BAH,CF_JDSJ,CF_BH,CF_XM,JG_ZC,"+
<span style="white-space:pre"> </span>"USERNAME from <span style="white-space:pre"> </span>birth ";
<span style="white-space:pre"> </span> List<BirthForm> list = this.jdbcTemplate.query(sql, new Object[] {}, new RowMapper<BirthForm>() {
<span style="white-space:pre"> </span> public BirthForm mapRow(ResultSet rs, int rowNum) throws SQLException {
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>BirthForm form = getFormFromRS( rs);<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>
<span style="white-space:pre"> </span> return form;
<span style="white-space:pre"> </span> }
<span style="white-space:pre"> </span> });
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span> for(BirthForm form :list){
<span style="white-space:pre"> </span>Object[] rowData = new Object[41];
<span style="white-space:pre"> </span>rowData[0]= form.CF_BAH;
<span style="white-space:pre"> </span>rowData[1]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_BH ;
<span style="white-space:pre"> </span>rowData[2]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_CC ;
<span style="white-space:pre"> </span>rowData[3]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_CSRQ ;
<span style="white-space:pre"> </span>rowData[4]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_FMDD ;
<span style="white-space:pre"> </span>rowData[5]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_FMFS ;
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>rowData[6]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_FMRQ ;
<span style="white-space:pre"> </span>rowData[7]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_GJ;
<span style="white-space:pre"> </span>rowData[8]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_GWYS ;
<span style="white-space:pre"> </span>rowData[9]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_HJ_QHDM ;
<span style="white-space:pre"> </span>rowData[10]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_HJ_S ;
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>rowData[11]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_HJ_SD ;
<span style="white-space:pre"> </span>rowData[12]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_HJ_XQ;
<span style="white-space:pre"> </span>rowData[13]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_JDSJ;
<span style="white-space:pre"> </span>rowData[14]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_JZD_QHDM ;
<span style="white-space:pre"> </span>rowData[15]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_JZD_S ;
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>rowData[16]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_JZD_SD ;
<span style="white-space:pre"> </span>rowData[17]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_JZD_XQ ;
<span style="white-space:pre"> </span>rowData[18]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_MZ ;
<span style="white-space:pre"> </span>rowData[19]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.cf_XM ;
<span style="white-space:pre"> </span>rowData[20]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_YC;
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>rowData[21]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_ZJHM;
<span style="white-space:pre"> </span>rowData[22]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.CF_ZJLX ;
<span style="white-space:pre"> </span>rowData[23]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.FM_PF1 ;
<span style="white-space:pre"> </span>rowData[24]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.FM_PF2;
<span style="white-space:pre"> </span>rowData[25]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.FM_PF3;
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>rowData[26]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.FM_PF4 ;
<span style="white-space:pre"> </span>rowData[27]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.FM_RSJJ1;
<span style="white-space:pre"> </span>rowData[28]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.FM_RSJJ2;
<span style="white-space:pre"> </span>rowData[29]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.FM_RSJJ3 ;
<span style="white-space:pre"> </span>rowData[30]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.FM_RSJJ4 ;
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>rowData[31]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.FM_XB1 ;
<span style="white-space:pre"> </span>rowData[32]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.FM_XB2 ;
<span style="white-space:pre"> </span>rowData[33]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.FM_XB3 ;
<span style="white-space:pre"> </span>rowData[34]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.FM_XB4 ;
<span style="white-space:pre"> </span>rowData[35]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.JG_BCRQ ;<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>rowData[36]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.JG_DWFZR ;
<span style="white-space:pre"> </span>rowData[37]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.JG_LXDH ;
<span style="white-space:pre"> </span>rowData[38]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.JG_TBR ;
<span style="white-space:pre"> </span> rowData[39]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.JG_ZC ;
<span style="white-space:pre"> </span> rowData[40]=<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>form.username ;
<span style="white-space:pre"> </span> writer.addRecord(rowData);
<span style="white-space:pre"> </span> }
<span style="white-space:pre"> </span> return writer;
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>//字段太多,写的我手软,而且要一一对应。不然dbf文件显示的数据就是错的
<span style="white-space:pre"> </span>//写完之后总算可以了,哈哈
给百度点无数个赞,我的无数个问题都是询问百度得知的,话说百度ceo是谁?百度下,哈哈
这个dbf文件是要传到一个上报系统里的,估计他们上报系统正好和我做的相反,他们把dbf文件,转化成数据库的表数据。