xref: /trunk/main/odk/examples/DevelopersGuide/Database/sdbcx.java (revision 34dd1e2512dbacb6a9a7e4c7f17b9296daa8eff3)

/**************************************************************
 *
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 *
 *************************************************************/



import java.io.*;

import com.sun.star.lang.XComponent;
import com.sun.star.uno.*;
import com.sun.star.bridge.XUnoUrlResolver;
import com.sun.star.beans.XPropertySet;
import com.sun.star.container.XNameAccess;
import com.sun.star.container.XIndexAccess;
import com.sun.star.sdbc.*;
import com.sun.star.sdbcx.*;
import com.sun.star.lang.XMultiServiceFactory;

public class sdbcx
{
    private XMultiServiceFactory xORB;
    private static XConnection con;
    private XTablesSupplier xTabSup;

        public static XMultiServiceFactory rSmgr;
    public static void main(String argv[]) throws java.lang.Exception
    {
        try{
            rSmgr = connect("socket,host=localhost,port=8100");
                        sdbcx test = new sdbcx(rSmgr);
                        test.createConnection();
                        test.displayTableProperties();
                        // now we dispose the connection to close it
                        XComponent xComponent = (XComponent)UnoRuntime.queryInterface(XComponent.class,con);
                        if(xComponent != null)
                        {
                                xComponent.dispose();
                                System.out.println("Connection disposed!");
                        }
                }
        catch(com.sun.star.uno.Exception e)
        {
            System.out.println(e);
            e.printStackTrace();
        }
        System.exit(0);
        }
        public static XMultiServiceFactory connect( String connectStr )
        throws com.sun.star.uno.Exception,
        com.sun.star.uno.RuntimeException, java.lang.Exception
    {
        // initial serviceManager
        XMultiServiceFactory xLocalServiceManager =
            com.sun.star.comp.helper.Bootstrap.createSimpleServiceManager();

        // create a connector, so that it can contact the office
        Object  xUrlResolver  = xLocalServiceManager.createInstance( "com.sun.star.bridge.UnoUrlResolver" );
        XUnoUrlResolver urlResolver = (XUnoUrlResolver)UnoRuntime.queryInterface(
            XUnoUrlResolver.class, xUrlResolver );

        Object rInitialObject = urlResolver.resolve( "uno:" + connectStr + ";urp;StarOffice.NamingService" );

        XNamingService rName = (XNamingService)UnoRuntime.queryInterface(
            XNamingService.class, rInitialObject );

        XMultiServiceFactory xMSF = null;
        if( rName != null ) {
            System.err.println( "got the remote naming service !" );
            Object rXsmgr = rName.getRegisteredObject("StarOffice.ServiceManager" );

            xMSF = (XMultiServiceFactory)
                UnoRuntime.queryInterface( XMultiServiceFactory.class, rXsmgr );
        }

        return ( xMSF );
    }


    public sdbcx(XMultiServiceFactory rSmgr )
    {
        xORB = rSmgr;
    }

    public void createConnection() throws com.sun.star.uno.Exception
    {
        // create the Driver with the implementation name
        Object aDriver = xORB.createInstance("com.sun.star.comp.sdbcx.adabas.ODriver");
        // query for the interface
        com.sun.star.sdbc.XDriver xDriver;
        xDriver = (XDriver)UnoRuntime.queryInterface(XDriver.class,aDriver);
        if(xDriver != null)
        {
            // first create the needed url
            String adabasURL = "sdbc:adabas::MYDB0";
            // second create the necessary properties
            com.sun.star.beans.PropertyValue [] adabasProps = new com.sun.star.beans.PropertyValue[]
            {
                new com.sun.star.beans.PropertyValue("user",0,"test1",com.sun.star.beans.PropertyState.DIRECT_VALUE),
                new com.sun.star.beans.PropertyValue("password",0,"test1",com.sun.star.beans.PropertyState.DIRECT_VALUE)
            };
            //

            // now create a connection to adabas
            con = xDriver.connect(adabasURL,adabasProps);
            if(con != null)
            {
                System.out.println("Connection could be created!");
                // we the XDatabaseDefinitionSupplier interface from the driver to get the XTablesSupplier
                XDataDefinitionSupplier xDDSup = (XDataDefinitionSupplier)UnoRuntime.queryInterface(
                        XDataDefinitionSupplier.class,xDriver);
                if(xDDSup != null)
                {
                    xTabSup = xDDSup.getDataDefinitionByConnection(con);
                    if(xTabSup != null)
                    {
                        XNameAccess xTables = xTabSup.getTables();
                        // now print all table names
                        System.out.println("Tables available:");
                        String [] aTableNames = xTables.getElementNames();
                        for ( int i =0; i<= aTableNames.length-1; i++)
                            System.out.println(aTableNames[i]);
                    }
                }
                else
                    System.out.println("The driver is not a SDBCX capable!");
            }
            else
                System.out.println("Connection could not be created!");
        }
    }

