1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 package net.sourceforge.addam.impexp;
20
21 import net.sourceforge.addam.impexp.csv.CSVParser;
22 import net.sourceforge.addam.util.TableUtil;
23
24 import org.apache.tools.ant.BuildException;
25 import org.apache.tools.ant.taskdefs.JDBCTask;
26
27 import java.io.*;
28 import java.sql.*;
29 import java.util.ArrayList;
30 import java.util.Collections;
31 import java.util.List;
32
33 public class ImportDataTask extends JDBCTask {
34 private boolean deleteFirst = false;
35 private boolean truncateFirst = false;
36 private Script pre;
37 private Script post;
38 private int batchSize = 0;
39 private final List<Tables> includes = new ArrayList<Tables>();
40 private final List<Tables> excludes = new ArrayList<Tables>();
41 protected File dir;
42
43 /**
44 * @param tableDir the directory to write the files into
45 */
46 public void setDir(File tableDir) {
47 dir = tableDir;
48 }
49
50 /**
51 * @param truncateFirst if true, 'truncateFirst table <table>' will be executed before
52 * importing new data from a file. the default value is false.
53 */
54 public void setTruncateFirst(boolean truncateFirst) {
55 this.truncateFirst = truncateFirst;
56 }
57
58 /**
59 * @param deleteFirst if true, 'delete from <table>' will be executed before
60 * importing new data from a file. the default value is false.
61 */
62 public void setDeleteFirst(boolean deleteFirst) {
63 this.deleteFirst = deleteFirst;
64 }
65
66 /**
67 * used for setting a script to execute before importing data
68 */
69 public void addPre(Script script) {
70 if (this.pre != null) throw new BuildException("cannot set multiple 'pre' scripts");
71 this.pre = script;
72 }
73
74 /**
75 * used for setting a script to execute after importing data
76 */
77 public void addPost(Script script) {
78 if (this.post != null) throw new BuildException("cannot set multiple 'post' scripts");
79 this.post = script;
80 }
81
82 public void setBatchSize(int batchSize) {
83 if (batchSize < 0) throw new BuildException("cannot set negative batchSize");
84 this.batchSize = batchSize;
85 }
86
87
88 /**
89 * Imports all data from the listed files into the named tables.
90 *
91 * @throws org.apache.tools.ant.BuildException
92 * if anything goes wrong
93 */
94 public void execute() throws BuildException {
95
96 if (pre != null) {
97 executeScript(pre);
98 }
99
100 if (deleteFirst || truncateFirst) {
101 List<String> reverseList = getTableNames();
102 Collections.reverse(reverseList);
103 for (String tableName : reverseList) {
104 if (deleteFirst || truncateFirst) this.emptyTable(tableName);
105 }
106 }
107
108 Connection connection = this.getConnection();
109 try {
110 for (String name : getTableNames()) {
111 this.importTable(name, connection);
112 }
113 } finally {
114 try {
115 connection.close();
116 } catch (SQLException e) {
117
118 }
119 }
120
121 if (post != null) {
122 executeScript(post);
123 }
124 }
125
126 private void executeScript(Script script) {
127 try {
128 Connection conn = this.getConnection();
129 for (Object o : script.getStatements()) {
130 String statementText = (String) o;
131 PreparedStatement stmt = null;
132 try {
133 if (statementText.startsWith("call")) {
134 stmt = conn.prepareCall(statementText);
135 System.out.println("calling: " + statementText);
136 } else {
137 stmt = conn.prepareStatement(statementText);
138 System.out.println("executing: " + statementText);
139 }
140 int rows = stmt.executeUpdate();
141 System.out.println("...updated " + rows + " rows");
142 } finally {
143 if (stmt != null) stmt.close();
144 }
145 }
146 conn.commit();
147 } catch (SQLException e) {
148 e.printStackTrace();
149 throw new BuildException(e);
150 }
151 }
152
153 private void emptyTable(String tableName) {
154 if (deleteFirst || truncateFirst) {
155 log("deleting from table... " + tableName);
156 try {
157 Connection conn = this.getConnection();
158 Statement stmt = null;
159 try {
160 stmt = conn.createStatement();
161 if (deleteFirst) stmt.executeUpdate("DELETE FROM " + tableName);
162 if (truncateFirst) stmt.executeUpdate("TRUNCATE TABLE " + tableName);
163 conn.commit();
164 } finally {
165 if (stmt != null) stmt.close();
166 }
167 } catch (SQLException e) {
168 e.printStackTrace();
169 throw new BuildException(e);
170 }
171 }
172 }
173
174 /**
175 * Imports all data from the file into the named table.
176 *
177 * @throws org.apache.tools.ant.BuildException
178 * if anything goes wrong
179 */
180 public void importTable(String tableName, Connection connection) throws BuildException {
181 File fromFile = new File(dir, TableUtil.getFileName(tableName));
182 log("importing " + fromFile + " to table " + tableName);
183
184 int row = 0, col = 0;
185
186 Reader reader = null;
187 String cmd = null;
188 List record = null;
189 PreparedStatement stmt;
190 try {
191
192 stmt = connection.prepareStatement("SELECT * FROM " + tableName);
193 stmt.setMaxRows(1);
194 ResultSet rs = stmt.executeQuery();
195 ResultSetMetaData rsmd = rs.getMetaData();
196 int numCols = rsmd.getColumnCount();
197
198 String columnNames[] = new String[numCols];
199 int columnTypes[] = new int[numCols];
200 String classNames[] = new String[numCols];
201 DataCodec valueGetters[] = new DataCodec[numCols];
202 for (int i = 1; i <= numCols; i++) {
203 columnNames[i - 1] = rsmd.getColumnName(i);
204 columnTypes[i - 1] = rsmd.getColumnType(i);
205 classNames[i - 1] = rsmd.getColumnClassName(i);
206 valueGetters[i - 1] = DataCodec.getCodec(classNames[i - 1]);
207 }
208
209 rs.close();
210 stmt.close();
211
212 reader = new FileReader(fromFile);
213 CSVParser parser = new CSVParser(reader);
214
215
216 StringBuffer colBuf = new StringBuffer();
217 StringBuffer valBuf = new StringBuffer();
218 record = parser.readLine();
219 for (int i = 0; i < record.size(); i++) {
220 String columnName = record.get(i).toString();
221 if (!columnName.equals(columnNames[i])) {
222 throw new BuildException("error in file " + fromFile +
223 " expected " + columnNames[i] +
224 " got " + columnName);
225 }
226 if (i > 0) {
227 colBuf.append(",");
228 valBuf.append(",");
229 }
230 colBuf.append(columnName);
231 valBuf.append("?");
232 }
233
234 cmd = "INSERT INTO " + tableName + " (" + colBuf.toString() +
235 ") VALUES (" + valBuf.toString() + ")";
236
237
238 stmt = connection.prepareStatement(cmd);
239 for (record = parser.readLine(); record != null; record = parser.readLine()) {
240 row++;
241
242 if (record.size() > 0) {
243
244 for (int i = 0; i < record.size(); i++) {
245 col = i;
246
247 String value = record.get(i).toString();
248
249
250
251 if (value != null && value.length() > 0) {
252 Object o = valueGetters[i].decode(value);
253 if (o instanceof java.sql.Timestamp) {
254 stmt.setTimestamp(i + 1, (Timestamp) o);
255 } else if (o instanceof byte[]) {
256 byte buf[] = (byte[]) o;
257 InputStream stream = new ByteArrayInputStream(buf);
258 stmt.setBinaryStream(i + 1, stream, buf.length);
259 } else if (o instanceof java.lang.String) {
260 stmt.setString(i + 1, (String) o);
261 } else {
262 stmt.setObject(i + 1, o, columnTypes[i]);
263 }
264 } else {
265 stmt.setNull(i + 1, columnTypes[i]);
266 }
267 }
268 if (batchSize == 0) {
269 stmt.execute();
270 } else {
271 stmt.addBatch();
272 if (row % batchSize == 0) {
273 stmt.executeBatch();
274 }
275 }
276 }
277 }
278 if (batchSize > 0) {
279 stmt.executeBatch();
280 }
281 log(" ...inserted " + row + " rows");
282 stmt.close();
283 connection.commit();
284 } catch (BuildException e) {
285 try {
286 for (Object value : record) {
287 cmd = cmd.replaceFirst("\\?", "\"" + value + "\"");
288 }
289 } finally {
290 log("error executing statement " + cmd);
291 log(" at row" + row + " col" + col);
292 }
293 throw e;
294 } catch (DataCodec.NoSuchCodec e) {
295 throw new BuildException(e);
296 } catch (Exception e) {
297 if (record != null) {
298 for (Object value : record) {
299 cmd = cmd.replaceFirst("\\?", "\"" + value + "\"");
300 }
301 }
302 log("error executing statement " + cmd);
303 log(" at row" + row + " col" + col);
304 e.printStackTrace();
305 throw new BuildException(e);
306 } finally {
307 try {
308 if (reader != null) {
309 reader.close();
310 }
311 } catch (IOException e) {
312 log(e.toString());
313 }
314 }
315 }
316
317 /**
318 * this is a shortcut for having an <includeTables> subelement by simply providing a filename as an attribute to this task
319 *
320 * @param tableFile a file containing an ordered list of tables
321 */
322 public void setIncludeFile(File tableFile) {
323 Tables tables = new Tables();
324 tables.setFile(tableFile);
325 includes.add(tables);
326 }
327
328 /**
329 * this is a shortcut for having a <excludeTables> subelement by simply providing a filename as an attribute to this task
330 *
331 * @param tableFile a file containing an ordered list of tables
332 */
333 public void setExcludeFile(File tableFile) {
334 Tables tables = new Tables();
335 tables.setFile(tableFile);
336 excludes.add(tables);
337 }
338
339 /**
340 * @param pattern a regexp pattern to compare table names to
341 */
342 public void setIncludesPattern(String pattern) {
343 Tables tables = new Tables();
344 tables.setPattern(pattern);
345 includes.add(tables);
346 }
347
348 /**
349 * @param pattern a regexp pattern to compare table names to
350 */
351 public void setExcludesPattern(String pattern) {
352 Tables tables = new Tables();
353 tables.setPattern(pattern);
354 excludes.add(tables);
355 }
356
357 /**
358 * @param names a comma separated list of table names
359 */
360 public void setIncludes(String names) {
361 Tables tables = new Tables();
362 tables.setNames(names);
363 includes.add(tables);
364 }
365
366 /**
367 * @param names a comma separated list of table names
368 */
369 public void setExcludes(String names) {
370 Tables tables = new Tables();
371 tables.setNames(names);
372 excludes.add(tables);
373 }
374
375 /**
376 * returns all Tables included by not excluded
377 *
378 * @return List of Strings each naming a table
379 */
380 protected List<String> getTableNames() {
381 List<String> names = new ArrayList<String>();
382 for (File file : dir.listFiles()) {
383 String tableName = TableUtil.getTableName(file);
384 if (tableName != null) {
385 names.add(tableName);
386 }
387 }
388 return Tables.narrow(names, includes, excludes);
389 }
390
391 }