001: /*
002: * This file is part of the GeOxygene project source files.
003: *
004: * GeOxygene aims at providing an open framework which implements OGC/ISO specifications for
005: * the development and deployment of geographic (GIS) applications. It is a open source
006: * contribution of the COGIT laboratory at the Institut Géographique National (the French
007: * National Mapping Agency).
008: *
009: * See: http://oxygene-project.sourceforge.net
010: *
011: * Copyright (C) 2005 Institut Géographique National
012: *
013: * This library is free software; you can redistribute it and/or modify it under the terms
014: * of the GNU Lesser General Public License as published by the Free Software Foundation;
015: * either version 2.1 of the License, or any later version.
016: *
017: * This library is distributed in the hope that it will be useful, but WITHOUT ANY
018: * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A
019: * PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details.
020: *
021: * You should have received a copy of the GNU Lesser General Public License along with
022: * this library (see file LICENSE if present); if not, write to the Free Software
023: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
024: *
025: */
026:
027: package fr.ign.cogit.geoxygene.util.loader;
028:
029: import java.lang.reflect.Field;
030: import java.math.BigDecimal;
031: import java.sql.Connection;
032: import java.sql.ResultSet;
033: import java.sql.SQLException;
034: import java.sql.Statement;
035:
036: import javax.swing.JOptionPane;
037:
038: import fr.ign.cogit.geoxygene.datatools.Geodatabase;
039: import fr.ign.cogit.geoxygene.util.loader.gui.GUISelectionGeometrie;
040:
041: /**
042: * Usage interne. Appelé par la Console.
043: * Génére à partir d'une classe Java, la table dans le SGBD et le fichier de mapping
044: * correspondants.
045: *
046: * @author Eric Grosso - IGN / Laboratoire COGIT
047: * @version 1.0
048: *
049: */
050:
051: public class SQLXMLGenerator {
052:
053: private Geodatabase data;
054: private OjbXMLGenerator theXMLGenerator;
055: private String geOxygeneMapping; // path
056: private String extentMappingFileName;
057: private String tableName;
058: private String javaFilePath;
059: private String userName;
060:
061: ///////////////////////////////////////////////////////////////////////////////////////////////////////////
062: ///////////////////////////////////////////////////////////////////////////////////////////////////////////
063: public SQLXMLGenerator(Geodatabase Data, String JavaFilePath,
064: String mappingDirectory, String TableName,
065: String mappingFileName) {
066: data = Data;
067: tableName = TableName;
068: javaFilePath = JavaFilePath;
069: String extentClassName = "";
070: extentMappingFileName = null;
071: geOxygeneMapping = mappingDirectory;
072: theXMLGenerator = new OjbXMLGenerator(data, geOxygeneMapping,
073: mappingFileName, extentClassName, extentMappingFileName);
074: }
075:
076: ///////////////////////////////////////////////////////////////////////////////////////////////////////////
077: ///////////////////////////////////////////////////////////////////////////////////////////////////////////
078: public void writeAll() {
079:
080: //ecriture de la table correspondante à la classe java
081: boolean heritage = true; // à paramétrer par la suite
082:
083: try {
084: if (data.getDBMS() == Geodatabase.ORACLE)
085: querySQLOracle(javaFilePath, tableName, heritage);
086: else if (data.getDBMS() == Geodatabase.POSTGIS)
087: querySQLPostgis(javaFilePath, tableName, heritage);
088: else {
089: JOptionPane.showMessageDialog(null,
090: "Problème de SGBD non supporté : il apparaît que"
091: + " ce n'est ni Oracle ni PostgreSQL",
092: "SGBD non supporté",
093: JOptionPane.WARNING_MESSAGE);
094: return;
095: }
096: } catch (Exception e) {
097: e.printStackTrace();
098: }
099:
100: //ecriture du fichier de mapping correspondant
101: try {
102: Connection conn = data.getConnection();
103: Statement stm = conn.createStatement();
104:
105: System.out.println("");
106:
107: theXMLGenerator.writeClassHeader(javaFilePath, tableName
108: .toUpperCase());
109:
110: String query = "";
111: if (data.getDBMS() == Geodatabase.ORACLE) {
112: query = getQueryColumnNameOracle(tableName
113: .toUpperCase());
114: conn.commit();
115: ResultSet rs = (ResultSet) stm.executeQuery(query);
116: // Boucle sur les colonnes
117: while (rs.next()) {
118:
119: // La colonne SQL
120: String sqlColumnName = rs.getString(1);
121:
122: // Le type SQL
123: String sqlDbmsType = rs.getString(2);
124:
125: // Si c'est le champ COGITID : on passe
126: if (sqlColumnName.compareToIgnoreCase("COGITID") == 0)
127: continue;
128:
129: // bidouille speciale Oracle pour traiter le cas des entiers et des booléens...
130: if (rs.getObject(3) != null) {
131: int dataScale = ((BigDecimal) rs.getObject(3))
132: .intValue();
133: //cas des entiers
134: if ((sqlDbmsType.compareToIgnoreCase("NUMBER") == 0)
135: && (dataScale == 0))
136: sqlDbmsType = "INTEGER";
137: //cas des booleans (ne sont pas reconnus par Oracle JDBC)
138: //On suppose que CHAR(1) est un boolean
139: if ((sqlDbmsType.compareToIgnoreCase("CHAR") == 0)
140: && (dataScale == 1))
141: sqlDbmsType = "BOOLEAN";
142: }
143: // fin de la bidouille
144:
145: // Le nom Java
146: String javaFieldName = sqlColumnName.toLowerCase();
147:
148: theXMLGenerator.writeField(javaFieldName,
149: sqlColumnName, sqlDbmsType);
150:
151: System.out.println(" nom sql : " + sqlColumnName
152: + "\n nom java : " + javaFieldName);
153: }
154: rs.close();
155: theXMLGenerator.writeClassBottom();
156: } else if (data.getDBMS() == Geodatabase.POSTGIS) {
157: try {
158: userName = conn.getMetaData().getUserName();
159: } catch (SQLException e) {
160: e.printStackTrace();
161: }
162: query = getQueryColumnNamePostgis(tableName, userName);
163: conn.commit();
164: ResultSet rs = (ResultSet) stm.executeQuery(query);
165: // Boucle sur les colonnes
166: while (rs.next()) {
167:
168: // La colonne SQL
169: String sqlColumnName = rs.getString(1);
170:
171: // Le type SQL
172: String sqlDbmsType = rs.getString(2);
173:
174: // Si c'est le champ COGITID : on passe
175: if (sqlColumnName.compareToIgnoreCase("COGITID") == 0)
176: continue;
177:
178: // Le nom Java
179: String javaFieldName = sqlColumnName.toLowerCase();
180:
181: theXMLGenerator.writeField(javaFieldName,
182: sqlColumnName, sqlDbmsType);
183:
184: System.out.println(" nom sql : " + sqlColumnName
185: + "\n nom java : " + javaFieldName);
186: }
187: rs.close();
188: theXMLGenerator.writeClassBottom();
189: }
190:
191: stm.close();
192:
193: } catch (Exception e) {
194: e.printStackTrace();
195: }
196:
197: theXMLGenerator.writeFileBottom();
198: theXMLGenerator.writeInFile();
199: }
200:
201: ///////////////////////////////////////////////////////////////////////////////////////////////////////////
202: ///////////////////////////////////////////////////////////////////////////////////////////////////////////
203: private String getQueryColumnNameOracle(String tableName) {
204: return "SELECT COLUMN_NAME, DATA_TYPE, DATA_SCALE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = '"
205: + tableName + "'";
206: }
207:
208: ///////////////////////////////////////////////////////////////////////////////////////////////////////////
209: ///////////////////////////////////////////////////////////////////////////////////////////////////////////
210: private String getQueryColumnNamePostgis(String tableName,
211: String user) {
212: return "select pg_attribute.attname, pg_type.typname "
213: + "from pg_attribute, pg_type, pg_class, pg_user "
214: + "where pg_class.oid = pg_attribute.attrelid "
215: + "and pg_attribute.attnum>0 "
216: + "and pg_attribute.atttypid = pg_type.oid "
217: + "and pg_class.relowner = pg_user.usesysid "
218: + "and pg_user.usename = '" + user.toLowerCase() + "' "
219: + "and pg_class.relname='" + tableName.toLowerCase()
220: + "'";
221: }
222:
223: ///////////////////////////////////////////////////////////////////////
224: //////////////////////////////// ORACLE ///////////////////////////////
225: ///////////////////////////////////////////////////////////////////////
226:
227: //l'identifiant COGITID est créé par défaut en tant que clé primaire de la table
228: //HERITAGE :
229: //héritage jusqu'à ce que la classe mère soit différente de java.lang.Object
230: //n'est hérité de FT_Feature que la géométrie et l'identifiant
231: private void querySQLOracle(String cheminClasse, String nomTable,
232: boolean flagHeritage) throws Exception {
233: String query = "", queryGeom = "";
234: Field[] attributs;
235: Class classe = Class.forName(cheminClasse);
236:
237: try {
238: Connection conn = data.getConnection();
239: Statement stm = conn.createStatement();
240:
241: if (flagHeritage) {
242: String[][] dataNomAttributs, dataTypeAttributs;
243: String nomAttribut, type;
244: query = "CREATE TABLE " + nomTable
245: + " (COGITID INTEGER PRIMARY KEY";
246: while (!classe.getName().equals("java.lang.Object")) {
247: attributs = classe.getDeclaredFields();
248: dataNomAttributs = new String[attributs.length][1];
249: dataTypeAttributs = new String[attributs.length][1];
250: if (!classe
251: .getName()
252: .equals(
253: "fr.ign.cogit.geoxygene.feature.FT_Feature")) {
254: int i = 0;
255: while (i != attributs.length) {
256: query = query + ", ";
257: Field attribut = (Field) attributs[i];
258: nomAttribut = attribut.getName();
259: if (!nomAttribut.equals("id")) {
260: type = javaType2OracleType(attribut
261: .getType().getName());
262: query = query + attribut.getName()
263: + " " + type;
264: if (type.equals("MDSYS.SDO_GEOMETRY")) {
265: stm
266: .executeQuery("INSERT INTO USER_SDO_GEOM_METADATA VALUES ('"
267: + nomTable
268: + "','"
269: + nomAttribut
270: + "',NULL,NULL)");
271: }
272: }
273: i++;
274: }
275: } else {
276: query = query + ",GEOM MDSYS.SDO_GEOMETRY";
277: stm
278: .executeQuery("INSERT INTO USER_SDO_GEOM_METADATA VALUES ('"
279: + nomTable
280: + "','GEOM',NULL,NULL)");
281: }
282: classe = classe.getSuperclass();
283: }
284: query = query + ")";
285: } else {
286: attributs = classe.getDeclaredFields();
287: String[][] dataNomAttributs = new String[attributs.length][1], dataTypeAttributs = new String[attributs.length][1];
288: String nomAttribut, type;
289: query = "CREATE TABLE " + nomTable
290: + " (COGITID INTEGER PRIMARY KEY";
291: int i = 0;
292: while (i != attributs.length) {
293: query = query + ",";
294: Field attribut = (Field) attributs[i];
295: nomAttribut = attribut.getName();
296: if (!nomAttribut.equals("id")) {
297: type = javaType2OracleType(attribut.getType()
298: .getName());
299: query = query + attribut.getName() + " " + type;
300: if (type.equals("MDSYS.SDO_GEOMETRY")) {
301: stm
302: .executeQuery("INSERT INTO USER_SDO_GEOM_METADATA VALUES ('"
303: + nomTable
304: + "','"
305: + nomAttribut
306: + "',NULL,NULL)");
307: }
308: }
309: i++;
310: }
311: query = query + ")";
312: }
313:
314: stm.executeQuery(query);
315: stm.close();
316: } catch (Exception e) {
317: e.printStackTrace();
318: }
319: }
320:
321: ///////////////////////////////////////////////////////////////////////
322: //////////////////////////////// POSTGIS //////////////////////////////
323: ///////////////////////////////////////////////////////////////////////
324: //l'identifiant COGITID est créé par défaut en tant que clé primaire de la table
325: //HERITAGE :
326: //héritage jusqu'à ce que la classe mère soit différente de java.lang.Object
327: //n'est hérité de FT_Feature que la géométrie et l'identifiant
328: private void querySQLPostgis(String cheminClasse, String nomTable,
329: boolean flagHeritage) throws Exception {
330: String query = "", queryGeom = "";
331: Field[] attributs;
332: Class classe = Class.forName(cheminClasse);
333: boolean flagGeomFeature = false;
334:
335: try {
336: Connection conn = data.getConnection();
337: Statement stm = conn.createStatement();
338:
339: if (flagHeritage) {
340: String[][] dataNomAttributs, dataTypeAttributs;
341: String nomAttribut, type;
342: query = "CREATE TABLE " + nomTable
343: + " (COGITID INTEGER PRIMARY KEY";
344: while (!classe.getName().equals("java.lang.Object")) {
345: attributs = classe.getDeclaredFields();
346: dataNomAttributs = new String[attributs.length][1];
347: dataTypeAttributs = new String[attributs.length][1];
348: if (!classe
349: .getName()
350: .equals(
351: "fr.ign.cogit.geoxygene.feature.FT_Feature")) {
352: int i = 0;
353: while (i != attributs.length) {
354: query = query + ", ";
355: Field attribut = (Field) attributs[i];
356: nomAttribut = attribut.getName();
357: if (!nomAttribut.equals("id")) {
358: type = javaType2PostgisType(attribut
359: .getType().getName());
360: query = query + attribut.getName()
361: + " " + type;
362: //if (type.equals("GEOMETRY")) {
363: //interface pour connaitre le type de géométrie (impossible à savoir interactivement)
364: //stm.executeQuery("SELECT AddGeometrycolumn ('','"+nomTable+"','"+nomAttribut+"','-1','GEOMETRY',2)");
365: //}
366: }
367: i++;
368: }
369: } else {
370: flagGeomFeature = true;
371: }
372: classe = classe.getSuperclass();
373: }
374: query = query + ")";
375: } else {
376: attributs = classe.getDeclaredFields();
377: String[][] dataNomAttributs = new String[attributs.length][1], dataTypeAttributs = new String[attributs.length][1];
378: String nomAttribut, type;
379: query = "CREATE TABLE " + nomTable
380: + " (COGITID INTEGER PRIMARY KEY";
381: int i = 0;
382: while (i != attributs.length) {
383: query = query + ",";
384: Field attribut = (Field) attributs[i];
385: nomAttribut = attribut.getName();
386: if (!nomAttribut.equals("id")) {
387: type = javaType2PostgisType(attribut.getType()
388: .getName());
389: query = query + attribut.getName() + " " + type;
390: if (type.equals("GEOMETRY")) {
391: flagGeomFeature = true;
392: }
393: }
394: i++;
395: }
396: query = query + ")";
397: }
398:
399: try {
400: stm.executeQuery(query);
401: } catch (Exception e) {
402: //e.printStackTrace();
403: }
404:
405: //interface pour connaitre le type de géométrie (impossible à savoir interactivement)
406: if (flagGeomFeature) {
407: GUISelectionGeometrie sg = new GUISelectionGeometrie();
408: switch (sg.getTypeGeometrie()) {
409: case 0:
410: stm.executeQuery("SELECT AddGeometrycolumn ('','"
411: + nomTable + "','geom','-1','MULTIPOINT',"
412: + sg.getDimensionGeometrie() + ")");
413: try {
414: stm
415: .executeQuery("ALTER TABLE "
416: + nomTable
417: + " DROP CONSTRAINT enforce_geotype_geom");
418: } catch (Exception e) {
419: }
420: stm
421: .executeQuery("ALTER TABLE "
422: + nomTable
423: + " ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POINT'::text OR geometrytype(geom) = 'MULTIPOINT'::text OR isempty(geom) OR geom IS NULL)");
424: break;
425: case 1:
426: stm.executeQuery("SELECT AddGeometrycolumn ('','"
427: + nomTable
428: + "','geom','-1','MULTILINESTRING',"
429: + sg.getDimensionGeometrie() + ")");
430: try {
431: stm
432: .executeQuery("ALTER TABLE "
433: + nomTable
434: + " DROP CONSTRAINT enforce_geotype_geom");
435: } catch (Exception e) {
436: }
437: stm
438: .executeQuery("ALTER TABLE "
439: + nomTable
440: + " ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'LINESTRING'::text OR geometrytype(geom) = 'MULTILINESTRING'::text OR isempty(geom) OR geom IS NULL)");
441: break;
442: case 2:
443: stm.executeQuery("SELECT AddGeometrycolumn ('','"
444: + nomTable
445: + "','geom','-1','MULTIPOLYGON',"
446: + sg.getDimensionGeometrie() + ")");
447: try {
448: stm
449: .executeQuery("ALTER TABLE "
450: + nomTable
451: + " DROP CONSTRAINT enforce_geotype_geom");
452: } catch (Exception e) {
453: }
454: stm
455: .executeQuery("ALTER TABLE "
456: + nomTable
457: + " ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text OR isempty(geom) OR geom IS NULL)");
458: break;
459: case 3:
460: stm.executeQuery("SELECT AddGeometrycolumn ('','"
461: + nomTable
462: + "','geom','-1','GEOMETRYCOLLECTION',"
463: + sg.getDimensionGeometrie() + ")");
464: stm.executeQuery("ALTER TABLE " + nomTable
465: + " DROP CONSTRAINT enforce_geotype_geom");
466: break;
467: default:
468: break;
469: }
470: }
471: stm.close();
472: } catch (Exception e) {
473: //e.printStackTrace();
474: }
475: }
476:
477: ///////////////////////////////////////////////////////////////////////////////////////////////////////////
478: ///////////////////////////////////////////////////////////////////////////////////////////////////////////
479: private static String javaType2OracleType(String javaType)
480: throws Exception {
481:
482: if (javaType.compareToIgnoreCase("java.lang.String") == 0)
483: return "VARCHAR(255)";
484: else if (javaType.compareToIgnoreCase("double") == 0)
485: return "NUMBER";
486: else if (javaType.compareToIgnoreCase("int") == 0)
487: return "INTEGER";
488: else if (javaType.compareToIgnoreCase("boolean") == 0)
489: return "CHAR(1)";
490: else if (javaType
491: .compareToIgnoreCase("fr.ign.cogit.geoxygene.spatial.geomroot.GM_Object") == 0)
492: return "MDSYS.SDO_GEOMETRY";
493: else if (javaType
494: .compareToIgnoreCase("fr.ign.cogit.geoxygene.spatial.geomprim.GM_Point") == 0)
495: return "MDSYS.SDO_GEOMETRY";
496: else if (javaType
497: .compareToIgnoreCase("fr.ign.cogit.geoxygene.spatial.coordgeom.GM_LineString") == 0)
498: return "MDSYS.SDO_GEOMETRY";
499: else if (javaType
500: .compareToIgnoreCase("fr.ign.cogit.geoxygene.spatial.coordgeom.GM_Polygon") == 0)
501: return "MDSYS.SDO_GEOMETRY";
502: else
503: throw new Exception("type non reconnu : " + javaType);
504: }
505:
506: ///////////////////////////////////////////////////////////////////////////////////////////////////////////
507: ///////////////////////////////////////////////////////////////////////////////////////////////////////////
508: private static String javaType2PostgisType(String javaType)
509: throws Exception {
510:
511: if (javaType.endsWith("GM_LineString"))
512: return "ligne";
513: else if (javaType.endsWith("String"))
514: return "VARCHAR";
515: else if (javaType.endsWith("boolean"))
516: return "boolean";
517: else if (javaType.endsWith("double"))
518: return "double precision";
519: else if (javaType.endsWith("GM_Point"))
520: return "point";
521: else if (javaType.endsWith("int"))
522: return "INTEGER";
523: else if (javaType.endsWith("GM_Polygon"))
524: return "surface";
525: else if (javaType.endsWith("List"))
526: return "liste";
527: else if (javaType
528: .compareToIgnoreCase("fr.ign.cogit.geoxygene.spatial.geomroot.GM_Object") == 0)
529: return "GEOMETRY";
530: else
531: throw new Exception("type non reconnu : " + javaType);
532: }
533:
534: }
|