Last active
August 29, 2015 14:20
-
-
Save gythialy/e5656990daf007b4b051 to your computer and use it in GitHub Desktop.
r/w paradox database by ODBC
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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(); | |
} | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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++; | |
} | |
} | |
} |
Exception in thread "main" java.sql.SQLException: [Microsoft][ODBC Çý¶¯³ÌÐò¹ÜÀíÆ÷] δ·¢ÏÖÊý¾ÝÔ´Ãû³Æ²¢ÇÒδָ¶¨Ä¬ÈÏÇý¶¯³ÌÐò ,我取了构造器自己连接,就报这个错误,是什么原因呢
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
我怎么联系你呀