Revision 31889 branches/v2_0_0_prep/extensions/org.gvsig.oracle/src/org/gvsig/fmap/dal/store/oracle/OracleHelper.java
| OracleHelper.java | ||
|---|---|---|
| 34 | 34 |
import java.sql.SQLException; |
| 35 | 35 |
import java.sql.Statement; |
| 36 | 36 |
import java.util.ArrayList; |
| 37 |
import java.util.Arrays; |
|
| 37 | 38 |
import java.util.Iterator; |
| 38 | 39 |
import java.util.List; |
| 39 | 40 |
|
| ... | ... | |
| 41 | 42 |
import oracle.sql.Datum; |
| 42 | 43 |
import oracle.sql.STRUCT; |
| 43 | 44 |
|
| 44 |
import org.apache.commons.dbcp.DelegatingConnection; |
|
| 45 |
import org.apache.commons.dbcp.PoolingDataSource; |
|
| 46 | 45 |
import org.cresques.cts.IProjection; |
| 47 | 46 |
import org.gvsig.fmap.crs.CRSFactory; |
| 48 | 47 |
import org.gvsig.fmap.dal.DALLocator; |
| ... | ... | |
| 58 | 57 |
import org.gvsig.fmap.dal.feature.FeatureQuery; |
| 59 | 58 |
import org.gvsig.fmap.dal.feature.FeatureSet; |
| 60 | 59 |
import org.gvsig.fmap.dal.feature.FeatureStore; |
| 60 |
import org.gvsig.fmap.dal.feature.FeatureType; |
|
| 61 | 61 |
import org.gvsig.fmap.dal.feature.exception.UnsupportedDataTypeException; |
| 62 | 62 |
import org.gvsig.fmap.dal.feature.exception.UnsupportedGeometryException; |
| 63 |
import org.gvsig.fmap.dal.resource.exception.AccessResourceException; |
|
| 63 |
import org.gvsig.fmap.dal.feature.impl.DefaultEditableFeatureAttributeDescriptor; |
|
| 64 |
import org.gvsig.fmap.dal.feature.impl.DefaultEditableFeatureType; |
|
| 65 |
import org.gvsig.fmap.dal.feature.impl.DefaultFeatureType; |
|
| 64 | 66 |
import org.gvsig.fmap.dal.resource.spi.ResourceManagerProviderServices; |
| 65 | 67 |
import org.gvsig.fmap.dal.store.jdbc.JDBCHelper; |
| 66 | 68 |
import org.gvsig.fmap.dal.store.jdbc.JDBCHelperUser; |
| ... | ... | |
| 69 | 71 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCExecuteSQLException; |
| 70 | 72 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException; |
| 71 | 73 |
import org.gvsig.fmap.geom.Geometry; |
| 72 |
import org.gvsig.fmap.geom.GeometryLocator; |
|
| 74 |
import org.gvsig.fmap.geom.Geometry.SUBTYPES; |
|
| 75 |
import org.gvsig.fmap.geom.Geometry.TYPES; |
|
| 73 | 76 |
import org.gvsig.fmap.geom.primitive.Envelope; |
| 74 | 77 |
import org.gvsig.fmap.geom.primitive.Point; |
| 75 | 78 |
import org.gvsig.fmap.geom.primitive.impl.Envelope2D; |
| ... | ... | |
| 87 | 90 |
*/ |
| 88 | 91 |
public class OracleHelper extends JDBCHelper {
|
| 89 | 92 |
|
| 90 |
private static final double ORACLE_SPATIAL_DEFAULT_TOLERANCE = 0.0005;;
|
|
| 93 |
private static final double ORACLE_SPATIAL_DEFAULT_TOLERANCE = 0.0005; |
|
| 91 | 94 |
|
| 95 |
private static final String IDENTIFIER_QUOTE_STRING = "\""; |
|
| 96 |
|
|
| 92 | 97 |
private static Logger logger = LoggerFactory.getLogger(OracleHelper.class); |
| 93 | 98 |
|
| 94 | 99 |
private boolean tableHasSrid = true; |
| ... | ... | |
| 449 | 454 |
} |
| 450 | 455 |
|
| 451 | 456 |
/** |
| 452 |
* Get oracle srid from srs code(EPSG code) |
|
| 453 | 457 |
* |
| 454 |
* @param epsg |
|
| 455 |
* @return oraSRID |
|
| 456 | 458 |
*/ |
| 457 |
public int getProviderSRID(String epsg) {
|
|
| 458 |
|
|
| 459 |
DataManager manager = DALLocator.getDataManager(); |
|
| 460 |
Integer isrs = null; |
|
| 461 |
if (epsg != null) {
|
|
| 462 |
FeatureStore oraSrsStore = (FeatureStore) OracleLibrary |
|
| 463 |
.getSRSDataStore(); |
|
| 464 |
|
|
| 465 |
FeatureSet set = null; |
|
| 466 |
try {
|
|
| 467 |
FeatureQuery query = oraSrsStore.createFeatureQuery(); |
|
| 468 |
query.setFilter(manager.createExpresion("EPSG = " + epsg));
|
|
| 469 |
set = (FeatureSet) oraSrsStore.getDataSet(query); |
|
| 470 |
if (set.getSize() > 0) {
|
|
| 471 |
Iterator<Feature> it = set.iterator(); |
|
| 472 |
while (it.hasNext()) {
|
|
| 473 |
Feature feat = it.next(); |
|
| 474 |
Double ora = feat.getDouble("ORACLE");
|
|
| 475 |
int iora = ora.intValue(); |
|
| 476 |
double prefe = feat.getDouble("PRF_ORACLE");
|
|
| 477 |
if (prefe == 1) {
|
|
| 478 |
isrs = new Integer(iora); |
|
| 479 |
break; |
|
| 480 |
} |
|
| 481 |
} |
|
| 482 |
} |
|
| 483 |
} catch (DataException e) {
|
|
| 484 |
e.printStackTrace(); |
|
| 485 |
} |
|
| 486 |
if (isrs != null) {
|
|
| 487 |
return isrs; |
|
| 488 |
} |
|
| 489 |
} |
|
| 490 |
return -1; |
|
| 491 |
} |
|
| 492 |
|
|
| 493 |
/** |
|
| 494 |
* Get EPSG SRS from Oracle SRS |
|
| 495 |
* |
|
| 496 |
* @param oraSRID |
|
| 497 |
* @return |
|
| 498 |
*/ |
|
| 499 |
public int getProviderEPSG(String oraSRID) {
|
|
| 500 |
|
|
| 501 |
DataManager manager = DALLocator.getDataManager(); |
|
| 502 |
Integer isrs = null; |
|
| 503 |
if (oraSRID != null) {
|
|
| 504 |
FeatureStore oraSrsStore = (FeatureStore) OracleLibrary |
|
| 505 |
.getSRSDataStore(); |
|
| 506 |
|
|
| 507 |
FeatureSet set = null; |
|
| 508 |
try {
|
|
| 509 |
FeatureQuery query = oraSrsStore.createFeatureQuery(); |
|
| 510 |
query.setFilter(manager.createExpresion("ORACLE = " + oraSRID));
|
|
| 511 |
set = (FeatureSet) oraSrsStore.getDataSet(query); |
|
| 512 |
if (set.getSize() > 0) {
|
|
| 513 |
Iterator<Feature> it = set.iterator(); |
|
| 514 |
while (it.hasNext()) {
|
|
| 515 |
Feature feat = it.next(); |
|
| 516 |
Double ora = feat.getDouble("EPSG");
|
|
| 517 |
int iora = ora.intValue(); |
|
| 518 |
isrs = new Integer(iora); |
|
| 519 |
} |
|
| 520 |
} |
|
| 521 |
} catch (DataException e) {
|
|
| 522 |
e.printStackTrace(); |
|
| 523 |
} |
|
| 524 |
if (isrs != null) {
|
|
| 525 |
return isrs; |
|
| 526 |
} |
|
| 527 |
} |
|
| 528 |
return -1; |
|
| 529 |
} |
|
| 530 |
|
|
| 531 |
/** |
|
| 532 |
* Get Oracle SRS from gvSIG projection |
|
| 533 |
* |
|
| 534 |
* @param proj |
|
| 535 |
* @return |
|
| 536 |
*/ |
|
| 537 |
public int getProviderSRID(IProjection proj) {
|
|
| 538 |
if (proj != null) {
|
|
| 539 |
String epsg = proj.getAbrev().trim(); |
|
| 540 |
int ocu = epsg.indexOf(":");
|
|
| 541 |
if (ocu != -1) {
|
|
| 542 |
epsg = epsg.substring(ocu + 1); |
|
| 543 |
} |
|
| 544 |
Integer oraSRID = getProviderSRID(epsg); |
|
| 545 |
if (oraSRID != null) {
|
|
| 546 |
return oraSRID.intValue(); |
|
| 547 |
} |
|
| 548 |
} |
|
| 549 |
return -1; |
|
| 550 |
} |
|
| 551 |
|
|
| 552 |
/** |
|
| 553 |
* |
|
| 554 |
*/ |
|
| 555 | 459 |
public String getSqlFieldName(FeatureAttributeDescriptor attribute) {
|
| 556 |
// TODO
|
|
| 460 |
/*
|
|
| 557 | 461 |
if (attribute.getDataType() == DataTypes.GEOMETRY) {
|
| 558 | 462 |
return "asBinary(" + super.getSqlFieldName(attribute) + ")";
|
| 559 | 463 |
} |
| 464 |
*/ |
|
| 560 | 465 |
return super.getSqlFieldName(attribute); |
| 561 | 466 |
} |
| 467 |
|
|
| 468 |
protected String getIdentifierQuoteString() {
|
|
| 469 |
return IDENTIFIER_QUOTE_STRING; |
|
| 470 |
} |
|
| 562 | 471 |
|
| 563 | 472 |
/** |
| 564 | 473 |
* |
| ... | ... | |
| 799 | 708 |
public void loadFeatureType(EditableFeatureType featureType, |
| 800 | 709 |
JDBCStoreParameters storeParams) throws DataException {
|
| 801 | 710 |
|
| 711 |
if ((storeParams.getDefaultGeometry() == null) && (storeParams instanceof OracleNewStoreParameters)) {
|
|
| 712 |
OracleNewStoreParameters osp = (OracleNewStoreParameters) storeParams; |
|
| 713 |
String geoname = osp.getDefaultFeatureType().getDefaultGeometryAttributeName(); |
|
| 714 |
storeParams.setDefaultGeometry(geoname); |
|
| 715 |
} |
|
| 716 |
|
|
| 802 | 717 |
String sqlstr = storeParams.getSQL(); |
| 803 | 718 |
|
| 804 | 719 |
if (sqlstr != null && sqlstr.trim().length() > 0) {
|
| ... | ... | |
| 810 | 725 |
|
| 811 | 726 |
loadFeatureType(featureType, storeParams, sqlstr, storeParams |
| 812 | 727 |
.getSchema(), storeParams.getTable()); |
| 728 |
|
|
| 729 |
storeParams.setSQL(null); |
|
| 813 | 730 |
// super.loadFeatureType(featureType, storeParams); |
| 814 | 731 |
} |
| 815 | 732 |
|
| ... | ... | |
| 828 | 745 |
|
| 829 | 746 |
Statement st = null; |
| 830 | 747 |
ResultSet rs = null; |
| 748 |
String reserved_geocolname = null; |
|
| 749 |
|
|
| 831 | 750 |
try {
|
| 832 | 751 |
// Sacamos la lista de los attributos geometricos |
| 833 | 752 |
|
| 834 | 753 |
EditableFeatureAttributeDescriptor attr; |
| 835 |
List<FeatureAttributeDescriptor> geoAttrs = new ArrayList<FeatureAttributeDescriptor>();
|
|
| 754 |
ArrayList geoAttrs = new ArrayList();
|
|
| 836 | 755 |
|
| 837 | 756 |
Iterator iter = featureType.iterator(); |
| 838 | 757 |
while (iter.hasNext()) {
|
| ... | ... | |
| 861 | 780 |
} |
| 862 | 781 |
String srID; |
| 863 | 782 |
|
| 864 |
Iterator it = geoAttrs.iterator(); |
|
| 783 |
EditableFeatureAttributeDescriptor auxdesc; |
|
| 784 |
|
|
| 865 | 785 |
|
| 866 |
while (it.hasNext()) {
|
|
| 867 |
attr = (EditableFeatureAttributeDescriptor) it.next(); |
|
| 868 |
String fieldName = attr.getName(); |
|
| 869 | 786 |
while (rs.next()) {
|
| 870 |
String rsName = rs |
|
| 871 |
.getString(OracleValues.USER_ORACLE_GEOMETADATA_VIEW_COLUMN_NAME); |
|
| 872 |
if (fieldName.compareTo(rsName) == 0) {
|
|
| 787 |
String rsName = |
|
| 788 |
rs.getString(OracleValues.USER_ORACLE_GEOMETADATA_VIEW_COLUMN_NAME); |
|
| 789 |
reserved_geocolname = rsName; |
|
| 790 |
auxdesc = getAttrDescForCol(geoAttrs, rsName); |
|
| 791 |
if (auxdesc != null) {
|
|
| 873 | 792 |
Object sridobj = rs.getObject("SRID");
|
| 874 |
|
|
| 875 | 793 |
if (sridobj == null) {
|
| 876 |
attr.setSRS(null);
|
|
| 794 |
auxdesc.setSRS(null);
|
|
| 877 | 795 |
} else {
|
| 878 | 796 |
srID = (String) sridobj; |
| 879 |
int epsg = this.getProviderEPSG(srID);
|
|
| 797 |
int epsg = OracleUtils.oracleSridToEpsg(srID);
|
|
| 880 | 798 |
String sepsg = "EPSG:" + Integer.toString(epsg); |
| 881 |
attr.setSRS(CRSFactory.getCRS(sepsg));
|
|
| 799 |
auxdesc.setSRS(CRSFactory.getCRS(sepsg));
|
|
| 882 | 800 |
} |
| 883 |
break; |
|
| 884 |
} else {
|
|
| 885 |
continue; |
|
| 886 | 801 |
} |
| 887 |
} |
|
| 802 |
if (featureType.getDefaultGeometryAttribute() == null) {
|
|
| 803 |
((DefaultEditableFeatureType) featureType).setDefaultGeometryAttributeName(reserved_geocolname); |
|
| 804 |
} |
|
| 805 |
|
|
| 806 |
} |
|
| 807 |
} catch (java.sql.SQLException e) {
|
|
| 808 |
throw new JDBCSQLException(e); |
|
| 809 |
} finally {
|
|
| 810 |
try { rs.close(); } catch (Exception e) { };
|
|
| 811 |
try { st.close(); } catch (Exception e) { };
|
|
| 812 |
} |
|
| 813 |
|
|
| 814 |
// guess shape type |
|
| 815 |
String geoColName = featureType.getDefaultGeometryAttributeName(); |
|
| 816 |
if (geoColName == null) {
|
|
| 817 |
geoColName = reserved_geocolname; |
|
| 818 |
} |
|
| 819 |
|
|
| 820 |
try {
|
|
| 821 |
String str_geo = "SELECT " + geoColName + " FROM " + baseTable + |
|
| 822 |
" WHERE (" + geoColName + " IS NOT NULL) AND " + OracleUtils.EXPONENTIAL_INDICES_CONDITION;
|
|
| 888 | 823 |
|
| 824 |
st = conn.createStatement(); |
|
| 825 |
try {
|
|
| 826 |
rs = st.executeQuery(str_geo); |
|
| 827 |
} catch (SQLException e) {
|
|
| 828 |
throw new JDBCExecuteSQLException(str_geo, e); |
|
| 889 | 829 |
} |
| 830 |
|
|
| 831 |
int aux = 0; |
|
| 832 |
int guess_type = TYPES.GEOMETRY; |
|
| 833 |
int guess_subtype = SUBTYPES.GEOM2D; |
|
| 834 |
|
|
| 835 |
STRUCT sample_geo; |
|
| 836 |
ArrayList shptypes = new ArrayList(); |
|
| 837 |
int[] ty_subty; |
|
| 838 |
while (rs.next()) {
|
|
| 839 |
sample_geo = (STRUCT) rs.getObject(1); |
|
| 840 |
ty_subty = OracleUtils.getGeoTypeSubTypeOfStruct(sample_geo); |
|
| 841 |
aux = ty_subty[0]; |
|
| 842 |
guess_subtype = ty_subty[1]; |
|
| 843 |
shptypes.add(new Integer(aux)); |
|
| 844 |
} |
|
| 890 | 845 |
|
| 846 |
if (shptypes.size() > 0) {
|
|
| 847 |
guess_type = OracleUtils.getShapeTypeFromArray(shptypes); |
|
| 848 |
} else {
|
|
| 849 |
logger.warn("Did not find geometries to sample. Assumed TYPE = GEOMETRY, SUBTYPE = 2D");
|
|
| 850 |
} |
|
| 851 |
|
|
| 852 |
DefaultEditableFeatureAttributeDescriptor dfad = null; |
|
| 853 |
try {
|
|
| 854 |
dfad = (DefaultEditableFeatureAttributeDescriptor) featureType.getDefaultGeometryAttribute(); |
|
| 855 |
dfad.setGeometryType(guess_type); |
|
| 856 |
dfad.setGeometrySubType(guess_subtype); |
|
| 857 |
} catch (ClassCastException cce) {
|
|
| 858 |
logger.error("Unexpected non editable feature type. Did not set geo types.");
|
|
| 859 |
} |
|
| 891 | 860 |
} catch (java.sql.SQLException e) {
|
| 892 | 861 |
throw new JDBCSQLException(e); |
| 893 | 862 |
} finally {
|
| 894 |
try {
|
|
| 895 |
rs.close(); |
|
| 896 |
} catch (Exception e) {
|
|
| 863 |
try {rs.close();} catch (Exception e) { };
|
|
| 864 |
try {st.close();} catch (Exception e) { };
|
|
| 865 |
} |
|
| 866 |
} |
|
| 867 |
|
|
| 868 |
private EditableFeatureAttributeDescriptor getAttrDescForCol(ArrayList list, String name) {
|
|
| 869 |
|
|
| 870 |
int sz = list.size(); |
|
| 871 |
for (int i=0; i<sz; i++) {
|
|
| 872 |
EditableFeatureAttributeDescriptor aux = (EditableFeatureAttributeDescriptor) list.get(i); |
|
| 873 |
if (aux.getName().compareToIgnoreCase(name) == 0) {
|
|
| 874 |
return aux; |
|
| 897 | 875 |
} |
| 898 |
; |
|
| 899 |
try {
|
|
| 900 |
st.close(); |
|
| 901 |
} catch (Exception e) {
|
|
| 902 |
} |
|
| 903 |
; |
|
| 904 | 876 |
} |
| 905 |
|
|
| 877 |
// not found |
|
| 878 |
return null; |
|
| 906 | 879 |
} |
| 907 | 880 |
|
| 908 | 881 |
/** |
| ... | ... | |
| 940 | 913 |
|
| 941 | 914 |
return sqls; |
| 942 | 915 |
} |
| 916 |
|
|
| 917 |
|
|
| 918 |
|
|
| 919 |
// protected void loadFeatureType(Connection conn, |
|
| 920 |
// EditableFeatureType featureType, String sql, String[] pks, |
|
| 921 |
// String defGeomName, String schema, String table) |
|
| 922 |
// throws DataException {
|
|
| 923 |
// |
|
| 924 |
// Statement stAux = null; |
|
| 925 |
// ResultSet rs = null; |
|
| 926 |
// try {
|
|
| 927 |
// |
|
| 928 |
// stAux = conn.createStatement(); |
|
| 929 |
// stAux.setFetchSize(1); |
|
| 930 |
// |
|
| 931 |
// try {
|
|
| 932 |
// rs = stAux.executeQuery(sql); |
|
| 933 |
// } catch (SQLException e) {
|
|
| 934 |
// throw new JDBCExecuteSQLException(sql, e); |
|
| 935 |
// } |
|
| 936 |
// ResultSetMetaData rsMetadata = rs.getMetaData(); |
|
| 937 |
// |
|
| 938 |
// List pksList = null; |
|
| 939 |
// if (pks != null) {
|
|
| 940 |
// pksList = Arrays.asList(pks); |
|
| 941 |
// } |
|
| 942 |
// |
|
| 943 |
// int i; |
|
| 944 |
// int geometriesColumns = 0; |
|
| 945 |
// String lastGeometry = null; |
|
| 946 |
// |
|
| 947 |
// EditableFeatureAttributeDescriptor attr; |
|
| 948 |
// for (i = 1; i <= rsMetadata.getColumnCount(); i++) {
|
|
| 949 |
// attr = getAttributeFromJDBC(featureType, conn, rsMetadata, i); |
|
| 950 |
// if (pksList != null && pksList.contains(attr.getName())) {
|
|
| 951 |
// attr.setIsPrimaryKey(true); |
|
| 952 |
// } |
|
| 953 |
// if (attr.getDataType() == DataTypes.GEOMETRY) {
|
|
| 954 |
// geometriesColumns++; |
|
| 955 |
// lastGeometry = attr.getName(); |
|
| 956 |
// if (lastGeometry.equals(defGeomName)) {
|
|
| 957 |
// featureType.setDefaultGeometryAttributeName(defGeomName); |
|
| 958 |
// } |
|
| 959 |
// } |
|
| 960 |
// } |
|
| 961 |
// |
|
| 962 |
// if (geometriesColumns > 0) {
|
|
| 963 |
// loadSRS_and_shapeType(conn, rsMetadata, featureType, schema, |
|
| 964 |
// table); |
|
| 965 |
// } |
|
| 966 |
// |
|
| 967 |
// if (defGeomName == null && geometriesColumns == 1) {
|
|
| 968 |
// featureType.setDefaultGeometryAttributeName(lastGeometry); |
|
| 969 |
// defGeomName = lastGeometry; |
|
| 970 |
// } |
|
| 971 |
// |
|
| 972 |
// } catch (java.sql.SQLException e) {
|
|
| 973 |
// throw new JDBCSQLException(e); // FIXME exception |
|
| 974 |
// } finally {
|
|
| 975 |
// try {
|
|
| 976 |
// rs.close(); |
|
| 977 |
// } catch (Exception e) {
|
|
| 978 |
// } |
|
| 979 |
// try {
|
|
| 980 |
// stAux.close(); |
|
| 981 |
// } catch (Exception e) {
|
|
| 982 |
// } |
|
| 983 |
// |
|
| 984 |
// } |
|
| 985 |
// |
|
| 986 |
// } |
|
| 943 | 987 |
|
| 944 | 988 |
|
| 945 | 989 |
} |
Also available in: Unified diff