ExampleCreateTable.java

// 
// Lapetus Ltd Java Class.  Copyright (c) Lapetus Systems Ltd, 2009, 2010. 
// ----------------------------------------------------------------------- 
// This is the intellectual property of Lapetus Systems Ltd, Artemis, Greece. 
// -------------------------------------------------------------------------- 
// www.lapetus-ltd.com, www.lapetus.com.gr, www.lapetus.eu 
// ------------------------------------------------------- 
// $LastChangedRevision: 1190 $ 
// $LastChangedDate:: 2010-11-17 13:21:35#$ 
// ---------------------------------------- 
// 
 
import com.lapetus_ltd._2009.xml.types.*; 
import com.lapetus_ltd.api.TLptsMainDatabase; 
import com.lapetus_ltd.api.common.TLptsCryptoUtil; 
import com.lapetus_ltd.api.common.logger.ILptsLogListener; 
import com.lapetus_ltd.api.common.logger.TLptsLog; 
import com.lapetus_ltd.api.common.logger.TLptsLogger; 
import com.lapetus_ltd.api.db.control.*; 
import com.lapetus_ltd.api.db.utils.TLptsDriverLoader; 
import com.lapetus_ltd.api.db.xml.types.*; 
 
import java.util.ResourceBundle; 
 
 
// 
// Class Description : This example shows the creation of a table and columns. 
// 
// This test uses the MS SQL database for the creation of tables, but there is commented code for other database types. 
// Please note that the classes and structures of the CREATION mechanism are geared up to working with a tree structure. 
// This means that there are separate classes for every type of settings and value. This is a bit long winded to program 
// but is extremely handy if one wishes to build a customised GUI against this capability. 
// The GUI supplied by dbJAPI uses these classes in its tree structure. 
// 
 
 
public class ExampleCreateTable 
{ 
  private String connectionMSSqlId = ""; 
 
  public static void main(String[] args) 
  { 
    new ExampleCreateTable(); 
  } 
 
  public ExampleCreateTable() 
  { 
    // required so that the API can function correctly. 
    TLptsMainDatabase.init(); 
 
    // register a listener for the logging system. 
    TLptsLogger.addListener(new ILptsLogListener() 
    { 
      public void newLogGenerated(TLptsLog log) 
      { 
        if (log.getType().equals(TLptsLogger.LOG_TYPE.ERROR)) 
          System.out.println("LOG ERROR :" + log.getMessage() + " : " + log.getSupportingText() + " : Exception : " + log.getExceptionMessage()); 
        if (log.getType().equals(TLptsLogger.LOG_TYPE.WARNING)) 
          System.out.println("LOG WARNING :" + log.getMessage() + " : " + log.getSupportingText() + " : Exception : " + log.getExceptionMessage()); 
        if (log.getType().equals(TLptsLogger.LOG_TYPE.MESSAGE)) 
          System.out.println("LOG MESSAGE :" + log.getMessage() + " : " + log.getSupportingText() + " : Exception : " + log.getExceptionMessage()); 
      } 
    }); 
 
    // register a listener for the connections 
    TLptsFactoryConnection.addListener(new ConnectionStatementListener()); 
 
    // register a listener for the rowsets produced 
    TLptsFactoryRowSet.addListener(new RowSetListener()); 
 
    // register a listener for the statements 
    TLptsFactoryStatement.addListener(new CreateStatementListener()); 
 
    TLptsDBConnectionType connectionType; 
 
    //Connect To To MS SQL 
    connectionType = connectToDB(); 
    if (connectionType != null) 
    { 
      connectionMSSqlId = connectionType.getId(); 
      TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!! 
    } 
//    ############################################################################################################## 
//        See ExampleConnectionUsage.connectToDB for an example of how to connect to other Data Sources 
//    ############################################################################################################## 
  } 
 
