root / branches / v2_0_0_prep / libraries / org.gvsig.fmap.dal.db.h2spatial / src / main / java / org / gvsig / fmap / dal / db / h2spatial / impl / H2SpatialServerExplorer.java @ 32684
History | View | Annotate | Download (12.9 KB)
1 |
package org.gvsig.fmap.dal.db.h2spatial.impl; |
---|---|
2 |
|
3 |
import java.sql.Connection; |
4 |
import java.sql.ResultSet; |
5 |
import java.sql.SQLException; |
6 |
import java.sql.Statement; |
7 |
import java.util.ArrayList; |
8 |
import java.util.HashSet; |
9 |
import java.util.Iterator; |
10 |
import java.util.List; |
11 |
import java.util.Set; |
12 |
|
13 |
import net.sourceforge.hatbox.jts.Proc; |
14 |
|
15 |
import org.gvsig.fmap.dal.DataStoreParameters; |
16 |
import org.gvsig.fmap.dal.NewDataStoreParameters; |
17 |
import org.gvsig.fmap.dal.exception.DataException; |
18 |
import org.gvsig.fmap.dal.exception.InitializeException; |
19 |
import org.gvsig.fmap.dal.exception.ProviderNotRegisteredException; |
20 |
import org.gvsig.fmap.dal.exception.RemoveException; |
21 |
import org.gvsig.fmap.dal.exception.ValidateDataParametersException; |
22 |
import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
23 |
import org.gvsig.fmap.dal.feature.FeatureType; |
24 |
import org.gvsig.fmap.dal.feature.NewFeatureStoreParameters; |
25 |
import org.gvsig.fmap.dal.spi.DataServerExplorerProviderServices; |
26 |
import org.gvsig.fmap.dal.store.jdbc.JDBCHelper; |
27 |
import org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorer; |
28 |
import org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorerParameters; |
29 |
import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters; |
30 |
import org.gvsig.fmap.dal.store.jdbc.TransactionalAction; |
31 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCExecuteSQLException; |
32 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException; |
33 |
import org.slf4j.Logger; |
34 |
import org.slf4j.LoggerFactory; |
35 |
|
36 |
/**
|
37 |
*
|
38 |
* @author Vicente Caballero Navarro
|
39 |
*/
|
40 |
public class H2SpatialServerExplorer extends JDBCServerExplorer { |
41 |
|
42 |
final static private Logger logger = LoggerFactory |
43 |
.getLogger(H2SpatialServerExplorer.class); |
44 |
|
45 |
public static final String NAME = "H2SpatialExplorer"; |
46 |
|
47 |
private static final String[] h2Alias = new String[] { |
48 |
"HATBOX_BUILD_INDEX",
|
49 |
"HATBOX_DE_SPATIALIZE",
|
50 |
"HATBOX_INTERSECTS_WKB",
|
51 |
"HATBOX_INTERSECTS_WKT",
|
52 |
"HATBOX_MBR_INTERSECTS_ENV",
|
53 |
"HATBOX_QUERY_WITH_PREDICATE_WKB",
|
54 |
"HATBOX_QUERY_WITH_PREDICATE_WKT",
|
55 |
"HATBOX_SPATIALIZE"
|
56 |
}; |
57 |
|
58 |
private static final String[] h2AliasMethod = new String[] { |
59 |
"net.sourceforge.hatbox.jts.Proc.buildIndex",
|
60 |
"net.sourceforge.hatbox.jts.Proc.deSpatialize",
|
61 |
"net.sourceforge.hatbox.jts.Proc.queryIntersectsWkb",
|
62 |
"net.sourceforge.hatbox.jts.Proc.queryIntersectsWkt",
|
63 |
"net.sourceforge.hatbox.jts.Proc.mbrIntersectsEnv",
|
64 |
"net.sourceforge.hatbox.jts.Proc.queryWithPredicateWkb",
|
65 |
"net.sourceforge.hatbox.jts.Proc.queryWithPredicateWkt",
|
66 |
"net.sourceforge.hatbox.jts.Proc.spatialize"
|
67 |
}; |
68 |
|
69 |
private static boolean initialized=false; |
70 |
|
71 |
public H2SpatialServerExplorer(JDBCServerExplorerParameters parameters,
|
72 |
DataServerExplorerProviderServices services) |
73 |
throws InitializeException {
|
74 |
super(parameters, services);
|
75 |
} |
76 |
|
77 |
private H2SpatialServerExplorerParameters getH2Spatialparameters() {
|
78 |
return (H2SpatialServerExplorerParameters) super.getParameters(); |
79 |
} |
80 |
|
81 |
|
82 |
/* (non-Javadoc)
|
83 |
* @see org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorer#createHelper()
|
84 |
*/
|
85 |
protected JDBCHelper createHelper() throws InitializeException { |
86 |
return new H2SpatialHelper(this, getH2Spatialparameters()); |
87 |
} |
88 |
|
89 |
|
90 |
/* (non-Javadoc)
|
91 |
* @see org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorer#getStoreName()
|
92 |
*/
|
93 |
protected String getStoreName() { |
94 |
return H2SpatialStoreProvider.NAME;
|
95 |
} |
96 |
|
97 |
/* (non-Javadoc)
|
98 |
* @see org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorer#getName()
|
99 |
*/
|
100 |
public String getName() { |
101 |
return NAME;
|
102 |
} |
103 |
|
104 |
/* (non-Javadoc)
|
105 |
* @see org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorer#createStoreParams()
|
106 |
*/
|
107 |
protected JDBCStoreParameters createStoreParams()
|
108 |
throws InitializeException, ProviderNotRegisteredException {
|
109 |
H2SpatialStoreParameters orgParams = (H2SpatialStoreParameters) super
|
110 |
.createStoreParams(); |
111 |
|
112 |
orgParams.setUseSSL(getH2Spatialparameters().getUseSSL()); |
113 |
|
114 |
return orgParams;
|
115 |
} |
116 |
|
117 |
/* (non-Javadoc)
|
118 |
* @see org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorer#canAdd()
|
119 |
*/
|
120 |
public boolean canAdd() { |
121 |
return true; |
122 |
} |
123 |
|
124 |
/* (non-Javadoc)
|
125 |
* @see org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorer#checkIsMine(org.gvsig.fmap.dal.DataStoreParameters)
|
126 |
*/
|
127 |
protected void checkIsMine(DataStoreParameters dsp) { |
128 |
if (!(dsp instanceof H2SpatialStoreParameters)) { |
129 |
// FIXME Excpetion ???
|
130 |
throw new IllegalArgumentException( |
131 |
"not instance of H2SpatialStoreParameters");
|
132 |
} |
133 |
super.checkIsMine(dsp);
|
134 |
|
135 |
H2SpatialServerExplorerParameters myParameters = getH2Spatialparameters(); |
136 |
H2SpatialStoreParameters myp = (H2SpatialStoreParameters) dsp; |
137 |
if (myp.getUseSSL().booleanValue() != myParameters.getUseSSL()) {
|
138 |
throw new IllegalArgumentException("worng explorer: useSSL (mine:" |
139 |
+ myParameters.getUseSSL() + " other:" + myp.getUseSSL()
|
140 |
+ ")");
|
141 |
} |
142 |
} |
143 |
|
144 |
/* (non-Javadoc)
|
145 |
* @see org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorer#remove(org.gvsig.fmap.dal.DataStoreParameters)
|
146 |
*/
|
147 |
public void remove(DataStoreParameters dsp) throws RemoveException { |
148 |
final H2SpatialStoreParameters slParams =(H2SpatialStoreParameters) dsp;
|
149 |
|
150 |
TransactionalAction action = new TransactionalAction() {
|
151 |
public boolean continueTransactionAllowed() { |
152 |
return false; |
153 |
} |
154 |
public Object action(Connection conn) throws DataException { |
155 |
|
156 |
|
157 |
Statement st;
|
158 |
try{
|
159 |
Proc.deSpatialize(conn, slParams.getSchema().toUpperCase(), slParams.getTable().toUpperCase()); |
160 |
st = conn.createStatement(); |
161 |
} catch (SQLException e) { |
162 |
throw new JDBCSQLException(e); |
163 |
} |
164 |
|
165 |
StringBuilder strb3 = new StringBuilder(); |
166 |
strb3.append("DROP TABLE IF EXISTS ");
|
167 |
strb3.append(slParams.getTable().toUpperCase()); |
168 |
|
169 |
StringBuilder strb4 = new StringBuilder(); |
170 |
strb4.append("DROP TABLE IF EXISTS ");
|
171 |
strb4.append(slParams.getTable().toUpperCase()); |
172 |
strb4.append("_HATBOX");
|
173 |
|
174 |
String sqlDeleteTable = strb3.toString();
|
175 |
String sqlDeleteTable_HATBOX = strb4.toString();
|
176 |
|
177 |
try{
|
178 |
try{
|
179 |
st.execute(sqlDeleteTable); |
180 |
} catch (SQLException e) { |
181 |
throw new JDBCExecuteSQLException(sqlDeleteTable, e); |
182 |
} |
183 |
|
184 |
try {
|
185 |
st.execute(sqlDeleteTable_HATBOX); |
186 |
} catch (SQLException e) { |
187 |
throw new JDBCExecuteSQLException(sqlDeleteTable_HATBOX, e); |
188 |
} |
189 |
} finally{
|
190 |
try{ st.close(); } catch (SQLException e) {}; |
191 |
} |
192 |
return null; |
193 |
} |
194 |
}; |
195 |
try {
|
196 |
this.helper.doConnectionAction(action);
|
197 |
} catch (Exception e) { |
198 |
throw new RemoveException(this.getName(), e); |
199 |
} |
200 |
} |
201 |
|
202 |
/* (non-Javadoc)
|
203 |
* @see org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorer#getAddParameters()
|
204 |
*/
|
205 |
public NewDataStoreParameters getAddParameters() throws DataException { |
206 |
H2SpatialServerExplorerParameters parameters = getH2Spatialparameters(); |
207 |
H2SpatialNewStoreParameters params = new H2SpatialNewStoreParameters();
|
208 |
params.setHost(parameters.getHost()); |
209 |
params.setPort(parameters.getPort()); |
210 |
params.setDBName(parameters.getDBName()); |
211 |
params.setUser(parameters.getUser()); |
212 |
params.setPassword(parameters.getPassword()); |
213 |
params.setCatalog(parameters.getCatalog()); |
214 |
params.setSchema(parameters.getSchema()); |
215 |
params.setJDBCDriverClassName(parameters.getJDBCDriverClassName()); |
216 |
params.setUrl(parameters.getUrl()); |
217 |
params.setUseSSL(parameters.getUseSSL()); |
218 |
|
219 |
|
220 |
params.setDefaultFeatureType(this.getServerExplorerProviderServices()
|
221 |
.createNewFeatureType()); |
222 |
|
223 |
|
224 |
return params;
|
225 |
} |
226 |
|
227 |
/* (non-Javadoc)
|
228 |
* @see org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorer#getSQLForList(int, boolean)
|
229 |
*/
|
230 |
protected List getSQLForList(int mode, boolean showInformationDBTables) { |
231 |
List list = new ArrayList(1); |
232 |
String sql="SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA<>'INFORMATION_SCHEMA' AND TABLE_NAME NOT LIKE '%_HATBOX' AND EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%_HATBOX')"; |
233 |
list.add(sql); |
234 |
return list;
|
235 |
|
236 |
} |
237 |
/* (non-Javadoc)
|
238 |
* @see org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorer#add(org.gvsig.fmap.dal.NewDataStoreParameters, boolean)
|
239 |
*/
|
240 |
public boolean add(NewDataStoreParameters ndsp, boolean overwrite) |
241 |
throws DataException {
|
242 |
|
243 |
//crea el fichero de la bbdd y la hace espacial
|
244 |
if (!initialized){
|
245 |
this.initializedb();
|
246 |
initialized=true;
|
247 |
} |
248 |
|
249 |
if (!(ndsp instanceof NewFeatureStoreParameters)) { |
250 |
throw new IllegalArgumentException(); |
251 |
} |
252 |
checkIsMine(ndsp); |
253 |
|
254 |
NewFeatureStoreParameters nfdsp = (NewFeatureStoreParameters) ndsp; |
255 |
|
256 |
StringBuilder sql = new StringBuilder(); |
257 |
|
258 |
if (!nfdsp.isValid()) {
|
259 |
throw new InitializeException(this.getName(), new Exception( |
260 |
"Parameters not valid"));
|
261 |
} |
262 |
try {
|
263 |
nfdsp.validate(); |
264 |
} catch (ValidateDataParametersException e1) {
|
265 |
throw new InitializeException(this.getName(), e1); |
266 |
} |
267 |
|
268 |
FeatureType fType = nfdsp.getDefaultFeatureType(); |
269 |
|
270 |
|
271 |
|
272 |
sql.append("Create table " + ((JDBCStoreParameters) ndsp).tableID().toUpperCase()
|
273 |
+ "(");
|
274 |
Iterator attrs = fType.iterator();
|
275 |
String sqlAttr;
|
276 |
List sqlAttrs = new ArrayList(); |
277 |
|
278 |
while (attrs.hasNext()) {
|
279 |
sqlAttr = helper.getSqlFieldDescription((FeatureAttributeDescriptor) attrs |
280 |
.next()); |
281 |
if (sqlAttr != null) { |
282 |
sqlAttrs.add(sqlAttr.toUpperCase()); |
283 |
} |
284 |
} |
285 |
sqlAttrs.add("THE_GEOM"+ " "+ "BLOB"); |
286 |
|
287 |
helper.stringJoin(sqlAttrs, ", ", sql);
|
288 |
sql.append(")");
|
289 |
final String sqlDrop="DROP TABLE IF EXISTS " + ((JDBCStoreParameters) ndsp).tableID().toUpperCase(); |
290 |
final String sqlDropHatBox="DROP TABLE IF EXISTS " + ((JDBCStoreParameters) ndsp).tableID().toUpperCase()+"_HATBOX"; |
291 |
final String sqlCreate = sql.toString(); |
292 |
final List sqlAdditional = helper |
293 |
.getAdditionalSqlToCreate(ndsp, fType); |
294 |
final String catalog=((JDBCStoreParameters) ndsp).getCatalog(); |
295 |
final String schema=((JDBCStoreParameters) ndsp).getSchema(); |
296 |
final String table=((JDBCStoreParameters) ndsp).getTable().toUpperCase(); |
297 |
final String geomCol=fType.getDefaultGeometryAttributeName().toUpperCase(); |
298 |
final String geomType="GEOMETRY"; |
299 |
String epsgAux = null; |
300 |
String abrev= fType.getDefaultGeometryAttribute().getSRS().getAbrev();
|
301 |
try{
|
302 |
epsgAux =abrev.substring(abrev.indexOf(":")+1, abrev.length()); |
303 |
}catch (Exception e) { |
304 |
e.printStackTrace(); |
305 |
} |
306 |
final String epsg=epsgAux; |
307 |
TransactionalAction action = new TransactionalAction() {
|
308 |
|
309 |
public boolean continueTransactionAllowed() { |
310 |
return false; |
311 |
} |
312 |
|
313 |
public Object action(Connection conn) throws DataException { |
314 |
Statement st = null; |
315 |
try {
|
316 |
st = conn.createStatement(); |
317 |
} catch (SQLException e1) { |
318 |
throw new JDBCSQLException(e1); |
319 |
} |
320 |
String sql = null; |
321 |
|
322 |
try {
|
323 |
sql = sqlCreate; |
324 |
st.execute(sqlDrop); |
325 |
st.execute(sqlDropHatBox); |
326 |
st.execute(sql); |
327 |
if (sqlAdditional != null) { |
328 |
Iterator iter = sqlAdditional.iterator();
|
329 |
while (iter.hasNext()) {
|
330 |
sql = (String) iter.next();
|
331 |
st.execute(sql); |
332 |
} |
333 |
} |
334 |
} catch (SQLException e) { |
335 |
throw new JDBCExecuteSQLException(sql, e); |
336 |
} finally {
|
337 |
try {
|
338 |
st.close(); |
339 |
|
340 |
} catch (SQLException e) { |
341 |
logger.error("Exception clossing statement", e);
|
342 |
} |
343 |
} |
344 |
return Boolean.TRUE; |
345 |
} |
346 |
|
347 |
}; |
348 |
|
349 |
Boolean result = Boolean.FALSE; |
350 |
|
351 |
try {
|
352 |
result = (Boolean) helper.doConnectionAction(action);
|
353 |
Proc.spatialize(helper.getConnection(), schema, table, geomCol, geomType, epsg, "false", "49"); |
354 |
Proc.buildIndex(helper.getConnection(), schema, table, 100, null); |
355 |
} catch (Exception e) { |
356 |
throw new RuntimeException(e); |
357 |
} |
358 |
|
359 |
return result.booleanValue();
|
360 |
} |
361 |
private void initializedb(){ |
362 |
Connection conn = null; |
363 |
try {
|
364 |
Class.forName("org.h2.Driver"); |
365 |
conn = getHelper().getConnection();//DriverManager.getConnection("jdbc:h2:"+System.getProperty("user.home")+File.separator+"gvSIG"+File.separator+"cache"+File.separator+"cache", null, null);
|
366 |
|
367 |
StringBuilder strb = new StringBuilder(); |
368 |
strb.append("DROP ALL OBJECTS");
|
369 |
|
370 |
String sqlDeleteTable = strb.toString();
|
371 |
|
372 |
Statement st;
|
373 |
try{
|
374 |
st = conn.createStatement(); |
375 |
st.execute(sqlDeleteTable); |
376 |
} catch (SQLException e) { |
377 |
throw new JDBCExecuteSQLException(sqlDeleteTable, e); |
378 |
} |
379 |
|
380 |
} catch (Exception e) { |
381 |
e.printStackTrace(); |
382 |
} |
383 |
//si no est? la bd espacializada se espacializa
|
384 |
if(!determineDbStatus(conn)){
|
385 |
spatializedb(conn); |
386 |
} |
387 |
} |
388 |
|
389 |
private void spatializedb(Connection conn) { |
390 |
Statement stmt = null; |
391 |
try {
|
392 |
stmt = conn.createStatement(); |
393 |
for (int i = 0; i < h2Alias.length; i++) { |
394 |
stmt.executeUpdate("create alias " + h2Alias[i] + " for \"" |
395 |
+ h2AliasMethod[i] + '"');
|
396 |
} |
397 |
stmt.close(); |
398 |
} catch (SQLException e) { |
399 |
e.printStackTrace(); |
400 |
} |
401 |
|
402 |
} |
403 |
|
404 |
private boolean determineDbStatus(Connection conn) { |
405 |
boolean spatialized = false; |
406 |
Statement stmt = null; |
407 |
ResultSet rs = null; |
408 |
try {
|
409 |
stmt = conn.createStatement(); |
410 |
rs = stmt.executeQuery("select ALIAS_NAME from INFORMATION_SCHEMA.FUNCTION_ALIASES "
|
411 |
+ "where ALIAS_SCHEMA = 'PUBLIC' and ALIAS_NAME like 'HATBOX%'");
|
412 |
Set<String> aliasSet = new HashSet<String>(); |
413 |
for (String alias : h2Alias) { |
414 |
aliasSet.add(alias); |
415 |
} |
416 |
while (rs.next()) {
|
417 |
aliasSet.remove(rs.getString(1));
|
418 |
} |
419 |
spatialized = (aliasSet.size() == 0);
|
420 |
} catch (SQLException e) { |
421 |
e.printStackTrace(); |
422 |
} |
423 |
return spatialized;
|
424 |
} |
425 |
|
426 |
} |