gvsig-mssqlserver / trunk / org.gvsig.mssqlserver / org.gvsig.mssqlserver.provider / src / main / java / org / gvsig / mssqlserver / dal / MSSQLServerHelper.java @ 165
History | View | Annotate | Download (14 KB)
1 |
/* gvSIG. Geographic Information System of the Valencian Government
|
---|---|
2 |
*
|
3 |
* Copyright (C) 2007-2016 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 3
|
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 |
package org.gvsig.mssqlserver.dal; |
23 |
|
24 |
import org.gvsig.mssqlserver.dal.operations.MSSQLServerOperationsFactory; |
25 |
import java.sql.Connection; |
26 |
import java.sql.ResultSet; |
27 |
import java.sql.SQLException; |
28 |
import java.sql.Statement; |
29 |
import java.text.MessageFormat; |
30 |
import java.util.ArrayList; |
31 |
import java.util.List; |
32 |
import org.apache.commons.dbcp.BasicDataSource; |
33 |
import org.apache.commons.lang3.StringUtils; |
34 |
import org.gvsig.fmap.dal.DataParameters; |
35 |
import org.gvsig.fmap.dal.DataTypes; |
36 |
import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
37 |
import org.gvsig.fmap.dal.feature.FeatureType; |
38 |
import org.gvsig.fmap.dal.SQLBuilder; |
39 |
import org.gvsig.fmap.dal.resource.exception.AccessResourceException; |
40 |
import org.gvsig.fmap.dal.store.jdbc.JDBCConnectionParameters; |
41 |
import org.gvsig.fmap.dal.store.jdbc.JDBCNewStoreParameters; |
42 |
import org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorerParameters; |
43 |
import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters; |
44 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCDriverClassNotFoundException; |
45 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException; |
46 |
import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils; |
47 |
import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory; |
48 |
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCHelperBase; |
49 |
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
50 |
import org.gvsig.fmap.geom.Geometry; |
51 |
import org.gvsig.fmap.geom.primitive.Envelope; |
52 |
import org.slf4j.Logger; |
53 |
import org.slf4j.LoggerFactory; |
54 |
|
55 |
public class MSSQLServerHelper extends JDBCHelperBase { |
56 |
|
57 |
private static final Logger logger = LoggerFactory.getLogger(MSSQLServerHelper.class); |
58 |
|
59 |
public static final String NAME = "MSSQLServer"; |
60 |
public static final String INSTANCE_NAME = "SQLEXPRESS"; |
61 |
public static final int PORT = 1433; |
62 |
public static final String MSSQLServerJDBCDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; |
63 |
|
64 |
// private static final boolean ALLOW_AUTOMATIC_VALUES = true;
|
65 |
// private static final String QUOTE_FOR_USE_IN_IDENTIFIERS = "\"";
|
66 |
// private static final String QUOTE_FOR_USE_IN_STRINGS = "'";
|
67 |
|
68 |
public static String getConnectionURL(JDBCConnectionParameters params) { |
69 |
String connectionURL = MessageFormat.format( |
70 |
//"jdbc:sqlserver://{0}:{1,number,#};databaseName={2};instanceName={3};",
|
71 |
"jdbc:sqlserver://{0};databaseName={2};instanceName={3};",
|
72 |
params.getHost(), |
73 |
params.getPort(), |
74 |
params.getDBName(), |
75 |
((DataParameters) params).getDynValue("instanceName")
|
76 |
); |
77 |
logger.debug("connectionURL: {}", connectionURL);
|
78 |
return connectionURL;
|
79 |
} |
80 |
|
81 |
private static class ConnectionProvider { |
82 |
|
83 |
private static boolean needRegisterDriver = true; |
84 |
|
85 |
private BasicDataSource dataSource = null; |
86 |
|
87 |
private final JDBCConnectionParameters connectionParameters; |
88 |
|
89 |
public ConnectionProvider(JDBCConnectionParameters connectionParameters) {
|
90 |
this.connectionParameters = connectionParameters;
|
91 |
} |
92 |
|
93 |
public Connection getConnection() throws SQLException { |
94 |
if (this.dataSource == null) { |
95 |
this.dataSource = this.createDataSource(); |
96 |
} |
97 |
Connection conn = this.dataSource.getConnection(); |
98 |
return conn;
|
99 |
} |
100 |
|
101 |
private BasicDataSource createDataSource() throws SQLException { |
102 |
if (!this.isRegistered()) { |
103 |
this.registerDriver();
|
104 |
} |
105 |
JDBCConnectionParameters params = connectionParameters; |
106 |
|
107 |
BasicDataSource dataSource = new BasicDataSource();
|
108 |
dataSource.setDriverClassName(params.getJDBCDriverClassName()); |
109 |
dataSource.setUsername(params.getUser()); |
110 |
dataSource.setPassword(params.getPassword()); |
111 |
dataSource.setUrl(params.getUrl()); |
112 |
|
113 |
dataSource.setMaxWait(60L * 1000); |
114 |
return dataSource;
|
115 |
} |
116 |
|
117 |
private boolean isRegistered() { |
118 |
return needRegisterDriver;
|
119 |
} |
120 |
|
121 |
public void registerDriver() throws SQLException { |
122 |
String className = this.connectionParameters.getJDBCDriverClassName(); |
123 |
if (className == null) { |
124 |
return;
|
125 |
} |
126 |
try {
|
127 |
Class theClass = Class.forName(className); |
128 |
if (theClass == null) { |
129 |
throw new JDBCDriverClassNotFoundException(MSSQLServerLibrary.NAME, className); |
130 |
} |
131 |
} catch (Exception e) { |
132 |
throw new SQLException("Can't register JDBC driver '" + className + "'.", e); |
133 |
} |
134 |
needRegisterDriver = false;
|
135 |
} |
136 |
|
137 |
} |
138 |
|
139 |
private ConnectionProvider connectionProvider = null; |
140 |
|
141 |
private FeatureType lastUsedFeatureType = null; |
142 |
private String lastUsedSpatialType = null; |
143 |
|
144 |
public MSSQLServerHelper(JDBCConnectionParameters connectionParameters) {
|
145 |
super(connectionParameters);
|
146 |
} |
147 |
|
148 |
@Override
|
149 |
public Connection getConnection() throws AccessResourceException { |
150 |
try {
|
151 |
if (this.connectionProvider == null) { |
152 |
this.connectionProvider = new ConnectionProvider(this.getConnectionParameters()); |
153 |
} |
154 |
return this.connectionProvider.getConnection(); |
155 |
} catch (SQLException ex) { |
156 |
throw new AccessResourceException(MSSQLServerLibrary.NAME, ex); |
157 |
} |
158 |
} |
159 |
|
160 |
@Override
|
161 |
public String getConnectionURL() { |
162 |
return getConnectionURL(this.getConnectionParameters()); |
163 |
} |
164 |
|
165 |
@Override
|
166 |
protected String getResourceType() { |
167 |
return MSSQLServerLibrary.NAME;
|
168 |
} |
169 |
|
170 |
@Override
|
171 |
public String getProviderName() { |
172 |
return MSSQLServerLibrary.NAME;
|
173 |
} |
174 |
|
175 |
@Override
|
176 |
public JDBCSQLBuilderBase createSQLBuilder() {
|
177 |
return new MSSQLServerSQLBuilder(this); |
178 |
} |
179 |
|
180 |
/**
|
181 |
* Devuelbe el nombre del tipo de datos espacial de SQLServer asociado
|
182 |
* al ultimo FeatureType cargado.
|
183 |
*
|
184 |
* Esta funcion es usada para dar un soporte limitado al tipo de datos
|
185 |
* spaciales "geography".
|
186 |
*
|
187 |
* Si no disponemos de un FeatureType asumimos "geometry".
|
188 |
* Si hay un solo campo espacial asume el tipo de ese campo, y si
|
189 |
* hay mas de uno asume "geometry".
|
190 |
* En caso de que no haya ningun campo espacial asumimos "geometry".
|
191 |
*
|
192 |
* @return "geometry" or "geography" for the last used table.
|
193 |
*/
|
194 |
public String getSpatialType() { |
195 |
if( this.lastUsedSpatialType != null ) { |
196 |
return this.lastUsedSpatialType; |
197 |
} |
198 |
if( this.lastUsedFeatureType == null ) { |
199 |
this.lastUsedSpatialType = "geometry"; |
200 |
return this.lastUsedSpatialType; |
201 |
} |
202 |
String spatialType = null; |
203 |
for (FeatureAttributeDescriptor attr : lastUsedFeatureType) {
|
204 |
if( attr.getType() == DataTypes.GEOMETRY ) {
|
205 |
if( spatialType != null ) { |
206 |
this.lastUsedSpatialType = "geometry"; |
207 |
return this.lastUsedSpatialType; |
208 |
} |
209 |
spatialType = (String) attr.getAdditionalInfo("SQLServer_type_name"); |
210 |
} |
211 |
} |
212 |
if( StringUtils.isEmpty(spatialType) ) {
|
213 |
this.lastUsedSpatialType = "geometry"; |
214 |
return this.lastUsedSpatialType; |
215 |
} |
216 |
this.lastUsedSpatialType = spatialType;
|
217 |
return this.lastUsedSpatialType; |
218 |
} |
219 |
|
220 |
/**
|
221 |
* Devuelbe el tipo de datos espacial de SQLServer asociado a la columna
|
222 |
* indicada.
|
223 |
*
|
224 |
* Esta funcion es usada para dar un soporte limitado al tipo de datos
|
225 |
* spaciales "geography".
|
226 |
*
|
227 |
* Si no disponemos de un FeatureType asumimos "geometry".
|
228 |
* Si no existe el campo solicitado o no es de tipo geoemtria, asumimos
|
229 |
* el tipo espacia asociado al FeatureType (getSpatialType()).
|
230 |
*
|
231 |
* @param columnName
|
232 |
* @return "geometry" or "geography" for column in the last used table.
|
233 |
*/
|
234 |
public String getSpatialType(String columnName) { |
235 |
if( this.lastUsedFeatureType == null ) { |
236 |
return "geometry"; |
237 |
} |
238 |
FeatureAttributeDescriptor attr = this.lastUsedFeatureType.getAttributeDescriptor(columnName);
|
239 |
if( attr == null ) { |
240 |
return this.getSpatialType(); |
241 |
} |
242 |
if( attr.getType() != DataTypes.GEOMETRY ) {
|
243 |
return this.getSpatialType(); |
244 |
} |
245 |
String spatialType = (String) attr.getAdditionalInfo("SQLServer_type_name"); |
246 |
if( StringUtils.isEmpty(spatialType) ) {
|
247 |
return "geometry"; |
248 |
} |
249 |
return spatialType;
|
250 |
} |
251 |
|
252 |
public void setLastUsedFeatureType(FeatureType featureType) { |
253 |
this.lastUsedSpatialType = null; |
254 |
this.lastUsedFeatureType = featureType;
|
255 |
} |
256 |
|
257 |
public void createOrUpdateSpatialIndex( |
258 |
Connection conn,
|
259 |
String database,
|
260 |
String schema,
|
261 |
String table,
|
262 |
String columnName
|
263 |
) throws JDBCSQLException {
|
264 |
List<String> sqls = this.createOrUpdateSpatialIndexSql( |
265 |
conn, database, schema, table, columnName |
266 |
); |
267 |
Statement st = null; |
268 |
try {
|
269 |
st = conn.createStatement(); |
270 |
for (String sql : sqls) { |
271 |
JDBCUtils.execute(st, sql); |
272 |
} |
273 |
} catch (SQLException ex) { |
274 |
throw new JDBCSQLException(ex); |
275 |
} finally {
|
276 |
JDBCUtils.closeQuietly(st); |
277 |
} |
278 |
|
279 |
} |
280 |
|
281 |
public List<String> createOrUpdateSpatialIndexSql( |
282 |
Connection conn,
|
283 |
String database,
|
284 |
String schema,
|
285 |
String table,
|
286 |
String columnName
|
287 |
) throws JDBCSQLException {
|
288 |
JDBCSQLBuilderBase sqlbuilder = createSQLBuilder(); |
289 |
MSSQLServerSQLBuilder.MSSQLServerCreateIndexBuilder create_index = (MSSQLServerSQLBuilder.MSSQLServerCreateIndexBuilder) sqlbuilder.create_index(); |
290 |
|
291 |
sqlbuilder.create_index().spatial(); |
292 |
create_index.setBoundingBox( |
293 |
this.getBoundingBox(
|
294 |
conn, |
295 |
database, |
296 |
schema, |
297 |
table, |
298 |
columnName |
299 |
) |
300 |
); |
301 |
create_index.if_not_exist(); |
302 |
create_index.name("idx_" + table + "_" + columnName); |
303 |
create_index.column(columnName); |
304 |
create_index.table().database(database).schema(schema).name(table); |
305 |
|
306 |
return create_index.toStrings();
|
307 |
} |
308 |
|
309 |
public Envelope getBoundingBox(
|
310 |
Connection conn,
|
311 |
String database,
|
312 |
String schema,
|
313 |
String table,
|
314 |
String columnName
|
315 |
) throws JDBCSQLException {
|
316 |
JDBCSQLBuilderBase sqlbuilder = createSQLBuilder(); |
317 |
sqlbuilder.select().column().value( |
318 |
sqlbuilder.getAsGeometry( |
319 |
sqlbuilder.ST_ExtentAggregate( |
320 |
sqlbuilder.column(columnName) |
321 |
) |
322 |
) |
323 |
); |
324 |
sqlbuilder.select().from().table().database(database).schema(schema).name(table); |
325 |
|
326 |
String sql = sqlbuilder.select().toString();
|
327 |
Statement st = null; |
328 |
ResultSet rs = null; |
329 |
try {
|
330 |
st = conn.createStatement(); |
331 |
rs = JDBCUtils.executeQuery(st, sql); |
332 |
if (!rs.next()) {
|
333 |
return null; |
334 |
} |
335 |
Geometry geom = this.getGeometryFromColumn(rs, 1); |
336 |
if (geom == null) { |
337 |
return null; |
338 |
} |
339 |
return geom.getEnvelope();
|
340 |
|
341 |
} catch (Exception ex) { |
342 |
throw new JDBCSQLException(ex); |
343 |
} finally {
|
344 |
JDBCUtils.closeQuietly(st); |
345 |
JDBCUtils.closeQuietly(rs); |
346 |
} |
347 |
} |
348 |
|
349 |
@Override
|
350 |
public OperationsFactory getOperations() {
|
351 |
if (this.operationsFactory == null) { |
352 |
this.operationsFactory = new MSSQLServerOperationsFactory(this); |
353 |
} |
354 |
return operationsFactory;
|
355 |
} |
356 |
|
357 |
@Override
|
358 |
public SQLBuilder.GeometrySupportType getGeometrySupportType() {
|
359 |
return SQLBuilder.GeometrySupportType.WKB;
|
360 |
} |
361 |
|
362 |
@Override
|
363 |
public boolean hasSpatialFunctions() { |
364 |
return true; |
365 |
} |
366 |
|
367 |
@Override
|
368 |
public boolean canWriteGeometry(int geometryType, int geometrySubtype) { |
369 |
return true; |
370 |
} |
371 |
|
372 |
@Override
|
373 |
public String getQuoteForIdentifiers() { |
374 |
return "\""; |
375 |
} |
376 |
|
377 |
@Override
|
378 |
public boolean allowAutomaticValues() { |
379 |
return true; |
380 |
} |
381 |
|
382 |
@Override
|
383 |
public boolean supportOffsetInSelect() { |
384 |
return true; |
385 |
} |
386 |
|
387 |
@Override
|
388 |
public String getQuoteForStrings() { |
389 |
return "'"; |
390 |
} |
391 |
|
392 |
@Override
|
393 |
public String getSourceId(JDBCStoreParameters parameters) { |
394 |
return parameters.getHost() + ":" + |
395 |
parameters.getDynValue("InstanceName")+ ":" + |
396 |
parameters.getDBName() + "." +
|
397 |
parameters.getSchema()+ "." +
|
398 |
parameters.getTable(); |
399 |
} |
400 |
|
401 |
@Override
|
402 |
public JDBCNewStoreParameters createNewStoreParameters() {
|
403 |
return new MSSQLServerNewStoreParameters(); |
404 |
} |
405 |
|
406 |
@Override
|
407 |
public JDBCStoreParameters createOpenStoreParameters() {
|
408 |
return new MSSQLServerStoreParameters(); |
409 |
} |
410 |
|
411 |
@Override
|
412 |
public JDBCServerExplorerParameters createServerExplorerParameters() {
|
413 |
return new MSSQLServerExplorerParameters(); |
414 |
} |
415 |
|
416 |
} |