root / branches / v2_0_0_prep / libraries / libFMap_daldb / src / org / gvsig / fmap / dal / store / mysql / MySQLHelper.java @ 33331
History | View | Annotate | Download (11.4 KB)
1 |
/* gvSIG. Geographic Information System of the Valencian Government
|
---|---|
2 |
*
|
3 |
* Copyright (C) 2007-2008 Infrastructures and Transports Department
|
4 |
* of the Valencian Government (CIT)
|
5 |
*
|
6 |
* This program is free software; you can redistribute it and/or
|
7 |
* modify it under the terms of the GNU General Public License
|
8 |
* as published by the Free Software Foundation; either version 2
|
9 |
* of the License, or (at your option) any later version.
|
10 |
*
|
11 |
* This program is distributed in the hope that it will be useful,
|
12 |
* but WITHOUT ANY WARRANTY; without even the implied warranty of
|
13 |
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
14 |
* GNU General Public License for more details.
|
15 |
*
|
16 |
* You should have received a copy of the GNU General Public License
|
17 |
* along with this program; if not, write to the Free Software
|
18 |
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
|
19 |
* MA 02110-1301, USA.
|
20 |
*
|
21 |
*/
|
22 |
|
23 |
/*
|
24 |
* AUTHORS (In addition to CIT):
|
25 |
* 2009 IVER T.I {{Task}}
|
26 |
*/
|
27 |
|
28 |
/**
|
29 |
*
|
30 |
*/
|
31 |
package org.gvsig.fmap.dal.store.mysql; |
32 |
|
33 |
import java.sql.Connection; |
34 |
import java.sql.ResultSet; |
35 |
import java.sql.ResultSetMetaData; |
36 |
import java.sql.SQLException; |
37 |
import java.sql.Statement; |
38 |
|
39 |
import org.gvsig.fmap.dal.DALLocator; |
40 |
import org.gvsig.fmap.dal.DataTypes; |
41 |
import org.gvsig.fmap.dal.exception.DataException; |
42 |
import org.gvsig.fmap.dal.exception.InitializeException; |
43 |
import org.gvsig.fmap.dal.exception.ReadException; |
44 |
import org.gvsig.fmap.dal.feature.EditableFeatureAttributeDescriptor; |
45 |
import org.gvsig.fmap.dal.feature.EditableFeatureType; |
46 |
import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
47 |
import org.gvsig.fmap.dal.feature.exception.UnsupportedDataTypeException; |
48 |
import org.gvsig.fmap.dal.resource.ResourceAction; |
49 |
import org.gvsig.fmap.dal.resource.spi.ResourceManagerProviderServices; |
50 |
import org.gvsig.fmap.dal.store.jdbc.JDBCHelper; |
51 |
import org.gvsig.fmap.dal.store.jdbc.JDBCHelperUser; |
52 |
import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters; |
53 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCException; |
54 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCExecuteSQLException; |
55 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException; |
56 |
import org.gvsig.fmap.geom.Geometry; |
57 |
import org.gvsig.fmap.geom.GeometryLocator; |
58 |
import org.gvsig.fmap.geom.operation.fromwkb.FromWKB; |
59 |
import org.gvsig.fmap.geom.operation.fromwkb.FromWKBGeometryOperationContext; |
60 |
import org.gvsig.fmap.geom.primitive.Envelope; |
61 |
import org.gvsig.tools.exception.BaseException; |
62 |
|
63 |
/**
|
64 |
* @author jmvivo
|
65 |
*
|
66 |
*/
|
67 |
public class MySQLHelper extends JDBCHelper { |
68 |
|
69 |
MySQLHelper(JDBCHelperUser consumer, |
70 |
MySQLConnectionParameters params) |
71 |
throws InitializeException {
|
72 |
|
73 |
super(consumer, params);
|
74 |
} |
75 |
|
76 |
protected void initializeResource() throws InitializeException { |
77 |
ResourceManagerProviderServices manager = (ResourceManagerProviderServices) DALLocator |
78 |
.getResourceManager(); |
79 |
MySQLResource resource = (MySQLResource) manager |
80 |
.createAddResource( |
81 |
MySQLResource.NAME, new Object[] { |
82 |
params.getUrl(), params.getHost(), |
83 |
params.getPort(), params.getDBName(), params.getUser(), |
84 |
params.getPassword(), |
85 |
params.getJDBCDriverClassName(), |
86 |
((MySQLConnectionParameters) params).getUseSSL() }); |
87 |
this.setResource(resource);
|
88 |
} |
89 |
|
90 |
|
91 |
protected String getDefaultSchema(Connection conn) |
92 |
throws JDBCException {
|
93 |
if (defaultSchema == null) { |
94 |
defaultSchema = params.getDBName(); |
95 |
} |
96 |
|
97 |
return defaultSchema;
|
98 |
} |
99 |
|
100 |
public Envelope getFullEnvelopeOfField(
|
101 |
JDBCStoreParameters storeParams, |
102 |
String geometryAttrName, Envelope limit)
|
103 |
throws DataException {
|
104 |
|
105 |
StringBuilder strb = new StringBuilder(); |
106 |
strb.append("Select asbinary(envelope(");
|
107 |
strb.append(geometryAttrName); |
108 |
strb.append(")) from ");
|
109 |
|
110 |
if (storeParams.getSQL() != null |
111 |
&& storeParams.getSQL().trim().length() == 0) {
|
112 |
strb.append('(');
|
113 |
strb.append(storeParams.getSQL()); |
114 |
strb.append(") as __tmp__ ");
|
115 |
} else {
|
116 |
strb.append(storeParams.tableID()); |
117 |
} |
118 |
|
119 |
|
120 |
if (limit != null){ |
121 |
strb.append(" where intersects(GeomFromText('");
|
122 |
strb.append(limit.toString()); |
123 |
strb.append("')), boundary(");
|
124 |
strb.append(geometryAttrName); |
125 |
strb.append(")) ");
|
126 |
} |
127 |
|
128 |
final String sql = strb.toString(); |
129 |
|
130 |
try {
|
131 |
initializeFromWKBOperation(); |
132 |
} catch (BaseException e1) {
|
133 |
throw new ReadException(this.name, e1); |
134 |
} |
135 |
|
136 |
this.open();
|
137 |
return (Envelope) getResource().execute(new ResourceAction() { |
138 |
public Object run() throws Exception { |
139 |
ResultSet rs = null; |
140 |
Statement st = null; |
141 |
String schema = null; |
142 |
Connection conn = null; |
143 |
Envelope fullEnvelope = null;
|
144 |
try {
|
145 |
conn = getConnection(); |
146 |
st = conn.createStatement(); |
147 |
try {
|
148 |
rs = st.executeQuery(sql); |
149 |
} catch (java.sql.SQLException e) {
|
150 |
throw new JDBCExecuteSQLException(sql, e); |
151 |
} |
152 |
while (rs.next()) {
|
153 |
|
154 |
byte[] data = rs.getBytes(1); |
155 |
if (data == null) { |
156 |
continue;
|
157 |
} |
158 |
|
159 |
fromWKBContext.setData(data); |
160 |
Geometry geom = |
161 |
(Geometry) fromWKB.invoke(null, fromWKBContext);
|
162 |
|
163 |
if (fullEnvelope == null) { |
164 |
fullEnvelope = geom.getEnvelope(); |
165 |
} else {
|
166 |
fullEnvelope.add(geom.getEnvelope()); |
167 |
} |
168 |
} |
169 |
|
170 |
return fullEnvelope;
|
171 |
} catch (java.sql.SQLException e) {
|
172 |
throw new JDBCSQLException(e); |
173 |
} catch (BaseException e) {
|
174 |
throw new ReadException(user.getName(), e); |
175 |
} finally {
|
176 |
try {
|
177 |
rs.close(); |
178 |
} catch (Exception e) { |
179 |
} |
180 |
try {
|
181 |
st.close(); |
182 |
} catch (Exception e) { |
183 |
} |
184 |
try {
|
185 |
conn.close(); |
186 |
} catch (Exception e) { |
187 |
} |
188 |
rs = null;
|
189 |
st = null;
|
190 |
conn = null;
|
191 |
} |
192 |
} |
193 |
}); |
194 |
} |
195 |
|
196 |
protected void initializeFromWKBOperation() throws BaseException { |
197 |
if (fromWKB == null) { |
198 |
fromWKB = (FromWKB) GeometryLocator.getGeometryManager() |
199 |
.getGeometryOperation(FromWKB.CODE, |
200 |
Geometry.TYPES.GEOMETRY, Geometry.SUBTYPES.GEOM2D); |
201 |
fromWKBContext = new FromWKBGeometryOperationContext();
|
202 |
|
203 |
} |
204 |
} |
205 |
|
206 |
public Geometry getGeometry(byte[] buffer) throws BaseException { |
207 |
if (buffer == null) { |
208 |
return null; |
209 |
} |
210 |
initializeFromWKBOperation(); |
211 |
Geometry geom; |
212 |
try {
|
213 |
fromWKBContext.setData(buffer); |
214 |
|
215 |
geom = (Geometry) fromWKB.invoke(null, fromWKBContext);
|
216 |
} finally {
|
217 |
fromWKBContext.setData(null);
|
218 |
} |
219 |
return geom;
|
220 |
} |
221 |
|
222 |
public String getSqlColumnTypeDescription(FeatureAttributeDescriptor attr) { |
223 |
|
224 |
switch (attr.getType()) {
|
225 |
case DataTypes.STRING:
|
226 |
return "VARCHAR(" + attr.getSize() + ")"; |
227 |
case DataTypes.BOOLEAN:
|
228 |
return "BOOL"; |
229 |
|
230 |
case DataTypes.BYTE:
|
231 |
return "TINYINT UNSIGNED"; |
232 |
|
233 |
case DataTypes.DATE:
|
234 |
return "DATE"; |
235 |
|
236 |
case DataTypes.TIMESTAMP:
|
237 |
return "TIMESTAMP"; |
238 |
|
239 |
case DataTypes.TIME:
|
240 |
return "TIME"; |
241 |
|
242 |
case DataTypes.BYTEARRAY:
|
243 |
if (attr.getSize() > 0) { |
244 |
return "BLOB(" + attr.getSize() + ")"; |
245 |
} else {
|
246 |
return "BLOB"; |
247 |
} |
248 |
|
249 |
case DataTypes.DOUBLE:
|
250 |
if (attr.getSize() > 0) { |
251 |
return "DOUBLE(" + attr.getSize() + "," + attr.getPrecision() |
252 |
+ ")";
|
253 |
} else {
|
254 |
return "DOBLE"; |
255 |
} |
256 |
case DataTypes.FLOAT:
|
257 |
return "FLOAT"; |
258 |
|
259 |
case DataTypes.GEOMETRY:
|
260 |
switch (attr.getGeometryType()) {
|
261 |
case Geometry.TYPES.POINT:
|
262 |
return "POINT"; |
263 |
case Geometry.TYPES.CURVE:
|
264 |
return "LINESTRING"; |
265 |
case Geometry.TYPES.SURFACE:
|
266 |
return "SURFACE"; |
267 |
case Geometry.TYPES.SOLID:
|
268 |
return "POLYGON"; |
269 |
|
270 |
case Geometry.TYPES.MULTIPOINT:
|
271 |
return "MULTIPOIN"; |
272 |
case Geometry.TYPES.MULTICURVE:
|
273 |
return "MULTILINESTRING"; |
274 |
case Geometry.TYPES.MULTISURFACE:
|
275 |
return "MULTISURFACE"; |
276 |
case Geometry.TYPES.MULTISOLID:
|
277 |
return "MULTIPOLYGON"; |
278 |
|
279 |
default:
|
280 |
return "GEOMETRY"; |
281 |
} |
282 |
case DataTypes.INT:
|
283 |
if (attr.getSize() > 0) { |
284 |
return "INT(" + attr.getSize() + ")"; |
285 |
} |
286 |
case DataTypes.LONG:
|
287 |
return "BIGINT"; |
288 |
|
289 |
default:
|
290 |
String typeName = (String) attr.getAdditionalInfo("SQLTypeName"); |
291 |
if (typeName != null) { |
292 |
return typeName;
|
293 |
} |
294 |
|
295 |
throw new UnsupportedDataTypeException(attr.getDataTypeName(), attr |
296 |
.getType()); |
297 |
} |
298 |
} |
299 |
|
300 |
|
301 |
public String getSqlFieldName(FeatureAttributeDescriptor attribute) { |
302 |
if (attribute.getType() == DataTypes.GEOMETRY) {
|
303 |
return "asBinary(" + super.getSqlFieldName(attribute) + ")"; |
304 |
} |
305 |
return super.getSqlFieldName(attribute); |
306 |
} |
307 |
|
308 |
protected EditableFeatureAttributeDescriptor createAttributeFromJDBC(
|
309 |
EditableFeatureType type, Connection conn,
|
310 |
ResultSetMetaData rsMetadata, int colIndex) throws SQLException { |
311 |
int colType = rsMetadata.getColumnType(colIndex);
|
312 |
if (colType == java.sql.Types.OTHER || colType == java.sql.Types.STRUCT
|
313 |
|| colType == java.sql.Types.BLOB |
314 |
|| colType == java.sql.Types.BINARY) { |
315 |
Integer geoType = null; |
316 |
|
317 |
if (rsMetadata.getColumnTypeName(colIndex).equalsIgnoreCase(
|
318 |
"geometry")) {
|
319 |
geoType = new Integer(Geometry.TYPES.GEOMETRY); |
320 |
} else if (rsMetadata.getColumnTypeName(colIndex).equalsIgnoreCase("POINT")) { |
321 |
geoType = new Integer(Geometry.TYPES.POINT); |
322 |
} else if (rsMetadata.getColumnTypeName(colIndex).equalsIgnoreCase("LINESTRING")) { |
323 |
geoType = new Integer(Geometry.TYPES.CURVE); |
324 |
} else if (rsMetadata.getColumnTypeName(colIndex).equalsIgnoreCase("SURFACE")) { |
325 |
geoType = new Integer(Geometry.TYPES.SURFACE); |
326 |
} else if (rsMetadata.getColumnTypeName(colIndex).equalsIgnoreCase("POLYGON")) { |
327 |
geoType = new Integer(Geometry.TYPES.SOLID); |
328 |
} else if (rsMetadata.getColumnTypeName(colIndex).equalsIgnoreCase("MULTIPOIN")) { |
329 |
geoType = new Integer(Geometry.TYPES.MULTIPOINT); |
330 |
} else if (rsMetadata.getColumnTypeName(colIndex).equalsIgnoreCase("MULTILINESTRING")) { |
331 |
geoType = new Integer(Geometry.TYPES.MULTICURVE); |
332 |
} else if (rsMetadata.getColumnTypeName(colIndex).equalsIgnoreCase( |
333 |
"MULTISURFACE")) {
|
334 |
geoType = new Integer(Geometry.TYPES.MULTISURFACE); |
335 |
} else if (rsMetadata.getColumnTypeName(colIndex).equalsIgnoreCase( |
336 |
"MULTIPOLYGON")) {
|
337 |
geoType = new Integer(Geometry.TYPES.MULTISOLID); |
338 |
} |
339 |
if (geoType != null){ |
340 |
EditableFeatureAttributeDescriptor attr = type.add(rsMetadata |
341 |
.getColumnName(colIndex), DataTypes.GEOMETRY); |
342 |
attr.setGeometryType(geoType.intValue()); |
343 |
attr.setGeometrySubType(Geometry.SUBTYPES.GEOM2D); |
344 |
|
345 |
return attr;
|
346 |
} |
347 |
|
348 |
} |
349 |
|
350 |
return super.createAttributeFromJDBC(type, conn, rsMetadata, colIndex); |
351 |
} |
352 |
|
353 |
public boolean allowAutomaticValues() { |
354 |
return Boolean.TRUE; |
355 |
} |
356 |
|
357 |
|
358 |
public String getSqlFieldDescription(FeatureAttributeDescriptor attr) |
359 |
throws DataException {
|
360 |
|
361 |
/**
|
362 |
column_definition:
|
363 |
data_type [NOT NULL | NULL] [DEFAULT default_value]
|
364 |
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
|
365 |
[COMMENT 'string'] [reference_definition]
|
366 |
|
367 |
*/
|
368 |
|
369 |
StringBuilder strb = new StringBuilder(); |
370 |
// name
|
371 |
strb.append(escapeFieldName(attr.getName())); |
372 |
strb.append(" ");
|
373 |
|
374 |
// Type
|
375 |
strb.append(this.getSqlColumnTypeDescription(attr));
|
376 |
strb.append(" ");
|
377 |
|
378 |
boolean allowNull = attr.allowNull()
|
379 |
&& !(attr.isPrimaryKey() || attr.isAutomatic()); |
380 |
|
381 |
// Null
|
382 |
if (allowNull) {
|
383 |
strb.append("NULL ");
|
384 |
} else {
|
385 |
strb.append("NOT NULL ");
|
386 |
} |
387 |
if (attr.isAutomatic()) {
|
388 |
strb.append("AUTO_INCREMENT ");
|
389 |
} |
390 |
|
391 |
// Default
|
392 |
if (attr.getDefaultValue() == null) { |
393 |
if (allowNull) {
|
394 |
strb.append("DEFAULT NULL ");
|
395 |
} |
396 |
} else {
|
397 |
String value = getDefaltFieldValueString(attr);
|
398 |
strb.append("DEFAULT '");
|
399 |
strb.append(value); |
400 |
strb.append("' ");
|
401 |
} |
402 |
|
403 |
// Primery key
|
404 |
if (attr.isPrimaryKey()) {
|
405 |
strb.append("PRIMARY KEY ");
|
406 |
} |
407 |
return strb.toString();
|
408 |
} |
409 |
|
410 |
public boolean supportOffset() { |
411 |
return true; |
412 |
} |
413 |
|
414 |
public boolean supportsUnion() { |
415 |
return true; |
416 |
} |
417 |
|
418 |
@Override
|
419 |
protected boolean supportsGeometry() { |
420 |
return false; |
421 |
} |
422 |
} |