root / trunk / org.gvsig.postgresql / org.gvsig.postgresql.provider / src / main / java / org / gvsig / postgresql / dal / PostgreSQLHelper.java @ 385
History | View | Annotate | Download (10.9 KB)
1 | 362 | jjdelcerro | |
---|---|---|---|
2 | package org.gvsig.postgresql.dal; |
||
3 | |||
4 | import java.sql.Connection; |
||
5 | import java.sql.PreparedStatement; |
||
6 | import java.sql.SQLException; |
||
7 | import java.util.ArrayList; |
||
8 | import java.util.List; |
||
9 | import org.apache.commons.dbcp.BasicDataSource; |
||
10 | import org.apache.commons.lang3.BooleanUtils; |
||
11 | import org.apache.commons.lang3.StringUtils; |
||
12 | import org.gvsig.fmap.dal.DataTypes; |
||
13 | import org.gvsig.fmap.dal.ExpressionBuilder; |
||
14 | import org.gvsig.fmap.dal.SQLBuilder; |
||
15 | import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
||
16 | import org.gvsig.fmap.dal.feature.FeatureType; |
||
17 | import org.gvsig.fmap.dal.feature.spi.FeatureProvider; |
||
18 | import org.gvsig.fmap.dal.resource.exception.AccessResourceException; |
||
19 | import org.gvsig.fmap.dal.store.jdbc.JDBCConnectionParameters; |
||
20 | import org.gvsig.fmap.dal.store.jdbc.JDBCNewStoreParameters; |
||
21 | import org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorerParameters; |
||
22 | import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters; |
||
23 | import org.gvsig.fmap.dal.store.jdbc.exception.JDBCDriverClassNotFoundException; |
||
24 | import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils; |
||
25 | import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory; |
||
26 | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCHelperBase; |
||
27 | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
||
28 | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSRSsBase; |
||
29 | import org.gvsig.fmap.geom.Geometry; |
||
30 | import org.gvsig.fmap.geom.aggregate.MultiLine; |
||
31 | import org.gvsig.fmap.geom.aggregate.MultiPoint; |
||
32 | import org.gvsig.fmap.geom.aggregate.MultiPolygon; |
||
33 | import org.gvsig.fmap.geom.exception.CreateGeometryException; |
||
34 | import org.gvsig.fmap.geom.primitive.Primitive; |
||
35 | import org.gvsig.fmap.geom.type.GeometryType; |
||
36 | import org.gvsig.postgresql.dal.operations.PostgreSQLOperationsFactory; |
||
37 | import org.slf4j.Logger; |
||
38 | import org.slf4j.LoggerFactory; |
||
39 | |||
40 | public class PostgreSQLHelper extends JDBCHelperBase { |
||
41 | |||
42 | static final Logger logger = LoggerFactory.getLogger(PostgreSQLHelper.class); |
||
43 | |||
44 | public static final String POSTGRESQL_JDBC_DRIVER = "org.postgresql.Driver"; |
||
45 | |||
46 | public static String getConnectionURL(PostgreSQLConnectionParameters params) { |
||
47 | return getConnectionURL(
|
||
48 | params.getHost(), |
||
49 | params.getPort(), |
||
50 | params.getDBName() |
||
51 | ); |
||
52 | } |
||
53 | |||
54 | public static String getConnectionURL(String host, Integer port, String db) { |
||
55 | if( StringUtils.isEmpty(host) ) {
|
||
56 | throw new IllegalArgumentException("Parameter 'host' can't be null."); |
||
57 | } |
||
58 | String connectionURL = "jdbc:postgresql://" + host; |
||
59 | if (port != null) { |
||
60 | connectionURL = connectionURL + ":" + port;
|
||
61 | } |
||
62 | connectionURL = connectionURL + "/" + db;
|
||
63 | logger.debug("connectionURL: {}", connectionURL);
|
||
64 | return connectionURL;
|
||
65 | } |
||
66 | |||
67 | private static class ConnectionProvider { |
||
68 | |||
69 | private static boolean needRegisterDriver = true; |
||
70 | |||
71 | private BasicDataSource dataSource = null; |
||
72 | |||
73 | private final PostgreSQLConnectionParameters connectionParameters; |
||
74 | |||
75 | public ConnectionProvider(PostgreSQLConnectionParameters connectionParameters) {
|
||
76 | this.connectionParameters = connectionParameters;
|
||
77 | } |
||
78 | |||
79 | public Connection getConnection() throws SQLException { |
||
80 | if (this.dataSource == null) { |
||
81 | this.dataSource = this.createDataSource(); |
||
82 | } |
||
83 | Connection conn = this.dataSource.getConnection(); |
||
84 | return conn;
|
||
85 | } |
||
86 | |||
87 | private BasicDataSource createDataSource() throws SQLException { |
||
88 | if (!this.isRegistered()) { |
||
89 | this.registerDriver();
|
||
90 | } |
||
91 | PostgreSQLConnectionParameters params = connectionParameters; |
||
92 | |||
93 | BasicDataSource ds = new BasicDataSource();
|
||
94 | ds.setDriverClassName(params.getJDBCDriverClassName()); |
||
95 | if( params.getUseSSL() ) {
|
||
96 | String s = BooleanUtils.toStringTrueFalse(params.getUseSSL());
|
||
97 | ds.addConnectionProperty("ssl", s );
|
||
98 | } |
||
99 | if( !StringUtils.isEmpty(params.getUser()) ) {
|
||
100 | ds.setUsername(params.getUser()); |
||
101 | } |
||
102 | if( !StringUtils.isEmpty(params.getPassword()) ) {
|
||
103 | ds.setPassword(params.getPassword()); |
||
104 | } |
||
105 | ds.setUrl(params.getUrl()); |
||
106 | |||
107 | ds.setMaxWait(60L * 1000); |
||
108 | return ds;
|
||
109 | } |
||
110 | |||
111 | private boolean isRegistered() { |
||
112 | return needRegisterDriver;
|
||
113 | } |
||
114 | |||
115 | public void registerDriver() throws SQLException { |
||
116 | String className = this.connectionParameters.getJDBCDriverClassName(); |
||
117 | if (className == null) { |
||
118 | return;
|
||
119 | } |
||
120 | try {
|
||
121 | Class theClass = Class.forName(className); |
||
122 | if (theClass == null) { |
||
123 | 364 | jjdelcerro | throw new JDBCDriverClassNotFoundException(PostgreSQLLibrary.NAME, className); |
124 | 362 | jjdelcerro | } |
125 | } catch (Exception e) { |
||
126 | throw new SQLException("Can't register JDBC driver '" + className + "'.", e); |
||
127 | } |
||
128 | needRegisterDriver = false;
|
||
129 | } |
||
130 | |||
131 | } |
||
132 | |||
133 | private ConnectionProvider connectionProvider = null; |
||
134 | |||
135 | public PostgreSQLHelper(JDBCConnectionParameters connectionParameters) {
|
||
136 | super(connectionParameters);
|
||
137 | this.srss = new JDBCSRSsBase(this); |
||
138 | } |
||
139 | |||
140 | @Override
|
||
141 | public Connection getConnection() throws AccessResourceException { |
||
142 | try {
|
||
143 | if (this.connectionProvider == null) { |
||
144 | this.connectionProvider = new ConnectionProvider(this.getConnectionParameters()); |
||
145 | } |
||
146 | return this.connectionProvider.getConnection(); |
||
147 | } catch (SQLException ex) { |
||
148 | 364 | jjdelcerro | throw new AccessResourceException(PostgreSQLLibrary.NAME, ex); |
149 | 362 | jjdelcerro | } |
150 | } |
||
151 | |||
152 | @Override
|
||
153 | public PostgreSQLConnectionParameters getConnectionParameters() {
|
||
154 | return (PostgreSQLConnectionParameters) super.getConnectionParameters(); |
||
155 | } |
||
156 | |||
157 | @Override
|
||
158 | public String getConnectionURL() { |
||
159 | return getConnectionURL(this.getConnectionParameters()); |
||
160 | } |
||
161 | |||
162 | @Override
|
||
163 | protected String getResourceType() { |
||
164 | 364 | jjdelcerro | return PostgreSQLLibrary.NAME;
|
165 | 362 | jjdelcerro | } |
166 | |||
167 | @Override
|
||
168 | public String getProviderName() { |
||
169 | 364 | jjdelcerro | return PostgreSQLLibrary.NAME;
|
170 | 362 | jjdelcerro | } |
171 | |||
172 | @Override
|
||
173 | public JDBCSQLBuilderBase createSQLBuilder() {
|
||
174 | return new PostgreSQLBuilder(this); |
||
175 | } |
||
176 | |||
177 | @Override
|
||
178 | public OperationsFactory getOperations() {
|
||
179 | if (this.operationsFactory == null) { |
||
180 | this.operationsFactory = new PostgreSQLOperationsFactory(this); |
||
181 | } |
||
182 | return operationsFactory;
|
||
183 | } |
||
184 | |||
185 | @Override
|
||
186 | public SQLBuilder.GeometrySupportType getGeometrySupportType() {
|
||
187 | return SQLBuilder.GeometrySupportType.WKB;
|
||
188 | } |
||
189 | |||
190 | @Override
|
||
191 | public boolean hasSpatialFunctions() { |
||
192 | return true; |
||
193 | } |
||
194 | |||
195 | @Override
|
||
196 | public boolean canWriteGeometry(int geometryType, int geometrySubtype) { |
||
197 | return true; |
||
198 | } |
||
199 | |||
200 | @Override
|
||
201 | public String getQuoteForIdentifiers() { |
||
202 | return "\""; |
||
203 | } |
||
204 | |||
205 | @Override
|
||
206 | public boolean allowAutomaticValues() { |
||
207 | return true; |
||
208 | } |
||
209 | |||
210 | @Override
|
||
211 | public boolean supportOffsetInSelect() { |
||
212 | return true; |
||
213 | } |
||
214 | |||
215 | @Override
|
||
216 | public String getQuoteForStrings() { |
||
217 | return "'"; |
||
218 | } |
||
219 | |||
220 | @Override
|
||
221 | public String getSourceId(JDBCStoreParameters parameters) { |
||
222 | return parameters.getDBName() + "." + |
||
223 | parameters.getSchema()+ "." +
|
||
224 | parameters.getTable(); |
||
225 | } |
||
226 | |||
227 | @Override
|
||
228 | public JDBCNewStoreParameters createNewStoreParameters() {
|
||
229 | return new PostgreSQLNewStoreParameters(); |
||
230 | } |
||
231 | |||
232 | @Override
|
||
233 | public JDBCStoreParameters createOpenStoreParameters() {
|
||
234 | return new PostgreSQLStoreParameters(); |
||
235 | } |
||
236 | |||
237 | @Override
|
||
238 | public JDBCServerExplorerParameters createServerExplorerParameters() {
|
||
239 | 385 | jjdelcerro | return new PostgreSQLServerExplorerParameters(); |
240 | 362 | jjdelcerro | } |
241 | |||
242 | public void setPreparedStatementParameters(PreparedStatement st, JDBCSQLBuilderBase sqlbuilder, FeatureType type, FeatureProvider feature) { |
||
243 | try {
|
||
244 | List<Object> values = new ArrayList<>(); |
||
245 | for (ExpressionBuilder.Parameter parameter : sqlbuilder.getParameters()) {
|
||
246 | Object value;
|
||
247 | if (parameter.is_constant()) {
|
||
248 | value = parameter.getValue(); |
||
249 | } else {
|
||
250 | String name = parameter.getName();
|
||
251 | value = feature.get(name); |
||
252 | FeatureAttributeDescriptor attr = type.getAttributeDescriptor(name); |
||
253 | if( attr.getType()==DataTypes.GEOMETRY ) {
|
||
254 | value = forceGeometry(attr.getGeomType(), (Geometry) value); |
||
255 | } |
||
256 | } |
||
257 | values.add(value); |
||
258 | } |
||
259 | JDBCUtils.setObjects(st, values, sqlbuilder.geometry_support_type()); |
||
260 | } catch (Exception ex) { |
||
261 | String f = "unknow"; |
||
262 | try {
|
||
263 | f = feature.toString(); |
||
264 | } catch (Exception ex2) { |
||
265 | // Do nothing
|
||
266 | } |
||
267 | throw new RuntimeException("Can't set parameters to prepared statement from the feature (" + f + ")", ex); |
||
268 | } |
||
269 | } |
||
270 | |||
271 | private Geometry forceGeometry(GeometryType geomtype, Geometry geom) throws CreateGeometryException { |
||
272 | switch( geomtype.getType() ) {
|
||
273 | case Geometry.TYPES.MULTIPOLYGON:
|
||
274 | if( geom.getType()==Geometry.TYPES.POLYGON ) {
|
||
275 | MultiPolygon x = getGeometryManager().createMultiPolygon(geomtype.getSubType()); |
||
276 | x.addPrimitive((Primitive) geom); |
||
277 | geom = x; |
||
278 | } |
||
279 | break;
|
||
280 | case Geometry.TYPES.MULTILINE:
|
||
281 | if( geom.getType()==Geometry.TYPES.LINE ) {
|
||
282 | MultiLine x = getGeometryManager().createMultiLine(geomtype.getSubType()); |
||
283 | x.addPrimitive((Primitive) geom); |
||
284 | geom = x; |
||
285 | } |
||
286 | break;
|
||
287 | case Geometry.TYPES.MULTIPOINT:
|
||
288 | if( geom.getType()==Geometry.TYPES.POINT ) {
|
||
289 | MultiLine x = getGeometryManager().createMultiLine(geomtype.getSubType()); |
||
290 | x.addPrimitive((Primitive) geom); |
||
291 | geom = x; |
||
292 | } |
||
293 | break;
|
||
294 | case Geometry.TYPES.POLYGON:
|
||
295 | if( geom.getType()==Geometry.TYPES.MULTIPOLYGON ) {
|
||
296 | MultiPolygon x = (MultiPolygon) geom; |
||
297 | if( x.getPrimitivesNumber()==1 ) { |
||
298 | geom = x.getPrimitiveAt(0);
|
||
299 | } |
||
300 | } |
||
301 | break;
|
||
302 | case Geometry.TYPES.LINE:
|
||
303 | if( geom.getType()==Geometry.TYPES.MULTILINE ) {
|
||
304 | MultiLine x = (MultiLine) geom; |
||
305 | if( x.getPrimitivesNumber()==1 ) { |
||
306 | geom = x.getPrimitiveAt(0);
|
||
307 | } |
||
308 | } |
||
309 | break;
|
||
310 | case Geometry.TYPES.POINT:
|
||
311 | if( geom.getType()==Geometry.TYPES.MULTIPOINT ) {
|
||
312 | MultiPoint x = (MultiPoint) geom; |
||
313 | if( x.getPrimitivesNumber()==1 ) { |
||
314 | geom = x.getPrimitiveAt(0);
|
||
315 | } |
||
316 | } |
||
317 | } |
||
318 | return geom;
|
||
319 | } |
||
320 | |||
321 | |||
322 | } |