  private void mssqlCreateTable() 
  { 
    //Find the Connection to Create table 
    TLptsConnection connection = TLptsFactoryConnection.getConnection(connectionMSSqlId); 
    if (connection == null) 
      return; 
 
    // Create the statement and set the Type of Statement to CREATE. 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    // Get the rootType 
    TLptsDBCreateStatementRootType rootType = new TLptsDBCreateStatementRootType(); 
    rootType.setTitle("root"); 
    // create a table 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    // Set the name of the table 
    table.setTable("newTable"); 
    // set the table in the root type 
    rootType.setTable(table); 
 
    // Some DB's tables can be temporary 
    TLptsDBCreateStatementTableTemporaryType temporary = new TLptsDBCreateStatementTableTemporaryType(); 
    temporary.setTemporary(false); 
    // Set the temporary type 
    TLptsDBCreateStatementTableGlobalLocal globalLocal = new TLptsDBCreateStatementTableGlobalLocal(); 
    //Temporary table can be global, local or none 
    globalLocal.setGlobalLocal(XLptsDBCreateStatementTableGlobalLocalType.GLOBAL); 
    temporary.setGlobalLocal(globalLocal); 
    table.setTemporary(temporary); 
 
    // With temporary tables, we need to specify whether they are deleted or preserved on commit 
    // TLptsDBCreateStatementTableCommitType commit = new TLptsDBCreateStatementTableCommitType(table); 
    // commit.setCommitType(XLptsDBCreateTableCommitType.DELETE); 
    // table.setCommitType(commit); 
 
    // A table can also have constraints 
    // TLptsDBCreateTableConstraintType tableConstraint = new TLptsDBCreateTableConstraintType(); 
    // see the Javadoc of TLptsDBCreateTableConstraintType for more information 
 
    // ### ID COLUMN ### 
    TLptsDBCreateStatementColumnType idColumn = new TLptsDBCreateStatementColumnType(); 
    // Set the column name 
    idColumn.setColumn("id"); 
    // Set the column sql type 
    TLptsDBCreateStatementColumnSqlType sqlType = new TLptsDBCreateStatementColumnSqlType(idColumn); 
    sqlType.setSqlType(java.sql.Types.INTEGER); 
    idColumn.setSqlType(sqlType); 
 
    // Some types like VARCHAR require a size. Other types, like DECIMAL, require two sizes. The first is the digits 
    // and the second is the decimal digits. 
    // TLptsDBCreateStatementColumnSizeType size = new TLptsDBCreateStatementColumnSizeType(idColumn); 
    // size.setSize1("10"); 
    // size.setSize2("2"); 
    // idColumn.setSizes(size); 
 
    // Text type columns have Collation. Collation is not available in all DB's and is not consistent across data sources. 
    // TLptsDBCreateStatementColumnCollateType collate = new TLptsDBCreateStatementColumnCollateType(); 
    // collate.setCollate("SQL_Latin1_General_CP1253_CI_AS"); 
    // idColumn.setCollate(collate); 
 
    // To set a default value for a column, use the following 
    // TLptsDBCreateStatementColumnDefaultType defaultValue = new TLptsDBCreateStatementColumnDefaultType(); 
    // defaultValue.setDefault("10"); 
    // idColumn.setDefault(defaultValue); 
 
    // Some DB's allow column Constraints. A column can have more than one constraint 
    // see the Javadoc of TLptsDBCreateColumnConstraintType for more information 
    TLptsDBCreateColumnConstraintType columnConstraintType = new TLptsDBCreateColumnConstraintType(); 
    columnConstraintType.setColumnId(idColumn.getId()); 
    // Constraint values are NOT NULL, UNIQUE, PRIMARY KEY, REFERENCES, CHECK 
    // The following needs to be noted for each type: 
    // NOT NULL and UNIQUE cannot have a name. setConstraint, onDelete and onUpdate should not be set. 
    // PRIMARY KEY can have a name but setConstraint, onDelete and onUpdate should not be set. 
    // CHECK can have a name and setConstraint should be set. onDelete and onUpdate cannot be set. 
    // REFERENCES can have a name. The table and column names must be set to the referenced column/table. 
    columnConstraintType.setConstraintType(XLptsDBCreateColumnConstraintParameterType.PRIMARY_KEY); 
    // columnConstraintType.setConstraint("id"); 
    // columnConstraintType.setColumn("idOtherTable"); 
    // columnConstraintType.setTable("otherTable"); 
    // columnConstraintType.setOnDelete(XLptsDBCreateColumnConstraintOnDeleteUpdateType.CASCADE); 
    // columnConstraintType.setOnUpdate(XLptsDBCreateColumnConstraintOnDeleteUpdateType.RESTRICT); 
    // At last we add the columnConstraint to the constraint list of the column 
    idColumn.addConstraint(columnConstraintType); 
    table.addColumn(idColumn); 
 
    // the comments above apply to the code below 
 
    // ### MONEY COLUMN ### 
    TLptsDBCreateStatementColumnType money = new TLptsDBCreateStatementColumnType(); 
    money.setColumn("Money"); 
    TLptsDBCreateStatementColumnSqlType sqlTypeMoney = new TLptsDBCreateStatementColumnSqlType(money); 
    sqlTypeMoney.setSqlType(java.sql.Types.DECIMAL); 
    money.setSqlType(sqlTypeMoney); 
 
    TLptsDBCreateStatementColumnSizeType size = new TLptsDBCreateStatementColumnSizeType(money); 
    size.setSize1("10"); 
    size.setSize2("2"); 
    money.setSizes(size); 
 
    TLptsDBCreateColumnConstraintType columnConstraintTypeMoney = new TLptsDBCreateColumnConstraintType(); 
    columnConstraintTypeMoney.setColumnId(money.getId()); 
    columnConstraintTypeMoney.setConstraintType(XLptsDBCreateColumnConstraintParameterType.NOT_NULL); 
    money.addConstraint(columnConstraintTypeMoney); 
    table.addColumn(money); 
 
    // ### NAME COLUMN ### 
    TLptsDBCreateStatementColumnType name = new TLptsDBCreateStatementColumnType(); 
    name.setColumn("Name"); 
 
    TLptsDBCreateStatementColumnSqlType sqlTypeName = new TLptsDBCreateStatementColumnSqlType(name); 
    sqlTypeName.setSqlType(java.sql.Types.VARCHAR); 
    name.setSqlType(sqlTypeName); 
 
    TLptsDBCreateStatementColumnSizeType nameSize = new TLptsDBCreateStatementColumnSizeType(name); 
    nameSize.setSize1("50"); 
    name.setSizes(nameSize); 
 
    TLptsDBCreateStatementColumnCollateType collate = new TLptsDBCreateStatementColumnCollateType(); 
    collate.setCollate("SQL_Latin1_General_CP1253_CI_AS"); 
    name.setCollate(collate); 
 
    TLptsDBCreateStatementColumnDefaultType defaultValue = new TLptsDBCreateStatementColumnDefaultType(); 
    defaultValue.setDefault("No_Name"); 
    name.setDefault(defaultValue); 
 
    TLptsDBCreateColumnConstraintType columnConstraintTypeName = new TLptsDBCreateColumnConstraintType(); 
    columnConstraintTypeName.setColumnId(name.getId()); 
    columnConstraintTypeName.setConstraintType(XLptsDBCreateColumnConstraintParameterType.REFERENCES); 
    columnConstraintTypeName.setColumn("FIRST NAME"); 
    columnConstraintTypeName.setTable("CLIENT"); 
    columnConstraintTypeName.setOnDelete(XLptsDBCreateColumnConstraintOnDeleteUpdateType.CASCADE); 
    columnConstraintTypeName.setOnUpdate(XLptsDBCreateColumnConstraintOnDeleteUpdateType.NO_ACTION); 
    name.addConstraint(columnConstraintTypeName); 
    table.addColumn(name); 
 
    createStatement.setCreateRootItem(rootType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
  } 
 
 
  // #################################################################################### 
  //            C O N N E C T I O N 
  // #################################################################################### 
 
  private TLptsDBConnectionType connectToDB() 
  { 
    String driverName = "", dbName = "", userName = "", password = ""; 
    ResourceBundle bundle = ResourceBundle.getBundle("resources"); // we have stored all the database info in this bundle 
 
    TLptsDBConnectionType connectionType = new TLptsDBConnectionType(); 
    connectionType.setTitle("MS-SQL DB"); 
 
    dbName = bundle.getString("Testing.database.mssql.testdb1"); 
    userName = bundle.getString("Testing.database.mssql.user"); 
    password = bundle.getString("Testing.database.mssql.password"); 
    driverName = "com.microsoft.sqlserver.jdbc.SQLServerDataSource"; 
 
    XLptsDriverType driver = TLptsDriverLoader.getDriverTypeByClassName(driverName); 
 
    if (driver == null) 
    { 
      System.out.println("Could not find driver for class " + driverName); 
      return null; 
    } 
 
    TLptsDriverType driverType = new TLptsDriverType(driver); 
    // changing values that are default in the driver.loader.xml file. 
    // the DB is now set correctly and will filter through to the Datasource interface execution 
    driverType.setValueForInterfaceFunctionParameter("setDatabaseName", "Database Name", dbName); 
 
    connectionType.setDriverType(driverType); 
 
    if (userName == null || password == null) 
      connectionType.setCredentials(TLptsCryptoUtil.defaultEncryptCredentialsRSA(driverType.getGuestUser(), 
                                                                                 driverType.getGuestPassword())); 
    else 
      connectionType.setCredentials(TLptsCryptoUtil.defaultEncryptCredentialsRSA(userName, password)); 
 
    return connectionType; 
  } 
 
//  ############################################################################################################### 
//  ####             C O N N E C T I O N      L I S T E N E R 
//  ############################################################################################################### 
 
  private class ConnectionStatementListener implements ILptsFactoryConnectionListener 
  { 
    public synchronized void newConnectionCreated(TLptsConnection connection) 
    { 
      System.out.println("New Connection created successfully. Statements can be processed."); 
      if (connection.getId().equals(connectionMSSqlId)) 
        //MS SQl create table\ 
        mssqlCreateTable(); 
    } 
 
    public void newConnectionProcessStarted() 
    { 
      System.out.println("Connecting..."); 
    } 
 
    public void newConnectionFailed(TLptsLog log) 
    { 
      System.out.println("New Connection Failed. The logger caught this."); 
    } 
 
    public void removedAndClosedConnection(TLptsConnection connection) 
    { 
      System.out.println("Connection removed from Connection Manager and closed successfully ... " + connection.getTitle()); 
    } 
 
  } 
 
//  ############################################################################################################### 
//  ####                   S T A T E M E N T       L I S T E N E R 
//  ############################################################################################################### 
 
  private class CreateStatementListener implements ILptsFactoryStatementListener 
  { 
    public void newStatementProcessStarted(TLptsConnection connection) 
    { 
    } 
 
    public void newStatementCreated(TLptsConnection connection, TLptsStatement statement) 
    { 
      // the CREATE has been prepared, so now we can execute it 
      TLptsFactoryStatement.executeOtherStatement(statement); 
      // There is no RETURN status from a CREATE statement 
      System.exit(0); 
    } 
 
    public void newStatementFailed(TLptsConnection connection) 
    { 
      TLptsLogger.logError("Fail to Create Statement for connection " + connection.getTitle(), null); 
    } 
 
    public void removedAndClosedStatement(TLptsConnection connection, TLptsStatement statement) 
    { 
    } 
  } 
 
 
  private class RowSetListener implements ILptsFactoryRowSetListener 
  { 
    public void rowEvent(TLptsRowEvent rowEvent) 
    { 
    } 
 
    public boolean processNewRowSetRows(TLptsRowSetEvent rowSetEvent) 
    { 
      return false; 
    } 
  } 
}