svn-gvsig-desktop / branches / v2_0_0_prep / libraries / libFMap_dataDB / src / org / gvsig / data / datastores / vectorial / db / jdbc / postgresql / PostgresqlStoreUtils.java @ 20973
History | View | Annotate | Download (10.3 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.DataException; |
12 |
import org.gvsig.data.InitializeException; |
13 |
import org.gvsig.data.ReadException; |
14 |
import org.gvsig.data.datastores.vectorial.db.DBAttributeDescriptor; |
15 |
import org.gvsig.data.datastores.vectorial.db.DBFeatureType; |
16 |
import org.gvsig.data.datastores.vectorial.db.jdbc.JDBCDriverNotFoundException; |
17 |
import org.gvsig.data.datastores.vectorial.db.jdbc.SQLException; |
18 |
import org.gvsig.data.vectorial.FeatureType; |
19 |
import org.gvsig.data.vectorial.IFeatureAttributeDescriptor; |
20 |
import org.gvsig.data.vectorial.IsNotAttributeSettingException; |
21 |
|
22 |
public class PostgresqlStoreUtils { |
23 |
|
24 |
static String getJDBCUrl(String host, String db, String port) { |
25 |
String url;
|
26 |
url = "jdbc:postgresql://"+host+":" + port +"/"+db; |
27 |
|
28 |
return url;
|
29 |
} |
30 |
|
31 |
private static void addConditionForSerialField(DBAttributeDescriptor attr,StringBuffer sqlSeq){ |
32 |
sqlSeq.append(" (");
|
33 |
sqlSeq.append(" column_name = '" + attr.getName() +"'"); |
34 |
sqlSeq.append(" and table_name = '" + attr.getTableName()+ "'"); |
35 |
if (attr.getSchemaName() != null && attr.getSchemaName().length() > 0){ |
36 |
sqlSeq.append(" and table_schema = '" + attr.getSchemaName() +"'"); |
37 |
} |
38 |
|
39 |
sqlSeq.append(" and table_catalog = '" + attr.getCatalogName()+ "'"); |
40 |
sqlSeq.append(")");
|
41 |
|
42 |
} |
43 |
|
44 |
private static void initializeSerialFields(Connection connection,DBFeatureType featureType) throws java.sql.SQLException, DataException{ |
45 |
DBAttributeDescriptor attr; |
46 |
|
47 |
ArrayList serialCandidates= new ArrayList(); |
48 |
Iterator iter = featureType.iterator();
|
49 |
while(iter.hasNext()){
|
50 |
attr = (DBAttributeDescriptor)iter.next(); |
51 |
if (attr.getSqlTypeName().equals("int4") && |
52 |
attr.getTableName() != null &&
|
53 |
attr.getTableName().length() > 0){
|
54 |
serialCandidates.add(attr); |
55 |
} |
56 |
} |
57 |
if (serialCandidates.size() == 0){ |
58 |
return;
|
59 |
} |
60 |
Statement st = connection.createStatement();
|
61 |
StringBuffer sqlSeq= new StringBuffer("select table_catalog,table_schema,table_name,column_name from information_schema.columns where column_default like 'nextval(%' and ( "); |
62 |
iter = serialCandidates.iterator(); |
63 |
String sql;
|
64 |
int i;
|
65 |
for (i=0;i<serialCandidates.size()-1;i++){ |
66 |
attr = (DBAttributeDescriptor)serialCandidates.get(i); |
67 |
addConditionForSerialField(attr,sqlSeq); |
68 |
sqlSeq.append(" or ");
|
69 |
} |
70 |
attr = (DBAttributeDescriptor)serialCandidates.get(i); |
71 |
addConditionForSerialField(attr,sqlSeq); |
72 |
|
73 |
|
74 |
sqlSeq.append(")");
|
75 |
sql=sqlSeq.toString(); |
76 |
ResultSet rs = st.executeQuery(sql);
|
77 |
while (rs.next()){
|
78 |
iter = serialCandidates.iterator(); |
79 |
while (iter.hasNext()){
|
80 |
attr = (DBAttributeDescriptor)iter.next(); |
81 |
if (rs.getString("column_name").equals(attr.getName())){ |
82 |
attr.setAutoIncrement(true);
|
83 |
serialCandidates.remove(attr); |
84 |
break;
|
85 |
} |
86 |
} |
87 |
|
88 |
} |
89 |
|
90 |
|
91 |
} |
92 |
|
93 |
|
94 |
static DBFeatureType getFeatureType(Connection connection, PostgresqlStoreParameters params) throws ReadException{ |
95 |
DBFeatureType featureType = new DBFeatureType();
|
96 |
String[] ids =params.getFieldsId(); |
97 |
int i;
|
98 |
DBAttributeDescriptor attr; |
99 |
|
100 |
|
101 |
loadFieldsToFeatureType(connection, params, featureType); |
102 |
|
103 |
|
104 |
|
105 |
try {
|
106 |
featureType.setFieldsId(ids); |
107 |
} catch (DataException e) {
|
108 |
throw new ReadException(PostgresqlStore.DATASTORE_NAME,e); |
109 |
} |
110 |
|
111 |
//Inicializamos los 'serial' ya que en postgres el
|
112 |
//'isAutonumeric' devuelve false
|
113 |
// try{
|
114 |
// initializeSerialFields(connection,featureType);
|
115 |
// } catch (java.sql.SQLException e) {
|
116 |
// throw new InitializeException(PostgresqlStore.DATASTORE_NAME,e);
|
117 |
//
|
118 |
// }
|
119 |
//
|
120 |
|
121 |
|
122 |
|
123 |
|
124 |
|
125 |
//Inicializar campos geometricos si los hubiese
|
126 |
//TODO Datos geometricos
|
127 |
|
128 |
|
129 |
//Inicializar la geometria por defecto
|
130 |
if (params.getDefaultGeometryField() != null && params.getDefaultGeometryField() != ""){ |
131 |
if (featureType.getFieldIndex(params.getDefaultGeometryField())< 0){ |
132 |
throw new InitializeException( |
133 |
PostgresqlStore.DATASTORE_NAME, |
134 |
new Exception("Geometry Field '"+ params.getDefaultGeometryField() +"' not Found")); |
135 |
|
136 |
} |
137 |
attr = (DBAttributeDescriptor)featureType.get(params.getDefaultGeometryField()); |
138 |
if (attr.getDataType() != IFeatureAttributeDescriptor.TYPE_GEOMETRY){
|
139 |
throw new InitializeException( |
140 |
PostgresqlStore.DATASTORE_NAME, |
141 |
new Exception("Field '"+ params.getDefaultGeometryField() +"' isn't a geometry")); |
142 |
|
143 |
} |
144 |
|
145 |
featureType.setDefaultGeometry(params.getDefaultGeometryField()); |
146 |
} |
147 |
|
148 |
|
149 |
return featureType;
|
150 |
|
151 |
} |
152 |
|
153 |
private static void loadFieldsToFeatureType(Connection conn,PostgresqlStoreParameters params,DBFeatureType featureType) throws ReadException{ |
154 |
String sql=""; |
155 |
String columns=params.getFieldsString();
|
156 |
boolean fillTableData;
|
157 |
|
158 |
if (params.getSqlSoure() != null){ |
159 |
sql = params.getSqlSoure(); |
160 |
fillTableData = false;
|
161 |
} else {
|
162 |
sql = "Select "+columns+" from " + params.tableID(); |
163 |
fillTableData = true;
|
164 |
} |
165 |
|
166 |
try {
|
167 |
|
168 |
Statement stAux = conn.createStatement();
|
169 |
stAux.setFetchSize(1);
|
170 |
ResultSet rs = stAux.executeQuery(sql);
|
171 |
ResultSetMetaData rsMetadata = rs.getMetaData();
|
172 |
|
173 |
int i;
|
174 |
|
175 |
featureType.setTableID(params.tableID()); |
176 |
DBAttributeDescriptor attr; |
177 |
for (i=1;i<=rsMetadata.getColumnCount();i++){ |
178 |
attr = getAttributeFromJDBC(featureType,conn,rsMetadata,i); |
179 |
featureType.add(attr); |
180 |
// attr.setOrdinal(i-1);
|
181 |
attr.loading(); |
182 |
attr.setCatalogName(params.getDb()); |
183 |
if (fillTableData){
|
184 |
attr.setSchemaName(params.getSchema()); |
185 |
attr.setTableName(params.getTableName()); |
186 |
|
187 |
} |
188 |
attr.stopLoading(); |
189 |
} |
190 |
rs.close(); |
191 |
stAux.close(); |
192 |
|
193 |
|
194 |
|
195 |
} catch (java.sql.SQLException e) {
|
196 |
throw new SQLException(sql,"getFeatureType",e); |
197 |
} catch (IsNotAttributeSettingException e) {
|
198 |
e.printStackTrace(); |
199 |
} |
200 |
|
201 |
} |
202 |
|
203 |
private static DBAttributeDescriptor getAttributeFromJDBC(FeatureType fType,Connection conn,ResultSetMetaData rsMetadata,int colIndex) throws SQLException{ |
204 |
DBAttributeDescriptor column= (DBAttributeDescriptor) fType.createAttributeDescriptor(); |
205 |
try {
|
206 |
column.loading(); |
207 |
column.setName(rsMetadata.getColumnName(colIndex)); |
208 |
column.setCaseSensitive(rsMetadata.isCaseSensitive(colIndex)); |
209 |
column.setSqlType(rsMetadata.getColumnType(colIndex)); |
210 |
column.setAllowNull(rsMetadata.isNullable(colIndex) == ResultSetMetaData.columnNullable);
|
211 |
column.setAutoIncrement(rsMetadata.isAutoIncrement(colIndex)); |
212 |
column.setReadOnly(rsMetadata.isReadOnly(colIndex)); |
213 |
column.setWritable(rsMetadata.isWritable(colIndex)); |
214 |
column.setClassName(rsMetadata.getColumnClassName(colIndex)); |
215 |
column.setCatalogName(rsMetadata.getCatalogName(colIndex)); |
216 |
column.setDefinitelyWritable(rsMetadata.isDefinitelyWritable(colIndex)); |
217 |
column.setLabel(rsMetadata.getColumnLabel(colIndex)); |
218 |
column.setSchemaName(rsMetadata.getSchemaName(colIndex)); |
219 |
column.setTableName(rsMetadata.getTableName(colIndex)); |
220 |
column.setCatalogName(rsMetadata.getCatalogName(colIndex)); |
221 |
column.setSqlTypeName(rsMetadata.getColumnTypeName(colIndex)); |
222 |
column.setSearchable(rsMetadata.isSearchable(colIndex)); |
223 |
column.setSigned(rsMetadata.isSigned(colIndex)); |
224 |
column.setCurrency(rsMetadata.isCurrency(colIndex)); |
225 |
column.setPrecision(rsMetadata.getPrecision(colIndex)); |
226 |
column.setSize(rsMetadata.getColumnDisplaySize(colIndex)); |
227 |
|
228 |
|
229 |
switch (rsMetadata.getColumnType(colIndex)) {
|
230 |
case java.sql.Types.INTEGER:
|
231 |
column.setType(IFeatureAttributeDescriptor.TYPE_INT); |
232 |
break;
|
233 |
case java.sql.Types.BIGINT:
|
234 |
column.setType(IFeatureAttributeDescriptor.TYPE_LONG); |
235 |
break;
|
236 |
case java.sql.Types.REAL:
|
237 |
column.setType(IFeatureAttributeDescriptor.TYPE_DOUBLE); |
238 |
break;
|
239 |
case java.sql.Types.DOUBLE:
|
240 |
column.setType(IFeatureAttributeDescriptor.TYPE_DOUBLE); |
241 |
break;
|
242 |
case java.sql.Types.CHAR:
|
243 |
column.setType(IFeatureAttributeDescriptor.TYPE_STRING); |
244 |
break;
|
245 |
case java.sql.Types.VARCHAR:
|
246 |
column.setType(IFeatureAttributeDescriptor.TYPE_STRING); |
247 |
break;
|
248 |
case java.sql.Types.FLOAT:
|
249 |
column.setType(IFeatureAttributeDescriptor.TYPE_FLOAT); |
250 |
break;
|
251 |
case java.sql.Types.DECIMAL:
|
252 |
column.setType(IFeatureAttributeDescriptor.TYPE_FLOAT); |
253 |
break;
|
254 |
case java.sql.Types.DATE:
|
255 |
column.setType(IFeatureAttributeDescriptor.TYPE_DATE); |
256 |
break;
|
257 |
case java.sql.Types.TIME:
|
258 |
column.setType(IFeatureAttributeDescriptor.TYPE_TIME); |
259 |
break;
|
260 |
case java.sql.Types.TIMESTAMP:
|
261 |
column.setType(IFeatureAttributeDescriptor.TYPE_TIMESTAMP); |
262 |
break;
|
263 |
case java.sql.Types.BOOLEAN:
|
264 |
column.setType(IFeatureAttributeDescriptor.TYPE_BOOLEAN); |
265 |
break;
|
266 |
case java.sql.Types.OTHER:
|
267 |
if (column.getSqlTypeName().equalsIgnoreCase("geometry")){ |
268 |
column.setType(IFeatureAttributeDescriptor.TYPE_GEOMETRY); |
269 |
break;
|
270 |
} |
271 |
//No hacemos break para que se quede en default
|
272 |
|
273 |
default:
|
274 |
column.setType(IFeatureAttributeDescriptor.TYPE_OBJECT); |
275 |
break;
|
276 |
} |
277 |
column.stopLoading(); |
278 |
} catch (java.sql.SQLException e){
|
279 |
throw new SQLException("","load attribute definition",e); |
280 |
} catch (IsNotAttributeSettingException e) {
|
281 |
e.printStackTrace(); |
282 |
} |
283 |
|
284 |
return column;
|
285 |
|
286 |
} |
287 |
|
288 |
|
289 |
|
290 |
|
291 |
static Connection getConnection(String dbUrl, String dbUser, String dbPass) throws InitializeException { |
292 |
//TODO: Aqu? habria que implementar la llamada
|
293 |
// al Resource Manager para comprobar si ya hay
|
294 |
// una connexion a la BD
|
295 |
String connID = getConnectionResourceID(dbUrl, dbUser);
|
296 |
|
297 |
Connection conn = null; |
298 |
// IResource res = ResourceManager.getResourceManager().getResource(connID);
|
299 |
|
300 |
|
301 |
|
302 |
try {
|
303 |
Class.forName("org.postgresql.Driver"); |
304 |
} catch (ClassNotFoundException e) { |
305 |
throw new JDBCDriverNotFoundException("org.postgresql.Driver",e); |
306 |
} |
307 |
try {
|
308 |
conn = DriverManager.getConnection(dbUrl, dbUser, dbPass);
|
309 |
conn.setAutoCommit(false);
|
310 |
|
311 |
} catch (java.sql.SQLException e1) {
|
312 |
throw new InitializeException(PostgresqlStore.DATASTORE_NAME,e1); |
313 |
} |
314 |
//TODO: Registrar en el Resource manager
|
315 |
// ResourceManager.getResourceManager().addResource(res);
|
316 |
|
317 |
return conn;
|
318 |
} |
319 |
|
320 |
static String getConnectionResourceID(String dbUrl,String dbUser){ |
321 |
return PostgresqlStore.CONNECTION_STRING+";"+dbUrl+";"+dbUser; |
322 |
|
323 |
} |
324 |
|
325 |
|
326 |
|
327 |
|
328 |
static String addLimitsToSQL(String aSql,int fetchSize,int page){ |
329 |
return aSql+ " limit " + fetchSize + " offset " + (fetchSize*page); |
330 |
} |
331 |
|
332 |
static String getDefaultSchema(Connection conn, String catalog) throws InitializeException { |
333 |
//TODO
|
334 |
return null; |
335 |
} |
336 |
|
337 |
} |
338 |
|