View Javadoc

1   //
2   // Copyright (c) 2004, International Decision Systems
3   // all rights reserved
4   //
5   package net.sourceforge.addam.ddlgen.oracle;
6   
7   
8   import java.io.*;
9   import java.sql.*;
10  import java.util.List;
11  import java.util.ArrayList;
12  
13  import net.sourceforge.addam.ddlgen.Filter;
14  import net.sourceforge.addam.ddlgen.Generator;
15  
16  /**
17   * Uses DBMS_METADATA to generate the script to recreate the object.
18   *
19   * @author TIM3
20   * @since Oct 12, 2005
21   */
22  public abstract class DBMSMetadataGenerator implements Generator {
23  
24      public String objectsSQL() {
25          return "select object_name from all_objects where owner=? and object_type=?";
26      }
27  
28      public abstract String objectType();
29  
30      public List<String> getObjects(String catalog, String schema, Filter filter,
31                                     Connection connection, DatabaseMetaData metadata) throws Exception {
32          return getObjects(catalog, schema, objectType(), filter, connection, metadata, objectsSQL(), schema, objectType());
33      }
34  
35      protected List<String> getObjects(String catalog, String schema, String objectType, Filter filter,
36                                     Connection connection, DatabaseMetaData metadata, String objectsSQL, String... args) throws Exception {
37  
38          List<String> objects = new ArrayList<String>();
39          PreparedStatement statement = null;
40          ResultSet resultSet = null;
41          try {
42              statement = connection.prepareStatement(objectsSQL);
43              int index=1;
44              for (String arg : args) {
45                  statement.setString(index++, arg);
46              }
47              resultSet = statement.executeQuery();
48              while (resultSet.next()) {
49                  String dbObject = resultSet.getString(1);
50                  if (filter.includes(dbObject)) {
51                      objects.add(dbObject);
52                  }
53              }
54          } finally {
55              if (resultSet != null) resultSet.close();
56              if (statement != null) statement.close();
57          }
58          return objects;
59      }
60  
61      public void generate(String catalog, String schema, String object,
62                           String vendor, Connection connection, DatabaseMetaData metadata, BufferedWriter writer)
63              throws Exception {
64          generate(catalog, schema, objectType(), object, vendor, connection, metadata, writer);
65      }
66  
67      protected void generate(String catalog, String schema, String objectType, String object,
68                           String vendor, Connection connection, DatabaseMetaData metadata, BufferedWriter writer)
69              throws Exception {
70          if (metadata.storesUpperCaseIdentifiers()) {
71              schema = (schema == null) ? null : schema.toUpperCase();
72              object = (object == null) ? null : object.toUpperCase();
73          } else if (metadata.storesLowerCaseIdentifiers()) {
74              schema = (schema == null) ? null : schema.toLowerCase();
75              object = (object == null) ? null : object.toLowerCase();
76          }
77  
78          PreparedStatement statement = null;
79          ResultSet resultSet = null;
80          try {
81              setTransformParam(connection,"PRETTY",true);
82              setTransformParam(connection,"SQLTERMINATOR",true);
83              statement = connection.prepareStatement("select dbms_metadata.get_ddl(?,?,?) from dual");
84              statement.setString(1,objectType);
85              statement.setString(2,object);
86              statement.setString(3,schema);
87              resultSet = statement.executeQuery();
88              while (resultSet.next()) {
89                  Clob clob = resultSet.getClob(1);
90                  Reader clobReader = clob.getCharacterStream();
91                  BufferedReader reader = new BufferedReader(clobReader);
92                  for (String line = reader.readLine(); line != null; line = reader.readLine() ) {
93                      line = line.replace("\"" + schema + "\".","");
94                      writeLine(line,writer);
95                  }
96              }
97              // put everything back the way it was
98              setDefaultTransformParams(connection);
99          } finally {
100             if (resultSet != null) resultSet.close();
101             if (statement != null) statement.close();
102         }
103     }
104 
105     protected void writeLine(String line, BufferedWriter writer) throws IOException {
106         writer.write(line);
107         writer.newLine();
108         writer.flush();
109     }
110 
111     protected void setTransformParam(Connection connection, String param, boolean value) throws SQLException {
112         PreparedStatement statement = null;
113         try {
114             statement = connection.prepareCall("{call DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,?,?)}");
115             statement.setString(1,param);
116             statement.setBoolean(2,value);
117             statement.execute();
118         } finally {
119             if (statement != null) statement.close();
120         }
121     }
122 
123     protected void setDefaultTransformParams(Connection connection) throws SQLException {
124         PreparedStatement statement = null;
125         try {
126             statement = connection.prepareCall("{call DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT',true)}");
127             statement.execute();
128         } finally {
129             if (statement != null) statement.close();
130         }
131     }
132 
133 }