今天终于把dbf的到处完全的弄好了,也不出乱码了。
将数据库的表导出为dbf格式文件的方法。
dao层代码:
通过Hibernate的Session绕过hibernate直接使用SQL语言获取列的信息和所有的数据供为javaDbf类提供数据生成dbf文件。
public Map findInfoAndData() throws SQLException {
// TODO Auto-generated method stub
Session session = getHibernateTemplate().getSessionFactory()
.openSession();
Transaction ts = session.beginTransaction();
Connection conn = session.connection();
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("select * from detail");
ResultSetMetaData meta = rs.getMetaData();
// 去掉最后一列importId
int columnCount = meta.getColumnCount(); //表字段的个数
String[] strutName = new String[columnCount]; //字段名字
byte[] strutType = new byte[columnCount]; // 字段类型
int[] strutLength = new int[columnCount]; //字段长度
rs.last();
int itemCount = rs.getRow(); //记录总数
System.out.println("***" + columnCount + "***" + itemCount);
int i = 0;
rs.beforeFirst();
Object[][] data = new Object[itemCount][columnCount];
int k;
for (k = 0; k < columnCount; k++) { //获取表中字段的信息
strutName[i] = meta.getColumnName(k + 1);
strutType[i] = (byte) meta.getColumnType(k + 1);
strutLength[i] = meta.getColumnDisplaySize(k + 1);
if (strutLength[i] > 100) {
strutLength[i] = 100;
}
System.out.println("----导入测试----:" + i + ":" + "strutName:"
+ strutName[i] + "||strutType:" + strutType[i]
+ "||strutLength:" + strutLength[i]);
i++;
}
i = 0;
int j;
// rs.first();
rs.beforeFirst();
// while(!meta.getColumnName(temp).trim().equals("zpxx")){
for (int rr = 0; rr < itemCount && rs.next(); rr++) {
j = 0;
for (k = 0; k < columnCount; k++) { //获取所有数据
data[i][j] = rs.getObject(k + 1);
// System.out.print("第"+i+"行第"+j+"列:"+","+data[i][j]);
j++;
}
i++;
// System.out.println();
// System.out.println();
}
ts.commit();
session.close();
HashMap map = new HashMap(); //用哈希表储存所有的数据
map.put("strutName", strutName);
map.put("strutType", strutType);
map.put("strutLength", strutLength);
map.put("data", data);
return map;
}
struts.xml中的配置信息
<action name="exportDetailDbf" class="dbfOperateAction" method="exportDetailDbf">
<result name="success" type="downloadResult"></result>//自定义result类用于输出文件
</action>
Action类中的代码
获取map中的信息,对信息进行处理,然后生成dbf文件。
public String exportDetailDbf() {
title = "detail.dbf";
if (dbfStream != null)
dbfStream = null;
Map map = detailService.findInfoAndData();
String[] strutName = (String[]) map.get("strutName");
byte[] strutType = (byte[]) map.get("strutType");
int[] strutLength = (int[]) map.get("strutLength");
Object[][] data = (Object[][]) map.get("data");
int columnCount = strutName.length;
int rowCount = detailService.findDetailSize();
try {
ExportDBF.typeMapping(strutType, columnCount, data, rowCount); //数据处理
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
dbfStream = ExportDBF.generateDbfFromArray(strutName, strutType, //dbf生成
strutLength, data);
return SUCCESS;
}
ExportDBF类中的代码:
public static String dbfPath = "d:/";
public static boolean state = true;
/**
* JavaDBF中的字段类型表示和ResultSetMetaData中的字段类型表示应该是不一致的, 这里做一个类型映射和转换即可
*
* @throws UnsupportedEncodingException
*/
public static synchronized void typeMapping(byte[] strutType,
int fieldCount, Object[][] data, int rowCount)
throws UnsupportedEncodingException {
// System.out.println("rowcount:" + rowCount);
for (int i = 0; i < fieldCount; i++) { //将所有的数据进行转换
int count = 0;
switch (strutType[i]) {
case 1: // char
strutType[i] = DBFField.FIELD_TYPE_C;
// System.out.println("测试:1:" + strutType[i] + "||第" + i);
for (int j = 0; j < rowCount; j++) {
if (data[j][i] == null || data[j][i].equals("")) {
data[j][i] = "";
} else {
data[j][i] = (new String(
(data[j][i].toString()).getBytes("GBK")))
.trim(); //以GBK的编码方式提取字节数组然后重新构
} //造字符串
}
// System.out.println("测试:1:");
case 2: // 如果为numeric
strutType[i] = DBFField.FIELD_TYPE_N;
// System.out.println("测试:2:" + strutType[i]);
for (int j = 0; j < rowCount; j++) {
// System.out.println("----测试:----2:" + data[j][i] + ">" + j
// + ">" + i + "||");
if (data[j][i] == null || data[j][i].equals("")) {
data[j][i] = "";
} else {
// System.out.println("测试:2--0:");
data[j][i] = (new String(
(data[j][i].toString()).getBytes("GBK")))
.trim();
// System.out.println("测试:2--1:");
}
}
// System.out.println("测试:2:");
case 4: // integer
case 6: // folat
case 8: // double
case 12:// varchar
strutType[i] = DBFField.FIELD_TYPE_C;
// System.out.println("测试:3:" + strutType[i]);
for (int j = 0; j < rowCount; j++) {
// System.out.println("----测试:----3:" + data[j][i]);
if (data[j][i] == null || data[j][i].equals("")) {
data[j][i] = "";
} else {
data[j][i] = new String(
(data[j][i].toString()).getBytes("GBK")).trim();
System.out.print(data[j][i].toString().length());
}
}
// System.out.println("测试:4:");
case 16: // boolean
case 93:
strutType[i] = DBFField.FIELD_TYPE_C;
// System.out.println("测试:3:" + strutType[i]);
for (int j = 0; j < rowCount; j++) {
// System.out.println("----测试:----3:" + data[j][i]);
if (data[j][i] == null || data[j][i].equals("")) {
data[j][i] = "";
} else {
data[j][i] = new String(
(data[j][i].toString()).getBytes("GBK")).trim();
System.out.print(data[j][i].toString().length());
}
}
default:
strutType[i] = DBFField.FIELD_TYPE_C;
// System.out.println("测试:3:" + strutType[i]);
for (int j = 0; j < rowCount; j++) {
// System.out.println("----测试:----3:" + data[j][i]);
if (data[j][i] == null || data[j][i].equals("")) {
data[j][i] = "";
} else {
data[j][i] = new String(
(data[j][i].toString()).getBytes("GBK")).trim();
System.out.print(data[j][i].toString().length());
}
}
break;
}
}
}
// 写入dbf文件
public static synchronized ByteArrayOutputStream generateDbfFromArray(
String[] strutName, byte[] strutType, int[] strutLength,
Object[][] data) {
ByteArrayOutputStream ba = new ByteArrayOutputStream();
try
{
int fieldCount = strutName.length;
// fieldCount = 27;
int rowCount = data.length;
System.out.println("***" + fieldCount + "***" + rowCount);
DBFField[] fields = new DBFField[fieldCount];
int i = 0;
for (int k = 0; k < fieldCount; k++) {
fields[i] = new DBFField();
String s;
if (strutName[i].length() > 10) {
s = (String) strutName[i].substring(0, 9);
} else
s = strutName[i];
fields[i].setName(s);
fields[i].setDataType(strutType[i]);
// fields[i].setFieldLength(strutLength[i] + 1);//by liukun
// +1中文问题
// fields[i].setFieldLength(strutLength[i] );
// 以下是最新解决办法,如果是奇数,+1,偶数,不加
System.out.println("&&&&&&&&" + strutLength[i]);
if (strutLength[i] < 0)
strutLength[i] = 6; //设置最小的长度为6防止数据显示不完全
if (strutLength[i] < 4)
strutLength[i] = 6;
if (strutLength[i] % 2 == 0)
fields[i].setFieldLength(strutLength[i]);
else
fields[i].setFieldLength(strutLength[i] + 1);
System.out.println(strutName[i] + "," + strutType[i] + ","
+ fields[i].getFieldLength());
i++;
}
DBFWriter writer = new DBFWriter();
writer.setCharactersetName("GBK"); //设置编码字符集为GBK
writer.setFields(fields);
for (i = 0; i < rowCount; i++) {
writer.addRecord(data[i]);
}
writer.write(ba);
return ba;
}
catch (Exception e)
{
state = false;
e.printStackTrace();
return null;
}
}
项目进展:添加了将将数据库数据生成dbf文件,和另外的一些小功能。