Revision 64 trunk/org.gvsig.postgresql/org.gvsig.postgresql.provider/src/main/java/org/gvsig/fmap/dal/store/postgresql/PostgreSQLHelper.java
| PostgreSQLHelper.java | ||
|---|---|---|
| 30 | 30 |
*/ |
| 31 | 31 |
package org.gvsig.fmap.dal.store.postgresql; |
| 32 | 32 |
|
| 33 |
import java.io.File; |
|
| 34 |
import java.io.InputStream; |
|
| 35 |
import java.net.URL; |
|
| 36 | 33 |
import java.sql.Connection; |
| 37 | 34 |
import java.sql.PreparedStatement; |
| 38 | 35 |
import java.sql.ResultSet; |
| ... | ... | |
| 67 | 64 |
import org.gvsig.fmap.dal.feature.FeatureType; |
| 68 | 65 |
import org.gvsig.fmap.dal.feature.exception.CreateGeometryException; |
| 69 | 66 |
import org.gvsig.fmap.dal.feature.exception.UnsupportedDataTypeException; |
| 70 |
import org.gvsig.fmap.dal.feature.exception.UnsupportedGeometryException; |
|
| 71 | 67 |
import org.gvsig.fmap.dal.resource.ResourceAction; |
| 72 | 68 |
import org.gvsig.fmap.dal.resource.spi.ResourceManagerProviderServices; |
| 73 | 69 |
import org.gvsig.fmap.dal.store.jdbc.ConnectionAction; |
| ... | ... | |
| 80 | 76 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCPreparingSQLException; |
| 81 | 77 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException; |
| 82 | 78 |
import org.gvsig.fmap.geom.Geometry; |
| 83 |
import org.gvsig.fmap.geom.GeometryLocator; |
|
| 84 |
import org.gvsig.fmap.geom.GeometryManager; |
|
| 85 |
import org.gvsig.fmap.geom.operation.GeometryOperationException; |
|
| 86 |
import org.gvsig.fmap.geom.operation.GeometryOperationNotSupportedException; |
|
| 87 |
import org.gvsig.fmap.geom.operation.fromwkb.FromWKB; |
|
| 88 |
import org.gvsig.fmap.geom.operation.fromwkb.FromWKBGeometryOperationContext; |
|
| 89 | 79 |
import org.gvsig.fmap.geom.primitive.Envelope; |
| 90 | 80 |
import org.gvsig.tools.ToolsLocator; |
| 91 | 81 |
import org.gvsig.tools.exception.BaseException; |
| ... | ... | |
| 138 | 128 |
try {
|
| 139 | 129 |
st = conn.createStatement(); |
| 140 | 130 |
try {
|
| 141 |
rs = st.executeQuery(sql);
|
|
| 131 |
rs = JDBCHelper.executeQuery(st, sql);
|
|
| 142 | 132 |
} catch (java.sql.SQLException e) {
|
| 143 | 133 |
throw new JDBCExecuteSQLException(sql, e); |
| 144 | 134 |
} |
| ... | ... | |
| 158 | 148 |
return defaultSchema; |
| 159 | 149 |
} |
| 160 | 150 |
|
| 161 |
public Envelope getFullEnvelopeOfField(
|
|
| 162 |
JDBCStoreParameters storeParams,
|
|
| 163 |
String geometryAttrName, Envelope limit)
|
|
| 164 |
throws DataException {
|
|
| 151 |
public Envelope getFullEnvelopeOfField(
|
|
| 152 |
JDBCStoreParameters storeParams,
|
|
| 153 |
String geometryAttrName, Envelope limit)
|
|
| 154 |
throws DataException {
|
|
| 165 | 155 |
|
| 166 |
StringBuilder strb = new StringBuilder();
|
|
| 167 |
strb.append("Select " + getFunctionName("ST_AsBinary") + "(" +
|
|
| 168 |
getFunctionName("ST_Extent") + "(");
|
|
| 169 |
strb.append(escapeFieldName(geometryAttrName));
|
|
| 170 |
strb.append(")) from ");
|
|
| 156 |
StringBuilder strb = new StringBuilder();
|
|
| 157 |
strb.append("Select " + getFunctionName("ST_AsBinary") + "("
|
|
| 158 |
+ getFunctionName("ST_Extent") + "(");
|
|
| 159 |
strb.append(escapeFieldName(geometryAttrName));
|
|
| 160 |
strb.append(")) from ");
|
|
| 171 | 161 |
|
| 172 |
if (storeParams.getSQL() != null
|
|
| 173 |
&& storeParams.getSQL().trim().length() > 0) {
|
|
| 174 |
strb.append('(');
|
|
| 175 |
strb.append(storeParams.getSQL());
|
|
| 176 |
strb.append(") as _subquery_alias_ ");
|
|
| 177 |
} else {
|
|
| 178 |
strb.append(storeParams.tableID());
|
|
| 179 |
}
|
|
| 162 |
if (storeParams.getSQL() != null
|
|
| 163 |
&& storeParams.getSQL().trim().length() > 0) {
|
|
| 164 |
strb.append('(');
|
|
| 165 |
strb.append(storeParams.getSQL());
|
|
| 166 |
strb.append(") as _subquery_alias_ ");
|
|
| 167 |
} else {
|
|
| 168 |
strb.append(storeParams.tableID());
|
|
| 169 |
}
|
|
| 180 | 170 |
|
| 181 |
if (limit != null || (storeParams.getBaseFilter() != null
|
|
| 182 |
&& storeParams.getBaseFilter().trim().length() > 0)){
|
|
| 183 |
strb.append(" where ");
|
|
| 171 |
if (limit != null || (storeParams.getBaseFilter() != null
|
|
| 172 |
&& storeParams.getBaseFilter().trim().length() > 0)) {
|
|
| 173 |
strb.append(" where ");
|
|
| 184 | 174 |
|
| 185 |
if (limit != null) {
|
|
| 186 |
strb.append(" ( " + getFunctionName("ST_Intersects") + "("
|
|
| 187 |
+ getFunctionName("ST_GeomFromText") + "('");
|
|
| 188 |
String workAreaWkt = null;
|
|
| 189 |
try {
|
|
| 190 |
workAreaWkt = limit.getGeometry().convertToWKT();
|
|
| 191 |
} catch (Exception e) {
|
|
| 192 |
throw new CreateGeometryException(e);
|
|
| 193 |
}
|
|
| 194 |
strb.append(workAreaWkt);
|
|
| 195 |
strb.append("', ");
|
|
| 175 |
if (limit != null) {
|
|
| 176 |
strb.append(" ( " + getFunctionName("ST_Intersects") + "("
|
|
| 177 |
+ getFunctionName("ST_GeomFromText") + "('");
|
|
| 178 |
String workAreaWkt = null;
|
|
| 179 |
try {
|
|
| 180 |
workAreaWkt = limit.getGeometry().convertToWKT();
|
|
| 181 |
} catch (Exception e) {
|
|
| 182 |
throw new CreateGeometryException(e);
|
|
| 183 |
}
|
|
| 184 |
strb.append(workAreaWkt);
|
|
| 185 |
strb.append("', ");
|
|
| 196 | 186 |
|
| 197 |
IProjection proj = storeParams.getCRS();
|
|
| 198 |
int sridInt = this.getProviderSRID(proj);
|
|
| 199 |
if (sridInt == -1) {
|
|
| 200 |
throw new CreateGeometryException(
|
|
| 201 |
new Exception("CRS is null or unknown."));
|
|
| 202 |
} else {
|
|
| 203 |
strb.append(Integer.toString(sridInt));
|
|
| 204 |
}
|
|
| 205 |
strb.append("), " + getFunctionName("ST_Envelope") + "(");
|
|
| 206 |
strb.append(escapeFieldName(geometryAttrName));
|
|
| 207 |
strb.append(")) ) ");
|
|
| 187 |
IProjection proj = storeParams.getCRS();
|
|
| 188 |
int sridInt = this.getProviderSRID(proj);
|
|
| 189 |
if (sridInt == -1) {
|
|
| 190 |
throw new CreateGeometryException(
|
|
| 191 |
new Exception("CRS is null or unknown."));
|
|
| 192 |
} else {
|
|
| 193 |
strb.append(Integer.toString(sridInt));
|
|
| 194 |
}
|
|
| 195 |
strb.append("), " + getFunctionName("ST_Envelope") + "(");
|
|
| 196 |
strb.append(escapeFieldName(geometryAttrName));
|
|
| 197 |
strb.append(")) ) ");
|
|
| 208 | 198 |
|
| 209 |
}
|
|
| 210 |
if (storeParams.getBaseFilter() != null && storeParams.getBaseFilter().trim().length() > 0) {
|
|
| 211 |
if (limit != null) {
|
|
| 212 |
strb.append(" and ");
|
|
| 213 |
}
|
|
| 214 |
strb.append(" ( ");
|
|
| 215 |
strb.append(storeParams.getBaseFilter());
|
|
| 216 |
strb.append(" ) ");
|
|
| 217 |
}
|
|
| 199 |
}
|
|
| 200 |
if (storeParams.getBaseFilter() != null && storeParams.getBaseFilter().trim().length() > 0) {
|
|
| 201 |
if (limit != null) {
|
|
| 202 |
strb.append(" and ");
|
|
| 203 |
}
|
|
| 204 |
strb.append(" ( ");
|
|
| 205 |
strb.append(storeParams.getBaseFilter());
|
|
| 206 |
strb.append(" ) ");
|
|
| 207 |
}
|
|
| 218 | 208 |
|
| 219 |
}
|
|
| 209 |
}
|
|
| 220 | 210 |
|
| 221 |
final String sql = strb.toString();
|
|
| 211 |
final String sql = strb.toString();
|
|
| 222 | 212 |
|
| 223 |
this.open();
|
|
| 213 |
this.open();
|
|
| 224 | 214 |
|
| 225 |
return (Envelope) getResource().execute(new ResourceAction() {
|
|
| 226 |
public String toString() {
|
|
| 227 |
return "getEnvelope"; |
|
| 228 |
} |
|
| 229 |
public Object run() throws Exception {
|
|
| 230 |
ResultSet rs = null; |
|
| 231 |
Statement st = null; |
|
| 232 |
Connection conn = null; |
|
| 233 |
Envelope fullEnvelope = null; |
|
| 234 |
|
|
| 235 |
Envelope emptyEnv = |
|
| 236 |
geomManager.createEnvelope(Geometry.SUBTYPES.GEOM2D); |
|
| 237 |
|
|
| 238 |
try {
|
|
| 215 |
return (Envelope) getResource().execute(new ResourceAction() {
|
|
| 216 |
public String toString() {
|
|
| 217 |
return "getEnvelope"; |
|
| 218 |
} |
|
| 239 | 219 |
|
| 240 |
conn = getConnection(); |
|
| 241 |
st = conn.createStatement(); |
|
| 242 |
try {
|
|
| 243 |
rs = st.executeQuery(sql); |
|
| 244 |
} catch (java.sql.SQLException e) {
|
|
| 245 |
throw new JDBCExecuteSQLException(sql, e); |
|
| 246 |
} |
|
| 247 |
if (!rs.next()) {
|
|
| 248 |
return emptyEnv; |
|
| 249 |
} |
|
| 220 |
public Object run() throws Exception {
|
|
| 221 |
ResultSet rs = null; |
|
| 222 |
Statement st = null; |
|
| 223 |
Connection conn = null; |
|
| 224 |
Envelope fullEnvelope = null; |
|
| 250 | 225 |
|
| 251 |
byte[] data = rs.getBytes(1); |
|
| 252 |
if (data == null) {
|
|
| 253 |
return emptyEnv; |
|
| 254 |
} |
|
| 226 |
Envelope emptyEnv |
|
| 227 |
= geomManager.createEnvelope(Geometry.SUBTYPES.GEOM2D); |
|
| 255 | 228 |
|
| 256 |
Geometry geom = geomManager.createFrom(data);
|
|
| 229 |
try {
|
|
| 257 | 230 |
|
| 258 |
fullEnvelope = geom.getEnvelope(); |
|
| 231 |
conn = getConnection(); |
|
| 232 |
st = conn.createStatement(); |
|
| 233 |
try {
|
|
| 234 |
rs = JDBCHelper.executeQuery(st, sql); |
|
| 235 |
} catch (java.sql.SQLException e) {
|
|
| 236 |
throw new JDBCExecuteSQLException(sql, e); |
|
| 237 |
} |
|
| 238 |
if (!rs.next()) {
|
|
| 239 |
return emptyEnv; |
|
| 240 |
} |
|
| 259 | 241 |
|
| 260 |
return fullEnvelope; |
|
| 261 |
} catch (java.sql.SQLException e) {
|
|
| 262 |
throw new JDBCSQLException(e); |
|
| 263 |
} catch (BaseException e) {
|
|
| 264 |
throw new ReadException(user.getProviderName(), e); |
|
| 265 |
} finally {
|
|
| 266 |
try {
|
|
| 267 |
rs.close(); |
|
| 268 |
} catch (Exception e) {
|
|
| 269 |
} |
|
| 270 |
try {
|
|
| 271 |
st.close(); |
|
| 272 |
} catch (Exception e) {
|
|
| 273 |
} |
|
| 274 |
try {
|
|
| 275 |
conn.close(); |
|
| 276 |
} catch (Exception e) {
|
|
| 277 |
} |
|
| 278 |
rs = null; |
|
| 279 |
st = null; |
|
| 280 |
conn = null; |
|
| 281 |
} |
|
| 282 |
} |
|
| 283 |
}); |
|
| 284 |
} |
|
| 242 |
byte[] data = rs.getBytes(1); |
|
| 243 |
if (data == null) {
|
|
| 244 |
return emptyEnv; |
|
| 245 |
} |
|
| 285 | 246 |
|
| 247 |
Geometry geom = geomManager.createFrom(data); |
|
| 248 |
|
|
| 249 |
fullEnvelope = geom.getEnvelope(); |
|
| 250 |
|
|
| 251 |
return fullEnvelope; |
|
| 252 |
} catch (java.sql.SQLException e) {
|
|
| 253 |
throw new JDBCSQLException(e); |
|
| 254 |
} catch (BaseException e) {
|
|
| 255 |
throw new ReadException(user.getProviderName(), e); |
|
| 256 |
} finally {
|
|
| 257 |
try {
|
|
| 258 |
rs.close(); |
|
| 259 |
} catch (Exception e) {
|
|
| 260 |
} |
|
| 261 |
try {
|
|
| 262 |
st.close(); |
|
| 263 |
} catch (Exception e) {
|
|
| 264 |
} |
|
| 265 |
try {
|
|
| 266 |
conn.close(); |
|
| 267 |
} catch (Exception e) {
|
|
| 268 |
} |
|
| 269 |
rs = null; |
|
| 270 |
st = null; |
|
| 271 |
conn = null; |
|
| 272 |
} |
|
| 273 |
} |
|
| 274 |
}); |
|
| 275 |
} |
|
| 276 |
|
|
| 286 | 277 |
@Override |
| 287 | 278 |
protected boolean supportsGeometry() {
|
| 288 | 279 |
return true; |
| ... | ... | |
| 433 | 424 |
|
| 434 | 425 |
st = conn.createStatement(); |
| 435 | 426 |
try {
|
| 436 |
rs = st.executeQuery(sql);
|
|
| 427 |
rs = JDBCHelper.executeQuery(st,sql);
|
|
| 437 | 428 |
} catch (SQLException e) {
|
| 438 | 429 |
throw new JDBCExecuteSQLException(sql, e); |
| 439 | 430 |
} |
| ... | ... | |
| 684 | 675 |
st.setInt(i + 1, Integer.parseInt(abrev[i])); |
| 685 | 676 |
|
| 686 | 677 |
try{
|
| 687 |
rs = st.executeQuery(); |
|
| 678 |
rs = JDBCHelper.executeQuery(st,sql); |
|
| 679 |
|
|
| 688 | 680 |
} catch (SQLException e){
|
| 689 | 681 |
throw new JDBCExecutePreparedSQLException(sql, abrev, e); |
| 690 | 682 |
} |
| ... | ... | |
| 956 | 948 |
try {
|
| 957 | 949 |
conn = this.getConnection(); |
| 958 | 950 |
st = conn.createStatement(); |
| 959 |
rs = st.executeQuery(sql);
|
|
| 951 |
rs = JDBCHelper.executeQuery(st,sql);
|
|
| 960 | 952 |
rs.next(); |
| 961 | 953 |
v = rs.getString(1); |
| 962 | 954 |
if (v == null) {
|
Also available in: Unified diff