1 /*
2 * PROJECT : DAR Runtime and Tools
3 * COPYRIGHT : Copyright (C) 1999-2004 tim.stephenson@enableit.org
4 * LICENSE : GNU LESSER GENERAL PUBLIC LICENSE
5 * Version 2.1, February 1999
6 *
7 * This program is free software; you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License as published by
9 * the Free Software Foundation; either version 2 of the License, or
10 * (at your option) any later version.
11 *
12 * This program is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License
18 * along with this program; if not, write to the Free Software
19 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
20 */
21 package org.enableit.db;
22
23 import java.io.File;
24 import java.io.FileReader;
25 import java.io.LineNumberReader;
26 import java.math.BigDecimal;
27 import java.sql.Connection;
28 import java.util.ArrayList;
29 import java.util.Date;
30 import java.util.Iterator;
31 import java.util.StringTokenizer;
32
33 import org.apache.log4j.Logger;
34
35
36 /***
37 * A class to read CSV files and load data into a database.
38 *
39 * @version v1.3
40 *
41 * @author __AUTHOR__
42 * @deprecated Functionality consolidated into org.enableit.db.darrt.DataHandler
43 */
44 public class DataLoader extends AbstractDBUtility {
45 /*
46 * Properties
47 */
48
49 /***
50 * The Log4J <code>Logger</code> doing the logging.
51 */
52 private static Logger logger = Logger.getLogger(DataLoader.class);
53 private static final String COMMA = ",";
54
55 /***
56 * CVS info about this class and its current version
57 */
58 public static final String ABOUT = "$Id: DataLoader.java,v 1.6 2004/03/19 19:10:34 tim Exp $";
59
60 /***
61 * The file to read.
62 */
63 private File file;
64
65 /***
66 * The Table to load into.
67 */
68 private String table;
69
70 /***
71 * The SQL to do load.
72 */
73 private String sql;
74
75 /***
76 * The columns to load into.
77 */
78 private ArrayList columns;
79
80 /***
81 * The datasource name to use to get a database connection.
82 */
83 private String dataSourceName;
84 private Connection conn;
85
86 /*
87 * Constructors
88 */
89
90 /***
91 * Default Constructor
92 */
93 public DataLoader() {
94 }
95
96 /***
97 * Constructor setting mandatory properties.
98 */
99 public DataLoader(String file) {
100 setFile(file);
101 }
102
103 /*
104 * Set Methods
105 */
106
107 /***
108 * Set the file to be loaded.
109 */
110 public void setFile(File file) {
111 // Log the entry into the method.
112 logger.debug("METHOD_ENTRY");
113
114 this.file = file;
115 }
116
117 /***
118 * Set the file to be loaded.
119 */
120 public void setFile(String file) {
121 // Log the entry into the method.
122 logger.debug("METHOD_ENTRY");
123
124 this.file = new File(file);
125 }
126
127 /***
128 * Set the file to be loaded. <br>
129 * This field is not mandatory if metadat is supplied at the top of the
130 * CSV file.
131 *
132 */
133 public void setTable(String table) {
134 // Log the entry into the method.
135 logger.debug("METHOD_ENTRY");
136
137 this.table = table;
138
139 // Log the exit from the method.
140 logger.debug("<EXIT>");
141 }
142
143 /***
144 * Set the names of columns to be inserted into.
145 *
146 * @param
147 * columns as a comma separated list.
148 */
149 public void setColumnNames(String columns) {
150 // Log the entry into the method.
151 logger.debug("METHOD_ENTRY");
152 logger.debug("... with param: columns=" + columns);
153
154 StringTokenizer st = new StringTokenizer(columns, ",");
155
156 this.columns = new ArrayList();
157
158 while (st.hasMoreElements()) {
159 this.columns.add(new Column(st.nextToken()));
160 }
161 }
162
163 /***
164 * Set the datatypes of the columns to be inserted into.
165 *
166 * @param
167 * columns as a comma separated list.
168 */
169 public void setColumnDatatypes(String datatypes)
170 throws DBException {
171 // Log the entry into the method.
172 logger.debug("METHOD_ENTRY");
173 logger.debug("... with param: columns=" + datatypes);
174
175 StringTokenizer st = new StringTokenizer(datatypes, ",");
176
177 for (Iterator i = columns.iterator(); i.hasNext();) {
178 Object obj = i.next();
179 Column col = (Column) obj;
180
181 col.setDatatype(st.nextToken());
182 }
183
184 setSqlStatement();
185
186 // Log the exit from the method.
187 logger.debug("<EXIT>");
188 logger.debug("... columns=" + columns);
189 }
190
191 /***
192 * Prepares the SQL statement based on column names and datatypes.
193 *
194 * @throws DBException If the column data has not been set yet.
195 */
196 private void setSqlStatement()
197 throws DBException {
198 logger.info("METHOD_ENTRY: setSqlStatement");
199
200 if (columns != null) {
201 // Prepare the SQL statement
202 StringBuffer sb = new StringBuffer();
203
204 sb.append("INSERT INTO ");
205 sb.append(table);
206 sb.append("(");
207
208 for (Iterator i = columns.iterator(); i.hasNext();) {
209 sb.append(((Column) i.next()).getName());
210
211 if (i.hasNext()) {
212 sb.append(",");
213 }
214 }
215
216 sb.append(")");
217 sb.append("VALUES (");
218
219 for (int i = 0; i < columns.size(); i++) {
220 sb.append("?");
221
222 if (i < (columns.size() - 1)) {
223 sb.append(",");
224 }
225 }
226
227 sb.append(")");
228 sql = sb.toString();
229 } else {
230 throw new DBException(
231 "Attempt to create SQL statement before column data set.");
232 }
233
234 logger.info("METHOD_EXIT: setSqlStatement, sql=" + sql);
235 }
236
237 /***
238 * Set the datasource name to get connections from.
239 */
240 public void setDataSourceName(String dataSourceName) {
241 // Log the entry into the method.
242 logger.debug("METHOD_ENTRY");
243
244 this.dataSourceName = dataSourceName;
245 }
246
247 /*
248 * Implementation methods
249 */
250
251 /***
252 * Do the load.
253 */
254 public void execute()
255 throws DBException {
256 // Log the entry into the method.
257 logger.info("METHOD_ENTRY execute");
258
259 try {
260 // If have connection props get them once and share the connection
261 if ((driver != null) && (url != null) && (userid != null)
262 && (password != null)) {
263 // If have specified connection properties use them...
264 conn = ConnectionFactory.getConnection(driver, url, userid,
265 password);
266
267 if (conn == null) {
268 throw new DBException(
269 "Null connection when connecting to database");
270 }
271 }
272
273 // Open file
274 FileReader fileReader = new FileReader(file);
275 LineNumberReader reader = new LineNumberReader(fileReader);
276
277 // Read and process each row
278 String line = reader.readLine();
279
280 ;
281
282 do {
283 if (line.startsWith("#")) {
284 // This is a comment, perhaps containing meta-data
285 processComment(line);
286 } else {
287 // presumable a data row
288 process(line);
289 }
290
291 line = reader.readLine();
292 } while (line != null);
293
294 conn.close();
295 conn = null;
296 } catch (java.io.IOException e) {
297 logger.error(e);
298 throw new DBException(e.getMessage());
299 } catch (java.sql.SQLException e) {
300 logger.error(e);
301 throw new DBException(e.getMessage());
302 }
303
304 logger.info("METHOD_EXIT: execute");
305 }
306
307 /***
308 * Processes a comment line, looking for meta data.
309 */
310 private void processComment(String line)
311 throws DBException {
312 // Log the entry into the method.
313 logger.debug("METHOD_ENTRY processComment");
314
315 // Look for the table name
316 if (line.startsWith("#TABLE")) {
317 setTable(line.substring(6).trim());
318 }
319
320 if (line.startsWith("#NAMES")) {
321 setColumnNames(line.substring(6).trim());
322 }
323
324 if (line.startsWith("#DATATYPES")) {
325 setColumnDatatypes(line.substring(11).trim());
326 }
327
328 logger.info("METHOD_EXIT: processComment");
329 }
330
331 /***
332 * Processes a data line.
333 */
334 private void process(String line) {
335 // Log the entry into the method.
336 logger.debug("METHOD_ENTRY process");
337
338 // TODO handle all values as single sql string (leave database to detect type) when have no data types provided
339 // This can be done using old version of this class to provide processUntyped method
340 StringTokenizer values = new StringTokenizer(line, ",", true);
341
342 try {
343 // Bind variables into the statement
344 ArrayList vars = new ArrayList();
345 String lastToken = null;
346
347 for (Iterator i = columns.iterator(); i.hasNext();) {
348 String datatype = ((Column) i.next()).getDatatype();
349 String nextToken = values.nextToken();
350
351 //logger.debug("B4: lastToken=" + lastToken + ", nextToken=" + nextToken);
352 if (COMMA.equals(nextToken) && !COMMA.equals(lastToken)) {
353 //logger.debug("advance");
354 lastToken = nextToken;
355 nextToken = values.nextToken();
356
357 if (COMMA.equals(nextToken) && COMMA.equals(lastToken)) {
358 //logger.debug("sub null");
359 lastToken = COMMA;
360 nextToken = "null";
361 } else {
362 lastToken = nextToken;
363 }
364 } else if (COMMA.equals(nextToken) && COMMA.equals(lastToken)) {
365 //logger.debug("sub null");
366 lastToken = COMMA;
367 nextToken = null;
368 } else {
369 lastToken = nextToken;
370
371 //logger.debug("no correction");
372 }
373
374 //logger.debug("lastToken=" + lastToken + ", nextToken=" + nextToken);
375 Object var = null;
376
377 if (datatype.startsWith("DATE")) {
378 if (nextToken == null) {
379 var = new SqlType(SqlType.DATE);
380 } else {
381 //sb.append("'" + nextToken + "'") ;
382 Object obj = org.enableit.db.ConvertUtils.convert((Object) nextToken,
383 java.util.Date.class);
384
385 if (obj instanceof String) {
386 logger.error(
387 "Failed to convert nextToken to Date, substituting null");
388 var = new SqlType(SqlType.DATE);
389 } else {
390 Date date = (Date) obj;
391
392 var = new java.sql.Date(date.getTime());
393 }
394
395 //logger.debug("Setting DATE type to " + nextToken);
396 }
397 } else if (datatype.startsWith("CHAR")) {
398 if (nextToken == null) {
399 var = new SqlType(SqlType.STRING);
400 } else {
401 var = nextToken;
402
403 //logger.debug("Setting CHAR type to " + nextToken);
404 }
405 } else if (datatype.startsWith("INTEGER")) {
406 if (nextToken == null) {
407 var = new SqlType(SqlType.INTEGER);
408 } else {
409 try {
410 var = new Integer(nextToken);
411 } catch (NumberFormatException e) {
412 logger.error("Failed to convert " + nextToken
413 + " to Integer, substituting null");
414 var = new SqlType(SqlType.INTEGER);
415 }
416
417 //logger.debug("Setting INTEGER type to " + nextToken);
418 }
419 } else if (datatype.startsWith("DECIMAL")) {
420 if (nextToken == null) {
421 var = new SqlType(SqlType.BIGDECIMAL);
422 } else {
423 try {
424 var = new BigDecimal(nextToken);
425 } catch (NumberFormatException e) {
426 logger.error("Failed to convert " + nextToken
427 + " to BigDecimal, substituting null");
428 var = new SqlType(SqlType.BIGDECIMAL);
429 }
430
431 //logger.debug("Setting INTEGER type to " + nextToken);
432 }
433 } else {
434 logger.debug("Unhandled! Data type=" + datatype);
435
436 //sb.append(nextToken) ;
437 }
438
439 vars.add(var);
440 }
441
442 logger.debug("Variables=" + vars);
443
444 // Execute the SQL
445 if (conn != null) {
446 DatabaseProxy.executeUpdate(conn, sql, vars);
447 } else {
448 // ... otherwise rely on preconfigured option for connection
449 GenericDBProxy.executeUpdate(sql, vars);
450 }
451 } catch (DBException e) {
452 // Log and continue with next line
453 logger.error(e);
454 } catch (Exception e) {
455 //e.printStackTrace() ;
456 // Log and continue with next line
457 logger.error(sql);
458 logger.error(e);
459 }
460
461 logger.info("METHOD_EXIT: process");
462 }
463
464 /***
465 * Used as a simple test of the class.
466 */
467 public static void main(String[] args) {
468 try {
469 new DataLoader(args[0]).execute();
470 } catch (Exception e) {
471 e.printStackTrace();
472 }
473 }
474 }
This page was automatically generated by Maven