Example of CREATE
//
// 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;
}
}
}