org.h2gis.drivers.dbf.DBFDriverFunction Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of h2drivers Show documentation
Show all versions of h2drivers Show documentation
Add H2 read/write support for file formats such as ESRI shape file
/**
* H2GIS is a library that brings spatial support to the H2 Database Engine
* .
*
* H2GIS is distributed under GPL 3 license. It is produced by CNRS
* columnIndexes = new ArrayList();
DbaseFileHeader header = dBaseHeaderFromMetaData(resultSetMetaData, columnIndexes);
if (encoding != null) {
header.setEncoding(encoding);
}
header.setNumRecords(recordCount);
DBFDriver dbfDriver = new DBFDriver();
dbfDriver.initDriver(fileName, header);
Object[] row = new Object[header.getNumFields()];
while (rs.next()) {
int i = 0;
for (Integer index : columnIndexes) {
row[i++] = rs.getObject(index);
}
dbfDriver.insertRow(row);
if (lineProgress != null) {
lineProgress.endStep();
}
}
dbfDriver.close();
} finally {
rs.close();
}
} finally {
st.close();
}
} else {
throw new SQLException("Only .dbf extension is supported");
}
}
@Override
public String getFormatDescription(String format) {
if(format.equalsIgnoreCase("dbf")) {
return DESCRIPTION;
} else {
return "";
}
}
@Override
public IMPORT_DRIVER_TYPE getImportDriverType() {
return IMPORT_DRIVER_TYPE.COPY;
}
@Override
public String[] getImportFormats() {
return new String[] {"dbf"};
}
@Override
public String[] getExportFormats() {
return new String[] {"dbf"};
}
@Override
public void importFile(Connection connection, String tableReference, File fileName, ProgressVisitor progress) throws SQLException, IOException {
importFile(connection, tableReference, fileName, progress, null);
}
/**
* @param connection Active connection, do not close this connection.
* @param tableReference [[catalog.]schema.]table reference
* @param fileName File path to read
* @param progress monitor
* @param forceFileEncoding File encoding to use, null will use the provided file encoding in file header.
* @throws SQLException Table write error
* @throws IOException File read error
*/
public void importFile(Connection connection, String tableReference, File fileName, ProgressVisitor progress,String forceFileEncoding) throws SQLException, IOException {
if (FileUtil.isFileImportable(fileName, "dbf")) {
DBFDriver dbfDriver = new DBFDriver();
dbfDriver.initDriverFromFile(fileName, forceFileEncoding);
final boolean isH2 = JDBCUtilities.isH2DataBase(connection.getMetaData());
String parsedTable = TableLocation.parse(tableReference, isH2).toString(isH2);
try {
DbaseFileHeader dbfHeader = dbfDriver.getDbaseFileHeader();
// Build CREATE TABLE sql request
Statement st = connection.createStatement();
List otherCols = new ArrayList(dbfHeader.getNumFields() + 1);
for (int idColumn = 0; idColumn < dbfHeader.getNumFields(); idColumn++) {
otherCols.add(new Column(dbfHeader.getFieldName(idColumn), 0));
}
String pkColName = FileEngine.getUniqueColumnName(H2TableIndex.PK_COLUMN_NAME, otherCols);
st.execute(String.format("CREATE TABLE %s (" + pkColName + " SERIAL PRIMARY KEY, %s)", parsedTable,
getSQLColumnTypes(dbfHeader, isH2)));
st.close();
try {
PreparedStatement preparedStatement = connection.prepareStatement(
String.format("INSERT INTO %s VALUES (null, %s )", parsedTable,
getQuestionMark(dbfHeader.getNumFields())));
try {
long batchSize = 0;
for (int rowId = 0; rowId < dbfDriver.getRowCount(); rowId++) {
Object[] values = dbfDriver.getRow(rowId);
for (int columnId = 0; columnId < values.length; columnId++) {
preparedStatement.setObject(columnId + 1, values[columnId]);
}
preparedStatement.addBatch();
batchSize++;
if (batchSize >= BATCH_MAX_SIZE) {
preparedStatement.executeBatch();
preparedStatement.clearBatch();
batchSize = 0;
}
}
if (batchSize > 0) {
preparedStatement.executeBatch();
}
} finally {
preparedStatement.close();
}
//TODO create spatial index on the_geom ?
} catch (Exception ex) {
connection.createStatement().execute("DROP TABLE IF EXISTS " + parsedTable);
throw new SQLException(ex.getLocalizedMessage(), ex);
}
} finally {
dbfDriver.close();
}
}
}
private static class DBFType {
char type;
int fieldLength;
int decimalCount;
DBFType(char type, int fieldLength, int decimalCount) {
super();
this.type = type;
this.fieldLength = fieldLength;
this.decimalCount = decimalCount;
}
}
/**
* Generate the concatenation of ? characters. Used by PreparedStatement.
* @param count Number of ? character to generation
* @return Value ex: "?, ?, ?"
*/
public static String getQuestionMark(int count) {
StringBuilder qMark = new StringBuilder();
for (int i = 0; i < count; i++) {
if(i > 0) {
qMark.append(", ");
}
qMark.append("?");
}
return qMark.toString();
}
/**
* Return SQL Columns declaration
* @param header DBAse file header
* @param isH2Database true if H2 database
* @return Array of columns ex: ["id INTEGER", "len DOUBLE"]
* @throws IOException
*/
public static String getSQLColumnTypes(DbaseFileHeader header, boolean isH2Database) throws IOException {
StringBuilder stringBuilder = new StringBuilder();
for(int idColumn = 0; idColumn < header.getNumFields(); idColumn++) {
if(idColumn > 0) {
stringBuilder.append(", ");
}
String fieldName = TableLocation.capsIdentifier(header.getFieldName(idColumn), isH2Database);
stringBuilder.append(TableLocation.quoteIdentifier(fieldName,isH2Database));
stringBuilder.append(" ");
switch (header.getFieldType(idColumn)) {
// (L)logical (T,t,F,f,Y,y,N,n)
case 'l':
case 'L':
stringBuilder.append("BOOLEAN");
break;
// (C)character (String)
case 'c':
case 'C':
stringBuilder.append("VARCHAR(");
// Append size
int length = header.getFieldLength(idColumn);
stringBuilder.append(String.valueOf(length));
stringBuilder.append(")");
break;
// (D)date (Date)
case 'd':
case 'D':
stringBuilder.append("DATE");
break;
// (F)floating (Double)
case 'n':
case 'N':
if ((header.getFieldDecimalCount(idColumn) == 0)) {
if ((header.getFieldLength(idColumn) >= 0)
&& (header.getFieldLength(idColumn) < 10)) {
stringBuilder.append("INT4");
} else {
stringBuilder.append("INT8");
}
} else {
stringBuilder.append("FLOAT8");
}
break;
case 'f':
case 'F': // floating point number
case 'o':
case 'O': // floating point number
stringBuilder.append("FLOAT8");
break;
default:
throw new IOException("Unknown DBF field type " + header.getFieldType(idColumn));
}
}
return stringBuilder.toString();
}
/**
* Create a DBF header from the columns specified in parameter.
* @param metaData SQL ResultSetMetadata
* @param retainedColumns list of column indexes
* @return DbfaseFileHeader instance.
* @throws SQLException If one or more type are not supported by DBF
*/
public static DbaseFileHeader dBaseHeaderFromMetaData(ResultSetMetaData metaData, List retainedColumns) throws SQLException {
DbaseFileHeader dbaseFileHeader = new DbaseFileHeader();
for(int fieldId= 1; fieldId <= metaData.getColumnCount(); fieldId++) {
final String fieldTypeName = metaData.getColumnTypeName(fieldId);
// TODO postgis check field type
if(!fieldTypeName.equalsIgnoreCase("geometry")) {
DBFType dbfType = getDBFType(metaData.getColumnType(fieldId), fieldTypeName, metaData.getColumnDisplaySize(fieldId), metaData.getPrecision(fieldId));
try {
dbaseFileHeader.addColumn(metaData.getColumnName(fieldId),dbfType.type, dbfType.fieldLength, dbfType.decimalCount);
retainedColumns.add(fieldId);
} catch (DbaseFileException ex) {
throw new SQLException(ex.getLocalizedMessage(), ex);
}
}
}
return dbaseFileHeader;
}
private static DBFType getDBFType(int sqlTypeId, String sqlTypeName,int length, int precision) throws SQLException {
switch (sqlTypeId) {
case Types.BOOLEAN:
return new DBFType('l', 1, 0);
case Types.BIT:
return new DBFType('n', Math.min(3, length), 0);
case Types.DATE:
return new DBFType('d', 8, 0);
case Types.DOUBLE:
case Types.FLOAT:
case Types.NUMERIC:
case Types.DECIMAL:
case Types.REAL:
// +1 because Field length is including the decimal separator
return new DBFType('f', Math.min(20, length + 1), Math.min(18,
precision));
case Types.INTEGER:
return new DBFType('n', Math.min(10, length), 0);
case Types.BIGINT:
return new DBFType('n', Math.min(18, length), 0);
case Types.SMALLINT:
return new DBFType('n', Math.min(5, length), 0);
case Types.VARCHAR:
case Types.NCHAR:
case Types.CHAR:
return new DBFType('c', Math.min(254, length), 0);
default:
throw new SQLException("Field type not supported by DBF : " + sqlTypeName);
}
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy