001/* 002 * Copyright (c) 2004-2010 The Regents of the University of California. 003 * All rights reserved. 004 * 005 * '$Author: welker $' 006 * '$Date: 2010-05-06 05:21:26 +0000 (Thu, 06 May 2010) $' 007 * '$Revision: 24234 $' 008 * 009 * Permission is hereby granted, without written agreement and without 010 * license or royalty fees, to use, copy, modify, and distribute this 011 * software and its documentation for any purpose, provided that the above 012 * copyright notice and the following two paragraphs appear in all copies 013 * of this software. 014 * 015 * IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY 016 * FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES 017 * ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF 018 * THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF 019 * SUCH DAMAGE. 020 * 021 * THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, 022 * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF 023 * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE 024 * PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF 025 * CALIFORNIA HAS NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, 026 * ENHANCEMENTS, OR MODIFICATIONS. 027 * 028 */ 029 030package org.ecoinformatics.seek.dataquery; 031 032import java.io.InputStream; 033import java.sql.Connection; 034import java.sql.PreparedStatement; 035import java.sql.SQLException; 036import java.sql.Statement; 037import java.sql.Timestamp; 038import java.util.Vector; 039 040import org.apache.commons.logging.Log; 041import org.apache.commons.logging.LogFactory; 042import org.kepler.configuration.ConfigurationManager; 043import org.kepler.configuration.ConfigurationProperty; 044import org.kepler.objectmanager.data.UnresolvableTypeException; 045import org.kepler.objectmanager.data.db.Attribute; 046import org.kepler.objectmanager.data.db.Entity; 047import org.kepler.objectmanager.data.text.TextComplexFormatDataReader; 048import org.kepler.util.DelimitedReader; 049import org.kepler.util.sql.DatabaseFactory; 050 051/** 052 * The class to generate db tables base table entity 053 * 054 * @author Jing Tao 055 * 056 */ 057 058public class DBTablesGenerator implements Runnable { 059 060 // Constant 061 private static final String CREATETEXTABLEPATH = "//sqlEngine[sqlEngineName=\"hsql\"]/SQLDictionary/textTable/createTextTable"; 062 private static final String CREATETABLEPATH = "//sqlEngine[sqlEngineName=\"hsql\"]/SQLDictionary/createTable"; 063 private static final String IFEXISTSPATH = "//sqlEngine[sqlEngineName=\"hsql\"]/SQLDictionary/dropSuffix"; 064 private static final String SEMICOLONPATH = "//sqlEngine[sqlEngineName=\"hsql\"]/SQLDictionary/semicolon"; 065 private static final String FIELDSPEPATH = "//sqlEngine[sqlEngineName=\"hsql\"]/SQLDictionary/textTable/fieldSeperator"; 066 private static final String SETTABLEPATH = "//sqlEngine[sqlEngineName=\"hsql\"]/SQLDictionary/textTable/setTable"; 067 private static final String SOURCEPATH = "//sqlEngine[sqlEngineName=\"hsql\"]/SQLDictionary/textTable/source"; 068 private static final String IGNOREFIRSTPATH = "//sqlEngine[sqlEngineName=\"hsql\"]/SQLDictionary/textTable/ignoreFirst"; 069 private static final String VECTORDATATYPE = "vectorDataType"; 070 private static final String TEXTFILETYPE = "textFileType"; 071 072 public static final String CREATETEXTTABLE; 073 public static String CREATETABLE; 074 public static final String DROPTABLE = "DROP TABLE"; 075 public static String IFEXISTS; 076 public static final String LEFTPARENTH = "("; 077 public static final String RIGHTPARENTH = ")"; 078 public static String SEMICOLON = ";"; 079 public static final String SPACE = " "; 080 public static final String COMMA = ","; 081 public static final String QUOTE = "\""; 082 public static String FIELDSEPATATOR; 083 public static final String SELECT = "SELECT"; 084 public static final String INSERT = "INSERT INTO"; 085 public static final String DELETE = "DELETE"; 086 public static final String WHERE = "WHERE"; 087 public static final String FROM = "FROM"; 088 public static final String LIKE = "LIKE"; 089 public static final String VALUES = "VALUES"; 090 public static final String AND = "AND"; 091 public static final String QUESTION = "?"; 092 public static String SETTABLE; 093 public static String SOURCE; 094 public static String IGNOREFIRST; 095 public static final String STRING = "String"; 096 public static final String INTEGER = "Integer"; 097 public static final String LONG = "Long"; 098 public static final String DOUBLE = "Double"; 099 public static final String FLOAT = "Float"; 100 public static final String DATETIME = "Timestamp"; 101 public static final String BOOLEAN = "Boolean"; 102 103 private Vector dbJavaDataTypeList = new Vector(); 104 private Entity tableEntity; 105 private InputStream givenData; 106 private String textFileLocation; 107 private String type; 108 private boolean isDone = false; 109 private boolean successStatus = false; 110 private boolean isRefresh = false; 111 112 private static Log log; 113 private static boolean isDebugging; 114 115 static { 116 log = LogFactory.getLog("org.ecoinformatics.seek.dataquery"); 117 isDebugging = log.isDebugEnabled(); 118 119 ConfigurationManager confMan = ConfigurationManager.getInstance(); 120 ConfigurationProperty commonProperty = confMan.getProperty(ConfigurationManager.getModule("common")); 121 ConfigurationProperty sqlEngineProperty = (ConfigurationProperty)commonProperty 122 .findProperties("sqlEngineName", "hsql", true).get(0); 123 124 CREATETEXTTABLE = sqlEngineProperty.getProperty("SQLDictionary.textTable.createTextTable").getValue(); 125 CREATETABLE = sqlEngineProperty.getProperty("SQLDictionary.createTable").getValue(); 126 IFEXISTS = sqlEngineProperty.getProperty("SQLDictionary.dropSuffix").getValue(); 127 SEMICOLON = sqlEngineProperty.getProperty("SQLDictionary.semicolon").getValue(); 128 FIELDSEPATATOR = sqlEngineProperty.getProperty("SQLDictionary.textTable.fieldSeperator").getValue(); 129 SETTABLE = sqlEngineProperty.getProperty("SQLDictionary.textTable.setTable").getValue(); 130 SOURCE = sqlEngineProperty.getProperty("SQLDictionary.textTable.source").getValue(); 131 IGNOREFIRST = sqlEngineProperty.getProperty("SQLDictionary.textTable.ignoreFirst").getValue(); 132 } 133 134 /** 135 * This constructor is for non-text file table. Before create the 136 * consturctor user should run DBTableNameResolver first. 137 * 138 * @param tableEntity 139 * TableEntity table will generated base on the object 140 * @param givenData 141 * InputStream data will be load to table. The input stream which 142 * from data file. It should be text format 143 */ 144 public DBTablesGenerator(Entity tableEntity, InputStream givenData) { 145 // default set refresh is false 146 this(tableEntity, givenData, false); 147 }// DBTablesGenerator 148 149 /** 150 * This constructor is for text file table. Before create the consturctor 151 * user should run DBTableNameResolver first. 152 * 153 * @param tableEntity 154 * TableEntity 155 * @param textFileLocation 156 * String 157 */ 158 public DBTablesGenerator(Entity tableEntity, String textFileLocation) { 159 // default set refresh is false 160 this(tableEntity, textFileLocation, false); 161 }// DBTablesGenerator 162 163 /** 164 * This constructor is for non-text file table. Before create the 165 * consturctor user should run DBTableNameResolver first. 166 * 167 * @param tableEntity 168 * TableEntity table will generated base on the object 169 * @param givenData 170 * Vector[] data will be load to table. This a vector array, each 171 * vector is one row of data. The element in vector is a string 172 * @param isRefresh 173 * boolean force to re-generate table again 174 */ 175 public DBTablesGenerator(Entity tableEntity, InputStream givenData, 176 boolean isRefresh) { 177 this.tableEntity = tableEntity; 178 this.givenData = givenData; 179 this.type = VECTORDATATYPE; 180 this.isRefresh = isRefresh; 181 }// DBTablesGenerator 182 183 /** 184 * This constructor is for text file table. Before create the consturctor 185 * user should run DBTableNameResolver first. 186 * 187 * @param tableEntity 188 * TableEntity 189 * @param textFileLocation 190 * String 191 * @param isRefresh 192 * boolean force to re-generate table again 193 */ 194 public DBTablesGenerator(Entity tableEntity, String textFileLocation, 195 boolean isRefresh) { 196 this.tableEntity = tableEntity; 197 this.textFileLocation = textFileLocation; 198 this.type = TEXTFILETYPE; 199 this.isRefresh = isRefresh; 200 }// DBTablesGenerator 201 202 /** 203 * A thread to call some private method to generate table. 204 */ 205 public void run() { 206 generateTables(isRefresh); 207 }// run 208 209 /** 210 * Method to get success status of generating table 211 * 212 * @return boolean 213 */ 214 public synchronized boolean getSuccessStatus() { 215 return this.successStatus; 216 } 217 218 /** 219 * Method to get isDone status of generationg table 220 * 221 * @return boolean 222 */ 223 public synchronized boolean getIsDone() { 224 return this.isDone; 225 } 226 227 /* 228 * This method will generate tables base on given type 229 */ 230 private void generateTables(boolean refresh) { 231 if (type == null) { 232 successStatus = false; 233 isDone = true; 234 } else if (type.equals(VECTORDATATYPE)) { 235 // generate vector table 236 successStatus = generateDBTableForGivenData(refresh); 237 isDone = true; 238 } else if (type.equals(TEXTFILETYPE)) { 239 // gernate text table 240 successStatus = generateDBTextTable(refresh); 241 isDone = true; 242 } else { 243 successStatus = false; 244 isDone = true; 245 } 246 247 } 248 249 /* 250 * This is for non-text file tables. And will load data into db. If it is 251 * refresh, it will delete the record in system table and drop the old 252 * table. Then generate new table and create an record in system table 253 * 254 * @param givenEntityList Hashtable 255 * 256 * @param givenData Vector[] This a vector array, each vector is one row of 257 * data. The element in vector is a string 258 */ 259 private synchronized boolean generateDBTableForGivenData(boolean refresh) { 260 boolean success = false; 261 if (tableEntity == null) { 262 log.debug("The entity is null and couldn't create table for it"); 263 return success; 264 } 265 // get table name 266 String tableName = null; 267 String url = null; 268 DBTableExistenceChecker checker = null; 269 try { 270 checker = new DBTableExistenceChecker(); 271 url = tableEntity.getURL(); 272 if (url == null) { 273 url = tableEntity.getName(); 274 } 275 if (isDebugging) { 276 log.debug("url is " + url); 277 } 278 // if the table already existed, we don't need 279 // generate again(url is the key) 280 if (checker.isURLExisted(url)) { 281 // to do get the table name and set to table entity 282 tableName = checker.getTableName(url); 283 tableEntity.setDBTableName(tableName); 284 if (isDebugging) { 285 log.debug("Table " + tableName + " is existed for url " 286 + url); 287 } 288 if (!refresh) { 289 // the table already existed, if not refresh, we need to 290 // stop here 291 if (isDebugging) { 292 log.debug("refesh setting is " + refresh 293 + " and we don't need generate table again"); 294 } 295 success = true; 296 return success; 297 } else { 298 // table already existed. But we need refresh it - delete 299 // record in 300 // System and drop the table 301 if (isDebugging) { 302 log 303 .debug("refesh setting is " 304 + refresh 305 + " and we need drop table and generate table again"); 306 } 307 success = cleanUpRecord(tableName, url, checker); 308 // if couldn't drop or delete record, return false 309 if (success == false) { 310 return success; 311 } 312 } 313 } 314 // if doesn't exited, we need to get the table name for TableEntity. 315 // so before run this method, we need run DBTableNameResovler first. 316 tableName = tableEntity.getDBTableName(); 317 if (isDebugging) { 318 log.debug("The table name " + tableName + " will be generated"); 319 } 320 if (tableName == null || tableName.trim().equals("")) { 321 log 322 .debug("The DB table name for given TableEntity object is null and couldn't generate table"); 323 success = false; 324 return success; 325 } 326 327 } catch (Exception ee) { 328 log.debug("The error in generate table is ", ee); 329 success = false; 330 return success; 331 } 332 333 try { 334 // this is for non-text type table 335 String generateTalbeSql = generateDDLForOneEntity(CREATETABLE, 336 tableName, tableEntity); 337 excuteSQLCommand(generateTalbeSql); 338 loadDataIntoTable(tableName, tableEntity, givenData); 339 success = true; 340 } catch (Exception e) { 341 log.debug("The error in generate table is ", e); 342 success = false; 343 } 344 // if success, we need store tablename and url 345 if (success) { 346 try { 347 checker.storeTableRecord(tableName, url); 348 tableEntity.setDBTableName(tableName); 349 } catch (Exception ee) { 350 log.debug("The error in generate table is ", ee); 351 success = false; 352 } 353 } 354 355 // if not success, we need drop the generate table 356 if (!success) { 357 cleanUpRecord(tableName, url, checker); 358 } 359 return success; 360 361 }// generatetable 362 363 /* 364 * This is for text file tables. And will load data into db. 365 * 366 * @param givenEntityList Hashtable 367 * 368 * @param givenData Vector[] This a vector array, each vector is one row of 369 * data. The element in vector is a string 370 */ 371 private synchronized boolean generateDBTextTable(boolean refresh) { 372 boolean success = false; 373 if (tableEntity == null) { 374 log.debug("The entity is null and couldn't create table for it"); 375 return success; 376 } 377 378 // get table name 379 String tableName = null; 380 DBTableExistenceChecker checker = null; 381 String url = null; 382 try { 383 checker = new DBTableExistenceChecker(); 384 url = tableEntity.getURL(); 385 if (url == null) { 386 url = tableEntity.getName(); 387 } 388 if (isDebugging) { 389 log.debug("The url in entity is " + url); 390 } 391 // if the table already existed, we don't need 392 // generate again(url is the key) 393 if (checker.isURLExisted(url)) { 394 // to do get the table name and set to table entity 395 tableName = checker.getTableName(url); 396 tableEntity.setDBTableName(tableName); 397 if (isDebugging) { 398 log.debug("Table " + tableName + " is existed for url " 399 + url); 400 } 401 if (!refresh) { 402 // the table already existed, if not refresh, we need to 403 // stop here 404 if (isDebugging) { 405 log.debug("refesh setting is " + refresh 406 + " and we don't need generate table again"); 407 } 408 success = true; 409 return success; 410 } else { 411 // table already existed. But we need refresh it - delete 412 // record in 413 // System and drop the table 414 if (isDebugging) { 415 log 416 .debug("refesh setting is " 417 + refresh 418 + " and we need drop table and generate table again"); 419 } 420 success = cleanUpRecord(tableName, url, checker); 421 // if couldn't drop or delete record, return false 422 if (success == false) { 423 return success; 424 } 425 } 426 427 } 428 429 // if doesn't exited, we need to get the table name for TableEntity. 430 // so before run this method, we need run DBTableNameResovler first. 431 tableName = tableEntity.getDBTableName(); 432 if (isDebugging) { 433 log.debug("The table name " + tableName + " will be generated"); 434 } 435 if (tableName == null || tableName.trim().equals("")) { 436 log 437 .debug("The DB table name for given TableEntity object is null and couldn't generate table"); 438 success = false; 439 return success; 440 } 441 442 } catch (Exception ee) { 443 log.debug("The error in generateDBTable is ", ee); 444 success = false; 445 return success; 446 } 447 448 try { 449 450 int numOfHeadLines = tableEntity.getNumHeaderLines(); 451 // hsql only handle two scenaro no head line or one head line 452 boolean ignoreHeadLines = false; 453 if (numOfHeadLines == 0) { 454 ignoreHeadLines = false; 455 } else if (numOfHeadLines == 1) { 456 ignoreHeadLines = true; 457 } else { 458 if (isDebugging) { 459 log.debug("HSQL text table only handle one line header" 460 + " and this entity has " + numOfHeadLines 461 + " headlines"); 462 } 463 success = false; 464 return success; 465 } 466 467 // if this is attribute row oriented, hsql can't handle it 468 String orientation = tableEntity.getOrientation(); 469 if (orientation != null && orientation.equals(Entity.ROWMAJOR)) { 470 log 471 .debug("DB doesn't handle a text table which attribute is row oriented"); 472 success = false; 473 return success; 474 } 475 476 // this is for text type table 477 String generateTableSql = generateDDLForOneEntity(CREATETEXTTABLE, 478 tableName, tableEntity); 479 excuteSQLCommand(generateTableSql); 480 481 // bind text source to table 482 String delimiterStr = tableEntity.getDelimiter(); 483 // need to figure out the delimiter str in db. The format is 484 // different 485 DelimiterResolver resolver = new DelimiterResolver(); 486 String dbDelimiter = resolver.resolve(delimiterStr); 487 String bindTextFileToTalbeSql = generateBindTextFileToTableSQL( 488 tableName, textFileLocation, dbDelimiter, ignoreHeadLines); 489 490 excuteSQLCommand(bindTextFileToTalbeSql); 491 success = true; 492 } catch (Exception sql) { 493 if (log.isDebugEnabled()) { 494 sql.printStackTrace(); 495 } 496 log.error("The error in generateDBTable is " + sql.getMessage()); 497 success = false; 498 } 499 500 // if success, we need store tablename and url 501 if (success) { 502 try { 503 checker.storeTableRecord(tableName, url); 504 tableEntity.setDBTableName(tableName); 505 } catch (Exception ee) { 506 success = false; 507 } 508 } 509 510 // if not success, we need drop the generate table 511 if (!success) { 512 cleanUpRecord(tableName, url, checker); 513 } 514 515 return success; 516 517 }// generateTable 518 519 /* 520 * roll back method. This method will delete the record generate in system 521 * table and also drop the generated table 522 */ 523 private boolean cleanUpRecord(String tableName, String url, 524 DBTableExistenceChecker checker) { 525 boolean success = true; 526 // drop the table 527 String drop = generateDropSqlCommand(tableName); 528 try { 529 // drop the existed table 530 excuteSQLCommand(drop); 531 // delete the record from system table 532 checker.deleteRecord(tableName, url); 533 } catch (Exception ee) { 534 success = false; 535 } 536 return success; 537 } 538 539 /* 540 * Method to generate drop sql command 541 */ 542 private synchronized String generateDropSqlCommand(String tableName) { 543 String sql = DROPTABLE + SPACE + tableName + SPACE + IFEXISTS 544 + SEMICOLON; 545 return sql; 546 } 547 548 /* 549 * Create a table base one given DDL 550 */ 551 private synchronized void excuteSQLCommand(String sql) throws SQLException, 552 ClassNotFoundException { 553 Connection conn = null; 554 Statement st = null; 555 if (isDebugging) { 556 log.debug("The sql command to run is " + sql); 557 } 558 try { 559 conn = DatabaseFactory.getDBConnection(); 560 st = conn.createStatement(); 561 st.execute(sql); 562 } finally { 563 st.close(); 564 conn.close(); 565 } 566 }// generateTable 567 568 /* 569 * Method to load data into table. If error happend, it will roll back. 570 * Vector is String vector in vector array data. 571 */ 572 private synchronized void loadDataIntoTable(String tableName, Entity table, 573 InputStream dataStream) throws SQLException, 574 ClassNotFoundException, IllegalArgumentException, Exception { 575 if (dataStream == null) { 576 return; 577 } 578 579 PreparedStatement pStatement = null; 580 Connection conn = DatabaseFactory.getDBConnection(); 581 conn.setAutoCommit(false); 582 try { 583 String insertCommand = generateInsertCommand(tableName, table); 584 pStatement = conn.prepareStatement(insertCommand); 585 // int length = data.length; 586 587 if (!table.getIsImageEntity() && table.isSimpleDelimited()) { 588 // create SimpleDelimiter reader 589 int numCols = table.getAttributes().length; 590 String delimiter = table.getDelimiter(); 591 int numHeaderLines = table.getNumHeaderLines(); 592 String lineEnding = table.getPhysicalLineDelimiter(); 593 if (lineEnding == null || lineEnding.trim().equals("")) { 594 lineEnding = table.getRecordDelimiter(); 595 } 596 int numRecords = table.getNumRecords(); 597 boolean stripHeader = true; 598 DelimitedReader simpleReader = new DelimitedReader(dataStream, 599 numCols, delimiter, numHeaderLines, lineEnding, 600 numRecords, stripHeader); 601 Vector row = simpleReader.getRowDataVectorFromStream(); 602 while (!row.isEmpty()) { 603 // insert one row data into table 604 int sizeOfRow = row.size(); 605 for (int j = 0; j < sizeOfRow; j++) { 606 String dataElement = (String) row.elementAt(j); 607 // get data type for the vector which already has the 608 // cloumn java 609 // type info after parsing attribute in private method 610 // parseAttributeList 611 String javaType = (String) dbJavaDataTypeList 612 .elementAt(j); 613 // this method will binding data into preparedstatement 614 // base on 615 // java data type 616 // The index of pstatement start 1 (Not 0), so it should 617 // j+1. 618 pStatement = setupPreparedStatmentParameter(j + 1, 619 pStatement, dataElement, javaType); 620 621 } 622 pStatement.execute(); 623 row = simpleReader.getRowDataVectorFromStream(); 624 } 625 } else if (!table.getIsImageEntity() && !table.isSimpleDelimited()) { 626 TextComplexFormatDataReader complexReader = new TextComplexFormatDataReader( 627 dataStream, table); 628 Vector row = complexReader.getRowDataVectorFromStream(); 629 while (!row.isEmpty()) { 630 // insert one row data into table 631 int sizeOfRow = row.size(); 632 for (int j = 0; j < sizeOfRow; j++) { 633 String dataElement = (String) row.elementAt(j); 634 dataElement = dataElement.trim(); 635 // System.out.println("The data is "+ dataElement); 636 // get data type for the vector which already has the 637 // cloumn java 638 // type info after parsing attribute in private method 639 // parseAttributeList 640 String javaType = (String) dbJavaDataTypeList 641 .elementAt(j); 642 // this method will binding data into preparedstatement 643 // base on 644 // java data type 645 // The index of pstatement start 1 (Not 0), so it should 646 // j+1. 647 pStatement = setupPreparedStatmentParameter(j + 1, 648 pStatement, dataElement, javaType); 649 650 } 651 pStatement.execute(); 652 row = complexReader.getRowDataVectorFromStream(); 653 } 654 } 655 656 } catch (SQLException sqle) { 657 conn.rollback(); 658 pStatement.close(); 659 conn.close(); 660 throw sqle; 661 } catch (IllegalArgumentException le) { 662 conn.rollback(); 663 pStatement.close(); 664 conn.close(); 665 throw le; 666 } catch (Exception ue) { 667 conn.rollback(); 668 pStatement.close(); 669 conn.close(); 670 throw ue; 671 } 672 conn.commit(); 673 pStatement.close(); 674 conn.close(); 675 }// loadDataIntoTable 676 677 /* 678 * Method for generate sql command to create table 679 */ 680 private synchronized String generateDDLForOneEntity(String tableType, 681 String tableName, Entity table) throws SQLException, 682 UnresolvableTypeException { 683 StringBuffer sql = new StringBuffer(); 684 String textFileName = table.getFileName(); 685 int headLineNumber = table.getNumHeaderLines(); 686 String orientation = table.getOrientation(); 687 String delimiter = table.getDelimiter(); 688 sql.append(tableType); 689 sql.append(SPACE); 690 sql.append(tableName); 691 sql.append(LEFTPARENTH); 692 Attribute[] attributeList = table.getAttributes(); 693 String attributeSql = parseAttributeList(attributeList); 694 sql.append(attributeSql); 695 sql.append(RIGHTPARENTH); 696 sql.append(SEMICOLON); 697 String sqlStr = sql.toString(); 698 if (isDebugging) { 699 log.debug("The command to create tables is " + sqlStr); 700 } 701 return sqlStr; 702 }// generateDDLForOneEntity 703 704 /* 705 * Add attribute defination in create table command. If one attribute is 706 * null or has same error an exception will be throw 707 */ 708 private synchronized String parseAttributeList(Attribute[] list) 709 throws SQLException, UnresolvableTypeException { 710 StringBuffer attributeSql = new StringBuffer(); 711 if (list == null || list.length == 0) { 712 log.debug("There is no attribute defination in entity"); 713 throw new SQLException("There is no attribute defination in entity"); 714 } 715 int size = list.length; 716 DBDataTypeResolver dataTypeResolver = new DBDataTypeResolver(); 717 boolean firstAttribute = true; 718 for (int i = 0; i < size; i++) { 719 Attribute attribute = list[i]; 720 if (attribute == null) { 721 log.debug("One attribute defination is null attribute list"); 722 throw new SQLException( 723 "One attribute defination is null attribute list"); 724 } 725 String name = attribute.getName(); 726 String dataType = attribute.getDataType(); 727 String dbDataType = dataTypeResolver.resolveDBType(dataType); 728 String javaDataType = dataTypeResolver.resolveJavaType(dataType); 729 dbJavaDataTypeList.add(javaDataType); 730 if (!firstAttribute) { 731 attributeSql.append(COMMA); 732 } 733 attributeSql.append(QUOTE); 734 attributeSql.append(name); 735 attributeSql.append(QUOTE); 736 attributeSql.append(SPACE); 737 attributeSql.append(dbDataType); 738 firstAttribute = false; 739 740 }// for 741 return attributeSql.toString(); 742 }// parseAttributeList 743 744 /* 745 * Generate a sql command to for insert data into talbe. Here we use 746 * PreparedStatement. 747 */ 748 private synchronized String generateInsertCommand(String tableName, 749 Entity table) throws SQLException { 750 StringBuffer sql = new StringBuffer(); 751 sql.append(INSERT); 752 sql.append(SPACE); 753 sql.append(tableName); 754 sql.append(LEFTPARENTH); 755 Attribute[] list = table.getAttributes(); 756 if (list == null || list.length == 0) { 757 log.debug("There is no attribute defination in entity"); 758 throw new SQLException("There is no attribute defination in entity"); 759 } 760 int size = list.length; 761 // cloumna name part 762 boolean firstAttribute = true; 763 for (int i = 0; i < size; i++) { 764 Attribute attribute = list[i]; 765 if (attribute == null) { 766 log.debug("One attribute defination is null attribute list"); 767 throw new SQLException( 768 "One attribute defination is null attribute list"); 769 } 770 String name = attribute.getName(); 771 if (!firstAttribute) { 772 sql.append(COMMA); 773 } 774 sql.append(name); 775 firstAttribute = false; 776 } 777 sql.append(RIGHTPARENTH); 778 sql.append(SPACE); 779 sql.append(VALUES); 780 sql.append(SPACE); 781 sql.append(LEFTPARENTH); 782 // value part, use ? replace 783 firstAttribute = true; 784 for (int i = 0; i < size; i++) { 785 if (!firstAttribute) { 786 sql.append(COMMA); 787 } 788 sql.append(QUESTION); 789 firstAttribute = false; 790 } 791 sql.append(RIGHTPARENTH); 792 sql.append(SEMICOLON); 793 if (isDebugging) { 794 log.debug("The insert command is " + sql.toString()); 795 } 796 return sql.toString(); 797 } 798 799 /* 800 * Method to setup data for prepare statment 801 */ 802 private synchronized PreparedStatement setupPreparedStatmentParameter( 803 int index, PreparedStatement pStatement, String data, 804 String javaDataType) throws SQLException, 805 UnresolvableTypeException, IllegalArgumentException { 806 if (pStatement == null) { 807 return pStatement; 808 } 809 810 // get rid of white space 811 if (data != null) { 812 data = data.trim(); 813 } 814 815 // set default type as string 816 if (javaDataType == null) { 817 pStatement.setString(index, data); 818 } else { 819 820 if (javaDataType.equals(STRING)) { 821 pStatement.setString(index, data); 822 } else if (javaDataType.equals(INTEGER)) { 823 pStatement.setInt(index, (new Integer(data)).intValue()); 824 } else if (javaDataType.equals(DOUBLE)) { 825 pStatement.setDouble(index, (new Double(data)).doubleValue()); 826 } else if (javaDataType.equals(FLOAT)) { 827 pStatement.setFloat(index, (new Float(data)).floatValue()); 828 } else if (javaDataType.equals(BOOLEAN)) { 829 pStatement 830 .setBoolean(index, (new Boolean(data)).booleanValue()); 831 } else if (javaDataType.equals(LONG)) { 832 pStatement.setLong(index, (new Long(data)).longValue()); 833 } else if (javaDataType.equals(DATETIME)) { 834 pStatement.setTimestamp(index, Timestamp.valueOf(data)); 835 } else { 836 throw new UnresolvableTypeException( 837 "This java type " 838 + javaDataType 839 + " has NOT implement in " 840 + "DBTablesGenerator.setupPreparedStatmentParameter method"); 841 } 842 } 843 return pStatement; 844 }// setupPreparedStatmentParameter 845 846 private synchronized String generateBindTextFileToTableSQL( 847 String tableName, String textFilePath, String delimiter, 848 boolean ignoreFirstLine) throws SQLException { 849 if (textFilePath == null || textFilePath.trim().equals("")) { 850 log.debug("No file location specify for this text table"); 851 throw new SQLException( 852 "No file location specify for this text table"); 853 } 854 if (delimiter == null) { 855 throw new SQLException("No delimiter be specified in metadata"); 856 } 857 StringBuffer sql = new StringBuffer(); 858 sql.append(SETTABLE); 859 sql.append(SPACE); 860 sql.append(tableName); 861 sql.append(SPACE); 862 sql.append(SOURCE); 863 sql.append(SPACE); 864 sql.append(QUOTE); 865 sql.append(textFilePath); 866 sql.append(SEMICOLON); 867 // delimiter part 868 sql.append(FIELDSEPATATOR); 869 sql.append(delimiter); 870 871 if (ignoreFirstLine) { 872 sql.append(SEMICOLON); 873 sql.append(IGNOREFIRST); 874 } 875 sql.append(QUOTE); 876 if (isDebugging) { 877 log.debug("The set source command is " + sql.toString()); 878 } 879 return sql.toString(); 880 } 881 882}// DBTablesGenerator