r/w paradox database by ODBC

hxttParadoxDriver.java

package com.wescon.cv.device.sbc.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class Test {
    // test hxtt.sql.paradox.ParadoxDriver and convert Chinese characters
    public static void main(String[] args) {
        try {
            Class.forName("com.hxtt.sql.paradox.ParadoxDriver");
            // D:/t/projects/3432-16/d200azcz
            Properties properties = new Properties();
            properties.setProperty("charSet", "gbk");
            String url = "jdbc:paradox:/D:/t/projects/3432-16/d200azcz";
            Connection connection = DriverManager.getConnection(url, properties);
            // String url =
            // "jdbc:paradox:/D:/t/projects/3432-16/d200azcz?charSet=gbk";
            // Connection connection = DriverManager.getConnection(url);
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery("select * from wvpai400");
            int i = 1;
            while (rs.next()) {
                try {
                    System.out.println(i + " >>> " + new String(rs.getBytes("descriptor"), "gbk"));
                    i++;
                }
                catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Paradox.java

import java.io.Closeable;
import java.io.IOException;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;

import org.apache.log4j.Logger;
import org.nutz.castor.Castors;
import org.nutz.castor.FailToCastObjectException;
import org.nutz.lang.Lang;

import com.google.common.base.Function;
import com.google.common.base.Joiner;
import com.google.common.base.Preconditions;
import com.google.common.base.Strings;
import com.google.common.collect.Collections2;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.wescon.cv.et0028.s0.ET0028Utils;
import com.wescon.cv.log.LogUtil;

/**
 * 读写 Paradox 数据库
 */
public class Paradox implements Closeable {
    private static final String ANSII_INT_ENCODING = "windows-1252";
    private static final Charset CHARSET_ANSII = Charset.forName(ANSII_INT_ENCODING);
    private static final Charset CHARSET_GBK = Charset.forName("GBK");
    private static final Logger LOGGER = LogUtil.getLog(Paradox.class);
    private static final String ODBC_DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
    private static final String ODBC_URL_FORMAT = "jdbc:odbc:driver={Microsoft Paradox Driver (*.db )};"
                                                  + "DriverID=538;Fil=Paradox 5.x;DefaultDir=%s;"
                                                  + "Dbq=%s;CollatingSequence=ASCII";

    static {
        try {
            Class.forName(ODBC_DRIVER);
        }
        catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        }
    }

    private Connection connection;
    private boolean isDebugEnable = false;

    /**
     * 启用调试日志
     * 
     * @return
     */
    public Paradox setDebugEnabled() {
        this.isDebugEnable = true;
        return this;
    }

    /**
     * 构造函数
     * 
     * @param url
     *            <code>db</code> 文件所在目录
     * @throws SQLException
     */
    public Paradox(String url) throws SQLException {
        Preconditions.checkNotNull(url);
        String connectionString = String.format(ODBC_URL_FORMAT, url, url);
        Properties properties = new Properties();
        properties.setProperty("charSet", ANSII_INT_ENCODING);
        connection = DriverManager.getConnection(connectionString, properties);
    }

    /**
     * 查询
     * 
     * @param sql
     * @param args
     * @return
     * @throws SQLException
     */
    public List<ParadoxRecord> executeQuery(String sql, Object... args) throws SQLException {
        return executeQuery(String.format(sql, args));
    }

    /**
     * 查询
     * 
     * @param sql
     * @return
     * @throws SQLException
     */
    public List<ParadoxRecord> executeQuery(String sql) throws SQLException {
        debug(sql);
        List<ParadoxRecord> records = Lists.newArrayList();
        try (Statement statement = connection.createStatement();) {
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()) {
                records.add(ParadoxRecord.create(rs));
            }
        }
        return records;
    }

    /**
     * 查询指定表达所有记录
     * 
     * @param tbName
     *            表名
     * @return
     * @throws SQLException
     */
    public List<ParadoxRecord> query(String tbName) throws SQLException {
        return query(tbName, null);
    }

    /**
     * 根据查询条件查询指定表
     * 
     * @param tbName
     *            表名
     * @param condition
     *            查询条件
     * @return
     * @throws SQLException
     */
    public List<ParadoxRecord> query(String tbName, Condition condition) throws SQLException {
        Preconditions.checkArgument(!Strings.isNullOrEmpty(tbName));

        Map<String, Object> values = condition == null ? null : condition.values();
        String where = parseWhere(values);
        if (Strings.isNullOrEmpty(where))
            return executeQuery("SELECT * FROM " + tbName);

        List<ParadoxRecord> records = Lists.newArrayList();
        String sql = String.format("SELECT * FROM %s WHERE %s", tbName, where);
        debug(sql);
        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            setValue(statement, values);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                records.add(ParadoxRecord.create(rs));
            }
        }
        return records;
    }

    /**
     * 插入记录
     * 
     * XXX:不能拼 SQL， SB ODBC 驱动不识别GBK/GB2312/UTF8编码
     * 
     * @param tbName
     *            表名
     * @param records
     *            记录
     * @return
     * @throws SQLException
     */
    public boolean insert(String tbName, Collection<ParadoxRecord> records) throws SQLException {
        Map<String, String> columns = columnTypes(tbName);
        List<String> t = Arrays.asList(new String[columns.size()]);
        Collections.fill(t, "?");
        String padding = Joiner.on(",").join(t);
        Collection<String> transform = Collections2.transform(columns.keySet(),
                                                              new Function<String, String>() {

                                                                  @Override
                                                                  public String apply(String input) {
                                                                      return String.format("[%s]",
                                                                                           input);
                                                                  }
                                                              });
        String sql = String.format("INSERT INTO [%s] (%s) VALUES(%s)",
                                   tbName,
                                   Joiner.on(",").join(transform),
                                   padding);
        debug(sql);
        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            for (ParadoxRecord record : records) {
                int i = 1;
                for (Entry<String, String> entry : columns.entrySet()) {
                    String type = entry.getValue();
                    String key = entry.getKey();

                    if (type.equals("Short")) {
                        statement.setShort(i, record.getShort(key));
                    } else if (type.equals("AlphaNumeric")) {
                        String desc = record.getString(key);
                        String encoding = new String(desc.getBytes(CHARSET_GBK), CHARSET_ANSII);
                        statement.setString(i, encoding);
                    } else if (type.equals("Number")) {
                        statement.setFloat(i, record.getFloat(key));
                    } else if (type.equals("Timestamp")) {
                        long time = ET0028Utils.string2Date(record.getString(key)).getTime();
                        statement.setTimestamp(i, new Timestamp(time));
                    } else if (type.equals("Long")) {
                        statement.setInt(i, record.getInt(key));
                    } else if (type.equals("Logical")) {
                        statement.setBoolean(i, Castors.me().castTo(record.get(key), boolean.class));
                    } else {
                        LOGGER.error("unknow type: " + type);
                    }
                    debug("%s >>> %s", i, record.get(key));
                    i++;
                }
                statement.addBatch();
            }
            int[] ids = statement.executeBatch();
            return ids.length == records.size();
        }
    }

    /**
     * 按表名删除
     * 
     * @param tbName
     * @return
     * @throws SQLException
     */
    public boolean remove(String tbName) throws SQLException {
        return remove(tbName, null);
    }

    /**
     * 按表名删除
     * 
     * @param tbName
     * @return
     * @throws SQLException
     */
    public boolean remove(String tbName, Condition condition) throws SQLException {
        Preconditions.checkArgument(!Strings.isNullOrEmpty(tbName));
        String sql;
        Map<String, Object> values = condition == null ? null : condition.values();
        String where = parseWhere(values);
        if (Strings.isNullOrEmpty(where)) {
            sql = String.format("DELETE * FROM [%s]", tbName);
            debug(sql);
            return execute(sql) > 0;
        } else {
            sql = String.format("DELETE * FROM [%s] WHERE %s", tbName, where);

            try (PreparedStatement statement = connection.prepareStatement(sql)) {
                setValue(statement, values);
                return statement.executeUpdate() > 0;
            }
        }
    }

    /**
     * 更新
     * 
     * @param sql
     * @return
     * @throws SQLException
     */
    public int execute(String sql) throws SQLException {
        try (Statement statement = connection.createStatement()) {
            return statement.executeUpdate(sql);
        }
    }

    /**
     * 获取指定表的字段名称及类型
     * 
     * 键大小写敏感，默认为大写
     * 
     * @param tbName
     *            需要查询的表名
     * @return
     * @throws SQLException
     */
    public Map<String, String> columnTypes(String tbName) throws SQLException {
        Map<String, String> map = Maps.newHashMap();
        DatabaseMetaData meta = connection.getMetaData();

        try (ResultSet res = meta.getColumns(null, null, tbName, null);) {
            while (res.next()) {
                String name = res.getString("COLUMN_NAME").toUpperCase();
                String type = res.getString("TYPE_NAME");
                map.put(name, type);
            }
        }

        return map;
    }

    /**
     * 获取指定表的字段名称及记录
     * 
     * 键大小写敏感，默认为大写
     * 
     * @param tbName
     *            表名
     * @return
     * @throws SQLException
     */
    public Map<String, ParadoxRecord> columns(String tbName) throws SQLException {
        Map<String, ParadoxRecord> map = Maps.newHashMap();
        DatabaseMetaData meta = connection.getMetaData();

        try (ResultSet res = meta.getColumns(null, null, tbName, null);) {
            while (res.next()) {
                ParadoxRecord record = ParadoxRecord.create(res);
                String name = record.getString("COLUMN_NAME").toUpperCase();
                map.put(name, record);
            }
        }

        return map;
    }

    @Override
    public void close() throws IOException {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        }
        catch (SQLException e) {
            LOGGER.error(e.getMessage(), e);
        }
    }

    private void debug(String format, Object... args) {
        if (isDebugEnable)
            LOGGER.debug(String.format(format, args));
    }

    /**
     * 解析 SQL WHERE 条件
     * 
     * @param condition
     * @return
     */
    private String parseWhere(Map<String, Object> condition) {
        if (Lang.isEmpty(condition))
            return "";
        Collection<String> transform = Collections2.transform(condition.keySet(),
                                                              new Function<String, String>() {

                                                                  @Override
                                                                  public String apply(String input) {
                                                                      return String.format("[%s]=?",
                                                                                           input);
                                                                  }
                                                              });
        return Joiner.on(" AND ").join(transform);
    }

    /**
     * 根据条件设置 PreparedStatement 中的值
     * 
     * @param statement
     * @param condition
     * @throws FailToCastObjectException
     * @throws SQLException
     * @see PreparedStatement
     */
    private void setValue(PreparedStatement statement, Map<String, Object> condition)
            throws FailToCastObjectException, SQLException {
        int i = 1;
        for (Entry<String, Object> entry : condition.entrySet()) {
            Object value = entry.getValue();
            if (value instanceof Integer) {
                statement.setInt(i, Castors.me().castTo(value, Integer.class));
            } else if (value instanceof String) {
                String desc = Castors.me().castTo(value, String.class);
                String encoding = new String(desc.getBytes(CHARSET_GBK), CHARSET_ANSII);
                statement.setString(i, encoding);
            } else if (value instanceof Boolean) {
                statement.setBoolean(i, Castors.me().castTo(value, Boolean.class));
            } else if (value instanceof Float) {
                statement.setFloat(i, Castors.me().castTo(value, Float.class));
            } else if (value instanceof Short) {
                statement.setShort(i, Castors.me().castTo(value, Short.class));
            } else {
                LOGGER.error("unknow type: " + value.getClass().getName());
            }
            i++;
        }
    }
}

兄弟，找了好久啊，感谢啊！但是少了文件，你可以帮忙把jar包还有相关类发给我吗，感谢啊

我怎么联系你呀

Exception in thread "main" java.sql.SQLException: [Microsoft][ODBC Çý¶¯³ÌÐò¹ÜÀíÆ÷] δ·¢ÏÖÊý¾ÝÔ´Ãû³Æ²¢ÇÒδָ¶¨Ä¬ÈÏÇý¶¯³ÌÐò ，我取了构造器自己连接，就报这个错误，是什么原因呢