    public void displayTableProperties() throws com.sun.star.uno.Exception
    {
        XNameAccess xTables = xTabSup.getTables();
        String [] aTableNames = xTables.getElementNames();
        if(0 != aTableNames.length)
        {
            Object table = xTables.getByName(aTableNames[0]);
            XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,table);
            System.out.println("Name:          " + xProp.getPropertyValue("Name"));
            System.out.println("CatalogName:   " + xProp.getPropertyValue("CatalogName"));
            System.out.println("SchemaName:    " + xProp.getPropertyValue("SchemaName"));
            System.out.println("Description:   " + xProp.getPropertyValue("Description"));
            // the following property is optional so we first must check if it exists
            if(xProp.getPropertySetInfo().hasPropertyByName("Type"))
                System.out.println("Type:          " + xProp.getPropertyValue("Type"));
        }
    }

    //###########################################################
    // 15. example
    // print all columns of a XColumnsSupplier
    //###########################################################
    public static void printColumns(XColumnsSupplier xColumnsSup) throws com.sun.star.uno.Exception,SQLException
    {
        System.out.println("Example printColumns");
        // the table must be at least support a XColumnsSupplier interface
        System.out.println("--- Columns ---");
        XNameAccess xColumns = xColumnsSup.getColumns();
        String [] aColumnNames = xColumns.getElementNames();
        for ( int i =0; i<= aColumnNames.length-1; i++)
            System.out.println("    " + aColumnNames[i]);
    }
    //###########################################################
    // 16. example
    // print all keys inclusive the columns of a key
    //###########################################################
    public static void printKeys(XColumnsSupplier xColumnsSup) throws com.sun.star.uno.Exception,SQLException
    {
        System.out.println("Example printKeys");
        XKeysSupplier xKeysSup = (XKeysSupplier)UnoRuntime.queryInterface(XKeysSupplier.class,xColumnsSup);
        if(xKeysSup != null)
        {
            System.out.println("--- Keys ---");
            XIndexAccess xKeys = xKeysSup.getKeys();
            for ( int i =0; i < xKeys.getCount(); i++)
            {
                Object key = xKeys.getByIndex(i);
                XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,key);
                System.out.println("    " + xProp.getPropertyValue("Name"));
                XColumnsSupplier xKeyColumnsSup = ( XColumnsSupplier ) UnoRuntime.queryInterface(XColumnsSupplier.class,xProp);
                printColumns(xKeyColumnsSup);
            }
        }
    }
    //###########################################################
    // 17. example
    // print all keys inclusive the columns of a key
    //###########################################################
    public static void printIndexes(XColumnsSupplier xColumnsSup) throws com.sun.star.uno.Exception,SQLException
    {
        System.out.println("Example printIndexes");
        XIndexesSupplier xIndexesSup = (XIndexesSupplier)UnoRuntime.queryInterface(XIndexesSupplier.class,xColumnsSup);
        if(xIndexesSup != null)
        {
            System.out.println("--- Indexes ---");
            XNameAccess xIndexs = xIndexesSup.getIndexes();
            String [] aIndexNames = xIndexs.getElementNames();
            for ( int i =0; i<= aIndexNames.length-1; i++)
            {
                System.out.println("    " + aIndexNames[i]);
                Object index = xIndexs.getByName(aIndexNames[i]);
                XColumnsSupplier xIndexColumnsSup = (XColumnsSupplier)UnoRuntime.queryInterface(XColumnsSupplier.class,index);
                printColumns(xIndexColumnsSup);
            }
        }
    }

    //###########################################################
    // 18. example
    // column properties
    //###########################################################
    public static void printColumnProperties(Object column) throws com.sun.star.uno.Exception,SQLException
    {
        System.out.println("Example printColumnProperties");
        XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,column);
        System.out.println("Name:            " + xProp.getPropertyValue("Name"));
        System.out.println("Type:            " + xProp.getPropertyValue("Type"));
        System.out.println("TypeName:        " + xProp.getPropertyValue("TypeName"));
        System.out.println("Precision:       " + xProp.getPropertyValue("Precision"));
        System.out.println("Scale:           " + xProp.getPropertyValue("Scale"));
        System.out.println("IsNullable:      " + xProp.getPropertyValue("IsNullable"));
        System.out.println("IsAutoIncrement: " + xProp.getPropertyValue("IsAutoIncrement"));
        System.out.println("IsCurrency:      " + xProp.getPropertyValue("IsCurrency"));
        // the following property is optional so we first must check if it exists
        if(xProp.getPropertySetInfo().hasPropertyByName("IsRowVersion"))
            System.out.println("IsRowVersion:    " + xProp.getPropertyValue("IsRowVersion"));
        if(xProp.getPropertySetInfo().hasPropertyByName("Description"))
            System.out.println("Description:     " + xProp.getPropertyValue("Description"));
        if(xProp.getPropertySetInfo().hasPropertyByName("DefaultValue"))
            System.out.println("DefaultValue:    " + xProp.getPropertyValue("DefaultValue"));
    }

    //###########################################################
    // 19. example
    // index properties
    //###########################################################
    public static void printIndexProperties(Object index) throws com.sun.star.uno.Exception,SQLException
    {
        System.out.println("Example printIndexProperties");
        XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,index);
        System.out.println("Name:              " + xProp.getPropertyValue("Name"));
        System.out.println("Catalog:           " + xProp.getPropertyValue("Catalog"));
        System.out.println("IsUnique:          " + xProp.getPropertyValue("IsUnique"));
        System.out.println("IsPrimaryKeyIndex: " + xProp.getPropertyValue("IsPrimaryKeyIndex"));
        System.out.println("IsClustered:       " + xProp.getPropertyValue("IsClustered"));
    }

    //###########################################################
    // 20. example
    // key properties
    //###########################################################
    public static void printKeyProperties(Object key) throws com.sun.star.uno.Exception,SQLException
    {
        System.out.println("Example printKeyProperties");
        XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,key);
        System.out.println("Name:            " + xProp.getPropertyValue("Name"));
        System.out.println("Type:            " + xProp.getPropertyValue("Type"));
        System.out.println("ReferencedTable: " + xProp.getPropertyValue("ReferencedTable"));
        System.out.println("UpdateRule:      " + xProp.getPropertyValue("UpdateRule"));
        System.out.println("DeleteRule:      " + xProp.getPropertyValue("DeleteRule"));
    }

    //###########################################################
    // 21. example
    // print all groups and the users with their privileges who belong to this group
    //###########################################################
    public static void printGroups(XTablesSupplier xTabSup) throws com.sun.star.uno.Exception,SQLException
    {
        System.out.println("Example printGroups");
        XGroupsSupplier xGroupsSup = (XGroupsSupplier)UnoRuntime.queryInterface(XGroupsSupplier.class,xTabSup);
        if(xGroupsSup != null)
        {
            // the table must be at least support a XColumnsSupplier interface
            System.out.println("--- Groups ---");
            XNameAccess xGroups = xGroupsSup.getGroups();
            String [] aGroupNames = xGroups.getElementNames();
            for ( int i =0; i < aGroupNames.length; i++)
            {
                System.out.println("    " + aGroupNames[i]);
                XUsersSupplier xUsersSup = (XUsersSupplier)UnoRuntime.queryInterface(XUsersSupplier.class,xGroups.getByName(aGroupNames[i]));
                if(xUsersSup != null)
                {
                    XAuthorizable xAuth = (XAuthorizable)UnoRuntime.queryInterface(XAuthorizable.class,xUsersSup);
                    // the table must be at least support a XColumnsSupplier interface
                    System.out.println("\t--- Users ---");
                    XNameAccess xUsers = xUsersSup.getUsers();
                    String [] aUserNames = xUsers.getElementNames();
                    for ( int j =0; j < aUserNames.length; j++)
                    {
                        System.out.println("\t    " + aUserNames[j] + " Privileges: " + xAuth.getPrivileges(aUserNames[j],PrivilegeObject.TABLE));
                    }
                }
            }
        }
    }

    //###########################################################
    // 22. example
    // create the table salesmen
    //###########################################################
    public static void createTableSalesMen(XNameAccess xTables) throws com.sun.star.uno.Exception,SQLException
    {
        System.out.println("Example createTableSalesMen");
        XDataDescriptorFactory xTabFac = (XDataDescriptorFactory)UnoRuntime.queryInterface(XDataDescriptorFactory.class,xTables);
        if(xTabFac != null)
        {
            // create the new table
            XPropertySet xTable = xTabFac.createDataDescriptor();
            // set the name of the new table
            xTable.setPropertyValue("Name","SALESMAN");
            // append the columns
            XColumnsSupplier xColumSup = (XColumnsSupplier)UnoRuntime.queryInterface(XColumnsSupplier.class,xTable);
            XDataDescriptorFactory xColFac = (XDataDescriptorFactory)UnoRuntime.queryInterface(XDataDescriptorFactory.class,xColumSup.getColumns());
            XAppend xAppend = (XAppend)UnoRuntime.queryInterface(XAppend.class,xColFac);
            // we only need one descriptor
            XPropertySet xCol = xColFac.createDataDescriptor();
            // create first column and append
            xCol.setPropertyValue("Name","SNR");
            xCol.setPropertyValue("Type",new Integer(DataType.INTEGER));
            xCol.setPropertyValue("IsNullable",new Integer(ColumnValue.NO_NULLS));
            xAppend.appendByDescriptor(xCol);
            // 2nd only set the properties which differs
            xCol.setPropertyValue("Name","FIRSTNAME");
            xCol.setPropertyValue("Type",new Integer(DataType.VARCHAR));
            xCol.setPropertyValue("IsNullable",new Integer(ColumnValue.NULLABLE));
            xCol.setPropertyValue("Precision",new Integer(50));
            xAppend.appendByDescriptor(xCol);
            // 3nd only set the properties which differs
            xCol.setPropertyValue("Name","LASTNAME");
            xCol.setPropertyValue("Precision",new Integer(100));
            xAppend.appendByDescriptor(xCol);
            // 4nd only set the properties which differs
            xCol.setPropertyValue("Name","STREET");
            xCol.setPropertyValue("Precision",new Integer(50));
            xAppend.appendByDescriptor(xCol);
            // 5nd only set the properties which differs
            xCol.setPropertyValue("Name","STATE");
            xAppend.appendByDescriptor(xCol);
            // 6nd only set the properties which differs
            xCol.setPropertyValue("Name","ZIP");
            xCol.setPropertyValue("Type",new Integer(DataType.INTEGER));
            xCol.setPropertyValue("Precision",new Integer(10)); // default value integer
            xAppend.appendByDescriptor(xCol);
            // 7nd only set the properties which differs
            xCol.setPropertyValue("Name","BIRTHDATE");
            xCol.setPropertyValue("Type",new Integer(DataType.DATE));
            xCol.setPropertyValue("Precision",new Integer(10)); // default value integer
            xAppend.appendByDescriptor(xCol);
            // now we create the primary key
            XKeysSupplier xKeySup = (XKeysSupplier)UnoRuntime.queryInterface(XKeysSupplier.class,xTable);
            XDataDescriptorFactory xKeyFac = (XDataDescriptorFactory)UnoRuntime.queryInterface(XDataDescriptorFactory.class,xKeySup.getKeys());
            XAppend xKeyAppend = (XAppend)UnoRuntime.queryInterface(XAppend.class,xKeyFac);
            XPropertySet xKey = xKeyFac.createDataDescriptor();
            xKey.setPropertyValue("Type",new Integer(KeyType.PRIMARY));
            // now append the columns to key
            XColumnsSupplier xKeyColumSup = (XColumnsSupplier)UnoRuntime.queryInterface(XColumnsSupplier.class,xKey);
            XDataDescriptorFactory xKeyColFac = (XDataDescriptorFactory)UnoRuntime.queryInterface(XDataDescriptorFactory.class,xKeyColumSup.getColumns());
            XAppend xKeyColAppend = (XAppend)UnoRuntime.queryInterface(XAppend.class,xKeyColFac);
            // we only need one descriptor
            XPropertySet xKeyCol = xKeyColFac.createDataDescriptor();
            xKeyCol.setPropertyValue("Name","SNR");
            // append the key column
            xKeyColAppend.appendByDescriptor(xKeyCol);
            // apend the key
            xKeyAppend.appendByDescriptor(xKey);
            // the last step is to append the new table to the tables collection
             XAppend xTableAppend = (XAppend)UnoRuntime.queryInterface(XAppend.class,xTabFac);
             xTableAppend.appendByDescriptor(xTable);
        }
    }

    //###########################################################
    // 23. example
    // create a user
    //###########################################################
    public static void createUser(XNameAccess xUsers) throws com.sun.star.uno.Exception,SQLException
    {
        System.out.println("Example createUser");
        XDataDescriptorFactory xUserFac = (XDataDescriptorFactory)UnoRuntime.queryInterface(XDataDescriptorFactory.class,xUsers);
        if(xUserFac != null)
        {
            // create the new table
            XPropertySet xUser = xUserFac.createDataDescriptor();
            // set the name of the new table
            xUser.setPropertyValue("Name","BOSS");
            xUser.setPropertyValue("Password","BOSSWIFENAME");
            XAppend xAppend = (XAppend)UnoRuntime.queryInterface(XAppend.class,xUserFac);
            xAppend.appendByDescriptor(xUser);
        }
    }
}