1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 package net.sourceforge.addam.impexp;
23
24
25 import net.sourceforge.addam.impexp.csv.CSVPrinter;
26 import net.sourceforge.addam.util.DatabaseMetaDataUtil;
27 import net.sourceforge.addam.util.TableUtil;
28
29 import org.apache.tools.ant.BuildException;
30 import org.apache.tools.ant.taskdefs.JDBCTask;
31
32 import java.io.File;
33 import java.io.FileWriter;
34 import java.io.IOException;
35 import java.io.Writer;
36 import java.sql.*;
37 import java.util.ArrayList;
38 import java.util.Iterator;
39 import java.util.List;
40
41 public class ExportDataTask extends JDBCTask {
42 private final List<Tables> includes = new ArrayList<Tables>();
43 private final List<Tables> excludes = new ArrayList<Tables>();
44 protected File dir;
45 private String schema;
46 private String catalog;
47
48 /**
49 * Selects all data from the named tables and extracts to the named files.
50 *
51 * @throws org.apache.tools.ant.BuildException
52 * if anything goes wrong
53 */
54 public void execute() throws BuildException {
55
56 for (String tableName : getTableNames()) {
57 Connection connection = null;
58 try {
59 connection = this.getConnection();
60 this.exportTable(tableName, connection);
61 } finally {
62 if (connection != null)
63 try {
64 connection.close();
65 } catch (SQLException e) {
66
67 }
68 }
69 }
70 }
71
72 /**
73 * Selects all data from the named table and extracts to the named file.
74 *
75 * @throws org.apache.tools.ant.BuildException
76 * if anything goes wrong
77 */
78 public void exportTable(String tableName, Connection connection) throws BuildException {
79 File toFile = new File(dir, TableUtil.getFileName(tableName));
80 log("extracting " + tableName + " to file " + toFile);
81 int count = 0;
82 Writer writer = null;
83 try {
84 writer = new FileWriter(toFile);
85 CSVPrinter printer = new CSVPrinter(writer);
86
87 Statement stmt = connection.createStatement();
88 ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName + " ORDER BY 1");
89 ResultSetMetaData rsmd = rs.getMetaData();
90 int numCols = rsmd.getColumnCount();
91 String line[] = new String[numCols];
92
93
94 for (int i = 1; i <= numCols; i++) {
95 line[i - 1] = rsmd.getColumnName(i);
96 }
97 printer.writeRecord(line);
98
99
100 while (rs.next()) {
101 for (int i = 1; i <= numCols; i++) {
102 String className = rsmd.getColumnClassName(i);
103 DataCodec codec = DataCodec.getCodec(className);
104 Object value;
105 if (className.equals("java.sql.Timestamp")) {
106 value = rs.getTimestamp(i);
107 } else if (className.equals("java.lang.String")) {
108 value = rs.getString(i);
109 } else {
110 value = rs.getObject(i);
111 }
112
113 String o = codec.encode(value);
114 line[i - 1] = (o == null) ? "" : o;
115 }
116 printer.writeRecord(line);
117 count++;
118 }
119 } catch (BuildException e) {
120 throw e;
121 } catch (Exception e) {
122 e.printStackTrace();
123 throw new BuildException(e);
124 } finally {
125 try {
126 if (writer != null) {
127 writer.close();
128 }
129 } catch (IOException e) {
130 log(e.toString());
131 }
132 }
133 log(" ... " + count + " records");
134 }
135
136 /**
137 * this is a shortcut for having an <includeTables> subelement by simply providing a filename as an attribute to this task
138 *
139 * @param tableFile a file containing an ordered list of tables
140 */
141 public void setIncludeFile(File tableFile) {
142 Tables tables = new Tables();
143 tables.setFile(tableFile);
144 includes.add(tables);
145 }
146
147 /**
148 * this is a shortcut for having a <excludeTables> subelement by simply providing a filename as an attribute to this task
149 *
150 * @param tableFile a file containing an ordered list of tables
151 */
152 public void setExcludeFile(File tableFile) {
153 Tables tables = new Tables();
154 tables.setFile(tableFile);
155 excludes.add(tables);
156 }
157
158 /**
159 * @param pattern a regexp pattern to compare table names to
160 */
161 public void setIncludesPattern(String pattern) {
162 Tables tables = new Tables();
163 tables.setPattern(pattern);
164 includes.add(tables);
165 }
166
167 /**
168 * @param pattern a regexp pattern to compare table names to
169 */
170 public void setExcludesPattern(String pattern) {
171 Tables tables = new Tables();
172 tables.setPattern(pattern);
173 excludes.add(tables);
174 }
175
176 /**
177 * @param names a comma separated list of table names
178 */
179 public void setIncludes(String names) {
180 Tables tables = new Tables();
181 tables.setNames(names);
182 includes.add(tables);
183 }
184
185 /**
186 * @param names a comma separated list of table names
187 */
188 public void setExcludes(String names) {
189 Tables tables = new Tables();
190 tables.setNames(names);
191 excludes.add(tables);
192 }
193
194 /**
195 * @param tables a set of tables to include or exclude
196 */
197 public void addIncludeTables(Tables tables) {
198 includes.add(tables);
199 }
200
201 /**
202 * @param tables a set of tables to include or exclude
203 */
204 public void addExcludeTables(Tables tables) {
205 excludes.add(tables);
206 }
207
208 /**
209 * @param tableDir the directory to write the files into
210 */
211 public void setDir(File tableDir) {
212 dir = tableDir;
213 }
214
215 public void setSchema(String schema) {
216 this.schema = schema;
217 }
218
219 public void setCatalog(String catalog) {
220 this.catalog = catalog;
221 }
222
223 /**
224 * returns all Tables included by not excluded
225 *
226 * @return List of Strings each naming a table
227 */
228 protected List<String> getTableNames() {
229 List<String> tableNamesFromDB;
230
231 Connection connection = null;
232 try {
233 connection = this.getConnection();
234 tableNamesFromDB = DatabaseMetaDataUtil.getTables(catalog,schema,connection);
235 } catch (SQLException e) {
236 throw new BuildException(e);
237 } finally {
238 if (connection != null)
239 try {
240 connection.close();
241 } catch (SQLException e) {
242
243 }
244 }
245
246 return Tables.narrow(tableNamesFromDB,includes,excludes);
247 }
248 }