svn-gvsig-desktop / trunk / libraries / libDataSourceDBBaseDrivers / src / org / gvsig / data / datastores / vectorial / db / jdbc / postgresql / PostgresqlStoreUtils.java @ 20029
History | View | Annotate | Download (12.5 KB)
1 |
package org.gvsig.data.datastores.vectorial.db.jdbc.postgresql; |
---|---|
2 |
|
3 |
import java.sql.Connection; |
4 |
import java.sql.DriverManager; |
5 |
import java.sql.ResultSet; |
6 |
import java.sql.ResultSetMetaData; |
7 |
import java.sql.Statement; |
8 |
import java.util.ArrayList; |
9 |
import java.util.Iterator; |
10 |
|
11 |
import org.gvsig.data.datastores.vectorial.db.DBFeatureType; |
12 |
import org.gvsig.data.datastores.vectorial.db.jdbc.JDBCAttributeDescriptor; |
13 |
import org.gvsig.data.datastores.vectorial.db.jdbc.JDBCFeature; |
14 |
import org.gvsig.data.datastores.vectorial.db.jdbc.JDBCStore; |
15 |
import org.gvsig.data.datastores.vectorial.db.jdbc.exception.JDBCDriverNotFoundException; |
16 |
import org.gvsig.data.datastores.vectorial.db.jdbc.exception.SQLException; |
17 |
import org.gvsig.data.exception.InitializeException; |
18 |
import org.gvsig.data.exception.ReadException; |
19 |
import org.gvsig.data.vectorial.DefaultAttributeDescriptor; |
20 |
import org.gvsig.data.vectorial.IFeature; |
21 |
import org.gvsig.data.vectorial.IFeatureAttributeDescriptor; |
22 |
import org.postgis.PGgeometry; |
23 |
|
24 |
import com.iver.cit.gvsig.fmap.core.IGeometry; |
25 |
import com.iver.cit.gvsig.fmap.core.ShapeFactory; |
26 |
import com.iver.cit.gvsig.fmap.drivers.WKBParser2; |
27 |
|
28 |
public class PostgresqlStoreUtils { |
29 |
|
30 |
static String getJDBCUrl(String host, String db, String port) { |
31 |
String url;
|
32 |
url = "jdbc:postgresql://"+host+":" + port +"/"+db; |
33 |
|
34 |
return url;
|
35 |
} |
36 |
|
37 |
private static void addConditionForSerialField(JDBCAttributeDescriptor attr,StringBuffer sqlSeq){ |
38 |
sqlSeq.append(" (");
|
39 |
sqlSeq.append(" column_name = '" + attr.getName() +"'"); |
40 |
sqlSeq.append(" and table_name = '" + attr.getTableName()+ "'"); |
41 |
if (attr.getSchemaName() != null && attr.getSchemaName().length() > 0){ |
42 |
sqlSeq.append(" and table_schema = '" + attr.getSchemaName() +"'"); |
43 |
} |
44 |
|
45 |
sqlSeq.append(" and table_catalog = '" + attr.getCatalogName()+ "'"); |
46 |
sqlSeq.append(")");
|
47 |
|
48 |
} |
49 |
|
50 |
private static void initializeSerialFields(Connection connection,DBFeatureType featureType) throws java.sql.SQLException{ |
51 |
JDBCAttributeDescriptor attr; |
52 |
|
53 |
ArrayList serialCandidates= new ArrayList(); |
54 |
Iterator iter = featureType.iterator();
|
55 |
while(iter.hasNext()){
|
56 |
attr = (JDBCAttributeDescriptor)iter.next(); |
57 |
if (attr.getSqlTypeName().equals("int4") && |
58 |
attr.getTableName() != null &&
|
59 |
attr.getTableName().length() > 0){
|
60 |
serialCandidates.add(attr); |
61 |
} |
62 |
} |
63 |
if (serialCandidates.size() == 0){ |
64 |
return;
|
65 |
} |
66 |
Statement st = connection.createStatement();
|
67 |
StringBuffer sqlSeq= new StringBuffer("select table_catalog,table_schema,table_name,column_name from information_schema.columns where column_default like 'nextval(%' and ( "); |
68 |
iter = serialCandidates.iterator(); |
69 |
String sql;
|
70 |
int i;
|
71 |
for (i=0;i<serialCandidates.size()-1;i++){ |
72 |
attr = (JDBCAttributeDescriptor)serialCandidates.get(i); |
73 |
addConditionForSerialField(attr,sqlSeq); |
74 |
sqlSeq.append(" or ");
|
75 |
} |
76 |
attr = (JDBCAttributeDescriptor)serialCandidates.get(i); |
77 |
addConditionForSerialField(attr,sqlSeq); |
78 |
|
79 |
|
80 |
sqlSeq.append(")");
|
81 |
sql=sqlSeq.toString(); |
82 |
ResultSet rs = st.executeQuery(sql);
|
83 |
while (rs.next()){
|
84 |
iter = serialCandidates.iterator(); |
85 |
while (iter.hasNext()){
|
86 |
attr = (JDBCAttributeDescriptor)iter.next(); |
87 |
if (rs.getString("column_name").equals(attr.getName())){ |
88 |
attr.setAutoIncrement(true);
|
89 |
serialCandidates.remove(attr); |
90 |
break;
|
91 |
} |
92 |
} |
93 |
|
94 |
} |
95 |
|
96 |
|
97 |
} |
98 |
|
99 |
|
100 |
static DBFeatureType getFeatureType(Connection connection, PostgresqlStoreParameters params) throws ReadException{ |
101 |
DBFeatureType featureType = new DBFeatureType();
|
102 |
String[] ids =params.getFieldsId(); |
103 |
int i;
|
104 |
|
105 |
|
106 |
loadFieldsToFeatureType(connection, params, featureType); |
107 |
|
108 |
|
109 |
//Inicializamos los IDs
|
110 |
JDBCAttributeDescriptor attr; |
111 |
for (i=0;i<ids.length;i++){ |
112 |
try {
|
113 |
attr = (JDBCAttributeDescriptor)featureType.get(ids[i]); |
114 |
} catch (IndexOutOfBoundsException e){ |
115 |
throw new InitializeException( |
116 |
PostgresqlStore.DATASTORE_NAME, |
117 |
new Exception("Id Field '"+ ids[i] +"' not Found")); |
118 |
|
119 |
} |
120 |
attr.setPrimaryKey(true);
|
121 |
} |
122 |
featureType.setFieldsId(ids); |
123 |
|
124 |
//Inicializamos los 'serial' ya que en postgres el
|
125 |
//'isAutonumeric' devuelve false
|
126 |
// try{
|
127 |
// initializeSerialFields(connection,featureType);
|
128 |
// } catch (java.sql.SQLException e) {
|
129 |
// throw new InitializeException(PostgresqlStore.DATASTORE_NAME,e);
|
130 |
//
|
131 |
// }
|
132 |
//
|
133 |
|
134 |
|
135 |
|
136 |
|
137 |
//Inicializar campos geometricos si los hubiese
|
138 |
//TODO Datos geometricos
|
139 |
|
140 |
|
141 |
//Inicializar la geometria por defecto
|
142 |
if (params.getDefaultGeometryField() != null && params.getDefaultGeometryField() != ""){ |
143 |
if (featureType.getFieldIndex(params.getDefaultGeometryField())< 0){ |
144 |
throw new InitializeException( |
145 |
PostgresqlStore.DATASTORE_NAME, |
146 |
new Exception("Geometry Field '"+ params.getDefaultGeometryField() +"' not Found")); |
147 |
|
148 |
} |
149 |
attr = (JDBCAttributeDescriptor)featureType.get(params.getDefaultGeometryField()); |
150 |
if (attr.getDataType() != IFeatureAttributeDescriptor.TYPE_GEOMETRY){
|
151 |
throw new InitializeException( |
152 |
PostgresqlStore.DATASTORE_NAME, |
153 |
new Exception("Field '"+ params.getDefaultGeometryField() +"' isn't a geometry")); |
154 |
|
155 |
} |
156 |
|
157 |
featureType.setDefaultGeometry(params.getDefaultGeometryField()); |
158 |
} |
159 |
|
160 |
|
161 |
return featureType;
|
162 |
|
163 |
} |
164 |
|
165 |
private static void loadFieldsToFeatureType(Connection conn,PostgresqlStoreParameters params,DBFeatureType featureType) throws ReadException{ |
166 |
String sql=""; |
167 |
String columns=params.getFieldsString();
|
168 |
boolean fillTableData;
|
169 |
|
170 |
if (params.getSqlSoure() != null){ |
171 |
sql = params.getSqlSoure(); |
172 |
fillTableData = false;
|
173 |
} else {
|
174 |
sql = "Select "+columns+" from " + params.tableID(); |
175 |
fillTableData = true;
|
176 |
} |
177 |
|
178 |
try {
|
179 |
|
180 |
Statement stAux = conn.createStatement();
|
181 |
stAux.setFetchSize(1);
|
182 |
ResultSet rs = stAux.executeQuery(sql);
|
183 |
ResultSetMetaData rsMetadata = rs.getMetaData();
|
184 |
|
185 |
int i;
|
186 |
|
187 |
featureType.setTableID(params.tableID()); |
188 |
JDBCAttributeDescriptor attr; |
189 |
for (i=1;i<=rsMetadata.getColumnCount();i++){ |
190 |
attr = getAttributeFromJDBC(conn,rsMetadata,i); |
191 |
featureType.add(attr); |
192 |
attr.setOrdinal(i-1);
|
193 |
attr.setCatalogName(params.getDb()); |
194 |
if (fillTableData){
|
195 |
attr.setSchemaName(params.getSchema()); |
196 |
attr.setTableName(params.getTableName()); |
197 |
|
198 |
} |
199 |
} |
200 |
rs.close(); |
201 |
stAux.close(); |
202 |
|
203 |
|
204 |
|
205 |
} catch (java.sql.SQLException e) {
|
206 |
throw new SQLException(sql,"getFeatureType",e); |
207 |
} |
208 |
|
209 |
} |
210 |
|
211 |
private static JDBCAttributeDescriptor getAttributeFromJDBC(Connection conn,ResultSetMetaData rsMetadata,int colIndex) throws SQLException{ |
212 |
JDBCAttributeDescriptor column= new JDBCAttributeDescriptor();
|
213 |
try {
|
214 |
column.setName(rsMetadata.getColumnName(colIndex)); |
215 |
column.setCaseSensitive(rsMetadata.isCaseSensitive(colIndex)); |
216 |
column.setSqlType(rsMetadata.getColumnType(colIndex)); |
217 |
column.setAllowNull(rsMetadata.isNullable(colIndex) == ResultSetMetaData.columnNullable);
|
218 |
column.setAutoIncrement(rsMetadata.isAutoIncrement(colIndex)); |
219 |
column.setReadOnly(rsMetadata.isReadOnly(colIndex)); |
220 |
column.setWritable(rsMetadata.isWritable(colIndex)); |
221 |
column.setClassName(rsMetadata.getColumnClassName(colIndex)); |
222 |
column.setCatalogName(rsMetadata.getCatalogName(colIndex)); |
223 |
column.setDefinitelyWritable(rsMetadata.isDefinitelyWritable(colIndex)); |
224 |
column.setLabel(rsMetadata.getColumnLabel(colIndex)); |
225 |
column.setSchemaName(rsMetadata.getSchemaName(colIndex)); |
226 |
column.setTableName(rsMetadata.getTableName(colIndex)); |
227 |
column.setCatalogName(rsMetadata.getCatalogName(colIndex)); |
228 |
column.setSqlTypeName(rsMetadata.getColumnTypeName(colIndex)); |
229 |
column.setSearchable(rsMetadata.isSearchable(colIndex)); |
230 |
column.setSigned(rsMetadata.isSigned(colIndex)); |
231 |
column.setCurrency(rsMetadata.isCurrency(colIndex)); |
232 |
column.setPrecision(rsMetadata.getPrecision(colIndex)); |
233 |
column.setSize(rsMetadata.getColumnDisplaySize(colIndex)); |
234 |
|
235 |
|
236 |
switch (rsMetadata.getColumnType(colIndex)) {
|
237 |
case java.sql.Types.INTEGER:
|
238 |
column.setType(IFeatureAttributeDescriptor.TYPE_INT); |
239 |
break;
|
240 |
case java.sql.Types.BIGINT:
|
241 |
column.setType(IFeatureAttributeDescriptor.TYPE_LONG); |
242 |
break;
|
243 |
case java.sql.Types.REAL:
|
244 |
column.setType(IFeatureAttributeDescriptor.TYPE_DOUBLE); |
245 |
break;
|
246 |
case java.sql.Types.DOUBLE:
|
247 |
column.setType(IFeatureAttributeDescriptor.TYPE_DOUBLE); |
248 |
break;
|
249 |
case java.sql.Types.CHAR:
|
250 |
column.setType(IFeatureAttributeDescriptor.TYPE_STRING); |
251 |
break;
|
252 |
case java.sql.Types.VARCHAR:
|
253 |
column.setType(IFeatureAttributeDescriptor.TYPE_STRING); |
254 |
break;
|
255 |
case java.sql.Types.FLOAT:
|
256 |
column.setType(IFeatureAttributeDescriptor.TYPE_FLOAT); |
257 |
break;
|
258 |
case java.sql.Types.DECIMAL:
|
259 |
column.setType(IFeatureAttributeDescriptor.TYPE_FLOAT); |
260 |
break;
|
261 |
case java.sql.Types.DATE:
|
262 |
column.setType(IFeatureAttributeDescriptor.TYPE_DATE); |
263 |
break;
|
264 |
case java.sql.Types.BOOLEAN:
|
265 |
column.setType(IFeatureAttributeDescriptor.TYPE_BOOLEAN); |
266 |
break;
|
267 |
case java.sql.Types.OTHER:
|
268 |
if (column.getSqlTypeName().equalsIgnoreCase("geometry")){ |
269 |
column.setType(IFeatureAttributeDescriptor.TYPE_GEOMETRY); |
270 |
break;
|
271 |
} |
272 |
//No hacemos break para que se quede en default
|
273 |
|
274 |
default:
|
275 |
column.setType(IFeatureAttributeDescriptor.TYPE_OBJECT); |
276 |
break;
|
277 |
} |
278 |
} catch (java.sql.SQLException e){
|
279 |
throw new SQLException("","load attribute definition",e); |
280 |
} |
281 |
|
282 |
return column;
|
283 |
|
284 |
} |
285 |
|
286 |
|
287 |
static String getFilterForID(DBFeatureType fType, IFeature feature){ |
288 |
return getFilterForID(fType, getPkFromFeature(feature,fType));
|
289 |
} |
290 |
|
291 |
static String getFilterForID(DBFeatureType fType, Object[] featureKey){ |
292 |
//TODO: Ojo para los multiples
|
293 |
if (fType.getFieldsId().length != 1) |
294 |
throw new UnsupportedOperationException("ID fields > 1"); |
295 |
String id =fType.getFieldsId()[0]; |
296 |
return id + " = " + objectToSqlString(featureKey[0]); |
297 |
} |
298 |
|
299 |
static String objectToSqlString(Object obj){ |
300 |
if (obj instanceof String){ |
301 |
return "'"+ scapeString((String)obj) +"'"; |
302 |
} else if (obj == null){ |
303 |
return "null"; |
304 |
}else{
|
305 |
// OJO con otros tipos!!
|
306 |
return obj.toString();
|
307 |
} |
308 |
|
309 |
} |
310 |
|
311 |
static String scapeString(String str){ |
312 |
return str.replace("'", "''"); |
313 |
} |
314 |
|
315 |
|
316 |
|
317 |
static Connection getConnection(String dbUrl, String dbUser, String dbPass) throws InitializeException { |
318 |
//TODO: Aqu? habria que implementar la llamada
|
319 |
// al Resource Manager para comprobar si ya hay
|
320 |
// una connexion a la BD
|
321 |
String connID = getConnectionResourceID(dbUrl, dbUser);
|
322 |
|
323 |
Connection conn = null; |
324 |
// IResource res = ResourceManager.getResourceManager().getResource(connID);
|
325 |
|
326 |
|
327 |
|
328 |
try {
|
329 |
Class.forName("org.postgresql.Driver"); |
330 |
} catch (ClassNotFoundException e) { |
331 |
throw new JDBCDriverNotFoundException("org.postgresql.Driver",e); |
332 |
} |
333 |
try {
|
334 |
conn = DriverManager.getConnection(dbUrl, dbUser, dbPass);
|
335 |
conn.setAutoCommit(false);
|
336 |
|
337 |
} catch (java.sql.SQLException e1) {
|
338 |
throw new InitializeException(PostgresqlStore.DATASTORE_NAME,e1); |
339 |
} |
340 |
//TODO: Registrar en el Resource manager
|
341 |
// ResourceManager.getResourceManager().addResource(res);
|
342 |
|
343 |
return conn;
|
344 |
} |
345 |
|
346 |
static String getConnectionResourceID(String dbUrl,String dbUser){ |
347 |
return PostgresqlStore.CONNECTION_STRING+";"+dbUrl+";"+dbUser; |
348 |
|
349 |
} |
350 |
|
351 |
|
352 |
|
353 |
protected static Object[] getPkFromResulset(ResultSet rs, DBFeatureType featureType) throws java.sql.SQLException{ |
354 |
String[] fieldsId = featureType.getFieldsId(); |
355 |
Object[] result = new Object[fieldsId.length]; |
356 |
for (int i=0;i<fieldsId.length;i++){ |
357 |
result[i] = rs.getObject(fieldsId[i]); |
358 |
} |
359 |
return result;
|
360 |
|
361 |
} |
362 |
|
363 |
|
364 |
|
365 |
protected static Object[] getPkFromFeature(IFeature feature, DBFeatureType featureType){ |
366 |
String[] fieldsId = featureType.getFieldsId(); |
367 |
Object[] result = new Object[fieldsId.length]; |
368 |
for (int i=0;i<fieldsId.length;i++){ |
369 |
result[i] = feature.get(fieldsId[i]); |
370 |
} |
371 |
return result;
|
372 |
|
373 |
} |
374 |
|
375 |
static IFeature createFeature(JDBCStore store,ResultSet rs,DBFeatureType featureType) throws ReadException{ |
376 |
|
377 |
JDBCFeature feature=null;
|
378 |
|
379 |
|
380 |
Object[] pk; |
381 |
try {
|
382 |
pk = getPkFromResulset(rs, featureType); |
383 |
|
384 |
feature=new PostgresqlFeature(featureType,store,pk);
|
385 |
|
386 |
Iterator iter = featureType.iterator();
|
387 |
IGeometry geom = null;
|
388 |
while (iter.hasNext()) {
|
389 |
IFeatureAttributeDescriptor fad=(IFeatureAttributeDescriptor)iter.next(); |
390 |
if (fad.getDataType().equals(IFeatureAttributeDescriptor.TYPE_GEOMETRY)) {
|
391 |
|
392 |
PGgeometry data =(PGgeometry)rs.getObject(fad.getName()); |
393 |
if (data == null) { |
394 |
geom = null;
|
395 |
} else{
|
396 |
geom = PostGIS2Geometry.getGeneralPath(data); |
397 |
} |
398 |
feature.setGeometry(fad.getName(),geom); |
399 |
} else {
|
400 |
feature.set(fad.getName(), rs.getObject(fad.getName())); |
401 |
} |
402 |
|
403 |
} |
404 |
return feature;
|
405 |
} catch (java.sql.SQLException e) {
|
406 |
throw new ReadException("CreateFeature",e); |
407 |
} |
408 |
} |
409 |
|
410 |
static String addLimitsToSQL(String aSql,int fetchSize,int page){ |
411 |
return aSql+ " limit " + fetchSize + " offset " + (fetchSize*page); |
412 |
} |
413 |
|
414 |
|
415 |
} |
416 |
|